Page 1 of 1
Insert ALL N element into subset
Posted: Mon Oct 08, 2012 1:25 pm
by 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
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:10 pm
by 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?
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:13 pm
by 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.
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:21 pm
by 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 ?
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:22 pm
by 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.
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:25 pm
by Wim Gielis
hyunjia wrote: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, ... ?
hyunjia wrote: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?
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:27 pm
by qml
hyunjia wrote: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.
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:29 pm
by Wim Gielis
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?
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?
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:37 pm
by 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.
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:39 pm
by Wim Gielis
qml wrote: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 !
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:42 pm
by 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
Wim Gielis wrote:hyunjia wrote: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, ... ?
hyunjia wrote: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?
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:46 pm
by 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.
Wim Gielis wrote:qml wrote: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 !
Re: Insert ALL N element into subset
Posted: Mon Oct 08, 2012 2:50 pm
by 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
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?
Re: Insert ALL N element into subset
Posted: Tue Oct 09, 2012 2:28 am
by 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.
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?
Re: Insert ALL N element into subset
Posted: Tue Oct 09, 2012 3:44 am
by 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
Re: Insert ALL N element into subset
Posted: Tue Oct 09, 2012 8:14 am
by 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).
Re: Insert ALL N element into subset
Posted: Tue Oct 09, 2012 11:39 am
by hyunjia
That is wonderful. Thanks all . The technique is elegant so smooth.
Wim Gielis wrote: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).