Page 1 of 1
How to get dimensions and column values used while generatin
Posted: Thu Mar 06, 2014 10:37 pm
by 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,
Re: How to get dimensions and column values used while gener
Posted: Fri Mar 07, 2014 12:16 am
by winsonlee
select {TM1Subsetall( [Branch] ) } ON COLUMNS, {TM1SUBSETALL( [Dimension2] ) } ON ROWS FROM [mycube]
You cant have column and rows from the same dimension.
Re: How to get dimensions and column values used while gener
Posted: Fri Mar 07, 2014 3:49 am
by 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.
Re: How to get dimensions and column values used while gener
Posted: Fri Mar 07, 2014 9:26 am
by lotsaram
AmeyJoshi wrote: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.
Re: How to get dimensions and column values used while gener
Posted: Thu Apr 03, 2014 10:36 pm
by AmeyJoshi
winsonlee wrote: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!!