Oracle BI Publisher is Oracle's leading product for creating enterprise reports. Various data sources are supported; these include Oracle, SQL Server, OLAP and web services. BI Publisher promotes keeping the data processing separate from styling and layout. This separation makes reports more flexible and easier to maintain. Schedules or other events can trigger an automatic update of report data.

OTBI is a data analysis tool aimed at business users, with intuitive subject areas to help users get started. OTBI users don't require deep technical knowledge. However, there are limits to what OTBI is capable of.

OTBI (Oracle Transactional Business Intelligence)

OTBI is a powerful tool for real-time data analysis that allows users to build reports without needing to understand complex internal data structures. Subject areas show the list of available data sources, which map closely to what a business user would recognise. In-depth SQL skills are not required.

The drag-and-drop interface is easy to use and allows the creation of sophisticated visualisations. A step-by-step interface in Oracle BI Composer is also available that guides a user through a few steps to create or update reports.

Oracle BI Publisher

BI Publisher is a powerful tool for creating operational reports, formatted documents and forms. The platform separates data modelling and the visual design tasks of report creation. Data modelling requires knowledge of database design, tables, joins and query languages.

How to choose between OTBI and BIP

• OTBI is usually the quickest and easiest option. Using OTBI is recommended if it can achieve what you need. Security is likely to be simpler, with fewer chances to make mistakes. It is particularly effective for short-term reports where rapid delivery is most important.
• BI Publisher excels with more complex data, advanced requirements and longer projects. Separating the data model from the presentation makes maintenance simpler. Separation is helpful when data models or user requirements change.

Power BI is an impressive mix of software to build reports (Power BI Desktop) and optional infrastructure (the Power BI Service). There are also further options to help create more complex analysis and reporting solutions, including Power BI Embedded, and supporting technologies such as Power Automate and the Azure cloud platform.

Comparing OTBI and Power BI

OTBI is real-time, in the sense that reports query the underlying data directly. Any changes to the data will appear almost immediately in the OTBI reports. Power BI can work in a similar way (using "direct query") but also has the ability to import data into its own data layer. Importing data is an opportunity to cleanse, transform and process the information before creating reports and visualisations.

Power BI can connect to a very wide range of data sources in addition to Oracle HCM. Reports can combine multiple data sources to create sophisticated visuals or if the analysis is more complex.

OTBI integrates closely with Oracle HCM and OBIEE. It is generally easy to set up. Correct setup is critical to ensure data security.

Using Power BI to report on OTBI data

For some situations, it can make sense to connect Power BI to Oracle HCM data. For example...

• When combining multiple data sources to extend or segment the HCM data, particularly if the data needs cleansing or transforming.
• When integrating the reports into web applications or automating the delivery of the reports (or sections of the reports) to a wider audience.
• When existing Power BI reports need extending with HR data, or if report developers are more familiar with Power BI and the Microsoft platform.

There are several different approaches to connecting Power BI to Oracle data sources in general and Oracle Fusion Cloud HCM specifically. A useful strategy is to use Power BI to connect to Oracle's REST APIs. These are interfaces designed specifically to allow third-party applications (including custom software and report designers) access to view the data in Oracle Fusion. (The APIs can also be used to update and manage the data for more advanced applications.)

Data security when using Power BI is critical but complex. Power BI Desktop can access the data using the credentials of end-users that have been granted the appropriate privileges. Automating the data refresh for regular updates should not use the credentials of an end-user, but should use a dedicated resource principal with the required role.

Please contact us to discuss this further as it can be very complex to set up correctly and securely.

• Small datasets

  Keep the data you need to a minimum. Filter the data as early as possible to reduce the load on the database server and help prevent out of memory errors. Queries should only fetch the columns needed (rather than every column). Filtering the rows returned in the database query will be quicker than filtering later in the report.

• Simple filters

  Keep the WHERE clause filters in the SQL as simple as possible. Adding functions or complex logic in the filter can prevent the database from optimising the query. Filters will run for each row rather than as a whole set across the data. Modifying the join condition (when using data from multiple tables) may be more efficient than adding filters in the WHERE clause.

• Sort early

  Sorting the data in the SQL rather than the report will improve the report speed.

• Monitor and tune queries

  Oracle provide several tools to investigate and tune performance. When the database receives a query, it creates a query execution plan. The "explain query plan" feature highlights which elements of the query are the most expensive to run. This allows users to compare different approaches and helps design more efficient queries. Oracle BI Publisher can enable the "explain plan" feature when connecting to Oracle databases.

  Tracing the query will highlight, for example, what indexes the query uses. If the execution plan has not determined the most effective index, query hints can force the index choice.