Efficient data model to represent range

[Abhijit]

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

[Dave]

ab**************@hotmail.com (Abhijit) wrote in message news:<d5**************************@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'
**** What defines an invalid overlap? Each range or the combination of
ranges? Your first paragraph suggests that a combination of ranges
defines a KPI, but does you example here suggest that each range (GL,
Dept, CA#, SA#) cannot overlap (e.g., GL acct can't be 110 in two
separate mappings regardless of the other ranges?)

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'
**** Perhaps this is understood, but your stated problem here does not
seem like a data model issue but rather maintaining the data in that
model. Once you figure out the appropriate model, you might want to
build a "friendly" API (e.g., PL/SQL) for maintaining that data model
based on user input. For instance, when a user inputs a new mapping,
the API logic determines if any records in your model need to be
deleted/updated/inserted. In this example the input might be
CREATE_NEW_MAPPING(000, 999, 0000, 9999, 302, 302, 'Period Cost');

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