How to get dimensions and column values used while generatin

[AmeyJoshi]

Hi Experts,

One of our TM1 architecture tool users creates '.cma' file by clicking on export dimension. Now we have to get same data by running mdx query in TM1 through ETL Tool - DataStage. What columns and rows to select in mdx query?

For eg:- Dimension is Branch.
Will the query be ... select {[Branch].members} on columns,{[Branch].members} on rows from mycube...?

Appreciate all help!!

Thanks in Advance,

[winsonlee]

select {TM1Subsetall( [Branch] ) } ON COLUMNS, {TM1SUBSETALL( [Dimension2] ) } ON ROWS FROM [mycube]

You cant have column and rows from the same dimension.

[winsonlee]

You can try the mdx on control cube , eg }ElementAttributes_Branch.
This will give you the list of elements in the dimension and also the attributes that attach to the dimension.

[lotsaram]

One of our TM1 architecture tool users creates '.cma' file by clicking on export dimension. Now we have to get same data by running mdx query in TM1 through ETL Tool - DataStage. What columns and rows to select in mdx query?

You may well be searching up a dead end.
A cma file contains the complete dimension structure of leaf members into nodes/hierarchies. That is it encodes the parent-child relationships (including hierarchy weighting). This information is not available in any cubes (unless you were to create these cubes yourself). Such information is however readily available with dimension information function cells from WITHIN TM1 itself. TM1 also has a quite capable script based ETL tool included which could export such information to a flat file (in a much better format than a cma file for consumption by another tool or DB). If you need to extract such information from TM1 I believe you would be much better off coding this internally within TM1 using turbointegrator script.

[AmeyJoshi]

You can try the mdx on control cube , eg }ElementAttributes_Branch.
This will give you the list of elements in the dimension and also the attributes that attach to the dimension.

Sorry for the late reply. I have got the attributes values for Branch dimension through }ElementAttributes_Branch.
Now how can I add attribute name in mdx query?

Attribute names for }ElementAttributes_Branch---> Lvl_2_type, Name

Query which I tried:-> (which failed )

Code: Select all

select  {[TIME].[2013]}  on columns, {TM1SubsetAll ([Branch].[Lvl_2_Type]} on rows from mycube;

Appreciate all your help!!
Thanks in Advance!!