Model with Attributes Based on Two Separate Dimensions

I am in the process of modeling a healthcare billing process and have run into an anomaly with the plan and payor dimensions that I would like to get some advice on from the community.

Initially it looked like we would have a plan dimension and each plan would have a unique payor, hooray, nice and easy. After interviewing users and digging into the data I found that plans and payors have a many to many relationship so no go on a single plan dimension. It looked like splitting them up into two separate dimensions would be the best course of action. The complication came in when we identified attributes the business uses to group transactions, based on both the plan and the payor. So a single plan, could be associated to multiple "grouping attributes" if it has different payors, and vice versa for payors.

I have been spending some time modeling what I think are a few different solutions and I would like some feedback or recommendations from the community on a best approach for this.

1. Create a "Transaction Group" dimension which is referenced on the facts then create bridges from the plan and payors, based on the "Transaction Group" to the facts. This would keep the facts very simple, but end up being a bit more complicated due to the bridge tables.

2. Create a "Transaction Group" dimension with two outrigger dimensions of the Plan and Payor. This is very similar to the first solution, but removes the bridge tables and has more of a "snowflake" structure to it.

3. Create three separate dimensions, for plans, payors and "Transaction Group" which are all referenced in the fact table. I like having the plan and payor dimensions referenced in the fact table and kept separate, as this is consistent with our other dimensions and will be simple for users to understand. My concern is about the "Transaction Group" dimension in the fact table, as it seems to be "tacked on."

4. Create a single "plan payor" dimension with the grain being the combination of the plan and the payor. This is more to the original idea for the plan dimension, but the grain becomes a combination of the plan and payor. It is similar to solution 2, but removes the outrigger dimensions. While I like the idea of having these related values all in a single dimension, I am concerned that it will become difficult to maintain/enhance due to the many to many relationship of plans and payors.


I am currently leaning towards option 2 for this as I can have separate plan and payor dimensions which can be used in other business processes, while keeping the additional transaction attributes the users need for this particular process, but I would like to get some feedback from the community. Is this the best approach? Are there other approaches I need to consider? Thanks in advance everyone!

I honestly don't follow where you are going with this…

From what I can gather you are a provider. A patient has a plan and one or more payers. Are the attributes that analysis is performed conditional on a particular combination of plan and payor (different physical attributes for different combinations)? What is a transaction group supposed to represent?

Thanks for the reply ngalemmo.

This is a healthcare billing process, with multiple providers.

Each transaction on an invoice will have a plan, and a payor.

The business groups these transaction, based on the plan and the payor on the transaction; what I am referring to as the "transaction group". So to answer your question, yes the physical attributes are based on combinations of the plan and the payor.

This is what is hanging me up, how to represent these physical attributes that are conditional based on the plan and payor.

Maybe I am making this more complicated than it really is, which would be great!

Put the Plan transaction group attributes in the plan dimension. Put the Payor transaction group attributes in the payor dimension. Do you end up with a nice hierarchy?

Thanks BoxesAndLines.

I started down that path, but ran into a road block when I found the the attributes can change, based on the plan/payor combination.

For instance:
Plan A with Payor Z will have an attribute of Group 1
then
Plan A with Payor Y will have an attribute of Group 2.

Similarly
Payor Z with Plan A will have an attribute of Group 1
then
Payor Z with Plan C will have an attribute of Group 3

This is where I am getting stumped at, how to model these attributes, based on a combination of values. Does what I am describing make sense, or I am over-complicating the process?

It sounds like a third table is in order if you have someway of maintaining it. You have your normal plan and payor dimensions with FKs on the fact and a third table (bridge?) with the two dimension keys and the grouping value. But, frankly, I don't know how you would maintain something like that...

Thanks ngalemmo. I am focusing on two solutions right now, both utilize a third table. The biggest hurdle right now, is as you say, maintaining it.

1. new dimension, linked directly onto the facts along with Payor and Plan. This seems to simplify reporting for the end users, but will make the ETL process more complicated.
2. new dimension that merges plan and payor and is associated to the facts. This may may the front end more complex though.

I am prototyping the two ideas, I'll post back here when I have a solution; hopefully it will help others that run into a similar scenario.

Thanks everyone for the ideas, and helping me vet my options.

It almost sounds like a junk dimension may work here as well.

I was making a very similar decision on a retail model. We had attributes that varied by product and store. I figured we could either 1) create a Store/Product dimension that is linked to the fact table or 2) create separate dimension(s) (junk or otherwise) for the attributes and link those new dimension(s) directly to the fact table. I ended up liking the 2nd option because creating a dimension with a key built from other dimensions didn't really feel right to me. Also, the size of the dimension would grow with the number of stores and was already in the millions of records.

I'm curious to see what you learn from your prototyping.

JasonK wrote:Thanks ngalemmo. I am focusing on two solutions right now, both utilize a third table. The biggest hurdle right now, is as you say, maintaining it.

1. new dimension, linked directly onto the facts along with Payor and Plan. This seems to simplify reporting for the end users, but will make the ETL process more complicated.
2. new dimension that merges plan and payor and is associated to the facts. This may may the front end more complex though.

I am prototyping the two ideas, I'll post back here when I have a solution; hopefully it will help others that run into a similar scenario.

Thanks everyone for the ideas, and helping me vet my options.

Here is what I came up with.

I prototyped two solutions:

1. Create a new (junk, derived,...) dimension (Dim_Transaction_Group) based on the plan and the payor. Reference the plan, payor, and transaction group dimensions in the facts.

2. Create a new transaction group dimension which includes the plan and payor dimensions as outrigger dimensions. On the facts, only reference the transaction group.

Solution 1. is much easier for the reporting side, but causes additional work in the ETL's. Since the transaction group is based on the plan and payor, if the business wants to change the transaction group assigned to the plan/payor, there is the potential for a change to the transaction group key in the fact table.

Solution 2. makes the ETL more straightforward as a change to the transaction group for a plan/payor combination, is handled as a Type 1 change in the dimension. e.g. Change the attributes associated to a payor/plan key combination and done. The main issue with solution two is it abstracts out the payor/plan data to another level, making reporting less straightforward.

Being that the end goal of this is to make reporting easier for the users, solution 1 is the solution I will be implementing. From a design standpoint either implementation will work, but this solution will follow the same structure as our other dimensions, so it will be familiar to the users. The other plus, as zip159 said, is it reduces the number of records in the new derived dimension, potentially improving performance. I also like the idea that the grain on the transaction group dimension, is a single transaction group, and not a combination of two dimensions.

From a reporting standpoint this gives our users three ways to attack the facts for payor, plan and transaction group. Those users interested in the transaction groups, look in one appropriately named dimension, those users looking for plans look in one appropriately named dimension, and those users looking for payor.. (you get the idea. )


I will be maintaining a transaction group look-up for the ETLs based on the plan and payor natural keys, but this will all be back end work that won't impact the users. This look-up has a similar structure as the combined dimension in solution 2, but once again, it is in the back end and will not complicate reporting for the users. Since the transaction group is a completely derived set of attributes, there will be an extra layer of validation needed in the ETLs, but once again this is back end work, that won't impact the users so I am OK with it.

Thanks to all of those that responded with comments and ideas. I have been lurking here for a while and appreciate how helpful this community is.


TLDR: Created a new transaction group dimension, which will be referenced on the facts along with plan and payor.