Right Outer Join with Fact table

Hello, I have a requirement where I need to pull in all the products into the report and show their performance. The fact table contains millions of records for each reporting date. Not every product will have records in the FACT table for every reporting date. So, we need to enable right outer join from the dimension to the fact table. this is taking hell lot of time for certain product types when we are using a filter on the product type.

We have indexed the product dimension attributes. The FACT table is partitioned by date. the FACT table contains more than 3 million records per reporting date. there is a date table linked to the fact table. a date filter is passed through the date dimension table while querying the product information.

My question is, what is the recommended design to report all the products even though there is no record in the fact table for a given date? Right outer join from product table to fact table is taking long time to return results. So, how do we make it better to improve the reporting performance?

Thanks a lot in advance...

Hi,

you may need to supply us the Explain plan if you are using Oracle. Does you query works fine if you do a right outer join and does not apply a filter criteria. Do you have index on the product type filter criteria.


thanks

Himanshu

Try aggregating the fact first before joining to the dimensions. You should be able to do that with a sub-select.

Unless you partition prune, it will take a long time.

akvbigb wrote:So, we need to enable right outer join from the dimension to the fact table. this is taking hell lot of time for certain product types when we are using a filter on the product type.

My question is, what is the recommended design to report all the products even though there is no record in the fact table for a given date? Right outer join from product table to fact table is taking long time to return results. So, how do we make it better to improve the reporting performance?

To your question, under such situations, an outer join is appropriate.  Having a row for every day for every product sold or not is not a reasonable approach.

As far a performance for the filter, again, a sub-select may help, such as SELECT fact stuff FROM facts WHERE product_key IN (SELECT product_key FROM product_dim WHERE product_type = whatever). Do a separate outer join to the dimension to get attributes.

I had similar problem showing all budgeted and soled products on one report. Using 'right outer join' and 'not exists' on a big fact table would be a performance killer. The fact table is structured to (left outer) join on dimension tables to get unique set of other dimension attributes for frequently repeated dimension surrogate keys. However when you make a right-outer join, each dimension record will generate numerous fact rows because of one-to-many join, which is a performance hindrance.

My solution to this problem is to turn right-outer join into left-outer join and not-exists into exists or inner join. But before scripting your query, you need to let ETL work out a list of all products that have not been soled on each day, and indeed, it's another fact table with only dimension keys and budgeted values or zero sales values. It would take a while using not-exists on a big fact table on initial load, but for on-going load, you only deal with delta to add new products unsold for current date.

Now with the unsold product-date fact table in place, you can just left outer join the product dimension or budget fact table as we normally do, and for missing unsold, you can use union-all to include unsold products and other relevant dimensions with budgeted values and nulls for inapplicable transaction values. In this way, your run-time query will be a lot faster, a perfect case of leveraging ETL to boost query performance.

Is this not the same design pattern as the promotion coverage issue that Kimball talks about? You create a coverage fact table showing all that products that could be sold on any one day, query this and your sales fact table independently and combine the results.

The concept is the same as Kimball's coverage fact, but with a twist, turning not-exists into exists combined with union-all to improve the performance. The performance may not be a major issue for promotion coverage if the sales fact table is small.