Dimension Table Design

Question

Dimension Table Design

mhweiss

SSCarpal Tunnel

Points: 4284
More actions
September 22, 2003 at 11:56 pm

#106599

I have a situation in which I find that I have the same (dimension) data repeated in three separate places in a preliminary data mart design. This data is common to three dimension tables in that it is the primary data in one table but is used as lookup data in two other tables(during the loading of the fact table dimension keys). The table structure in question follows(abbreviated):
geolocation:
geolocation_key
npa
nxx
lata
ocn
city
state
country
minvcoordinate..etc etc
npa_nxx:
npa_nxx_key
npa
nxx
ocn:
ocn_key
ocn
ocn_name
npa
nxx
My problem is that npa and nxx are used to lookup the correct ocn key value in the loading of the fact table. They are also used to lookup the correct foreign key value for geolocation, as well as the npa/nxx key value itself. OCN can logically be contained in the geolocation table...although to put it here could introduce some null field values in the ocn field as opposed to having it in its own table.
Since all of the fields in question could be logically contained in one table, is it best to do so and then use views, with dimension-specific columns, off that table for the actual dimensions themselves? Also, OCN definitely is a slowly changing dimension. I hate having repetitive data between tables but in some cases it is the best solution. I am not clear yet if this is one of those cases or not. Any thoughts?
Thank you,
Michael
Michael Weiss

Michael Weiss

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Michael,

We have recently had a similar problem. In short, we had dimensional data that relied on a Business Unit for unique identification. So for e.g. we had a Store dimension that included the BUId (Business Unit ID). As did the Product dimension. We also had a Business Unit dimension out there by itself too.

It sounds strange to do this, but in the end there was little choice. In a "perfect world" we would have created a true conformed dimension where all stores were part of a global set, but the client didn't want this.

Also, as we had BUId as only an int, it wasn't like it was going to take up all the disk space in the free world.

Steve

Steve.

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 2

Thanks, Steve...in your experience, if I decide to keep it all in one table and use views off that table for the individual dimensions, do you see any potential pitfalls I may not be aware of?

Thanks,

Michael

Michael Weiss

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hi Michael,

I can't think of any. Possibly a requirement for a few more indexes on the table to make it perform in all of it's roles? Other than that, maybe some DW purist who disagrees with the design

Steve.