Search code examples
database-designdata-warehousedimensions

Many-To-Many dimensional model

Folks,

I have a dimension table called DIM_FILE which holds information of the files we received from customers. Each file has detail records which constitutes my FACT table, CUST_DETAIL. In the main process, file is gone through several stages and each stage tags a status to it. Long in a short, I have many-to-many relationship. Any ideas around star schema dimensional modeling. A customer record only belong to a single file and a file can have multiple statuses.

FACT
----
CustID
FileID
AmountDue


DIM_FILE
--------
FileID
FileName
DateReceived

FILE_STATUS
-----------
FileID
StatusDateTime
StatusCode
Solution

  • There are a few things you can do to marry this with a dimensional model / star schema:

    1. Build two stars (possibly, they'd end up in different datamarts). One has FACT as the fact table, the other star has FILE_STATUS as fact (you can consider it as a transaction grained fact table). To make this work, I'd probably denormalize and add CustId to FILE_STATUS too
    2. Since you are dealing with FILE_STATUS, you could turn FACT into a accumulating snapshot fact table. In this model, you'd have a separate set of extra columns in FACT to record all information belonging to each status transition. At least, you'd have a column to the date/time dimension to record when a particular status was reached. In your ETL, you'd have to UPDATE the fact table to record how a file progresses through states. This design only works if the number of statuses is finite and relatively small. In addition, there should be a more or less clear path of status progressions (like with a customer order: received -> picked -> packaged -> shipped -> payed)
    3. Make a so called multivalued dimension for the statuses: FACT would get a key to this new dimension, and this new dimension would actually represent a collection of statuses that apply to a row in the FACT table.
    4. You could have a bridge table (although I don't think that applies to this subject, not sure)

    References:

    Accumulating snapshot:http://blog.chrisadamson.com/2007/03/accumulating-snapshot-use-accumulating.html

    multivalued dimension vs bridge table: http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/multivalued-dimension-bridge-table/