Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Aggregate data from Fact Table

Aggregate data from Fact Table

Aggregate data from Fact Table

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

Product   CycleTime
P1            50
P2            75
P3            100
P4            150

ProductGroupID   CycleTime
1                 50
2                 150

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

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:
ProductGroupID  ProductID   CycleTime
1                 111          50
1                 112          75
1                 -999         50
2                 113          100
2                 114          150
2                 -999         150

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.


RE: Aggregate data from Fact Table

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?  

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.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close