Real Time Business Intelligence
Traditional BI deployments result in a time lag between the time data is entered in a transactional system and the time at which this data is available to the analytical systems for reporting & analysis. This time lag is dependent on the data extraction process (ETL) to extract, transform & load the data warehouse, aggregate and present the data in the form of reports, dashboards and alerts to the end user. The architecture of the BI platform deployed and the business requirements of an organization mandate the schedule of data extraction processes. For a typical manufacturing organization having an ERP system, to capture transactional data the ETL processes are generally scheduled to run on a fortnightly, weekly or daily basis. The complexity of the KPI’s measured through the reports governs the time spent on data aggregation and presentation.
However, with constantly changing market dynamics, today the decision makers and analysts want to monitor and analyze the performance of their business on the latest available data i.e. on a near-real-time basis. This means that a Real-Time BI solution should be able to deliver information just-in-time when required by the user; much faster than normal BI platforms.
Real-time Business Intelligence is required by event-driven enterprises where the action to be taken by the business user is dependent on the latest data. Some of the industries where Real-Time BI solutions are required include:
- Financial Services - to monitor Credit Card frauds and Risk Management (market, credit, ops)
- Retail - for Price Optimization, stock replenishment & inventory management
- Consumer Products - to gain insight into sales promotion effectiveness
- Telecommunications - to report customer balances
Apart from the above mentioned niche areas there is a growing need for few metrics to be reported on a real-time basis in the traditional manufacturing and distribution organizations. These metrics include the latest updated customers, suppliers, orders, quotes etc.
Oracle's Solutions for Real-Time BI Reporting
Oracle provides Oracle Data Integrator (ODI) to integrate data from various heterogeneous data sources and perform integration in real time. ODI is an E-LT (Extract, Load & Transform) tool and part of Oracle's Fusion Middle ware product suite. Real-time data integration is possible through ODI's Changed Data Capture (CDC) feature. ODI utilizes a set of out-of-the-box Journalizing Knowledge Modules to monitor the data sources and capture any new/modified data and makes the new data available for analysis through the already defined data integration mappings. This helps in minimizing the data load time as only new/ changed records are loaded. The data integration process is started automatically once the changed records reach a predefined limit or after a predefined time limit is elapsed, and thus, provides data from different sources to be analyzed on a real-time basis.
Similarly for Oracle BI Applications, Siebel OLTP change capture techniques are embedded into DAC (Data warehouse Administration Console) which helps in providing near Real-time reporting on some of the Seibel CRM metrics.
Including Real-time Metrics in existing BI deployments
For existing BI deployments, real-time data analysis can be achieved by minimizing the time consumption in ETL processes or customizing BI platforms to efficiently query required data directly from the transactional system.
Apart from using faster processors, one of the approaches to optimize ETL process for better performance is to simplify the data transformations. However, most of the transformations cannot be simplified because of data cleansing, aggregation and reporting requirements. The analysts need to identify the specific metrics where real-time data would actually impact the business users in making decisions and carrying out their responsibilities. The transformation of data from the source system to the warehouse could be simplified by splitting the mappings affecting the identified metrics from existing complex mappings and minimizing cleansing & aggregation processes for these mappings. Incremental load through these bifurcated mappings could then be scheduled on intra-day basis to achieve near real-time BI. This approach would be most successful when there are strict guidelines which restrict the data entry at the source system to be clean.
Another approach to Real-Time BI is to query data directly from the transactional system databases. However, most of the transactional systems use fully normalized relational schema (e.g. Entity Relationship schema) in their database which are efficient in reducing data redundancy and optimizing data updates but not suitable for analytic purposes to query data for historical analysis. This presents the need for data extraction to dimensional schemas in the warehouse for analysis of data from transactional system database. To report some of the metrics on real-time basis like new customers, suppliers or orders, the BI metadata layer could source the required selective data directly from the OLTP database by-passing the ETL process . This entails very specific queries to return selective data without any historical analysis. These metrics could then be embedded as a separate set of measures along with the regular BI application leading to a near real-time BI reporting.
This blog has inputs from Sumit Sharma who is an Associate Consultant with the Oracle Business Intelligence Practice at Infosys. His areas of interest include Packaged BI Apps and Financial Analytics.