Federated Data Warehouse

Introduction

Data warehousing has come a long way since its inception. In the current scenario, data warehouses are growing into rich systems capable of delivering key performance metrics to top management, strong analytical capabilities to middle management and corrective data back to operational systems based on information derived from the analytical system. The DW market is currently driven by business-driven solutions focused on domain specific challenges.

The current business environment of global village, consolidation in numerous industries and cutthroat competition originate newer and stiffer challenges for the data warehouse architects. Although, there are various methodologies available in the market to handle these challenges, most of the data warehouses fail to deliver the expected results. There are solutions available in the market which are infallible in theory and appear to address most of the challenges but are ridden by the implementation nightmares.

Federated Data Warehouse

A federated data warehouse is the integration of heterogeneous business intelligence systems set to provide analytical capabilities across the different function of an organization. It’s a realistic method to achieve the “single version of the truth” across the organization considering the political and implementation challenges. It aims to integrate the key business metrics, measures and dimensions. But it doesn’t aspire to create a single platform to carry out all the functional analysis.

The foundation of federated DW is the common business model of the organization. Common business model is a continuously evolving semantic understanding of the business in consent with all business units. This common business model is the initiation point for an iterative process of building the different business intelligent subsystem based on a common staging area.

The Need of Federated Data Warehouse

• Mergers & acquisitions – The way business has adopted the inorganic growth path has put many architects in unenviable positions. Merger & acquisitions have become routine events in today’s business scenario. Each acquisition brings in a big information bank along with it but the biggest challenge is to integrate this information with the existing BI system. It doesn’t make sense to abandon a fully functioning data warehouse
infrastructure, so the DW teams are forced to adopt a federated BI architecture. It’s certainly not an easy task to completely imbibe the new system with the existing BI architecture. The best way to handle it is to federate and integrate the two systems.

• Cross-functional requirements – Cross function analysis has become an everyday need for most of the enterprises; the need of cross-functional analysis must be emphasized while finalizing the architecture of the data warehouse. A federated DW stores the information of most of the functions, if not all, that an organization deals with. This information could be stored in one or more than one BI systems. FDW support the cross functional analysis using the common dimension across the different systems. Common dimension is an outcome of the common business model which defines the collective nature of business functionality. A federated DW is an active cooperation of multiple business intelligence systems where each system can talk to other BI system and fulfill cross-functional requirements.

• A speedy cost effective solution – The build time involved in federated data warehouse is enormously less when compared with enterprise wide data warehouse. One of the main reasons is that the federated DW tries to integrate the existing system by providing a common framework. It does not aspire to build a uniform foundation which is a tedious and lengthy process. The incremental nature of the federated DW reduces the long waiting period associated with most of the big DW, eventually reducing the cost as well.

• Ease of implementation – When building an enterprise wide data warehouses, architects have experienced a lot of politics and vested interests trying to mould the crucial decisions. In the real world scenario these factors cannot be sidelined and at times, influence the implementation in a larger manner. Federated DW approaches the problem in a more pragmatic manner and the idea to use the old BI systems by integrating them with the
newer system prevents the major point of conflict.

Architecture of Federated Data Warehouse

We envisage two types of federation possible in a federated data warehouse:
• Regional federation (or more appropriately regional versus corporate/global federation),
• Functional federation

Regional federation

This can be explained with the help of an example. A big organization has regional data warehouses for regional analytical requirements and a global data warehouse for the corporate requirements. The difference between the two systems (regional & global) is based on the nature of the data that is contained in each system. The global data warehouse stores data which is mostly summarized for the purpose of corporate analytics and reference data.

Reference data would contain confirmed dimensions and corporate level data like currency conversions etc. Regional warehouses store data based on the regional analytical requirements which generally has more detailed information. Reference data provide the integration platform for regional and global warehouse. The data flow from regional data warehouses to the global data warehouse is defined as upward federation and the data flow from global data warehouse to regional data warehouses as downward federation. For data
consistency and integrity, reference data should be made common across the various data warehouses. Uniform & consistent definition of reference data across the participating data warehouses ensures the ‘single version of truth’ across the federated architecture.

Data movement during upward federation:
• Upward federation would include the movement of fact data from regional data warehouses to the global data warehouse. If required, this data can be aggregated during movement.
Data movement during downward federation

• Reference data will flow from global to the regional level data warehouses. This flow would be strictly downward to ensure consistency and integrity of reference data.
• Transactional data that is available in corporate transactional systems (such as corporate ERP) will be sourced at the global level, cleansed and transformed and then moved to the respective regions.
• Summary data – The global summarization will happen in the global data warehouse and will be moved to the regional data warehouses. This can be useful in the analyzing how a particular Region is performing against the rest of the company.

Functional federation

A functional federated data warehouse will be the candidate when an organization has built data warehouses which are either subject specific, packaged solutions or built for a specific enterprise application. The federated data warehouse architecture is the “big umbrella” that provides the foundation and environment to facilitate and enable business analysis and decision support in this heterogeneous environment. A functional federated data warehouse has room for all the components of a contemporary BI application of a large and complex business entity. Typically it should contain the following components:

• Packaged data warehouses (DWs) and data marts (DMs)
• Custom built data warehouses and data marts
• Real time data store & real time data reporting
• Custom built analytical applications
• Online analytical processing (OLAP) tools
• Extraction, transformation and load (ETL) tools
• Cross functional reporting systems

Approach for Federation

The following steps should be taken when architecting a federated data warehouse:
• Define the federated data warehouse goals and business requirements
• Define team structure & assign roles and responsibilities
• Document existing data warehouses & BI systems. The information to be documented is - Business areas addressed by the existing systems, target users, reporting and analytical capabilities provided, the data sourcing strategy and cleansing/transformations methodology while moving the data into these systems
• An analysis needs to be done to identify whether it is a case for federated architecture
• Define the common business model which should provide the basis for common dimensions
• Define an integration strategy for federation. Identify inter-dependency between existing and new to-be developed data warehouses
• Perform the integration of existing data warehouses into the new architecture in small phases, giving first priority to the areas which are critical for business