System and method for warehousing and retrieving data

• US 6,587,857 B1
• Filed: 06/30/1999
• Issued: 07/01/2003
• Est. Priority Date: 06/30/1998
• Status: Expired due to Term

• Current Assignee: Citicorp Credit Services Incorporated (Citigroup Incorporated)
• Original Assignee: Citicorp Development Center Incorporated (Citigroup Incorporated)
• Inventors: Khilkevich, Simon, Shih, Lin-Chen, Carothers, Kevin
• Primary Examiner(s): Coby, Frantz
• Application Number:
  US09/342,902
• Time in Patent Office: 1,462 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5, 707/10, 707/102, 707/100, 707/101, 709/223
• US Class Current: 707/602
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/959 Network
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A system and method for preparing and integrating operational data received from nodes on a network prior to loading the data onto a data warehouse. Operational data received from nodes on a network is transformed to produce an output file of integrated data records which are audited to ensure data contained in the records has not already been loaded to the data warehouse in a previous operation. Data previously loaded onto the data warehouse is compared with current operational data to be loaded to determine whether the status of any nodes in the network has changed. The audited integrated operational data and data indicating which nodes, if any, have changed status since the previous upload of data are provided with unique identification numbers and uploaded to the data warehouse. In a preferred embodiment, the data concerns transactions occurring on, and the status of, components in customer activated terminals or transactions occurring on home banking servers. Once uploaded to the data warehouse, data may be viewed graphically by an end user querying the data warehouse.

Claims

1. A process for preparing data for upload to a data warehouse comprising the steps of:

transmitting data from nodes on a network relating to an operation of each node and an operation of each node component;

storing data obtained from the transmitted data as a series of records;

verifying the data related to each node by referencing a mnemonic table;

determining whether data relating to a transaction is an orphan;

computing the total elapsed time for each transaction;

writing the data into an output file;

determining time zone information for each record in the series of records; and

calculating the local time associated with data obtained from each node by referencing a time zone table.

2. The process for preparing data for upload to a data processor as recited in claim 1, further comprising the steps of:

rejecting node data having invalid syntax; and

reporting rejected node data in an audit error log file.

3. A process for preparing data for upload to a data warehouse comprising the steps of:

transforming a set of data comprised of several records relating to nodes on a network;

calculating a number of records contained in the set of transformed data;

calculating a load control number for uniquely identifying the set of transformed data;

querying the data warehouse to determine whether any records in the set of transformed data have previously been uploaded to the data warehouse; and

building table management utilities for uploading the set of transformed data to the data warehouse.

4. The process for preparing data for upload to a data warehouse as recited in claim 3, wherein the building step includes building a load control table management utility and building a node management table utility.

5. The process for preparing data for upload to a data warehouse as recited in claim 4, further comprising the step of assigning a unique identification number to each record in the transformed set of data.

6. The process for preparing data for upload to a data warehouse as recited in claim 5, further comprising the step of verifying the existence of templates, an audit initialization file, and the data warehouse.

7. The process for preparing data for upload to a data warehouse as recited in claim 5, further comprising the step of providing an error notification if a record in the transformed data is already contained in the data warehouse.

8. The process for preparing data for upload to a data warehouse as recited in claim 5, further comprising the step of determining beginning and end times for the set of transformed data.

9. The process for preparing data for upload to a data warehouse as recited in claim 5, further comprising the step of loading the unique identification number assigned to each record of the transformed data and the transformed data into the data warehouse.

10. A method for processing operational data received from nodes on a network for storage in a data warehouse comprising the steps of:

integrating the operational data received from the nodes on the network;

producing an output file from the integrated data which contains at least one record;

auditing the output file;

loading the output file onto the data warehouse;

comparing a current status of nodes on the network with a previous status of the nodes during a previous period of time to determine whether the current status is different from the previous status;

forming another output file containing the current status of each node on the network, whether the current status of each node is different from the previous status for that node during the previous period of time, and how long the node has been in the current status;

auditing the another output file; and

uploading the another output file into the data warehouse.

11. A system for preparing operational data for upload to a data warehouse comprising:

an integrated network control computer for retrieving and storing data relating to transactions occurring on nodes on a network, wherein said nodes are customer activated terminals; and

a data warehouse connected to the integrated network control computer, wherein said data warehouse having a data processor for receiving, transforming, and auditing the data relating to transactions occurring on the nodes;

wherein said data relating to transactions includes data regarding customer activated transactions occurring on the customer activated terminals and data relating to a status of customer activated terminal components; and

wherein said data warehouse processor transforms the data relating to transactions occurring on the nodes by determining a local time at which each customer activated transaction occurred, verifying the data associated with each customer activated terminal by using a mnemonic table, determining whether any data relating to a transaction is an orphan, computing a total elapsed time for each transaction and writing the transformed data into an output file comprised of records.

12. A system for preparing operational data for upload to a data warehouse comprising:

an integrated network control computer for retrieving and storing data relating to transactions occurring on nodes on a network, wherein said nodes are customer activated terminals; and

a data warehouse connected to the integrated network control computer, wherein said data warehouse having a data processor for receiving, transforming, and auditing the data relating to transactions occurring on the nodes;

wherein said data relating to transactions includes data regarding customer activated transactions occurring on the customer activated terminals and data relating to a status of customer activated terminal components; and

wherein said data warehouse processor generates records reporting the availability of nodes on the network by obtaining a previous status of nodes on the network, comparing a current status of nodes with the previous status, determining which nodes have changed status, and forming an output file containing the current, previous, and change statuses of each node.

13. A process for preparing and uploading data into a data warehouse comprising the steps of:

(a) obtaining a set of data from nodes on a network relating to the operation of and transactions occurring on each node and the operation of each node component in elementized message format;

(b) storing the set of data obtained as a series of records on an integrated network control computer;

(c) transmitting the stored set of data to a data warehouse processor;

(d) transforming the transmitted set of data into data base formatted records wherein said transforming step is comprised of;

(i) determining time zone information for data obtained from each node in the network;

(ii) rejecting node data having invalid syntax;

(iii) reporting rejected node data in an audit error log file;

(iv) calculating a local time associated with data obtained from each node by referencing a time zone table;

(v) verifying the data associated with each node by referencing a mnemonic table containing the location of individual node devices by number, name and mnemonic;

(vi) determining whether any data relating to a transaction is an orphan;

(vii) computing a total elapsed time for each transaction;

(viii) writing the transformed data into an output file comprised of records;

(e) auditing the transformed data contained in the output file wherein said transformed data auditing step is comprised of;

(i) verifying existence of templates, an audit initialization file, and the data warehouse

(ii) calculating number of records contained in the transformed data;

(iii) determining beginning and end times for the set of data obtained from the nodes on the network;

(iv) determining a load control key by querying the data warehouse for previous load control key and incrementing result of the inquiry by one unit;

(v) querying the data warehouse to determine whether records currently being audited have previously been uploaded to the data warehouse;

(vi) building a load control table management utility containing all instructions necessary for undertaking the current data base load;

(vii) assigning a unique identification number to each record in the transformed set of data;

(viii) building a node table management utility for loading data into the data warehouse and associating a load identification number with the transformed set of data;

(ix) providing an error notification if a record in the transformed set of data was previously loaded into the data warehouse;

(x) loading the unique identification number assigned to each record in the transformed set of data and the transformed data into the data warehouse;

(f) generating records reporting the availability of nodes on the network and node components wherein said generating step is comprised of;

(i) obtaining previous status of nodes and node components;

(ii) comparing current status of nodes and node compodnents with their previous status;

(iii) determining whether the current status of each node in the network or each node component has changed from the previous status of the node or node component;

(iv) determining a length of time each node and each node component has been in a current state;

(v) forming an output file containing the current status of each node component, whether the current state of each node component is different from the status recorded for that node during a previous upload and how long the node component has been in the current state;

(g) auditing records reporting the availability of nodes on the network and node components wherein said record availability auditing step is comprised of;

(i) counting a number of records reporting the availability of nodes on the network and node components;

(ii) counting a number of node components that have changed status from previous states of the node components;

(iii) determining earliest and latest time for the set of records reporting availability of nodes on the network and node components;

(iv) assigning a unique load identification number to number of records reporting node availability, the number of nodes having changed status and the earliest and latest time for the set of records reporting niode availability;

(v) producing a load control table management utility providing a set of instructions for loading audit information concerning the records reporting the availability of nodes on the network and node components;

(vi) producing a load control table management utility providing a set of instructions for loading the records reporting the availability of nodes on the network and node components;

(h) loading the number of records reporting node availability, the number of nodes having changed status, and the earliest and latest time for the set of records reporting node availability and the load control identification number to the data warehouse.