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

• US 20050246357A1
• Filed: 04/27/2005
• Published: 11/03/2005
• Est. Priority Date: 04/29/2004
• Status: Active Grant

• Current Assignee: PrecisionPoint Software Ltd.
• Original Assignee: PrecisionPoint Software Ltd.
• Inventors: Gore, Helen, Geary, Nigel, Mew, Chris, Jarvis, Beverley
• Granted Patent: US 9,684,703 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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. The present invention is based on the insight that the technical bias against generating individual data marts and OLAP cubes from multiple source ledgers is not well founded. Previously, the norm has been to generate a single physical OLAP cube for each individual ledger—hence one for the General Ledger, a further one for sales order processing etc. This prior art approach makes cross functional analysis multiple source ledgers and sub-ledgers difficult since it requires the creation of a further level of physical or virtual cubes across multiple physical cubes and complex querying. This is no longer necessary with the present invention.

Claims

1. A method of analysing data from multiple source ledgers or modules;

comprising the steps of;

(a) constructing a data warehouse that combines data from multiple source ledgers, sub-ledgers or modules;

(b) generating an associated, single physical OLAP cube to enable cross-functional analysis of data from those multiple source ledgers or modules.

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

3. The method of claim 2 in which the data warehouse that is constructed using software 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 OLAP cube that is generated using software 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 multiple input ledgers or modules to generate additional, detail rows in a central fact table that also holds summary information.

6. The method of claim 5 in which the SQL joins are made between one or more source ledgers or modules, such as:

(a) General Ledger and inventory/stock

(b) General Ledger and sales /purchase ledger

(c) General Ledger and invoice/credit (sales/purchase order processing).

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 central fact table.

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

InvoiceCredit”

table).

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

ExtraSubLedger”

).

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

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

Document Number, Document Type, GL 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 GL, InvoiceCredit and ExtraSubLedger to provide further analysis fields:

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

20. The method of claim 3 in which there are stored procedures that are executed against the data warehouse to determine the various metadata structures required to create or update the OLAP cube to facilitate analysis and reporting of data;

the stored procedures take as parameters a list of new fields to be added to the data warehouse and the stored procedures then loop through this list of new fields and add them in to the correct tables in a star schema in the data warehouse.

21. The method of claim 4 in which there are stored procedures that are executed against the data warehouse to determine the various metadata structures required to create or update the OLAP cube to facilitate analysis and reporting of data;

and the stored procedures take as parameters a list of new fields to be added to the data warehouse and the stored procedures then loop through this list of new fields and add them in to the correct tables in a star schema in the data warehouse;

in which an application adds new dimensions to the OLAP cube based on these new fields.

25. The method of claim 1 in which, in order to ensure that all the ledger information from an ERP system has been combined correctly, there are stored procedures which check the resulting account balances in the data warehouse against the equivalent balances in the ERP system.

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

27. The method of claim 1 in which one or more of the following types of reporting or analysis is performed:

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

(b) Aged Credit and Aged Debt comparison to obtain overall health of 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 etc

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

(g) Status of orders, including quotes

(h) Asset management

(i) Cash flow planning and reporting

28. The method of claim 1 in which the source ledgers, sub-ledgers or modules relate to one type of information.

29. The method of claim 28 in which a different single data warehouse and associated single OLAP cube is generated for each different type of information.

30. The method of claim 29 in which the following are each different types of information:

financials;

customer relationship management;

supply chain analysis.

31. Computer software adapted to perform the method of claim 1.

32. Computer hardware when programmed with the software of claim 31.

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

14. The method of claim I in which stored procedures are executed against the data warehouse to determine various metadata structures required to create or update the OLAP cube to facilitate analysis and reporting of the data.

15. The method of claim 14 in which these 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 the member names for each dimension;

(c) hierarchies of the members in each dimension.

16. The method of claim 14 in which an application uses a file containing instructions for creating multiple stored procedures which, when executed, initiate and control a process whereby the relevant and appropriate data is extracted from an ERP system into the data warehouse and the OLAP cube.

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

18. The method of claim 16 in which the application determines dynamically (by executing appropriate queries) and utilizes during the installation process, 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 (and advanced dimensions);

(e) granularity of financial periods used, e.g. years/months vs. weeks &

quarters.

19. The method of claim 16 in which the stored procedures are adaptive (self modifying) and will change their 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 15 in which the application is written to loop multiple times through a series of scripts to create multiple sets of stored procedures, altering each set of stored procedures slightly on the basis of the structures and data in the ERP system.

23. The method of claim 15 in which the stored procedures are defined in an XML file.

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