Design for star schema

[Database Administrator]

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.

[Database Administrator]

On Oct 5, 8:30 am, Jean wrote:
> 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.

You design is right if Cession is the fact table. However, if your
users would like to analyze all related cessions for a cession group
(or a Captive) considering all possible relationships through
Customer, you might want to take a look at more complex models (see my
many-to-many paper here: http://www.sqlbi.eu/manytomany.aspx).
Nevertheless, I'd suggest you to make a simple star schema in V1 (what
you're doing now) and only if customer asks more, then you might
consider more complex design (it implies performance issue... so do
that with care).

Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo