Code snippet outlining approach to MDX view construction

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2233
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Code snippet outlining approach to MDX view construction

Post by Steve Rowe » Tue Oct 12, 2021 11:40 am

I find that if I try and write a MDX view statement in a single pass it is very challenging to get the syntax correct.

I use a TI process to construct my MDX statement so that it is in small pieces I can individually test. Something like the following, note heavy use of the expand function which you may need to read up on.

Snipped out of a longer piece of code so it may not be totally complete, used to create datasources for TIs rather than user facing views so may need to be adjusted for that.

Code: Select all

sCubeName='Your Cube';
sMDXL0='{TM1FILTERBYLEVEL( {TM1SUBSETALL( [%sDimName%] )}, 0)}';
booTempOn=0;
sObjectName='WhatYouPlease';

#Build the view for use as the Source
#General form of a MDX cube query against a 5d cube, known to be correct.  MDX for each dimension substituted at runtime

sMDXToUse='SELECT  {%sMDX5%}  ON 0,
%sFilterHead%
NONEMPTY(
	CROSSJOIN(%sMDX1%,
    	CROSSJOIN(%sMDX2%,
    		CROSSJOIN(%sMDX3%,
			%sMDX4%))),
    {%sMDX5%}
    %sFIlterEnd% ) ON 1 
 
FROM [%sCubeName%]
';

#Set-up the MDX expression for each subset, each one of these can be tested in set editor for accruacy
#though in this case it is heavily abstracted.  Uncomment the asciioutput

sDimName=TabDim(sCubeName ,1);
sMDX1=Expand(sMDXL0);
sDimName=TabDim(sCubeName ,2);
sMDX2=Expand(sMDXL0);
sDimName=TabDim(sCubeName ,3);
sMDX3=Expand(sMDXL0);
sDimName=TabDim(sCubeName ,4);
sMDX4=Expand(sMDXL0);
sDimName=TabDim(sCubeName ,5);
sMDX5=Expand(Expand('{Filter(%sMDXL0% , [%sDimName%].[Test Measure]=1)}'));

#Asciioutput('mdx.cma' , sMDX1, sMDX2, sMDX3, sMDX4, sMDX5);

#Set up the filter, leave these values blank / empty if you don't want to filter your view
   sFIlterHead= 'Filter(';
   sFilterEnd=Expand('),[DimName].[Hierarchy].[Element]="%sTest%" ');

If (ViewExists(sCubeName , sObjectName)=1);
    ViewDestroy(sCubeName , sObjectName);
EndIf;

sMDX=Expand(sMDXToUse);

ViewCreateByMDX(sCubeName , sObjectName, sMDX, booTempOn);
Technical Director
www.infocat.co.uk

Post Reply