Degenerate dimension question.

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!

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.

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.

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

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.

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.

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.