Contact US

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!

*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

Attributes of two dims in an N dim fact table

Attributes of two dims in an N dim fact table

Attributes of two dims in an N dim fact table

I have a fact table with 5 dimensions. Two of them are product and warehouse.
There are textual attributes of the product / warehouse combination
Clearly these can't be held in either of the two dimension tables
I have created a ProductWarehouse table to hold the attributes. Not sure what type of table this would be, Bridge?

The idea is to relate the ProductWarehouse attributes to the fact data when reporting.

Should the ProductWarehouse table be joined directly to the fact or to the dimensions?

(The quick and dirty approach would be to hold the textual attributes on the fact but this feels completely wrong - any thoughts?)

Assuming the ProductWarehouse table is a viable approach, should this table use surrogate keys? If so then there is an additional complication in that the product dimension is SCD2.

Feel this not an uncommon modelling problem. Is there a best practice approach?

Thanks, Tom

RE: Attributes of two dims in an N dim fact table

I'd say this is no bridge as it does not fit into many to many resolvation. Any drawbacks by using the composite product-warehouse key for an additional product-warehouse dimension? Like generating a cartesian product between product and warehouse (all combinations), lookup keys and add attributes to 'real' combinations?

Ties Blom

RE: Attributes of two dims in an N dim fact table

Ah, Mr Blom, read a lot of your posts.

Yes, the composite dimension idea came to me last night but its good to hear it from some one else. What has happened today is that the ProductWarehouse has become a fact as I now have a measure that is at that grain (stock in transit). That being the case I really couldn't have the attributes in a fact table so I was forced down the path of the composite dimension. It makes a lot of sense and is applicable elsewhere in the the DW.

The BI layer is Cognos so having the ProductWarehouse table joining to the dimensions of the Inventory table resulted in a stitch query just to get a few attributes.

Knew it wasn't right but hadn't come across the composite dimension idea before.


RE: Attributes of two dims in an N dim fact table

Cognos .. Stitch queries.. Love 'm. Still king of the hill in resolving multifact scenario's.

Ties Blom

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