2

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 purchases but no revenue.

Each fact joins the 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 an 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

corycorycory
  • 1,448
  • 2
  • 23
  • 42

1 Answers1

3
  1. Create a union of your fact tables. There will be mismatched fields, but that is ok. enter image description here enter image description here
  2. Perform data blending on the connection to bring in additional dimensions(Not shown in the example)
  3. Build the view

enter image description here

Jose Cherian
  • 7,207
  • 3
  • 36
  • 39
  • Just a question here: last time I looked and did a JOIN over common dimensions with 2 fact tables, it lost data. I am pretty sure Tableau also stated that. Are you saying my knowledge here is out of date? – thebluephantom Aug 07 '19 at 20:10
  • @thebluephantom, the answer uses union and not join. As you can see in the screenshots, common dimensions are not a requirement. – Jose Cherian Aug 07 '19 at 20:49