dimensional model best practice

View previous topic View next topic Go down

dimensional model best practice

Post  scabral on Sat Aug 04, 2012 11:00 pm

I'm in the beginning stages of building a dimensional model for an insurance company who only deals with Homeowner's insurance. We are using SQL server 2008 R2 version.

We are basically going to build a Policy fact table and Claim fact table. Each table with have their own dimensions, but some dimensions will overlap like date and customer.

My question is what is the best practice for creating the database to store the fact and dim tables? Is it better to create a separate database for the Policy Fact table and it's dimensions and then another database for the Claims fact tables and it's dimensions, even though some dim tables will be redundant? Or is it better to just create 1 database and has both facts and all dim tables living in that database (so just one date and one customer dim table in the database instead of 2 in separate databases)?

Just trying to get an idea on what best practice is regarding this situation.

thanks

scabral

Posts : 58
Join date : 2012-05-02

View user profile

Back to top Go down

Declare common dimensions as conformed

Post  M. Khan on Sun Aug 05, 2012 7:30 am

Build common dimensions once and use them for both fact tables. When any dimension is used with more than a single fact table is called conformed dimension. It is the foundation for building Enterprise Data Warehouse. The conformed dimension also enable to report from two fact tables as well.

In my opinion, both facts along with dimensions can live happily in a single database. In BI layer, BI modeler can create either seperate or combined semantic layer for policy and claim for reporting depending on the need. I would suggest you to create seperate staging database so that the two distinct areas are delineated clearly for procesing, auditing and security needs.

You may need to explore the possibility to design fact table as as accummulating fact table to accommodate both policy and claim in a single fact table. It would reduce the work as well as make reporting simpler.

M. Khan

Posts : 11
Join date : 2012-07-24

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