Migrating from OBI to Incorta

I am sharing my experience of migrating from OBI to Incorta.

Process

1. Start with Incorta EBS Blueprint
2. Configure and customize for the deploying company
3. Optionally, Demo the Fusion Connector
4. Preview and demo to business users using their own data
5. Provide the existing OBI dashboard usage analysis – Help prioritize the replacement project
6. Provide the lineage and SQLs from OBI. Get the access to nqquery log and SDE logic to the development team
7. Analyze the SQL and OBI report one by one against the blueprint business schemas
8. Interactive processes about enhancing Incorta physical schema and creating dashboards in Incorta
9. Create materialized View when necessary
10. Demo and get feedback from business users during the development
11. Leverage the existing OBI team in verifying and comparison, help the team gain knowledge about Incorta for future support
12. Prepare the pilot and overlap period before shutting down OBI. Define the Exit criteria
13. Prepare the training to the future analyzers/superuser, who can create and manage their own contents. This may be necessary as a recursive event for several months. Providing an office hour will be helpful.
14. Provide a demo to Excel Add-in if necessary
15. Provide the SQL interface if necessary. Control the usage by understanding their usage. This may be helpful for Tableau users, but need to be careful as it impacts the system resource usage
16. Schedule the dashboard delivery and the download. Configure the integration with network drive storage. FTP, dropbox, OneDrive, GDrive, etc.
17. Along with the development of the new content, start discussion about job schedule options. For example, dependencies from Incorta to source app batch process. Also, understand the source data update pattern and frequency. For example, when the Transfer to GL was executed, when the depreciation process is executed. How frequent these processes are run. For example, any difference in period closing weeks?

Very few resistance from existing OBI developers since most of them see this as an opportunity.

Learning new skills such as using Spark SQL and PySpark in creating materialized views. Explore ML library in Incorta.

Their Data Model knowledge is typically the asset to the new platform.

Oracle App Cloud and Incorta

OTBI is great. But when people are migrating from Oracle EBS to Oracle Cloud App, they would like to view the data from both EBS and Oracle Cloud, Incorta becomes a cost saving and a quick implementation solution without implementing a data warehouse.

Incorta is not a data warehouse although it does has the data storage, which is optimized for extraction, optimized for internal process reading and transformation, and optimized for analytical queries.

The scalability is accomplished by a distributed architecture, and thus no issue with resource contention.

The support of Tableau and PowerBI provides an alternatives for those companies that already standardized their BI front-end.

Very interested to see how Incorta can help upsell Oracle Cloud App, not the other way around.

Schema On Read?

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.

How to access Fusion Cloud for building custom BI?

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.

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

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

Incremental ETL in BI Apps 11.1.1.7.1 (1)

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.

 

 

 

 

 

 

 

ODI Shortcut

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.

 

OTBI vs. OBIA

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. 

 

 

 

User Defined Function (UDF) in ODI

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.

Data Security in Oracle BI Apps

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

Use ANSI SQL for Outer Join

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,

Use SQL*Loader with Named Pipe

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.

Hyperion Essbase Dimension Terminology

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.

Changed Data Capture in ODI

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:

Oracle BI Applications 7.9.5 is out

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.

Credit Management in OLTP

Account receivables can have a big impact to the cash flow of an organization. Bad credit can hurt your company’s bottom line. Manging customer credits help the company to manage the risk and avoid the issues.

In this post, I will touch credit check and credit limit, centralized and decentralized credit management, and credit limit currency.  This is a a result of studying various credit management features in the OLTP system. The objective is to understand how the these various system works and understand how the credit management process looks like, and how BI can help in these processses.

ABC Analysis in Inventory Management

 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.

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

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

Bitmap Index – when to use it?

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?

BI Applications and Embedded BI, Part 4

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.