Performance of MDX calculated members

Performance of MDX calculated members

Post by morle.. » Sat, 23 Oct 1999 04:00:00



Hey folks ... I have a rather pressing question.

I have a Plato cube which contains a calculated measure using a
CROSSJOIN function. The calculated measure gives the right results but
takes several minutes to perform the calculation on a very small
dataset. Both the server and the clients being used are high-spec
machines which give superb performance in all other circumstances.

My question is: is there some way of precalculating and storing the
results of this calculated measure, so that it wouldn't have to be
calculated at runtime? Essentially this means building a cube on a
cube, I suppose. Is this possible?

Thanks for any assistance.

Morley.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Performance of MDX calculated members

Post by Harsha Munasingh » Sat, 23 Oct 1999 04:00:00


Hello Morley

I am about to use the same method for a distinct count problem. Could you
tell me what the specs of the machines were, the crossjoined dimension
sizes, whether you were using MOLAP/ROLAP/HOLAP and the timings.

Also, could you post this to the olap newsgroup as well. I think this is a
very important issue.

Many Thanks


> Hey folks ... I have a rather pressing question.

> I have a Plato cube which contains a calculated measure using a
> CROSSJOIN function. The calculated measure gives the right results but
> takes several minutes to perform the calculation on a very small
> dataset. Both the server and the clients being used are high-spec
> machines which give superb performance in all other circumstances.

> My question is: is there some way of precalculating and storing the
> results of this calculated measure, so that it wouldn't have to be
> calculated at runtime? Essentially this means building a cube on a
> cube, I suppose. Is this possible?

> Thanks for any assistance.

> Morley.

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

Performance of MDX calculated members

Post by morle.. » Tue, 26 Oct 1999 04:00:00


Thanks for the response Harsha. I'll put the problem into context.

We have a data warehouse of web usage data. I'm using MDX here to
overcome a classic 'semi-additive measure' data warehouse issue because
I have found the problem cumbersome to solve using standard SQL. The
measure is Concurrent Users. We have five dimensions, including two
time dimensions: Calendar Yr By Mth goes from Year down to Day Of
Month, Hrs and Mins allows drill-down to minute level. I need to
calculate the average and the maximum concurrent users over both time
dimensions, whilst summing over all other dimensions. Here are the
object definitions:

AvgConcUsers: [Measures].[Conc Users] / Count(Descendants([Calendar Yr
By Mth].CurrentMember,[Day Of Month])) / Count(Descendants([Hrs and
Mins].CurrentMember,[Minute]))

MaxConcUsers: MAX(Crossjoin(Descendants([Hrs and
Mins].CurrentMember,Levels("Minute")) , Descendants([Calendar Yr By
Mth].CurrentMember, Levels("[Day Of Month]"))), [Measures].[Conc Users])

These definitions give the right results. AvgConcUsers is slow to
calculate but MaxConcUsers (which contains the CROSSJOIN) is
appallingly slow. The cube is a MOLAP cube and I selected 100%
performance gain in the storage settings (I have also tested 75% and
50%, which were worse). The fact table is actually a view (which may
impact cube processing time but not cube query time I believe) with
only 3000 rows. The CROSSJOINed dimensions, ie. the two time
dimensions, contain 1826 and 1440 table rows respectively. The server
is a Dell PowerEdge PIII 450MHz 256MB 9GB SCSI HDD NT Server, the
client a Dell Precision PIII 450MHz 256MB NT Workstation. During the
trials I was the only user of the server and there were no other apps
running on the client.

Using BrioQuery as the query tool, I repeatedly ran a simple query with
the objects Month, Day Of Month, Hour, AvgConcUsers and MaxConcUsers.
Query time was 35 secs; 5 secs if I used only AvgConcUsers. I then
added the Product object (ie. now using 3 dimensions) and the query
time with both measures was 15 min, 1 min with just AvgConcUsers. We
will consistently need to run queries accross all 5 dimensions, hence
these response times are totally unacceptable.

This has now become a major problem for us. I don't see any
hardware/network issues. Can I use a different formula for
MaxConcUsers? Can I precalculate the results into another cube somehow?

Thanks loads for any assistance.

Glenn.



> Hello Morley

> I am about to use the same method for a distinct count problem. Could
you
> tell me what the specs of the machines were, the crossjoined dimension
> sizes, whether you were using MOLAP/ROLAP/HOLAP and the timings.

> Also, could you post this to the olap newsgroup as well. I think this
is a
> very important issue.

> Many Thanks




Quote:> > Hey folks ... I have a rather pressing question.

> > I have a Plato cube which contains a calculated measure using a
> > CROSSJOIN function. The calculated measure gives the right results
but
> > takes several minutes to perform the calculation on a very small
> > dataset. Both the server and the clients being used are high-spec
> > machines which give superb performance in all other circumstances.

> > My question is: is there some way of precalculating and storing the
> > results of this calculated measure, so that it wouldn't have to be
> > calculated at runtime? Essentially this means building a cube on a
> > cube, I suppose. Is this possible?

> > Thanks for any assistance.

> > Morley.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Performance of MDX calculated members

Post by Harsha Munasingh » Tue, 26 Oct 1999 04:00:00


Hello Glenn

I cannot see anything wrong with what you are doing, but hey I am no MSOLAP
guru!. Although, 256Mb for memory seems to be a bit low on your server.

Precalulating the results and saving them back into a table is an idea but
remember MSOLAP will only take data into a cube at the lowest level. Since
you have aggregated dimensions you will have to bring the data into severel
cubes at differing levels and bring them together in a virtual cube. Seems
very messy to me!

I urge you to post your 2 messages onto the  microsoft.public.sqlserver.olap
newsgroup and if you are desperate for an answer to email

 As I said before distinct counts are very important and the calculation
times you are getting are not acceptable. Which does not bode well for my
solution.

Regards

Harsha.


> Thanks for the response Harsha. I'll put the problem into context.

> We have a data warehouse of web usage data. I'm using MDX here to
> overcome a classic 'semi-additive measure' data warehouse issue because
> I have found the problem cumbersome to solve using standard SQL. The
> measure is Concurrent Users. We have five dimensions, including two
> time dimensions: Calendar Yr By Mth goes from Year down to Day Of
> Month, Hrs and Mins allows drill-down to minute level. I need to
> calculate the average and the maximum concurrent users over both time
> dimensions, whilst summing over all other dimensions. Here are the
> object definitions:

> AvgConcUsers: [Measures].[Conc Users] / Count(Descendants([Calendar Yr
> By Mth].CurrentMember,[Day Of Month])) / Count(Descendants([Hrs and
> Mins].CurrentMember,[Minute]))

> MaxConcUsers: MAX(Crossjoin(Descendants([Hrs and
> Mins].CurrentMember,Levels("Minute")) , Descendants([Calendar Yr By
> Mth].CurrentMember, Levels("[Day Of Month]"))), [Measures].[Conc Users])

> These definitions give the right results. AvgConcUsers is slow to
> calculate but MaxConcUsers (which contains the CROSSJOIN) is
> appallingly slow. The cube is a MOLAP cube and I selected 100%
> performance gain in the storage settings (I have also tested 75% and
> 50%, which were worse). The fact table is actually a view (which may
> impact cube processing time but not cube query time I believe) with
> only 3000 rows. The CROSSJOINed dimensions, ie. the two time
> dimensions, contain 1826 and 1440 table rows respectively. The server
> is a Dell PowerEdge PIII 450MHz 256MB 9GB SCSI HDD NT Server, the
> client a Dell Precision PIII 450MHz 256MB NT Workstation. During the
> trials I was the only user of the server and there were no other apps
> running on the client.

> Using BrioQuery as the query tool, I repeatedly ran a simple query with
> the objects Month, Day Of Month, Hour, AvgConcUsers and MaxConcUsers.
> Query time was 35 secs; 5 secs if I used only AvgConcUsers. I then
> added the Product object (ie. now using 3 dimensions) and the query
> time with both measures was 15 min, 1 min with just AvgConcUsers. We
> will consistently need to run queries accross all 5 dimensions, hence
> these response times are totally unacceptable.

> This has now become a major problem for us. I don't see any
> hardware/network issues. Can I use a different formula for
> MaxConcUsers? Can I precalculate the results into another cube somehow?

> Thanks loads for any assistance.

> Glenn.



> > Hello Morley

> > I am about to use the same method for a distinct count problem. Could
> you
> > tell me what the specs of the machines were, the crossjoined dimension
> > sizes, whether you were using MOLAP/ROLAP/HOLAP and the timings.

> > Also, could you post this to the olap newsgroup as well. I think this
> is a
> > very important issue.

> > Many Thanks



> > > Hey folks ... I have a rather pressing question.

> > > I have a Plato cube which contains a calculated measure using a
> > > CROSSJOIN function. The calculated measure gives the right results
> but
> > > takes several minutes to perform the calculation on a very small
> > > dataset. Both the server and the clients being used are high-spec
> > > machines which give superb performance in all other circumstances.

> > > My question is: is there some way of precalculating and storing the
> > > results of this calculated measure, so that it wouldn't have to be
> > > calculated at runtime? Essentially this means building a cube on a
> > > cube, I suppose. Is this possible?

> > > Thanks for any assistance.

> > > Morley.

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. MDX calculated member performance versus where statement

I'm relatively new to MDX and have a big performance problem as described
below. Could someone please give an indication as to why version 1 of the
MDX snippet below is taking a huge amount of extra time compared to version
2 ? Could it be a problem in the cube ?

*** Version 1 - takes about 30 minutes ****

with member [Organisation].[Subset] as '[Organisation].[All
Organisation].[Organisation].[ABCLife].[IFA].[XYZ].[New Policies IFA]'
...MDX select on rows and columns...
where [Organisation].[Subset]

*** Version 2 - takes 3 seconds ****

...the same MDX select on rows and columns as in version 1...
where [Organisation].[All
Organisation].[Organisation].[ABCLife].[IFA].[XYZ].[New Policies IFA]

Thanks for your assistance.
Andrew.

2. database design resources

3. Major MDX problems with Calculated member (this is a good test for MDX)

4. How to UPDATE record with struct in Pro*C

5. MDX - Member properties in a calculated member

6. User to Tablespace Mapping

7. Calculated Members vs Calculated Cells - Performance

8. problems installing sql server on Windows.NET web server RC1

9. OLAP 2000 - Calculated member with a calculated parent member

10. MDX - Tail + Filter on calculated member

11. MDX for Calculated member .. correction

12. How to improve MDX statement in Calculated member

13. mdx calculated member error in some Excel 2000 install