What is Many-To-Many dimensional model in datawarehousing ?
What is Many-To-Many dimensional model in datawarehousing ?
Answer
There are a few things you can do to marry this with a dimensional model / star schema:
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
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)
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.
Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website.
If you are using this website then its your own responsibility to understand the content of the website