Modeling a dimension table
Page 1 of 1
Modeling a dimension table
I have a scenario where I have two dimension tables to hold the diagnostic related grouping information. One for the previous version which is Medicare Severity Diagnostic Related Grouping(MS) and the other dimension table to hold the new version that got introduced last year: All patient refined diagnostic related groupings.(APR)
So MS version supports both 32 and version 33, but APR supports just version 33. And the two tables I have include the following attributes. There are some overlapping codes between MS and APR and the weights differ between version 32 and version 33 of MS.
MS
ID CD DESC WEIGHT DC_CD DC_DESC V32_WEIGHT
APR
ID CD DESC WEIGHT DC_CD DC_DESC
I don't seem to understand the need of separating these two into two separate tables, because if they come up with another new name down the lane I don't want to introduce another table but be able to add that information to the existing dimension table. And I have the below two options in my mind and leaning towards using the first approach so I don't have to again snowflake off the type dimension table. Am I on the right track? Is there a reason why having these two as separate tables would make sense rather than combining all versions into one single table? And any advantages of having the type dimension table to include this information as opposed to having the flag columns to help identify the type? As always, thanks for the support.
Option 1:
ID CD DESC WEIGHT DC_CD DC_DESC VERSION_ID MS_FLAG APR_FLAG
Option 2:
ID CD DESC WEIGHT DC_CD DC_DESC VERSION_ID TYPE
Type_dim
ID NM
1 MS
2 APR
So MS version supports both 32 and version 33, but APR supports just version 33. And the two tables I have include the following attributes. There are some overlapping codes between MS and APR and the weights differ between version 32 and version 33 of MS.
MS
ID CD DESC WEIGHT DC_CD DC_DESC V32_WEIGHT
APR
ID CD DESC WEIGHT DC_CD DC_DESC
I don't seem to understand the need of separating these two into two separate tables, because if they come up with another new name down the lane I don't want to introduce another table but be able to add that information to the existing dimension table. And I have the below two options in my mind and leaning towards using the first approach so I don't have to again snowflake off the type dimension table. Am I on the right track? Is there a reason why having these two as separate tables would make sense rather than combining all versions into one single table? And any advantages of having the type dimension table to include this information as opposed to having the flag columns to help identify the type? As always, thanks for the support.
Option 1:
ID CD DESC WEIGHT DC_CD DC_DESC VERSION_ID MS_FLAG APR_FLAG
Option 2:
ID CD DESC WEIGHT DC_CD DC_DESC VERSION_ID TYPE
Type_dim
ID NM
1 MS
2 APR
tawnyap- Posts : 2
Join date : 2016-03-29
Similar topics
» Modeling advice for Dimension Table
» Pros and cons of consolidated dimension table Vs. many dimension table ?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» joining dimension table to dimension and again fact table
» Can a dimension table directly link to another dimension table?
» Pros and cons of consolidated dimension table Vs. many dimension table ?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» joining dimension table to dimension and again fact table
» Can a dimension table directly link to another dimension table?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum