Attributes of two dims in an N dim fact table

[Vivarto]

Hi,
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

 

[blom0344]

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

 

[Vivarto]

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.

Tom

 

[blom0344]

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

Ties Blom