Abstract
The regular execution plan for BI Apps includes one or more subject area (facts, dimension and other tables), e.g. Finance, HR and Spend and Procurement etc. More the subject area is added to an execution plan, the longer the ETL process runs.
The regular execution plan is set to run overnight to avoid its impact on source system during working day, which makes perfect sense. Some specific users may find reports are stale if data is only refreshed once nightly, for example financial reports, invoices on hold as of now etc.
I have seen many customer would like to refresh data as and when require in some areas e.g. finance over the month end to make sure that the current status is being reflected in month end board reporting etc.
Micro ETL execution plan can refresh BI Apps results of small subject area or subset for hourly or half hourly basis or on-demand to cope up with one off requirement such as month end etc.
This blog aim is to discuss the problem and solution for intraday data updates with Micro Execution Plan, the features and limitations of the proposed solution is also discussed in subsequent sections.
Problem Description
The problem description is explained below in terms of what are the challenges and business requirement.
Challenges
- Users can not have access to reports with latest changed data sets
- Users are forced to wait for a day to finish over night incremental data load
- Users are forced to source data directly from E-business Suit or any source system for critical reports which often end in performance issues on application
Requirement
- User wanted a data load system which should facilitate on demand incremental data load for chosen subject area
- User want to avoid to report directly against source data to avoid performance issues on application
- The system needs to report the data in near real time for business critical reports
- The user also want to have a system which performs faster by using same BI APPS reporting metadata and also should have no impact on source system
- The system needs to update BI Apps on demand for selected subject are particularly over a month end /quarter or year end processes.
Solution
Micro ETL execution plan is created to accommodate a business need for specific users to access updated subsets of data at frequent intervals. A micro ETL execution plan can refresh the results of a small subject area or a subset of star schema hourly or half hourly.
Idea is to create a micro version of the ETL with fact and dimension required for a set of critical reports/dashboard. The micro ETL would extract the changed data over a specified period. The subset of data extracted for a micro ETL execution plan will be updated and patched during a regularly scheduled nightly ETL process.
As BI Apps tasks have update strategies that can update the data even though it has already been loaded into the data warehouse. It is very essential to make sure that any custom mappings participating in the Micro ETL execution should have update strategies in place.
Detail of Solution
Procedure to design a Micro ETL Execution Plan is follows
Step 1: Choose source system container from drop down list in the Design View
Step 2: Subject Are Tab > Click New > Enter Name e.g. Finance Micro ETL & click Save.
Step 3: In the Tables sub-tab, click Add/Remove > Choose Tables dialog is displayed > Query for one or more tables > Select the fact table one or more > click Add > Click OK to close the Choose Table dialog
Step 4: Click Assemble in the Subject Areas tab toolbar > In the Assembling dialog, select Selected record only > The tree view on the left side of the Subject Area Assembly dialog displays the fact tables that belong to the subject area. You can expand the fact table node to view its related tables > Deselect unnecessary fact and dimension tables
Step 5: Click Calculate Task List to assemble the tasks needed to load the tables displayed in the tree view > Accept to complete the subject area assembly process.
Step 6: Inactive any unnecessary tasks > Reassemble the subject are by clicking on Assemble.
Solution Considerations
- The Micro ETL can cause issues with data inconsistencies, data availability and additional load on the transactional database
- Reports spans the cross star may be inaccurate, when one of the star (fact) is refreshed more often than other.
- Dimension table omission from micro ETL may result into reporting as unspecified record
- Omission of aggregate table in micro ETL execution plans may result in inconsistent with the reports that use data from the detail fact tables while adding aggregate tables in micro ETL execution plan may be inefficient to perform that quite often
- Soft deletes and all downstream post load processes such as GL Reconciliation are must to include into micro ETL execution plan to avoid mismatches between transactions and balances e.g. Financial BI Apps
- Inclusion of Hierarchy rebuilt in Micro ETL execution plan may result in inefficient data load , but exclusion would result into data inconsistencies
Products Involved
DAC 10/11g
Business Benefits
Business Performance
- Business users are better equipped with standard or on demand refresh mechanism for critical reporting phase
- The users are able to avoid E-Business data extraction and processed via home grown excel factory to generate reporting packages for board reports
Cost
- Single system for reporting avoids the multiple silos of systems and their maintenance
- Near to real-time reporting will enable faster decision-making/ reduce losses
Operation
- Near real-time reporting helps them to respond and manage crisis situations more efficiently
My Inference
The well informed business about the frequency of micro ETL execution plans, its impact on the dashboard results and critical reports throughout the day is the key of success. I would suggest weighing benefits against the limitations within which solution works and deciding.
