Aggregate data from Fact Table

[sagargoli]

I have a scenario where the measure is stored at two different levels (Product and Group) in the fact tables, as the measure is non aggregatable and follows certain rules to calculate the value at group level. Here is the sample data

Fact_Product
Product CycleTime
P1 50
P2 75
P3 100
P4 150

Fact_ProductGroup
ProductGroupID CycleTime
1 50
2 150

The dimension tables are
Dim.Product
ProductID ProductName
111 P1
112 P2
113 P3
114 P4

Dim.ProductGroup
ProductGroupID ProductGroup
1 G1
2 G2

I have combined the two facts into one fact view and the dimensions into one dimension view as follows:
Fact
ProductGroupID ProductID CycleTime
1 111 50
1 112 75
1 -999 50
2 113 100
2 114 150
2 -999 150

Dim
ProductID ProductName ProductGroupID ProductGroupName
111 P1 1 G1
112 P2 1 G1
-999 G1 1 G1
113 P3 2 G2
114 P4 2 G2
-999 G2 2 G2

The output of the cube I get is:

ProductGroup Product CycleTime
G1 G1 50
P1 50
P2 75
G1 Total 175
G2 G2 150
P3 100
P4 150
G2 Total 400

What I would like to see the cube output as
G1 Total as 50 and G2 Total as 150 and hide the first record from each group. I achieved the desired output by creating the calculated members and using HideMemberIf property, but the performance is not acceptable in our case.

Is anybody has the similar situation? Thanks for your time.

Sagar

 

[Alan0568]

Just reading this... "I have combined the two facts into one fact view and the dimensions into one dimension view "

I wouldn't have done that in the first place at all. Why don't you just have two measure groups in the cube, one based on each fact table?

 

[sagargoli]

Alan,

Thanks for the reply.

As of now, we have multiple fact tables, one for each level, and users have to pick the corresponding mesaure from the measure group. However, it is very confusing to the users to pick the corresponding measure for the level which he picks. We have two measure groups accross three levels (total 6 measure groups). In order to to remove the confusion for end users, I was trying different approaches.

Actually I got the solution for this. There are couple of ways we can achieve it.

Thanks,
Sagar