dimensional model best practice

[scabral]

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

[M. Khan]

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.