February 15, 2010 at 11:46 am
I'm in the process of re-designing our healthcare claims data warehouse.
A single pharmacy/drug claim has a drug identifier called a NDC (National Drug Code). So, given that the NDC will exist in the fact table, there's a need to have a drug dimension that describes the drug.
Drug claims have a pesky property: The problem is that a single NDC can rollup to one or more drug types/categories. Consequently, it isn't clear to me if the drug category should be snowflaked/bridged off from the drug dimension or if the drug categories should be stamped in the fact table, which would require a separate drug category dimension (and perhaps a bridge table since drug category can be multivalued).
This situation reminds me of an employee who reports to multiple departments, or a musician who gigs with multiple bands, or a construction part that can be used for multiple projects.
Ultimately, one of my colleagues may request a report regarding a type of drug (e.g., anti-inflammatory). I need to make sure that all such drug claims for that type of drug category are identified.
Thanks in advance,
Pete
February 15, 2010 at 12:03 pm
Hey Pete,
Isn't this a (somewhat) classic case of many-to-many? Kimbal covers how to manage this with a bridge table (basically NDC stays in the fact, joins to bridge, then 'NDC Grouping FK' joins out to NDC Grouping table).
Marco Russo also published (some time ago now, maybe updated for 2k8??) a paper on Many to Many within SSAS. You should be able to find it somewhere here[/url].
HTH,
Steve.
February 15, 2010 at 12:58 pm
I appreciate the quick feedback, steve.
The NDC scenario is slightly different, I think, from something like a claim with multiple diagnoses. Rather than having a fact table with multiple diagnosis fields, Kimball recommends that a single field exist that retains a key to a bridge table which ultimately links to a dimension table: i.e., diagnosis is a multivalued dimension.
With a drug claim, however, a single NDC exists on the claim. The catch is that the NDC can have multiple rollup categories. So, I'm wondering if the bridge actually exists off of the drug dimension rather than from the fact table?
In the mean time, I'll check out the reference to Russo.
--pete
February 15, 2010 at 1:13 pm
Yeah, agreed on the multiple diagnosis example, was more leaning toward the multiple owners of a single bank account, so in your case, multiple NDC 'Groups' 'owning' a single NDC. Same same?
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply