Calculated measures in cube giving very poor performance

Question

Calculated measures in cube giving very poor performance

Don Shields-368041

Ten Centuries

Points: 1189
More actions
March 18, 2010 at 11:11 am

#220016

I have an SSAS 2005 OLAP cube with 4 dimensions (Including servertime) and 5 measure groups along with 8 calculated measures in the cube.
My MDX query consists of two cubes, Customers and Items. When I query all measures except the calculator measures performance is fine. Customers * Items brings back 20k rows and 10 columns in about 5 seconds.
In my cube
[Last year total Sales quantity] =
sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,[ServerTime].[Fiscal Year].Currentmember),[Measures].[QTYINV])
A query like:
select {[Last year total Sales quantity]}
on columns,
NON EMPTY
{
[Customers].[CUSTTABLE].[CUSTTABLE] *
[InventItems].[INVENTTABLE].[INVENTTABLE]
}
on rows
from [SalesCube]
where ([ServerTime].[Fiscal Month].&[2010-04-01T00:00:00])
takes over 6 minutes to run on a server dedicated to ssas w/ 4gb ram (with cache cleared before query). thi returns about 13500 rows with 3 columns.
My other problematic measure is Last year to date Quantity =
SUM(Periodstodate([ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Year],
ParallelPeriod([ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].[Fiscal Year],1,[ServerTime].[Fiscal Year - Fiscal Month - Fiscal Day].currentmember)), [Measures].[QTYINV])
Performance is bad for any query including one of these measures. I turned on query logging and noticed that a single query containing these to measures logged over 8,000,000 entries. So I know I'm doing something wrong here, just not sure what.
Thanks,
don Shields

Viewing 12 posts - 1 through 11 (of 11 total)

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

Leo Mepham Ten Centuries Points: 1045 More actions · Answer 1

What level of aggregations do you have on the cube, and have you considered usage-based optimisation? You could capture the long-running query in the query log table, then add aggregations targeted at that.

EDIT: Also, have you set the NON_EMPTY properties correctly for the calculated measures? That really helps and would be preferable to the above if you haven't done it. Each calculated member should have the NON_EMPTY properties as the underlying physical measures it relies upon.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 2

I have indeed tried usage based optimizaiton. Every time I run it doesn't suggest any aggregations. I think that is because instead of one long running query I have 4 million short queries all with a duration of 1.

There doesn't seem to be a measure that I can use for the non empty behavior. I get incorrect results when I do so I get incorrect results. For instance the field that is being summed is QTYINV but if I use that field for NON Empty behavior I get a null for Period to date if the field was null for that particular tuple instead of a cumulative value of the prior periods. I think the non empty would be the way to go IF I had a measrue that truly reflected if the tuple should be empty or not. To do so I would have to use something that indicated if there was a value for that measure anywhere in the entire fiscal year. Does that make sense?

Thanks for your reply.

Don

Leo Mepham Ten Centuries Points: 1045 More actions · Answer 3

EDIT:

Scratch that, try this and report on what happens:

http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!500.entry

Seeing as there's no NON_EMPTY, include the base measure in the query. So, run:

select {[Last year total Sales quantity], [Measures].[QTYINV]}

on columns,

NON EMPTY

{

[Customers].[CUSTTABLE].[CUSTTABLE] *

[InventItems].[INVENTTABLE].[INVENTTABLE]

}

Good luck.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 4

Thanks Leo. Your query was somewhat faster. About 4:30 to 5:00 minutes instead of 6 to 7. that got me thinking though. I think the current query must me calculating the [last year total sales] measure for every tuple in the cube which is not what I desire. If I run somehting like this:

with member

[Measures].[LYTEST] as sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,strtomember('[ServerTime].[Fiscal Year].&[2009-10-01T00:00:00]')),[Measures].[qtyinv])

select {

[measures].[LYTEST],

[measures].[qtyinv]

}

on 0,

non empty

[Customers].[CUSTTABLE].[CUSTTABLE] *

[InventLocations].[Warehouses].[Warehouses]

on 1

from [salescube]

I get results in 23 seconds. So what I need to tell it is that I'm running the query for this year and I only need results for the prior year. I can do this with a parameter when I'm running the SSRS report I'm working on but I'm afraid if end users query the calculated measure in the cube using other tools like Excel then it is going to be extreemly so, so I also need to fix the calc'd measure in the cube. any ideas on how to do this?

thanks,

Don

Leo Mepham Ten Centuries Points: 1045 More actions · Answer 5

Out of interest, how many financial years are there in your cube? Have you gone very far into the future or the past?

If you're unfamiliar with Mosha's blog, he worked on the MS BI stack, and has some good articles, this is one: http://sqlblog.com/blogs/mosha/archive/2006/10/25/time-calculations-in-udm-parallel-period.aspx

One thing you could consider, is creating the parallel period measure as NULL, then using SCOPE to generate it only on the when the YEAR hierarchy is queried, so your code would look roughly like:

CREATE MEMBER [Measures].[LYTEST]

AS NULL;

SCOPE([Measures].[LYTEST], [ServerTime].[Fiscal Year]);

This = sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,[ServerTime].[Fiscal Year].Currentmember),[Measures].[QTYINV]);

END SCOPE;

But then looking at your sample queries, I notice that you don't seem to have the time dimension in play, so I'm not sure how helpful that would be. Do you get correct results (Albeit slowly) with your current measure? Note that CurrentMember is not the year we're in, it's the coordinate being executed at the time. Makes me wonder if you're evaluating Last Year's sales for every day...

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 6

There are only 2 1/2 years of data in the fact table. I left "

where ([ServerTime].[Fiscal Month].&[2010-04-01T00:00:00])" of of the query. I think you are probably right that it is calculating for every day or at least every month, not what I need on this measure. I'll try your suggestions and take a look at the blog you recommend.

Thanks,

Don

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 7

Another thought I had. When you say "caclulating for every date" could this be due to the granularity attibute being set to "Date" on Dimension usage for the servertime dimension?

Thanks,

Don

Leo Mepham Ten Centuries Points: 1045 More actions · Answer 8

Don Shields-368041 (3/23/2010)
Another thought I had. When you say "caclulating for every date" could this be due to the granularity attibute being set to "Date" on Dimension usage for the servertime dimension?

That was what I'd meant, but given that you supplied a date filter on the original query I doubt this is the problem.

One thing I did wonder, is whether you would be able to create a hidden measure, like a row count on the relevant fact table, that you could use as the NON_EMPTY? Without knowing your fact table and data I can't say for sure if this would work, but the odd invisible measure has worked well for me in the past. If the actual data doesn't support this, consider a calculated column in your DSV that generates a usable value just for this.

Failing that, the NULL measure and SCOPE setup I outlined earlier could save SSAS some work in the calculations.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 9

Thanks again. I have been contemplating the hidden measure that you discussed. That may be the only way to really "fix" it. I'll update you on how that works.

Don

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 10

I decided to try creating the null measure with scope. here is what I have:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[LY_TOT_QTY]

AS sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,[ServerTime].[Fiscal Year].Currentmember),[Measures].[QTYINV]),

FORMAT_STRING = "#,#.00",

NON_EMPTY_BEHAVIOR = { [INVCOUNT] },

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[LY_TOT_QTY_TEST]

AS NULL,

VISIBLE = 1;

SCOPE([MEASURES].[LY_TOT_QTY_TEST], [ServerTime].[Fiscal Year]);

This = sum(parallelperiod([ServerTime].[Fiscal Year].[Fiscal Year],1,[ServerTime].[Fiscal Year].Currentmember),[Measures].[QTYINV]);

END SCOPE;

My issue is that I can get anything returned but NULL on the [LY_TOT_QTY_TEST] measure. What am I doing wrong here?

example:

select {

[Measures].[LY_TOT_QTY],

[Measures].[LY_TOT_QTY_TEST]

}

on 0,

NON EMPTY

[Customers].[CUSTTABLE].[CUSTTABLE] *

[InventItems].[PRIMARYVENDORID].[PRIMARYVENDORID]

* [InventItems].[INVENTTABLE].[INVENTTABLE]

on 1

from [SalesCube 1]

where (([ServerTime].[Fiscal Year].&[2009-10-01T00:00:00]))

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 11

Just an FYI for anyone in this situation. I upgraded to SSAS 2008 and my exact same query runs in seconds.

Don