Fct clarification

View previous topic View next topic Go down

Fct clarification

Post  vermarajiv on Mon Feb 15, 2010 9:33 pm

Hi all,

For an insurance company, I am trying to come up with the dimensional model for claims. A claim has attributes like

Date of Loss
Date Reported
Status Date
Cause of loss
Authority Contacted
Claimant
Reported by..... etc

I can see that claim has some attributes that can go in fact(factless) table and others in claim dimension. But if I do that there is 1:1 relationship between these two and I cannot see a reason why I should separate them into two tables. How do you guys justify separating them into two tables?

vermarajiv

Posts : 7
Join date : 2009-12-14

View user profile

Back to top Go down

This is how I did it

Post  Jeff Smith on Tue Feb 16, 2010 7:12 pm

I work for an insurance company. The Key for the Claim is the Claim Number. The Claim Number at my company contains logic. It contains the Date the Claim was received, the Batch number in which it was loaded into the system, the sequence number, etc. Instead of carrying the claim number, I broke it up into 2 dimension tables. A Date Dimension for the Received Date and a second table to store the unique combinations of the Batch and Sequence. It saved a lot of space. And the Claim Number can be put back together by the reporting tool.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Fct clarification

Post  BoxesAndLines on Tue Feb 16, 2010 11:35 pm

Isn't the grain of your fact table at the claim line level? In that case you don't have a 1-1 with a claim dimension. Don't automatically assume you will have a claim dimension. It may be that you do not need one.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fct clarification

Post  vermarajiv on Wed Feb 17, 2010 10:48 am

Yes, the grain of fact table is at the claim line level. Maybe I am looking at it differently. Following are the attributes of my claim :

Claim Number (No Logic in it)
Cause of Loss
Cause of Loss event
Event Catastrophe
Authority Contacted
Assigned State
Attorney represented
Adjuster
Claim Status
Date Reported
Reported By
Relationship
Date of Loss
Status Date...etc

The facts like Date of Loss, Date Reported are at the claim level and so is the other information like attorney Represented, Adjuster, Cause of Loss, Claim Status etc and hence I was thinking it has 1:1 relationship. Are you saying that I should have separate dimensions for these non fact attributes (dim for State, dim for adjuster, dim for Attorney, dim for relationship) ?

Thanks

vermarajiv

Posts : 7
Join date : 2009-12-14

View user profile

Back to top Go down

Re: Fct clarification

Post  ngalemmo on Wed Feb 17, 2010 1:20 pm

Yes. That is what B&L is implying. You need to figure out what attributes are important for analysis and break those out into dimensions. You may be left with a few 'nice to know' attributes, such as 'reported by' that may show up on reports, but not used to select or group in analysis. Those may wind up in a 'claims' dimension just because it is easier to do and you don't want to lose the information, but it would not be critical in reporting. You want the key dimensions to be as small as possible to improve performance of queries.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fct clarification

Post  Mj1978 on Wed Mar 10, 2010 8:17 pm

I joined this Group a couple days back and I am really looking forward to pursue my career as a DWH architect. Please advise in case I am following a wrong track.

This is my first post. :-))

In the Insurance domain, CLAIM is one of the most important intersection of various Dimensional attributes of the Insurance World.
Any CLAIM line submitted has all the dimensional attributes plotted on that
Dimensions/facts represented on a claim line can be:
DIMENSIONS:
1. The Submitter(MEMBER)
2. The Authority to which the claim is submitted (INS_COMP)
3. Event of catastophie (QLFY_EVNTS)
4. Other refernce attributes required on the claim line
FACTS:
4. Relationship between member and Ins comp(CONTRACT)
5. Other Facts

So, I would suggest to model the CLAIM as a fact instead of modeling it as a Dimension because CLAIMS is primarily the single
largest FACT table in a insurance Domain. I work for HEALTHCARE INSURANCE Enterprise Data Warehouse.
Our CLAIM is the single largest fact containing
MEMBER
CUSTOMER
PROVIDER
SUBSCRIBER
DIAGNOSIS
PROCEDURE
PROVIDER_AFFILIATION
MEMBER_CONTRACT
CONSUMER_OFFER.


Sometimes its a better Idea to divide huge facts into two smaller facts like we did for CLAIM.
As per the source system we use we have more than 180 attributes attached to a CLAIM Servive line and having all these
Attributes in a Single CLAIM FACT table will have a very bad impact on the performance. Instead we have two FACT tables as
CL_SVC and CL_SVC_EXT with the latter table containing attributes lest often used for reporting or building further
User view layers.

I hope it explains.

Thanks
Manik

Thanks
Manik
avatar
Mj1978

Posts : 8
Join date : 2010-03-10

View user profile

Back to top Go down

Re: Fct clarification

Post  AKris on Wed Mar 10, 2010 9:16 pm

Even though you see them as 1-1 , you can come up with some of these dimensions along with an accumulating snapshot table with grain as one row per claim transaction. Your claim Nr could be a DD, if it uniquely identifies a claim transaction.
You will have a role playing date dimension (taking claim reported date,claim open date,claim payment date,claim close date etc).
Some of the other dimensions could be claimant dimen(name,address etc;),Coverage Dim (describing coverage related attributes),Claim Status Dim (having various statuses),Claim Agent(adjuster,authority both can be under a role playing dim if they both belong to same org),Insured Party (if different from claimant),loss type dimension(if you can catogerize the types of losses).
Some of your facts can include Claim Amount,Original Paid Amount and any others you notice.

AKris

Posts : 6
Join date : 2010-02-28

View user profile

Back to top Go down

Re: Fct clarification

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum