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

Understanding Multiple Fact Tables


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)





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 



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.




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.