I have a question regarding modeling exercise. Oracle EBV Chart of account in data Vault 2.0. Oracle has a Combination segment table with COA segments pivoted accros columns such as Segment 1, segment 2 to segment 7. Another set of tables will contain the descriptions for the various segments. In SQL I would create a view to retrieve the descriptions. The Flex fields tables are unpivoted so I would query using multi joins like.
SELECT * FROM landing.gl.gl_code_combinations gcc Join applsys.fnd_flex_values fvv1 ON gcc.segment1 = fvv1.flex_value Join applsys.fnd_flex_value_sets fvs1 ON fvs1.flex_value_set_id = fvv1.flex_value_set_id Join applsys.fnd_flex_values_tl fvt1 ON fvv1.flex_value_id = fvt1.flex_value_id
And then repeat the joins for all 7 segments. I try to come up with a Data vault 2.o model for this where I would have Hub_Segments like Combination table with pivoted segments and Satellite segment (or reference table) where I would have the descriptions, the sets and the languages translations for each segment level. Would have any advice on how to go after this? I was thinking Pivoting the flex values or unpivoting the segments I will be very grateful for any pointer, idea or directions you could provide. Thank you very much. Philippe