MDX Optimization Question

Question

MDX Optimization Question

awood-935115

SSC Enthusiast

Points: 116
More actions
December 18, 2008 at 7:53 am

#216724

have been struggling with this for most of the day, and read a ton of articles about how to do it. I've finally found a "solution" that gets the right numbers, but the performance of it is horrible. 🙂
My intent is to answer the question:
For all the boxes that contain a large box (parent-child hierarchy), show me the number of large boxes in use or recently used, the number of large boxes available and the ratio of boxes in use to boxes available.
Ideally, I want the 3 measures to become "caluclated members" at the cube level and usable in MDX.
The following query has some performance issues, along with the issue of not being able to support "multi-select" with the where clause due to the use of Descendants. I've tried to re-write it with Exists / EXISTING, but to my chagrin, the "upper levels" of the hierarchy have the [Box Type Code].[LARGEBOX] attribute since they contain a large box at some point in their lower levels. This causes duplicate counts to occur (and took me forever to track down!)
I'm using MDX Studio from Mosha's page (thank you!), but I've gotten as far as I can go with it. 🙁 It's complaining about subexpressions (how do I reduce them? I've tried a named set in the calculation, but that didn't seem to help). It recommends changing Count(Filter(...)) to Sum with a Summator. I read the article on that, but I'm still a bit unclear as to what that is doing or how to work it into the below script.
I'm new to MDX, so I was hoping that some MDX experts could look at this and point me in the right direction.
Dimensions:
Box has attributes [Box Type Code] = { ENORMOUSBOX, HUGEBOX, LARGEBOX, SMALLBOX, TINYBOX }
[Box Hierarchy] = Parent Child Relationship of Boxes
Usage Status has attributes [Status Code] = { INUSE, EMPTY, RECENTUSE, CLOSED }
Usage by Boxes is a simple Fact Table that contains [Box], [Usage Status], [Usage Status Count] (where count is 1 for summing purposes)
I'm mostly interested in understanding how the optimization is done, so that I can learn how best to handle these kinds of cases.
Thanks for any help anyone can provide!
BTW: Sorry about the smiles below, but I can't see to figure out a way to make a right paren not show a smile.
WITH
MEMBER [Measures].[Number of Large Boxes] AS
Count
(
Filter
(
Descendants
(
[Box].[Box Hierarchy].CURRENTMEMBER
),
[Box].[Box Hierarchy].CURRENTMEMBER.Properties("Box Type Code") = "LARGEBOX"
)
)
MEMBER [Measures].[Number of Large Boxes In Use] AS
Count
(
Exists
(
Filter
(
Descendants
(
[Box].[Box Hierarchy].CURRENTMEMBER
) ,
[Box].[Box Hierarchy].CURRENTMEMBER.Properties("Box Type Code") = "LARGEBOX"
) ,
{ [Usage Status].[Status Code].[INUSE], [Usage Status].[Status Code].[RECENTUSE] },
"Usage By Boxes"
)
)
MEMBER [Measures].[Box Usage Ratio] AS
IIF([Number of Large Boxes] = 0, NULL, [Number of Large Boxes In Use] / [Number of Large Boxes]) ,
FORMAT_STRING="0.00%;;;"
SELECT
NON EMPTY
{
[Measures].[Number of Large Boxes In Use], [Measures].[Number of Large Boxes], [Measures].[Box Usage Ratio]
}
ON COLUMNS,
NON EMPTY
{
Filter
(
Descendants
(
[Box].[Box Hierarchy].MEMBERS
) ,
[Measures].[Number of Large Boxes] > 0
)
} ON ROWS
FROM
[Box DW]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 1

I did a quick example using the same concepts that you are trying to apply with boxes against the Adventure Works DW database. Granted I didn't have anything to use as a Usage Type, buy you could simply swap out what I have and insert your set of members or simply create a named with with your Usage Type members you are trying to monitor and report on.

Here is what I came up with and not having any performance issues, but I most like do not have as large of a dimension as you are reporting against. Let me know if this helps or if you still have perforrmance issues. You will simply have to swap our your dimensions and members, but you can use it as a guide.

with member measures.[Blue Products] as iif(count((descendants(Product.[Product Categories].CurrentMember), [Product].[Color].&[Blue]))=0,null,count((descendants(Product.[Product Categories].CurrentMember), [Product].[Color].&[Blue])))

member measures.[Blue High Class Products] as iif(count((descendants(Product.[Product Categories].CurrentMember), [Product].[Color].&[Blue],[Product].[Class].&[H]))=0,null,count((descendants(Product.[Product Categories].CurrentMember), [Product].[Color].&[Blue],[Product].[Class].&[H] )))

member measures.[Blue High Class Ratio] as iif(measures.[Blue Products]=0, null,

measures.[Blue High Class Products]/measures.[Blue Products]), format_string="percent"

select {measures.[Blue Products], measures.[Blue High Class Products],measures.[Blue High Class Ratio]} on 0,

non empty(descendants(Product.[Product Categories].members)) on 1

from [adventure works]

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

awood-935115 SSC Enthusiast Points: 116 More actions · Answer 2

Thanks for the reply to this. Sorry I didn't see it earlier (thought I was subscribed to this forum, but I wasn't).

I'm going to look into what you provided and see if it solves my issue. 🙂

EDIT: Appears to do some of what I want to do, but not all of it. 🙂 I've made a new post with more information and a bit less of an abstraction from the problem I'm trying to solve. What I thought was an optimization problem has turned into a more complex problem (in other words my first attempt wasn't valid anyway). See the other post in this forum for a more detailed set of questions.