Re: Efficient data model to represent range

abhijit_bhattach_at_hotmail.com (Abhijit) wrote in message news:<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'

• 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
Received on Wed Feb 11 2004 - 15:38:51 CST