February 19, 2010 at 5:53 pm
Hello…
I’m struggling with this MDX query for I just can’t see to get it correct with a good execution time…
What I’m trying to do… Is find all the children of a parent based on a value in the parent dimension. In this case the Attribute is RefID and the value is 274.
For Example…
To Get the Parent Record I can do something like this…
SELECT
EXISTS(
[Customer].[CustomerID].[CustomerID],
[Customer].[RefID].&[274]
) ON 0
FROM [CashManagement]
Which Returns (the correct value of 14)
14
(null)
To Get the Children on “14” I can do this…
SELECT
([Customer].[Customers].&[14].CHILDREN) ON 0
FROM
[CashManagement]
Both work good alone but when I try to use them together; I end up with this…
I have to use them together for I only have the [Customer].[RefID] value of "274" at runtime.
SELECT
FILTER(
Descendants([Customer].[Customers].MEMBERS),
IsLeaf([Customer].[Customers].CurrentMember)
) ON 0
FROM
(
SELECT
([Customer].[Customers].CURRENTMEMBER.PARENT) ON 0
FROM
[CashManagement]
WHERE
EXISTS([Customer].[CustomerID].[CustomerID].MEMBERS , [Customer].[RefID].&[274])
)
And the end result is the correct results but a horrible execution time of 12 seconds… so I know I must be overlooking something or heading in the wrong direction…
Anyone got any advice? tips?
February 21, 2010 at 8:14 pm
The following is intended to get you started. You really need to have a hierarchy defined in your Customer dimension to get this to work. Once you have a hierarchy, analysis services has a whole heap of functions that work really well and fairly intuitively
To get the parent of a dimension member, try something like ....
SELECT
{ [Customer].[CustHierarchy].[RefID].&[274].Parent} ON 0
FROM [CashManagement]
If you want to show all of the child of this parent, try
SELECT
{ [Customer].[CustHierarchy].[RefID].&[274].Parent.Children} ON 0
FROM [CashManagement]
And if you want to include the parent
SELECT
{ [Customer].[CustHierarchy].[RefID].&[274].Parent, [Customer].[CustHierarchy].[RefID].&[274].Parent.Children} ON 0
FROM [CashManagement]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply