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: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Code snippet outlining approach to MDX view construction

Post by Steve Rowe »

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