Build a Performance Layer to Make the BI Apps Fly

The BI Apps from Oracle present customers with a nice head start to getting their BI environment up and running.  Financial Analytics, HR Analytics, Supply Chain Analytics, etc. all come pre built with much of the code and reports you’ll need to build to support your business.  But for many customers they just are too slow for their user community while running dashboards, reports and ad-hoc queries.  In an era where an internet search engine can give you what you want in one second, reports running for a minute or more are just not acceptable.

In this post I’ll discuss some of the inherent performance limitations in the BI Apps and what you should do about it.  Note the vast majority of customers really don’t have a performance problem with their system, but you can always deliver reporting content faster.  If you are running at 15 seconds per page, wouldn’t 5 seconds be that much better?  The performance problem really lies with some large customers with larger data volumes.  It is here where the BI Apps design can be enhanced with more performance in mind.

I’ve written about OBI performance a few times in the past, and I’m sure there will be more to come.  As a refresher, here are a few other posts to take a look at:

• Achieving Good Performance with OBIEE
• OBI Performance Preso
• Performance Tuning Financial Analytics
• Stitch Joins

Big Data for Oracle BI

The term Big Data has existed in some form or another for years but recently has taken on a new and more official meaning.  In today’s world of massive internet applications, digital instruments streaming non-stop data, scientific data collection and fraud detection, Big Data has grown far beyond what even a large company used to consider large – into the hundreds of terabytes or even petabytes.  Furthermore, Big Data has a large unstructured component to it, whether comments on websites, blog data, internet usage, images or documents.  This kind of information typically does not map well to traditional database technologies which rely on a very structured table/column arrangement.

Considering high volume and great variability of data, along with very high uptime and extremely short response times needed, traditional RDBMSs simply won’t work – they will not be able to scale out to provide 1 second response time when a Facebook user posts a picture or visits a friend’s wall when there are millions of users looking at petabytes of data.  Thus, completely different kinds of data access and storage technologies are needed, ones which are designed to scale far beyond even a very powerful systems such as Oracle ExaData.

This article discusses Oracle’s view of Big Data and in particular how it pertains to Data Warehousing and Business Intelligence.  Keep in mind there are many offerings and capabilities pertaining to the acquisition and use of Big Data which are well beyond the scope of Data Warehousing and BI systems; I’m going to focus on just a slice of it here.

Performance Tuning and Financial Analytics

In the past I’ve written and presented on OBI performance from the ‘before perspective’: before you begin development, what are the things you should plan on and how should you implement them.  This paper however is with the ‘after perspective’; what to do if you are stuck with a performance problem that you need to solve quickly.  It will use the Financial Analytics (All versions) application from Oracle’s BI Applications (aka the BI Apps) to walk through a plan of attack and demonstrate specific changes using the Oracle Database 10x.  Thus, this paper has two purposes:

1. Specifically to document actual changes for Financial Analytics
2. Generally to walk through a performance tuning effort that you yourself may undertake

Note: You do not need to be working on Financial Analytics or even the BI Apps for the concepts in this article to apply.  It merely uses Financial Analytics as its example, and where appropriate I will explain the model.

I’m going to do something a bit different with this article in that I will tell the story of a recent performance tuning project for a client.  A previous integrator had delivered Financial Analytics and it was up and running in a production environment, but the performance was terrible.  Many queries were over 8 minutes.  We were asked to tune the GL Transactions star, but the lessons learned here will work for all modules of Financial Analytics, regardless of version.  In fact, implementing them for only one star actually boosted the performance of the other Financial Analytics Subject Areas.

Oracle’s Data Warehouse Reference Architecture

Last year Oracle published a white paper called “Enabling Pervasive BI Through a practical Data Warehouse Reference Architecture”.  I took a read through this paper and have some comments on it  (so maybe this is a book report).  For your convenience, I’ve uploaded a copy of it here Oracle DW Reference Architecture Feb 2010.  I also believe the architecture is a little vague (on purpose I suppose).  Depending on how you look at it, you might be looking at how the BI Apps are Architected, or you might be looking at how a real-time Enterprise Data Warehouse is built.

Informatica CDC for Real Time Data Capture

Thanks to my colleague Sobhan Surapaneni for helping me out with some of the details of CDC.  Sobhan was the guy who made it all happen on the project and really knows this stuff cold.

Introduction

Traditional Data Warehousing and BI systems rely on batch data extraction and load routines (ETL or ELT) to acquire their data from sources.  Depending on data timeliness needs, this may work for intervals down to perhaps one hour for small data sets.  However as data timeliness needs go below what is reasonable with a batch process, a complete shift in technology is required.  There are tools out there that acquire data in a “real-time” manner directly from the database log files and send individual transactions out over the wire to a target database.

This post is about Informatica’s CDC product, but the lessons and the manner in which it works are similar for another popular product called Golden Gate from Oracle.  Note the name Change Data Capture is not the best; this really is more about a real-time solution as any DW has to implement change data capture as part of its Data Acquisition approach.

SSDs – A Short Story

Many of you are aware that there is a relatively new kind of storage out there called a Solid State Disk.  Instead of spinning platters with moving heads, SSDs are flash memory chips.  Think of them as a super fast, large thumb drive.

There are 2 main kinds of SSDs, SLC and MLC.  They roughly define the difference between enterprise (SLC) and consumer (MLC), based on both performance and reliability (which of course means price!).  Then there are 2 main interfaces out there – a traditional SATA II/SATA III (just coming out these days) or one that plugs into the bus directly (PCI for desktop PCs and most Intel servers).  The ones that plug into the PIC bus are really only for businesses, as they are very expensive.  But by removing the SATA interface bottlenecks, they are unbelievably fast.

OOW BI Imp Panel Questions #3: Can Exadata Replace a DW?

Click here for the first post in the series and here for the second.

Question #3: With the power of Exadata, are Data Warehouses even needed anymore?  Can’t you just map your BI tools to the transactional system without all of the back end ETL work?

Short Answer: For most systems a DW and ETL are necessary.  However a simple reporting system however can leverage this option for a while.

Long Answer: Data Warehouses started out as a means to offload processing of large data set queries from online transactional systems.  Basically breaking up the workload.

2 OOW Presentations

I’m giving two presentations this year at OOW.  The first one is on planning for good performance with OBI.  It goes over some classic topics that you should consider while you are designing your system.  Its one I’ve given a few times in the past, but it is a message that is timeless really.  I’ts key target audience is architects, project managers, OBI designers/developers, Data Modelers and ETL developers/architects.

The other one is a case study on my current project at Qualcomm.  Its a fantastic project with a lot of interesting components and technologies.  We’re deploying a customer facing, revenue generating OBI solution that is vital to one of their business units.  We’re even using a real-time technology to get data our of our source systems.  I’ll be co-presenting it with the customer, and we’re going to talk about typical case study topics such as the business case, over view the solution, talk about the team and timelines, best practices,lessons learned, etc.

I hope to see you there!

Jeff M.

Here are the details:
ID#: S318485
Title: Planning for High Performance
Track: Business Intelligence
Date: 19-SEP-10
Time: 13:00 – 14:00
Venue: Moscone West L2
Room: Rm 2012

ID#: S315300
Title: Sales Growth with Oracle Business Intelligence Enterprise Edition at Qualcomm
Track: Business Intelligence
Date: 21-SEP-10
Time: 15:30 – 16:30
Venue: Moscone West L3
Room: Rm 3016

Approaches to Building BI Applications and Data Warehouses

Original Post date 2/27/09

I’d like to comment a bit on the differences between BI and Data Warehousing as I believe they are commonly confused and used interchangeably.  Having just come from a project which mixed the two together with poor results, I think some discussion on the topic is a good idea and one that is fresh in my head.  This is a long post, so go get some coffee (or Red Bull) first.

Before I get into this, I fully understand and accept that there are varied opinions about this topic.  I am not going to propose a one-size-fits-all philosophy.  There also may be some technical features out there which some may claim make my comments obsolete.  I am going to focus on the purpose of the two and how that drives how you build a BI system.  If you are hoping I’ll get into a Kimball vs. Inmon data warehouse architecture or modeling discussion you will be disappointed.  I recommend you read their books and articles for the details of their solutions.

I am going to approach this by defining two very distinct items at different ends of a spectrum, and then discuss how they can and cannot be merged into the same thing.  The point is to contrast the two needs so conflicts become apparent.

Achieving Good Performance With OBIEE

Originally posted 1/30/2007

How do you extract good performance from Oracle Business Intelligence (OBIEE)?  Are there any tool specific features that can be used to achieve better performance, or is it all about the backend?  This post will discuss some things to consider to make a page or report perform well; scalability is not included here.