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
Share Improve this question edited Mar 31 at 19:43 NickW 9,8382 gold badges8 silver badges24 bronze badges asked Mar 31 at 18:27 SunilSunil 595 bronze badges1 Answer
Reset to default 1Denormalise the reference data into the Dims - which is what I think you have as option 2