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

[pzajkowski]

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

[Jeff Smith]

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.