Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

How to handle bad zip codes

2 posters

Go down

How to handle bad zip codes Empty How to handle bad zip codes

Post  Jeff Smith Wed Sep 22, 2010 12:09 pm

My company acts as a third party vendor. A new cleint does not want us to cleanup geographic information that comes they send to us. 2 things happen as a result. 1) we can get multiple spellings for the same city, and 2) we can get Zip Codes that do not exist in the corresponding city or even state.

I have a Zip Code Dimension table that I load from an outside source. The source has the Zip Code, City, State, and County. In the Zip Code dimension, the hierarchy is Zip Code to City to County to State. This isn't perfect because zip codes can cross cities, counties and states, but it's what I was told to use.

In the past, I would ignore the City and State information that cames with the Member data (for example) and assign the dimension key base solely on the Zip Code. But the new client wants to keep the City, State, and Zip as it comes to us.

My gut says do 2 things - 1) keep using the Zip Code Dimension that I've built, 2) create a new geographic dimension that is essentially a Junk dimension based on the Zip Code, City, and State as recieved from the client.

I would love to get other people's comments.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

How to handle bad zip codes Empty Re: How to handle bad zip codes

Post  ngalemmo Wed Sep 22, 2010 12:19 pm

Option 2 sounds like a reasonable compromise.

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

Back to top Go down

Back to top

- Similar topics

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