Dylan's BI Study Notes

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

Archive for the ‘OTBI’ Category

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.

Advertisements

Posted in BI, BI Application, Business Intelligence, OBIA, Oracle, OTBI | 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.

http://docs.oracle.com/cd/E15586_01/fusionapps.1111/e20836/adf.htm#BIEMG3435

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 »

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 »