sql server data warehouse: I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables

My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null

If I understand correctly you basically are talking about which users can have access to view which type of claims. Is this correct?

If this is your question then I think you can solve via using roles and permissions on the cube. I myself am in the learning process - as my name implies ;), but based on my so far understanding, this should do the trick.

Please let me know if this solves your problem? Kindly post your reply to the newsgroup.

By copy of this mail to the experts, I would like to ask a related question: Is it a good or bad design practice to have multiple fact tables?

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/

If you include all measures related to all three types of claims, then your fact table can get huge depending on how much claims activity you process, amount of history, and record width.
If you put them in different fact tables, you can still join them by your degenerate key(primary key -ex. claims number), but at least you won't be wasting space.


----- shache wrote: ----

I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables

My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null

in the fact table, there is no primary key because you have "degenerated' the primary key from the source system. It has ceased to become the primary key in the fact table even though the field still exists. So, in general, a claim number was the primary key in your claims source system, but now it is just an ordinary field

In the dimension table, the primary key is no longer valid anymore because a surrogate key is created. The surrogate key is a meangless number. So a customerid was a primary key in your claims system, but now it is not a PK anymore in your dimension table. You have just a number(surrogate key) to describe your product and that is your new PK

I'll understand if it is still confusing


primary key is not the same as surrogate key. primary key is the same as what is called the degenerate key in the fact table. The surrogate key is actually the primary key in the dimension table

----- Learner wrote: ----

Hi

A silly question from me

Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article

Regards

[quoted text, click to view]