Import and Export Data between SQL Server and Cognos TM1 Cube

By:   |   Comments (4)   |   Related: > Other Database Platforms


Within the last 15+ years of working as an SQL Server DBA and Developer for small, medium and large corporations the simple task of exchanging data between heterogeneous environments can be a challenge. This tip addresses the topic of transferring SQL Server data to and from IBM Planning Analytics (aka IBM Cognos TM1).


It is not the goal of this tip to explain what "IBM Planning Analytics" is, more information can be found at:  The goal of this tip is to show how to import and export data into an IBM Cognos TM1 data Cube.  For the sake argument, the tip is based on TM1 Version 10.2.0 and I will mainly use TM1 Architect to import and export data to and from SQL Server.  TM1 is an enterprise planning software that can be used to implement budgeting and forecasting solutions, as well as analytical and reporting applications. The main characteristic of IBM Cognos TM1 is that the data is stored solely in memory and it is represented as multidimensional OLAP cubes.

Importing SQL Server data into a TM1 Cube

The import phase is fairly straight forward, because the TM1 Architect interface can talk to an ODBC connector.

Step 1: Create an ODBC Connector

On the server that is running the TM1 server application, create an ODBC connection to connect to your SQL Server. The picture below shows a simple example of an ODBC connection. We will use the ODBC name MSSQL_TM1 to import and export data to SQL Server. It is important that a dedicated SQL Server account is created to establish SQL connectivity and to Read/Write to a SQL Server table.

ODBC Setup
ODBC Setup

Step 2: Create our sample data

Let's set up our sample table and load it with the US state names and abbreviations.

('AK', 'ALASKA'),
('AZ', 'ARIZONA'),
('FL', 'FLORIDA'),
('GA', 'GEORGIA'),
('HI', 'HAWAII'),
('ID', 'IDAHO'),
('IN', 'INDIANA'),
('IA', 'IOWA'),
('KS', 'KANSAS'),
('ME', 'MAINE'),
('MT', 'MONTANA'),
('NV', 'NEVADA'),
('NY', 'NEW YORK'),
('OH', 'OHIO'),
('OR', 'OREGON'),
('TX', 'TEXAS'),
('UT', 'UTAH'),
('VT', 'VERMONT'),
('WY', 'WYOMING');

Step 3: Create our TM1 process to load our data

IBM Cognos TM1 uses a tool called Turbo Integrator or TI, to load data from an ODBC data source.  To load our sample set of data do the following:

Start the IBM Cognos TM1 Architect application and click on "Create New Process".

Create a new Turbo Integrator Process

In this initial phase, we must:

  1. Select an ODBC connection as our data source
  2. Indicate the ODBC data source name (MSSQL_TM1)
  3. Enter our SQL Server TM1 user and password
  4. Type our select query inside the Query window

Click on "Preview" and TM1 will use the information in the ODBC connection, query and retrieve a few sample rows of data from our sample TM1STATE table.

Input Source Screen

The Turbo Integrator ETL tool has a variable Tab where each column from our input query is mapped to a variable. By default, the table column names became the TM1 variable names, but they can be always changed to reflect your naming convention.

TM1 Input variables Section

At this point, we are ready to write our TM1 ETL code using Turbo Integrator. It is not the goal of this tip to explain the Turbo Integrator commands and functions, but it is necessary to do a little overview. Turbo Integrator code is written in tabs: Parameters, Prolog, Metadata, Data and Epilog. The ETL process is executed in the same order; first Prolog code is executed followed by Metadata and then Data code. The Epilog code is executed as the last step of the entire ETL process.

TM1 Advance Tabs

Parameters: This is used to define input parameters that can be use by the source query WHERE clause

Prolog: Code in the prolog is executed one time only. During this initialization phase, TM1 opens the connection to the data source and retrieves the data.

Metadata: For each data record of the data source, the Metadata section is executed. Its execution starts with the population of the data variables. The variables are populated with the value in the corresponding column of the actual record and then the code of the Metadata is executed. In our example, the Metadata code is executed as many times as we have records present in the TM1STATE table.

Data: It works like the Metadata section. The data section is usually used to populate a Cube with Dimensions, Measures and Attributes values.

Epilog: The code in the Epilog section is executed only once when the ETL process is completed and usually cleans up open TM1 objects and closes the connection to the data source.

The Turbo Integrator process is started when the user clicks on the "Run" button.

Start Turbo Integrator Process

Exporting TM1 data to SQL Server

At present, TM1 Version 10.2.0 release, there is not a GUI based interface that allows us to export data to SQL Server. The only commands available are: odbcopen, odbcoutput and odbcclose.

The process of exporting data is very similar to the process of importing data. The first step is to start the IBM Cognos TM1 Architect application, "Create New Process" and click directly on the Advanced tab.

TM1 Advance Tabs

Prolog: In the prolog section, we open the connection to our data source via ODBC. In our example the syntax will be something like this: odbcopen('MSSQL_TM1','TM1SQLUser','password');

Metadata: This section can be skipped because no export code is needed here.

Data: In the data section, were all the cube source records are examined one by one, we place our odbcoutput to write to our SQL Server.

Odbcoutput is very flexible and allows us to insert data into a table or to execute a stored procedure. For example, if our Test Cube has only two dimensions called 'State Code' and 'State Name'.

TM1 Sample Cube View

Our command to export data present in the dimension will be like:

SQLStmInsertODBC= 'insert into dbo.TM1STATE (STATE_CODE, STATE_NAME) (' | '''' | STATE_CODE | '''' | ',' | '''' | STATE_NAME '''' | ')';
odbcoutput('MSSQL_TM1', SQLStmInsertODBC);

Where MSSQL_TM1 is our ODBC Connection and SQLStmInsetODBS is a string that contains our T-SQL Statements.

For each data record in the Cube Dimension, TM1 invokes the odbcoutput command passing STATE_CODE and STATE_NAME as input parameters.

Below is a simple SQL Profiler view of the odbcoutput command results.

SQL Profiler

Epilog: In the Epilog tab, we close the connection with our data source by executing odbcclose('MSSQL_TM1');


Even if TM1 offers only 3 ODBC functions (odbcopen, odbcoutput and odbcclose), it is possible to create functionality to fully integrate and exchange data within heterogeneous systems if an ODBC connector is available.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 10, 2021 - 10:07:35 AM - John Fredy Cortes Triana Back To Top (88839)
Hi, how to deal with primary keys into database, is not null value, but how can I get an incremental number that not exists into my TM1 cube?.

Wednesday, October 7, 2020 - 10:33:05 AM - Matteo Lorini Back To Top (86618)
Sorry, I do not know how to encrypt communication between TM1 and SQL. It looks like a question for the Network group ...

Wednesday, October 7, 2020 - 8:35:41 AM - Cormac Roddy Back To Top (86617)

we have had a request from our IT dept to encrypt the OCBC connection between SQL & TM1.

any ideas how we can do this?


Thursday, February 27, 2020 - 4:17:42 PM - mike Back To Top (84826)

Hi Matteo,

What if your cube has fewer dimensions than columns in the database? Say I want to select 10 dimensions from the Cube, this is all the dimensions from the cube. Then I want to write those to a table which has 20 total columns, 10 will be populated from Cube dimensions, but 10 are going to be sourced from different tables in the database? Is it possible to write all of that into 1 new table?

get free sql tips
agree to terms