I am using analysis service to analyze my perfmon counters. I collect %
Processor and store it in a SQL Server table. I have my cube defined with
one Measure :
ProcessorTime
And One Dimension:
Time - With Five Levels:
Year, Month, Day, Hour, Minute

I have it Averaging the Processor utilization at each level with a custom
rollup:
Avg(Descendants(Time.CurrentMember,Minute))

My problem is that I need to report on the average processor utilization
during business hours (Hour>9 and Hour<17). How would I do this with a
calculated member?

The following MDX should work when the selected [Time] member is at a
higher level than [Hour]. However, I would recommend that you split Time
into separate Date and TimeOfDay dimensions, and group Business Hours
separately in the TimeOfDay hierarchy, if they are fixed. Then cube
aggregations can be leveraged to improve performance:

Filter(Descendants(Time.CurrentMember,Hour),
Rank(Time.CurrentMember,Time.CurrentMember.Sibling s)
< 18), Minute))

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks! The MDX expression worked - but with the perfomance issues as
advertised....

I tried splitting the dimensions, but all of my data is at the minute level.
So my hours and minutes roll up for every day where the hour and minute
match. Also - I can't do the rollup to the Day, Month, and Year because the
data is all in another dimension.

Is there another way?




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

Even without splitting the [Time] dimension, you should be able to
significantly improve performance by using cube aggregations for the
Average at the Hour level and below:

Rank(Time.CurrentMember,Time.CurrentMember.Sibling s)
< 18), Measures.PerfSum/Measures.PerfCount)
Here, I assume that there are 2 measures defined on the Utilization
column, a "sum" aggregation: PerfSum and a
"count" aggregation: PerfCount (which is a sample count)


The approach that I have used to split the [Time] dimension is as
follows (there may be other approaches as well):

1) Use separate Date and TimeOfDay dimension tables. These
can be generated via SQL scripts if they don't exist.
The Date table would have a row for each day of interest
and the TimeOfDay a row for each minute of a day.

2) Since SQL Server has a combined DateTime, define a view
on the fact table with derived fields to join to the 2
dimension tables. Eg: convert(varchar,MyTime,101) for
date and left(convert(varchar,MyTime,8),5) for time.

3) If Business Hours are fixed, a flag field for this can
be added to the time table/view and used in TimeOfDay.


Here is a paper by Tom Chester on Time Dimensions:

http://www.sqljunkies.com/Article/D1...F20D60951395.s
cuk


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!