Dear newsgroup readers,

I am working on suggesting a data warehouse design for an insurance
company. The business logic is that there are Customers and each
Customer has a Captive. Also, each Captive has a Cession.

On the technical side, there is at present an OLTP database for the
Front End which is highly normalized. For reporting purposes, it will
be not very efficient reporting off this database, and that is why I
would like to implement a star schema in a separate reporting database
(MSSQL 2005).

The OLTP database looks like this:

tblCustomer
---------------
CustomerID (PK)
CustomerName
....

tblCaptive
---------------
CaptiveID (PK)
Name
CustomerID (FK1)
....

tblCession
-------------
CessionID
CaptiveID (FK1)

For my star schema design, I would like to have Cession as the fact
table, and Captive and Customer as dimension tables, as follows:

factCession
--------------
CessionID (PK)
CustomerID (FK1)
CaptiveID (FK2)
AmountCommision
.....

dimCaptive
-------------
CaptiveID (PK)
Name
.....

dimCustomer
----------------
CustomerID (PK)
Name
.....


Is this design a good choice when one considers the relationship
between Customer, Captive and Cession? I.e. each Customer has one or
more Captives and each Captive has one or more Cessions. Please have a
look and let me know where there could be improvements.

I expect that the end-user will want to report on the measures from
Cessions and view it by Customer. There is also the possibility that
they should be able to drill-down from the Customer level to the
Captive level for viewing Cessions.

Thanks in advance.