Dylan’s BI Study Notes

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

Cloud to Cloud ETL

Posted by Dylan Wan on January 16, 2015

I found that Cloud to Cloud ETL is different from the regular ETL running in the on premise env in several ways:

1. Direct Database Access is not available

2. Complex / Nested Objects

3. Paging

4. Volume Restriction

5. File base approach

6. Data Streaming

7. Data Security / Protection

8. Change data capture

9. Translation / System Data Availability

10. Metadata Reverse Engineering

11. API / Interface versioning

12. Frequency of the update / patching

13. Provisioning

Posted in Business Intelligence | Leave a Comment »

BI Applications in Cloud

Posted by Dylan Wan on October 6, 2014

Prepackaged analytics applications are available as cloud services.

The idea is that the client company does not need to use their own hardware and does not need to install the software or apply patches by themselves.

What they need is just simply the browsers.

For the end users, there should not be much difference.   The BI apps built on the OBIEE platform is already a web based application.  Users today, even when they use the BI applications deployed on premise, access the applications via the browser.

The real difference is to the IT department and to the company from the investment perspectives.

The deployment time should be very short.

Where is the role of the ISV, VAR, or SI in this picture?

I think that their role is still very important.

First of all, the out of box reports may not work for everyone.  New reports may need to be created.

Secondly, the source apps may be extended.  For example, oracle cloud services, such as CX clouds, support extensible attributes.  Where those attribute need to be reported should be probably added or hidden.

When the data are not loaded from other Oracle cloud applications, loading the data from Oracle’s  Application Unlimited products such as PeopleSoft, E-Business Suite, Siebel, J.D. Edwards may require a lot of work.

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

Supply Chain Questions

Posted by Dylan Wan on September 28, 2014

The key question are about inventory and orders.

  • What is our inventory status? by product and by location?
    • Do we have enough inventory on hand and in process to meet customer demand?
    • How can we maximize customer satisfaction while minimizing cost and size of inventory on hand? Both over stocking and under stocking are bad
    • Do we have enough finished goods to our backlog orders?  How much raw materials, work in process, and finished goods on hand?
  • How well is our order fulfillment process performing?
    • What are the top orders in backlog?  How can we prioritize strategic customers?
    • How can we ensure fulfillment on time?
    • Which products have the longest fulfillment cycles?
  • Where are the bottlenecks in order management process?
    • What is our order management cycle time? How long does it take for booking an order?
  • Do we know why customers cancel orders and return products?
    • How much revenue was lost due to order cancellations or returns

Typical dimensions: By Product, By Location, By Customer

Different roles: Director of Sales Operations, Inventory Managers, Order Management Managers

Integration with other systems:

  • Customers and pipeline information from a CRM application
  • Product information from ERP
  • Workforce information from Human Resource
    • Align the training plans to skill gaps in critical product area
      • Correlate staffing levels with order volume and product plan

Posted in BI Application | Tagged: | Leave a Comment »

Analytics 3.0

Posted by Dylan Wan on January 2, 2014

One of the books I like most about data warehousing is the book e-Data, written by Jill Dyche.  Here is a paper she co-authorized with Thomas H. Davenport about Big Data:

Big Data in Big Companies: Executive Summary

Thomas H. Davenport is the author of the book Competing on Analytics, which is also a book I own and found interesting.

I cannot agree more about these two points they mentioned in the above executive summary:

  • Overall, we found the expected co-existence; in not a single one of these large organizations was big data being managed separately from other types of data and analytics. The integration was in fact leading to a new management perspective on analytics, which we’ll call “Analytics 3.0.”
  • “It’s important to remember that the primary value from big data comes not from the data in its raw form, but from the processing and analysis of it and the insights, products, and services that emerge from analysis.”

I feel that what customers may really need is an integrated prepackaged BI apps that is built on an integrated BI platform and the associated tech stacks that can handle big data together with the other existing enterprise data.  Building an non-integrated big data data warehouse is time consuming and may not get a good balance of the cost and the benefit.  The non integrated big data data warehouse may work well for the specialized firms for special purposes.  However, when we think of leveraging the big data technology in the enterprise, integration with the existing data warehouse is the key for success.


Posted in Business Intelligence | 1 Comment »

Data Warehouse for Big Data: Scale-Up vs. Scale-Out

Posted by Dylan Wan on January 1, 2014

Found a very good paper: http://research.microsoft.com/pubs/204499/a20-appuswamy.pdf

This paper discuss if it is a right approach of using Hadoop as the analytics infrastructure.

It is hard to argue with the industry trend.  However, Hadoop is not new any more.  It is time for people to calm down and rethink about the real benefits.


Posted in Business Intelligence | Leave a Comment »

Technologies behind Oracle Transactional Business Intelligence (OTBI)

Posted by Dylan Wan on July 25, 2013

Oracle Transactional Business Intelligence (OTBI) is one of the business intelligence solutions provided as part of Fusion Applications.

To build a real-time BI, the major challenge is to make sure that it can perform and has no or minimum interfere to the core objective of the transactional application, the online processing.

This is the reason why we need Oracle Business Intelligence Applications (OBIA) for Fusion Applications.  The idea is to keep the minimal processing of detecting changes and capturing changes in the transactional system and leave everything else, such as, preparing and consolidating the data for reporting, to BI Applications.

Here are some of the technologies available to make OTBI possible:

1. SQL Trimming from ADF

ADF stands for Application Development Framework.  It is the application development framework used in developing Fusion Applications.  In general, it is a declarative metadata driven framework to let the application developers to define the data model, define the data access layer, define the UI rendering, put the validation logic and processing in the middle tier.

The underlying data model, in most of cases, is still the relational model defined in the Fusion Apps transactional database under the 3rd NF design.

The key enabling technologies provided from ADF to OTBI is the “Composite VO” or “Composite View Object”.  For me, it can generate the database SQL for us based on the metadata.  Unlike the database technology using the database view, ADF engine can look further down to the entity objects included in the view object and selectively choose which entities are needed in a given SQL.  If the view object includes two tables (EOs), one primary EO for data at the line level, and the other EO for getting the parent data, When the query (Composite VO) does not include any column from the parent EO, the SQL generated by ADF will not include the table in the join.

This is a superior technologies, comparing to the old technologies of building the business views.

If you are a Java programmer and would like to get the feeling about what Composite View Object looks like and how it works, here is a good blog post:

Do you know what is a Composite View Object?

2. BI Platform – ADFQuery to Composite VO

This enabling technology is provided by BI platform and available as a Java library. It adds a layer on top of the ADF composite VO.  Without writing the Java code, it generates the codes of creating the composite VO on the fly.  It allows us to query the data from the ADF engine by sending them a XML block called ADFQuery.

This doc shows some of the ADFQuery XML blocks.


To see better examples, you can find them in NQQuery.log files.

It is a query language like SQL.  You have the section  for the column projection, the join criteria using view links, and the filter using view criteria.

Here are other enabling technologies behind OTBI.

3. ADFQuery generation from BI Server

4. SQL By Pass Database

5. Relational to Dimensional Mapping (Physical Layer to Business Model Layer)

6. SELECT Physical in initialization block

7. ADFQuery Initialization block

8. Physical Lookup function from BI platform

9. Logical Lookup function from BI platform

10. Data Security enabled at the VO layer via Fusion AppCore

11. Applcore Tree Flattening

12. Applcore Business Intelligence Column Flatten VO (BICVO)

13. BI Flexfield VO generator

14. BI Extender via Import Wizard

15. BI View Object created based on the BI EE Logical SQL (BIJDBC)

16. Effective Date VO with as of date filter

17. ADF Application Module to BI variable interface

and more…

Regardless, the goal of these technologies is to enable the users to get the real time data access to the Fusion Apps.  There is really little or no much we can do for providing the feature like data snapshot, pre-built aggregate, multiple currencies, data consolidation and conformance, cross subject area analysis, and the most important, the query performance with complexity logic to be available in a reasonable time without the interfere to the transactional system.

Posted in BI Application, Business Intelligence, OTBI | Leave a Comment »

Multiple Language Support in Oracle BI Applications (1)

Posted by Dylan Wan on June 27, 2013

In release, one of the major feature is to supported the translated data so you can have a global deployment of data warehouse.

In the past, before release, the BI platform and BI applications support the translated displayed prompts and labels, so the users that use different language can use the BI Apps and see the instruction and navigation and displayed labels in their own language.  However, when they are seeing the data stored in the data warehouse, they are seeing the data in one language only.  Even some of OLTP applications such as E-Business Suite, PeopleSoft, and Siebel Applications are all supported multiple languages in their database, BI Apps only extracted the data from one language that is specified in DAC as a global variable.

In release, BI Apps data warehouse model design officially deliver the true multiple language data warehouse.  The feature can enable the deploying organizations which have the users from different countries to share a global deployment,  They can enable multiple data warehouse languages as part of the Bi Apps configuration, and the ETL process can extract the language specific reference data from the OLTP applications.

When the different users from different countries login to the same application with the same role, even they have the same data access, they can see the data displayed in their own language.



Posted in Business Intelligence | Leave a Comment »

Using Load Plan for managing your ETL task in BI Apps (1)

Posted by Dylan Wan on May 14, 2013

One of the major change introduced in BI Apps is the way how we manage the ETL task sequence and trim the unnecessary tasks.

This functionality was accomplished earlier using DAC.  The problem we frequently faced was that the DAC repository and the INFA repository are maintained as two separate repositories.  We have to sync up the name of tasks exactly in order to use DAC to manage the task execution of the Informatica workflow tasks.

Load Plan and Load Plan Generator was designed for addressing this requirement.

Here is a good article that describes the story.

Load Plan Generator – An Inside Look

Posted in BI Application, Business Intelligence, DAC, Data Warehouse, ETL, Infomatica, OBIA, ODI, Oracle, Oracle Data Integrator | Tagged: , , , | Leave a Comment »

Incremental ETL in BI Apps (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, 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 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 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 »

BI Apps (BI Apps in ODI) is available in OTN

Posted by Dylan Wan on May 6, 2013

First Go to OTN


Go to DOWNLOADS -> Middleware

You will see Business Intelligence Applications under Middleware

You see both 10g and 11g.  Click on Oracle Business Intelligence 11g downloads

You will see BI Apps here:

Oracle Business Intelligence Applications (


Posted in Business Intelligence | 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 »

BI Application development team is hiring

Posted by Dylan Wan on February 26, 2013

BI Application development team is hiring

They are hiring a senior application engineer.  The job requires experienced ETL and BI developers with 3- 7 year experience.


Posted in Business Intelligence | 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 »

Extending your BI using Fusion Application Composer

Posted by Dylan Wan on December 12, 2012

This is a very cool feature I have not seen for years.

We have heard the demands of customizing the reports from customers for years.  The deploying company almost always wants to create some custom reports.  One of the selling point of the Oracle BI platform and Oracle BI Apps has been how easy it is to extend the BI and how these extensions can be preserved during upgrade.

This Fusion Apps Application Composer is pushing this further.

What it is trying to do is to allow bring the tasks used to be done by “BI architect” to the hand of the BI user or departmental level of admin.  Also, it brings the extension to the world of the SaaS based deployment.

The center of the BI platform is to have the metadata defined to model the data and make it possible to generate the SQL behind the scene to generate the queries.  In the past, we had to use the BI Admin Tool to modify the metadata.  The BI metadata maintenance requires a specialized tool, the BI admin tool The Fusion Apps Composer now supports a specific pattern of extension – creating the custom subject area, to use a web based tool, without opening the BI Admin.

The tool actually extends the BI Apps OOTB repository file (rpd) in real time.

You can see a list of Fusion Apps business objects defined in ADF VOs and you build the custom subject area by choosing which objects to be the primary object for your fact, and which related objects as the sources for your dimensions.  After completing a guided web based “Wizard”, it creates the physical layer objects, logical objects, and presentation layer objects automatically!  Within an hour, you are creating a new subject area.

It won’t be able to replace all the advanced features available from the window based admin client tool.  However, it can satisfy a set of SaaS customer’s reporting requirements.

In my view, it is still not well documented – Customizing Objects and Customizing Analytics.  It seems a hidden powerful feature from BI content extension perspectives.

This feature is currently only available for Fusion CRM only.  Not sure if there is any plan to make it available from other pillars.  It is worth to watch how it evolves.

Posted in BI, BI Application, Business Intelligence, OBIEE, Oracle, Oracle BI Suite EE, OTBI | Tagged: , , , | 3 Comments »

ODI Shortcut

Posted by Dylan Wan on May 25, 2012

ODI provides the shortcut feature since the release.  Shortcuts look like symbolic links in Linux file system and shortcuts in windows. You keep a reference to an object that exists in another folder.

The symbolic link feature has been used widely in prepackaged apps development to handle multiple releases of the software product internally. Typically multiple top folders are created, one for each release.

Many codes can be shared from an old release to its next release.  Instead of copying the codes, a symbolic link can be created in the folder created for the new release to point to the code in the folder created for the old release.

ODI shortcut feature was created for a similar purpose.

ODI allow you to create shortcuts for these objects under the ODI Project:

  • Interface
  • Package
  • Procedure

Shortcuts are for the codes that the developers created and modified.  It try to maximize the code reuse.  You create a code in a folder, and reference it in another folder, after you change the code in the original folder, the reference in the other folder also get the change.  You do not need to make the same change over again for each release.

I also notice that the scenarios are not shortcutable.  The scenarios (package scenario and interface scenario) are executable objects.

This dsign means that the scenarios should be regenerated after the shortcuts are created.  It also means that the scenario should be regenerated after the code in the base folder is modified.  It is kind of strange, but it is a design to help you to maximize the code reuse.  Each interface or a package is tie to an ODI model and model could be source apps version specific.  You want your code to run against a different model even the code is a shortcut.

Folders are also not shortcutable. ODI Folders are not regular folders like the way how we think in the file system directory.  Each folder carries a property called “Release Tag”.  The release tag can help redirecting the model to be used in generating the scenarios.


Posted in Data Warehouse, ODI, Oracle, Oracle Data Integrator, OWB, Sunopsis | Tagged: | Leave a Comment »

Use ODI SDK in Groovy

Posted by Dylan Wan on May 12, 2012

ODI includes a built-in Groovy editor in the ODI studio.

If you run the groovy script within ODI studio, it automatically run against the ODI repository you are connecting to.

You can do a lot of the tasks that you do in ODI studio in ODI SDK.

I tried to use ODI SDK to create a project, create a folder, create a subfolder, create a short to an interface, create a variable, and update a variable.

All of these scripts are directly created in the Groovy Editor in the ODI studio.  I know Java but I have never used Groovy.  The learning curve is almost none.  If you know basic Java programming, using Groovy to update ODI repository should be easy for you.

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

About Big Data (2): SQL and No SQL

Posted by Dylan Wan on April 14, 2012

This is a good article written by Alan Gates, Pig architect at Yahoo!

Comparing Pig Latin and SQL for Constructing Data Processing Pipelines

He compares Pig Latin, a query language for Hive with SQL, the query language for relational database. He gave a good example that helps those who know SQL to understand the differences.

The query language is not difficult to write.  The key point is that it lets the programmer to control the execution plan.  SQL on the other is a higher level abstraction that hides the execution plan from users.  It does not means that we do not really need to be worried about execution plan.  We do.  It is a key for performance tuning.

A good practice for SQL developers is to get the explain plan and to see how Oracle plans to execute the query and optionally use hint and index to control the execution.

Comparing to this, the approach of directly telling the system what to do may seem easier.

But it means that the database system to handle big data does less things, not smarter.

Posted in Big Data, Data Warehouse | Tagged: , , | 1 Comment »

Tutorial: Create ODI Repositories

Posted by Dylan Wan on April 14, 2012

This step is not required if you are working on an existing repository.  If you are doing a new development project, or just want to get some experiences on using ODI, you may need to create ODI repositories yourselves.

A typical ODI deployment includes two repositories: Master Repository and Work Repository.

ODI provides two different tools to create them.

Here is a good tutorial page available from oracle.com:


The tool does not create database users.  Here is my script for creating the database users:

create tablespace ODI_MREP
size 100M autoextend on Next 100M Maxsize unlimited;

create tablespace ODI_WREP
size 200M autoextend on Next 100M Maxsize unlimited;create user ODI_MREP
identified by ODI_MREP
default tablespace ODI_MREP
temporary tablespace TEMP
quota unlimited on ODI_MREP;

create user ODI_WREP
identified by ODI_WREP
default tablespace ODI_WREP
temporary tablespace TEMP
quota unlimited on ODI_WREP;

grant connect, resource, SELECT_CATALOG_ROLE  to ODI_MREP, ODI_WREP;

If you are using RCU, you will not be able to create Work Repository and Master Repository separately.  In my opinion, RCU is a poor tool and lacks many basic features. I will stay away from it.

Posted in Business Intelligence, ODI, Oracle Data Integrator | Leave a Comment »

ODI Topology

Posted by Dylan Wan on April 13, 2012

ODI has a very clever design to isolate the deployment objects from design objects.

The designer only interface to deployment information (topology) via logical schema.  The project objects only indirectly get the dependency via model.  The “context” can determine the physical resources and allow you switch among the development, testing and production env.

From prebuilt package apps perspective, the codes are shipped under a set of logical schema.    The logical schema represents the assumption about the various source and target systems.

The concept of “context” really enables the support of unlimited multiple source instances using a single set of codes.

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


Get every new post delivered to your Inbox.

Join 124 other followers