Wednesday, April 25, 2012 3:39 PM
I have a stupid question normally i handle that with the ETL but on that case my manager don't want any ETL. So i have parent-child dimension. On some parent by error i have data, i don't want to use the data on the parent level, only use the data of the leaves to rollup. So my first thought was to scope the behavior, but with big rollup it's slow. So does it's a properties somewhere to block the data on the parent level. The things is i cannot calculate a leaf level because the tree is unbalanced.
My code was like that
scope([Dimension Scenario].[Dimension Scenario].members,
[Dimension Scenario].[Is Leaf].&);
Descendants([Dimension Scenario].[Dimension Scenario].currentmember,,leaves)
But it's slow and i have unary operator on each member so i also build the code to take care of unary i worried that it will be really slow.
All the best
Wednesday, April 25, 2012 4:39 PM
this article might be an asnwer on how to ignore non leaf data with a scope statement:
Wednesday, April 25, 2012 5:28 PM
Thanks very much, but he do the same things as me meaning re-aggrate by ignoring non-leaf value. The thing is you kill the unary operator with that solution,it's where i am stuck.
Thursday, April 26, 2012 10:18 AM
I find you case really interresting.
The following article explains why out of the box, you can only hide the value of non leaf data, but not let them being ignored in aggregation. It seems like the work-around's comes with a performance penalty: http://bimatters1403.wordpress.com/
If you don't succeed to boost performance to an acceptable level, you might consider working with normal hierarchies. If so, you could use the Parent-Child Dimension Naturalizer of the BIDS Helper that will do the initial hard job for you.
Other reasons why you would flatten you dimension are:
Thursday, April 26, 2012 12:42 PM
Hello, Thanks really much for your answer. I am totally agree with you to consider to user normal hierarchie, and for exemple use the Parent-child naturalizer.But my case is a little bite annoying in fact i create a software who create cube on the fly for a financial application. The source model can change at any moment ( Add dimension, Delete, Rename) and the software keep synchronize them. And the size of the hierarchie change all the time, some member who was under other move to another....so that why my first thought was to use parent child hierachie because even if you change a member to another ou or change the number of level of this hierarchie the CubeGenerator only send a process update.The things is for another client i already help to resolve this kind of issue, and i was using a factorization of the minus and plus of the unary operator to resolve the point,performance was not really good but it was working. Unlucky me i have no remember how to do that.
Friday, April 27, 2012 9:35 AM
I came across yet another interesting article wich used the .datamember function in its illustrations:
msdn defines it as returning the system-generated data member that is associated with a nonleaf member of a dimension, but a note would suggest that a nonleaf membership test might be required in your case.
If you could scope the datamembers of "[Dimension Scenario].[Dimension Scenario]" to zero, this would achieve the same without re-aggregating the whole hierarchy and maybe could perform better.
If you've remembered you factorization method, can you please share it with me as it still intrigues me.
Maybe you have multiplications in you unary operators. You might look whether you should scope to the multiplication neutral element (1) in stead of the sum neutral element (0).
Friday, April 27, 2012 3:46 PM
Hi thanks very much it's working.
I will try to find the factorisation things, i remember it was working with flat dimension not parent-child.I make a post to try to find with the help of the msdn community, how to do that.
I use this MDX code
scope([Dim Product].[Id Parent].members,
[Active Scope].[Is Activate].&);
this=([Dim Product].[Id Parent].currentmember,[Measures].[Value1])-([Dim Product].[Id Parent].currentmember.datamember,[Measures].[Value1]);
and my result is
Friday, May 11, 2012 2:12 PM
Hello, i was trick by the unary operator and in fact it's not working that good.
So i h ave hierarchy with the member p13 =10
p131 and p132 are bot children of p13 and their value is 10 each.
So i didn't make any script and just use this query
So the value of p13minusnonleaf is correct, i try to reproduce that with a scope statement
scope([Dim Product].[Id Parent].members,
this=[Dim Product].[Id Parent].currentmember
-[Dim Product].[Id Parent].currentmember.datamember;
But the result is incorrect
Because p13 don't have a minus with him own value. And the most strange thing it's working for the†[Dim Product].[Id Parent].[p13].datamember because the result is 0 so it's show that the engine make a minus himself.
If somebody have any idea, i try the solution upper with a re-calculation of the descendant but it's too heavy my cube are use as a back-end for a software and receive a lot of different query i a short time, so i try to have good performance. All the idea are welcome thanks in advance.
Friday, May 11, 2012 3:19 PM
you've scoped the leaf datamember [Dim Product].[Leaf].& instead of the non leaf member.
try to use exists() with ".parent" or ".item(0).parent" without exists() in the hope it will return the non data member
scope exists([Dim Product].[Id Parent].members, [Dim Product].[Leaf].&).parent; ...
Friday, May 11, 2012 6:00 PM
When i use the exists its give me an error
An arbitrary shape of the sets is not allowed in the current context.
MdxScript(TEST ROLLUP) (15, 1) An arbitrary shape of the sets is not allowed in the current context.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(TEST ROLLUP) (19, 1) The END SCOPE statement does not match the opening SCOPE statement.
Why do you want to scope the leaves data ? in fact what i want to do is ignore the value insert in a non-leaf member, That why i scope all the non-leaf member and substract their own value from the sum of their children.
Friday, May 11, 2012 6:00 PMSorry didn's see your update i try that
Friday, May 11, 2012 6:11 PM
Hi In my dimension i have an attribut Leaf and it's 0 if it's not a leaf and 1 if it's a leaf. If i scope only the parent of a leaf i will don't have the parent of a non-leaf so the result will be false
with this code i will only change the behavior of 11 and not of 1,