How to create dynamic view with the help of MDX?

[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...

[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

[habibullah.sayyed]

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

[orlando]

Hi,

don't have the time for an example code - but here are some links and hints

Best regards
orlando

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

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

[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.

[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

[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.