I am not entirely sure how to resolve the issue that i am currently facing. I have email campaign fact table with all the email send to the supporter, FactCampaignTable with columns supporterid, campaignid, openflag,clickflag. We have NoOfClick measure in this fact.
I then have these fact table of all the donation that is given to us in the table named, FactFinancialTransaction with columns specific to this case supporterid, campaignid, donationamount. We have TotalAmount Measure in this fact. Both these table are join to Dimension table Supporter and Campaign
Both table are with big with FactCampaignTable around 20 millions , while FactFinancialTransaction is around 81 millions.
We want to measure the success of the campaign by seeing how many of these supporter who clicked on the email end up donating. So we have to join on supporterid and campaignid to see if a transaction exist in FactFinancialTransaction for any of the row in FactCampaignTable. Other way this translate to is if the NoOfClick and TotalAmount, both measure are not equal to 0 with both Supporter and Campaign in context.
I am now wondering that as we already have these two fact table in the model, i am not too keen to do these at the ETL level by left join and check if it exist or not as this FactFinancialTransaction would need to be scan twice. I was thinking of using GenerateAll calculated table, by joining on supporterid and campaignid using TREATAS, but not entirely sure if that is the best approach and how it will perform:)
So any idea what will be the best possible solution for such case. Another similar example might be like lead conversion where we have all the lead in one table and all the orders in other table where we have join to check if a lead was converter.
