MDX hierarchy problem: reading

Showing posts with label reading. Show all posts

Monday, March 12, 2012

MDX, Matrix and Aggregate()

All,I keep reading that the Matrix is the perfect tool to use with OLAP data, but I am very confused about the efficacy of such a notion. As we know, OLAP is about precalculated aggregations, and the noticeable performance improvements that such an analytical database yields.
But RS answer to all of this is to calculate the aggregations at run-time,they recommend to bring in the leaf level data from the cubes, and then let RS aggregate the results at runtime.
This may an acceptable approach for simple MDX queries, but for advanced analysis -- it just does not make good sense. 
We desperately want to use RS for our Enterprise Reporting solution, but we are hard pressed to justify the performance issues (Cellset flattening + run-time aggregations) and the complexity of a MDX/ Matrix solution (if one exists)
Any examples of a multiple group (rows and columns) Matrix using an MDX 
datasource would be appreciated!!!
Thanks,
Jim

Are you asking about RS 2000 or RS 2005?

MDX queries designed in graphical query designer of RS 2005 can retrieve AS server aggregates directly within the query. You would then use the =Aggregate(...) function within the matrix cells instead of using regular aggregations. Based on the matrix cells' scopes, the Aggregate(...) function will determine the correct aggregate row from the flattened rowset. However, this means that the aggregate rows must be present and retrieved by the MDX query in the first place. Adjusting the MDX query based on the usage of the Aggregate function within the report is taken care of automatically by report designer if you designed the MDX query with the graphical MDX query designer of RS 2005.

-- Robert

|||

Robert,

Thanks for getting back to me -- I'm using RS 2000, so the good news is that it sounds like RS 2005 has addressed the issue of the MDX / Matrix complexity.

Any ideas on RS 2000? We won't be going to 2005 until Q1 2007.

Thanks,

Jim

|||

Technically, you could implement a full custom data extension for RS 2000. That data extension would need to implement the IDataReaderExtension interface (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_8je0.asp) among other interfaces and identify aggregate rows within the flattened rowset returned from a AS 2000 server.

However, this is a non-trivial task and requires a lot of effort to get everything working. Btw, RS 2000 already supports the Aggregate function - so you would then use it as described in my previous posting. Anyway, the Aggregate function is only useful if with a data extension that implements the IDataReaderExtension interface. None of the RS 2000 data extensions implement that functionality however.

Specific support for AS server aggregates is a feature that was added in RS 2005 in combination with a new RS 2005 Analysis Services data extension that works on top of the new AS 2005 AdoMd data provider.

-- Robert

|||

When I use the Aggregate() function instead of the default Sum() function in subtotals RS2005 returns nothing, i.e. it doesn't work.

The problem in all its simplicity: I have two measures in my MDX query:

Sales in dollars (can use Sum for subtotals)

Friday, March 9, 2012

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