Creating MDX views for the data source to a TI process in PAW

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Creating MDX views for the data source to a TI process in PAW

Post by Wim Gielis »

Hi all,

The title sums it up I guess. For all application cubes, create a view that can be used in the Data source.
It should avoid issues with messed up variable orderings in PAW.

Code: Select all

## Create an MDX view for the data source of TI processes

vColumn_Selections = '';
vRow_Selections = '';

vView = 'ZZZ_tmp';
cMDX_Template_View = 'SELECT ' | vColumn_Selections | ' ON 0, ' | vRow_Selections | ' ON 1 FROM [' | vCube | ']';
cMDX_Template_OneElement = '{[%vDimension%].[%vHierarchy%].[%vElement%]}';

c = 1;
While( c <= Dimsiz( '}Cubes' ));

   vCube = Dimnm( '}Cubes', c );
   If( Subst( vCube, 1, 1 ) @<> '}' );

      # loop over the cube dimensions and add to the MDX view
      vColumn_Selections = '';
      d = 1;
      While( d <= CubeDimensionCountGet( vCube ));
         vDimension = Tabdim( vCube, d );
         vHierarchy = vDimension;
         vElement = ElementFirst( vDimension, vHierarchy );
         If( d < CubeDimensionCountGet( vCube ));
            vColumn_Selections = vColumn_Selections | If( d = 1, '', '*' ) | Expand( cMDX_Template_OneElement );
         Else;
            vRow_Selections = Expand( cMDX_Template_OneElement );
         EndIf;
         d = d + 1;
      End;

      vMDX = Expand( cMDX_Template_View );
      ViewCreateByMDX( vCube, vView, vMDX );

   EndIf;
   c = c + 1;

End;
Last edited by Wim Gielis on Sun Jun 04, 2023 9:10 am, edited 3 times in total.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Creating views for the data source to a TI process in PAW

Post by MarenC »

Hi,

I only ever create views using TM1 cube views, not by MDX. Therefore, the title does not say it all, as it should have included MDX in the title!

I may find this very useful if I ever need to use MDX views as a data source. I will report back if that ever happens!

Maren
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Creating views for the data source to a TI process in PAW

Post by Wim Gielis »

MarenC wrote: Tue Feb 21, 2023 12:22 pm Hi,

I only ever create views using TM1 cube views, not by MDX. Therefore, the title does not say it all, as it should have included MDX in the title!

Maren
A data source in PAW that is a view. Do you have native views in PAW, then ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Creating views for the data source to a TI process in PAW

Post by MarenC »

Hi,

Your title simply says a TI process in PAW, but the view doesn't have to be MDX does it? I presume you can create processes in PAW that behave the same way as cube view data sources in architect? I.e. not using ViewCreateByMDX

Maren
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Creating views for the data source to a TI process in PAW

Post by Wim Gielis »

MarenC wrote: Tue Feb 21, 2023 12:43 pm Hi,

Your title simply says a TI process in PAW, but the view doesn't have to be MDX does it? I presume you can create processes in PAW that behave the same way as cube view data sources in architect? I.e. not using ViewCreateByMDX

Maren
Hello,

I see now that you can still have public native views as data source in PAW.
I thought it was only MDX views. I will change the topic title.
Can we still create a native view in PAW ? Through a TI process yes, but manually ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Creating MDX views for the data source to a TI process in PAW

Post by MarenC »

Hi,

Not sure about PAW but I think in PAX you can Save to Server, as opposed the Content Store.

Maren
Post Reply