Regarding UNION / UNION ALL on performance for BI / Reporting

View previous topic View next topic Go down

Regarding UNION / UNION ALL on performance for BI / Reporting

Post  karan_das on Mon Mar 18, 2013 3:46 am

Hi ,

We have a reporting tool that needs to query the dimensional model in a way that provides the best performance as is possible.
We have multiple FACT tables in the dim mod which needs to be queried and the results collated together for Reporting.
Example :

FACT I
ID ColA ColB ColC
FACT II
ID ColA Col D ColE

Wanted :
REPORT
ID ColA ColC ColD

Wanted to know if the UNION operator will give better performance than a inner join between the fact tables (I believe that joins are a bad idea between fact tables).
Your thoughts on this ?
Did not know where to post this query as this issue was faced by reporting.
Regards,
Karan
avatar
karan_das

Posts : 10
Join date : 2013-02-26
Age : 31

View user profile

Back to top Go down

Re: Regarding UNION / UNION ALL on performance for BI / Reporting

Post  Mike Honey on Mon Mar 18, 2013 11:33 pm

Hi Karan,

Attempting to either Union or Join Fact tables is unlikely to provide "the best performance as is possible".

I would review the dimensional model with a view to creating a new Fact that combines the measures at the correct grain to support the reporting requirements.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Regarding UNION / UNION ALL on performance for BI / Reporting

Post  karan_das on Tue Mar 19, 2013 8:43 am

Hi Mike ,

Currently, we do not have access rights to modify the analytical model. We have to make do with whatever we can under the present circumstances.
Would you say that UNION is a better idea ?

Regards,
Karan
avatar
karan_das

Posts : 10
Join date : 2013-02-26
Age : 31

View user profile

Back to top Go down

Re: Regarding UNION / UNION ALL on performance for BI / Reporting

Post  VHF on Wed Apr 17, 2013 7:19 pm

Fact tables should not generaly be joined directly, but can be joined on common attributes in conformed dimensons.

I frequently use subqueries when needing to report on data from multiple fact tables.

Using your example it would look something like this:

SELECT COALESCE(sq1.ID, sq2.ID) AS [ID], COALESCE(sq1.ColA, sq2.ColA) AS [ColA], sq1.ColC, sq2.CoD
FROM (
_____SELECT ID, ColA, ColC FROM [Fact I]
) AS sq1 --subquery_1
FULL OUTER JOIN (
_____SELECT ID, ColA, ColD FROM [Fact II]
) AS sq2 --subquery_2
ON sq2.ColA = sq1.ColA

The full outer join is to support cases where some data exists only in one of the fact tables; the coalesce statements in the main select allow returning the data from subquery_2 in cases where that data is not returned by subquery_1.

Normally the subqueries would be more complex and include joins on various dimensions, and the final ON clause that joins the two subqueries together would be based on one or more dimension attriubtes. Example:

ON sq2.ProductCategory = sq1.ProductCategory AND sq2.ProductColor = sq1.ProductColor




VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Regarding UNION / UNION ALL on performance for BI / Reporting

Post  ngalemmo on Thu Apr 18, 2013 2:38 am

A UNION ALL will, usually, perform better than a join. A UNION will not, as it returns distinct rows.

But, as with anything, it depends. The aggregation step prior to joining can often significantly reduce the number of rows, potentially giving the advantage to the JOIN approach. Your best option is to try both.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Regarding UNION / UNION ALL on performance for BI / Reporting

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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