view zeroout - subset all n
-
- 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
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,
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,
-
- 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: view zeroout - subset all n
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.
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
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
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
-
- 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
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,
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,
-
- 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: view zeroout - subset all n
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).
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
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
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
- gtonkin
- MVP
- Posts: 1192
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: view zeroout - subset all n
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.
-
- 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: view zeroout - subset all n
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
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
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
-
- 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
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.
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.
-
- 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: view zeroout - subset all n
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.
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
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
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
-
- 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
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,
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,
-
- 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: view zeroout - subset all n
Good news !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,
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
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
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