Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Posts Tagged ‘OBIA’

Conformed Dimension and Data Mining

Posted by Dylan Wan on April 20, 2015

I believe that Conformed Dimensions are playing a key roles in data mining.  Here is why: Read the rest of this entry »

Advertisements

Posted in Business Intelligence | Tagged: , , , , | Leave a Comment »

Incremental ETL in BI Apps 11.1.1.7.1 (1)

Posted by Dylan Wan on May 10, 2013

The data warehouse needs to be refreshed periodically to make sure that the data warehouse have the up to date information.  The Incremental Load is the regular ETL process running everyday.  The frequency can vary, but it is commonly seen as nightly job.  To ensure that the incremental ETL job can be finished within a limited time window, incremental ETL load typically identifies the changes happened in the source system and only deal with the delta to avoiding doing a Full ETL Load.

In BI Apps 11.1.1.7.1, if you are not leveraging the Golden Gate and the SDS (Source Dependent Data Store), the incremental ETL is accomplished by directly comparing the last update date captured in the source table with the last refreshed date.

BI Apps 11.1.1.7.1 ODI based ETL does not use separate ETL tasks for incremental load and full load.  The logic is determined by whether the data has been executed before.  In other word, it is based on whether the data has been refreshed.  This was a productivity gain for BI App developers and also make the support of incremental ETL becomes clear.  In the past, you may see the Incremental Load Informatica Workflow and Full Load.  Every time when there is a logic change, the workflows need to be modified.  It was a source of code bugs before.  By using a better ETL architecture, we eliminate the needs of maintaining two separate tasks.

Here is a high level summary of how the incremental ETL is accomplished in BI Apps 11.1.1.7.1 ETL:

1. Use the W_ETL_LOAD_DATES table for tracking all the ETL execution

2. Use the IS_INCREMENTAL Variable for holding and passing the state

3. Refresh Variable in a Package Step.  If there is an entry in the W_ETL_LOAD_DATES, perform an incremental ETL.

4. Use the RUN_INCREMENTAL to turn on and off the incremental filter

5. Optionally, use the ODI Variable Evaluate Step to branch the Full and Incremental ETL logic within a package

6. Table Maintenance, take the ETL Load Date information to determine the operations, including Drop Index, Truncate Table before Full Load, Rebuild the index after Load, and Analyze Table after Load, tec.

 

 

 

 

 

 

 

Posted in BI, BI Application, ETL, ODI, Oracle, Oracle Data Integrator | Tagged: , , , | Leave a Comment »

Benefits of using ODI as the ETL for BI Apps

Posted by Dylan Wan on May 2, 2013

Oracle Data Integrator is being used as the ETL tool for BI Apps. 

Here are some benefits:

1. Productivity

It tasks less efforts for developers to write a ETL map comparing  to other ETL tool.

2. Define ETL flow patterns in KM

The reason why now we can take less effort is that the flow is now inside the knowledge module.  You pick which flow logic you want to use by picking which KM to use.  You configure the flow for your specific requirements by specifying the KM options. The rest is to define the source SQL and the mapping expression.  It pretty much that it is for any typical job.

In a prebuilt ETL adaptor solution, like what BI Apps has, we have a set of patterns.  Unless the specific task requirement is unique, you do not need to write any new KM and any complex logic.

3. Code Generation

ODI provides SDK.  Although the ETL programs themselves are declarative in the ODI repository, the ETL program can be generated by using the ODI SDK.  Not just the ETL flow logic is centralized inside KM, the ETL meta can also be generated.

4. User Defined Function

User Defined Function is one of the magic behind supporting different platforms.  You use the same function and do not need to worry about the different SQL function supported in different database platforms, ODI engine will compile and replace it with different implementation for you.

It is not a required feature for building a custom ETL.  It is a required feature for prebuilt ETL adaptors which need to support source apps, such as Siebel Apps, JDE Apps, PeopleSoft Apps in different database platforms.

5. Data Lineage

The data lineage within ETL tool becomes so easy.  You can easily identify which ETL task populates a given table column, and for a given source table column which ETL tasks are using it as the source.

Actually although it may not be recommended the ODI metadata repository can be queries directly from database. It makes ETL auditing becomes possible.  It is a productivity grain since you can spend less quality assurance time to detect issues and it is a quality gain as you get the ETL adaptor with high quality.

6. Performance

When the source data and target data are within the same database, the middle tier ETL engine becomes an overhead.  We can better leverage the power from the database for ETL transformation.

This is the benefit we expect from a E-L-T solution.

For cross database deliveries, many optimization options can be used.  A simple database link over different database instances can be used.  Also using Oracle Golden Gate to replicate the data is also possible.  With the above benefits of KM and UDF, the same ETL codes can be switched to run the extract process against the replicated data or run against the remote source apps database.

7. Load Plan

The ETL task dependencies and trimming can be accomplished via ODI Load Plan within the same ODI repository.  This architecture design eliminates the needs of duplicating the ETL task metadata to a separate task execution sequencing and management tool, like BI Apps Data Warehouse Administration Console.

You can define your ETL job, execute your ETL job, monitor your ETL jobs within a single tool, the same ODI studio.

(to be continued…)

Posted in Business Intelligence | Tagged: , , , , , , , , | Leave a Comment »

Why use GoldenGate in ETL?

Posted by Dylan Wan on February 15, 2013

The traditional way of doing ETL nightly is no longer satisfying the requirements.  In the past, we have the ETL “window”  to perform ETL.  The ETL processes typically run as daily or nightly ETL.  We can use the downtime to execute some processes in the OLTP systems to extract their data while we can still avoid the impacts to the transaction processing system.

Nowadays, there are many cases of no downtime and no ETL window.

GoldenGate provides the unique benefit of constantly feeding the data from the OLTP system.  The data are replicated spreading over the day without introducing much impact to the source system.

 

 

 

Posted in Business Intelligence | Tagged: , , | Leave a Comment »

Oracle BI Applications 7.9.5 is out

Posted by Dylan Wan on May 30, 2008

Oracle BI Applications 7.9.5 is released early this month. Here is a quick summary of the features introduced in this release and where you can get more information about it.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, ETL, Infomatica, OBIA, OBIEE, Oracle, Oracle BI Suite EE, PeopleSoft, Siebel Analytics | Tagged: | 3 Comments »