What to do when a single attribute rolls up to multiple categories?

View previous topic View next topic Go down

What to do when a single attribute rolls up to multiple categories?

Post  pzajkowski on Mon Feb 15, 2010 2:05 pm

I'm working with healthcare claims data, and developing a new data warehouse.

A single pharmacy claim line will have a drug identifier called a NDC (national drug code). A single NDC may belong to one or more drug categories. Given the one-to-many relationship of NDC to drug category, what would be the recommended way to build a drug dimension? Would it be advisable to stamp the drug category in the fact table along with the NDC_key such that a drug category dimension exists as well as the drug dimension?

I'm just not sure how best to handle an attribute that relates to multiple rollup categories.

Thanks in advance


Posts : 31
Join date : 2009-08-10

View user profile

Back to top Go down

Treat it as a junk dimension

Post  Jeff Smith on Tue Feb 16, 2010 7:03 pm

If the source data has the Drug and Drug Category, then you can treat the columns as if they were a junk dimension. Pull all the unique combinations of Drug and Drug Category and load them into the dimension table. Assign the Drug Dimension Key to the fact table based on both the Drug and Drug Category. The lowest level of the Drug dimension table becomes DRUG-DRUG CATEGORY. You can rollup the data to Drug or to Drug Category. But you could not roll the data up to the DRUG and then to DRUG CATEGORY.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

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