Dimension and fact table

I was reading through some Kimball design tips for data modeling and would like to get more information on dimension and fact table design tips.

The current situation that am in right now is, I have a dimension table for claims data. And when am looking at pharmacy claim data, there are some attributes that are included on the fact table which I would think can be made as attributes of existing claim dimension table. Let's say for example the drug tier, drug type code etc. These are all attributes of a claim which make me lean towards making these claim dimension table attributes. But then again, I sometimes feel like coming up with a new dimension table for pharmacy claim data and can't decide for sure what are the pros/cons of developing a new dimension table vs modifying the existing dimension table.

I don't see a problem in modifying the existing dimension table to add in a bunch of extra fields that apply for pharmacy claim data. Similarly, when I think about developing a new dimension table, I feel like am unnecessarily introducing another table join if we have to query for pharmacy claims data. The same applies to the fact table. Why can't we have just one table for all types of claims and build views on top of that for categorizing these claims as opposed to developing 'n' number of fact tables for n different types?

What would you recommend would be a better approach of dealing with this? What factors come into play when we make this decision of modifying existing dimension/fact as opposed to creating a new one?

Appreciate your help.

It really depends on wither pharma transactions should be maintained in a separate fact.

One consideration is the volume. Pharma claims then to be far greater in number than patient claims.

The kinds of analysis are usually different. However a lot of instance of care analysis requires inclusion of both Pharma and patient care data.

If you retain pharma in a separate fact, you may want to also include drugs administered during patient care in that fact (such data would appear in both claim facts). This allows for drug interaction and outcome analysis of drug use to be performed on a single fact without need to look at both pharma and clinical facts.

If a new field/column belong to the existing dim then you add it to that dim. For example, patient’s gender belongs to a patient dim so adding into a clinical code dim is incorrect.  You can correct your fact table and move claim attributes from there but you or your manager have to look at the benefit of doing it and the impact it would create. Move those attribute from the Fact table is going to impact the ETL process and would impact any reports which is using them from the Fact table.

The factor you consider when you add new measures to an existing Fact table is that fact's grain. If that measure fit into that fact grain then you add it. For example, you have a fact table which has daily claim amount, so the gain on the fact table is ONE unique claim per DAY. Adding a measure of “total weekly claim amount” would be incorrect to add into that fact table because this new measure represents claim amount of many claims.

It is acceptable to create a claim "types" dim and have different types of claims in one transaction fact table.

Thank you for the suggestions.

Can I have a dimension table with mixed attributes? When I say mixed attributes, what I mean to say is: take for example pharma claims. There are certain attributes like: drug type or if it's over the counter drug etc. When I look at this, I feel the need of coming up with a new dimension table for pharma claims since these are specific fields that apply to only one type of claims. Or should I just have one dimension table with all the claim attributes(irrespective of whether they apply to pharma/medical) all added onto one single dimension table?

Probably in my scenario, it makes more sense to have only one fact table, and avoid joining multiple fact tables when the users do analysis around different types of claims.

As far as attributes that describe a drug, they should be all in the same dimension regardless of what they are applied to.  The dimension describes the drug.  If it is over the counter, a controlled substance or is blue and oval shaped should not matter if it is purchased from a pharmacy or administered during a patient visit.

As far as fact tables go, you really need to spend time understanding the kinds of analysis the business will perform.

The idea that a user can access a single fact to perform analysis of drugs relating to diagnosis across clinical and pharma data is a quaint notion but is not practical.  This is simply because pharma data doesn't provide the necessary information.  The act of picking up up a prescription and administering a drug during a clinical visit are completely different independent events with different contexts (read that to mean different granularity).  It requires separate queries of clinical and pharma data to correlate the two to develop an instance of care.  There is nothing in a single fact table that avoids this and offers no real advantage to analysis.  Besides, a properly modeled clinical care subject area is going to require more than one fact table as it is.

Now, keep in mind I am not saying you could not produce an aggregate fact that reflects an instance of care and incorporates data from multiple facts, but building such a table as you primary storage (without the underlying atomic facts) is a really bad idea.