Bulk Absorption Options
From a pure architectural perspective, what is sometimes referred to as “bulk uploading” sometimes is (mistakenly) considered to be part of reporting or included in the data consumption component of a TM1 application design. Loading and/or updating data is part of an applications absorption or configurations components and should be handled with a best practice approach of leveraging TM1 TurboIntegrator scripts and/or active WebSheets (designed based upon user and environmental requirements and constraints).
The 3 best approaches to loading data in substance are:
- Bulk Paste
- File Route
- Off-Line Template
If data volumes are manageable, using bulk past is most likely the best option. Bulk paste utilizes a TM1 Websheet that allows the business user to paste from the clip board all data to be loaded into the TM1 application (rather than inserting the data line by line). To make this effective, the worksheet must not include formatting that would prevent a “single paste” – this would include subtotals, “spacer” rows and/or columns or actually any Excel formatting that requires data to be manually (or programmatically) translated (back to a “raw” form). The general “rule of thumb” here is that the use of bulk paste should only be used when the data to be loaded fits on a single screen (little or no horizontal or vertical scrolling is required).
For larger volumes of data, the File Route option should be considered. With this option, network administration or IT support is required to set-up (and maintain) a controlled network folder location where user “load files” can be placed. From this location, TurboIntegrator scripts can be written to identify the file, load the data and then move the file to an archive location. This option works best when “near real time” loading is acceptable. (In addition, a mechanism should be implemented to inform the appropriate personnel when or if load errors occur).
The Off-Line template is certainty the most user-friendly. With this option, an Excel worksheet is programmed to allow a user to download data from a TM1 application (if applicable) and then automatically disconnect (from TM1) – this allows a business user to work locally and in a non-connected mode in a static (with no TM1 functions or formulas) Excel worksheet. Once the data is “ready”, the worksheet can validate and/or format the data and then connect to the TM1 application and load the information. Real time statuses are given. The drawback to using this option is that unless the business user is restricted to a local network connection, a virtual desktop must be utilized (such as Citrix or Terminal Services).