modeling fact/dimensions at 2 different grain level

I have a a transactions fact table that shows the number of transactions by product.

In the product dimension table, I have a 'vendor' column which shows the vendor of the product.

I have a second fact table (Revenue fact) that shows the amount charged to the customers.
Because of the business rule, it is impossible to break the amount charged by transaction. The amounts charged to the customer is at the vendor level, not at the product level.

What's the best design method if I want to relate the 2 fact tables? Would I just have to create a vendor dimension table (rolled up version of product)? I was afraid that it would confuse the business users since they would have to select the vendor column from the product dimension, and also select the same vendor from the vendor dimension. Any suggestions?

What 'business rule' are you talking about?

Let me understand this... you have a fact table by product without customer and another fact table by customer without product? And you want to relate the two?

You don't have a transactional fact table with customer/product etc...?

Short answer... you can't associate the two facts. They are far too summarized to be useful.

This goes back to a fundimental rule of data warehouse design... always create fact tables at the lowest level of detail possible. If you had an atomic fact table of sales transactions, you could aggregate it any way you want. Customer/Vendor, Product/Customer, whatever... its easy.

With two incompatible summary tables, there isn't much you can do.

dellsters wrote: Would I just have to create a vendor dimension table (rolled up version of product)?

Yes, I think you should create a vendor dimension, remove the vendor details from the product dimension and have a vendor FK in the first fact table. Therefore the product-vendor relationship is reflected in the fact table and the two fact tables are naturally connected through the common vendor key.

Thanks for the reply. I realized how badly I had described my situation. Let me try to explain it more clearly.

I have a transactional fact table with customer, product, day, etc. The product dimension I just mentioned has the product name, and the vendor name, from where the product was purchased from. It is a one to many relationship between product and vendor in the same dimension.

I have a second fact table (Revenue fact) with customer, day, and vendor, etc. The revenue fact can be related to the transactional fact through customers, but how would I relate them through vendor? The revenue fact is at the vendor grain, but the transactional fact is at the product grain.

I could have the transactional fact relate to the product dimension, and create a rolled up dimension at the vendor level and relate it to the revenue fact table. But then, a user would have to select both dimension tables. Is this the way to go or is there a better design approach?

It's still not very clear...

You have a product and that product can be purchased from many vendors. Ok. What business are you in? Are you simply taking orders and having them fulfilled by these vendors (like Amazon) OR do you inventory product and ship from inventory.

I am assuming the former since it would be very difficult to know a vendor in an inventory situation unless each product/vendor combination has a unique SKU.

If the vendor is a known entity at the time of the sale and if vendor is important to analysis, it should be carried as a FK in the fact as its own dimension. It does not mean, however, that you need to drop vendor (or at least the vendor ID) from the product dimension since, this may be useful, particularly in an Amazon type model, to distinguish different characteristics of a product from vendor to vendor (price, description, condition, etc...).

Mixing vendor in the product dimension will pose some potential problems if not already obvious now. Firstly a product could be purchased from multiple vendors, so this alone will rule out the possibility having them related in the product dimension. Secondly both product and vendor could be eventually type 2 dimensions, even if not required by business at the moment, so having them mixed up will make product dimension unnecessarily too big and more complicated.

I guess the question now comes down to how the product and vendor are related. Well I imagine there should be some kind of procurement transaction fact tables in the business, such as purchase order fact or vendor payment fact where the two dimensions should be related. So from there, the product-vendor correlations will propagate through to other down-stream fact tables like your first fact table.