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