Combining data from different fact tables in a query

[Mariusvs]

I have a requirement to combine data from three different fact tables at the lowest level, and I'm not sure how to model this. I'll try to simplify the scenario as much as possible.

The example is about a loyalty program. Customers earn rewards based on purchases and usage events (e.g. swiping their cards at a partner store). There are fact tables for purchases and usage events (FactPurchase and FactUsage). FactPurchase is at a receipt level and not a line item level.

Based on the usage events and purchases, rewards are calculated monthly by customer, store, etc. This information is stored in FactRewards. Each reward has a unique reward calculation number. RewardCalculationID is included in FactRewards as a degenerate dimension and can be a primary key for the fact table.

Logically, there is a one to many relationship between FactRewards and FactPurchases. E.g. one reward calculation could be based on one or more purchases. A purchase contributes to only one reward calculation. The same is true for FactUsage.

There is a requirement to "join" FactRewards to FactUsage and FactPurchases at the lowest level. E.g. If business look at a specific reward calculation, they might want to know which purchases or usage events resulted in this reward. The only way I can think of doing this is to include the RewardCalculationID on both FactPurchases and FactUsage. I guess this can be thought of as degenerate dimensions. This will allow me to join from FactRewards to FactPurchases on RewardCalculationID.

I know Kimball says that fact tables should not be joined to each other directly, but through dimension tables. Technically, using the solution above, I'm joining two fact tables through degenerate dimensions.

If the approach above is corerct, I have another issue. The RewardCalculationID for purchases and usage events are not known at the time FactPurchases and FactUsage is populated. They are only known once the reward is calculated at the end of the month. And not every purchase results in a reward. What value should I put in RewardCalculationID in FactPurchase (which I think of as a degenerate dimension) until the value is known? Maybe NULL or -9?

Is this approach correct? I'd like your comments, please. The scenario is fairly large. I hope I did a good job at describing it.

[ngalemmo]

A degenerate dimension is like any other dimension, so what you describe is fine, however your problem is timing. You don't know the calculation ID when you record the purchases. This is not unlike an order/invoice scenario. Since invoicing occurs later, the invoice line references the order line, not the other way around. Since the reward calculation fact grain does not allow you to reference a single purchase, what you need is either a purchase/calculation bridge table loaded when the rewards are calculated, or store summary information about the purchases in the reward calculation fact.

[Mariusvs]

Hi ngalemmo,

Thanks for the reply.  I'd prefer not to store summary info about purchases in the rewards fact.  Could you please give me some for detail or a reference about the purchase/rewards bridge.  According to my understanding bridge tables are used for modelling many-to-many relationships between fact tables and dimensions tables.  In this instance, it seems you are saying that this will  be a bridge between two fact tables?

Technically, it this a bridge table between the degenerate dims on the two fact tables?

Kind regards
Marius

[ngalemmo]

Yes, it is a bridge between two degenerate dimensions. And while the normal use is between a fact and a dimension, there is no reason it cannot be used to associate facts.

My suggestion to add summarized purchase data to the reward fact was motivated by your reference to loyalty cards. I assumed this is retail data and you are running on an MPP platform and the fact tables are very large. The challenge is you cannot co-locate the two facts and bridge and it is safe to assume the type of query you will be doing will perform poorly. Adding summarized purchase data to the rewards table would circumvent that issue.

Alternately, you can build an separate aggregate of the purchase data with the reward ID (and whatever columns you use to distribute the rewards data, presumably the customer key) so you can co-locate the summary with the rewards fact. You would append to the summary monthly as you process the rewards. You would use that month's bridge data to build the summary. You then would not need the bridge to query the data.

[Sponsored content]