Adding Analytics to On-Premises Transactional Data – What Are the Options?

6 min read

Part 2: What are the options available for Oracle Database clients, and what are the advantages and disadvantages of each option?

There are many Oracle Database transactional and operational systems that exist in on-premises environments across thousands of clients, and many of these clients would like to perform analytics on their data to gain insights into their business and improve decision making to gain a competitive advantage. In our prior blog post, we provided an overview of both IBM Db2 Warehouse on Cloud and Oracle Autonomous Data Warehouse. In the second part of our blog about cloud data warehouse offerings between IBM and Oracle, the discussion will center on the options available to Oracle clients that wish to perform analytics on transactional data. After looking at these options in more detail, a client can determine the cloud offering (IBM or Oracle) that is the easiest platform for data migration and lowest-cost option that provides the best performance for analytics processing of transactional data.

Continuing our discussion of the best analytics platform for Oracle transactional data from Part 1 of this blog, let’s now look at the options that are available to a current Oracle Database client who would like to perform analytics processing on their current transactional data.

Option 1: Add analytics capability to an existing Oracle transactional database

Add analytics capability to an existing Oracle Database transaction processing database by purchasing the Oracle Database Enterprise Edition database options: (1) Database In-Memory (columnar, in-memory tables) and (2) (Table) Partitioning (to allow large tables to be easily subdivided into smaller pieces to minimize the database memory required for columnar tables).

Advantages

  • A single environment to manage for both transactional and analytics processing.

Disadvantages

  • Will introduce risk to the on-premises transaction processing environment and contention for database resources.
  • Adding a new workload may require additional database cores, which will increase the cost of the existing Oracle Database licenses and support costs plus the new database options to add analytics processing.
  • Adding the two new database options will increase the Oracle Database Enterprise Edition license and support costs by ~73% over the cost of Oracle Database Enterprise Edition (base license, regardless of Oracle discount).
  • Oracle Database Standard Edition has NO database options available, so these clients cannot add capabilities that would provide a performant data warehouse platform.

Option 2: Subscribe to Oracle Autonomous Data Warehouse

Purchase a subscription to Oracle Autonomous Data Warehouse (ADW) in the Oracle Cloud to allow analytics processing of the on-premises transactional data.

Advantages

  • No risk is introduced to the existing on-premises transactional system.
  • Can tailor data warehouse growth for analytic processing requirements without impacting the existing on-premises Oracle Database environment and Oracle Database license/support costs.
  • Oracle ADW is fully managed, so there is minimal impact for the on-premises staff (only data and application management is required, database itself is fully managed).
  • Can continue to use existing Oracle Database application development skills (SQL and PL/SQL).
  • Oracle ADW Shared can increment compute by 1 OCPU and storage in 1 TB increments to provide compute and storage resources on demand.

Disadvantages

  • Oracle ADW is a specialized implementation of Oracle Database on Oracle Exadata, so no standard Oracle physical data movement methods can be used to copy the data or tables from an on-premises database to Oracle ADW. Oracle ADW will require that the client capture and modify the table DDL, unload the on-premises data, and then create the database objects and load data in Oracle ADW.
  • Oracle ADW Shared has NO dedicated resources other than Oracle CPUs (OCPUs). All storage, database memory, and Exadata Storage Server flash cache and persistent memory is shared among all Oracle clients using Oracle ADW on the same physical Oracle Exadata hardware.
  • Oracle ADW Dedicated has large growth increments (Exadata Quarter Rack, Half Rack, or Full Rack) and the dedicated infrastructure cost does NOT include compute (OCPUs are extra cost and charged by the hour, with a minimum initial configuration of OCPU and specific OCPU growth increments depending on the Oracle Exadata configuration selected).

Option 3: Subscribe to IBM Db2 Warehouse on Cloud

Purchase a subscription to Db2 Warehouse on Cloud (WoC) in either the IBM Cloud or the AWS Cloud to allow analytics processing of the on-premises transactional data.

Advantages

  • No risk is introduced to the existing on-premises transactional system.
  • Can tailor data warehouse growth for analytic processing requirements without impacting the existing on-premises Oracle Database environment and Oracle Database license/support costs.
  • Db2 WoC is fully managed, so the client only has to manage their data and applications.
  • Can continue to use existing Oracle Database application development skills (SQL and PL/SQL), as Db2 WoC provides Oracle compatibility.
  • Both Db2 WoC Flex and Flex Performance plans provide dedicated compute (cores and memory).
  • Moving data and tables to Db2 WoC is no more difficult than moving the data and tables to Oracle ADW. The database objects must be created and the data unloaded from the on-premises database and loaded into the Db2 WoC database.

Disadvantages

  • Db2 WoC has fixed initial configurations (16 cores for Flex and 48 cores for Flex Performance) and fixed compute increments (16 cores for Flex and 24 cores for Flex Performance). [1]

Wouldn’t it be easier to use Oracle Autonomous Data Warehouse since my existing data is contained within an Oracle Database?

Since the data source being discussed in this blog is in an Oracle transactional database, it would be simple to assume that Oracle Autonomous Data Warehouse is the easiest choice, as it is based on the same database engine. However, this is not the case; since this is a new database workload, it is no easier to use Oracle Autonomous Data Warehouse than Db2 Warehouse on Cloud. From a database object creation and data loading standpoint, Db2 WoC requires the identical configuration steps to move Oracle on-premises data to the data warehouse.

  • Create new database object DDL or modify existing database object DDL. (Remember, the analytics workload is new, so there may not be existing database objects that match the data warehouse table design. Db2 WoC with Oracle compatibility supports all Oracle data types, so there is no change in table definitions.)
  • Execute the table object DDL against the new data warehouse platform.
  • Unload the Oracle data from the on-premises database.
  • Load the data into the target cloud data warehouse.

When the Oracle compatibility capability of Db2 WoC is factored into the overall effort, application development of Oracle SQL and PL/SQL code for the new data warehouse can be executed against both Oracle Autonomous Data Warehouse and Db2 Warehouse on Cloud. Moving to Db2 WoC does NOT require any training in Db2 SQL syntax or different application development practices. Watch this video to see how easy it is to move Oracle transactional data to Db2 Warehouse on Cloud.

Since the ease of data migration and database object creation are comparable on both Db2 Warehouse on Cloud and Oracle Autonomous Data Warehouse, let’s examine Oracle Autonomous Data Warehouse and Db2 Warehouse on Cloud from a performance, ease of growth, and cost perspective.

A pricing example

To provide a quick pricing comparison, let’s look at an example data warehouse configuration where a client has 50 TB of raw data and has an analytics workload that regularly processes 40% of this data (20 TB). Since this data warehouse will be a production analytics workload, a dedicated compute platform will be required.

In this case, the two data warehouse cloud configurations that will be priced are Db2 Warehouse on Cloud Flex Performance (WoC Flex Performance) and Oracle Autonomous Data Warehouse – Dedicated (ADW-Dedicated). Based on the amount of data being processed within the data set, the data warehouse configurations will be made up of the following components:

  • Db2 WoC Flex Performance: Base Instance + 3 Compute Increments + 5 Storage Increments
  • Oracle ADW-Dedicated: Exadata X8M-2 Half Rack (Maximum of 192 OCPUs) + 108 OCPUs

Note: An Oracle Exadata X8M-2 Quarter Rack has a maximum of 96 cores, so the Half Rack configuration is required for Oracle ADW-Dedicated in this particular configuration.

Pricing comparison table

Pricing comparison table

Db2 WoC Flex delivers a dedicated data warehouse environment that is less than 10% of the cost. [2]

Conclusion

Based on the more flexible configurations, lower monthly subscription costs with dedicate compute resources, and built-in Oracle compatibility to allow Oracle application development skill re-use, IBM Db2 Warehouse on Cloud is the optimal data warehouse cloud choice for clients that want to perform analytics processing on their existing on-premises Oracle Database transactional data without impacting their mission-critical applications.

For your next step in deciding whether Db2 Warehouse on Cloud is the best choice, try this 90-minute tutorial that will provide more information about moving on-premises data to Db2.

[1] Db2 WoC initial configuration and compute increments for the Flex plan are based on IBM Cloud. Db2 WoC Flex Performance plans have identical initial configuration and compute increments on both IBM Cloud and AWS Cloud.
[2] Pricing is rounded to the nearest whole dollar amount. Oracle ADW pricing is based on the Oracle pricing information found at https://www.oracle.com/autonomous-database/autonomous-data-warehouse/pricing/. Db2 Warehouse on Cloud Flex Performance pricing is based on OnDemand pricing (no discount).

Be the first to hear about news, product updates, and innovation from IBM Cloud