Mixed Dimensions

View previous topic View next topic Go down

Mixed Dimensions

Post  gnilrets on Sat Dec 17, 2011 5:27 pm

I'm modeling some dental claims data. The fact table has a row for every claim line item. There are two dimensions:

1) A procedure code dimension
2) A service provider dimension

The business users want to group procedure codes into descriptive service categories as defined in a spreadsheet. It's a simple many to one mapping between procedure code and service categories for the majority of codes. However, there are a few cases where they want the service category for a particular procedure code to be dependent the provider type (e.g., dentist vs. orthodontist).

I can't decide if I should replicate the service provider type from the provider dimension on the procedure dimension. On the one hand, it seems like replicating the provider type would be bad, because then there would be two sources for the same information and could end up violating the single source of truth. On the other hand, leaving it out of the procedure code dimension would mean that I would have to maintain a separate surrogate key lookup table.

Any thoughts?

Thanks,
Sterling

gnilrets

Posts : 8
Join date : 2011-10-19

View user profile

Back to top Go down

Re: Mixed Dimensions

Post  ngalemmo on Sun Dec 18, 2011 12:53 pm

Just have a service category dimension. Put the necessary logic in your ETL process to determine the correct category based on the provider and procedures.

If you need to re-categorize old claims based on changed rules, you may want to consider a bridge to avoid rekeying the fact table when changes occur.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum