Method and apparatus for automatically creating a data warehouse and OLAP cube

• US 9,684,703 B2
• Filed: 04/27/2005
• Issued: 06/20/2017
• Est. Priority Date: 04/29/2004
• Status: Active Grant

• Current Assignee: PrecisionPoint Software Ltd.
• Original Assignee: PrecisionPoint Software Ltd.
• Inventors: Geary, Nigel, Jarvis, Beverley, Mew, Chris, Gore, Helen
• Primary Examiner(s): Hoang, Son T
• Application Number:
  US11/115,475
• Publication Number:
  US 20050246357A1
• Time in Patent Office: 4,437 Days
• Field of Search: 705 26, 707 4, 707200
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A data warehouse design that combines data from multiple source ledgers or modules is constructed and from that an associated, single physical Star or Snow-Flake schema and OLAP hyper-cube analytic structure is generated to enable cross-functional analysis of any those multiple source ledgers or modules. The step of constructing the data warehouse or the step of generating the associated OLAP cube is achieved without significant human intervention using software.

Claims

1. A method of constructing a data warehouse that combines data from multiple financial accounting transactional ledgers into a single fact table;

including the following steps of;

(a) connecting to an Enterprise Resource Planning (ERP) data store that stores the multiple financial accounting transactional ledgers, wherein

the multiple financial accounting transactional ledgers are a classification and summarization of financial transactions, and

the multiple financial accounting transactional ledgers include General Ledger, legally required for audits, and one or more of the following ledgers;

Sales (Accounts Receivable), Purchase (Accounts Payable), Invoice (Order Processing), Inventory, and Fixed Assets;

(b) using Structured Query Language (SQL) scripts to extract and combine data from the multiple financial accounting transactional ledgers existing in the ERP data store, including the general ledger, to automatically create and populate a single fact table in the data warehouse which integrates transactional data from the multiple financial accounting transactional ledgers;

(c) generating, from the data warehouse, an associated, single physical Online Analytical Processing (OLAP) cube such that the single physical OLAP cube enables cross-functional analysis of data from the multiple financial accounting transactional ledgers;

(d) executing stored procedures against the data warehouse to determine various metadata structures required to create or update the single physical OLAP cube to facilitate analysis and reporting of the data, wherein

the stored procedures take as parameters a list of new fields to be added to the data warehouse, and

the stored procedures loop through the list of new fields and add each field in the list into appropriate tables in a star schema in the data warehouse.

2. The method of claim 1, in which the step of constructing the data warehouse or the step of generating the single physical OLAP cube is achieved without significant human intervention using software.

3. The method of claim 2, in which the data warehouse is a generic warehouse that is capable of being modified without technical knowledge of the software or data structures.

4. The method of claim 2, in which the single physical OLAP cube is a generic OLAP cube that is capable of being modified without technical knowledge of the software or data structures.

5. The method of claim 1, in which the data warehouse is created using SQL joins from the multiple financial accounting input transactional ledgers to generate additional, detail rows in the single fact table.

6. The method of claim 5, in which the SQL joins are made between one or more of the following financial accounting source transactional ledgers or modules:

(a) the General Ledger and inventory/stock;

(b) the General Ledger and sales/purchase ledger;

(c) the General Ledger and invoice/credit.

7. The method of claim 5, in which orders and other entries that do not have a financial impact to the General Ledger are placed into the single fact table.

8. The method of claim 5, in which invoice and credit memo records for both the Accounts Receivable and the Accounts Payable ledgers are joined together via a SQL command to create an intermediate InvoiceCredit table representing line details for sales/purchasing and/or invoices/credit memos.

9. The method of claim 8, in which information in the Accounts Receivable/Payable ledgers that is not necessarily generated by an invoice or credit is included in an additional intermediate ExtraSubLedger table.

10. The method of claim 9, in which the General Ledger, the InvoiceCredit and the ExtraSubLedger tables are then joined together to form the single fact table.

11. The method of claim 10, in which the single fact table includes fields functionally equivalent to one or more of the following fields:

Document Number, Document Type, General Led Description, SubLedger Description, Invoice Line Description, Order Number and External Document Number.

12. The method of claim 10, in which one or more of the following tables holding transactions are also joined to the General Ledger, InvoiceCredit and ExtraSubLedger to provide further analysis fields:

inventory tables, fixed asset ledgers, and bank account ledgers.

13. The method of claim 1, in which fields in data warehouse tables are dynamically updateable in that the fields change in accordance with an underlying ERP accounting structure and system data when the warehouse is updated with a new snapshot of ERP system data.

14. The method of claim 1, in which the stored procedures execute queries to determine and identify one or more of the following types of metadata in the data warehouse;

(a) dimension names;

(b) fields containing member names for each dimension;

(c) hierarchies of members in each dimension.

15. The method of claim 14, in which the application is written to loop multiple times through a series of scripts to create multiple sets of the stored procedures, altering each set of the stored procedures slightly based on the structures and data in the ERP system.

16. The method of claim 14, in which the stored procedures are defined in an XML file.

17. The method of claim 14, in which, when a periodic update is automatically triggered, the application will execute certain stored procedures which will scan through ERP ledger data and recreate the data warehouse, and refresh the data in the single physical OLAP cube.

18. The method of claim 1, in which an application uses an Extensible Markup Language (XML) the containing instructions for creating multiple stored procedures which;

when executed, initiate and control a process whereby the relevant and appropriate data is extracted from the ERP system into the data warehouse and the single physical OLAP cube.

19. The method of claim 18, in which the stored procedures initially exist in the file in a generic state, but structure of the stored procedures is changed dynamically at runtime, according to the responses given by a user during installation of the application.

20. The method of claim 18, in which the application determines dynamically, and utilizes during the installation of the application, one or more of the following:

(a) ERP system product version;

(b) table/field naming convention used;

(c) any other naming differences between product versions;

(d) number of levels of totaling within the account hierarchy;

(e) granularity of financial periods used.

21. The method of claim 18, in which the stored procedures are adaptive and will change execution strategy according to changes in any of the following parameters:

(a) installed version of the ERP system;

(b) table and field structure of the ERP system;

(c) internal switches within the XML file;

(d) external switches in an external license key file.

22. The method of claim 1, in which an application adds new dimensions to the single physical OLAP cube based on the list of new fields.

23. The method of claim 1, in which results of error checking can be automatically reported to an implementation or support team via a Diagnostic Support Web Service.

24. The method of claim 1, in which one or more of the following types of reporting or analysis is performed within the single physical OLAP cube:

(a) Product/Customer/Salesperson analysis against the Chart of Accounts;

(b) Aged Credit and Aged Debt comparison to obtain overall health of a company;

(c) Complete Item/Customer profit calculated including adjustments, cost of materials and indirect costs;

(d) Dashboard style information on each customer including outstanding orders, debt, invoice history, suppliers and warehouses used to fulfill orders;

(e) Full product analysis including orders, stock, previous sales, cost adjustments;

(f) Discount analysis by product/customer/salesperson;

(g) Status of orders, including quotes;

(h) Asset management;

(i) Cash flow planning and reporting.

25. The method of claim 1, in which the multiple financial accounting transactional ledgers relate to one type of information.

26. The method of claim 1, in which, in order to ensure that all ledger information from the ERP system has been combined correctly, comparing resulting account balances in the data warehouse against corresponding balances in the ERP system.