Struggling with Cross-Subject Area OTBI Reporting?
Oracle’s cloud market shares are growing day by day. The three primary applications that are expanding very fast are: HCM Cloud, Oracle ERP Cloud and SCM Cloud.
Today we’re going to focus on OTBI, which comes as a part of your Oracle Application. And the agenda is – what are the challenges? What are the solutions? What can you do? And we’re going to make some recommendations on how we can ease some of the struggles and pain points you experience while using.
What is OTBI?
OTBI reports are standard out-of-the-box reports that Oracle provides when you go live with the SaaS applications. There are several out of the box reports across different areas, such as:
1. HR reports
This is a detailed report which shows all the details of employees in the organization from an HR standpoint – their HR attributes, the location, job, etc.
2. Absence Breakdown
This provides visual reports, where you can select your years and see how many hours of absence people have taken.
3. General Journals Report
Similarly, there are other reports on the financial areas and supply chain. GL journal reports is when you export one of the standard OTBI reports to excel. It just shows you all the journal entries, and you can look at what entries are made in a time Range and for what organizations. They cover a wide area of your SaaS applications.
What is Subject-Areas?
All these reports that come out of the box are built on something called subject areas. Oracle has provided these pre-built subject areas, which are logical collections of similar attributes. Let’s say all the financials or all the general Ledger attributes are combined into one subject area and all the HR in another subject area.
It’s an approximate collection of all the data items you would want to report on, and these are all pre-built. And the expectation is that you can choose through these subject areas and build your reports if you like. You can use the Oracle out-of-the-box reports, or if it doesn’t meet your needs, you can create your own reports using these standardized subject areas.
When you choose a subject area after the fact, you are presented with this list of facts and dimensions: the folders and subfolders inside those, and there are individual fields. If you look at the bottom, you can drag and drop and build your own reports in OTBI if necessary.
How to Create Reports in OTBI?
- Learn Subject Areas
- Create reports from Scratch
- Learn OTBI formatting/ How to add Prompts
- Learn how to do Pivots/Charts
- Learn changes in the OTBI Subject areas
If the standard reports do not fit your needs and if you have to create your own reports, then this is the general procedure at high-level. Basically, you must first learn the subject area, you need to figure out which subject area interests you, you have to go up and down the lists & figure out which one is it. Once you choose the subject area, this is how a list of fields comes up, and then you can then drag and drop your own individual fields onto this right blank canvas, and then that can build your report as you go. So, to create from scratch, all the columns that you select, you can just drop here, and it becomes a report by itself.
Drag and drop reports sound simple, but we have seen from the customers that once you have several dozen reports to build and potentially hundreds of columns, this drag and drop becomes cumbersome. It sounds simple, but once you start doing it, it’s pretty laborious, and it takes up quite a bit of your time. And then similarly, after you drop these columns, you have to do the formatting then, learn how to format those reports, and add the prompts, then you have to do pivot tables and charts. You must keep up with the changes in the subject areas that come along with every release. So basically, as Oracle goes through its paces, new things get added, or things get repositioned. So, in some cases, you have to keep up with those changes on the subject areas, which may or may not have those effects on your reports.
Once a report is built in the OTBI, it becomes your property. So, if there are any architectural changes or if Oracle enhances the functionality that no longer complies with the report you previously built, you must revisit the report & tweak it as necessary. That is a custom report, and it stays in your area of responsibility.
As your organizations mature, you will find that you must build your reports across subject areas. For example, you must show data from the general Ledger and reconcile the data with one of the sub-ledgers. OTBI allows you to select two different subject areas from two different business functions but what usually ends up happening in the first shot is that the minute you drag-and-drop fields from two different subject areas, you will be presented with an error message.
Why does this generally happen? Because the fields we drag and drop are not what OTBI was expecting? And when you’re running to this error message, it is not super descriptive and doesn’t tell you what to do to fix the problem.
The OTBI cross-subject out-of-the-box reports are few and far between borderline non-existent, but only if you build your own. It takes quite some expertise to make it happen if you can do it, but it takes some time to learn to make it up. Once those reports are built, you run into other challenges, and that’s what we are hearing from our customers who have been in OTBI for several years.
- Missing data sets in the OTBI
- Cross subject area operational reports
- Cannot report off legacy/other applications
- No on-the-fly report modifications
- Payroll reports must be run from Payroll “flows”
- No multi-threaded reports
- 75,000-row limits
- Inability to add more subject areas
- No Excel Plug-in
- High Time-to-Value
BI Publisher and its Limitations
To overcome the OTBI challenge, BI Publisher is your answer. But to use BI Publisher you need –
- Comprehensive SQL Knowledge – It requires thorough SQL knowledge as you’ll have to write the actual SQL statements, which also requires the SQL syntax, experience, and understanding.
- Entity Relationship Knowledge – you also need to understand the entity-relationship between those different entities that the SAS application has. You must learn how those entities relate to each other.
- Data Security Missing – Unlike OTBI, the BI Publisher does not automatically inherit the data security profiles. So it’s on us as developers to build those security profiles back into the reports & if that extra care is not taken, the security will just not work. Everybody gets to see all data in those cases.
- No multi-threaded Reports – Just like OTBI, there is no straightforward way to do multi-threaded reports.
- RTF Templates Needed in Most Cases – RTF templates are required in most cases, once you write these data models. So once you create these SQL statements, to give them a visual appearance, we’re supposed to create RTF templates that feed off this data model and show data in a kind of a layout.
- Very High time-to Value – This also has a very high time to value because you first have to build these SQL statements with no defects. Because it’s so technical, it’s very easy to get defects around these. It takes a while to resolve the problems, and if it will, we have to build RTF templates that will produce the visual reports for you.
It looks like the first option is OTBI and the second option is the BI Publisher.
BI Publisher is quite technical in some cases, but then the other option is to limit it. So, there isn’t a middle ground, apparently that you want to be on that. So, what is the other option that you want to go for?
The other option that we are proposing, and many of our customers have been using already, is SplashBI. It has developed a so-called middle ground that overcomes a lot of these challenges and simplifies all of these problems.
Enters…. SplashOC powered by SplashBI
SplashOC powered by SplashBI comes with tons of pre-built reports. Starting from elementary reports to extremely complicated cross-subject area reports.
- These reports don’t have 75,000-row limits.
- There’s also an Excel Plugin that does complete ad hoc changes in Excel and still reports off of your Oracle Cloud applications.
- There aren’t any RTF templates to be built.
- No extreme knowledge of Entity-Relationship is required.
So all of those problems disappear because these reports are pretty much pre-built out of the box to execute.
It also shows you the past executions of the report, so it remembers the history.
Once your report is completed, you can see the results in these many formats: HTML, PDF, CSV…But usually, Excel is the most popular one. So in which case, if you click on Excel, it downloads a spreadsheet just like any other ordinary spreadsheet, and then you can pop it open, just like any other spreadsheet does. So it brings in all of your Oracle SaaS application data into one simplified view.
Also, we have about 563 reports to be precise, that are pre-built out of the box, which will run off the SAS application in SplashBI. There are 233 HCM reports, 169 for financials. And these numbers are growing, and we add more reports as the new functionality comes in.
We keep an eye on how Oracle is evolving the ERP application or the HCM application. And we go and manage & maintain these reports. So the burden of management and maintenance is not on you as customers, it’s on us to keep them up to date with all of the progress in the Oracle Applications per se. So you don’t have to keep an eye on all the subject area evolution yourselves.
In conclusion, these are the available options –
- Continue to use OTBI?
- Learn SQL and use BI Publisher.
- Buy Oracle Analytics Cloud ($$$)
- Oracle Cloud Reports with SplashOC ($)
SplashOC fills and complements the gaps that you’re experiencing right now with OTBI using the SplashBI cloud reports. OTBI provides you the real-time analytical capability of the current state of your business based on the latest and greatest information from the transactional system. However, it falls short when we start talking about historical snapshots of the data, cross-subject reporting, trending off your KPIs tracking changes over time.
SplashBI fixes all those problems & fills the gap that OTBI cannot do.