TM1 Forum

Posted: **Mon Jun 25, 2018 6:23 pm**

Hi guys,

In my model I have several process that run data from a database to cubes.

But before that I have to use viewzeroout to clean the period that is going to receive the data.

I am trying to create the view on prolog, then use the view zero out and after that destroy it.

The problem is when I use the command "SubsetElementInsert". As I have the dimension of operation, I would like to select all N elements of this dimension and using this command I understand that I have to put it one by one.

There is another way to do that which not use a fixed view of the cube?

Thanks in advance.

JR

Posted: **Mon Jun 25, 2018 7:28 pm**

If you already have an All N subset, assign that to your view to be zeroed out. Alternatively, create dynamic subset for All N using SubsetCreatebyMDX. You should be able to find quite a few examples with a quick search - don't forget to set the last parameter to avoid an error on an empty subset.

Posted: **Mon Jun 25, 2018 7:36 pm**

Hi, when you say "assign that to your view to be zeroed out", I must have to do that using the "ViewSubsetAssign"?

Thanks a lot.

gtonkin wrote: ↑Mon Jun 25, 2018 7:28 pm If you already have an All N subset, assign that to your view to be zeroed out. Alternatively, create dynamic subset for All N using SubsetCreatebyMDX. You should be able to find quite a few examples with a quick search - don't forget to set the last parameter to avoid an error on an empty subset.

Posted: **Mon Jun 25, 2018 7:45 pm**

Yes, that is correct. You need to create a view if you do not have one then for each dimension, assign the relevant subset to ensure that you zero out only what you intend to. Make sure that you know what is going to be zeroed.
If you want to check your view, hash out the ZeroOut or add a ProcessQuit after your SubsetAssigns.
You can then right-click the View name linked to the cube and select the export option. This shows the dimensions and subsets linked so that you can confirm.

Posted: **Mon Jun 25, 2018 8:53 pm**

gtonkin wrote: ↑Mon Jun 25, 2018 7:28 pm If you already have an All N subset, assign that to your view to be zeroed out. Alternatively, create dynamic subset for All N using SubsetCreatebyMDX. You should be able to find quite a few examples with a quick search - don't forget to set the last parameter to avoid an error on an empty subset.

No.
If you want to clear data on all leaves of a dimension then don't assign a subset to this dimension at all. You should only assign filters where there is a need to restrict the view. Data is only held against leaves so when doing a zero out there is categorically never a need to create or assign a subset of all leaf elements to the view.

Posted: **Mon Jun 25, 2018 9:37 pm**

lotsaram wrote: ↑Mon Jun 25, 2018 8:53 pmNo.
If you want to clear data on all leaves of a dimension then don't assign a subset to this dimension at all. You should only assign filters where there is a need to restrict the view. Data is only held against leaves so when doing a zero out there is categorically never a need to create or assign a subset of all leaf elements to the view.

My emphasis added above.

Your answer is correct from a technical point of view. However, 1 reason for writing out the SubsetCreateByMDXs for N-level elements too, could be:
It's more clear what the dimensions of the cube are. It creates 'context'. One is less likely to 'forget' a selection/dimension. If, after some time, the process needs 1 filter more (or less), it will be easier for the person in charge, as the code is almost there for the dimension at hand. If the customer needs to make the change in the code, I am confident that this will be a bit easier than compared to the case where one needs to add SubsetDestroy, ViewSubsetAssign, SubsetCreate/ByMDX, SubsetElementInsert, SubsetDestroy again, and so on. (Temporary objects could reduce the coding). It is said that this kind of writing out the selections is more clear when you're not a seasoned TM1 developer.

YMMV and of course this is personal preference, Bedrock notably being in favour of less coding in the main processes (with a lot of code in the auxiliary processes).

Another reason could be that code for zero out selections and code for data source views can be copied easier towards each other, if they also contain the dimensions that are restricted to N-level. The code looks similar. Again, this is in the absence of generic processes such as Bedrock.

Posted: **Tue Jun 26, 2018 12:09 pm**

Even when you have new elements being added to the dimension?

I have created a dynamic subset for level n elements to be sure that the process will clean all the data.

Is that also wrong?

Thanks for helping.

JR.

lotsaram wrote: ↑Mon Jun 25, 2018 8:53 pm
gtonkin wrote: ↑Mon Jun 25, 2018 7:28 pm If you already have an All N subset, assign that to your view to be zeroed out. Alternatively, create dynamic subset for All N using SubsetCreatebyMDX. You should be able to find quite a few examples with a quick search - don't forget to set the last parameter to avoid an error on an empty subset.
No.
If you want to clear data on all leaves of a dimension then don't assign a subset to this dimension at all. You should only assign filters where there is a need to restrict the view. Data is only held against leaves so when doing a zero out there is categorically never a need to create or assign a subset of all leaf elements to the view.

Posted: **Tue Jun 26, 2018 12:55 pm**

Even with new elements you are not forced to create a (dynamic) subset. TM1 will default to all lowest level elements (cells).

Posted: **Tue Jun 26, 2018 3:14 pm**

Good to know Wim.
So on ViewSubsetAssign I can only put the default subset of dimension and it will pick all n elements?
Thanks a lot,
JR

Wim Gielis wrote: ↑Tue Jun 26, 2018 12:55 pm Even with new elements you are not forced to create a (dynamic) subset. TM1 will default to all lowest level elements (cells).

Posted: **Tue Jun 26, 2018 3:29 pm**

Jorge Rachid wrote: ↑Tue Jun 26, 2018 3:14 pm So on ViewSubsetAssign I can only put the default subset of dimension and it will pick all n elements?
Thanks a lot,
JR

No, that's not what he is saying. If you create a view for the purpose of zeroing out AND you do not specifically assign a subset for a dimension in the cube AND Skip Consolidations is turned on (the default setting), the view will default to all the current leaf members in that dimension.

Posted: **Tue Jun 26, 2018 3:58 pm**

Ok, I understand.
I will try now and if I have any doubts I post here.
Thanks a lot,
JR

tomok wrote: ↑Tue Jun 26, 2018 3:29 pm
Jorge Rachid wrote: ↑Tue Jun 26, 2018 3:14 pm So on ViewSubsetAssign I can only put the default subset of dimension and it will pick all n elements?
Thanks a lot,
JR
No, that's not what he is saying. If you create a view for the purpose of zeroing out AND you do not specifically assign a subset for a dimension in the cube AND Skip Consolidations is turned on (the default setting), the view will default to all the current leaf members in that dimension.

Posted: **Tue Jun 26, 2018 5:40 pm**

Hi, I am still in doubt:
How am I going to create the view on prolog without using the ViewSubsetAssign? In my case am using it when I have to filter specific elements but also when I want all elements of dimension (here is the point):
Find below the code:

##----------TM1 Parameters------------##
pCube = 'FC.020.Base_CRK_Renovacao';
pDim1 = 'versao';
pVersao = 'Realizado';
pDim2 = 'periodo_fluxo_caixa';
pPeriodo = vPeriodoAtual;
pDim3 = 'numero_operacao_fc';
sSubsetDim3 = 'Default';
pDim4 = 'fc.m.020.base_crk_renovacao';
sSubsetDim4 = 'Default';

##--------Creating view---------------##
sView = 'Limpeza';
sSubset = sView;

If (ViewExists(pCube, sView) = 1);
ViewDestroy(pCube, sView);
Endif;
ViewCreate(pCube, sView);

If (SubsetExists(pDim1, sSubset) = 1);
SubsetDeleteAllElements(pDim1, sSubset);
Else;
SubsetCreate(pDim1, sSubset);
Endif;
SubsetElementInsert(pDim1, sSubset, pVersao, 1);

ViewSubsetAssign(pCube, sView, pDim1, sSubset);

If (SubsetExists(pDim2, sSubset) = 1);
SubsetDeleteAllElements(pDim2, sSubset);
Else;
SubsetCreate(pDim2, sSubset);
Endif;
SubsetElementInsert(pDim2, sSubset, pPeriodo, 1);

ViewSubsetAssign(pCube, sView, pDim2, sSubset);

If (SubsetExists(pDim3, sSubset) = 1);
SubsetDeleteAllElements(pDim3, sSubset);
Else;
SubsetCreate(pDim3, sSubset);
Endif;

ViewSubsetAssign(pCube, sView, pDim3, sSubsetDim3);

If (SubsetExists(pDim4, sSubset) = 1);
SubsetDeleteAllElements(pDim4, sSubset);
Else;
SubsetCreate(pDim4, sSubset);
Endif;

ViewSubsetAssign(pCube, sView, pDim4, sSubsetDim4);

#-------------View Zero Out-------------##
ViewZeroOut (pCube, sView);

Tks!

Jorge Rachid wrote: ↑Tue Jun 26, 2018 3:58 pm Ok, I understand.
I will try now and if I have any doubts I post here.
Thanks a lot,
JR

tomok wrote: ↑Tue Jun 26, 2018 3:29 pm
Jorge Rachid wrote: ↑Tue Jun 26, 2018 3:14 pm So on ViewSubsetAssign I can only put the default subset of dimension and it will pick all n elements?
Thanks a lot,
JR
No, that's not what he is saying. If you create a view for the purpose of zeroing out AND you do not specifically assign a subset for a dimension in the cube AND Skip Consolidations is turned on (the default setting), the view will default to all the current leaf members in that dimension.

Posted: **Tue Jun 26, 2018 6:04 pm**

If you only run: ViewCreate(pCube, sView); and nothing further, you will notice that the each of the dimensions references a subset called "All". THis is what Lotsaram was saying about not having to assign subsets if you don't need to. The "All" subsets contain as you expect, all elements, and as Lotsaram mentioned, the zero out clears leaf elements so no need to try filter C levels out.

Where you do need to apply a filter e.g. on a dimension like Period, Version etc., you would need to create a subset with the relevant elements.
This can be done like you have done in some cases by inserting the elements or via MDX - depends on your situation. Once you have created and populated the subsets, you assign them to the view which replaces the default "All" subset.

If you only replace Period and Version with subsets contains only the relevant elements, by default all other dimensions will be cleared across all leaf elements.

If you prefer to be more explicit, like myself and Wim, you may create and assign subsets for all dimensions. I use a child/auxillary process where I pass parameters either as elements, subsets or MDX and it builds the necessary view and zeroes out. Saves a lot of time when creating a new TI.

Personal choice on how you choose to implement. There will be overhead in creating and destroying objects, possible locking issues etc. which may be worth considering before creating a subset for each dimension and allocating it.

As with many things TM1, it all depends on what your are trying to do, the frequency of running, the size of the dimensions and cubes and what you want to leave to those that will take over the model one day.

Posted: **Tue Jun 26, 2018 8:28 pm**

Now I got it.
I have tested and it worked.
Thanks a lot for helping me guys.
Best regards,
JR

gtonkin wrote: ↑Tue Jun 26, 2018 6:04 pm If you only run: ViewCreate(pCube, sView); and nothing further, you will notice that the each of the dimensions references a subset called "All". THis is what Lotsaram was saying about not having to assign subsets if you don't need to. The "All" subsets contain as you expect, all elements, and as Lotsaram mentioned, the zero out clears leaf elements so no need to try filter C levels out.

Where you do need to apply a filter e.g. on a dimension like Period, Version etc., you would need to create a subset with the relevant elements.
This can be done like you have done in some cases by inserting the elements or via MDX - depends on your situation. Once you have created and populated the subsets, you assign them to the view which replaces the default "All" subset.

If you only replace Period and Version with subsets contains only the relevant elements, by default all other dimensions will be cleared across all leaf elements.

If you prefer to be more explicit, like myself and Wim, you may create and assign subsets for all dimensions. I use a child/auxillary process where I pass parameters either as elements, subsets or MDX and it builds the necessary view and zeroes out. Saves a lot of time when creating a new TI.

Personal choice on how you choose to implement. There will be overhead in creating and destroying objects, possible locking issues etc. which may be worth considering before creating a subset for each dimension and allocating it.

As with many things TM1, it all depends on what your are trying to do, the frequency of running, the size of the dimensions and cubes and what you want to leave to those that will take over the model one day.

TM1 Forum

SubsetElementInsert

SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert

Re: SubsetElementInsert