MDX to get member names?

I want to get a list of member name for an attribute, without accessing the fact table, for speed.

I can write a statement like "select {Measures.SalesAmount} on columns, {CostCenter.Members} on rows from MyCube", which will give me a list of Cost Centers going down. Is there a way to get this same list, but without accessing the fact table, with the hopes that the query will execute faster?

Thanks for any assistance,

Ernie

Assume the CostCenter attribute belongs to the dimension [CCDim]

select CostCenter.members on rows

from [$CCDim]

|||

Works great.

Thanks Jeffrey!

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

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

MDX Query/Caching/Performance Questions

I am having trouble trying to improve processing speed for an MDX Query.This one is driving me bonkers so I’m hoping someone can help me.

Here is my MDX Query:

SELECT CROSSJOIN([Year],{[Measures].[Quick Ratio]}) ON COLUMNS, CROSSJOIN(DESCENDANTS([Department]),DESCENDANTS([GL Company])) ON ROWS FROM [GLRatios]

Year Count: 9

Department Count: 33

GL Company Count: 41

GL Summary Category ID.Category Count: 25

Quick Ratio is a Calculated Member calculated as follows:

'IIF(([GL Summary Category ID].[Category].&[Current Liabilities],[Measures].[Balance]) <> 0,(

(([GL Summary Category ID].[Category].&[Cash],[Measures].[Balance])

+([GL Summary Category ID].[Category].&[Accounts Receivable],[Measures].[Balance]))

/([GL Summary Category ID].[Category].&[Current Liabilities],[Measures].[Balance])) ,0)

'

The Result Cell set consists of 1430 rows and 11 columns.

Storage Method is MOLAP.I have designed aggregations.

1) When I run this query from the SQL Server Management Studio, it takes 23 seconds to run it the first time.The second time I run this MDX Query from SQL Server Management Studio, it takes 2 seconds to run. I would assume this is the result of caching.The first time I run the query after the cube has been processed, I see over 73,000 Query SubCube entries in SQL Profiler that look something like this:

EventClassQuery Subcube

EventSubClass1 - Cache data

TextData0000000000000000000000000000000000000001000000000,1,10,1

I would say over 99% of all of the entries in SQL Profiler contain the same TextData.Why is the MDX Query generating these entries, and why is it generating so many entries with that appear to be identical TextData values?Is it caused by something I have done when defining my dimensions, or a design flaw made while defining my Data Source View?Is this caused by using a Calculated Member as measure?

2) I have a C# web service that uses the ExecuteXmlReader method of the Microsoft.AnalysisServices.AdomdClient.AdomdCommand class to execute the same MDX query on the server and return it to the client as Xml.In other words I am rolling my own web service instead of calling the XMLA web service from the client.

When my web service call executes the MDX query, it uses ASPNET as user. Each time the MDX Query is executed via the web service it also generates >73,000 Query Subcube entries in SQL Profiler and takes 23 seconds to run.The MDX query generates the same SQL Profiler entries each time it is executed from the web service.It does not cache.

Is there something about the use of the ASPNET user that prevents caching of the data on the server? Is it a case of each ASPNET session having its’ own cached data?

Thanks in advance for your help.

Wendell G.

The large number of Query Subcube events is likely the result of getting cell values one at time. The TextData field only shows the grains of the queries, not the slices. There is a Query Subcube Verbose event which shows the slices as well. Using that event you should see that the grains stay the same but the slices change each time. Since the sub event is Cache data, it means AS server has pre-fetched all necessary cell values earlier on in a larger query and later on filters single cell values from the larger data cache. There was some performance improvement in the SP1 QFE rollup release which makes the cache lookup faster in certain cases. Without looking at the database it is hard to tell why AS server chose the cell-by-cell query plan, maybe the presence of IIF was the cause. You can try the connection string property Cache Policy=9 to see if it makes any difference in performance for this query. Management Studio doesn't support custom connection string properties, you have to use either the MDX Sample application from SQL Server 2000 or write your own C# application.

|||

What does CachePolicy=9 do? The MDX Solutions book only lists Cache Policy values up to 7.

Thanks,

|||

Sometimes AS server chooses a cell by cell calculation plan over a bulk evaluation plan. Setting Cache Policy=9 forces AS server to always use a bulk evaluation plan if one is available. This is a workaround in the rare cases where the AS decision hurts performance.

WARNING:

Generally speaking, setting Cache Policy=9 could cause severe performance degradation. Users should only use this for diagnosis purpose or occassionally boost the performance of individual queries. Microsoft does not recommend users to change this connection string property unless explicitly recommended by Customer Support after a full disgnose of customer database and exhaust all other means of improving performance.

|||

Hey,

I've tracked the majority of my speed issues down to a single calculation. My [Measures].[Quick Ratio] is a calculated member that is based on a second calculated member:

SUM(PeriodsToDate([Time Periods].[(All)],[Time Periods].CurrentMember),Measures.[Beginning Amount])

This calculated member is very slow when I crossjoin Department and GL Company on rows. In the fact table of my database, there is at least one row for each combination of department and company, dated 1/1/2001, that contains Beginning Amount, even if it is zero. At the end of 2001, if the balance for Department and GL Company has changed as of the end of the year, I am storing the net change increase or decrease of the balance as Beginning Amount in a new row, dated 1/1/2002, and so on for each subsequent year. This way in order to get the beginning balance at beginning of any year all I do is sum the Beginning Amount up to that point.

A Sum of PeriodsToDate([Time Periods].[(All)] always provides the correct amount, no matter what time dimension I use (year, month, quarter) but it is very slow. Is there a more efficient way to get the right answer?

Thanks for any help you can provide.

Wendell G

|||There was a major performance improvement done in SP2 which addresses exactly this scenario. A public beta release is scheduled to come out in a few weeks.

MDX Query/Caching/Performance Questions

I am having trouble trying to improve processing speed for an MDX Query.This one is driving me bonkers so I’m hoping someone can help me.

Here is my MDX Query:

SELECTCROSSJOIN([Year],{[Measures].[Quick Ratio]})ONCOLUMNS,CROSSJOIN(DESCENDANTS([Department]),DESCENDANTS([GL Company]))ONROWSFROM [GLRatios]

Year Count: 9

Department Count: 33

GL Company Count: 41

GL Summary Category ID.Category Count: 25

Quick Ratio is a Calculated Member calculated as follows:

'IIF(([GL Summary Category ID].[Category].&[Current Liabilities],[Measures].[Balance]) <> 0,(

(([GL Summary Category ID].[Category].&[Cash],[Measures].[Balance])

+([GL Summary Category ID].[Category].&[Accounts Receivable],[Measures].[Balance]))

/([GL Summary Category ID].[Category].&[Current Liabilities],[Measures].[Balance])) ,0)

'

The Result Cell set consists of 1430 rows and 11 columns.

Storage Method is MOLAP.I have designed aggregations.

1) When I run this query from the SQL Server Management Studio, it takes 23 seconds to run it the first time.The second time I run this MDX Query from SQL Server Management Studio, it takes 2 seconds to run.I would assume this is the result of caching.The first time I run the query after the cube has been processed, I see over 73,000 Query SubCube entries in SQL Profiler that look something like this:

EventClassQuery Subcube

EventSubClass1 - Cache data

TextData0000000000000000000000000000000000000001000000000,1,10,1

I would say over 99% of all of the entries in SQL Profiler contain the same TextData.Why is the MDX Query generating these entries, and why is it generating so many entries with that appear to be identical TextData values?Is it caused by something I have done when defining my dimensions, or a design flaw made while defining my Data Source View?Is this caused by using a Calculated Member as measure?

2) I have a C# web service that uses theExecuteXmlReader method of the Microsoft.AnalysisServices.AdomdClient.AdomdCommand class to execute the same MDX query on the server and return it to the client as Xml.In other words I am rolling my own web service instead of calling the XMLA web service from the client.

When my web service call executes the MDX query, it uses ASPNET as user. Each time the MDX Query is executed via the web service it also generates >73,000 Query Subcube entries in SQL Profiler and takes 23 seconds to run.The MDX query generates the same SQL Profiler entries each time it is executed from the web service.It does not cache.

Is there something about the use of the ASPNET user that prevents caching of the data on the server? Is it a case of each ASPNET session having its’ own cached data?

Thanks in advance for your help.

Wendell G.

The large number of Query Subcube events is likely the result of getting cell values one at time. The TextData field only shows the grains of the queries, not the slices. There is a Query Subcube Verbose event which shows the slices as well. Using that event you should see that the grains stay the same but the slices change each time. Since the sub event is Cache data, it means AS server has pre-fetched all necessary cell values earlier on in a larger query and later on filters single cell values from the larger data cache. There was some performance improvement in the SP1 QFE rollup release which makes the cache lookup faster in certain cases. Without looking at the database it is hard to tell why AS server chose the cell-by-cell query plan, maybe the presence of IIF was the cause. You can try the connection string property Cache Policy=9 to see if it makes any difference in performance for this query. Management Studio doesn't support custom connection string properties, you have to use either the MDX Sample application from SQL Server 2000 or write your own C# application.

|||

What does CachePolicy=9 do? The MDX Solutions book only lists Cache Policy values up to 7.

Thanks,

|||

Sometimes AS server chooses a cell by cell calculation plan over a bulk evaluation plan. Setting Cache Policy=9 forces AS server to always use a bulk evaluation plan if one is available. This is a workaround in the rare cases where the AS decision hurts performance.

WARNING:

Generally speaking, setting Cache Policy=9 could cause severe performance degradation. Users should only use this for diagnosis purpose or occassionally boost the performance of individual queries. Microsoft does not recommend users to change this connection string property unless explicitly recommended by Customer Support after a full disgnose of customer database and exhaust all other means of improving performance.

|||

Hey,

I've tracked the majority of my speed issues down to a single calculation. My [Measures].[Quick Ratio] is a calculated member that is based on a second calculated member:

SUM(PeriodsToDate([Time Periods].[(All)],[Time Periods].CurrentMember),Measures.[Beginning Amount])

This calculated member is very slow when I crossjoin Department and GL Company on rows. In the fact table of my database, there is at least one row for each combination of department and company, dated 1/1/2001, that contains Beginning Amount, even if it is zero. At the end of 2001, if the balance for Department and GL Company has changed as of the end of the year, I am storing the net change increase or decrease of the balance as Beginning Amount in a new row, dated 1/1/2002, and so on for each subsequent year. This way in order to get the beginning balance at beginning of any year all I do is sum the Beginning Amount up to that point.

A Sum of PeriodsToDate([Time Periods].[(All)] always provides the correct amount, no matter what time dimension I use (year, month, quarter) but it is very slow. Is there a more efficient way to get the right answer?

Thanks for any help you can provide.

Wendell G

|||There was a major performance improvement done in SP2 which addresses exactly this scenario. A public beta release is scheduled to come out in a few weeks.