Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Regarding UNION / UNION ALL on performance for BI / Reporting

4 posters

Go down

Regarding UNION / UNION ALL on performance for BI / Reporting Empty Regarding UNION / UNION ALL on performance for BI / Reporting

Post  karan_das 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
karan_das
karan_das

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

Back to top Go down

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

Post  Mike Honey 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
Mike Honey
Mike Honey

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

http://www.mangasolutions.com

Back to top Go down

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

Post  karan_das 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
karan_das
karan_das

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

Back to top Go down

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

Post  VHF 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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