Re: Joining fact tables?

Vikas Agnihotri <vikasa_at_writeme.com> wrote in message news:7ukj2e$75q$1_at_nnrp1.deja.com...
> Invariably, there would be a need to combine information from 2 fact
> tables, leading to a join between 2 fact tables and the related
> dimension tables.
>
> Is this a Good Thing? Seems like fact tables are not meant to be joined
> to anything but their corresponding dimension tables.
>
> Wouldnt joining 2 fact tables lead to horrible performance and violate
> the basic point of doing DM?
>

If you include two large fact tables in a single query the performance will be bad. Indeed, the query may never return.

The trick is to use a query tool that is multi-pass sql aware. Typically in OLAP the result set returned to the user will fit on a screen or two. (A main advantage of ad-hoc query capability is getting the information at the level of detail that you want, and not having to wade through 200 pages of data.) So the query tool queries the first star (fact table and dimensions) and returns a small number of rows. If your relational database has star optimizations, this is fast even on million row fact tables. Next, the query tool queries the second star and returns a small number of rows. Finally the query tool (or a ROLAP server) combines the two small result sets using the common dimension values.

To get the full performance advantage of a dimensional datawarehouse, you need to have a RDBMS that performs star optimizations and you need to have a query tool that performs multi-pass SQL. Of the two, it is more important to have a tool that performs multi-pass SQL.

It is a good thing to combine multiple fact tables. Indeed it is a requirement.

-Clark Received on Sat Oct 23 1999 - 19:47:33 CDT