Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Suggestion for a star schema

 
   Database Forums (Home) -> OLAP RSS
Next:  Ingres forums and community development  
Author Message
Jean

External


Since: Jun 21, 2007
Posts: 3



(Msg. 1) Posted: Wed Oct 03, 2007 6:06 am
Post subject: Suggestion for a star schema
Archived from groups: comp>databases>olap (more info?)

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.

 >> Stay informed about: Suggestion for a star schema 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
hai - what is difference between fact table and lookup table regards sivakumar.r

Essbase & Datawarehouse - Hi all, at my workplace, we currently have a datawarehouse (DWH) and use essbase to do some analysis. at the moment, a text file is saved from DWH using business objects; which is then loaded into essbase, run calculation then export. I find that proces...

Full time position in MD: Project Manager, Financial Systems - The client company offers a tremendous benefits and relocation package for the successful candidate. The company is stable and long established. Dress code is business casual. The position reports to the Manger of Finance Systems and is responsible for....

SAS Enterprise miner - Hi, i want to implement neural networks in SAS Enterprise miner but the documentation given in SAS help is not sufficient. Can anyone suggest a resource where i can get detailed explanation of the various options available? Thanks in advance. Cheers,..

DMS-E-YV_SGINOLIB, Unable to locate the gateway 'msdbaL.DLL' - Hi there, I have a Win XP machine that I am trying to open Cognos Impromptu reports on over a LAN. I am using Impromptu 6 and can open local reports but whenever I open a report from the LAN I get the following error: DMS-E-YV_SGINOLIB, Unable to locat...
   Database Forums (Home) -> OLAP All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]