Streamlined creation and updating of OLAP analytic databases

• US 10,311,047 B2
• Filed: 11/21/2016
• Issued: 06/04/2019
• Est. Priority Date: 10/19/2016
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Gitelman, Alex, Arivazhagan, Ravishankar, Schneider, Donovan, Field, Alex, Mantha, Pradeep, Gupta, Puneet
• Primary Examiner(s): Corrielus, Jean M
• Application Number:
  US15/358,128
• Publication Number:
  US 20180107705A1
• Time in Patent Office: 925 Days
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

The technology disclosed relates to creating and frequently updating multiple online analytic processing (OLAP) analytic databases from an online transaction processing (OLTP) transaction updatable system that includes transaction commit, rollback, and field level security capabilities. It also relates to transparently decoupling extraction from rebuilding of frequently updated OLAP analytic databases from the OLTP transaction updatable system.

Claims

1. A method including:

using a first extract process to extract database objects and fields from an online transaction processing (OLTP) transaction updatable system;

periodically and in parallel over multiple object sets of the OLTP transaction updatable system, using a second extract process to incrementally extract at least one of the database objects and fields updated in the OLTP transaction updatable system since the first extract process;

replicating the database objects and fields to multiple bulk updatable-only builder-input-datasets for respective object sets;

building multiple online analytic processing (OLAP) analytic databases based at least in part on the multiple bulk updatable-only builder-input-datasets for the respective object sets; and

periodically updating the multiple OLAP analytic databases from respective subset combinations of the builder-input-datasets using a separate load process based at least in part on respective OLAP dataflow build specifications and a rebuild schedule.

2. The method of claim 1, wherein the OLAP dataflow build specifications identify different subsets of fields.

3. The method of claim 1, wherein the rebuild schedule for the separate load process that builds the multiple OLAP analytic databases is decoupled from schedule for the extract process, thereby allowing the separate load process to independently update the multiple OLAP analytic databases from the respective subset combinations of the builder-input-datasets.

4. The method of claim 1, further including:

identifying objects and fields in the OLAP dataflow build specifications;

calculating a union of the identified objects and fields; and

automatically modifying the extract process to incrementally replicate the identified objects and fields.

5. The method of claim 1, further including:

automatically integrating the extract process and OLAP dataflow jobs by;

responsive to creation of a new and/or modified OLAP dataflow build specification followed by extraction of a new field by the extract process, automatically modifying the extract process to include the new field;

running an initial extract process to populate objects in the builder-input-datasets that include the new field; and

after the initial extract, including the new field in an incremental extraction.

6. The method of claim 1, further including:

creating an additional bulk updatable-only builder-input-dataset from an external system, wherein the external system is different from the OLTP transaction updatable system; and

during the periodic updating, merging the additional builder-input-dataset into the builder-input-datasets using the separate load process, following instructions in respective OLAP dataflow build specifications.

7. The method of claim 1, wherein the OLTP transaction updatable system includes transaction commit, rollback, and field level security capabilities.

8. The method of claim 1, wherein the multiple bulk updatable-only builder-input-datasets for respective object sets are locally cached datasets.

9. A method including:

accessing online analytic processing (OLAP) dataflow build specifications that identify database objects and fields in an online transaction processing (OLTP) transaction updatable system for building OLAP analytic databases;

resolving dependencies between the database objects and fields identified by the OLAP dataflow build specifications;

merging the database objects and fields into a first OLAP dataflow build specification and constructing a first extract process and a second extract process that covers the resolved dependencies between the database objects and fields in the OLTP transaction updatable system;

using the first extract process to extract the database objects and fields from the OLTP transaction updatable system;

periodically and in parallel over multiple object sets of the OLTP transaction updatable system, using the second extract process to incrementally extract at least one of the database objects and fields updated in the OLTP transaction updatable system since the first extract process;

replicating the database objects and fields to multiple bulk updatable-only builder-input-datasets for respective object sets; and

building multiple OLAP analytic databases based at least in part on the multiple bulk updatable-only builder-input-datasets for the respective object sets and the OLAP dataflow build specifications.

10. The method of claim 9, wherein the OLAP dataflow build specifications are defined using a graphical user interface, further including:

receiving a selection of visual elements of the graphical user interface representing the database objects and fields for the constructed extract process to incrementally replicate the database objects and fields to the builder-input-datasets.

11. The method of claim 10, further including:

generating a programmable code representing the defined OLAP dataflow build specifications; and

updating the OLAP dataflow build specifications and a corresponding extract process constructed based on the updated OLAP dataflow build specifications in dependence upon modifications to the programmable code.

12. A non-transitory computer readable storage medium impressed with computer program instructions, the instructions, when executed on a processor, implement a method comprising:

using a first extract process to extract database objects and fields from an online transaction processing (OLTP) transaction updatable system;

periodically and in parallel over multiple object sets of the OLTP transaction updatable system, using a second extract process to incrementally extract at least one of the database objects and fields updated in the OLTP transaction updatable system since the first extract process;

replicating the database objects and fields to multiple bulk updatable-only builder-input-datasets for respective object sets;

building multiple online analytic processing (OLAP) analytic databases based at least in part on the multiple bulk updatable-only builder-input-datasets for the respective object sets; and

periodically updating the multiple OLAP analytic databases from respective subset combinations of the builder-input-datasets using a separate load process based at least in part on respective OLAP dataflow build specifications and a rebuild schedule.

13. The non-transitory computer readable storage medium of claim 12, wherein the rebuild schedule for the separate load process that builds the multiple OLAP analytic databases is decoupled from schedule for the extract process, thereby allowing the separate load process to independently update the multiple OLAP analytic databases from the respective subset combinations of the builder-input-datasets.

14. The non-transitory computer readable storage medium of claim 12, implementing the method further comprising:

identifying objects and fields in the OLAP dataflow build specifications;

calculating a union of the identified objects and fields; and

automatically modifying the extract process to incrementally replicate the identified objects and fields.

15. The non-transitory computer readable storage medium of claim 12, implementing the method further comprising:

creating an additional bulk updatable-only builder-input-dataset from an external system, wherein the external system is different from the OLTP transaction updatable system; and

during the periodic updating, merging the additional builder-input-dataset into the builder-input-datasets using the separate load process, following instructions in respective OLAP dataflow build specifications.

16. A non-transitory computer readable storage medium impressed with computer program instructions, the instructions, when executed on a processor, implement a method comprising:

accessing online analytic processing (OLAP) dataflow build specifications that identify database objects and fields in an online transaction processing (OLTP) transaction updatable system for building OLAP analytic databases;

resolving dependencies between the database objects and fields identified by the OLAP dataflow build specifications;

merging the database objects and fields into a first OLAP dataflow build specification and constructing a first extract process and a second extract process that covers the resolved dependencies between the database objects and fields in the OLTP transaction updatable system;

using the first extract process to extract the database objects and fields from the OLTP transaction updatable system;

periodically and in parallel over multiple object sets of the OLTP transaction updatable system, using the second extract process to incrementally extract at least one of the database objects and fields updated in the OLTP transaction updatable system since the first extract process;

replicating the database objects and fields to multiple bulk updatable-only builder-input-datasets for respective object sets; and

building multiple OLAP analytic databases based at least in part on the multiple bulk updatable-only builder-input-datasets for the respective object sets and the OLAP dataflow build specifications.

17. The non-transitory computer readable storage medium of claim 16, wherein the OLAP dataflow build specifications are defined using a graphical user interface, implementing the method further comprising:

receiving a selection of visual elements of the graphical user interface representing the database objects and fields for the constructed extract process to incrementally replicate the database objects and fields to the builder-input-datasets.

18. The non-transitory computer readable storage medium of claim 17, implementing the method further comprising:

generating a programmable code representing the defined OLAP dataflow build specifications; and

updating the OLAP dataflow build specifications and a corresponding extract process constructed based on the updated OLAP dataflow build specifications in dependence upon modifications to the programmable code.

19. A system including one or more processors coupled to memory, the memory loaded with computer instructions, the instructions, when executed on the processors, implement a method comprising:

using a first extract process to extract database objects and fields from an online transaction processing (OLTP) transaction updatable system;

periodically and in parallel over multiple object sets of the OLTP transaction updatable system, using a second extract process to incrementally extract at least one of the database objects and fields updated in the OLTP transaction updatable system since the first extract process;

replicating the database objects and fields to multiple bulk updatable-only builder-input-datasets for respective object sets;

building multiple online analytic processing (OLAP) analytic databases based at least in part on the multiple bulk updatable-only builder-input-datasets for the respective object sets; and

periodically updating the multiple OLAP analytic databases from respective subset combinations of the builder-input-datasets using a separate load process based at least in part on respective OLAP dataflow build specifications and a rebuild schedule.

20. A system including one or more processors coupled to memory, the memory loaded with computer instructions, the instructions, when executed on the processors, implement a method comprising:

accessing online analytic processing (OLAP) dataflow build specifications that identify database objects and fields in an online transaction processing (OLTP) transaction updatable system for building OLAP analytic databases;

resolving dependencies between the database objects and fields identified by the OLAP dataflow build specifications;

merging the database objects and fields into a first OLAP dataflow build specification and constructing a first extract process and a second extract process that covers the resolved dependencies between the database objects and fields in the OLTP transaction updatable system;

using the first extract process to extract the database objects and fields from the OLTP transaction updatable system;

periodically and in parallel over multiple object sets of the OLTP transaction updatable system, using the second extract process to incrementally extract at least one of the database objects and fields updated in the OLTP transaction updatable system since the first extract process;

replicating the database objects and fields to multiple bulk updatable-only builder-input-datasets for respective object sets; and

building multiple OLAP analytic databases based at least in part on the multiple bulk updatable-only builder-input-datasets for the respective object sets and the OLAP dataflow build specifications.