I need advice. I am in the designing phase of building a DW.
Question 1:- My source has (Customer/Customer Emergency Contact and Customer ResponsibleParty data in one source file). What do you guys think should I create one "Dim_Customer" to store all information or create three Dim tables ("Dim_Customer/Dim_EmergencyContact and Dim_ResponsibleParty) and link all tables with Customer id and Link Dim_Customer.CustomerKey to the Fact table?
Question 2:- I have two source tables "Invoice and Invoice_Detail". Should I create two Dim tables "Dim_Invoice and Dim_InvoiceDetail" and link both tables with Invoice# and Link Dim_Invoice.InvoiceKey (SurgetKey) to the Fact table?
I am planning to build a Star Schema DW.
Please, any advice.
Thanks in advance.