We do have a reqirement to design a OLAP model for consolidating the issues identified on ASSETS from various sources\system.
Let us take one sample record;
Issue | Asset | Status |Priority |St_DT |End_Dt
ISS1 | ASS1,ASS2 |Open |High |1-Jan-13 |NULL
We have designed the below model for this scenario:
Dimension_Asset: Conformed dimesion for assets.
Dimension_Asset_Group: Since each issue can have multiple assets we have designed a group table.
Dimension_Date: Conformed and role playing dimension for start and end date
Please clarify the below doubts ( if at all the above model is good to go)
1. As of now client is not intersted in the history of the issue, if at all in future it requires then is it good to go with this model, by have multiple entries in the fact_issue table for same issue. Also on top of it can we build a accumulating snapshot fact table.
2. Actually issues will be associated with many deatiled text and lattributes. Do we need to capture this information in the FACT_ISSUE table as per this model. As of now we planned to move those information to another table called Dimension_Issue (though we cannot call it as dimension since its not repitative data in the fact table).