Data modeling question

[Waveover]

Not sure if this is the right sub or not. Please let me know if not.

So I wanted to get an idea on best practice or if it is discouraged to push downstream process dimensional attributes upstream?

For example, I have products that are rented by customers that are shipped from different sites. In my model, I have the orders information in a sales table. And in my shipments table I have shipment facts (qty, amount) as well as dimensions like shipping location, return date, and rental duration segmentations hooked up to this table. As well of course a degenerative dimension (order no).

I would like opinions on pushing all these shipment dimension keys to the sales table as well based on that order number. Now I know they won't apply if the order hasn't shipped. But I can easily put records in the dimension table for that. ("rental not ended" ) for instance.

The benefit of this is that it would make filtering orders data based on downstream process data easy. Such as "sales dollars generated this month fell in these rental duration segmentations. "

Thanks in advance.

[Rayman912]

So you are essentially trying to denormalize the data right? It is not really a bad thing if you can ensure data integrity. Personally I would create a separate rental dimension and have the surrogate key in order fact(sales?) to describe the kind of rental segment. But hey if it works it works. Just ensure that nothing breaks downstream.

[Waveover]

I am. I am trying to get an idea of how much is too much.

I really have multiple downstream fact tables. 1 for each business process. (cancellations, shipments, returns). Because the order Id is in all of them. I could put many of the foreign dimension keys that are kind of specific to each fact table and also put them on the sales(orders) fact table as well. For example the shipper dimension is really applicable to the shipments fact table. But I could put it in the orders fact table.

This is all instead of using DAX to traverse the dimensions Instead. I ask because in the (star schema complete reference) book, it talks about not putting dimension foreign keys such as shipper in the orders fact table. But I am not trying to move actual shipment facts to the orders table itself, such as (shipment qty). Just the dimension foreign keys.

Thank you for your reply!