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

Joining facts to multiple levels in the same dimension

Joining facts to multiple levels in the same dimension

Joining facts to multiple levels in the same dimension

The following thread was posted in June 20008 (thread353-1479467: Joining facts to multiple levels in the same dimension)

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

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: Joining facts to multiple levels in the same dimension

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

RE: Joining facts to multiple levels in the same dimension

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


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! Already a Member? Login

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