Data Model Query

[knotty]

Hi All, I'm hoping someone can offer some assistance with a query relating to a data model please.  Apologies if this is really basic, I've not long studied Star Schemas and I'm trying to follow best practice:

Imagine for example the typical Star Schema model including tables like dim_Customer, dim_Product, dim_Date, fact_Sales. 

I want to bring in another table containing data of all customer contact instances (e.g. marketing, tele canvassing, mailshots etc.) which, is essentially a different one to many relationship to dim_Customer.  Does anyone have any suggestions on the best practice method of doing this please?  All the videos and guides I see tend to just append InternetSales with ResellerSales and put all these into one fact table but, in my example above, it's completely different data and I can't see how I'd append it to the existing fact_Sales table.  Am I right to assume that one dimension table should only ever really link to one fact table else I run the risk of indirect many to many relationships?

Any help would be much appreciated, thanks!

[knotty]

Thanks very much for your response - I'll go ahead and create a second fact table.

[AmiraBedh · Accepted solution]

I agree with you : in a typical star schema, one dimension table should only be linked to one fact table to avoid indirect many-to-many relationships, which can lead to issues with data accuracy.

In your case, since the customer contact instances data is a different one-to-many relationship to dim_Customer, it would be recommended to create a new fact table to store this data instead of appending it to the existing fact_Sales table.

You can create a fact table called fact_CustomerContact with its own set of measures and foreign keys to the appropriate dimension tables such as dim_Customer, dim_Date, etc.

When designing the fact_CustomerContact table, you should consider the granularity of the data and what measures you want to track. For example, you may want to track the number of contacts per customer, the type of contact, the date of the contact, the success rate of the contact, etc. You can then create appropriate measures and dimensions in the fact_CustomerContact table.

Once you have created the fact_CustomerContact table, you can link it to the appropriate dimension tables using their foreign keys.

This will create a separate one-to-many relationship between dim_Customer and fact_CustomerContact, which will not interfere with the existing one-to-many relationship between dim_Customer and fact_Sales.

Overall, the key is to maintain the integrity of the star schema and avoid indirect many-to-many relationships. Creating a new fact table for the customer contact instances data will allow you to store and analyze this data separately from the sales data while still maintaining the star schema structure.

[knotty]

Thanks very much for your response - I'll go ahead and create a second fact table.

[YukiK]

If understand your description correctly, that table is your another fact table. Power BI allows multi fact table analysis, meaning, you can have multiple fact tables in a model. Shared dimensions between fact tables allow you to analyze data across multiple fact tables. I think this is the direction you were looking for?

Consider giving it a thumbs up and accept as solution if this is helpful!