Fact table design

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 b
ut 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 ty
pes null.
Thanks in advance for the helpIf I understand correctly you basically are talking about which users can ha
ve 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 perm
issions on the cube. I myself am in the learning process - as my name implie
s ;), 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 th
e 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/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Actually what I am looking for is more of a best practice, is it better to h
ave one fact table that has columns that are null or have multiple fact tabl
es.|||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, a
mount of history, and record width.
If you put them in different fact tables, you can still join them by your de
generate key(primary key -ex. claims number), but at least you won't be wast
ing 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 b
ut 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 ty
pes null.
Thanks in advance for the help|||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.

> 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.|||in the fact table, there is no primary key because you have "degenerated' th
e primary key from the source system. It has ceased to become the primary ke
y in the fact table even though the field still exists. So, in general, a cl
aim 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 cus
tomerid was a primary key in your claims system, but now it is not a PK anym
ore 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 wha
t is called the degenerate key in the fact table. The surrogate key is actua
lly 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.

> 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.|||There are a number of different characteristics between medical, dental and
RX drugs that facilitate analysis meaningful for management, diagnosis and p
rocedure codes having the largest variation. I would treat all three as sepa
rate facts and use views an
d virtual cubes for those situations where combining the three makes sense.