Create a complex multi-subject area analysis in Oracle Transactional Business Intelligence

Summary

Oracle Transactional Business Intelligence (OTBI) is a great reporting tool that is embedded in a lot of Oracle's cloud services - including ERP Cloud, HCM Cloud and many others.

A lot of reports and analyses come out-of-the box and you can use the BI tool itself to change existing reports and/or create new ones. Creating a new report is really easy and does not need any IT involvement. It involves dragging and dropping the fields you want analyse and then choosing a nice layout:

But what if you want to need to do something much more complex such as combining data from different possibly unrelated subject areas and joining them together? 

This blog will explain how you can do that and provide you an example.

Subject Areas

Data in the OTBI repository is grouped in subject areas. You can easily create your own analyses using the pre-defined subject areas. These subject areas are defined as follows in the online documentation:

The BI repository defines the columns that are available for you to include in analyses and where data for each column comes from. The repository is organized into subject areas which contain folders with columns. Each subject area has one fact folder and a number of dimension folders. The dimension folders contain attribute and hierarchical columns and are joined to the fact folder within a subject area. For example, if your analysis has Currency attribute from a dimension folder, you see a currency column in the results. If you also add the Total fact, then your analysis includes only those records that have both currency and total amount columns populated.

For the vast majority of your reporting needs you can use drag and drop. 

The online documentation also documents how to create analyses across different related subject areas.

But if you need to create an analysis which combines unrelated subject areas (maybe because you are using flexfields to join data together) then you can't use drag and drop anymore.

Advanced OTBI

If you can't use drag and drop anymore then you need to use the advanced capabilities and write your own logical SQL. Don't worry, you will not need to learn the physical data-model to write SQL. You will be accessing the same subject areas you use when you are simply dragging fields on your report.

Let's take this step by step an create an advanced OTBI analysis.

Create Analysis

From within OTBI you navigate to 'New - Analysis':

Instead of choosing a specific subject area you choose 'Create analysis from simple logical SQL':

Write SQL

A popup opens where you can enter your logical SQL. It is much easier if you have already done that beforehand in a simple text editor such as notepad. You write the SQL statement and use the subject areas as tables. 

If you do not know the exact name of the columns then you just drag and drop the columns into a simple analysis and navigate to the 'Advanced' tab. Below you can see the logical SQL that is issued for this simple analysis:

You now copy this SQL into notepad. You do this for every subject area you want to combine and then you need to write the WHERE clause to link the SQL statements together. A very simple example can be found below:

Let's analyze this SQL statement:

• It combines two unrelated subject areas together. In this case "Procurement - Purchasing Real Time" and "Receivables - Transactions Real Time"
• It queries two columns from each subject area
• In the WHERE clause the join is made on one column from each subject area. You can make this WHERE clause much more complex, but most of the time you join on one column from each subject area.

Make sure you edit the SQL in notepad first so there will be no extra formatting in the SQL statement. Keep it simple.

View the results

This is the simple part. Just navigate to 'Results' and you can see the first results of your analysis:

You can now format this analysis and use the visualization that makes most sense.

So What?

One of the main advantages of Oracle SAAS services is that all Supply Chain, Sales and Service, ERP and HCM and other data resides in one database. This blog shows that even though the OTBI repository is organized in distinct subject areas, you can always report across ALL of your real-time enterprise data without the need to move data into a data-warehouse first.

If you need to combine data that resides in Oracle SAAS services with other data then you can use the advanced capabilities of Oracle Analytics Cloud - but that may be the subject of another blog in the future.

I hope this is helpful and good luck!

Alex.

I am not a spokesperson of Oracle and the information in this blog reflects my personal findings and opinions.