I am hoping someone can advise me on a more efficient calculation for the
following calc-member.

I have "Time" dimension with levels Hour, Shift, Day, Month, Year.
I have a organizational hierarchy in another dimension (MyHierarchy).
The fact table is a set of events, each with a time-stamp and a field from
the leaf level of MyHierarchy.

The calc-member "Repeater" is to count the number of times where a specific
member of MyHierarchy at the leaf level has an event more than once in an
hour. I created a recursive calc as shown below...

iif([Time].CurrentMember.Level.Name = "Hour",
Count(Filter(Descendants([MyHierarchy].CurrentMember, [MyHierarchy].
[LeafLevel]), [Count] > 1)),
Sum(Descendants([Time].CurrentMember, [Hour]), Repeater)
)

We have a Crystal report that shows the totals for a few months. Each month
has about 120K events and MyHierarchy has about 10K members at the leaf
level.
Opening the cube to browse takes several minutes. Viewing in AS Manager is
also painfully slow.

Any suggestions on a different approach to the MDX to improve performance?

Thanks,

Les M

Hi Les

This sounds like one of those calcs which may never perform particularly well; checking that you have all the necessary aggregations built in your cube (use Usage Based Optimisation rather than the Storage Design Wizard, or even build them manually), as well as doing all the other standard cube performance tuning things like partitioning mentioned in the 'AS Performance Guide', would be a good idea

As far as the MDX goes, does the following make any improvement

COUNT
GENERATE
NONEMPTYCROSSJOIN(Descendants([Time].CurrentMember, [Hour])),
FILTER
NONEMPTYCROSSJOIN({TIME.CURRENTMEMBER}, Descendants([MyHierarchy].CurrentMember,[MyHierarchy].[LeafLevel])
, MEASURES.COUNT>1


One other thought: if this is a really important type of analysis for you, can you somehow include a column in your fact table which flags whether this is not the first time this event has happened in this hour and then link a dimension to it? It would make your queries much quicker

HTH

Chri

----- Les McPhee wrote: ----

I am hoping someone can advise me on a more efficient calculation for the
following calc-member

I have "Time" dimension with levels Hour, Shift, Day, Month, Year
I have a organizational hierarchy in another dimension (MyHierarchy)
The fact table is a set of events, each with a time-stamp and a field from
the leaf level of MyHierarchy.

The calc-member "Repeater" is to count the number of times where a specific
member of MyHierarchy at the leaf level has an event more than once in an
hour. I created a recursive calc as shown below..

iif([Time].CurrentMember.Level.Name = "Hour"
Count(Filter(Descendants([MyHierarchy].CurrentMember, [MyHierarchy]
[LeafLevel]), [Count] > 1))
Sum(Descendants([Time].CurrentMember, [Hour]), Repeater


We have a Crystal report that shows the totals for a few months. Each month
has about 120K events and MyHierarchy has about 10K members at the leaf
level
Opening the cube to browse takes several minutes. Viewing in AS Manager is
also painfully slow

Any suggestions on a different approach to the MDX to improve performance

Thanks

Les

I don't know if this will help, but this formulas counts
the number of store that had inventory greater than zero.
Thsi goes to the level. The 1, SELF, prevents the
calcutation from counting the parent.

The time period is a separate dimension that I slice.

TC

--FREQ - NUM of STORES STOCKED--

IIF(SUM(DESCENDANTS([Organization].[Org Type].
[Store],1,SELF),[Measures].[On Hand Quantity]>0)=0,NULL,
SUM(DESCENDANTS([Organization].[Org Type].[Store],1,SELF),
[Measures].[On Hand Quantity]>0))