MS SQL Server OLAP - Bad Performance on Semiadditive Measures

Author

Bad Performance on Semiadditive Measures

Mauricio Cotes

2006-03-22, 8:34 pm

Hello

At my customer we are building a BI solution using SQL Server 2005 and other
MS products. The fact table data is not so big, about 40 million of records
in the largest fact table. We are using partitioning in both, cubes and
tables. We designed aggregations in MOLAP Partirions using values between
25% and 40% of optimization. Even though, we are having performance problems
with the semiadditive measures. Particularly using the LastNonEmpty and
AverageOfChildren. The performance is unacceptable. The "Regular" Measures
(Sum and Count) perform well.

This performance problems can be reproduced easily in Adventure Works DW
Olap Sample database. The problem can be reproduced on any cube browser like
OWC. Use the following steps to reproduce it.
1. Drop the Amount Measure into the Totals Area (Aggregation ByAccount)
2. Drop the Date.Calendar Hierarchy on the Filter Area
3. Drop the Accounts dimension on Columns
4. Then use the Date.Calendar Hierarchy to filter data from CY 2002 and CY
2003

Although we are not using the ByAccount Aggregation yet, we are using
LastNonEmpty and AverageOfChildren Aggregation Funtions on some measures and
Calculations and have similar response times.

Instead of the LastNonEmpty functions I created a calculated Member hanging
from the Date.[Date Calendar Calculations] Hierarchy (the one created from
the Time Intelligence Wizard) The member is called MyLastNonEmpty and here
is the definition

IIF([Date].[Calendar].CurrentMember.Level.UniqueName = "[Calendar].[Date]",
CoalesceEmpty(
([Date].[Calendar Date Calculations].[Current Date],
[Date].[Calendar].CurrentMember,
Measures.CurrentMember),
([Date].[Calendar Date Calculations].& #91;MyLastNonEmpty],

[Date].[Calendar].CurrentMember.PrevMember,
Measures.CurrentMember)
),
IIF(IsEmpty(([Date].[Calendar Date Calculations].[Current Date],
[Date].[Calendar].CurrentMember,
Measures.CurrentMember)),
([Date].[Calendar Date Calculations].& #91;MyLastNonEmpty],

[Date].[Calendar].CurrentMember.PrevMember,
Measures.CurrentMember),
([Date].[Calendar Date Calculations].& #91;MyLastNonEmpty],

[Date].[Calendar].CurrentMember.LastChild,
Measures.CurrentMember)
)
)

This performs far better than the built-in LastNonEmpty function but still
not acceptable. I have other Member to replace the AverageOfChildren. I
would expect that the cube stores aggregations for this semiadditive
measures. But it seems like it is not storing aggregations for this type of
Functions.

I hope someone could help me solving this problem.

Thanks,

Mauricio Cotes
BI Solutions Manager
Intergrupo S.A.

Deepak Puri

2006-03-23, 3:31 am

It's puzzling that the built-in semiadditive functions are performing
poorly for you:

- If you substitute the AS 2000 style SUM/COUNT for the
AverageOfChildren, do you see much better performance?

- Dave Wickert indicated in the MSDN Forums post below that LastNonEmpty
performed equivalently to Sum or Count measures in the large Project
REAL cube, so he might be able to shed some light on that issue:

http://forums.microsoft.com/MSDN/Sh...230289&SiteID=1[color=darkred]
Dave Wickert

Re: Semi-additive measures in AS2005 Standard Edition

There was a great deal of internal work done deep inside the runtime
engine to support semi-additive measures. If all it involved was writing
some fancy mdx it would have been much easier to solve. There is nothing
that you can do which will perform anywhere near as fast as the native
core aggregation functions.

This is one area where Enterprise Edition will be the only way to get
good performance at any real scale. I've been using LastNonEmptyChild on
a 255GB AS database (see http://www.microsoft.com/sql/bi/ProjectREAL)
for all of our inventory data (e.g. quantities on-hand). It is
performing virtually the same as sum and count.

_-_-_ Dave

------------------------------------------------------------------------
--------
Dave Wickert (MSFT)[color=darkred
]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Mauricio Cotes

2006-03-24, 7:40 am

Hello Deepak

The answer is yes. The MDX expression I used to calculate the
AverageOfChildren perform better than the built-in function.

As Dave Wickert explains, I also would expect that those functions perform
nearly the same as Sum and Count. But the fact is they don't.

As I said anyone can reproduce the behavior with Adventure Works DW sample
database. I also would like to share some measurements I have taken over my
customer's cube:

- Using Regular Measures (based on Sum and Count): 230 miliseconds (taken
from the profiler)
- Using the SQL Server built-in function: 150 Seconds
- Using the MDX expressions: from 10 to 15 Seconds

Every measurement taken under the same conditions. The behavior is
consistent in two different machines using the SQL Server Enterprise
Edition. And the testing I have done over Adventure Works DW was in my
personal Machine running the Developer Edition.

Tranks for your interest and help Deepak

Mauricio Cotes
BI Solutions Manager
Intergrupo S.A.

"Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
news:%230zHDNjTGHA.5884@TK2MSFTNGP14.phx.gbl...
> It's puzzling that the built-in semiadditive functions are performing
> poorly for you:
>
> - If you substitute the AS 2000 style SUM/COUNT for the
> AverageOfChildren, do you see much better performance?
>
> - Dave Wickert indicated in the MSDN Forums post below that LastNonEmpty
> performed equivalently to Sum or Count measures in the large Project
> REAL cube, so he might be able to shed some light on that issue:
>
> http://forums.microsoft.com/MSDN/Sh...230289&SiteID=1
> Dave Wickert
>
> Re: Semi-additive measures in AS2005 Standard Edition
>
> There was a great deal of internal work done deep inside the runtime
> engine to support semi-additive measures. If all it involved was writing
> some fancy mdx it would have been much easier to solve. There is nothing
> that you can do which will perform anywhere near as fast as the native
> core aggregation functions.
>
> This is one area where Enterprise Edition will be the only way to get
> good performance at any real scale. I've been using LastNonEmptyChild on
> a 255GB AS database (see http://www.microsoft.com/sql/bi/ProjectREAL)
> for all of our inventory data (e.g. quantities on-hand). It is
> performing virtually the same as sum and count.
>
> _-_-_ Dave
>
> ------------------------------------------------------------------------
> --------
> Dave Wickert (MSFT)
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***

Marius Dumitru \(MS\)

2006-03-30, 3:26 am

Thank you for reporting this problem. As mentioned in previous replies on
this thread, usually LastNonEmpty and AverageOfChildren would be somewhat
slower than Sum, but faster than equivalent hand-crafted MDX.

We'll try to investigate the performance differences on AdventureWorks
between LastNonEmpty and the MDX calculation you were using, and consider
potential improvements in a future product release or service pack.

--
Hope this helps

Marius

"Mauricio Cotes" <mcotes@intergrupo.com> wrote in message
news:Opayf0zTGHA.4340@TK2MSFTNGP10.phx.gbl...
> Hello Deepak
>
> The answer is yes. The MDX expression I used to calculate the
> AverageOfChildren perform better than the built-in function.
>
> As Dave Wickert explains, I also would expect that those functions perform
> nearly the same as Sum and Count. But the fact is they don't.
>
> As I said anyone can reproduce the behavior with Adventure Works DW sample
> database. I also would like to share some measurements I have taken over
> my customer's cube:
>
> - Using Regular Measures (based on Sum and Count): 230 miliseconds (taken
> from the profiler)
> - Using the SQL Server built-in function: 150 Seconds
> - Using the MDX expressions: from 10 to 15 Seconds
>
> Every measurement taken under the same conditions. The behavior is
> consistent in two different machines using the SQL Server Enterprise
> Edition. And the testing I have done over Adventure Works DW was in my
> personal Machine running the Developer Edition.
>
> Tranks for your interest and help Deepak
>
> Mauricio Cotes
> BI Solutions Manager
> Intergrupo S.A.
>
>
>
> "Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
> news:%230zHDNjTGHA.5884@TK2MSFTNGP14.phx.gbl...
>
>

Sponsored Links