Insert ALL N element into subset

[hyunjia]

During the process of zero out data in cube, it occurs to me that I can only create a subset of all N element by MDX(dynamic) . Is there other way to include all N element in the subset ? Of course you can loop all element and determine if it's N element, but that's not the way I wanted . I have double-check with the TM1 reference but can't the answer.

Probably this should be a very simple question, thank you

[qml]

Why, are dynamic subsets not to your liking?

The quickest way I can think of would be to:
a) create the dynamic subset using SubsetCreateByMDX();
b) use SubsetElementInsert() to insert a dummy element in that subset to implictly turn it to a static subset;
c) delete the dummy element from the subset.

And voila, you have yourself a static subset defined by whatever criteria you applied in step a.

Is that what you are after?

[David Usherwood]

Since you're trying to zero out a view, set the dimension(s) to 'All' and then set the view to skip 'calculated' (really consolidated) values.

[hyunjia]

Thanks for the prompt reply. It just occur to me during zero out cube, but the really focus is how I can insert all N element in subset , not just for cube clearing. Essential, I have heard so many bad things about MDX subset being slower than the static subset.

Yet, it seems the MDX would be no.1 options here and then probably would need another TI to turn it into a static subset though. Are there other tricks ?

[Wim Gielis]

Probably a simpler idea: if a zero out view contains all leaf level elements in a dimension,
you do not have to specify it at all. That is, SubsetDestroy, SubsetCreateByMDX, ViewSubsetAssign, ...
do not have to be written out explicitly for these kind of dimensions.

[Wim Gielis]

Thanks for the prompt reply. It just occur to me during zero out cube, but the really focus is how I can insert all N element in subset , not just for cube clearing. Essential, I have heard so many bad things about MDX subset being slower than the static subset.

If it's only a zero out in the Prolog tab, I don't think it will matter a lot (dynamic vs. static subset).
What is your concern? Speed, too much code, ... ?

Yet, it seems the MDX would be no.1 options here and then probably would need another TI to turn it into a static subset though. Are there other tricks ?

Did you see Kamil's method? Why would you need a different TI?

[qml]

Yet, it seems the MDX would be no.1 options here and then probably would need another TI to turn it into a static subset though.

The issue with MDX subsets is that they tend to recalculate themselves more often than needed and that this recalculation puts a lock on the dimension. With the solution described by me you only need 1 TI with 3 lines of code to get the desired result with only one recalculation of MDX, so it gives you the best of both worlds, really.

[Wim Gielis]

Why, are dynamic subsets not to your liking?

The quickest way I can think of would be to:
a) create the dynamic subset using SubsetCreateByMDX();
b) use SubsetElementInsert() to insert a dummy element in that subset to implictly turn it to a static subset;
c) delete the dummy element from the subset.

And voila, you have yourself a static subset defined by whatever criteria you applied in step a.

Is that what you are after?

Would a zero out in the Prolog tab be faster using a-b-c versus only a ?
Is it worth coding the subset being static or not?

[qml]

I think you are focusing on the zeroout part too much, Wim. From what I understood the OP was asking for a more generic tip on subsets, not necessarily those used for view zeroouts.

[Wim Gielis]

I think you are focusing on the zeroout part too much, Wim. From what I understood the OP was asking for a more generic tip on subsets, not necessarily those used for view zeroouts.

Ah okay, a more general discussion, then. Fine !

[hyunjia]

Sure, it can be done within a TI , just my expression , it really means another sub routine
And yes, the differences between dynamic and static would relatively the same if I use them mostly in Prolog. Thanks you all , now I can sleep well and not having this problem surrounding me

Thanks for the prompt reply. It just occur to me during zero out cube, but the really focus is how I can insert all N element in subset , not just for cube clearing. Essential, I have heard so many bad things about MDX subset being slower than the static subset.

If it's only a zero out in the Prolog tab, I don't think it will matter a lot (dynamic vs. static subset).
What is your concern? Speed, too much code, ... ?

Yet, it seems the MDX would be no.1 options here and then probably would need another TI to turn it into a static subset though. Are there other tricks ?

Did you see Kamil's method? Why would you need a different TI?

[hyunjia]

Yeah , I just put it into a broader spectrum. But I am very surprise to see so many valuable advice following up. Thank you all for being so supportive.

I think you are focusing on the zeroout part too much, Wim. From what I understood the OP was asking for a more generic tip on subsets, not necessarily those used for view zeroouts.

Ah okay, a more general discussion, then. Fine !

[hyunjia]

I have never heard of this behavior to turn a dynamic subset into static one by SubsetElementInsert() . I think I really should get some hands-on and do my homework . Very appreciate for this one

Why, are dynamic subsets not to your liking?

The quickest way I can think of would be to:
a) create the dynamic subset using SubsetCreateByMDX();
b) use SubsetElementInsert() to insert a dummy element in that subset to implictly turn it to a static subset;
c) delete the dummy element from the subset.

And voila, you have yourself a static subset defined by whatever criteria you applied in step a.

Is that what you are after?

[hyunjia]

I have done my homework and it works. However, it seems dimension could not refresh until the Prolog process completed. Is there any way to work around this problem and put all my code in Prolog. My steps would be :
1. Create a dummy element
2. Create a dynamic subset
3. Put the element into dynamic subset
4. remove it from the subset
The issue would be : I have to manually create a dummy element since the dimension could not be refresh within Prolog.

Why, are dynamic subsets not to your liking?

The quickest way I can think of would be to:
a) create the dynamic subset using SubsetCreateByMDX();
b) use SubsetElementInsert() to insert a dummy element in that subset to implictly turn it to a static subset;
c) delete the dummy element from the subset.

And voila, you have yourself a static subset defined by whatever criteria you applied in step a.

Is that what you are after?

[Steve Rowe]

qml wrote:
Why, are dynamic subsets not to your liking?

The quickest way I can think of would be to:
a) create the dynamic subset using SubsetCreateByMDX();
b) use SubsetElementInsert() to insert a dummy element in that subset to implictly turn it to a static subset;
c) delete the dummy element from the subset.

And voila, you have yourself a static subset defined by whatever criteria you applied in step a.

Is that what you are after?

Fantastic piece of slight of hand Kamil. Thanks for sharing, you should make a post in the Useful forum

@hyunjia.

Not sure why are inserting a dummy element into the dimnesion before you start? I don't think is required.
I just insert the first element in the dimension into the first position in the subset. Then subsetelementdelete is done by index, so just delete the first element of the subset.

Cheers

[Wim Gielis]

Creating a dummy element is not needed. All subset updates are done immediately. No looping in this code.

My test code:

Code: Select all

vDim='Projects';
vSub='test';
vFile='test_subset.cma';

If(SubsetGetSize(vDim,vSub)>0);
  SubsetElementInsert(vDim,vSub,SubsetGetElementName(vDim,vSub,1),1);
  AsciiOutput(vFile,NumberToString(SubsetGetSize(vDim,vSub)));
  SubsetElementDelete(vDim,vSub,1);
  AsciiOutput(vFile,NumberToString(SubsetGetSize(vDim,vSub)));
EndIf;

'test' is a dynamic subset containing only 1 element. So the first AsciiOutput gives 2 as the result, the second AsciiOutput gives 1, as expected.
With this code, 'test' becomes a static subset (losing its MDX expression).

[hyunjia]

That is wonderful. Thanks all . The technique is elegant so smooth.

Creating a dummy element is not needed. All subset updates are done immediately. No looping in this code.

My test code:

Code: Select all

vDim='Projects';
vSub='test';
vFile='test_subset.cma';

If(SubsetGetSize(vDim,vSub)>0);
  SubsetElementInsert(vDim,vSub,SubsetGetElementName(vDim,vSub,1),1);
  AsciiOutput(vFile,NumberToString(SubsetGetSize(vDim,vSub)));
  SubsetElementDelete(vDim,vSub,1);
  AsciiOutput(vFile,NumberToString(SubsetGetSize(vDim,vSub)));
EndIf;

'test' is a dynamic subset containing only 1 element. So the first AsciiOutput gives 2 as the result, the second AsciiOutput gives 1, as expected.
With this code, 'test' becomes a static subset (losing its MDX expression).