Dimensional Modeling- How to handle Code-Name-Description lookup table - Stack Overflow

admin管理员组

文章数量:1353303

Is there a best practice to handle OLTP lookup tables( code-name-description tables) when converting to Dimension? Eg, the ACCOUNT table in OLTP has almost 15 codes (Eg: TRADING_PLATFORM_CD, SERVICE_MODEL_CD etc.. ) . Some of the columns have corresponding code-name-description lookup tables available in OLTP, while some of the columns have their names and descriptions available with business. I could think of 4 options , but wondering which is the correct method?

option 1- Bring Lookup table as-is from OLTP into warehouse. Do not create separate Dims. Leave only CD in ACCOUNT_DIM. Expose the NAME and DESC in the views by joining REF and ACCOUNT_DIM using CD column

option 2- For every CD, add NAME and DESC in the ACCOUNT_DIM itself

Option 3- Create each CD as a separate DIM table. Put those Dim Keys into ACCOUNT_DIM (snowflaking?), or via a fact

Option 4- Create single catch-all Dim to hold all types of Codes , names and description

Any guidance will be appreciated Thanks Sunil

Is there a best practice to handle OLTP lookup tables( code-name-description tables) when converting to Dimension? Eg, the ACCOUNT table in OLTP has almost 15 codes (Eg: TRADING_PLATFORM_CD, SERVICE_MODEL_CD etc.. ) . Some of the columns have corresponding code-name-description lookup tables available in OLTP, while some of the columns have their names and descriptions available with business. I could think of 4 options , but wondering which is the correct method?

option 1- Bring Lookup table as-is from OLTP into warehouse. Do not create separate Dims. Leave only CD in ACCOUNT_DIM. Expose the NAME and DESC in the views by joining REF and ACCOUNT_DIM using CD column

option 2- For every CD, add NAME and DESC in the ACCOUNT_DIM itself

Option 3- Create each CD as a separate DIM table. Put those Dim Keys into ACCOUNT_DIM (snowflaking?), or via a fact

Option 4- Create single catch-all Dim to hold all types of Codes , names and description

Any guidance will be appreciated Thanks Sunil

• data-modeling
• dimensional-modeling

Share Improve this question Follow edited Mar 31 at 19:43 NickW 9,83822 gold badges88 silver badges2424 bronze badges asked Mar 31 at 18:27 SunilSunil 5955 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Denormalise the reference data into the Dims - which is what I think you have as option 2

本文标签： Dimensional Modeling How to handle CodeNameDescription lookup tableStack Overflow