Page 1 of 1
How to create dynamic view with the help of MDX?
Posted: Thu Aug 29, 2019 5:35 am
by habibullah.sayyed
Hello Everyone,
I want to make a Dynamic view through MDX, please explain step by step cause I am not expert in this...
Re: How to create dynamic view with the help of MDX?
Posted: Thu Aug 29, 2019 7:16 am
by orlando
Hi,
what did you tried until now to make a dynamic view?
Where will you use this view later?
Where does the parameter come from, to make it dynamic?
Best regards
orlando
Re: How to create dynamic view with the help of MDX?
Posted: Thu Aug 29, 2019 9:44 am
by habibullah.sayyed
orlando wrote: ↑Thu Aug 29, 2019 7:16 am
Hi,
what did you tried until now to make a dynamic view?
Where will you use this view later?
Where does the parameter come from, to make it dynamic?
Best regards
orlando
I did not tried yet but I know from where to get MDX code(get from Expression Window).
I will be using this on the views which are getting corrupted.
And the parameter is coming within the cube.
Best Regards,
Habib
Re: How to create dynamic view with the help of MDX?
Posted: Thu Aug 29, 2019 10:36 am
by orlando
Hi,
don't have the time for an example code - but here are some links and hints
Best regards
orlando
habibullah.sayyed wrote: ↑Thu Aug 29, 2019 9:44 am
I did not tried yet but I know from where to get MDX code(get from Expression Window).
You mean the MDX Expression Window of a subset?
Be aware that the MDX produced there might not work in Turbointegrator
habibullah.sayyed wrote: ↑Thu Aug 29, 2019 9:44 am
I will be using this on the views which are getting corrupted.
you should read:
https://www.ibm.com/support/knowledgece ... ons_N728E8
and:
https://www.ibm.com/support/knowledgece ... ons_N72463
and:
https://www.ibm.com/support/knowledgece ... ons_N7121E
keep an eye on
ViewCreate
SubsetCreateByMDX
ViewSubsetAssign
CellGetN
CellGetS
Re: How to create dynamic view with the help of MDX?
Posted: Thu Aug 29, 2019 12:39 pm
by Emixam
Hi,
If you want to create a dynamic view and use it as a source in a TI you should do something like this:
Code: Select all
#==============================
# Other Subset : Static
#==============================
#...
#==============================
# Account : MDX
#==============================
sDimAccount = 'GL Account';
sTopAccount = CellGetS( sLookUpCube, ..., ..., ...);
SubsetCreatebyMDX( sSubName, '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[' | sDimAccount | '].[' | sTopAccount | ' ]}, ALL, RECURSIVE )}, 0)}' );
ViewSubsetAssign( sSourceCube, sViewName, sDimAccount, sSubName );
#==============================
# Setup Source Data - VIEW
#==============================
DATASOURCENAMEFORSERVER = sSourceCube;
DATASOURCETYPE ='View';
DATASOURCECUBEVIEW = sViewName;
#==============================
# View Extract
#==============================
ViewExtractSkipRuleValuesSet( sSourceCube, sViewName, 1 );
ViewExtractSkipCalcsSet( sSourceCube, sViewName, 1 );
ViewExtractSkipZeroesSet( sSourceCube, sViewName, 1);
We are using a MDX expression to retrieve all Level 0 elements under a consolidated element store in a sLookUpCube.
Re: How to create dynamic view with the help of MDX?
Posted: Thu Aug 29, 2019 1:47 pm
by Mark RMBC
I am sure someone will say it would then be a good idea to make the subset(s) static.
But I haven't yet seen where that makes anything run quicker!
No sure how most people create views in TM1 but I just have one create view process which uses the expand and tabdim functions
and creates a view for any given cube based on mdx for each dimension.
regards, Mark
Re: How to create dynamic view with the help of MDX?
Posted: Thu Sep 05, 2019 10:36 pm
by Extirpator
It might help to understand if you're working with TI process, a Planning Analytics Workspace (PAW) view, a dynamic report in Planning Analytics for Excel (PAX), or somewhere else.
For better or for worse, I have created quite a few dynamic views in PAW and PAX over the past week and can probably help.