Federated Data Warehouse Architecture

Summary: in this article, we will discuss ­federated data warehouse architecture in detail and its benefits.

Introduction to federated data warehouse

Nowadays, corporate usually has a set of heterogeneous system landscape that contains transaction systems and business intelligence tools which provide analytical capabilities for each individual department needs.

Each department views a business model from its own perspective. For example, a product in Sales can be defined as a material in Manufacturing and equipment in Service Management. In order to integrate those heterogeneous systems that aim to provide analytic capabilities across the different functions and departments, the federated data warehouse was invented.

A federated data warehouse is a practical approach to achieving the “single version of the truth” across the organization. The federated data warehouse is used to integrate key business measures and dimensions. The foundations of the federated data warehouse are the common business model and common staging area.

The architecture of federated data warehouse

Regional federation possible in federated data warehouse

The big organization has various regions that provide businesses to customers globally. Different regional data warehouses were built for each region to meet the specific business needs. A global data warehouse also was built to provide analytical capabilities to the executive at the global level.

The difference between the regional and global data warehouse systems is the nature of data resided at each system level. In the regional federated data warehouse architecture picture below, there are two data flows between regional and global data warehouses:

  • Upward federation – only fact data are moved from regional data warehouse to global data warehouse. The aggregation of data can take place at a global data warehouse after data integrated or during data movement.
  • Downward federation – in the downward federation, the reference flows from the global to the regional level. This ensures the consistency and integrity of data across the organization. Transactional data from corporate operational systems such as ERP and CRM are sourced at the global level and then extracted, transformed, and loaded into a respective regional data warehouse.
Regional Federation - Federated Data Warehouse
Regional Federation – Federated Data Warehouse

Functional federation possible in federated data warehouse

A functional federated data warehouse is used when the organizations have different data warehouses system was built for specific applications such as ERP, CRM, or subject-specific. The components of functional federated data warehouse architecture include data marts, custom-built data warehouses, ETL tools, cross-function reporting systems, real-time data store, and reporting as the picture below:

Functional Federation - Federated Data Warehouse
Functional Federation – Federated Data Warehouse

Benefits of federated data warehouse

  • Ease of implementation – Federated data warehouse integrated all legacy data warehouses, business intelligence systems into a newer system that provides analytical capabilities across the function. Federated data warehouse data do not try to rebuild a new system which potentially causes the major point of conflict.
  • Shorter implementation time – By integrating all legacy BI systems, the federated data warehouse approach has a shorter implementation time in comparison with the lengthy processing of building an enterprise data warehouse.
  • Cross-functional analytics requirements –  Cross function analytics requirements accomplished using common business modules across different BI systems of each department. A Federated data warehouse is the dynamic cooperation of various business intelligence systems to make them talk to each other.

A Federated data warehouse offers a practical solution for building the data warehouse. The iterative manner of the federated data warehouse approach helps reduce the implementation time and cost, therefore, provide an excellent proposition to the business.