mdx questions

I'm going to start reading through the performance document, but in the mean time any suggestions on how to speed up the following?

with member Measures.bucket1 AS sum(filter(([REL TURN HRS].[REL TURN HRS].[REL TURN HRS].members * [Measures].[FACT CUT RELEASE Count] ) ,[REL TURN HRS].[REL TURN HRS].membervalue < 6),[Measures].[FACT CUT RELEASE Count]),

NON_EMPTY_BEHAVIOR = { [FACT CUT RELEASE Count] }

SELECT NON EMPTY { [Measures].bucket1} ON COLUMNS, NON EMPTY { ([CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [CUSTOMER JOB].[CUSTOMER NAME].&[T016]&[TIME4 MEDIA, INC. (T016)] } ) ON COLUMNS FROM [DW INSIGHT]) WHERE ( [CUSTOMER JOB].[CUSTOMER NAME].&[T016]&[TIME4 MEDIA, INC. (T016)] )

Before you start optimizing this - you need to have it correct. Please remove incorrect NON_EMPTY_BEHAVIOR clause.|||

A couple of other clarifications/questions:

Are the lower members of [REL TURN HRS].[REL TURN HRS] known in advance (like 1, 2, 3, ..)? If so, filter() wouldn't be needed to select the desired range (MemberValue < 6) of members.|||

I'm new to working with Analysis Services, MDX and reporting services. So i'm doing my best to read books and learn as fast as possible, so any information i'm able to gleen from these forums is a big help. So let me thank you in advance for your input.

First off you should know i'm working on this through reporting services in creating a dataset. I find I can use both the gui and design mode to see how it writes the mdx.

First in response to Mosha suggestion about the non empty behavior, this is a left over from what i copy from the calculated member in ssas. Surprisingly when it is removed the performance drops off even further to the point of locking up Visual Studio. If I write it into the mdx like I believe it should be

member Measures.bucket1 AS sum(nonempty(filter(([REL TURN HRS].[REL TURN HRS].[REL TURN HRS].members *[Measures].[FACT CUT RELEASE Count] ) ,[REL TURN HRS].[REL TURN HRS].membervalue < 6),[Measures].[FACT CUT RELEASE Count]),[FACT CUT RELEASE Count])

I get a preparing Query pop up and things pretty much get hung up again.

now your to questions, I'll answer the 2nd question first. That's the way that ssrs generated the mdx. I thought it was a bit strange. but it performed well so i left it alone.

ok 1st question, I think a little background info would help. I have a fact table with 3.5 million recs, each record has a start and end timestamp the duration of which is "REL TURN HRS". I want to eventually have a report where theses duration times can be grouped into buckets of time. 0 to < 6, >= 6 to <12 etc... The end user of the would enter a number into a parameter that would determine the size of the buckets in this case 6.

So i started to work on the mdx just using hardcoded values, the results of which I initially posted. When I started to design the cube,

I created a dimension with all the distinct values (77,000 +) of the duration times and linked that back to the fact table.

I now believe that maybe a flawed design perhaps this should have been a fact dimension?

|||The fact dimension would presumably be pretty large (3.5 million recs?), so I'm not sure that approach will perform better. Are [REL TURN HRS] rounded to integer hours, or could they be, for the purposes of bucketing (ie. if the user is only selecting buckets in multiples of hours)? In that case, wouldn't there be far fewer than 77K distinct values? Furthermore, as I mentioned earlier, in that case, you could direcltly specify the range of members for a bucket (assuming that there are no "holes" in the values), like [0]:[5], rather than applying Filter(). But if you really do need a dimension with large numbers of distinct values, maybe creating a multi-level hierarchy could help improve performance via aggregations.|||

I can see how converting to integer values would simplify things and would shink the size of the dimension. I'll look into this. However i think the end users may come back with a need to also show the value carried out to two places. But I should be able to set up the dimension with both values the detail 10.25 as one attribute and another attribute with the value of 11 and set up the muti level hierarchy off of that.

your thoughts?

|||A multi-level hierarchy (higher level being integer) could improve performance, if aggregations exist at that level and bucketing is done on integer boundaries. But if users only need to know actual values when drilling down to the fact level, then a fact dimension with drillthrough could meet that need.|||

here is what i finally did and the associated mdx, I would appreciate any critiquing thanks.

on the fact table I converted the values to whole integers, then set up a dimension with a list of the values.

On the reporting services report i have a parameter than excepts a integer value (bucket size) which is then used in the following mdx for the report

The only thing i noticed is that I probably should be using Aggregate instead of Sum below.

WITH member measures.bucket1 as sum([REL TURN HRS].[TRN HRS].&[0]:strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(@.bucket_size) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket2 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 2)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket3 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 2) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 3)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket4 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 3) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 4)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket5 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 4) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 5)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket6 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 5) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 6)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket7 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 6) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 7)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket8 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 7) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 8)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket9 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 8) + 1)) + "]"):strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR((@.bucket_size * 9)) + "]"),[Measures].[FACT CUT RELEASE Count])

member measures.bucket10 as sum(strtomember("[REL TURN HRS].[TRN HRS].&[" + CSTR(((@.bucket_size * 9) + 1)) + "]"): null,[Measures].[FACT CUT RELEASE Count])

SELECT NON EMPTY{ measures.bucket1,measures.bucket2,measures.bucket3,measures.bucket4,measures.bucket5,measures.bucket6,measures.bucket7,

measures.bucket8,measures.bucket9,measures.bucket10} ON COLUMNS,

NON EMPTY {[CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].allmembers} ON ROWS

FROM [DW INSIGHT] WHERE ( {[CUSTOMER JOB].[TITLE CD].&[FST1]} )

|||

You could use Subset() to simplify the bucket definitions, like:

WITH

Member [Measures].[BucketSize] as

Val(@.bucket_size)

member measures.bucket1 as

sum(Head([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

[Measures].[BucketSize] + 1),

[Measures].[FACT CUT RELEASE Count])

member measures.bucket2 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

[Measures].[BucketSize] + 1, [Measures].[BucketSize]),

[Measures].[FACT CUT RELEASE Count])

member measures.bucket3 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

([Measures].[BucketSize] * 2) + 1, [Measures].[BucketSize]),

[Measures].[FACT CUT RELEASE Count])

...

member measures.bucket10 as

sum(Subset([REL TURN HRS].[TRN HRS].[TRN HRS].Members,

([Measures].[BucketSize] * 9) + 1),

[Measures].[FACT CUT RELEASE Count])