am having difficulty with my time dimension.. i have yearly, monthly, weekly,
daily. my weekly separates by end and start of the month because of obvious
reasons. however i need my weekly together, so i created another dimension
which is week, but the thing is my query really slows down when i do this.
mind you that i have custom roll up formulas for both dimension. is there
any other way to do this?

Thank you in advance!

Is the new "Week" dimension a regular or virtual dimension, and is the
leaf level "week" or "day"?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak! This is a virtual dimension and is a leaf level week. Thanks!
Hope you could help me.

"Deepak Puri" wrote:

Can you give a general idea of the custom rollup on the time dimensions,
and of the query with poor performance?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

for my year, month, week, i have the ff custom rollup formula (gets the
lastchild for a headcount measure):
IIF([Measures].[CSA Headcount] is [Measures].[HeadCount]),
CoalesceEmpty(Time.Lastchild, Time.CurrentMember.PrevMember),
Avg(Time.CurrentMember.Children))

Then I have 2 other dimensions which also have a member formula(computes for
the sumproduct): (for site and for program)
IIF([Measures].CurrentMember is [Measures].[Headcount],
sum(site.CurrentMember.Children),
IIF([Measures].CurrentMember is [Measures].[Utilization]
sum(site.CurrentMember.Children,[Measures].[Headcount]*[Measures].[Utilization])/[Measures].[Headcount],
IIF([Measures].CurrentMember is [Measures].[Other Utilization],
IIF([Measures].[Peak]=0,sum(site.CurrentMember.Children),
sum(site.CurrentMember.Children,[Measures].[Peak]*[Measures].[Other
Utilization])/[Measures].[Peak]),sum(site.CurrentMember.Children))))

hope you could help me optimized my code or have other options to make my
query faster. Thank you so much Deepak!

"Deepak Puri" wrote:

Without knowing all the datails of your cube and application, it looks
like there may be some scope for replacing custom rollups with standard
aggregation functions. The last value for HeadCount probably can't be
aggregated over time; but how about the other measures, which are
averages or weighted averages? If site and program are regular
dimensions, then using standard aggregation on those should speed up
queries.

If you start with the fact data fields, and consider the various
averages that are needed, can they (or most of them) be derived from
appropriate base measures?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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