asvlad wrote:I have following trouble:
My customer wants to develop following architecture:
1) Corporate TM1 Server (for working analyst and other inside stuff);
2) remote stuff, which MUST work off-line all current month, and only for end of the month they must connect to the corporate TM1 server for uploading their data. Such stuff must have opportunity to refresh dimension structure for any time if it needed. Also such stuff must enter their data with definite rules of validation (it's about pick lists...).
3) Installing of local TM1 server per each member of remote stuff is not acceptable by customer requirements.
Can anybody help my?
TM1's not really best designed for off-line working. Certainly you can't get the benefit of having a single repository for business rules.
Nonetheless, I know that there are some mutterings where I am about wanting that as well, though those haven't progressed very far yet.
However whatever the case, what you
do need is:
(1) A way to store and update data in an off-line fashion.
To my mind there are three basic options for this.
One is to store the data in an Excel workbook. Obviously there are some risks involved with this; it's vulnerable to data loss (but then so is any other off line method), and it's also vulnerable to metadata corruption (deletion of sheets or columns, someone inserting rows when they shouldn't, etc., all of which can have a toxic effect when it comes to uploading /refreshing the data) unless you keep it locked down tightly.
Input validation is a particular problem; Excel's data validation lists are all but useless for this partly because they don't have an autocomplete option but more importantly because if you paste data from another source into the cells, not only does the validation fail but it gets obliterated. The only viable way around this would be to use input forms (whether based on spreadsheets or user forms) which allow more robust pre-entry validation.
It may be possible to replicate any business rules (for example, rules to calculate staffing costs) in an Excel workbook using a combination of formulas and, if necessary, VBA but of course if those rules change you're back in the Excel Hell situation of needing to ensure that all of the workbooks that are "out there" are handed in and updated with the new rules.
A second option may be to use an Access database. If the users have Office Professional they could use this as is, otherwise you'd need some application to serve as the front end for it. In some ways this is more powerful and flexible than Excel. Certainly input validation can be far more robust, and there need not be any assumptions about how many rows to set aside for input.
Business rules can be calculated in a similar fashion to the Excel solution, but have the same weaknesses.
This would probably require a bit more "up-front" investment of time and resources, and the connection to TM1 to do the upload would have to be done via API code.
The third option is to store the values in plain text data files, though this would probably require some form of front end application to read and write them since I doubt that users would want to edit them manually. Mind you, even an Excel workbook could be made to serve that purpose if you throw enough VBA code at it. It would present plenty of challenges but none would be insurmountable. Further routines would be needed to upload the data to TM1 once connected.
(2) Potentially a way to update the metadata.
When you say
Such stuff must have opportunity to refresh dimension structure for any time if it needed
it's not clear whether you're indicating that they need to be able to make changes to the structure (which would be uploaded to the server), or whether these need to be refreshed
from the server. In the latter case, they certainly and obviously wouldn't be able to do that off line, unless the metadata structure was exported to a text file that they could pick up. Doing it on-line isn't so much of a problem; it would simply need to be pulled down to the container application (Excel, Access, a VB application or whatever) via VB code.
I doubt that you'll find that very many people have implemented this as a solution, though I don't doubt that there will be a few.