Altek Solutions Business Intelligence Blog

Integrating data is a constantly evolving strategy, especially as Business Intelligence becomes a more critical application. In order to ensure success, trustworthy data must be built and, to do this, tools need to be used to bring together disparate data and address data quality.

The evolution of Enterprise Information Management (EIM) has brought us to several strategies. Now, there is consolidation, federation, or a combination of the two. Business Objects offers solutions in the form of two products: Data Integrator and Data Federator.

Data Integrator is the classic ETL (Extract, Transfer, Load) tool that has a graphical user interface to consolidate your data. Data Federator is an EII (Enterprise Integration Information) tool that creates a virtual database to “federate” multiple data sources. Deciding on a potential strategy (or strategies) will be based on how and when the data will be accessed as well as development and monetary resources

Data Integrator (Consolidate)

Data Integrator excels at creating data marts/warehouses that include historical data, i.e. if the data needs to be archived from the ERP system. Usually, the data is processed on a nightly basis and brought over into a defined data model that can be reported against using a universe or other SQL based tool. Because this structure has been defined during design, performance should be addressed with the use of indexes, summary tables, etc.

Functional Overview of Business Objects Data Integrator

While time and money is needed to invest in developing and deploying a data warehouse, it is well worth the investment. Transforming and loading data into a warehouse or mart via a tool like Data Integrator will provide high performance and trusted, accurate data for reporting and dashboards.

Pros

• Allows for historical storage and archiving
• Data moved ahead of time (batch)
• Moves predetermined set of data
• Designer for systematic development
• Distributed batch processing and parallelism
• Aggregates disparate data from multiple source systems
• Handles large volumes of data
• Manages long and complex calculations
• Excellent query performance

Cons

• Long loading times could be encountered
• More expensive to develop (time and money)
• No ad-hoc data capabilities
• Real time data access can be more costly to attain

Data Federator (Federate)

Data Federator excels at a quicker implementation (thereby more cost effective), real time data access, and on-demand data access. Data Federator creates a virtual database using several different data sources, such as Oracle, SQL Server, MySQL, and text files. With this virtual database, there isn’t a need to worry about additional storage for the data, and the performance is handled by the Data Federator Query Server engine.

Functional Overview of Business Objects Data Federator

If time and money are an issue, as they often are, Data Federator can also provide a faster turnaround time for your ROI. With incremental deployment, multiple data sources can be tied together into a virtual database that can be used for both reporting and dashboards. Using the transforms available through the Designer tool and the Query Server, high performance and trusted data can be achieved as well.

Pros

• Access current data stored in sources
• Data moved on-demand (at query time)
• Only requested data returned
• Real Time data access
• Quicker development time – supports incremental development
• Optimized query engine – bind, hash, merge joins for efficient processing
• Reduces data storage and data transfer

Cons

• Still does query against original sources
  • Unlike Data Integrator that uses a specific BI database(s) or data warehouse.
• Only contains as much data as source system
  • If data is archived off source, data is no longer available in Federator.
• Query performance is good, but not as quick as a data warehouse

A Hybrid Approach – The Best of Both Worlds

While it seems that Data Federator could be used to replace ETL, in reality, it works best when used in conjunction with Data Integrator. Data Federator can be used as a prototype or first phase of data warehouse and solve connectivity issues, but it cannot make queries run faster on OLTP databases. Data Integrator with Data Federator can provide full data integration.

Using Business Objects Data Federator to add real-time or detailed data to an existing data warehouse.

For example, if the warehouse is used to store the historical data, Data Federator can virtually merge the warehouse and the ERP system to provide both historical and real time data (as seen in the picture below). This model includes the best of both worlds: storage, transformation, and performance through Data Integrator and real-time access and performance via Data Federator.

Using Data Integrator and Data Federator together ensures that your Business Intelligence users have trustworthy and timely information for your organization to make business decisions. Both of these tools are now available for your organization to determine the best strategy to deliver this information.

Related Posts:

1. Why Ad Hoc Reporting Won’t Work (part 2): We Need to Massage the Data First