Re: Joining fact tables?

From: Victor Bos <bos_at_aedius.com>
Date: Thu, 21 Oct 1999 08:57:34 +0200
Message-ID: <7umd1l$pcj$1@news1.xs4all.nl>

Vikas,

As the fact-tables are of a different grain, you shouldn't join them. To retreive info from multiple fact tables, query the fact tables along some of their common dimensions, and join the results. Have a look at kimballs "conformed dimensions" and the data warehouse "bus structure".

Realistically speaking: I don't think there is a maximum amount of dimensions per fact table, although as each dimension will result in the growth of the number of rows in the facttable, so a physical limit will be reached. Furthermore, I question whether a starscheme with dozens of dimensions will be well-designed.

Victor
--

                         _      "This is only a small vehicle for a man,
        _        ,-.    / )      but a giant car for a Suzuki."
       ( `.     // /-._/ /
        `\ \   /(_/ / / /        Victor Bos
          ; `-`  (_/ / /         Aedius IT-diensten
          |       (_/ /          Zeist, the Netherlands
          \          /
           )       /`            business hp: http://www.aedius.com/

    jgs   /      /`              private hp:

http://members.xoom.com/Victor_Bos/

Vikas Agnihotri <vikasa_at_writeme.com> wrote in message news:7ukj2e$75q$1_at_nnrp1.deja.com...
> Transforming a complex OLTP system (400+ entities) into a DM model would
> lead to dozens (?) of dimension and facts.
>
> Each fact table could contain information at a different "grain",
> different subject areas for exploration, etc.
>
> 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?
>
> Any comments, experiences appreciated.
>
> Thanks,
> Vikas
>
> P.S: Realistically speaking, what is the maximum number of dimension
> tables that one could have in a "star" schema? 10, 20, ...?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 21 1999 - 01:57:34 CDT