Variables can be used in a variety of ways in Turbo Integrator processes. The use of variables can increase efficiency by making the administration of your TM1 model easier. Variables can also play a role in providing an end-user with the ability to perform administrative tasks similar to typical user input.
Many times SQL statements used as a data source for a TI process need to be filtered in the where clause to narrow the result set. The values used in the where clause may need modified throughout the year. Most end users are not SQL experts, but they are more than capable of entering appropriate values into an input cube.
You can use variables in your SQL statement that retrieve values input by a user. This prevents users from having to modify SQL they don’t understand, and also prevents the need for more technical administrators from having to get involved.
1. Set up an input cube where the appropriate user(s) can input the values needed for the TI process.
In this example, the user can update information about their current forecast cycle. A TI process that imports actuals from their GL system will utilize the values entered by an end user. Each time a new forecast is planned, only the input parameters need updated, and the TI process does not need to be modified.
2. Set up the TI process with no variables in the SQL.
When variables are included in a TI process SQL statement, the preview is unavailable. So, first you should set up your TI process with no variables in the SQL to test that all is working correctly and then fold them in after the process is set up properly without them.
On the Data Source tab, write the SQL statement without variables and preview the data.
Configure the settings on the Variables tab.
Specify the mappings.
Save the TI process. You should also run the process and ensure that it functions as expected.
3. Set the variable(s) on the Advanced Prolog tab.
The Prolog tab is executed before the SQL statement on the Data Source tab, so we are able to set a variable here and use it within the SQL. In this example, the variables are retrieving values from a cube that are set by a user.
4. Modify the SQL statement on the Data Source tab to use the variables.
Notes: To use a variable in the query, specify the variable name between two question marks, as shown above. Also, the variables must behave the same way as the item it is standing in for. For example, if a variable is standing in for a string, it must be contained within single quotes. You may need to cast either the variable or an item from the database to ensure you are comparing compatible data types.
5. Save the TI process.
You now have a Turbo Integrator process that uses variables in the SQL statement.
By following the steps above, you should now be able to use variables in your SQL statement that retrieve values input by a user. This provides many benefits as users will not have to modify SQL and technical administrators will not need to be involved.
