I'm so sorry to repost. I left out Custom Rollup Formula in my previous
posting. Admin may remove my previous posting. Thanks

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.

One of the dimension rollup is pretty complication. It contains 3 level: All
Shops -- Shop -- Category -- Product

Rollup value for each level is using a custom rollup formula. Custom rollup
formula is required because no rollup is using the standard function
provided. Eg. SUM, COUNT, AVG. Here is the formula:-

IIf(([Cycle].CurrentMember IS [Cycle].[Month Cycle 2] AND
[Time].CurrentMember.Level.Name = "Month")
OR ([Cycle].CurrentMember IS [Cycle].[Month Cycle 2] AND
[Time].CurrentMember.Level.Name = "Quarter")
OR ([Cycle].CurrentMember IS [Cycle].[Month Cycle 2] AND
[Time].CurrentMember.Level.Name = "Year")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 3] AND
[Time].CurrentMember.Level.Name = "Quarter")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 3] AND
[Time].CurrentMember.Level.Name = "Year")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 4] AND
[Time].CurrentMember.Level.Name = "Quarter")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 4] AND
[Time].CurrentMember.Level.Name = "Year")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 5] AND
[Time].CurrentMember.Level.Name = "Quarter")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 5] AND
[Time].CurrentMember.Level.Name = "Year")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 6] AND
[Time].CurrentMember.Level.Name = "Quarter")
OR ([Cycle].CurrentMember IS [Cycle].[Quarter Cycle 6] AND
[Time].CurrentMember.Level.Name = "Year"),

IIf([Measures].CurrentMember IS [Measures].[Cost],
IIf(SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])
= 0, NULL,
SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity]*[Cost])
/ SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])),

IIf([Measures].CurrentMember IS [Measures].[Measure 2],
IIf(SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])
= 0, NULL,
SUM([Factory_Package_Product].CurrentMember.children,
[RollupQuantity]*[Measure 2])
/ SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])),

IIf([Measures].CurrentMember IS [Measures].[Measure 3],
IIf(SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])
= 0, NULL,
SUM([Factory_Package_Product].CurrentMember.children,
[RollupQuantity]*[Measure 3])
/ SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])),

IIf([Measures].CurrentMember IS [Measures].[Measure 4],
IIf(SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])
= 0, NULL,
SUM([Factory_Package_Product].CurrentMember.children,
[RollupQuantity]*[Measure 4])
/ SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])),

IIf([Measures].CurrentMember IS [Measures].[Measure 5],
IIf(SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])
= 0, NULL,
SUM([Factory_Package_Product].CurrentMember.children,
[RollupQuantity]*[Measure 5])
/ SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])),

IIf([Measures].CurrentMember IS [Measures].[Measure 6],
IIf(SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])
= 0, NULL,
SUM([Factory_Package_Product].CurrentMember.children,
[RollupQuantity]*[Measure 6])
/ SUM([Factory_Package_Product].CurrentMember.children, [RollupQuantity])),

CalculationPassValue([Shop_Category_Product].CurrentMember,0))))))),
CalculationPassValue([Shop_Category_Product].CurrentMember,0))




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.)