Enterprise information management system and methods

• US 6,668,253 B1
• Filed: 08/30/2000
• Issued: 12/23/2003
• Est. Priority Date: 09/08/1999
• Status: Expired due to Term

• Current Assignee: The Reynolds and Reynolds Company
• Original Assignee: Reynolds & Reynolds Holdings, Inc.
• Inventors: Thompson, Michael R., Sunkara, Satya S., Smith, Lisa D., Jung, Mark A., Johnston, Teresa J., Horan, Julie L., Datla, Rayapa S., Hergenrather, Carol M., Campbell-Kaminski, Elizabeth E., Niehoff, Gregory P., Matthews, Mary J., Syed, Safdar R., Ward, Jamie A., Burkhart, Tal D.
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/651,203
• Time in Patent Office: 1,210 Days
• Field of Search: 707/3, 707/511, 707/101, 707/100, 707/104.1, 707/10, 707/1, 705/7, 705/8, 705/35, 705/37, 705/26
• US Class Current: 1/1
• CPC Class Codes: G06Q 10/06 Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling

Abstract

A system for enterprise information management includes a data warehouse server; a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server; a data source application connected to the transformation and staging server to provide data to the transformation and staging server, wherein the transformation and staging server obtains data from the data source application via requests and places the data into temporary staging tables to prepare for the transformation and cleansing process prior to movement of the data to the data warehouse server; a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data; a web server connected to the data warehouse server; and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server.

Claims

1. A system for enterprise information management, comprising:

a data warehouse server;

a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server;

a data source application connected to the transformation and staging server to provide data to the transformation and staging server;

a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data;

a web server connected to the data warehouse server; and

a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server,

wherein a client accessing the data warehouse server via the web server is presented with a login screen and is required to provide a valid user identifier and password in order to access the system, and wherein, upon authentication of the user identifier and password, a new browser window is opened at the client and the client is presented with a user interface (UI) tailored to the client'"'"'s preferences and security privileges,

wherein the UI comprises,

a task list.

buttons/controls for launching components of the system, and

a content area for task related data and output, wherein each item listed in the task list has supporting screens that populate the content area,

wherein the UI provides access to performance indicators through home paves made up of one to four reports or graphs displayed on the content area, and

wherein clients are provided with a set of predefined drill paths or next steps for the user to follow in analyzing their data, wherein the predefined next steps display additional reports with new, more detailed, or related information to a report currently being viewed.

2. A system as in claim 1 wherein the transformation and staging server obtains data from the data source application via requests and places the data into temporary staging tables to prepare for the transformation and cleansing process prior to movement of the data to the data warehouse server.

3. A system as in claim 1 wherein the transformation and staging server includes the following databases and files:

a master database that contains all system tables;

a model database template for the creation of all new databases;

a staging database which contains all data from systems that is being or has been cleansed, transformed, denormalized, summarized;

an error database which contains source information that could not automatically be cleansed or transformed;

a financial database used by the financial consolidation application and containing consolidated and summarized financial information;

at least one flat file for loads;

a repository database used by the transformation tool and containing information regarding the transformation procedures and automation plans; and

a temporary work area for the creation of temporary tables and information.

4. A system as in claim 3 wherein the transformation and staging server comprises:

four physical disk drives and wherein the physical disk drives for the transformation and staging server, are separated by individual logical drives.

5. A system as in claim 4 wherein the disk drives serve separate purposes and functionality of the system has been separated by drive so as to minimize disk contention during the various process that run on the transformation and staging server.

6. A system as in claim 1 wherein the data warehouse server includes the following databases and files:

a master database that contains all of the system tables on the data warehouse server;

a model database template for the creation of all new databases on the data warehouse server;

a temporary database used as a temporary work area for the creation of temporary tables and information on the data warehouse server;

a meta-view database that contains all of the metadata, views, and stored procedures that are to be used by the users of the system; and

a database that contains some normalized data, but mostly denormalized and summarized data that has been cleansed, transformed on the transformation and staging server.

7. A system as in claim 6 wherein the meta-view database is the sole access method for users to the information in the database.

8. A system as in claim 6 wherein the data warehouse server comprises a plurality of physical disk drives combined into one logical drive to take full advantage of Redundant Array of Independent Disks (RAID) technology.

9. A system as in claim 1 wherein the data relate to automobiles.

10. A system as in claim 1 wherein the home pages can be customized.

11. A system for enterprise information management, comprising:

a data warehouse server;

a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server;

a data source application connected to the transformation and staging server to provide data to the transformation and staging server;

a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data;

a web server connected to the data warehouse server; and

a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server,

wherein a client accessing the data warehouse server via the web server is presented with a login screen and is required to provide a valid user identifier and password in order to access the system, and wherein, upon authentication of the user identifier and password, a new browser window is opened at the client and the client is presented with a user interface (UI) tailored to the client'"'"'s preferences and security privileges,

wherein the UI comprises,

a task list,

buttons/controls for launching components of the system, and

a content area for task related data and output, wherein each item listed in the task list has supporting screens that populate the content area,

wherein the UI provides access to performance indicators through home pages made up of one to four reports or graphs displayed on the content area,

wherein clients are provided with a set of predefined drill paths or next steps for the user to follow in analyzing their data, wherein the predefined next steps display additional reports with new, more detailed, or related information to a report currently being viewed, and

wherein predefined next step buttons are only provided with the original reports provided with the system.

12. A system as in claim 11, wherein the data relate to automobiles.

13. A method for enterprise information management, comprising:

providing an enterprise information management system, the system comprising a data warehouse server, a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server, a data source application connected to the transformation and staging server to provide data to the transformation and staging server, a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data, a web server connected to the data warehouse server, and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server;

by the transformation and staging server;

retrieving operational data from the data source application using a data flow plan;

validating and cleansing the retrieved operational data;

loading the data into an appropriate temporary staging table; and

performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations; and

loading information from the transformation and staging server on the data warehouse server,

wherein the loading of information comprises one of;

a round-robin approach used for refresh processing and extracting information from permanent tables; and

a see-saw approach used for non-refresh processing and extracting information from temporary tables,

wherein the round-robin approach comprises;

(a) exporting information to flat files on the transformation and staging server, selecting rows from normalized, denormalized, and summary tables,

(b) loading information into the normalized, denormalized, and summary load tables;

(c) for each table

(c1) updating load statistics metadata on the data warehouse server, indicating that the information is in a “

loading”

state;

(c2) renaming the current table to a temporary table as a temporary holding area;

(c3) renaming the load table to the actual table, making the latest information available to the users;

(c4) renaming the temporary table to the load table to prepare for the next period'"'"'s processing batch;

(c5) updating the load statistics metadata on the data warehouse server indicating that the information is in a “

updated”

state and updating the date, time, number of records loaded; and

(d) deleting all of the information from the normalized, denormalized, and summary load tables to prepare for the next period'"'"'s processing batch.

14. A method as in claim 13 further comprising:

writing data with an error to an error table along with an error message describing a reason for rejection.

15. A method as in claim 14 further comprising:

correcting errors and reprocessing the data.

16. A method as in claim 13 wherein the validations and transformations performed include at least one of:

checking for absence of data;

checking data typing;

checking free form text fields;

checking multipurpose fields;

checking dummy values entered in a mandatory field;

checking for contradicting data;

checking violation of business rules;

checking data anomalies;

restructuring operational keys;

checking data integrity;

supplying default values;

renaming data elements moved from the operational environment to the data warehouse;

data format conversion; and

aggregating data values.

17. A method as in claim 13 wherein the round-robin approach is used if the system requires minimal down time.

18. A method as in claim 13, wherein the data relate to automobiles.

19. A method for enterprise information management, comprising:

providing an enterprise information management system comprising a data warehouse server, a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server, a data source application connected to the transformation and staging server to provide data to the transformation and staging server, a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data, a web server connected to the data warehouse server, and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server;

by the transformation and staging server;

retrieving operational data from the data source application using a data flow plan;

validating and cleansing the retrieved operational data;

loading the data into an appropriate temporary staging table; and

performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations; and

loading information from the transformation and staging server on the data warehouse server,

wherein the loading of information comprises one of;

a round-robin approach used for refresh processing and extracting information from permanent tables; and

a see-saw approach used for non-refresh processing and extracting information from temporary tables,

wherein the see-saw approach comprises;

(a) exporting information to flat files on the transformation and staging server, selecting rows from the normalized, denormalized, and summary temporary tables;

(b) for each table of the normalized, denormalized, and summary temporary tables;

(b1) updating the load statistics metadata on the data warehouse server indicating that the information is in a “

loading”

state;

(b2) renaming the current table load table;

(b3) loading information into the load table;

(b4) renaming the load table to the permanent table; and

(b5) updating the load statistics metadata on the data warehouse server indicating that the information is in a “

updated”

state and updating the date, time, number of records loaded.

20. A method as in claim 19 further comprising:

storing historic information on the transformation and staging server.

21. A method as in claim 19, wherein the data relate to automobiles.

22. A method for enterprise information management, comprising:

providing an enterprise information management system comprising a data warehouse server, a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server, a data source application connected to the transformation and staging server to provide data to the transformation and staging server, a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data, a web server connected to the data warehouse server, and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server;

by the transformation and staying server;

retrieving operational data from the data source application using a data flow plan;

validating and cleansing the retrieved operational data;

loading the data into an appropriate temporary staging table; and

performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations; and

loading information from the transformation and staging server on the data warehouse server,

wherein the loading of information comprises one of;

a round-robin approach used for refresh processing and extracting information from permanent tables; and

a see-saw approach used for non-refresh processing and extracting information from temporary tables, and

storing historical information on both the data warehouse server and the transformation and staging server, and using the non-refresh processing approach on the transformation and staging server and the see-saw approach on the data warehouse server.

23. A method as in claim 22, wherein the data relate to automobiles.

24. A computerized record-keeping system that tracks and records transactions of a method for enterprise information management, comprising:

providing a system for enterprise information management comprising a data warehouse server, a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server, a data source application connected to the transformation and staging server to provide data to the transformation and staging server, a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data, a web server connected to the data warehouse server, and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server;

by the transformation and staging server;

retrieving operational data from the data source application using a data flow plan;

validating and cleansing the retrieved operational data;

loading the data into an appropriate temporary staging table; and

performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations; and

loading information from the transformation and staging server on the data warehouse server,

wherein the loading of information comprises one of;

a round-robin approach used for refresh processing and extracting information from permanent tables, and

a see-saw approach used for non-refresh processing and extracting information from temporary tables,

wherein the round-robin approach comprises;

(a) exporting information to flat files on the transformation and staging server, selecting rows from normalized, denormalized, and summary tables;

(b) loading information into the normalized, denormalized, and summary load tables;

(c) for each table;

(c1) updating load statistics metadata on the data warehouse server, indicating that the information is in a “

loading”

state;

(c2) renaming the current table to a temporary table as a temporary holding area;

(c3) renaming the load table to the actual table, making the latest information available to the users;

(c4) renaming the temporary table to the load table to prepare for the next period'"'"'s processing batch; and

(c5) updating the load statistics metadata on the data warehouse server indicating that the information is in a “

updated”

state and updating the date, time, number of records loaded; and

(d) deleting all of the information from the normalized, denormalized, and summary load tables to prepare for the next period'"'"'s processing batch.

25. A system as in claim 24 where the method further comprises:

writing data with an error to an error table along with an error message describing a reason for rejection.

26. A system as in claim 25 where the method further comprises correcting errors and reprocessing the data.

27. A system as in claim 24 wherein the validations and transformations performed include at least one of:

checking for absence of data;

checking data typing;

checking free form text fields;

checking multipurpose fields;

checking dummy values entered in a mandatory field;

checking for contradicting data;

checking violation of business rules;

checking data anomalies;

restructuring operational keys;

checking data integrity;

supplying default values;

renaming data elements moved from the operational environment to the data warehouse, data format conversion; and

aggregating data values.

28. A system as in claim 24 wherein the round-robin approach is used if the system requires minimal down time.

29. A computerized record-keeping system that tracks and records transactions of a method for enterprise information management, comprising:

providing a system for enterprise information management comprising a data warehouse server, a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server, a data source application connected to the transformation and staging server to provide data to the transformation and staging server, a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data, a web server connected to the data warehouse server, and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server;

by the transformation and staging server;

retrieving operational data from the data source application using a data flow plan;

validating and cleansing the retrieved operational data;

loading the data into an appropriate temporary staging table; and

performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations; and

loading information from the transformation and staging server on the data warehouse server,

wherein the loading of information comprises one of;

a round-robin approach used for refresh processing and extracting information from permanent tables, and

a see-saw approach used for non-refresh processing and extracting information from temporary tables,

wherein the see-saw approach comprises;

(a) exporting information to flat files on the transformation and staging server, selecting rows from the normalized, denormalized, and summary temporary tables;

(b) for each table of the normalized, denormalized, and summary temporary tables;

(b1) updating the load statistics metadata on the data warehouse server indicating that the information is in a “

loading”

state;

(b2) renaming the current table load table;

(b3) loading information into the load table;

(b4) renaming the load table to the permanent table; and

(b5) updating the load statistics metadata on the data warehouse server indicating that the information is in a, “

updated”

state and updating the date, time, number of records loaded.

30. A system as in claim 29 wherein the method further comprises:

storing historic information on the transformation and staging server.

31. A computerized record-keeping system that tracks and records transactions of a method for enterprise information management, comprising:

providing a system for enterprise information management comprising a data warehouse server, a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server, a data source application connected to the transformation and staging server to provide data to the transformation and staging server, a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data, a web server connected to the data warehouse server, and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server;

by the transformation and staging server;

retrieving operational data from the data source application using a data flow plan;

validating and cleansing the retrieved operational data;

loading the data into an appropriate temporary staging table; and

performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations;

loading information from the transformation and staging server on the data warehouse server;

wherein the loading of information comprises one of;

a round-robin approach used for refresh processing and extracting information from permanent tables, and

a see-saw approach used for non-refresh processing and extracting information from temporary tables,

further comprising storing historical information on both the data warehouse server and the transformation and staging server, and using the non-refresh processing approach on the transformation and staging server and the see-saw approach on the data warehouse server.

32. A system for enterprise information management comprising:

a data warehouse server;

a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server;

a data source application connected to the transformation and staging server to provide data to the transformation and staging server;

a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data;

a web server connected to the data warehouse server; and

a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server,

wherein a client accessing the data warehouse server via the web server is presented with a login screen and is required to provide a valid user identifier and password in order to access the system, and wherein, upon authentication of the user identifier and password, a new browser window is opened at the client and the client is presented with a user interface (UI) tailored to the client'"'"'s preferences and security privileges,

wherein the UI comprises,

a task list.

buttons/controls for launching components of the system, and

a content area for task related data and output, wherein each item listed in the task list has supporting screens that populate the content area,

wherein the UI provides access to performance indicators through home pages made up of reports or graphs displayed on the content area, and

wherein the UI provides an exception folder to store at least one exception report satisfying at least one exception condition defined by a user, wherein the at least one exception condition has an associated color identifier and cannot be changed by a user when the at least one exception report is run.

33. A system as in claim 32, wherein the at least one exception condition has an associated textual description.

34. A system as in claim 32, wherein contents of the exception folder are automatically processed on a nightly basis.