Performance problem with Analysis Services 2005

#1

Hello,

We have a large customer where we implement an OLAP solution for sales.

Recently, we run into the following problem: there is a measure (say M1)
which works very nice in our MDX queries (our test MDX query returns results
in under 2 seconds), but if I replace M1 with a calculated member (say CM1)
defined as being equal with M1 the same query runs for almost 4 hours! (the
query contains a CROSSJOIN statement and the non-empty behavior of CM1 is
set to M1)

I am puzzled about this enormous difference in response time, especially
because the documentation states that setting the non-empty behavior enables
the engine to avoid calculating members to decide whether they are empty or
not.

Can anybody help me figure out this? Thanks a lot.

Best regards,

Ciprian Jichici

Genisoft

#2

what is the formula of the CM1?
can you post it?

does its a simple = measures.m1 ?

"Ciprian Jichici" <ciprian.jichici (AT) genisoft (DOT) ro> wrote

Quote:

Hello,

We have a large customer where we implement an OLAP solution for sales.

Recently, we run into the following problem: there is a measure (say M1)
which works very nice in our MDX queries (our test MDX query returns
results in under 2 seconds), but if I replace M1 with a calculated member
(say CM1) defined as being equal with M1 the same query runs for almost 4
hours! (the query contains a CROSSJOIN statement and the non-empty
behavior of CM1 is set to M1)

I am puzzled about this enormous difference in response time, especially
because the documentation states that setting the non-empty behavior
enables the engine to avoid calculating members to decide whether they are
empty or not.

Can anybody help me figure out this? Thanks a lot.

Best regards,

Ciprian Jichici

Genisoft

#3

The formula is simply [MEASURES].[M1], and NON_EMPTY_BEHAVIOR is set to
[MEASURES].[M1].
It seems to me that for some strange reason, AS2005 is simply ignoring the
NON_EMPTY_BEHAVIOR.

Thanks,
Ciprian Jichici
Genisoft

"Jeje" <willgart (AT) hotmail (DOT) com> wrote

Quote:

what is the formula of the CM1?
can you post it?

does its a simple = measures.m1 ?

"Ciprian Jichici" <ciprian.jichici (AT) genisoft (DOT) ro> wrote in message
news:O3%23a0ronGHA.3896 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
Hello,

We have a large customer where we implement an OLAP solution for sales.

Recently, we run into the following problem: there is a measure (say M1)
which works very nice in our MDX queries (our test MDX query returns
results in under 2 seconds), but if I replace M1 with a calculated member
(say CM1) defined as being equal with M1 the same query runs for almost 4
hours! (the query contains a CROSSJOIN statement and the non-empty
behavior of CM1 is set to M1)

I am puzzled about this enormous difference in response time, especially
because the documentation states that setting the non-empty behavior
enables the engine to avoid calculating members to decide whether they
are empty or not.

Can anybody help me figure out this? Thanks a lot.

Best regards,

Ciprian Jichici

Genisoft

#4

Hi Ciprian,

Please, post the whole MDX query.
Such behaviour of simplest CM is is very suspicious.

Thanks,

Vladimir Chtepa

"Ciprian Jichici" <ciprian.jichici (AT) genisoft (DOT) ro> schrieb im Newsbeitrag
news:%23jODfCqnGHA.4728 (AT) TK2MSFTNGP03 (DOT) phx.gbl...

Quote:

The formula is simply [MEASURES].[M1], and NON_EMPTY_BEHAVIOR is set to
[MEASURES].[M1].
It seems to me that for some strange reason, AS2005 is simply ignoring the
NON_EMPTY_BEHAVIOR.

Thanks,
Ciprian Jichici
Genisoft

"Jeje" <willgart (AT) hotmail (DOT) com> wrote in message
news:edxgGkpnGHA.1808 (AT) TK2MSFTNGP02 (DOT) phx.gbl...
what is the formula of the CM1?
can you post it?

does its a simple = measures.m1 ?

"Ciprian Jichici" <ciprian.jichici (AT) genisoft (DOT) ro> wrote in message
news:O3%23a0ronGHA.3896 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
Hello,

We have a large customer where we implement an OLAP solution for sales.

Recently, we run into the following problem: there is a measure (say M1)
which works very nice in our MDX queries (our test MDX query returns
results in under 2 seconds), but if I replace M1 with a calculated
member (say CM1) defined as being equal with M1 the same query runs for
almost 4 hours! (the query contains a CROSSJOIN statement and the
non-empty behavior of CM1 is set to M1)

I am puzzled about this enormous difference in response time, especially
because the documentation states that setting the non-empty behavior
enables the engine to avoid calculating members to decide whether they
are empty or not.

Can anybody help me figure out this? Thanks a lot.

Best regards,

Ciprian Jichici

Genisoft

#5

And could you post the MDX test query which you're using?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

#6

Is it possible that you have not defined NON EMPTY BEHAVIOUR for calculated
measure?
In this case system can decide to do caclulations for obvious( for you, not
system) empty cells and that is why it takes so long.

Regards

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Quote:

And could you post the MDX test query which you're using?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

#7

Please remove the redundant curly braces from the Non_Empty_Behavuior
expression:

CREATE MEMBER CURRENTCUBE.[MEASURES].[ValCM] AS [Measures].[Val],
NON_EMPTY_BEHAVIOR = [Val] ...

Also, the upcoming SP2 release includes a fix for this problem.
Still, in general, it is highly recommended to avoid using { }in
Non_Empty_Behavior in the first place...

--
Hope this helps

Marius

"Ciprian Jichici" <ciprian.jichici (AT) genisoft (DOT) ro> wrote

Quote:

Hello everybody,

I've worked today to isolate a scenario that reproduces with accuracy our
problem. You will find attached an archive that contains a Visual Studio
2005 solution named SparseWarehouse. There are 3 projects there:

CreateWarehouse - simple database project containing a single SQL script
that creates a database named SparseWarehouse used to demonstrate our
problem.

SparseCube - simple AS2005 project that builds a cube named Test on top of
the SparseWarehouse database.

MDXTest - simple console app that used ADOMD.NET to execute our MDX query
against the Test cube.

You will also find inside the archive a SQL Profiler trace named
SparseCubeTrace that shows execution times (we used a Pentium Xeon 3 GHz
2-processor box with hyperthreading, 4Gb RAM, 64 bit, SQL Server 2005
Enterprise Edition 64 bit, build 9.0.2047, Windows Server 2003 SP1 64
bit). We also did a test on build 9.0.2153 and the results are the same.

Let me elaborate a bit on the demo I attached:

The cube is very simple, has 2 trivial dimensions, each having 3000
members. The fact table contains only 5 records. We did this on purpose,
to get into the marginal case where ration between the non empty cells and
the total number of cells is very close to 0. There is only one measure
named Val and only one calculated member named ValCM. The formula to ValCM
is trivial, it simply gets the value of Val. Non-empty-behavior for ValCM
is set to Val.

Once this is set up, you can see that on our machine, the following MDX

select

non empty Dimension1.Dimension1.Members on 0,

non empty Dimension2.Dimension2.Members on 1

from

Test

where

Measures.ValCM

runs in 22406 miliseconds, while the following MDX

select

non empty Dimension1.Dimension1.Members on 0,

non empty Dimension2.Dimension2.Members on 1

from

Test

where

Measures.Val

runs in 16 miliseconds. If the dimensions are larger (say 10000 x 10000
members, the first MDX jumps to more than half our, while the first runs
in about 30 miliseconds). From what I see, the closer the ratio gets to 0,
the worse is the performance hit.

We used MDXTest to run the queries (just to make sure that NON EMPTY
THRESHOLD is properly set.

Since there are only 5 cells out of 9 million that have values, I simply
cannot explain to myself the huge difference in execution time. As far as
I know, having the non empty behavior set to a measure and setting the non
empty threshold properly should force AS2005 to kick in with the new
version of the calculation algorithm that is supposed to be capable of
handling very fast non empty cells.

Unfortunately for us, eliminating the NON EMPTY statement from our MDX is
not an option (because our users rely heavily on Excel and Excel does not
let you influence the use of NON EMPTY).

Thanks a lot,

Ciprian Jichici

Genisoft

"Vladimir Chtepa" <vc.nospam (AT) diacom-systemhaus (DOT) nospam.de> wrote in message
news:uacII4qnGHA.4180 (AT) TK2MSFTNGP02 (DOT) phx.gbl...
Hi Ciprian,

Please, post the whole MDX query.
Such behaviour of simplest CM is is very suspicious.

Thanks,

Vladimir Chtepa

"Ciprian Jichici" <ciprian.jichici (AT) genisoft (DOT) ro> schrieb im Newsbeitrag
news:%23jODfCqnGHA.4728 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
The formula is simply [MEASURES].[M1], and NON_EMPTY_BEHAVIOR is set to
[MEASURES].[M1].
It seems to me that for some strange reason, AS2005 is simply ignoring
the
NON_EMPTY_BEHAVIOR.

Thanks,
Ciprian Jichici
Genisoft

"Jeje" <willgart (AT) hotmail (DOT) com> wrote in message
news:edxgGkpnGHA.1808 (AT) TK2MSFTNGP02 (DOT) phx.gbl...
what is the formula of the CM1?
can you post it?

does its a simple = measures.m1 ?

"Ciprian Jichici" <ciprian.jichici (AT) genisoft (DOT) ro> wrote in message
news:O3%23a0ronGHA.3896 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
Hello,

We have a large customer where we implement an OLAP solution for
sales.

Recently, we run into the following problem: there is a measure (say
M1)
which works very nice in our MDX queries (our test MDX query returns
results in under 2 seconds), but if I replace M1 with a calculated
member (say CM1) defined as being equal with M1 the same query runs
for
almost 4 hours! (the query contains a CROSSJOIN statement and the
non-empty behavior of CM1 is set to M1)

I am puzzled about this enormous difference in response time,
especially
because the documentation states that setting the non-empty behavior
enables the engine to avoid calculating members to decide whether they
are empty or not.

Can anybody help me figure out this? Thanks a lot.

Best regards,

Ciprian Jichici

Genisoft

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Performance problem with Analysis Services 2005

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Performance problem with Analysis Services 2005 - 07-03-2006 , 05:26 AM

Re: Performance problem with Analysis Services 2005 - 07-03-2006 , 07:05 AM

Re: Performance problem with Analysis Services 2005 - 07-03-2006 , 08:01 AM

Re: Performance problem with Analysis Services 2005 - 07-03-2006 , 09:36 AM

Re: Performance problem with Analysis Services 2005 - 07-03-2006 , 09:43 AM

Re: Performance problem with Analysis Services 2005 - 07-04-2006 , 08:01 AM

Re: Performance problem with Analysis Services 2005 - 07-06-2006 , 09:23 PM