Efficient Data model for representing range
Date: 11 Feb 2004 01:01:14 -0800
Message-ID: <d5d2bf2e.0402110006.401dd6b0_at_posting.google.com>
I am working in a data warehousing environment which gets sourced from Oracle ERP (AR/GL/AP). The dimensional entities associated with incoming data are GL Code (e.g. 110), Department (e.g. 1050), Core account (e.g. 301) , sub account (e.g 9). The incoming data needs to be mapped to key performance indicators
(KPI) e.g. 'All Other Revenue', 'OEM Revenue' etc. The mapping is
driven by GL, Dept, Core account , sub-account ranges. Example -
GL (000 - 999 i.e. for all GLs), Department (0000 - 9999), Core Account (301-314), sub account (0 - 0) maps to 'All Other Revenue' GL (110 - 110 i.e. for all GLs), Department (1010 - 1014), Core Account (500-510), sub account (1 - 2) maps to 'Reg. Development'
Once this mapping is stored in database there is need to periodically
verify that there is no overlapping KPI definitions using SQL
statements. i.e. the following KPI definition is invalid
GL (110-110) , Dept (8999 - 8999), Core Account (314-315), sub account
(0-0) -> 'OEM Revenue'
The data model needs to be flexible enough to accomodate new KPI definitions or new GLs/ Depts when they get created.Example - A new core account 302 is created which may map to KPI 'Period Cost'. In that case the definition of 'All Other Revenue' needs to be changed to -
GL(000-999),Department(0000-9999) Core account (301-301), sub account
(0-0) -> 'All Other Revenue'
GL(000-999), Department(0000-9999), Core account (303-314), sub account (0-0) -> 'All Other Revenue'
I would like to get suggestion about representing this mapping
information in best possible / efficient data model in relational
(Oracle) database which can be easily manipulated using SQL. I will
really appreciate any suggestion.
Received on Wed Feb 11 2004 - 10:01:14 CET