Conformed dimension or Degenerate dimension?
3 posters
Page 1 of 1
Conformed dimension or Degenerate dimension?
I am building a dimensional model and I need advice on whether I should create conformed dimensions or degenerate dimensions. I have several fields that I believe belong as two dimensions (see below). The problem is I don't have a natural key for either. I can't get one. This information comes from a claim and there is no master data for either of these. Should I go ahead and create conformed dimensions and use the entire record as the natural key for the ETL or should I keep these in the fact table? I don't want to have dimensions that are as large as the fact table. Employer probably would not, but Insured definitely could.
One dimension could be Employer:
Employer Name
Employer Status Code
Employer Address Line 1
City
State
Zip
Telephone
Another is Insured:
Insured First Name
Insured Last Name
Insured Address Line 1
City
State
Zip
DOB
Gender
Thanks!
One dimension could be Employer:
Employer Name
Employer Status Code
Employer Address Line 1
City
State
Zip
Telephone
Another is Insured:
Insured First Name
Insured Last Name
Insured Address Line 1
City
State
Zip
DOB
Gender
Thanks!
flabbott- Posts : 9
Join date : 2012-01-16
Re: Conformed dimension or Degenerate dimension?
Employer is usually a role of a Company (or Party) dimension, representing some sort of legal entity.
And Insured are people, so they'd go to another dimension (Person).
You can fork adresses to yet another geographical dimension to ease your life while all those folks will be changing addresses.
And neither of these dims is actually degenerate )
And Insured are people, so they'd go to another dimension (Person).
You can fork adresses to yet another geographical dimension to ease your life while all those folks will be changing addresses.
And neither of these dims is actually degenerate )
Re: Conformed dimension or Degenerate dimension?
You should not put these columns in the fact table. And as YKUD pointed out, these are not candidates for degenerate dimensions. I would consider a master address dimension with two relationships to the fact table (employer and insured). Possible an employee status dimension and an employer dimension. Depending on report requirements I would also consider dimensions for Gender, State, and DOB (date dim).
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Conformed dimension or Degenerate dimension?
Thanks! Just to clarify, even though I don't have a key to match the Insured as part of my ETL and it could become as large as the fact, I should still create a dimension? I've had issues when a dimension is as large as the fact. That's what I'm afraid of. There is not time or budget for a data cleansing tool. There is not a key, so we will have to match the whole record each time. Since it's from claim, it is entered by the claim submitter and it's not master data.
flabbott- Posts : 9
Join date : 2012-01-16
Re: Conformed dimension or Degenerate dimension?
flabbott wrote: There is not a key, so we will have to match the whole record each time. Since it's from claim, it is entered by the claim submitter and it's not master data.
Create a crc32 or md5 hash of the fields you're going to join on and use this hash to quickly find “similar” insurers. That allows you to turn an ugly multi-varchar fields join into a nice int join with a bit of post work. Search this forum, there are a few discussions on this topic.
Similar topics
» Conformed Degenerate Dimension?
» Question on breaking out Degenerate Dimension to separate dimension
» Should I use a degenerate dimension or create a junk dimension?
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» Remove degenerate dimension?
» Question on breaking out Degenerate Dimension to separate dimension
» Should I use a degenerate dimension or create a junk dimension?
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» Remove degenerate dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum