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 1 3321 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Raghavendra Mahuli |
last post by:
i need to store a lot of integers(say 10,000) in a datastructure.
The constraints are:
1. It should be fast.
2. It should be orderded or sorted.
3.Insterting and deleting should be fast.
4. The...
|
by: Fred Ma |
last post by:
I'm using the expression "int a = ceil( SomeDouble )".
The man page says that ceil returns the smallest
integer that is not less than SomeDouble, represented
as a double. However, my...
|
by: Simon Harvey |
last post by:
Hi,
In my application I get lots of different sorts of information from
databases. As such, a lot of information is stored in DataSets and DataTable
objects.
Up until now, I have been passing...
|
by: Robert W. |
last post by:
I'm building my first major C# program and am try to use best practices
everywhere. So I've implemented the "Document/View Model" whereby:
- There's a Windows Form, which we'll call "formView"
-...
|
by: laimis |
last post by:
Hey guys,
I just recently got introduced to data mappers (DTO mapper). So now I
have a SqlHelper being used by DTOMapper and then business layer is
using DTOMapper when it needs to persist...
|
by: Peter Jansson |
last post by:
Hello group,
The following code is an attempt to perform URL-decoding of URL-encoded
string. Note that std::istringstream is used within the switch, within
the loop. Three main issues have been...
|
by: eriwik |
last post by:
I'm working on an application that performs calculations on triangles
of a 3D-model. As part of those computations I have to calculate a
value for each pair of triangles and the number of triangles...
|
by: py_genetic |
last post by:
Hello,
I'm importing large text files of data using csv. I would like to add
some more auto sensing abilities. I'm considing sampling the data
file and doing some fuzzy logic scoring on the...
|
by: Abhijit |
last post by:
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),...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |