I'm building a DW just like the one from AdventureWorks. I have one fact table called FactSales and theres a table in the database called SalesReason that tells us the reason why a certain costumer buys our product. The thing is there are two types of costumers - the resselers and the online customers - and only the online customers have a sales reason linked to them.
First of all, can I vave to Dimension tables pointing to the same FK in the Fact? Like in my case - Sk_OnlineCustomer and SK_Resseler both point to FK_Customer. Their Id numbers don't overlap-
And Second, Should I build a reason dimension, link it to the fact and have a FK that most of the times is null or with a "dummy reason"?
Should I just put the reason in the fact sales without it being a key, just like a technical description that is nullable?
Should I divide the fact in two fact tables with one for the resselers and one for the online customers? But even in that case, I would have some costumers that don't answer to the reason, so the fk_reason would be null in some of its appearences in the new fact_Online_Customer.
In a solution I saw from the adventure works tutorial, it's created a new fact table called fact_reason. It Links the factSales with a DimReason. That looks like a good solution, but I don't know how it works, because I never lerned in my classes that I could link a fact to a fact, thus I wouldn't be able to justify my option to my teacher.
If you could explain it I would appreciate it.
Thanks!
Please find my comments for your questions:
First of all, can I vave to Dimension tables pointing to the same FK in the Fact? Like in my case - Sk_OnlineCustomer and SK_Resseler both point to FK_Customer. Their Id numbers don't overlap-
Yes the dimension in this case would be Dim_Customer(for eg) and this could be a role playing dimension. You can expose reporting views to separate the Online customer and Reseller customer
And Second, Should I build a reason dimension, link it to the fact and have a FK that most of the times is null or with a "dummy reason"?
Yes it would make sense to build a reason dimension. In this you can tag a fact record to the reason
Should I divide the fact in two fact tables with one for the resselers and one for the online customers? But even in that case, I would have some costumers that don't answer to the reason, so the fk_reason would be null in some of its appearences in the new fact_Online_Customer.
I would suggest you keep one fact as your business activity is sales, you can add context to it, online or reseller using your dimensions. If you would prefer you can have separate Dim_Sales dimension to include the sales type and other details of the sales which you cannot include in the dact
To summarise you probably might be well off with the following facts:
Fact_Sales linked to Dim_Customer Dim_Sales Dim_Reason (This can also may be go to the Dim_Sales) Dim_Date(always include a date dimension when you build a DWH solution)
Hope that helps...