Degenerate dimension question.

View previous topic View next topic Go down

Degenerate dimension question.

Post  dmjk2011 on Tue Oct 18, 2011 11:24 am

Hello,
I am modeling a Claims datawarehouse with claim header and its corresponding claim Lines. Different facts are available at both levels.

Approx size of the claim header fact table would be close to 2B rows and claim line fact would be 3B rows.

Would it make any sense to create a separate Claim dimension instead of treating it as a degenerate dimension into both fact tables? (If we create a separate dimension, it would essentially grow at the same rate as the claim header fact table (i.e., 2B rows) and hence I'm not in favor of it.)

However, if we want to get a complete picture of a claim, we will have to join Claim Header Fact and Claim Line Fact on the claim identifying key. Is that the right way to go?

All comments are appreciated! Thank you!

dmjk2011

Posts : 7
Join date : 2011-05-09

View user profile

Back to top Go down

Re: Degenerate dimension question.

Post  ngalemmo on Tue Oct 18, 2011 1:33 pm

Why do you need to combine claim fact and claim line fact to get a complete picture of the claim? Why do you need a claim fact at all? Is it because of dimensions related to the claim?

Generally speaking, the claim attributes are broken down into many dimensions, eliminating the need for the claim dimension itself. The claim id would be carried as a degenerate dimension. If you had a claim level fact and a line level fact, the line level fact would carry the same dimensions as the claim level fact with the addition of line level specific dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Degenerate dimension question.

Post  dmjk2011 on Tue Oct 18, 2011 1:57 pm

The facts that are reported at the claim header are different from the claim line. I can't put it in claim line fact because they become non-additive there (mixed with claim line level facts). Also, there are multivalued dimensions that make sense only at the header level.

dmjk2011

Posts : 7
Join date : 2011-05-09

View user profile

Back to top Go down

Re: Degenerate dimension question.

Post  ngalemmo on Tue Oct 18, 2011 8:41 pm

Are these medical claims or some other kind of claim? What kind of measures only make sense at the claim level?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Degenerate dimension question.

Post  dmjk2011 on Wed Oct 19, 2011 9:21 am

Medical and Drug. There are amounts that are calculated by the adjudication system (source) at the header level that might not be a sum of the amount from all the claim lines. The requirement is to store what came from the source system. Also, there are diagnosis codes and procedure codes that come in the claim header in addition to each claim line.

Thank you for your feedback.

dmjk2011

Posts : 7
Join date : 2011-05-09

View user profile

Back to top Go down

Re: Degenerate dimension question.

Post  ngalemmo on Wed Oct 19, 2011 10:35 am

Odd. I had not seen that before. What is the purpose of procedure codes on the header? Do they not appear in the lines? Ususally claims are structured where charges are made for procedures performed, one line per procedure instance. Why would the adjudication amounts not sum up? While one "adjudicates a claim" they usually do it one line at a time. Diagnosis should be carried at the line level (multivalued dimension) regardless of weither you have a claim level fact.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Degenerate dimension question.

Post  dmjk2011 on Wed Oct 19, 2011 10:50 am

The procedure codes and diagnosis codes comes in at the header usually for inpatient claims, since there is a possibility that there are more than the usual procedure codes (esp. for long stays) than that are reported at the line level. (these are categorized as "other" diagnosis and procedure codes. (not used to determine costs during adjudication) The ones in the claim lines would be primary, secondary etc. used to determine cost.

Adjudication amounts dont necessarily sum up all the time because the service on the claim may be priced differently instead of being based on individual claim line services.

dmjk2011

Posts : 7
Join date : 2011-05-09

View user profile

Back to top Go down

Re: Degenerate dimension question.

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