Efficient data model to represent range

From: Abhijit <abhijit_bhattach_at_hotmail.com>
Date: 11 Feb 2004 00:12:19 -0800
Message-ID: <d5d2bf2e.0402110012.3d7e5a2b_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 - 09:12:19 CET