October 5, 2013 at 7:32 am
HI All,
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
Fact_Issue: Issue_ID,Asset_Group_ID,Status,Priority,St_DT,End_DT
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).
October 10, 2013 at 9:42 am
manickam (10/5/2013)
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).
For point #2, it sounds like you're on the right track; you could create a degenerate dimension that has the issue text in it. You could put it right in the fact table, but since it's probably large I'd consider moving it to a separate dimension table.
http://www.kimballgroup.com/2003/06/03/design-tip-46-another-look-at-degenerate-dimensions/
HTH,
Rob
October 15, 2013 at 6:00 am
Many thanks for the reply.
Need one more suggestion.
As per current requirment Issue can be logged at
1. asset level
2. applicaiton level
3. process level
4. line of business level
5. combination of each of the above entities.
Can i have dimension table for each entity (asset, application, process and line of business)
and a fact table which would have keys of all these dimensions and measures as ratings, score and status..
October 17, 2013 at 8:25 pm
manickam (10/15/2013)
Many thanks for the reply.Need one more suggestion.
As per current requirment Issue can be logged at
1. asset level
2. applicaiton level
3. process level
4. line of business level
5. combination of each of the above entities.
Can i have dimension table for each entity (asset, application, process and line of business)
and a fact table which would have keys of all these dimensions and measures as ratings, score and status..
If the combination of all of these values is a relatively low number, you could create a junk dimension that has all of these values.
Or it's perfectly valid to create separate dimensions for these if the junk dimension would be too large.
HTH,
Rob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply