Delphix enables Agile Analytics

Introduction

When it comes to enabling business analytics there are two main schools of thought.

1.    Specialized Analytics

There are several specialized Analytics or Warehouse Systems and appliances, examples include Teradata, Netezza, Exadata and Hadoop.

These systems require that data is routinely copied, synchronized and translated into their own specific data format for processing.  This often means that data has to be captured from production during short time windows but does mean that subsequent analytics processing is offloaded from production sources.

These systems suffer from having out of date data, they require substantial storage to retain their own large copies of data and a completely separate and often expensive and proprietary infrastructure.  Note there are not too many new proprietary analytics and warehouse systems or appliances being produced, many organizations are focusing on Hadoop which is seen as lower cost, more open and easier to scale.  However Hadoop still requires (at least 3) copies of the data and mechanisms to keep its data files in sync with source data.

An additional challenge is how do you secure that data if needs to be masked.

2.    Abstract Analytics

There are several Analytics Tool that abstract underlying data sources and connect directly to native databases and files, examples include Microsoft SQL Server Analysis Services (SSAS), Oracle Business Intelligence Suite and Tableau.

These systems can avoid referencing out of date data by directly connecting to production sources.  They also avoid the need to maintain a large physical copy of the data.

However running workload on production to prepare and refresh aggregations and analytics cubes is a strain on production that should be avoided.  Also any queries that require to be resolved by the underlying database using pass through will also place workload on production.

Using production directly also means there is no opportunity to secure the data through data masking.

Enter Delphix                          

Delphix can provision, production or masked, virtual copies of all the data sources necessary to support your analytics system.

Delphix for Specialised Analytics

In the simplest case Delphix can support Specialized Analytics systems by allowing all the data refresh activity to be driven directly off of virtual data copies.  This takes the pressure off of production, eliminates the time window constraint and also allows for the data to be masked before being synchronized to the Analytics system.

Delphix is ideal for this scenario since can curate all the source data necessary to populate your analytics system.

Delphix can feed your warehouse

Delphix for Abstract Analytics

However there is much more benefit available by using Delphix with an Abstract Analytics system.  You can enjoy the benefits offered by Microsoft SSAS, Oracle OBIEE, Tableau, etc which do not need a separate siloed proprietary copy of substantial data sets.

Delphix provides fresh copies of production data providing scalable up-to-date analytics

With Delphix you can also avoid the potential performance and security risks these systems can create when underpinned directly by production databases.   Abstract Analytics systems can run directly on Delphix provisioned virtual data copies.  This completely isolates the analytics workload from production.

The virtual databases underpinning the Analytics Engine can be refreshed from production independently or in synchronization.  You can also have multiple versions of the virtual data sets.

To improve performance the virtual databases can have schema changes applied, such as indexes, materialized views, etc. which better support the analytics engine requirements.

You can even scale out by leveraging as many copies of the databases as necessary to parallelize the query activity across as many servers as possible.  You can leverage underutilized server capacity to support your analytics processes.

Finally you could perform some or all of this activity on a cloud based Delphix deployment.

Summary

Delphix enables faster, fresher, scalable analytics whilst offloading production, avoiding a siloed investment and leveraging your existing database licenses and database server investments.

Accelerate ETL by Leveraging Delphix Virtual Databases

Many downstream database and analytics systems need to be routinely refreshed with processed versions of the latest changes from production.   Typically the new data needs go through an ETL process to prepare it for downstream use in an Analytics Engine, Warehouse, Data Mart, Reader Farm, etc.

There are several major benefits Delphix offers in this scenario.

Firstly Delphix maintains its own up-to-date copy of source data by seamlessly synchronising with the source databases and retaining this in a secure central data hub.

Second, all ETL processes can be run directly from virtual databases (VDBs) provisioned by Delphix, completely offloading production and without the need for physical copies.  You can create as many VDBs as you want to run these processes in parallel.

Third, if you wish to keep pace with new transactions you can use Delphix Live Sources, which uses an Oracle Standby to keep the Delphix dSource copy continuously updated but also enables reporting using Oracle Active Dataguard.  If synchronising with a target Hadoop cluster you can run Apache Sqoop jobs against a virtual data warehouse that is constantly being updated from the physical data warehouse.  You can run your ETL directly on the changed data stored in the virtual data warehouse and import only the processed changes into your Hadoop cluster.

In addition it is also possible to use Delphix to help identify deltas between two versions of a source database by creating two VDBs based on two point-in-time snapshots.

If the two VDBs (snap1 and snap2) are based on an Oracle source database, first you simply create a database link connecting to snap1 from snap2.

On snap2 you create tables to store:

1.     INSERTS, rows that have been added since the old version (snap1)

2.     UPDATES, rows that have been modified between the two versions

3.     DELETES, rows that no longer exist in the new version (snap2)

You can identify INSERTS, UPDATES and DELETES between the two versions using the MINUS set operator between the corresponding tables in the two VDBs.

For example to find and populate my local table “delta_inserts” with new rows, I use something like:

           

/* create empty table with correct structure */

create table delta_inserts as select * from table where 1=0;

alter table delta_inserts nologging;

/* populate table with new rows only */

insert /*+ append */

  into delta_inserts

  with new_rows as

  (

    select id from table@snap2

     minus

    select id from table@snap1

)

/* fetch entire row from latest version */

select latest.*

  from table@snap2 latest,

       new_rows delta

 where latest.c_id = delta.c_id;

The SQL is similar for UPDATES and DELETES.  However for UPDATES we need to compare the entire row in the minus operation since any column value might have changed.  For DELETES we only need to store the key or id we will use to identify the rows to delete during the apply process.

Now we have two tables containing copies of changed rows and one with a list of rows to be deleted.

We can now run our ETL processing on only the changed rows held in delta_inserts and delta_updates making this process substantially more efficient than running against the full dataset.

The final phase is to implement an apply process to merge the processed deltas with the target database.  The options for applying the changes will depend on the nature of the changes and the type of the target database which might be relational, Hadoop (HDFS), etc.

For Hadoop you can use Apache Sqoop to perform incremental imports and merges.  For relational databases there are more options.

The apply process would usually

1.     delete all the rows in the target table having the keys saved in delta_deletes

2.     insert or append all the rows to the target table having processed rows saved in delta_inserts

3.     merge or update all the rows in the target table having processed rows saved in delta_updates

If there are foreign key dependencies then you might need to disable and re-enable them before and after the apply process.

In summary, Delphix is ideal for provisioning data sets for ETL processing.  Delphix Virtual Databases (VDBs) can also underpin the process to identify changes between two versions of the same database. ETL jobs can then operate against only the changed rows and finally once processed these changes can be used to refresh the target database using a simple apply process.

Learn more at Delphix Agile Data Management

Delphix in a Nutshell

Business Goals and Project Challenges

All successful businesses need to build and maintain reputation, increase revenues, improve efficiency, stay competitive, ensure security and remain compliant.

To achieve these goals businesses need to attract and retain customers, increase adoption levels and effectiveness of internal applications, maintain security and avoid and correct problems.  

On top of that many customers are curating and analysing more data to enable them to track and predict patterns of behaviour to allow them to innovate.  All of these requirements necessitate constant changes to applications.

However most customers still have projects taking longer than planned, limited release cycles and a growing project backlog. 

These growing requirements for constant changes to applications and faster releases is why there is so much interest in Agile Development.

How does Virtual Data enable Agile Development?

One of the primary bottlenecks for application and analytics projects is lack of access to adequate environments.   This often manifests itself with shared, reduced sized and out of date environments.  This is caused by the difficulty in provisioning and maintaining enough environments to enable developers, testers, analysts and administrators to perform their jobs efficiently.

Virtual Data allows near instant access to as many full copies of environments as required, synchronised to any point in time with minimal storage overhead.  Virtual Data accelerates all projects by enabling parallel development and testing.  Virtual Data enables high quality and high frequency releases through full provisioning and maintenance of continuous integration environments.

How does Delphix implement Virtual Data?

Databases are made up of files containing data blocks.  Over time data blocks are added, updated and occasionally deleted.  A database version is a consistent set of database blocks relevant to a point in time.  As databases grow an increasing proportion of data blocks remain the same over time.

Delphix captures an initial copy of a database and then continuously captures all changes to that database over time.   All data blocks and transaction logs captured and created by Delphix are compressed.

A database version is represented by a snapshot or timecard. When a timecard is created an incremental backup is taken of the source database and Delphix stores compressed copies of all the changed blocks.  The timecard is implemented as a tree of pointers to the set of blocks consistent with that point in time.  Delphix stores the distinct set of data blocks for all timecards for a given database.  Each data block in Delphix is pointed to by all timecards that have a dependency on that block.

A Virtual Database can be created from any point in time since the source database was first copied into Delphix.  If the Virtual Database is to be created from an existing timecard it will share the same tree of pointers as the timecard.  If the Virtual Database requires provisioning from a different point in time then Delphix will perform point in time recovery to the required time, create any required new versions of data blocks and the Virtual Database timecard will then have its own tree of pointers.   

So when a Virtual Database is first created the only storage overhead are the blocks unique to that Virtual Database.  Subsequently any blocks changed by DML and DDL activity on the Virtual Database are private to that Virtual Database.

This is how Delphix can provision any number of database copies, near instantly with minimal storage overhead.  Delphix applies a similar approach to files and folders allowing customers to virtualise all data.

Summary

Virtual Data enables fast provisioning of multiple full copy environments, enabling parallel development, testing and analytics which in turn enables faster, more frequent and higher quality project releases.  Virtual Data directly supports all businesses to meet their goals.

Learn more at Delphix Agile Data Management