Dylan's BI Study Notes

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

Archive for the ‘Oracle’ Category

Schema On Read?

Posted by Dylan Wan on September 24, 2017

I saw “create external table ” first in Oracle DBMS 11G.

It was created for the purpose of loading data.

When Hive was introduced, a lot of data were already created in HDFS.

Hive was introduced to provided the SQL interface on these data.

Using the external table concept is a nature of the design.  It is not really a creative thing.

Read the rest of this entry »

Advertisements

Posted in Big Data, Data Lake, Data Science, Oracle | Tagged: , | Leave a Comment »

How to access Fusion Cloud for building custom BI?

Posted by Dylan Wan on December 20, 2016

It was a challenge when we tried to build a BI application for Fusion Cloud application as Fusion Cloud applications, unlike those acquired solutions, such as RightNow, Elouque, and Taleo, do not have web services at that time.

It was the reason why Oracle BI Application Configuration Manager was introduced.  It fills the gap by building an agent at the Fusion  cloud application side.  The agent talks to Fusion Apps like how OTBI talks with Fusion Apps.  It queries the physical layer of the objects from Fusion Apps and download the data into csv files.  It is not a web service based solution but for building a custom BI that requires bulk access any way, it is a goo choice.

Once the data is downloaded from Fusion Apps as CSV files, it can be accessible via FTP.  Here is the documentation about configuring Oracle BI Apps but I guess that it will work for other FTP client as well.

It seems that the situation improved and now multiple alternates are available.

Another possible way is to use BI Publisher.  If you have the BI Publisher Data Model Developer role, you will be able to “data model” in publisher.  Here is the documentation.

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

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

Posted by Dylan Wan on May 14, 2013

One of the major change introduced in BI Apps 11.1.1.7.1 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 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 »

ODI Shortcut

Posted by Dylan Wan on May 25, 2012

ODI provides the shortcut feature since the 11.1.1.6.0 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 »

OTBI vs. OBIA

Posted by Dylan Wan on October 5, 2011

Several people are curious about what are OTBI and OBIA, and what are the differences between OTBI and OBIA.  I will discuss these in this article.

OTBI stands for Oracle Transactional Buisness Intelligence. 

OBIA stands for Oracle Business Intelligence Applications.

Let’s start with OBIA.  OBIA is the pre-packaged BI Apps that Oracle has provided for several years.  It is the data warehouse based solution.  It is based on the universal data warehouse design with different prebuilt adapters that can connect to various source application to bring the data into the data warehouse.  It allows you to conslidate the data from various sources and bring them together.  It provides a library of metrics that help you measure your business. It also provides a set of predefined reports and dashboards.  OBIA works for multiple sources, including E-Business Suite, PeopleSoft, JDE, SAP, and Fusion Applications.

OTBI is different.  First of all, it is a real time BI.  There is no data warehouse or ETL process for OTBI.  Second, it is for Fusion Apps only.   OTBI is leveraging the advanced technologies from both BI platform and ADF to enable the online BI queries agains the Fusion Applications database directly.  In addition, in some area, such as Financial, you can also connect to the Essbase cubes.  Unlike OBIA, OTBI does not have a lot of prebuilt dashboards and reports.  The reason is that for some advanced analysis, the data need to be prepared.  You cannot get eveything you can get from the OBIA data warehouse in OTBI. 

Both OTBI and OBIA are available from the same metadata repository.  Some of the repository objects are shared between OTBI and OBIA.  It was designed to allow you have the following configurations:

  • OTBI Only
  • OBIA only
  • OTBI and OBIA coexist

If you implement Fusion Apps, you can enable OTBI.  You can use the BI EE Answer to access the prebuild metadata and metrics those are built against the Fusion Apps.  You may not get the full powerful prebuild dashboard and repost and prebuilt navigation workflow.  However, you can start experiencing what the BI EE based reports look like.  You can start bring the data out from your OLTP system.  You can provide training to the users to get familar with the subject areas, some of which are shared with OBIA. 

If you enjoy OTBI and want to further get OBIA with a data warehouse based solution.  You can implement OBIA later.  Some of the OTBI reports maybe switched to run against OBIA.  Some of OTBI reports can continue connecting to Fusion Apps directly.  They can coexist in a single BI server and a single BI answer client.

Both OTBI and OBIA are accessing Fusion Apps via the ADF.   This is a more advanced topic. 

 

 

 

Posted in BI, BI Application, BI Links, Business Intelligence, Data Warehouse, DBI, essbase, ETL, Infomatica, OBIA, OBIEE, Oracle, Oracle BI Suite EE, OTBI | 1 Comment »

User Defined Function (UDF) in ODI

Posted by Dylan Wan on March 21, 2011

User Defined Function (UDF) is a very powerful feature from ODI.

One of features that are absent from other ETL tool is to address the need to support different database platforms.  I won’t blame those ETL tools since they are not really designed for pre-package BI Apps.

Who will need to switch database platform like that?

If your data warehouse is deployed on Oracle, you can use Oracle SQL.  If you are using Teredata, you can use Teradata.  You know that your PeopleSoft is running on DB2, you can write the DB2 SQL.  In the custom data warehouse ETL environment, switching database platforms is uncommon, one time only task.  You do not need to switch among different database platforms within your code.

A prepackaged BI apps ETL developers, however, are facing different challenges.  You do not know if the source apps is running on which database platform.  Also, you want to give customers the choices on the database platforms to deploy the data warehouse.

ODI UDF comes very handy.  You can create a UDF to use in your SQL, you can have multiple implementation of the UDF for different database platform.  You can use GetDate() for MS SQL and use SYSDATE for Oracle database in the implementation, but you can create you own function such TODAY() and use in your SQL.

User Defined Function is not a new idea.  You may see something similar in other tools.  However, to be able to use UDF in SQL and to be able to use UDF with multiple implementations under different technologies, I only see the feature in ODI.

I won’t be surprised to see those “me too” products in the near feature.

More and more companies are moving to use prepackaged BI apps.

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

Data Security in Oracle BI Apps

Posted by Dylan Wan on July 15, 2008

I am describing how data security works in Oracle BI Apps.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, OBIA, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | Tagged: | 1 Comment »

Use ANSI SQL for Outer Join

Posted by Dylan Wan on July 4, 2008

The OLTP source applications like PeopleSoft and Siebel applications can run on many different databases including Oracle, MS SQL, or DB2. The target data warehouse can also run on different database platforms, incluidng the above databases, plus Teradata.

Various technologies can be used to enable the cross database platform support in the pre-packaged BI apps. Oracle BI Enterprise Edition allows you configure the database connections using the native drivers. ODBC can also be used to access different databases.

In order to deliver to deliver the pre-packaged ETL adapters, two technologies can be used : ANSI SQL and ETL tool specific SQL. It is preferable to avoid the dependency on the ETL plarform and use ANSI SQL92 syntax. When we move from ETL to ELT, Read the rest of this entry »

Posted in BI, BI Application, BI Work, ETL, Infomatica, Oracle, Oracle Data Integrator | Leave a Comment »

Use SQL*Loader with Named Pipe

Posted by Dylan Wan on June 24, 2008

Typically using SQL*Loader assumes that a flat file will be used as the input. The file will need to be created and generated before the SQL*Loader can take the data from the file and load the data into Oracle. The performance can be improved and the disk space can be saved if you use named pipe with SQL*Loader.

Read the rest of this entry »

Posted in BI, BI Work, Business Intelligence, Data Warehouse, ETL, Infomatica, Oracle, Oracle Data Integrator, Sunopsis | Tagged: , , | 5 Comments »

Hyperion Essbase Dimension Terminology

Posted by Dylan Wan on June 20, 2008

I found the the language used in Hyperion Essbase documentation is very useful for describing the dimension hierarchy. To communicate effectively, sometime we need precise teams to describe things. The terms defined in the Hyperion Essbase documentation helps.

Read the rest of this entry »

Posted in BI, Business Intelligence, essbase, hyperion, Oracle, PeopleSoft | Tagged: , , , | 3 Comments »

Changed Data Capture in ODI

Posted by Dylan Wan on June 9, 2008

ODI is the tool will also be used to develop the ETL (or ELT) for Oracle BI Applications. ODI supports Changed Data Capture by its journalizing module.

My understanding of CDC flow in ODI is as follows: Read the rest of this entry »

Posted in BI, BI Application, BI Work, Oracle, Oracle Data Integrator, Sunopsis | 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 »

ABC Analysis in Inventory Management

Posted by Dylan Wan on March 11, 2008

 I did some study on the ABC Analysis in Inventory Management.  It is also useful in business analytics.  I will cover what it is and how it is supported in various ERPs. Finally, how it may be used in analytics application. Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, EBS, Oracle, PeopleSoft, Supply Chain Intelligence | 10 Comments »

A good OBIEE web demo from New York City’s City Wide Performance Reporting

Posted by Dylan Wan on February 15, 2008

New York City now enables the public to monitor the city’s performance by making the OBIEE based dashboard and report online.

Here is the link to their CPR (City Wide Performance Reporting) site : http://www.nyc.gov/html/ops/cpr/html/home/home.shtml

Read the rest of this entry »

Posted in BI, BI Links, Business Intelligence, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | 2 Comments »

Bitmap Index – when to use it?

Posted by Dylan Wan on February 1, 2008

I will cover how Bitmap index work, when to use it and how to use it in this article.

How does it work?

The bitmap index stores the column values in bits. Each bit represents a single value. For example, the gender column has two possible values: Male and Female. three bit will be used in the bitmap to capture the index on the gender column. A good example can be seen in reference 1. So the more distinct value is, the more space is required to store the bitmap.

Internally, the database engine, like Oracle, uses a map function to converts the bit location to the distinct value. (See reference #2) Many bitmap indexes can be used together since database can merge it, so this can improve the response time. (See Reference #3 for the example of merging the index on Marital Status and Region)

When to use it?

Read the rest of this entry »

Posted in BI, Business Intelligence, Data Warehouse, Oracle, Siebel Analytics | 5 Comments »

BI Applications and Embedded BI, Part 4

Posted by Dylan Wan on January 29, 2008

This is my 4th post about the embedded BI. The key is that a OBIEE and OBIA warehouse based solution is embeddable to the OLTP system as long as the OLTP system can provide the basic support.

In this article, I will discuss one of the key enabling technology – integrated authentication.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, EBS, OBIA, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | 2 Comments »

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.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, DAC, Data Warehouse, OBIA, OBIEE, Oracle, Oracle BI Suite EE | 1 Comment »

Your questions about OBIEE or OBIA

Posted by Dylan Wan on January 24, 2008

Some people left me messages in my MeeboMe. If you have questions about Oracle BI Suite Enterprise Edition and Oracle BI Applictions. Here are the links to the public discussion forums:

Oracle BI Suite Enterprise Edition

Oracle BI Applications

Posted in BI, BI Application, BI Links, Business Intelligence, Data Warehouse, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | Leave a Comment »