Right Outer Join with Fact table

View previous topic View next topic Go down

Right Outer Join with Fact table

Post  akvbigb on Mon Jun 30, 2014 12:24 pm

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...

akvbigb

Posts : 2
Join date : 2013-05-30

View user profile

Back to top Go down

Re:Right Outer Join with Fact table

Post  hkandpal on Mon Jun 30, 2014 2:19 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Right Outer Join with Fact table

Post  ngalemmo on Mon Jun 30, 2014 4:44 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Right Outer Join with Fact table

Post  BoxesAndLines on Mon Jun 30, 2014 5:52 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Right Outer Join with Fact table

Post  ngalemmo on Tue Jul 01, 2014 12:44 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Right Outer Join with Fact table

Post  hang on Fri Jul 11, 2014 6:09 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Right Outer Join with Fact table

Post  nick_white on Sun Jul 13, 2014 8:00 am

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.

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Right Outer Join with Fact table

Post  hang on Sun Jul 13, 2014 4:28 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Right Outer Join with Fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum