What to do when a single attribute rolls up to multiple categories?
2 posters
Page 1 of 1
What to do when a single attribute rolls up to multiple categories?
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
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
pzajkowski- Posts : 31
Join date : 2009-08-10
Treat it as a junk dimension
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
Similar topics
» Single Attribute Dimensions - why not instead write textual attribute into Fact table?
» Single or Multiple Fact : Single or Multiple Dimension
» How to model article dimension for unpredictable changes with multiple versions and multiple article categories
» SCD2 Product Dim has multiple categories which can change over time
» multiple hierarchy : single dimension vs multiple
» Single or Multiple Fact : Single or Multiple Dimension
» How to model article dimension for unpredictable changes with multiple versions and multiple article categories
» SCD2 Product Dim has multiple categories which can change over time
» multiple hierarchy : single dimension vs multiple
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum