Product:
IBM Cognos TM1 10.1.1
Microsoft Windows 2008 R2 server
Problem:
How do I create a system cube, where I can store common values ( like file paths, database logins) and other things that can change when I move my TM1 application between Cognos Servers.
Solution:
Log into TM1 Architect
Create first a dimension Sys.Environment variables, like this:
Right click on dimensions and select “create new dimension”
Right click and select “insert element”
Enter “fileshare_to_TM1”
Select “string” for Element type.
Click Add.
Enter “ODBC connection”
Select “string” for Element type.
Click Add.
Click OK
Click OK in the top right of the dialog.
Enter the name Sys.Environment variables.
Click OK
Right click on dimensions and select “create new dimension”
Right click and select “insert element”
Enter Value
Select “string” for Element type.
Click Add.
Enter Number
Select “simple” for Element type.
Click Add.
Click OK
Click OK in the top right of the dialog.
Enter the name Sys.Measures
Click OK
Right click on cubes and select “create new cube”
Enter cube name: Sys.Settings
Move the two dimensions to the right side column by mark sys.measures and sys.environment variables and click on the arrow button to move them over.
Click create cube.
Now you can use this for your application.
Double click on the cube Sys.Settings to get into Cube Viewer.
Click on Recalculate icon to show content.
Under the value column for the row of fileshare_to_tm1 enter the path your application will use to get csv files to import, in format \\servername\sharename\foldername , e.g. \\srv001\common\budgetimport.
In the same manner can you enter the name of the ODBC connection you are going to use in the Microsoft Windows server where TM1 is installed, e.g. DATA42 ( if you have named your ODBC connection to that).
To create a ODBC connection you start program c:\windows\system32\odbcad32.exe.
This value can you then use in your TI process code.
In your PROLOG tab you enter;
ODBC_Connection = cellgets (‘sys.settings’,’ODBC Connection’, ‘Value’);
This will populate variable ODBC_Connection with the value DATA42 that you have stored in the cube.
So when you set the source for the data source programmatically in your prolog tab with the value DataSourceNameForClient=ODBC_Connection; The “Data Source Name” will be DATA42.
If you move your TM1 application to some other TM1 server, then the TM1 administrator easy can update the Sys.Settings cube only. And then does not need to search your TI process code for references to the ODBC connection.
In similar manner you can get the path to your code by in PROLOG tab enter code like this;
sPath = cellgets(‘sys.settings’,’fileshare_to_tm1′,’value’);
This will set the variable sPath with the share you need, so when you need it you can use it in cases like;
DataSourceNameForServer= sFilePath;
or
Asciioutput ( sPath | ‘\’ | sFilename, vA_Value, vB_Value);
The best is if the sFilename also come from a stored value in Sys.Settings.
Right click on dimension Sys.Environments variables and select Edit Dimension Structure to get into Subset Editor.
Right click and select Insert Element
Enter “Filename”
Select “string” as element type
Click Add
Click OK
Click OK to close the Dimension Editor
One question about saving changes to dimension you select Yes.
Double click on the cube Sys.Settings to get into Cube Viewer.
Click on Recalculate icon to show content.
At Filename you under Value enter the name of the csv file you want to import, can be SapExport.csv.
Now use this code to get the value;
sFilename = cellgets (‘sys.settings’,’Filename’, ‘Value’);
Then you can create the sFilePath with this code;
sFilePath = sPath | ‘\’ | sFilename;
This will add value of sPath together with value of sFilename with a \ in-between.
The value of sFilePath will then in our example be \\srv001\common\budgetimport\SapExport.csv.
By using this technique for any environment value, like paths or database connections, it is easy to support the application when moved between DEV or PROD environments.