As many of TM1 consultants know, reading TI code always has the possibility of being a tricky task – especially if it is someone else’s code. This is mostly due to TM1 Perspectives Turbo Integrator lacking Syntax Highlighting (See our Notepad++ highlighter) but in part it is due to poor variable naming, indenting, casing or things simply not developed consistently throughout the model. Read more …
Creating a Time Dimension
This post should really be titled “Creating a Time Dimension with a method which allows it to be easily expanded later.” but that simply wont fit. The concept is simple, where previously a time dimension is manually updated and eventually becomes tiresome to update (once every few years) we can instead use one process to create the dimension and for each year call another process which added the year its given, its quarters and months along with aliases and attributes. This way, in 2 years time the dimension can be expanded simply by running the second process and providing the years to add as parameters.
The reason this sample dimension has no alternate hierarchies is because this example was taken from a system in-which the time dimension was to be consumed via Cognos Express Reporter.
Process 1: Meta Time Pt1
- Creates the Dimension
- Adds the Name Alias
- Sets the Sort Order (In the Attachment, not reflected in screenshots)
- Calls the 2nd process for the years to be added by default
Process 2: Meta Time Pt2
- Adds the Quarter Elements
- Adds the Month Elements
- Adds the Names on the Quarters
- Adds the Names on the Months
Download Here: Time Dimension Process Samples (.zip)
How to Import all Files in a Directory
We’ve recently been on a Property Management project using TM1 to deliver a Management, Planning and Reporting Solution. The Client has properties which are managed by an external property manager who produces tenancy schedules and general ledger reports for data consumption.
The Problem:
Every properties reports would come from the external property manager in a different file. Making the process to load these into TM1 only mildly faster than using Excel as a solution.
The Solution:
A small script was developed which lists the files from a given directory into a text file for consumption by a “Master” TI Process which then calls another “Child” TI Process for each file, giving the filename and path as a parameter each time.
The “Master” TI Process’ data source is the generated list of files and in the prolog it would run the script over the provided directory (via a parameter variable). This ensures the list of files is up to date.
The “Child” TI Process dynamically sets the datasource to the filename and path provided as a parameter. As the load is accumulative, the section of the cube (a property specific slice) being loaded needs to be cleared before each load to prevent data doubling up. This was further complicated by the fact that the property could not be determined by the name of the file. The clear out was achieved by reading the first line of the datasource file and retrieving the Property Identifier from it, thus performing the Zero Out in the Metadata Tab.
Download Here: makeListOfFoldersFiles.zip
Script Usage:
*PathToScript*\makeListOfFoldersFiles.vbs "*PathToFolder*" "*PathToOutputTextFile*"
Example:
C:\makeListOfFoldersFiles.vbs "C:\TM1s\PropertyModel\Data Files\Oct 2011\" "C:\TM1s\PropertyModel\Data Files\files.txt"
There is additional scripting required when then using this within a TI Process:
Executing the Script from a TI Process: (Prolog)
sExec = 'CScript D:\blat\full\makeListOfFoldersFiles.vbs "' | pFolder | '" "C:\TM1s\PropertyModel\Data Files\files.txt"'; EXECUTECOMMAND(sExec,1);
This will execute the Script listing the files held within the folder path stored in the parameter variable “pFolder” and write this list to the specified path, which is also the datasource path for this process. Within the Data Tab of the process the “Child” process is called using the “EXECUTEPROCESS” function passing the filename as a parameter. Lastly the “DataSourceNameForServer” Local variable is used within the prolog of the “Child” Process to set the given filename as the datasource to be imported.
Nightly TM1 Backups
All data pertaining to a TM1 server is typically held in a single folder we call the Data Directory. We recommend you backup this folder at the very least weekly. Although most servers have automatic backups owned by the IT Department it can be beneficial to have an archive backup (*.zip file) taken regularly which enables (subject to licenses) a TM1 Administrator to run a backup in parallel with their production server for the purpose of restoring data without engaging the IT Department.
Requirements:
- Download: Command Line version of 7-Zip found here: http://www.7-zip.org/download.html
- Access to the Server Running TM1
Theory:
An optimal solution puts TM1 in charge of the Archival process enabling TM1 developers and administrators to Backup the server from the TM1 Frontend prior to development or data loads etc. As such its a logical fit that the Archive Process be executed from a TI Process. The 7-Zip Command Line interface provides this flexibility when combined with the TI Processes EXECUTECOMMAND function.
Note: 7-Zip is a Commercially Free File Compression Application/Library.
Below is a sample of using this methodology, the path variables will need to be updated when it is initially setup but the basics should remain the same. This will create a backup archive with a Date and Time Stamp to differentiate if from other backups.
SaveDataAll(); sExec = 'D:\archiver\7za.exe'; sBackupDir = 'D:\TM1Servers\Finance\Backups\'; sDataDir = 'D:\TM1Servers\Finance\Data\'; sFileName = 'Data ' | TIMST(NOW(), '\Y\m\d \h.\i', 1) | '.zip'; sArgs = ' a -tzip '; sCommand = sExec | sArgs | '"' | sBackupDir | sFileName | '" "' | sDataDir | '*"'; EXECUTECOMMAND(sCommand,0);
Note: This process does not wait for the command to finish before proceeding, this is simply so that TM1 doesn’t wait unnecessarily.
For simplicity sake the following lines need to be updated when this system is implemented on your site:
- sExec = ‘D:\archiver\7za.exe’; (The path to the 7-Zip Command Line Executable)
- sBackupDir = ‘D:\TM1Servers\Finance\Backups\’; (The Path to the Destination Directory)
- sDataDir = ‘D:\TM1Servers\Finance\Data\’; (The Path to the TM1 Server’s Data Directory)
TM1 Data Directory Archives
An Extract form the 7-Zip License File:
Notes: You can use 7-Zip on any computer, including a computer in a commercial organization. You don't need to register or pay for 7-Zip.