Fact table
Number of rows: 3000.
Contains 9 columns for 9 measures.
One Primary Key clustered indexed.
1 non-clustered index on shop_id, product_id, packaging_id and cycle_id and
time_id.
â—‹ Time_id will determine that fact is a value for quarter, month or year.
It also determine the fact is for which quarter, year or month. Eg. 20000000
is year 2000 value. 20000100 is year 2000 Quarter 1. A time tablespace will
keep this information.
Searching mostly based on non-clustered index.

There is a special rollup for quantity which called RollupQuatity. This
rollup quantity will sum up current month and 2 of the month before as its
value. (E.g. Apr RollupQuatity is a summation of Feb to Apr. If Apr is NULL,
assume the rollup is NULL)

In order to fulfill this, I created a view and perform 2 self-join (LEFT
JOIN) to get the value of previous months.


Structure of the OLAP Cube in details
Contains 12 measures and 16 calculated members.
Let me explains what the 16 calculated members are.

To derive a cost value, we need to determine the fact is for quarter or year
and we have to take the latest month of a quarter or a latest quarter for a
year. Here is the MDX
a. NormalizedCost:

IIf([Time].CurrentMember.Level.Name = "Quarter",

IIf(Cycle.CurrentMember IS [Cycle].[Monthly Cycle 1],
COALESCEEMPTY(Time.CurrentMember.LastChild,
(COALESCEEMPTY(Time.CurrentMember.LastChild.PrevMe mber,
Time.CurrentMember.FirstChild))),

[Measures].[Cost]),

IIf([Time].CurrentMember.Level.Name = "Year",

IIf(Cycle.CurrentMember IS [Cycle].[Quarter Cycle 2]
OR Cycle.CurrentMember IS [Cycle].[Quarter Cycle 3]
OR Cycle.CurrentMember IS [Cycle].[Quarter Cycle 4]
OR Cycle.CurrentMember IS [Cycle].[Quarter Cycle 5]
OR Cycle.CurrentMember IS [Cycle].[Quarter Cycle 6],

COALESCEEMPTY(Time.CurrentMember.[Q4],
(COALESCEEMPTY(Time.CurrentMember.[Q3],
((COALESCEEMPTY(Time.CurrentMember.[Q2],
Time.CurrentMember.[Q1])))))),

[Measures].[Cost]),

[Measures].[Cost]))

After that, if the fact is a quarter fact which has monthly value, we copy
it's monthly rollup with another calculated member:
This is the MDX query: This calculated member make use of the calculated
member above:-

IIf(([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 1]
And ([Time].CurrentMember.Level.Name = "Quarter"
Or [Time].CurrentMember.Level.Name = "Year"))
Or ([Cycle].CurrentMember IS [Cycle].[Year Cycle 1]
And [Time].CurrentMember.Level.Name = "Year"),
([Cycle].[Month Cycle 1], [Measures].[NormalizedCost]),
[Measures].[Normalized Cost])

There are about 4 measure make use of this type of calculation.

Some additional info:
1. This cube work very fact in localhost OLAP environment. (< 5 second to
slice and dice)
2. It works poor when deploy to a production environment. (15 mins to
display the result when drill down to the deepest level with about 100 member
in a product dimension) for 3 years data. [5 mins for 1st level.]

Any idea what is going on for so slow? Fact table design incorrect?
Calculated Members MDX incorrect (not optimized)?
What is the optimization can be done? (I performed partitioned by year and
partitioned by cycle but both doesn't improve much.)

Thanks for your help.

there is no impact related to your databases indexes if you are in MOLAP
mode (which is the default aggregation mode)

First point, Insure that the client used the SP4 drivers and the server has
the SP4 applied.

but, for only 3000 rows, the problem come from your formula.

maybe this can help you:
http://sqlserveranalysisservices.com...20AS2005v2.htm


"Joel Leong" <JoelLeong (AT) discussions (DOT) microsoft.com> wrote

I'm still using MSAS 2K. Yup, most probably it is causing by Calculated
Member + Complicated rollup. I already updated at the next thread.

"Jéjé" wrote: