BI Applications and Embedded BI, Part 3
Posted by Dylan Wan on January 28, 2008
I would like to go over some of the embedded BI related technologies you can use to make the data from BI Apps embedded in the OLTP systems.
In this post I will describe the Micro ETL feature from DAC.
Typically the ETL jobs are executed as a nightly job. The last refresh date of the previous is used to determine the data to be loaded during each incremental load. However, the Data Warehouse Console also allows you to run the ETL process at a very frequent intervals.
This is accomplished by using the “Keep Separate Refresh Date” checkbox for an execution plan. If the checkbox is checked, a separate refresh date will be maintained at the execution plan level. We can call this as a mico ETL execution plan. A fact table can be populated by both regular nightly job and the hourly micro ETL execution plan. The technique can be used together with the late arriving dimension logic in the ETL. The typical assumption is the dimension is not updated as frequently as is the fact table. The refresh of dimension hierarchy may be costly to run too frequently. If a new dimension member is added, since the dimension reload may not be included in the micro ETL execution plan, the fact data will still be loaded with the dimension FK as ‘Unspecified’. Later, during the nightly job, the FK in the fact table can be updated with the right dimension member after the new dimension member is loaded into the warehouse.
Micro ETL cannot replace the true real time or near real time ETL. However, it may be an option to solve part of the problem that you may face in the embedded BI, the data in warehouse is out dated so the OLTP and the embedded dashboard are out of sync.