Conformed dimension or Degenerate dimension?

View previous topic View next topic Go down

Conformed dimension or Degenerate dimension?

Post  flabbott on Thu Jan 26, 2012 6:36 pm

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!

flabbott

Posts : 9
Join date : 2012-01-16

View user profile

Back to top Go down

Re: Conformed dimension or Degenerate dimension?

Post  ykud on Fri Jan 27, 2012 9:39 am

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 )
avatar
ykud

Posts : 12
Join date : 2012-01-16

View user profile http://ykud.com

Back to top Go down

Re: Conformed dimension or Degenerate dimension?

Post  BoxesAndLines on Fri Jan 27, 2012 10:08 am

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).
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Conformed dimension or Degenerate dimension?

Post  flabbott on Fri Jan 27, 2012 11:59 am

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

View user profile

Back to top Go down

Re: Conformed dimension or Degenerate dimension?

Post  ykud on Fri Jan 27, 2012 9:21 pm

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

Posts : 12
Join date : 2012-01-16

View user profile http://ykud.com

Back to top Go down

Re: Conformed dimension or Degenerate dimension?

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