When working with TM1 with environments that you fully control it's easy to manage the behavior of the Turbo integrator processes (TI processes).
Loading data to the TM1 models is simple and very straight forward if we use the Metadata and Data tabs of the TI process, guaranteeing that all records in the source will be read and handle within the process code.
However, this full control access rights don't occur often in complex organizations, where the accountability regarding data warehouse, databases, TM1 and even infrastructure is geographically spread.
This post presents a methodology regarding the configuration of Data sources in TI Processes and how to manage connections between different environments.
Managing Data Sources
Whenever you open a process with a data source, TM1 tests the connection that was defined through the wizard.
Because of this, if the authentication to the source changes (for example because of security policy after a period of time), it’s very easy to get the user locked due to incorrect logins while developing or managing the process.
This TM1 behavior can’t be disabled and if the source is not defined you won’t be able to take advantage of the metadata and data tab in the wizard.
Of course you can define the loop within the dimensions of the source and cube related to the process either in the prolog or epilog tab. However, the process may be simplified for this functionality to be available.
It’s possible to define a data source connection during the process. Doing this, will not enable both tabs but will avoid the connection tryouts that TM1 does when opening the process.
To do this, the following code may be used in the Prolog tab (example regarding ODBC connections):
### ODBC Data Source Definition ###
DataSourceType=’ODBC’;
DataSourceNameForServer=’Connection Name’;
DatasourceUserName=’Userid’;
DatasourcePassword=’Password’;
DatasourceQuery = Expand (‘
select
A,B,C,D
from INBOUND_TABLE’);
Datasource instructions will guarantee that the TI process uses the query defined by DatasourceQuery as the source of the process and, when you open the process this query isn’t tested (meaning that if the connection authentication changes it will not be locked by opening the process).
However, the metadata and data tab will remain unavailable.
To complete the data source creating in TM1 you will need to get the variables defined in the Variable Tab. In fact, just when you have variables defined as Element or Consolidation will the metadata and data tab be available.
To trick TM1 regarding this you must use a method to create the structure of variables and columns for the TI process to use.
As a best practice you should create in the server a folder to keep the structures (for example, “Source Templates”).
In this folder you can add a text file for each TI process that uses data sources.
For the example shown above let’s name the process as [DIM] Update dimension X.
In the source templates we should create a file named [DIM] Update dimension X.txt.The main goal is to define the structure in the same way as it will be read in the Datasourcequery instruction. Regarding our current example the file would be:
A;B;C;D
The configuration of the file (delimiter type, quote char, number of title records, etc.) isn’t of any importance but you should keep a standard and simple structure. However, the order of the columns must be the same as you’ll read it in the query that you’re using in the process and the number of columns must be the same (TM1 will associate each column as a variable and later will link this to the columns in the data source query).
With this file you're able to create a text data source in the process and TM1 will identify A, B, C, D as variables. Having the variables defined regarding Contents will allow TM1 to make Metadata and Data tab available.
Following this you can redefine the data source configuration in Prolog and use it in the TI process.
Managing Connections and Environments through Configuration Cubes
In the creation and configuration of the data sources in an infrastructure with Development, Quality and Production environments the TM1 may need to be configured to adjust to these different environments. This gains more importance in environments where the developers don't have access to the service account authentication in Quality or Production environments.
To solve this, a configuration cube may be the solution to manage these connections. Because this is sensible information, the cube must be restricted to Administration and/or specific group of users that will be accountable to change the user authentication for the different groups.
Although the access is restricted to some groups TM1 is able to get data in TI Processes.
The following diagram is an example of a Configuration Cube (named Admin_Cube) where the power user is able to select the environment to be used in the server and the different connection configurations.
The use of the configuration cube allows the development not to place the security information hardcoded in the TI processes and to transfer the accountability of this information to specific user groups.
In the TI Processes, the data source configuration must be changed to gain the flexibility regarding the authentication information.
In the Prolog Tab the process must gather information regarding the environment selected in the Admin_Cube and with it define the correct authentication to be used (the dimensions in the CellGetS must be in the same order of the dimension’s order in the Admin_Cube).
### Environment definition : DEV, QA, PROD ###
sEnvironment=CellGetS(‘Admin_Cube ‘,’Parameter_Value’, ‘Parameter’);
With the Environment information loaded in the sEnvironment the process is configured to automatically point to the variable to be used.
DataSourceNameForServer=CellGetS(‘Admin_Cube ‘,’Parameter_Value’, sEnvironment|’Connection’);
DatasourceUserName=CellGetS(‘Admin_Cube ‘,’Parameter_Value’, sEnvironment|’User’);
DatasourcePassword=CellGetS(‘Admin_Cube ‘,’Parameter_Value’, sEnvironment|’Password’);
Joining the Environment (defined as DEV,QA or PROD in the example) with the suffix of the elements in the Admin_Cube the TI Process will get the authentication to the variables.
From the example used, the Prolog Tab of the process would have the following configuration for data source and connection dynamic management.
### Environment definition : DEV, QA, PROD ###
sEnvironment=CellGetS(‘Admin_Cube ‘,’Parameter_Value’, ‘Parameter’);
### ODBC Data Source Definition ###
DataSourceType=’ODBC’;
DataSourceNameForServer=CellGetS(‘Admin_Cube’,’Parameter_Value’, sEnvironment|’Connection’);
DatasourceUserName=CellGetS(‘Admin_Cube ‘,’Parameter_Value’, sEnvironment|’User’);
DatasourcePassword=CellGetS(‘Admin_Cube ‘,’Parameter_Value’, sEnvironment|’Password’);
DatasourceQuery = Expand (‘
select
*
from INBOUND_TABLE’);
