0

I have a transaction fact table and product,time and location as a dimension tables. This fact table is sparse so if no Pizzas sold in January there is no record for Pizza in fact table.

When I drill down by product aggregated results for Pizza which is not in the result. But I want it with 0 values as units_sold = 0.

A solution is to join product table to fact table with a left outer join. Then I can get the desired result.

  • But when I cut with another dimension such as location or time, again those products are missing in the result.

  • Outer join provides empty column for other dimensional foreign keys so WHERE clause will remove them again.

How can I solve the problem? (I use ROLAP)

Use join condition is a good idea as some people answered. But I need more general solution.

For example,

Table1

 person      birth year       death year

 a          1950               2006
 b          1952               2008
 c          1960               2007
 d          1953               1990

I want to get year by year count of the people that born between 1950-1953 and died in 2006-2008.

Like

birth = 1950   death = 2006   count = 1
birth = 1951   death = 2006   count = 0
...

Can we handle this scenario by using join conditions and where conditions appropriately.

Thamalu Piyadigama
  • 21
  • 2
  • Move the `WHERE` conditions into the `JOIN` condition. – Nick Nov 30 '19 at 03:05
  • Does this answer your question? [SQL Left Join does not return all results](https://stackoverflow.com/questions/46067701/sql-left-join-does-not-return-all-results) – Nick Nov 30 '19 at 03:06
  • Please provide sample data and desired results. – Gordon Linoff Nov 30 '19 at 03:10
  • Thanks @Nick this will work. However still I like to know is there some general solution that can be implemented in OLAP cubes. – Thamalu Piyadigama Nov 30 '19 at 14:43
  • For example when we have two dimensions and we are perform a range cut and drilldown in each dimension. How can we obtain all the results corresponding to the every drilldown cell in the cube when the fact table is sparce. – Thamalu Piyadigama Nov 30 '19 at 14:49

1 Answers1

0

You want LEFT JOIN, and then LEFT JOIN again. Then conditions go in the on clause. Something like this:

select . . .
from products p left join
     fact f
     on p.product_id = f.product_id left join
     timedim td
     on f.time_id = td.time_id and
        td.month = 'January'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786