Data on parent in a Unbalanced hierachie ( parent-child)

Question

Hello,

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].&[0]); 
this=SUM(
        Descendants([Dimension Scenario].[Dimension Scenario].currentmember,,leaves)
        );
end scope

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

Vincent

Answer 1

Hi,

this article might be an asnwer on how to ignore non leaf data with a scope statement:

http://www.bidn.com/blogs/MarkGStacey/bidn-blog/324/scope-statement-ignoring-non-leaf-data

Philip,

Answer 2

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.

Vincent

Answer 3

Hello Vincent,

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.

http://pcdimnaturalize.codeplex.com/

http://bidshelper.codeplex.com/wikipage?title=Parent-Child%20Dimension%20Naturalizer

Other reasons why you would flatten you dimension are:

- aggregations can be created on any level of a flattened hierarchy

- only one parent child relationship per dimension is allowed

- sometimes different rollups are needed.

Philip,

Answer 4

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.

Vincent

Answer 5

hi,

I came across yet another interesting article wich used the .datamember function in its illustrations:

http://hccmsbi.blogspot.com/2007/07/data-member-part-2.html

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).

Philip,

Answer 6

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,
[Dim Product].[Leaf].&[0],
[Active Scope].[Is Activate].&[1]);
this=([Dim Product].[Id Parent].currentmember,[Measures].[Value1])-([Dim Product].[Id Parent].currentmember.datamember,[Measures].[Value1]);
end scope; 

and my result is 

Vincent

Answer 7

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,
[Dim Product].[Leaf].&[0]);
this=[Dim Product].[Id Parent].currentmember
-[Dim Product].[Id Parent].currentmember.datamember;
end scope;†

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.

Vincent

Answer 8

hello,

you've scoped the leaf datamember  [Dim Product].[Leaf].&[0] 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].&[0]).parent;
...

try !

Philip,

Answer 9

Thanks

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.

Thanks

Vincent

Answer 10

Sorry didn's see your update i try that

Answer 11

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

For exemple

p1 id=1

--p11 id=11

----p111 id=111

fact table 

id value

1  10

11 10

111 10

with this code i will only change the behavior of 11 and not of 1,