Modelling a conformed business dimension
2 posters
Page 1 of 1
Modelling a conformed business dimension
Hi!
We are in a project to create data warehouse solution for accident information. One of the challenges we have is how to model the business that is involert. An accident can happen in a business, the person injured may be employed by another company. The building that the accident happens to be owned by a third company. We've found that we can have 9 different business types. A business can also be a subcontractor to a company. We want to use the same business dimension for all cases. We see that we have different roles here. The question is how to model this so that we are able to create reports in the best possible way so that we do not have adverse effects. Should we create a bridge table, or should we create this in cubes with roles? Is there anyone who made something similar, please give us some tips.
Regards Geir F
We are in a project to create data warehouse solution for accident information. One of the challenges we have is how to model the business that is involert. An accident can happen in a business, the person injured may be employed by another company. The building that the accident happens to be owned by a third company. We've found that we can have 9 different business types. A business can also be a subcontractor to a company. We want to use the same business dimension for all cases. We see that we have different roles here. The question is how to model this so that we are able to create reports in the best possible way so that we do not have adverse effects. Should we create a bridge table, or should we create this in cubes with roles? Is there anyone who made something similar, please give us some tips.
Regards Geir F
gforsmo- Posts : 3
Join date : 2012-12-01
Re: Modelling a conformed business dimension
Hi,
You are right: use 1 conformed dimension!
You can solve the problem very easily in relational databases:
-Create just 1 dimension table
-Create 8 so called „role playing” dimensions as relational views. The views are based on the physical table, the content
is the same, in the name of a view you can express the role. Connect these views to the fact table
-The foreign key column names have to reflect to the view names.
-Don’ t forget to insert dummy row to the dimension table: N/A, unknown or sg like this
You can use this model in BI tools as well! ( Simple star shema )
You are right: use 1 conformed dimension!
You can solve the problem very easily in relational databases:
-Create just 1 dimension table
-Create 8 so called „role playing” dimensions as relational views. The views are based on the physical table, the content
is the same, in the name of a view you can express the role. Connect these views to the fact table
-The foreign key column names have to reflect to the view names.
-Don’ t forget to insert dummy row to the dimension table: N/A, unknown or sg like this
You can use this model in BI tools as well! ( Simple star shema )
gvarga- Posts : 43
Join date : 2010-12-15
Similar topics
» Conformed Dimension Partial Set of Attributes
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» Modelling two independent business processes related to a single event
» Modelling situation with Task, Person and Document in unpredictable business processes
» Conformed dimension or Degenerate dimension?
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» Modelling two independent business processes related to a single event
» Modelling situation with Task, Person and Document in unpredictable business processes
» Conformed dimension or Degenerate dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|