view zeroout - subset all n

Post Reply
manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

view zeroout - subset all n

Post by manu0521 » Sat Jun 05, 2021 11:00 pm

Hi ,

I am doing a zerout on a view in prolog .

For few dimensions i want to zero out all n elements , should i create a subset with all n elements for these dimensions ?

if so should i do a mdx with all n elements ?

Thanks,

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

Re: view zeroout - subset all n

Post by Wim Gielis » Sat Jun 05, 2021 11:18 pm

That's not necessary. You can skip all dimensions where you take level 0 elements, since by default TM1 will skip consolidated elements.

HOWEVER, it depends on the other dimensions too and the specifications of how you create the view. If you overwrite the default behavior of skipping consolidations and still have them, the view can grow very large if you don't filter to level 0.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: view zeroout - subset all n

Post by manu0521 » Sun Jun 06, 2021 12:21 am

One more question , I am using this to do a extract from tm1 .

I am creating a view where it has some dimensions with consolidated elements. So I dont want my consilidations on some dimension to be extracted , so its better if i do only n elements on those right.

By default i have a cube view as my source and choose the default view , then in prolog i construct a view and assign the data source as view .

I have a very basic question here , my variable tab is filled with variables in order based on my default view ? or the dimension order in cube .What if my default view is changed . Does the order of variables change?

How do I know if my variables order do not change when i change my source view in prolog. Which should be my initial source view.


Thanks,

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

Re: view zeroout - subset all n

Post by Wim Gielis » Sun Jun 06, 2021 2:04 am

I typically create a view called ZZZ_tmp and use that one at design time of the process. It's in the order of the dimensions.
Then I write the process and test. I delete the view again (or leave it there for other processes, depends, if I still can make a use of it).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

User avatar
gtonkin
MVP
Posts: 930
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: view zeroout - subset all n

Post by gtonkin » Sun Jun 06, 2021 9:53 am

manu0521 wrote:
Sun Jun 06, 2021 12:21 am
...
How do I know if my variables order do not change when i change my source view in prolog. Which should be my initial source view.
...
Maybe to elaborate on what Wim said - NEVER use a view that you created by opening and saving the view - you cannot guarantee that the variable order will be the same as when the process creates the actual view. In my experience, hardly ever is.

Rather create the view like Wim said, either in the process or if in Perspectives, right click on the cube, export, create view there. Seen user created views lead to hours of wasted time trying to troubleshoot output issues or failures later due to some other unexpected consequence.

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

Re: view zeroout - subset all n

Post by Wim Gielis » Sun Jun 06, 2021 11:06 am

Come to think about it, I should write a simple TI process or REST API code to generate that view. I have been doing this task too many times manually ! Or REST API code to create a boilerplate process connected to the right cube and all variables on ‘Other’. Variable names without spaces and all that :D
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: view zeroout - subset all n

Post by manu0521 » Mon Jun 07, 2021 2:13 am

Hi ,

If we dont use a view that we created , in my data source tab what should i pick from the cube as cube view ?

In my prolog , I create a view and assign values to dimension and assign it to the view , does this view gets created with the dimension in order of the cube and can it link back to my appropriate values in my TI .

Ex : I have a simple sales cube .

I have a defailt view with customers, products,timeperiod , measures,promotions etc..

so the varaible is in order customer, products,timeperiod,promotions and measure .

Then my cube has dimensions in another order , products, customers,promotions, time period and measure.
I create my view in prolog with above order by assigning dimensions to subset view .

Now when I ascii output with variables name I used in varaible can they remap to my source view order with variable order .

This might be very basic sorry if i am making it complex, just want to make sure nothing should break if i change my default view later.

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

Re: view zeroout - subset all n

Post by Wim Gielis » Mon Jun 07, 2021 7:04 am

To put it in simple terms: a view in a cube is not the same as a view in a TI process.
A view in a TI process does not have a layout. Everything comes in as data with references to dimension elements.
It is through the use of the CellPutN formula or CellIncrementN formula that you will add it to the same or a different cube.
I see that you’re still using the default view. I wonder what you did with my advice to use a dedicated view like ZZZ_tmp.
Did you read and understand that post ? I would also highly recommend a TM1 course.
120 forum posts and joining 7 years ago, I would guess that this knowledge would already be had.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

manu0521
Posts: 124
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: IBM TM1, Planning Analytics
Version: PA 2.0.5
Excel Version: 2016

Re: view zeroout - subset all n

Post by manu0521 » Mon Jun 07, 2021 5:49 pm

Hi Wim ,

I am creating a zz_view in the prolog and assigning to the datasource .I was confused where something has to be initally set on the datasourc tab to the process een we overwrite that on the prolog with a view. So I get that the order of the view now created will be based on dimension order in the cube and all looks okay now .

Thanks for your help .

Thanks,

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

Re: view zeroout - subset all n

Post by Wim Gielis » Mon Jun 07, 2021 6:07 pm

manu0521 wrote:
Mon Jun 07, 2021 5:49 pm
Hi Wim ,

I am creating a zz_view in the prolog and assigning to the datasource .I was confused where something has to be initally set on the datasourc tab to the process een we overwrite that on the prolog with a view. So I get that the order of the view now created will be based on dimension order in the cube and all looks okay now .

Thanks for your help .

Thanks,
Good news !

Here is a simple TI process to create a ZZZ_tmp view and its subsets. There is a pCube string parameter.

Code: Select all

####################################################
# Wim Gielis
# June 2021
# https://www.wimgielis.com
####################################################

# Create a cube view containing only 1 cell (handy for creating data sources in a TI process)
#####

c0 = 'ZZZ_tmp';

vCube = Trim( pCube );
If( CubeExists( vCube ) = 0 );
   ProcessError;
EndIf;


If( ViewExists( vCube, c0 ) = 0 );

   d = 1;
   While( d <= CubeDimensionCountGet( vCube ));
      vDim = Tabdim( vCube, d );

      If( d = 1 );
         ViewCreate( vCube, c0 );
         ViewExtractSkipCalcsSet( vCube, c0, 0 );
         ViewExtractSkipRuleValuesSet( vCube, c0, 0 );
         ViewExtractSkipZeroesSet( vCube, c0, 0 );
         ViewExtractSkipConsolidatedStringsSet( vCube, c0, 0 );
      EndIf;

      If( SubsetExists( vCube, c0 ) = 0 );
         SubsetCreate( vDim, c0 );
         SubsetElementInsert( vDim, c0, Dfrst( vDim ), 1 );
      EndIf;

      ViewSubsetAssign( vCube, c0, vDim, c0 );
      ViewTitleDimensionSet( vCube, c0, vDim );

      d = d + 1;

   End;

EndIf;
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Post Reply