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
- Select MDX Query from the drop down
- 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
Nice post, tank you
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.
But ODBO Feature only supported on Windows Environment, Not Unix/Linux, Right?