Many-To-Many dimensional model

Question

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

Answer 1

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://www.ralphkimball.com/html/designtipsPDF/DesignTips2002/KimballDT37ModelingPipeline.pdf multivalued dimension vs bridge table: http://www.ralphkimball.com/html/articles_search/articles1998/9808d05.html