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.