Modelling a conformed business dimension

View previous topic View next topic Go down

Modelling a conformed business dimension

Post  gforsmo on Sat Dec 01, 2012 10:43 am

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

gforsmo

Posts : 3
Join date : 2012-12-01

View user profile

Back to top Go down

Re: Modelling a conformed business dimension

Post  gvarga on Sun Dec 09, 2012 2:19 pm

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 )


gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum