cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Greenwoodr Regular Visitor
Regular Visitor

Understanding Multiple Fact Tables

Hi

I would be grateful for some assitance with the following data model design query.......

 

 

I have the following structure 

 

1) Fact Table 1 Sales Smiley Sad1 row per sale) : Contains details of sales with links to date , customer and product dimensions  (amongst others in tradditional star schema model)

 

2) Fact Table 2 Campaigns: (1 row per customer per campaign sent) Contains details of marketing campaigns received by a customer  with links to the customer dimension and a campaigns dimension sumarising the campaigns details (each campaign being split into diffferent treatments)

 

 

 

image.png

I would like to get the sales results (How many people were mailed, how many responded etc) for specific campaigns but am struggling to work out how I do this and what changes are needed to the design to allow me to achieve this. Having read this it seems to be called the chasm trap 

 

 

For reference I can calculate the answer in SQL by pulling out who was mailed in each campaign and searching for the relevant sales in the sales table. I could import this into PowerBI and report of that but it would mean sales data existing in both Fact Tables and have a feeling that there is a better way?

 

Does anyone have any suggestions as to the best way to approach this?

 

Many Thanks 

Richard

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Understanding Multiple Fact Tables

Hi @Greenwoodr,

 

Could you please share your sample data to me? You can upload your file to one drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.