TM1 Offline Templates

[appleglaze28]

I'd like to ask if any of you have used or created Offline Data Entry Templates? I'd like to get your opinion on the benefits & downfall and if its ever really recommended other than it allows you to buy less license and depend on the admin or some other user to upload the data from that Offline template with the use of TI.

[Alan Kirk]

I'd like to ask if any of you have used or created Offline Data Entry Templates? I'd like to get your opinion on the benefits & downfall and if its ever really recommended other than it allows you to buy less license and depend on the admin or some other user to upload the data from that Offline template with the use of TI.

We make use of them for Capex budgeting; a lot of the people who have to do entry for Capex aren't regular TM1 users, so they fill in the templates and send them back to someone in the Group Planning area for upload.

The upside is obvious; if you're on named user licencing, you don't have to burn up user licences on people who may use the system once per year. (And who will doubtless have forgotten how to log in in the meantime.)

The primary down side is the possibility of people putting in dodgy inputs. Unfortunately you can't validate against element names the way a direct input template will. This is also relatively easy to handle for title elements; you can make the inputs selectable from a drop down control. (Or if you're really desperate, Data Validation; however I tend to regard data validation lists as only a few inches this side of useless. In the first place if someone pastes over the cell it obliterates the validation, and in the second when you have a long list of element values the user can't type in the beginning of the list and have matching elements selected.)

It's more of a problem with row and column elements, but even then it's a fairly trivial exercise to have some VBA code which validates against lists which are contained in the workbook. However the more lists you embed in the workbook, the larger it'll be. Excel workbooks have a tendency to bloat in size rather too readily for my liking.

And of course you have to be careful about ensuring that the users can't destroy the structure of the workbook (deleting sheets or critical rows and columns), thus making it more difficult for the user who does the upload.

There's nothing to stop you from incorporating the DBS formulas in hidden cells; if the input cells are just normal blank cells with DBS formulas hidden away, then the user who does the upload can simply log in and press [F9].

One other down side is that it's the admin's name which will go against the entries in the log file rather than the name of the person who created the values; that's not ideal from an audit trail perspective. You'd need to keep all of the input workbooks as your audit trail.

In short, it's not an ideal solution; but with the cost of licences it's one that can work in some circumstances.

[Martin Ryan]

We use them a bit as we have a lot of far flung users, and at budget time there are a lot of smaller entities that need to enter the budget, but it's not worth getting a whole bunch of licences for the sake of budget time two times a year.

We use a VBA heavy template that spits out a csv file onto the WAN to be sucked up by a TI process. It takes a bit of looking after but it does the job.

What particular scenario are you facing?

Martin

[appleglaze28]

I'm not really looking into any particular scenario just yet. Since its kinda a very tedeious job on creating 1 online & offline templates. Is it possible to create a VBA script on being able to produce multiple workbooks. Like for example I have like 20 cost center, I want 1 cost center 1 workbook with using the existing Online tempate layout? And having the cost center drop down be static to 1 specific cost center.

How do I encorporate the dimensions with subnm in the online template in the offline template? I assume I need to have some hidden columns to accomodate those dimension elment. Or like in my example can I like have a VBA code for a button "CREATE TEXT FILE" to add 1 additional column for the static cost center as it creates the text file to be use by TI to upload the data?

Since for easier loading for the admin I also thought maybe its easier to create a user form that gets data from TM1 and from there enter data that is fed to rows. However, downside of this, the datas that were entered wouldn't be viewed in a nice way if I'm not gonna hide it and if I hide it, people doing data entry wont be able to see the data.

What is the best way to create offline templates to ensure that performance is still good.

[highlnder8]

I would probably create one template with two (or more) sheets. Sheet 1 would be for the online users, Sheet 2 for off line users. Then use VBA to to select which to use and delete the other based on an Attribute of the Cost center.