I was in a conference yesterday and there was some lively debate about the pros and cons of APEX vs Oracle Analytics Cloud, OBIEE & BICS. I thought I’d drill in to this a little further and explore what role does Oracle Application Express plays in a Business Analytics environment and should (or could) Application Express be used instead of Oracle Analytics Cloud?
For the sake of argument, I’m just going to reference Oracle Analytics Cloud here – but the points equally apply to BICS and OBIEE.
The debate – why use OAC instead of just APEX and BI Publisher. Now, BI Publisher is a great tool and actually could, arguably should, form part of an enterprise BI stack.
But what about Apex? The hypothesis was that reporting needs can be better served by building Apex applications than implementing BI – specifically that BI is cumbersome, expensive & difficult to implement.
This is an opinion – but it’s not right. Sorry guys.
I understand some people feel this way. – but let’s try and understand how this might have happened and address it properly. However – let’s just cut to the chase – Apex is great, but it’s not a business analytics tool.
Apex – what is it, what does it do?
APEX is a web based application development platform that sits on top of an Oracle database. It’s bundled in for free and is even included in the free versions of the Oracle database. It provides a web based development UI and allows you to build really powerful data aware applications. It’s a great tool – as part of our boarding pass to cloud programme, we have used Apex to build our Needs Analysis Bot
The argument was that APEX can meet all the needs of a business for reporting and the cost and complexity to deliver APEX solutions is materially lower.
There’s 2 problems with this statement – and it ties in to many of the best practices that need to be adopted to implement world class solutions. Arguably – Excel can meet the reporting needs of a business – but that’s not really world class, is it?
World Class BI
Let’s start with looking at what makes a world class Business Analytics implementation:
- Cost of change needs to be low – new reports should be able to be delivered quickly, easily and shouldn’t be an IT function – business SME’s should be able to build and deploy reports repidly
- Performance needs to be high – reports should run in <5s – and ideally <1s
- Visualisations should be engaging & inciteful – they should invite curiosity.
- Dashboards have to be be aligned to the business needs.
- Efficient, Effective & Smart business modelling, flowing into simple and elegant data modelling
- The cost of change as the business moves on needs to be very low.
The gotcha on any Business Analytics implementation is that you have to make the right decisions on the solution architecture from day 1 – and you have to keep sight of all the goals all the time – you can’t focus on only half – but so often people do and are then surprised when the implementation doesn’t give them all they need.
When I review any business analytics implementation and start putting a plan together on how we transform it into something amazing, I repeatedly see the same design flaws:
- Enterprise Data Architect designs an Inman Datawarehouse model.
- Datawarehouse takes 1-2 years to be built (in one case, I’ve seen it take 3 years).
- Datawarehouse is so fragile and complex that no-one is allowed access to it.
- The data in the datawarehouse is large – but only at the transactional/granular level.
- There is no Performance & Access layer in the EDW – it’s just pushing into a heavily normalised EDW structure.
BI & Reporting
The BI team then get engaged. They start building the RPD and quickly find that a lot of the calculated metrics and variances the business needs to report on aren’t in the database. And the database design is locked down – so they model these in the RPD instead.
The team then start testing and find that reporting performance is poor – running huge sequences or large group by queries against the relational database creates IO, CPU & Memory bottlenecks on the database server. Reports time out.
The next phase is to then turn on caching, so that the report data is pre-cached on the BI (Application) server. This shifts more of the workload off the database and onto the BI server – so batch processing time becomes excessive and capacity and capability limits of the BI server are hit.
All this takes a long time – by now, the business needs have evolved and new reports are needed – but this data is not in the EDW or RPD, so more requirements, design, development and so forth are needed – the reporting needs never catch up with the business.
APEX for Reporting
If you’ve had the above experience of implementing BI – of course it won’t have been a great experience. So you then look at APEX (Or Tableau, Cognos, or any other tool) – Apex let’s you hand craft your own SQL queries, so you can tune to your hearts content. As a database front-end, it lets you run stored procs to prebuild summary fact tables, and it has a functional set of interfaces and visualisations. What’s not to like?
A lot, actually (and this comes from a big fan of APEX – I use it a lot – it’s just not a Business Analytics tool)
- Apex is a singular product working on top of an oracle database – only.
- It is a very technical tool – extensive SQL experience is needed to build queries and reports
- It doesn’t encourage the adoption of any business standards or best practices – it’s a free for all.
- Any new reports will typically be considered a release – including new tables, indexes, procs, views and other associated artifacts.
- It’s a developer tool, not an end user or SME tool
Not really sounding very Enterprise Analytics, is it.
Enterprise Analytics Best Practice
There really is only 1 solution architecture that should be adopted these days for the implementation of a Business Analytics solution:
There are some key points here:
- The EDW is implemented adopting a hybrid Kimball model – ensuring the reactiveness of the model to changing business needs is pretty dynamic and business changes to be swiftly implemented.
- The Reporting Data Marts are an extension of this, based on detail level (but nevertheless potentially aggregated and highly tuned).
And at the top of the stack sits Essbase. Why Essbase? There’s a couple of pretty compelling answers to this:
- Relational data sources don’t lend themselves to financial intelligence
- Everything has to be hand crafted.
- BI Tools include “some” financial intelligence – but it puts the onus on relational DB capabilities
- Difficult to reuse functions across subject areas
- What If & Scenario modelling
- Essbase AND Relational is the right answer!
Essbase includes all these capabilities, right out of the box. The Data Marts (including Essbase) are modelled by subject area – but the key point is to use Essbase extensively as part of the reporting solution – and it’s all about performance:
In a like for like comparison on a 130 million datapoint recordset to report summary level balances:
- Oracle Analytics Cloud (both DVCS and BICS tested) : <1s
- OBIEE or Tableau using EDW Fact tables only : >22s
That’s a pretty compelling result!
Federated Reporting Architecture
Using the power of the BI server, technical data modelling gets pushed into the solution design meaning that report builders no longer care about the underlying technical architecture at all.
This means the report designers and user can drill their way down from top level data (an aggregation of 100’s of millions of data points) through the data all the way into the leaf level transactional data – without having to change datasources or pay any attention at all the the technical underpinnings of the solution.
Correctly implemented (we’ll drill in to that below), the platform and toolkits underneath OAC takes care of the the tech – allowing the reporting teams to ignore and just focus on providing the insight and analytics the business needs. Their focus can now solely be on business needs.
Implementing a business analytics solution that allows an organisation to effectively generate insight into their business requires a bunch of different skills. Not every BI implementer has even 2 of these capabilities – let alone all 3!
Oracle Application Express is a great tool – but it’s not a patch on the Oracle Analytics Cloud Platform. It is even included in the Oracle Analytics cloud platform in the DBAAS license.
However – it’s not an Enterprise Class Business Analytics tool. It’s not even a Business Analytics tool – it’s a comprehensive web developer toolkit. Perfect for developing apps, but not for dashboards & reports, business insight & innovation.
And if your experience of implementing BICS, OBIEE or OAC hasn’t given you the wow factor you hoped for – see how many of the gotchas above ring true! Get in touch and we can definitely get you back on track.