Transfering Data between TM1 Servers using ODBO (i.e. Without CSV’s)

This blog shows the steps needed to establish an ODBO connection in order to  transfer data between 2 cubes in different server instances.

STEP 1 : Connection Details

  1. Select MDX Query from the drop down
  2. Enter details
  • ODBO Provider :IBM Cognos TM1 OLE DB MD Provider
  • ODBO Location: Enter Server name (Adminhost)
  • ODBO Datasource : Enter Instance name
  • ODBO UserID : Enter  username
  • ODBO Password : Enter password














Step 2: Create MDX Query for Dimension subsets




[set name] AS

‘{ MDX statement}’


Eg: The sample below shows the different types of MDX statements and data selection so that the required element lands in the same column for all rows of data.


SET [Department Set] AS  ‘{[Department].[All Departments].children}’


SET [Account Set] AS ‘{[Account].[Profit and Loss].[5000].[5005].[6500].[6505].children}’



Note :

  • pWeek is the parameter name
  • If a dimension has multiple hierarchies, the account display may stagger,so identify the parents for the hierarchy required


Step 3: Create View


SELECT { [Dimension].[Element1],[Dimension].[Element 2] } ON COLUMNS,


[Set 1]*[Set2]*[Set3]


ON ROWS FROM [Source Cube]

WHERE ( [Dimension].[?parametername?],[Dimension].[Element 1])


Eg : The sample below will produce the week, account and department as columns and the data cell containing the data for the Total Amount ($000) element from the Measures General Ledger dimension.

SELECT {[Measures General Ledger].[Total Amount ($000)]} ON COLUMNS,


[Week Set]*[Account Set]*[Department Set]


ON ROWS FROM [General Ledger]

WHERE ( [Year].[?pYear?],[Scenario].[?pScenario?], [Company].[All Companies])


Note :

1. pyear and pscenario are parameter names

2. Data is selected at All Companies

If you like this post, please spread the love…

3 comments on “Transfering Data between TM1 Servers using ODBO (i.e. Without CSV’s)

  1. Laurent says:

    Nice post, tank you

  2. Kimmo says:


    I’ve tested this also, but there is one key challenge. Namely, if there are parallel hierarchies in the dimension, same element in different hierarchies will get different principal name. This means that there is also unnecessary duplicate data in the target cube.

  3. Dongjin says:

    But ODBO Feature only supported on Windows Environment, Not Unix/Linux, Right?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.