Three Options for Scaling Up E-Business Suite for Reporting

[Oct 10, 2008 Update: Added link to article comparing the use of Oracle Active Data Guard with Oracle Data Guard for EBS environments]

The run-up to our annual OpenWorld conference consists of frenzied activities to ensure that all of our planned certifications wrap up in time to be announced at the conference.  The follow-up from OpenWorld consists of handling questions, bug reports, and escalations from our sessions, panels, and private customer meetings.  Given that this is all on top of our regular day jobs, one day I'm going to print up some t-shirts that say, "I survived another Oracle OpenWorld."

So, back in the blogging saddle again. I'll address one of the architectural questions that seems to pop perennially:

How do I handle heavy reporting overhead without disrupting my E-Business Suite instance's transactional users?  Can I offload this to a separate reporting instance?

:Architecture%20Diagram%20showing%20Oracle's%EBS%20Instance

Reporting against E-Business Suite Global Single Instances

The Oracle E-Business Suite is explicitly designed to support a centralized operating model where business transactions from different geographic regions or organizational units can take place in a consolidated Global Single Instance.  We do this ourselves at Oracle with E-Business Suite Release 12, having consolidated 40 data centers from around the world into a single instance. The diagram above shows our internal deployment of the E-Business Suite Release 12 here in Oracle.

The primary advantage of centralizing as much of your data in a single database is clearly the ease of reporting on consolidated data.  I'm not a reporting or business intelligence specialist any more, so I'll leave it to other functional experts to discuss the latest aspects of our analytic tools like BI Publisher, OLAP, and Oracle Business Intelligence Applications.  I'll going keep the scope of this article to the relative merits of different architectural approaches to handling this kind of load.

Options for Handling Reporting Loads

Depending upon the type of reporting that your end-users require and the resulting performance bottleneck, your architectural options fall into the following general categories:

  1. Mirror your production data to a reporting instance
     
  2. Scale up your application tier
     
  3. Scale up your database tier

1. Mirror Your Production Data to a Reporting Instance

The primary advantage of this approach is that reporting users can run whatever reports they wish, whenever they wish, without ever having to worry about affecting your transactional users.  This standalone environment can be scaled up as needed.

The disadvantages are numerous and non-trivial:

  • Cloning the database from production can be time-consuming and may require downtimes
  • Reporting instances can double your storage costs (or multiply them by an order of magnitude if you build custom star schemas or dimensional summaries on top of your production data)
  • Reporting instances need to be maintained, secured, and licensed separately
  • Reporting instances will not have up-to-the-minute data, since database cloning or replication will invariably be performed only periodically
  • Replicating specific data subsets from production requires custom development
  • Custom development requires data model synchronization whenever the source E-Business Suite data model changes

Some customers end up deploying reporting instances regardless of these disadvantages.  These users have consciously weighed the cost-benefit balance and have concluded that it's worth it.

Your mileage will vary.  Before you decide on to taking this approach, I would urge you to weigh your expected benefits against the operational and business costs carefully.

2. Scaling Up Your Application Tier

If you're running an analytic tool such as Discoverer, you may find that the performance bottleneck is on your application tier.  This was particularly acute with earlier versions of Discoverer 4i, which tended to merrily ignore configuration settings for maximum memory usage.  This was a bug with fairly severe performance implications.  When lots of Discoverer 4i users ran heavy workbooks simultaneously, it wasn't uncommon to see application tier server CPU and memory usage peg at 100%.

If this is your only bottleneck, it's relatively simple to address:

First, if you're still on Discoverer 4i, you should immediately plan your upgrade to Discoverer 10g.  Discoverer 10g has made tremendous strides in respecting the memory limits you set.  Perhaps more notably, Discoverer 10g now automatically trims unused columns out of its generated SQL statements, which dramatically reduces both your application and database tier loads.

Second, if you haven't already done so, you should consider moving your Discoverer instance onto a dedicated server.  It's technically feasible to run Discoverer off your existing E-Business Suite application tier server (in a separate ORACLE_HOME), but doing so can sometimes feel like putting two sumo wrestlers on a single bicycle.

Third, if the number of concurrent Discoverer users warrants it, you can create a cluster of Discoverer servers behind a load-balancer. 

The advantages of this approach are:

  • Easier performance scaling:  you can add nodes as your application tier reporting loads increase. 
  • Improved fault tolerance:  if one application server node goes down, the other nodes can take over the load.
  • Finer-grained load-monitoring and tuning.
  • No custom development required:  this is a fully certified, documented, and supported architectural option for E-Business Suite environments.

The disadvantages of this approach are:

  • Separate application tier nodes need to maintained, secured, and licensed separately

I've been talking about Discoverer up until now, but I should emphasize that this general approach applies to all reporting or analytic tools that generate load at the application tier level.  This includes products such as BI Publisher, Oracle Business Intelligence Enterprise Edition (OBIEE), Oracle Business Intelligence Applications (OBIA), and third-party reporting products.

3. Scaling Up Your Database Tier

Even if you've carefully separated your middle tier OLTP transaction load from your middle tier reporting load, everything still ultimately boils down to running queries against your E-Business Suite database.  At some point, your database server may start to look a little weary of the additional load from your reporting users.

The first step for many customers is to add memory and additional CPUs to their existing servers.  That's fine until you hit the ceiling for your existing hardware.  The next step is to consider getting a bigger server with the latest smoking multi-core CPUs and more expansion options.  At some point, though, you're still going to max out a single database node, regardless of the size of that database server.

This is where Real Application Clusters (RAC) comes in.  In addition to the obvious advantage of improved scalability, the other major advantage of using Real Application Clusters is that it allows you to segregate your reporting load onto a separate database node from the rest of your transactional users.  For example, in a three-node RAC-enabled EBS environment, your HRMS Payroll users can be handled by DatabaseNode1, your Financials users can be handled by DatabaseNode2, and your reporting users can be handled by DatabaseNode3.

Other advantages are:

  • Improved fault tolerance:  if one of the database nodes goes down, the others can pick up the load.
  • Finer-grained load-monitoring and tuning.
  • No custom development required:  this is a fully certified, documented, and supported architectural option for E-Business Suite environments.

The disadvantages are:

  • Increased licencing costs for Real Application Clusters
  • Increased database maintenance overhead and complexity

Get a Second Opinion

Naturally, the goal of this article was to provide you with a decision-making framework rather than making a set of specific recommendations for your needs.  It never hurts to let your Oracle account manager -- or the ERP consultancy of your choice -- know that you're debating various architectural options.  If they're doing their job right, they should be able to engage some  specialists to discuss your requirements with you in more detail. 

Related Database Tier Articles

Related Application Tier Articles

Comments:

Many of the companies I have done Apps DBA consulting for had a standby instance. With 11g you now have the ability to open that standby for reads (like before) and writes and then flashback that instance and roll forward on archivelogs.

Additional cost? Zero. You're fully leveraging your standby database that you're paying licensing for already.

It was one of the major reasons my current client moved to 11g as soon as it was certified for Apps 11.

p.s. Sorry I didn't get to meet you at OpenWorld. I figured I'd get more bang for my buck at VMWorld. Maybe next year.

Posted by Jay Weinshenker on October 07, 2008 at 11:44 AM PDT #

Hi, Jay,

Yes, the new 11g support in this area makes things much easier for customers who've moved to that version and have licenced the appropriate new database options. The cost-benefit equation tips in different directions based on whatever you might have already purchased, which is why it's so hard to make blanket recommendations that cover everyone's situations.

I'd be interested in hearing more about your client's experiences with E-Business Suite and the 11g Database. If you have the time and inclination, feel free to drop me a private email with more details.

I look forward to meeting you at some future conference. My next scheduled stop will be at the upcoming OAUG Collaborate 2009 conference in Florida, I believe.

Regards,
Steven

Posted by Steven Chan on October 08, 2008 at 04:02 AM PDT #

Hi Steven,

There is another aspect that you could have mentioned: using Database Resource Plans to put on-line users, Concurrent Manager queues, say Discoverer users, etc. into separate groups. This improves experience of on-line users while still fully using processing capabilities of each database server. It works on single or RAC instances, on RAC you have the additional advantage of load balancing.
I have implemented this for a few Customers since 9i and up with considerable success.

Kind regards,
Zsolt

Posted by Zsolt Kövesi on October 08, 2008 at 07:22 PM PDT #

Hi, Zsolt,

Thanks for pointing that out. We haven't done any formal certification testing with Database Resource Plans with the E-Business Suite, so I'm very pleased to hear that you've had good experiences with this feature. I'll be sure to pass your comments on to our Applications Performance Group, too.

Regards,
Steven

Posted by Steven Chan on October 09, 2008 at 05:47 AM PDT #

I'm on the tech side, so I'm not a familiar with replicating data from EBS, but could you take advantage of Active DataGuard in 11g to provide a secondary instance for reporting?

Posted by Jeff Ritschel on October 09, 2008 at 11:50 PM PDT #

Hi, Jeff,

The discussion about Active Data Guard requires a bit of insight into how the E-Business Suite works. I've just published the following article with a more in-depth discussion about this:

Comparing Oracle Data Guard vs. Active Data Guard for EBS Environments - http://blogs.oracle.com/stevenChan/2008/10/comparing_oracle_data_guard_vs_active_data_guard_f.html

Regards,
Steven

Posted by Steven Chan on October 10, 2008 at 07:01 AM PDT #

Steve,

Can you advise if/when Oracle's Discoverer can report off (as officially supported) from an Active Data Guard standby site?

Posted by sutanu das on May 28, 2010 at 04:27 AM PDT #

Not yet. We're still working directly with the Active Data Guard on enhancements to support EBS reporting against ADG standby databases. Stay tuned; I'll share more news here as soon as things develop further.

Regards,
Steven

Posted by Steven Chan on May 28, 2010 at 07:12 AM PDT #

Hi Steve,

Thanks in advance.

I have read with great interest the current article as well the article on Data
Guard Vs Active Data Guard, posted on your blog.

You mentioned above (on 28-May-2010) that Oracle is still working with Active Data Guard on enhancements to support EBS reporting against ADG standby databases. Any progress on that since it's been more than a year since this blog was updated?

Best Regards,
Vasu

Posted by Vasu Ranganathan on October 09, 2011 at 05:42 PM PDT #

Hi, Vasu,

Yes, we've made some progress on this, but the project isn't completed yet. I'll post an announcement on this blog as soon as it's ready.

Regards,
Steven

Posted by Steven Chan on October 11, 2011 at 04:01 AM PDT #

Is this available yet?

Posted by Fred on September 26, 2012 at 09:16 PM PDT #

Hi, Fred,

Not yet. I'm afraid that a number of other critical priorities interceded, so we had to divert resources to handle those first. We've restarted this project now, but I don't have any ETA that I can share at this point.

I'll post updates on this blog as soon as they're available.

Regards,
Steven

Posted by Steven Chan on September 27, 2012 at 08:13 AM PDT #

Hi Steven,

Is there any upadte now on EBS reporting with ADG?

Thanks,
-Anish

Posted by guest on February 25, 2013 at 01:08 PM PST #

Hi, Anish,

Oracle Business Intelligence Enterprise Edition (OBIEE) has been certified to run reports and ETL against an E-Business Suite Active Data Guard database. I'm told that you can refer to the generic OBIEE documentation for procedures (I haven't seen these myself). If you need help with OBIEE + ADG + EBS, your best bet would be to log a Service Request against OBIEE.

We're still working through the details for running Discoverer against an EBS + ADG database. I'll post more details on this blog as they become available.

Regards,
Steven

Posted by Steven Chan on February 25, 2013 at 01:20 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today