I have a fairly simple data model which consists of a star schema of 2 Fact tables and 2 dimension tables:
Fact 1 - Revenue
Fact 2 - Purchases
Dimension 1 - Time
Dimension 2 - Product
These tables are at different levels of granularity - meaning a given date could have many rows across many products. A specific date and product may have revenue, but no purchases. Likewise, it may have purchased but no revenue.
Each fact joins both dimensions, which contain additional detail such as the product name, product category, etc.
What I would like to do is combine these two facts such that I can report revenue and purchases together (example, by date, by product, or by date and product combined):
I can get very close with data blending, however, the issue I run into is that data blending only supports a pseudo 'inner-join'. As you can see, if either of these data sources is specified as primary then dates without purchases/revenue will cause rows in the secondary source to fall off.
What is the best way to blend this data without causing records to fall off