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

You might also like