Joining facts to multiple levels in the same dimension

[sagargoli]

The following thread was posted in June 20008 (thread353-1479467)

http://www.tek-tips.com/viewthread.cfm?qid=1479467&page=1

I have a scenario where the measure is stored at two different levels (Product and Group) in the fact tables, as the measure is a non aggregatable. 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

 

[johnherman]

As this forum is a DW general discussion, you should probably tell us about your DW environment (Oracle, Microsoft, IBM, Teradata, etc).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[sagargoli]

I am sorry, I missed to include that information. I am using Microsoft SSAS Cube.