0 votes
1 view
in BI by (13.2k points)

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):

example of data model

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 

1 Answer

0 votes
by (36.4k points)
  • Create a union of your fact tables. There will be mismatched fields, but that is ok. enter image description here enter image description here

  • In order to bring in additional dimensions(Not shown in the example), Perform data blending on the connection

  • Build the view

enter image description here


 

...