How to create dynamic view with the help of MDX?

Post Reply
habibullah.sayyed
Posts: 20
Joined: Mon Jul 29, 2019 12:01 pm
OLAP Product: TM1
Version: v10.x
Excel Version: excel 2016

How to create dynamic view with the help of MDX?

Post by habibullah.sayyed » Thu Aug 29, 2019 5:35 am

Hello Everyone,
I want to make a Dynamic view through MDX, please explain step by step cause I am not expert in this... :)

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: How to create dynamic view with the help of MDX?

Post by orlando » 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

habibullah.sayyed
Posts: 20
Joined: Mon Jul 29, 2019 12:01 pm
OLAP Product: TM1
Version: v10.x
Excel Version: excel 2016

Re: How to create dynamic view with the help of MDX?

Post by habibullah.sayyed » Thu Aug 29, 2019 9:44 am

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

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: How to create dynamic view with the help of MDX?

Post by orlando » Thu Aug 29, 2019 10:36 am

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

Emixam
Posts: 36
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: How to create dynamic view with the help of MDX?

Post by Emixam » Thu Aug 29, 2019 12:39 pm

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
Regular Participant
Posts: 194
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: How to create dynamic view with the help of MDX?

Post by Mark RMBC » Thu Aug 29, 2019 1:47 pm

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
Posts: 6
Joined: Fri Nov 23, 2012 7:11 pm
OLAP Product: TM1
Version: Planning Analytics 2.0.7
Excel Version: Excel 2016 64-bit
Location: Dallas, TX

Re: How to create dynamic view with the help of MDX?

Post by Extirpator » Thu Sep 05, 2019 10:36 pm

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.

Post Reply