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
