Thank you in advance for you help:
I have two fact tables:
Customer Facts - Has facts like Monthly Balance etc. I know this could be a Customer Balance Fact table but the issues would be the same.
Campaign Facts Snapshot - This holds the current status of all Campaings by Customer and shares the Customer Dimension with the Customer Facts. However, this Fact also has a Campaign Dimension that is not shared.
What I would like to do is apply the Campaign Dimension and in turn filter / group the Customer Facts by the Customers that are in the Campaign. I know this can be done, but not sure how. I believe I could create a Bridge Fact Table, but not sure that's the right approach or not.
Customer Facts:
Customer ID Balance
1 500
2 600
3 300
Campaign Facts:
Campaign Dim Key Customer ID
1 1
2 1
1 2
2 3
3 1
Results I need in the cube whe applying the Campaign Dim and using the Balance Measure
Campaign Dim Balance
1 1100 (from cust 1 and 2 only)
2 800 (from cust 1 and 3 only)
3 500 (from cust 1 only)