Exploring TM1 - a Chartertech Company
Close this search box.

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

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In