## Aggregate data from Fact Table

## Aggregate data from Fact Table

(OP)

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

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

## RE: Aggregate data from Fact Table

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?

## RE: Aggregate data from Fact Table

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