Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Mixed Dimensions

2 posters

Go down

Mixed Dimensions Empty Mixed Dimensions

Post  gnilrets 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?



Posts : 8
Join date : 2011-10-19

Back to top Go down

Mixed Dimensions Empty Re: Mixed Dimensions

Post  ngalemmo 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.

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

Back to top Go down

Back to top

- Similar topics

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