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].[MyLastNonEmpty],
[Date].[Calendar].CurrentMember.PrevMember,
Measures.CurrentMember)
),
IIF(IsEmpty(([Date].[Calendar Date Calculations].[Current Date],
[Date].[Calendar].CurrentMember,
Measures.CurrentMember)),
([Date].[Calendar Date Calculations].[MyLastNonEmpty],
[Date].[Calendar].CurrentMember.PrevMember,
Measures.CurrentMember),
([Date].[Calendar Date Calculations].[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.

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/Sho...30289&SiteID=1

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.developersdex.com ***

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 (AT) progressive (DOT) com> wrote

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 (AT) intergrupo (DOT) com> wrote

Hello Marius

Is there some fix regarding this problem in SQL 2005 SP1?

Thanks for your help

Mauricio

"Marius Dumitru (MS)" <mariusd (AT) online (DOT) microsoft.com> wrote

Just wanted to follow up by saying that the upcoming service pack release
(SP2) for SQL 2005 includes a fix for this performance issue.

--
Hope this helps

Marius


"Marius Dumitru (MS)" <mariusd (AT) online (DOT) microsoft.com> wrote