Virtually Yours?

Most of us will be familiar with the challenge of providing a common view of a type of data from multiple heterogeneous systems. This could be for providing consolidated data for management reporting, or a 360 degree view of say customer data from several “MDM” sources, or even just getting data damn quick for that BI or legislative reporting requirement.

The traditional approach is Extract, Transform and Load (ETL) to another store (eg a Data Warehouse) and then report from there.

However, that’s not the only way. Enterprise class Data Virtualisation products such as Composite Software have now made the promise of Data Federation a realistic alternative for some use cases – let’s have a look at a few.

Data migration and take on ETL vs EII (or both?)

By now most of us will be familiar with the purpose of Extract, Transform and Load tools.  Less well known however are the capabilities of the Data Virtualisation or Enterprise Information Integration  tools such as Composite or MetaMatrix.

Broadly speaking these provide the capability to access data from a massively wide variety of sources without having to move it from the source system.  They have extremely rich caching and aggregation capabilities and in my experience have dramatically reduced the time to provide rich access to data.  I once heard them described as “views on steroids”.

Can EII / Data Virtualisation add value to Data Warehousing?

The use of EII technology in Enterprise Data Warehousing and for data take-on is something that demands serious consideration.  There are several ways in which EII can add value to DW solutions; here are just 3 to consider:

a)        Prototyping Data Warehouse Development

During DW development, the time taken for schema changes, adding new data sources and providing data federation are often considerable.  Using Data Virtualisation to prototype a development environment means you can rapidly build a virtual DW rather than a physical one.  Reports, dashboards and so on can be built on the virtual DW.  After prototyping the physical DW can be introduced.

b)        Enriching the ETL process

Frequently new data sources particularly from ERPs are required in the DW.  All too often the ETL lacks data access capabilities to complex sources.  Tight processing windows may require access, aggregation & federation activities to be performed prior to the ETL process.  The powerful data access capabilities of EII provide rich access and federation capabilities which can present virtual views to the ETL process which continues as though using a simpler data source.

c)         Federating Data Warehouses

How many organisations have more than one DW?  Is the Information in each completely discrete?  I don’t think so.  Data Virtualisation provides powerful options to federate multiple DW’s by creating an integrated view across them.  This has particular relevance in providing rapid cross warehouse views following a merger or acquisition.

Data take on considerations ETL or EII?

When providing data into a DW, the use of ETL or EII (or both) needs care.  Some of the key considerations include:

Requirement	Implication
Data replicated in DW and Operational System	Update in one or both locations? If data is physically in two locations are there compliance issues (e.g. SoX, HIPPA etc)
Data Governance	Is the data only managed in the originating Operational System?
Currency of the data	How up to date are the data requirements of the DW? Is there a need to see the operational data?
Time to solution	How rapidly is a solution required?
Life expectancy of source system(s)	Are the source systems likely to be retired?
Need for historical / summary / aggregate data	How much historical, aggregated data is required in the DW solution?

So whilst not applicable for every use case, the reality of having your data virtually served is well and truely there.