Mixed Dimensions

[gnilrets]

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

[ngalemmo]

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.