Single command data warehouse table update

Abstract

Information warehouse table data is updated through a full outer join between warehouse tables and source tables in response to a single query language command statement of a relational database computer language. The single command statement comprises table mapping instructions for updating the warehouse tables that define matching conditions, delete instructions, archive instructions, and update instructions. The full outer join inserts rows in the warehouse tables for source table data rows that do not have corresponding matching rows in the warehouse table. Data rows in the warehouse tables without corresponding matching source table rows are deleted if a value of a parameter in the warehouse table system is set to delete; updated if the parameter is update; and updated with archive values and a new table row inserted with existing values if the parameter is archive.

Description

RELATED APPLICATION[0001]This application is a continuation of U.S. application Ser. No. 13 / 285,281, filed Oct. 21, 2011.TECHNICAL FIELD[0002]The present invention relates to updating dimensional data and fact tables in information warehousing databases.BACKGROUND[0003]The terms “Information Warehouse” and “Data Warehouse” refer generally to relational database management systems (RDBMS) and to the associated programming language command tools and processes used in the storage, retrieval and analyzing of the data in database tables structures defined therein. Information Warehouse databases are generally organized with respect to dimensional data and fact tables. A dimension is a data element that categorizes each item in a data set into non-overlapping regions, and provides structured labeling information to otherwise unordered numeric measures. For example, “Customer”, “Date”, and “Product” are all dimensions that could be applied meaningfully to a sales receipt.[0004]Fact tables ...

AI Technical Summary

Benefits of technology

The present invention provides a method for updating table data in an information warehouse via a single query language command. This allows for efficient and quick updates of data from source tables to warehouse tables. The method includes performing a full outer join between the source and warehouse tables, deleting rows based on a treat history parameter, updating rows with new data, and inserting new rows into the warehouse table if the treat history parameter is set to update or archive. The method also includes validating the user's authority to update the source table data and ensuring that other users are locked out during the update. The technical effects of this invention include improved efficiency and speed in updating table data in an information warehouse.

Problems solved by technology

Updating data warehouse tables may be complicated and costly in terms of programming overhead, generally requiring the design and implementation of batch flows or procedures to execute a series of individual programming language database operation commands, and which often must be customized for every update event.

Such process not only leads to undesired overhead for build costs and ongoing maintenance, but as the update process can fail at anytime during any of multiple steps, table locking and restart procedures must also be taken into consideration.

Images