Hi,

I am new to SQL Server AS and have created a small project to learn a little.

My problem is the amount of time it takes to calculate. I suspect I am
making some severe mistakes.

My data is NCAA basketball statistics. I have player level game data stored
in the database.

I have a cube with dimensions such as [roster].[position], [roster].[class],
[player], [team], etc… And attributes such as points, blocks, steals, weight,
height, etc

I have created calculated measures to average attributes over the game
dimension.

I also have calculated measures to get the average and stdev of the first
average.

I other words; what is the average of points scored per game by all players.
And what is the stdev of that average.

I then take individual player or team avg points and map its specific
average to a discreet value representing where the particular average falls
within the overall average.

The calculations take ~1 min to get [MEASURES].[Discreet Points] on a 1.7
ghz Pentium with 1 gig of ram. Memory does not seem to be a problem here as
only about 300 megs are used by SQL server.

Can anyone suggest and improvements?

Example by team:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Avg Points]
AS Avg([Game Log].[Game Log].[Game Log],[Measures].[Points]),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Overall AVG Points]
AS Avg( ([Team].[Team].[Team]), [Measures].[Avg Points] ),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Overall STDEV Points]
AS STDDEV( ([Team].[Team].[Team] ), [Measures].[Avg Points] ),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Discreet Points]
AS case
when [Measures].[Avg Points]-[Measures].[Overall AVG
Points]>[Measures].[Overall STDEV Points] then 2
when [Measures].[Avg Points]-[Measures].[Overall AVG
Points]<-[Measures].[Overall STDEV Points] then -2

when [Measures].[Avg Points]-[Measures].[Overall AVG
Points]>([Measures].[Overall STDEV Points]/2) then 1
when [Measures].[Avg Points]-[Measures].[Overall AVG
Points]<-([Measures].[Overall STDEV Points]/2) then -1

else 0
END,
VISIBLE = 1 ;

It may be possible (depending on what the fact table looks like) to
compute [Measures].[Avg Points] as a 'Sum' measure divided by a 'Count'
measure. This would avoid computing averages 'on-the-fly', and the
overall versus individual averages can then be compared by navigating
the appropriate dimension hierarchy, like [Team].

http://groups.google.com/group/micro...olap/msg/6e9d7
3404113dfa0
From: "Sean Boon [MS]"
Date: Mon, 14 Jul 2003 10:55:54 -0700
Subject: Re: avg

If you want to create an average, what you need to do is create one
measure
called SUM, and another measure based on COUNT and then create a
calculated
member called AVG which would just be SUM/COUNT. I believe there are
examples of this in the Foodmart 2000 cubes.

Sean

--
Sean Boon
SQL Server BI Product Unit

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Clint, from the syntax it looks like you might be using AS2k5. If so, it
has the AverageOfChildren aggregate type which you can set for a given
measure. From my understanding, it basically does the Sum divided by
count logic for you. (which really only saves you from having to set up
one calculated measure - I don't think it is much different in
performance terms)

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uBNmY2Z7FHA.3388 (AT) TK2MSFTNGP11 (DOT) phx.gbl>,
deepak_puri (AT) progressive (DOT) com says...