Regarding UNION / UNION ALL on performance for BI / Reporting

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

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

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

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

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.