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 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.
Thanks in advance for the help
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/
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 have one fact table that has columns that are null or have multiple fact tables.
|||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.
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' 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.

> 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.