TM1 Tutorials.com

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

 

Syntax:

WITH

[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.

WITH

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

SET [Week Set] AS  ‘{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Week].[?pWeek?]},ALL,RECURSIVE)}, 0)}’

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

Syntax:

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


Categorised as: Code Sample - Downloadable, IBM Cognos Express, IBM Cognos TM1, TM1 10.1, TM1 10.2, Training Material


4 Comments

  1. Laurent says:

    Nice post, tank you

  2. Kimmo says:

    Hi,

    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?

  4. jessica says:

    good information about steps needed to establish an ODBO connection in order to transfer data between 2 cubes in different server instances in cognos TM1

Leave a Reply

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