What is TurboIntegrator (TI)?
Introduction
TM1 TurboIntegrator is the programming or scripting tool that allows you to automate data importation, metadata management, and many other tasks.
Data sources available with TurboIntegrator
Using TurboIntegrator, you can import data from the following data sources:
- Industry-standard comma-delimited (CSV) text files including ASCII files
- Information stored in relational databases (accessible through an ODBC data source)
- Other OLAP cubes, including Cognos TM1 cubes and views.
- Microsoft Analysis Services
- SAP via RFC, creating TI processes
To create a new TI script you can use TM1 Server Explorer. You have to just right-click on Processes and then select Create New Process. TM1 then opens the TurboIntegrator dialog showing a new, blank process ready for your code.
Within the process, there will be five visible tabs:
- The Data Source tab
- The Variables tab
- The Maps tab (this tab is initially grayed out)
- The Advanced tab
- The Schedule tab
The Data Source tab
You can use the Data Source tab to identify the source from which you want to import data to TM1.
The Variables tab
The concepts of database table columns or file fields are handled in the TI Variables tab. If the process has a data source defined, each column in that data source will become a variable in the Variables tab.
Important facts on the Variables tab include:
- Be sure to verify the variable type of each and every variable. TM1 will make assumptions about your data which are not always correct. A string, which has numeric data in it, will be initially defined as numeric by TM1.
- The order of the variables in the Variables tab is really irrelevant to your scripting. For example, a CellPutN function that you code need not have variables in the same order as which appears in the variables list. The CellPutN function needs the variables in the order of the cube’s dimensions.
- A variable is the name of a specific value. You can use the variable’s value in any way you need to; write it to a cube, use it as an element name, write it to a text file using the ASCIIOutput function, or whatever you want.
- TM1 will show sample data from the data source in the Variables tab but only if TI is currently connected to the data source.
Defining your own variables!
Although TM1 sets up the variables it sees in your selected data source, you can also define your own variables using the New Variable button on the Variables tab.
More on the Variables tab
If you select an existing TM1 cube as a data source, you will see in the Variables tab that:
- There is one variable for each of the dimensions in the selected cube
- There is one variable called Value which will contain the value that is stored at the intersection of the cube’s dimension elements.
Local variables
Local variables are destroyed when a process exits. These local variables include:
- DatasourceNameForServer
- DatasourceNameForClient
- DatasourceType
- DatasourceUsername
- DatasourcePassword
- DatasourceCubeview
- DatasourceDimensionSubset
- DatasourceASCIIDelimiter
- DatasourceASCIIDecimalSeparator
- DatasourceASCIIThousandSeparator
- DatasourceASCIIQuoteCharacter
- DatasourceASCIIHeaderRecords
- Value_Is_String
- NValue
- SValue
- OnMinorErrorDoItemSkip
- MinorErrorLogMax
- DataSourceODBOCatalog
- DataSourceODBOConnectionString
- DataSourceODBOCubeName
- DataSourceODBOHierarchyName
- DataSourceODBOLocation
- DataSourceODBOProvider
- DataSourceODBOSAPClientID
- DataSourceODBOSAPClientLanguage
Some very special local variables
- NValue: Used to access your cube data as numeric data.
- SValue: Used to access your cube data as a type string. Keep in mind that if the data in your cube is numeric you still have to convert it to string before using it.
- Value_Is_String (as a numeric value): It is 0 if the Value variable is numeric and 1 if it is a string (note that this refers to the value stored in the cube).
if (VALUE_IS_STRING=1);
# Use CellPutS… ..
else;
# Use CellPutN…
end if;
The Maps tab
The Maps tab is used if you want TM1 to generate TI code automatically for you.
For this purpose you need to perform the following steps:
- You need to have at least one variable on the Variables
- The Contents column of the variable(s) must be set to Element, Consolidation, Data, or Attribute.
Cube tab
Key points to know about the Cube tab are:
- You need to have at least two variables defined as Element in the Contents column for TI to generate code to create or recreate a cube.
- To update data in a cube you must use variables of Contents type Data.
- Generated TI code does not always follow the same syntax as that of the code written by you.
- If you select the Recreate option rather than the Create option, code will be generated to destroy the cube (if it exists) as well as to create the cube.
- You can also specify whether you want to store the values or Accumulate the values.
- With a simple checkbox selection, cube logging can be enabled or disabled— more on cube logging later in this chapter.
- If you select Update Cube you can then specify the Zero out view to be used.
Dimensions tab
The following are the key facts to know about this tab:
- The Element Variable column shows the name of the variable defined on the Variables
- The Sample Value column is for information only.
- The Dimension column is dependent on the value you select for its Action
- The Order In Cube column only applies if you specify Create or Recreate a cube on the Cube
- The options in the Action column are Create, Recreate, Update, and As Is.
- The As Is action will use the elements that already exist in the specified dimension. If a variable contains an element name which currently does not exist in the dimension, you will get an error when you run the process.
- The Element Type column indicates if the element is numeric or string.
- You can order the elements in the dimension using the Element Order
The Advanced tab
All TI processes give you the ability to provide runtime parameters.
TI processes also include several procedures tabs. They are as follows:
- Prolog
- Metadata
- Data
- Epilog
Order of operations within a TurboIntegrator process
- When you run a TI process, the procedures are executed in the following sequence: Prolog, Metadata, Data, and Epilog (basically left to right as they are displayed).
- The Prolog is executed only one time.
- If the data source for the process is None; Metadata and Data are disabled and do not execute.
- If the data source for the process is None; Epilog executes only one time, immediately after the Prolog finishes processing.
- If the data source for the process is None, Metadata and Data will execute for each record in the data source.
- Code to build or modify a TM1 dimension resides in the Metadata
- The Metadata tab is used to build TM1 subsets.
- All lines of code in the Metadata procedure are sequentially executed for each record in the data source.
- All lines of code in the Data procedure are sequentially executed for each record in the data source.
- The Data procedure is executed for each row or record in the data source when an error is generated in the procedure for multiple times.
- The Data procedure is the procedure used to write/edit code used to load data into a cube.
- The data source is closed after the Data procedure is completed.
- The Epilog procedure is always the last procedure to be executed
- Not all TM1 functions will work as expected in every procedure. For example, the DimensionElementInsert function is a TI function that can only be used in the Data or Epilog procedures of a process.
The Schedule tab
The Schedule tab allows you to schedule a process to execute at regular intervals. Keep the following in mind about the Schedule tab:
- You can use the calendar to select a start date for the process.
- Processes can be set with a frequency granularity of seconds, minutes, hours, or days.
- Parameters can be provided which are available to the process every time it runs.
- You can supply a name for the Chore that will run your process (it does not have to have the same name as the process itself).
String lengths limit in TurboIntegrator
Remember that TI is capable of handling string data in sizes of up to 8000 bytes characters at a time.
Reserved words
There are four types of reserved words in TI:
- Rule function names such as DATE, DTYPE, and AVG
- Process function names such as CellPutN or ASCII output
- Implicit variable names such as DatasourceASCIIDelimiter
- TI keywords such as If and While
Functions
- AsciiOutput: This function writes a comma-delimited record to an ASCII file.
- AttrPutN and AttrPutS: These functions assign a value to a numeric or string element attribute.
- CellGetN, CellGetS, CellPutN, and CellPutS: These functions retrieve (or update) values from (or to) a numeric or string cube cell.
- CellIsUpdateable: This function lets you determine whether a cube cell can be written or not. The function returns 1 if the cell can be written, otherwise it returns 0.
- DimensionElementInsert and DimensionElementDelete: These functions add or delete an element to or from a dimension.
- DimensionElementComponentAdd and DimensionElementComponentDelete: These functions will add or delete a component (child) to or from a consolidated element. You cannot use this function in the Epilog procedure of a TI process.
- ExecuteProcess: This function lets you execute a TI process from another process.
- ItemSkip: This function forces a process to skip the current data source item.
- ItemReject: This function rejects a source record and places it in the error log, along with a specified error message.
- If and While: The If statement allows a process to execute a statement or series of statements when a given expression is true, and while allows a process to repeat a series of statements when a given condition is true.
- ProcessQuit: This function terminates a TI process.
- SecurityRefresh: This function reads all the security control cubes and regenerates the internal structures in the server that are used by TM1 API functions.
- SaveDataAll: This function saves all TM1 data from the server memory to disk and restarts the log file.
- SubsetCreate and SubsetDestroy: These functions create or destroy a public subset of a specified dimension.
- ViewCreate and ViewDestroy: These functions create or destroy a view of a specified cube.
Cube logging
TM1 can keep a record of all data changes made to a cube. If the server stops unexpectedly, TM1 can use this logged record of changes to recover upon startup.
The logging element indicates whether logging is enabled for a given cube. When cube logging is enabled, the value of this property is Yes, otherwise the value is No.
By default, TM1 logs transactions to all cubes which are loaded on the server.