BI & Warehousing

Technologies behind Oracle Transactional Business Intelligence (OTBI)

Dylan Wan - Thu, 2013-07-25 16:57

Oracle Transactional Business Intelligence (OTBI) is one of the business intelligence solutions provided as part of Fusion Applications. 

To build a real-time BI, the major challenge is to make sure that it can perform and has no or minimum interfere to the core objective of the transactional application, the online processing.

This is the reason why we need Oracle Business Intelligence Applications (OBIA) for Fusion Applications.  The idea is to keep the minimal processing of detecting changes and capturing changes in the transactional system and leave everything else, such as, preparing and consolidating the data for reporting, to BI Applications.

Here are some of the technologies available to make OTBI possible:

1. SQL Trimming from ADF

ADF stands for Application Development Framework.  It is the application development framework used in developing Fusion Applications.  In general, it is a declarative metadata driven framework to let the application developers to define the data model, define the data access layer, define the UI rendering, put the validation logic and processing in the middle tier.

The underlying data model, in most of cases, is still the relational model defined in the Fusion Apps transactional database under the 3rd NF design.

The key enabling technologies provided from ADF to OTBI is the “Composite VO” or “Composite View Object”.  For me, it can generate the database SQL for us based on the metadata.  Unlike the database technology using the database view, ADF engine can look further down to the entity objects included in the view object and selectively choose which entities are needed in a given SQL.  If the view object includes two tables (EOs), one primary EO for data at the line level, and the other EO for getting the parent data, When the query (Composite VO) does not include any column from the parent EO, the SQL generated by ADF will not include the table in the join. 

This is a superior technologies, comparing to the old technologies of building the business views.

If you are a Java programmer and would like to get the feeling about what Composite View Object looks like and how it works, here is a good blog post:

Do you know what is a Composite View Object?

2. BI Platform – ADFQuery to Composite VO

This enabling technology is provided by BI platform and available as a Java library. It adds a layer on top of the ADF composite VO.  Without writing the Java code, it generates the codes of creating the composite VO on the fly.  It allows us to query the data from the ADF engine by sending them a XML block called ADFQuery.

This doc shows some of the ADFQuery XML blocks.

To see better examples, you can find them in NQQuery.log files.

It is a query language like SQL.  You have the section  for the column projection, the join criteria using view links, and the filter using view criteria.

Here are other enabling technologies behind OTBI.

3. ADFQuery generation from BI Server

4. SQL By Pass Database

5. Relational to Dimensional Mapping (Physical Layer to Business Model Layer)

6. SELECT Physical in initialization block

7. ADFQuery Initialization block

8. Physical Lookup function from BI platform

9. Logical Lookup function from BI platform

10. Data Security enabled at the VO layer via Fusion AppCore

11. Applcore Tree Flattening

12. Applcore Business Intelligence Column Flatten VO (BICVO)

13. BI Flexfield VO generator

14. BI Extender via Import Wizard

15. BI View Object created based on the BI EE Logical SQL (BIJDBC)

16. Effective Date VO with as of date filter

17. ADF Application Module to BI variable interface

and more…

Regardless, the goal of these technologies is to enable the users to get the real time data access to the Fusion Apps.  There is really little or no much we can do for providing the feature like data snapshot, pre-built aggregate, multiple currencies, data consolidation and conformance, cross subject area analysis, and the most important, the query performance with complexity logic to be available in a reasonable time without the interfere to the transactional system.

Categories: BI & Warehousing

Oracle GoldenGate 11g Handbook

Amardeep Sidhu - Thu, 2013-07-18 10:02

Few months ago I contributed a chapter (on Monitoring, Troubleshooting and Performance tuning) to a GoldenGate book on Oracle Press that Robert Freeman was authoring. Thought of posting a small update that the book is now out. My name doesn’t appear on the main page Sad smile but you will see it in the Acknowledgements section Winking smile Below is a screenshot taken from Amazon preview Smile.

You may want to grab a copy if you are using/planning to use Oracle GoldenGate 11g.

Here is the link to the book page on Amazon. It seems the book is not published in India yet but one can order the imported edition on


Categories: BI & Warehousing

Exalytics - Version X3-4 is Here

Look Smarter Than You Are - Tue, 2013-07-16 21:15
I've mentioned before that the Exalytics X3-4 was nearly available (the first clue was when it hit the engineered system price list back on June 4).  It was talked about at-length during the Kscope13 Sunday Developer's Symposium and... it's finally here.

Hardware Upgrades
  • RAM.  Doubling from 1 terabyte to 2 terabytes.  This will help everything on the box but those of us running Essbase now have even more RAM to use for making RAM drives.
  • Flash.  Exalytics now comes standard with 2.4 TB of flash.  I mentioned this earlier as an upgrade option to the Exalytics X2-4, but it now is native to the X3-4.  As mentioned in my earlier article, flash impacts Essbase performance far more than OBIEE (which isn't as disk I/O intensive).  Having .25 millisecond read latency (what these flash drives are rated) means there's virtually no seek time finding values in an Essbase cube on disk.  I'm expecting most Essbase customers will put their physical cubes on the flash drives and then quickly load them into a RAM drive upon start up (which has better performance than reading into the Essbase caches for each database).
  • Hard Drive.  They are upping the traditional hard drives from 3.6 TB to 5.4 TB.  It still has 6 physical drives in it, but they are going from 600GB drives to 900GB drives. [Updated on 8-25-2013.]
The cores (still 40) stay the same... for now.  At some point, someone is going to start hitting these limits and they're probably up the cores and I wouldn't be surprised if they went 100% flash drives in a future release.

X3-4 supports OBIEE, Endeca 3.0, Essbase, and any Linux-allowed Hyperion EPM product on  They also strongly imply that there are some Essbase optimizations in that only work on Exalytics, but I haven't found them yet to verify.  Regardless, Exalytics X3-4 is the best engineered system you can currently buy for Essbase, bar none.

Pricing: $175,000
The X2-4 was $135,000 for the hardware (software sold separately), but to add-on flash, you paid an additional $35,000 giving us a real price for X2-4 of $170,000.  The new box is $175,000... and for that additional $5,000, they double the RAM and increase the hard drives 50%.  In other words, you're getting a hell of a deal.  For what is literally $40,000 more in total, you're getting 1 TB more of RAM, 2.4 TB of really good flash, and 1.8 TB of additional hard drive.

What if I Already Bought an X2-4?
First of all, congratulations.  You're really smart, despite what your high school guidance counselor said.  To upgrade your X2-4 to an X3-4, you can buy an upgrade kit!  The upgrade kit (to get flash and the 1 TB of RAM) does cost $105,000 though.  So your X2-4 with an upgrade to an X3-4 will end up costing you $240,000 in total.  Oracle will support your X2-4 under their lifetime support policy even though it is being phased out.  

You can order an X3-4 now.  I haven't seen one actually ship yet, but it was just officially launched yesterday.  While I think you can still buy the X2-4 until the end of this quarter (August 31, 2013), I'm not at all sure why you would.  Find the extra $40K and get not only blazingly fast flash drives but more RAM than you know what to do with.
Categories: BI & Warehousing

Next Generation Essbase Outline Extractor Released!

Tim Tow - Sat, 2013-07-13 00:05
After a bunch of work, we have finally released the Next Generation Essbase Outline Extractor. So, what is the Next Generation Essbase Outline Extractor?  Well, the original outline extractor was written in Visual Basic and Visual Basic has has not been supported by Microsoft for a number of years.  In addition, the original Essbase Outline Extractor requires the 32-bit runtime client which is getting harder and harder to support.  In addition, users have asked for new functionality over the years and it just wasn't feasible to add the new functionality to a VB based product.

Some people have asked me "What about the new Maxl Outline Extractor?"  That is a good question, but keep in mind it only works with 11.1.2.x and higher plus it gives you an XML output. You still have to do something with the XML format for it to be useful.

The Next Generation Essbase Outline Extractor was completely redesigned from scratch.  The main design goals were:
  • Design for long term
    • Extensible
    • Use Oracle’s strategic technology
  • Design for supportability
    • Wider adoption
    • Wider platform choices
    • Less support calls
It was also architected for extensibility to enable new functionality to be added and to allow programmers to write their own extensions to the code.  I chose to write the extractor in Java for a number of reasons.  First, it is Oracle's strategic API for the future which means new features of Essbase will be available in the Java API first.  In addition, Java is multi-platform which means it runs on Windows, Linux, AIX, Solaris, and MacOS regardless of whether it is 32-bit or 64-bit.  In addition, with the Java API, we can ship a single zip download that has everything it needs (except Java itself).  There is no need for the runtime client with the new extractor!

The new Outline Extractor also supports a number of features.  Many of these features are ne:
  • Export one or more dimensions in an outline.
  • Filter members to to be exported by dimension.
  • Write to multiple outputs on a single pass of the outline.
  • Output multiple alias tables into a single output file.
  • Write to Hyperion Planning Outline Load Utility format.
  • Output to real relational databases via JDBC.
The extractor was also designed to be run by a batch process using Java properties files.  The properties files can be written from the GUI interface and directly executed from the GUI.  There is even built-in functionality that optionally encrypts the username and password on first usage.

Here are a few screenshots of the new extractor.  The first screenshot shows the GUI screen where you can select one or more writers for output.

The second screenshot shows the option screen showing the options for the Load File Text Writer.

The final screenshot shows the execution screen. 

The Next Generation Outline Extractor is available on our website at  If you have any questions on the new extractor, don't hesitate to contact us at

Categories: BI & Warehousing

Write It Out

Chet Justice - Mon, 2013-07-08 10:54
This one was sitting in the drafts folder for a week or two, then I saw this post on Twitter:

I wonder what percentage of people ask a question and figure it out on their own before you help them. What about in your experience?

— Amy Caldwell (@amyccaldwell) July 8, 2013
Years ago I had a boss who was my technical superior (he may still be). I used to pop in and out of his office, or try to, and ask questions. Most of them were silly, n00b questions.

He was nice, but busy. It didn't take me very long to "read" that. So I slowed down my pace of questions. I began to write things up via email so that he could respond when he the had time. I started to use forums as well. Then I found was directed to How To Ask Questions The Smart Way.

One of the things that became evident quickly is that I didn't always have to hit Send (email) or Submit (forum post), just the act of writing it out forced me to think through the issue and more often than not, I would figure out the answer on my own.

Flash forward five or six years and I started to receive all these questions, either in person or via chat. "Send me an email" was usually my response, especially if I was in the middle of something (see: Context Switching). I was happy to help, just not at that moment. With email, I could get to it when I got a break (or needed one).

One of my favorite people, Jason Baer, who has worked for RittmanMead for the last couple of years, took this to heart. We started working together in December of 2009 and he would pepper me with questions constantly. I could never keep up. "Email the question Jason."

I didn't realize it, but I started getting fewer and fewer emails/questions from him. He began to figure them out on his own. It seemed most of the time he had just missed something, other times he just figured out another way to do something.

Jason is a smart guy. I think I'm smart. Sometimes it's just easier to ask the question without thinking it through. In fact, I do that quite a bit on The Twitter Machine ™, especially those errors that I seem to know but just don't have the bandwidth to research (think DBA type questions). I believe the types of questions that should must be written down are those that deal with Approach (design, architecture, etc). Any of those ORA errors better come along with a link to the error code in the documentation and some proof that you've researched it a bit yourself...but then that's getting into How To Ask Questions The Smart Way.

Go out and practice. Next time you have a (technical) question for someone, anyone, write it down and see what happens.
Categories: BI & Warehousing

Major Price Cuts in Essbase, OBIEE, BIFS, and OSSM

Look Smarter Than You Are - Thu, 2013-07-04 19:20
Pricing Went Down 25-40%While Oracle is pretty good at giving discounts off list price, it's rare when they actually cut their list prices.  Shockingly, they just lowered (for what I believe is the first time since these products made it onto the price lists) the per processor prices on several of their Business Intelligence offerings: Essbase, OBIEE (Oracle Business Intelligence Foundation Suite), BIFS (Business Intelligence Foundation Suite), and OSSM (Oracle Scorecard & Strategy Management).

Per the price list dated June 25, 2013, the per processor prices have dropped substantially:

  • Essbase went from $184,000 to $138,000.  That's a 25% decrease.
  • OBIEE went from $295,000 to $221,250.  That's also a 25% decrease.
  • BIFS went from $450,000 to $300,000.  That's a 33% decrease.
  • OSSM went from $149,250 to $89,550.  That's a 40% decrease.
Now think about this for a second.  BIFS (Business Intelligence Foundation Suite) comes with Essbase, OBIEE, OSSM, and a few other fun things like EAL4HFM (Essbase Analytics Link for HFM).  BIFS was already a great deal because just buying Essbase, OBIEE, and OSSM separately was setting you back $628,250 but as a bundle costs you only $450,000.  That's a 28% decrease off just those 3 components separately.  Now those separate components list at $448,800 or if you buy the BIFS bundle, $300,000 which is a 33% discount off the components separately.

In other words, you now get OBIEE, Essbase, OSSM, and some other products for just $5,000 more per processor than OBIEE cost alone 2 weeks ago (it was $295,000, remember).  The named user costs for these products has not changed which means that they are positioning these price cuts directly at the enterprise customers: companies who are looking to adopt Oracle Business Analytics across their organization.  Considering those prices above are list, enterprise customers should be getting a discount starting off those prices which makes processor licensing start to seem very attractive for large deployments.

Core Factors
Also remember that Oracle doesn't charge this full price for every core on the processor.  They have a "processor factor" which charges less per core.  Depending on the type of processor, the Oracle Processor Core Factor Table will charge between 25% and 100% of the list processor price.

Take Exalytics X2-4, for example.  It has 4 Intel Xeon E7-4800 chips in it.  Each of those chips has 10 cores giving you 40 cores in total.  Based on the Processor Factor, these cores count as only half a processor.  In other words, to license a full X2-4, you'd need to pay for 20 processor licenses which at the new $300,000 price means a list of $6,000,000.  That's the maximum (not including tax, maintenance, TimesTen, etc.) that you'd pay but it would assumedly come in less than that which is really impressive to license an entire Exalytics box for unlimited users.  Unlimited, people.  Your whole organization could access OBIEE and Essbase for at most $6MM in software.

This may be the pricing discount your company needs to buy unlimited user licenses of Oracle Business Analytics.  And don't hold your breath for Oracle to drop any more list prices.  Take it as a gift and buy it before they change their minds.

Update as of 7-15-13According to an article on Information Week, during the release of Exalytics X3-4, Paul Rodwick was asked about the recent price decreases mentioned above.  He gave the intriguing response that while the prices did go down, it's "old news" because Oracle stealthily did it 9 months ago.  While I don't have the technology price list he's referring to (if you do, post a link to it in the comments), here's Paul's quote:
The cost for BI Foundation Suite on a named-user basis has never been changed, but about nine months ago we adjusted per-CPU pricing in part because we were seeing more customers want to license the full complement of Exalytics.
Categories: BI & Warehousing

Context Switching: An Example

Chet Justice - Mon, 2013-07-01 15:45
Last week at #kscope13 I saw an outstanding example of context switching. If you don't know what it is, Tom Kyte explains it here.

The two environments are just "different", separate and distinct. You can do plsql without SQL, you can do SQL (and many times do) without invoking plsql. There is a call overhead to go from SQL to PLSQL (the "hit" is most evident when SQL invokes PLSQL - not so much the other way, when SQL is embedded in PLSQL). Even if this hit is very very small (say 1/1000th of a second) - if you do it enough, it adds up. So, if it can be avoided - it should be.

The session was Using Kanban and Scrum to Increase Your Development Throughput presented by Stew Stryker (not to be confused with Ted Striker) of Dartmouth College (Stew gave me a gallon of Vermont Maple Syrup which exploded in my bag on the flight home, a gift for sharing my hotel room. Thanks Stew! ;)). So here's the example he gave to demonstrate context switching.

Take a list of names and time yourself writing out the first letter of each name, then the second, until you are finished.

Now, same list of names and write them out the way you normally would, left to right.

If the first method was faster, you are a freak of nature.
Categories: BI & Warehousing

Oracle database 12c

Amardeep Sidhu - Wed, 2013-06-26 20:58

So there is a new toy in the market for database geeks : Oracle has released database 12c. Every social platform is abuzz with the 12c activity. So thought that I should also complete the ritual Winking smile

In this post Aman has already summed up many important links.

Maria Colgan has posted some useful links here.

And here is a link to a slidedeck about Upgrading and Migrating to 12c.

Happy 12c’ing !

Categories: BI & Warehousing

Kscope - Oracle Business Analytics Strategy & New Features

Look Smarter Than You Are - Sun, 2013-06-23 11:34
"Business Analytics is a key strategic priority for Oracle."
                 - Paul Rodwick
I'm sitting in the Kscope13 BI Symposium listening to keynote speaker Paul Rodwick, VP of Oracle BI Product Management. Paul was rather interesting despite his flight having landed in New Orleans at 4AM.  On 3-4 hours sleep, Paul reviewed Oracle's Business Analytics strategy.  It's surprising to me how little Oracle's EPM/BI architecture has changed over the last 5 years (other than the renaming to "Oracle Business Analytics."  This is a good thing.
Why?  Because over the last 5 years, the architecture has gone from a products-integrating-is-a-theoretically-good-idea-so-let's-put-it-on-a-slide-cross-our-fingers-and-see-what-happens to an actual integrated solution that uses the various products in the Oracle Business Analytics line together with each product doing a key part.  Instead of "Essbase or OBIEE or an application?" it's "Essbase as the cube platform, OBIEE as the front-end, applications for needs that are often common across multiple companies."

So now that Oracle has gotten the basics out of the way, they're looking to expand their Business Analytics offerings.  Their key focuses for the immediate future are big data, mobile, in-memory computing, and cloud-based analytics.  The last two really speak to technology of deployment (in-memory and cloud), big data seems to be one of those things that everyone is talking about and no one's quite sure what to do with for the moment, but mobile is on everyone's minds and people are actually doing something about it.  To further that immediate mobile need, Oracle is releasing new functionality in every release or patch of the Oracle mobile analytics products.  For instance, Oracle now has a full mobile security toolkit (available on OTN) for companies that want greater security than native Apple iOS provides.

Paul discussed some of the key features in the release (including Smart View as the primary Office front-end for BI going forward).  He mentioned that the bundled patch for OBIEE will be out on a few weeks, so prep yourself for  He also talked about some recent improvements to Endeca in version 3.0 of that product.  While I love Endeca's extremely powerful ability to discover information in unstructured data, right now, most companies are still focused on analyzing their structured information.  Unstructured analysis is definitely coming: it's just only being deployed by a handful of leading-edge companies at the moment.
Where Are They Going?The key releases we should see in the next 9-12 months will revolve around these themes:
  • Visual analysis.  They're trying to make the analysis more intuitive because the majority of users don't spend their day being analysts: they want the system to help them find issues quickly so they can make better business decisions faster.
  • Mobile Analytics. Oracle is planning to create a BI Mobile "Applications Designer" that will allow developers to make HTML5 applications purpose-built for mobile deployment.  They will also continue to improve the mobile applications every version but they didn't go into what some of the new improvements are going to be specifically beyond more HTML5 deployment.
  • Exalytics.  They promised a new Exalytics announcement in the near future.  I'm presuming this refers to the new Exalytics X3-4 version that's mentioned on the June 4 Oracle Engineered System Price List (page 5).  I expect this will be detailed more during Steve Liebermensch's session later this week.
  • Cloud analytics.  Oracle is making a huge investment in the cloud and it looks like there will be more and more applications in Oracle Business Analytics that run in the cloud.  This makes it a lot easier for customers to get immediate ROI from a BI implementation without huge server investments.
  • Big data.  Part of Oracle's strategy in this area is to tie into any data in any source behind the scenes into Oracle BI.  Data agnostic
  • Predictive analytics.  Paul didn't really talk to this one other than to tease that they do have dedicated resources to expanding the Predictive Analytics capabilities of Oracle BI Foundation Suite.  There is some P.A. functionality in Hyperion Planning, Crystal Ball, and Hyperion Strategic Finance and that sounds like it will be expanded into the BI layer in future releases.
The one thing that's really apparent from Paul's session is that Business Analytics is now a $1+ billion dollar portion of Oracle revenue... and they're treating it as such in terms of research and development.  It's a fast growing space and Oracle seems determined to maintain their market share in overall Business Analytics.

I hope to blog later in the week if any new announcements come out.  Coming to you from Kscope13, this is your humble reporter, Edward Roske.
Categories: BI & Warehousing

agent deployment error in EM 12c

Amardeep Sidhu - Sun, 2013-06-16 12:04

Yesterday I was configuring EM 12c for a Sun Super Cluster system. There were a total of 4 LDOMs where I needed to deploy the agent (Setup –> Add targets –> Add targets manually). Out of these 4 everything went fine for 2 LDOMs but for the other two it failed with an error message. It didn’t give much details on the EM screen but rather gave a message to try to secure/start the agent manually. When I tried to do that manually the secure agent part worked fine but the start agent command failed with the following error message:

oracle@app1:~$emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ………………………………………………………. failed.
HTTP Listener failed at Startup
Possible port conflict on port(3872): Retrying the operation…
Failed to start the agent after 1 attempts.  Please check that the port(3872) is available.

I thought that there was something wrong with the port thing so I cleaned the agent installation, made sure that the port wasn’t being used and did the agent deployment again. This time it again failed with the same message but it reported a different port number ie 1830 agent port no:

oracle@app1:~$emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ……………………………………………. failed.
HTTP Listener failed at Startup
Possible port conflict on port(1830): Retrying the operation…
Failed to start the agent after 1 attempts.  Please check that the port(1830) is available.

Again checked few things but found nothing wrong. All the LDOMs had similar configuration so what worked for the other two should have worked for these two also.

Before starting with the installation I had noted the LDOM hostnames and IPs in a notepad file and had swapped the IPs of two LDOMs (actually these two only Smile with tongue out ). But later on I found that and corrected. While looking at the notepad file it occurred to me that the same stuff could be wrong in /etc/hosts of the server where EM is deployed. Oh boy that is what it was. While making the entries in /etc/hosts of EM server, I copied it from the notepad and the wrong entries got copied. The IPs for these two LDOMs got swapped with each other and that was causing the whole problem.

deinstalled the agent, correct the /etc/hosts and tried to deploy again…all worked well !

Categories: BI & Warehousing

Required Reading

Chet Justice - Wed, 2013-06-12 13:10
It's not often that I run across articles that really resonate with me. Last night was one of those rare occasions. What follows is a sampling of what I consider to be required reading for any IT professional with a slant towards database development. Bad CaRMa

how NOT to design a database schema - super classic article. Every data architect should read this !… @timothyjgorman

— Kyle Hailey (@dboptimizer) June 11, 2013
That led me to Bad CaRMa by Tim Gorman. This was an entry in Oracle Insights: Tales of the Oak Table, which I have not read, yet.

A snippet:

...The basic premise was that just about all of the features of the relational database were eschewed, and instead it was used like a filing system for great big plastic bags of data. Why bother with other containers for the data—just jam it into a generic black plastic garbage bag. If all of those bags full of different types of data all look the same and are heaped into the same pile, don't worry! We'll be able to differentiate the data after we pull it off the big pile and look inside.

Amazingly, Randy and his crew thought this was incredibly clever. Database engineer after database engineer were struck dumb by the realization of what Vision was doing, but the builders of the one-table database were blissfully aware that they were ushering in a new dawn in database design...

This is from 2006 (the book was published in 2004). Not sure how I missed that story, but I did. Big Ball of Mud I've read this one, and sent it out, many times over the years. I can't remember when I first encountered it, but I read this once every couple of months. I send it out to colleagues about as often. You can find the article here.

A BIG BALL OF MUD is haphazardly structured, sprawling, sloppy, duct-tape and bailing wire, spaghetti code jungle. We’ve all seen them. These systems show unmistakable signs of unregulated growth, and repeated, expedient repair. Information is shared promiscuously among distant elements of the system, often to the point where nearly all the important information becomes global or duplicated. The overall structure of the system may never have been well defined. If it was, it may have eroded beyond recognition. Programmers with a shred of architectural sensibility shun these quagmires. Only those who are unconcerned about architecture, and, perhaps, are comfortable with the inertia of the day-to-day chore of patching the holes in these failing dikes, are content to work on such systems.

Read it. Remember it. How To Ask Questions The Smart WayEver been in a forum? Has anyone ever given you the "RTFM" answer? Here's how you can avoid it. How To Ask Questions The Smart Way. I read this originally about 9 or 10 years ago. I've sent it out countless times.

The first thing to understand is that hackers actually like hard problems and good, thought-provoking questions about them. If we didn't, we wouldn't be here. If you give us an interesting question to chew on we'll be grateful to you; good questions are a stimulus and a gift. Good questions help us develop our understanding, and often reveal problems we might not have noticed or thought about otherwise. Among hackers, “Good question!” is a strong and sincere compliment.

Despite this, hackers have a reputation for meeting simple questions with what looks like hostility or arrogance. It sometimes looks like we're reflexively rude to newbies and the ignorant. But this isn't really true.

What we are, unapologetically, is hostile to people who seem to be unwilling to think or to do their own homework before asking questions. People like that are time sinks — they take without giving back, and they waste time we could have spent on another question more interesting and another person more worthy of an answer. We call people like this “losers” (and for historical reasons we sometimes spell it “lusers”).
Business Logic - PL/SQL Vs Java - Reg The article can be found here.

I'm don't believe this is the one that I would read just about every day during my first few years working with Oracle, but it's representative (I'll link up the original when I find it). I cut my teeth in the Oracle world by reading AskTom every single day for years. Some of my work at the time included working with java server pages (jsp) - at least until I found APEX. I monkeyed around with BC4J for awhile as well, but I believe these types of threads on AskTom kept me from going off the cliff. In fact, I got to a point where I would go to an interview and then debate the interviewer about this same topic. Fun times.

if it touches data -- plsql.

If it is computing a fourier transformation -- java.

If it is processing data -- plsql.

If it is generating a graph -- java.

If it is doing a transaction of any size, shape or form against data -- plsql.
Thinking Clearly About Performance Cary Millsap. Most of the people seem to know Cary from Optimizing Oracle Performance, I didn't. I first "met" Cary virtually and he was gracious enough to help me understand my questions around Logging, Debugging, Instrumentation and Profiling. Anyway, what I've learned over that time, is that Cary doesn't think of himself as a DBA, he's a Developer. That was shocking for me to hear...I wonder how many others know that. So I've read this paper about 20 times over the last couple of years (mostly because I'm a little slow). I organize events around this topic (instrumentation, writing better software, etc) and this fits in perfectly. My goal is to one day co-present with Cary, while playing catch, on this topic (I don't think he knows that, so don't tell him). Link to his paper can be found here. Enjoy! The Complicator's Gloves One of my favorite articles from The Daily WTF of all time. Find the article here. The gist of the story is this: an internal forum where people were discussing how to warm a given individuals hands on his bike ride to work. The engineers then proceeded to come up with all kinds of solutions...they spent all day doing this. Finally, someone posts, "wear gloves." End of discussion. Love it. I wrote about it years ago in Keeping it Simple. For a few years I considered buying up and try to gather related stories, but I got lazy. You should read this often, or better, send it out to colleagues on a regular basis to remind them of their craziness.

I'll continue to add to this list as time goes on. If you have any suggestions, leave a comment and I'll add them to the list.
Categories: BI & Warehousing

Looking Forward to Kscope13

Look Smarter Than You Are - Thu, 2013-06-06 23:17
On June 9, the rates for Kscope13 go up $300 per person (basically, you're going up to the last minute, I-don't-know-why-I-waited-but-now-it-costs-a-lot-more price).  If you haven't registered yet for what is by far the best Oracle EPM, BI, Hyperion, Business Analytics, Essbase, etc. conference in the world, go right now to and register.  It'll be the best training experience of the year: you're basically getting 4.5 days of training that you won't see anywhere else the entire year... for the price of 2 days of training at an Oracle training center.

And when you register, don't forget to use promo code IRC to save $100 off whatever the current rate is.

The conference is June 23-27 in New Orleans though my favorite day is always the opening Sunday, so make sure you fly in Saturday night.  On Sunday, they turn the sessions over to the Oracle Development team to talk about everything they have planned for the next 1-3 years.  It's the one time each year that you can hear right from the people who are building it what you're going to be seeing in the future.  There's generally an hour on each major product line (an hour on Essbase, an hour on Hyperion Planning, an hour on mobile BI, etc.).  The keynote this year is Balaji Yelamanchili, the head of Oracle BI and EPM development for Oracle.  My only semi-complaint about this year's BI/EPM Symposium is that there's so much content that they're splitting it into three concurrent symposiums: Business Intelligence, EPM, and a special symposium for the EPM business users.

This year will be somewhat bittersweet for me since I am no longer actively involved with the chairing of the conference.  This means that I get to focus on going to sessions, learning things, playing/leading Werewolf games, and of course, presenting a few sessions.  Here are the ones I'm personally teaching:

  • Using OBIEE to Retrieve Essbase Data:  The 7 Steps You Won’t Find Written Down.  This is in the BI track and it's basically all the quirks about connecting OBIEE to Essbase in a way that uses the strengths of each product.
  • What’s New in OBIEE Oracle on Your iPhone & Other Cool Things.  This is also in the BI track and it's an overview of all the things that people will like in (for both Hyperion and relational audiences).
  • Everything You Know About Essbase Optimization  is Incomplete, Outdated, Or Just Plain Wrong.  This is in the Essbase track and it's the one I'm most looking forward to delivering, because I get to break all of the optimization rules we all have been accepting as gospel for close to 20 years.
  • Learn From Common Mistakes: Pitfalls to Avoid In Your Hyperion Planning Implementation.  This is a vendor presentation hosted by interRel.  I get to sit on the panel and answer Planning questions from the audience while talking about blunders I've seen during Planning implementations.  It should be fun/rousing.  Since it's all interRel, I wouldn't be surprised if a few punches were thrown or at minimum, a few HR violations were issued.
  • Innovations in BI:  Oracle Business Intelligence against Essbase & Relational (parts 1 and 2).  This is also in the BI track (somehow I became a BI speaker???) and I'm co-presenting this session with Stewart Bryson from Rittman Mead.  We'll be going over OBIEE on Essbase on relational and compare it to OBIEE on relational directly.  Stewart is a long-time friend and Oracle ACE for OBIEE, so it should let us each showcase our respective experiences with Essbase and OBIEE in a completely non-marketing way.
  • CRUX (CRUD meets UX): Oracle Fusion Applications Functional UI Design Patterns in Oracle ADF.  This is in the Fusion track and I'll be talking about how to make a good user interface as part of the user experience of ADF.  No, this doesn't have a thing to do with Hyperion.
I am looking forward to all the wacky, new things Mike Riley (my replacement as Conference Chair for Kscope) has in store.  My first Kscope conference was in New Orleans in 2008 (back when they called it Kaleidoscope and no one was quite sure why it wasn't "i before e") so this is a homecoming of sorts albeit with 8 times as many sessions on Oracle BI/EPM.  If you're there (and let's face it, all the cool kids will be), stop by the interRel booth and say "hi."  It's the only 400 square feet booth, so it shouldn't be hard to find.
Categories: BI & Warehousing

Webcast Series - What's New in EPM and OBIEE

Look Smarter Than You Are - Tue, 2013-06-04 10:56
Today I'm giving the first presentation in a 9-week long series on all the new things in Oracle EPM Hyperion and OBIEE  The session today (and again on Thursday) is an overview of everything new in all the products.  It's 108 slides which goes to show you that there's a lot new in  I won't make it through all 108 slides but I will cover the highlights.

I'm actually doing 4 of the 9 weeks (and maybe 5, if I can swing it).  Here's the complete lineup in case you're interested in joining:

  • June 4 & 6 - Overview
  • June 11 & 13 - HFM
  • June 18 & 20 - Financial Close Suite
  • July 9 & 11 - Essbase and OBIEE
  • July 16 & 18 - Planning
  • July 23 & 25 - Smart View and Financial Reporting
  • July 30 & Aug 1 - Data & Metadata Tools (FDM, DRM, etc.)
  • Aug 6 & 8 - Free Supporting Tools (LCM, Calc Mgr, etc.)
  • Aug 13 & 15 - Documentation

If you want to sign up, visit  There's no charge and I don't do marketing during the sessions (seriously, I generally forget to explain what company I work for).  It's a lot of information, but we do spread it out over 9 weeks, so it's not information overload.

And bonus: you get to hear my monotone muppet voice for an hour each week. #WorstBonusEver
Categories: BI & Warehousing

Using Load Plan for managing your ETL task in BI Apps (1)

Dylan Wan - Tue, 2013-05-28 02:24

One of the major change introduced in BI Apps is the way how we manage the ETL task sequence and trim the unnecessary tasks.

This functionality was accomplished earlier using DAC.  The problem we frequently faced was that the DAC repository and the INFA repository are maintained as two separate repositories.  We have to sync up the name of tasks exactly in order to use DAC to manage the task execution of the Informatica workflow tasks.

Load Plan and Load Plan Generator was designed for addressing this requirement.

Here is a good article that describes the story.

Load Plan Generator – An Inside Look
Categories: BI & Warehousing

Exalytics - Now with 2.4 Tb of Flash

Look Smarter Than You Are - Mon, 2013-05-13 22:26
I'm not sure why there wasn't a major announcement about this, but as of April 9, customers buying an Exalytics machine to speed up their Oracle Business Intelligence can get 2.4 Tb of PCIe flash drives from Oracle certified and engineered to run on Exalytics.  The cost (as of April 9's price list) is $35,000 (search for "flash upgrade kit").

While I haven't seen one in action yet, the flash pack seems to be 6 Sun Flash Accelerator F40 PCIe Cards each of which has a capacity of 400 Gb.  These cards run amazingly fast with read times of more than 2 GB/second (write time is about half that speed at 1+ GB/second).  These cards normally sell for almost $6K each, so Oracle is providing the flash add-on pack for no more markup than you'd get if you bought them on your own (but you'd then have to get them into the Exalytics machine all on your own).
This Matters If You Own EssbaseWhy would you want this?  Essbase, primarily.  Essbase uses a ton of disk I/O and one of the ways Exalytics can speed up Essbase is by pulling your cubes into a RAMDisk (since you have 1 Tb of RAM to play with).  At some point, though, it has to get that data from physical drives to a RAMDisk (unless you're building all your cubes at start up in memory each time).  Having blazingly speedy flash drives with .25 millisecond read latency allows you to store your cubes on the flash drive and then pull into RAM much more quickly than reading from traditional drives.

We have tested Essbase running on flash drives and it helps everything (particularly minimizes the negative effects of fragmentation since seek time drops to basically nothing on flash).  For customers buying Exalytics primarily for Essbase, the Exalytics Flash Upgrade Kit should be strongly considered with every Exalytics purchase (and if you already own Exalytics, buy it to put on top).

OBIEE is much less affected by hard drives, so while it may help OBIEE, this really matters a lot more to Essbase customers.
Oracle EPM Fully Supported on ExalyticsSince we're on the subject of Exalytics, now that is out, all Oracle EPM/Hyperion components certified to run on Linux will run on Exalytics PS2.  These include:

  • Administration Services
  • Calculation Manager
  • EPM Workspace
  • Essbase Server
  • Essbase Studio Server
  • Financial Reporting
  • Interactive Reporting (32-bit only)
  • Oracle HTTP Server
  • Planning
  • Profitability and Cost Management
  • Production Reporting (32-bit only)
  • Provider Services
  • Reporting and Analysis Framework Services and Web Application 
  • Shared Services
  • Web Analysis
Categories: BI & Warehousing

BI Apps (BI Apps in ODI) is available in OTN

Dylan Wan - Thu, 2013-05-09 14:18
First Go to to DOWNLOADS -> MiddlewareYou will see Business Intelligence Applications under MiddlewareYou see both 10g and 11g. Click on Oracle Business Intelligence 11g downloads will see BI Apps here:Oracle Business Intelligence Applications (
Categories: BI & Warehousing

Hyperion 11.1.1.x Drops Off Full Support in July 2013

Look Smarter Than You Are - Mon, 2013-05-06 22:50
Someone recently told me that they just upgraded their Hyperion applications to  I asked them why they didn't go to 11.1.2.  They gave me the standard story about wanting to wait until the current version got stable (even though came out over 3 years ago in April of 2010).

What they didn't know (cause apparently it hasn't been communicated well) is that Hyperion 11.1.1.x support drops from Premier Support to Sustaining Support in July of 2013 (only two months from the time I'm writing this).  For anyone who doesn't know, Sustaining Support is equivalent to life support.  While Oracle's Lifetime Support Policy does say that you can stay on versions of Oracle's products indefinitely, they don't agree to fully support them.

At the Premier Support level (the one all products start on), you get all the support you'd expect.  When you call in, the support people help you find the bug, they patch it in the next release, you install it, and life goes on happily.  Also, as new versions of supporting products come out like new versions of Office, Windows, or your web browser, Premier Support will make sure the Oracle products work with these new versions.

Extended Support (if offered at all for your Oracle product) comes about 5 years after a product is released.  At this point, Oracle will still let you do all the Premier Support things, they'll just charge you a premium for doing so.  Extended Support will not be offered on Hyperion 11.1.1.x (there aren't enough customers to warrant it).

Sustaining Support (AKA "life support") allows you to call in to ask for support.  Oracle will help you with questions, look up your problems in their knowledgebase, and help you troubleshoot.  They won't patch anything, make versions available that are compatible with new releases of Windows, Office, etc., and in general do anything beyond the bare minimum required.  From Oracle's Lifetime Support Policy document from March 2013, here's what Sustaining Support doesn't do:
  • New updates, fixes, security alerts, data fixes, and critical patch updates
  • New tax, legal, and regulatory updates
  • New upgrade scripts
  • Certification with new third-party products/versions
  • Certification with new Oracle products
And Hyperion (Oracle EPM) 11.1.1.x falls to this level in July 2013. To point out the obvious, if you're not already planning an upgrade to 11.1.2.x, you should start now.  You really don't want to be on Sustaining Support for long.  Since just was released last week, I would probably hesitate going to that version until the first patch set update is released (probably this summer). If you're doing your upgrade before then, I would suggest going to (the current patch of

If you want to read the sunset dates (the dates they drop to Sustaining Support) for all the current releases, visit for the current Lifetime Support Policy.  Here's the one from March 2013 (scroll to page 22) for the Hyperion products:
Categories: BI & Warehousing

All the Cool New Features in Oracle EPM

Look Smarter Than You Are - Mon, 2013-04-29 23:16
Oracle EPM is out and there are some great features in it (Planning and Financial Reporting have my favorites which you'll see in a second). is not as impressive as or, though.  A lot of the products got new features in between releases like Smart View (which adds new features with every patch set), Strategic Finance, HFM (which got Tax Provisioning in February), and Financial Close Management instead of making those products wait until was officially released.

Below are the features I think are most interesting in  This is by no means an exhaustive list and I didn't include every product though I did hit all the ones with significant market share (and a few others including a shout-out to the long-forgotten "EPM Workspace").  All the new feature documents are on  If you see anything major I missed, let me know in the comments.

Oracle Essbase
  • Essbase didn't change much in and users are never going to see any of the changes.
  • There are several new BSO functions and calc script commands like @INTERSECT, @ISMBRUDA, @ISRANGENONEMPTY, @MEMBERAT, @RANGEFIRSTVAL, and @RANGELASTVAL.  My favorite is @CREATEBLOCK which, wait for it, creates a sparse block with all the dense combinations in it set to missing.  Yes, we finally have a specific command to create specific blocks after 20+ years of silly block creation workarounds.
  • There's a new calc script SET command called RUNTIMESUBVARS that let's you create substitution variables in the script that are passed to the script at run-time.  You can then pass values to these variables when you call the calc scripts from MaxL or the API.  There's a new optional argument (with runtimesubvars) to MaxL's "execute calculation" command that sends the run-time variables to the calc script as you call it.
  • There's a new Essbase.CFG setting called ESTIMATEDHASHSIZE that lets you specify (in millions) how many members should be loaded into memory from the outline.  It's meant to speed up massive dimension builds and outline edits.  There are two other new CFG settings as well: ENABLERTSVLOGGING (which logs the new run-time substitution variables) and UNICODEENABLE (which sets the server to unicode mode, basically).
  • Performance has been improved on ASO dimension builds (specifically with duplicate members), the JAPI, and most helpful for many people including Hyperion Planning users, @XREF has been improved in some cases as much as 40-times.
  • Data Mining is gone.  Many people didn't even know it was there, but it's been removed.
  • Speaking of gone, there is no Essbase Excel Add-In  Further, earlier versions of the Add-In that you may have (like are not supported against Essbase  It probably will work (the Add-In is wonderfully backwards and forwards compatible) but it's not supported.

Smart View
  • There were a lot of improvements to Smart View in which came out about a month ago, so there isn't much new in Smart View  Smart View is the new strategic Microsoft Office add-in for all the Business Analytics (BI and EPM) products, though, so that's why the newest release supported direct access to OBIEE  Hyperion Strategic Finance is also now fully supported in Smart View.
  • The only major improvement is actually in Hyperion Planning just exposed through Smart View.  There is a new "Planning Admin" extension that lets administrators update Planning metadata (and a few other things) from Smart View.  I'll say more about this under Planning.

Hyperion Planning
  • Essbase ASO databases can now be created as Planning Plan Types.  These databases will allow writeback provided it's at level-0.  This is stunning.  These ASO cubes can be displayed on Planning composite forms with Essbase BSO cubes just like current BSO cubes in multiple plan types can be combined on a single composite form.  For some reason, this release doesn't put security filters on the ASO cubes, so you have to access these cubes through Planning or through the Planning ADM driver in Hyperion Financial Reporting.  Still, ASO in Planning is a step in the right direction.  Oh, and you have to own full-use Essbase to create these ASO cubes; just a Planning license doesn't cut it.
  • You can create members on the fly (kinda) if they don't exist when business rules need them.  This may only work in modules and not custom plan types.  The documentation says only modules, but I admittedly haven't tried it on custom plan types yet.
  • Custom dimension hierarchies can now vary by plan type.
  • Task lists can now include "Copy Version" and "Job Console" as tasks.
  • There is a new Task List Dashboard (gives you an overview of tasks by user, due date, etc.) and a Task List Report Page (that exports to XLS and PDF).
  • Grid Scroll Preferences lets you control if all rows/columns in a form are retrieved at once or only when you scroll down.  This should improve performance for large forms (and not slow down the internet when we want to watch cat videos on YouTube).
  • Users can now control through user preferences if they want member names, aliases, or both.  This can be set to override the form settings.
  • Outline Load Utility has been enhanced to do fun things like export metadata to a relational database or export data to a text file.  There's a new user interface for loading dimensions and data from flat files (and exporting them too).  This was previously only doable through the command line.
  • There's a new "Plan Type Editor" that lets admins add and delete plan types to already existing applications. Previously, this required going back to the initial creation step or hacking the underlying tables.  The Plan Type Editor can be used to add ASO to an existing Planning application.
  • Admins can now do some administration of Planning from within Smart View like editing dimensions, creating cubes, and refreshing cubes.
  • Workforce and CapEx have been enhanced to get them up to speed with Project Financial Planning's improvements to these modules in  You won't get these improvements with a straight migration: you have to create a new "shell" application and manually migrate your old dimensions into the new application.
  • You can create, assign, and delete substitution variables directly from the Planning web interface.

Data Relationship Management
  • There's an entirely new module within DRM (no word yet on if it costs money, but my gut feeling is that it's included with DRM) called "Data Relationship Governance."  To oversimplify what is actually quite cool, it adds workflow to DRM.  It lets data stewards coordinate entry, validation, and approval of hierarchies but it does a lot more too.  Users can request hierarchy changes and they go into a worklist so nothing gets lost.  It has built-in alerts for when users have requested changes and it also sends e-mails when something has been sent to you or it's something you should be informed of.  In my humble opinion, this is the greatest improvement to DRM since it was created.
  • Dynamic scripting lets you use JavaScript instead of formulas to create derived properties and validations.  This is a welcome improvement over formulas since we now have access to a real programming language.
  • You can now connect directly to external relational database tables to import hierarchies.

Hyperion Financial Management (HFM)
  • There's a new module (that technically was released in February) called Hyperion Tax Provision.  It handles tax automation, data collection, tax calculations, reporting and it does it all within HFM.  Now when you create an application in HFM, you tell it if you want a Consolidation (traditional) application type or Tax Provision.
  • Data forms let you show/hide the POV members, access the dynamic POV member lists, and run "on-demand rules" to essentially calculate the data form (by running the on-demand rule on a subset of data).
  • Data grids also let you control which POV dimensions you're showing and gives you access to dynamic POV member lists.
  • Admins can turn off modules for all users if they aren't applicable (for instance, if you don't allow intercompanies in HFM).

Financial Data Quality Management (FDM)
  • FDM and ERPi (ERP Integrator) combine in into a single product: FDMEE (Financial Data Quality Management Enterprise Edition).  It's more than just ERPi renamed which is what some are claiming.  Below are some of the improvements.
  • The UI (user interface) is now consistent with the other Hyperion products like Planning and HFM.
  • FDM is fully supported in Shared Services and Lifecycle Management.
  • Data loads are sped up.  Scaling and load balancing are fully supported.
  • ERPi users will now see full FDM functionality that they couldn't access before.
  • SAP BW is now supported directly as a data source.
  • FDM and ERPi owners have access (at no charge) to all the new FDMEE capabilities.

Financial Reporting
  • I know what you're thinking and yes, Financial Reporting actually got improved in (it is a strategic product for Oracle). So for the coolest thing you'll see since ASO in Hyperion Planning...
  • Financial Reporting is now mobile.  That's right: it runs on Apple iPhones, iPads, Android phones and Android tablets.  Users can browse the repository, launch reports (HTML or PDF), run books (HTML or PDF), change POVs, change page members, expand rows and columns, and even launch related content.  Pick your jaw off the floor.
  • Books have been enhanced to allow cover pages before the table of contents, embedding (and launching) Word DOCs from an HTML book, and changing the starting page number of books.
  • The designer has new authoring features including a row property to show dots after the member name (to fill the column) in PDF output, custom text colors, and auto-sizing text boxes in PDF output.
  • Annotations have a new auditing capability that puts annotation info (like creation date or modified date) in a log file on the server.

EPM Workspace
  • OBIEE is back in Workspace (it used to be there back in OBIEE 10).  You can both create and launch OBIEE from Workspace.
  • OBIEE uses single-sign on if you're logged into Workspace (OBIEE now can also share a security model with Essbase).

Web Analysis, Interactive Reporting, SQR Production Reporting...
  • Seriously?  People, move to OBIEE.  These are dead products.

General Comments
  • You can upgrade to directly only from and 11.1.2.x.  Earlier versions require going to either or first.
  • still doesn't officially support Chrome or Safari.  The documentation says that Oracle recommends IE (Internet Explorer) 9 or Firefox 10 because older versions are slower.
  • The EPM documentation is now fully supported on Apple mobile devices (in ePub format).  It already worked on Amazon Kindles (in Mobi format).
That's all folks.  You can thank me at Kscope13 for making it so you don't have to scour all the readme files yourself.

Oh, that reminds me. Since you read this entire essay of bullets, I owe you a reward.  When you register for Kscope13 (the best Oracle BI, EPM, and Hyperion conference in the world bar none), mention promo code IRC.  It'll save you $100 off whatever the prevailing rate is.  You can tell your friends or make them read this blog to find that out themselves.
Categories: BI & Warehousing

EPM Available for Download

Tim Tow - Mon, 2013-04-29 11:22
Oracle Enterprise Performance Management became available over the weekend, so let the rush of upgrading begin!

Here at Applied OLAP, we have already started our work with Essbase  We have added the new version to our build list and have started testing Dodeca with Essbase  We will make the version of our server available to our customers within a few days.

One interesting fix that we have seen so far is that EPM uses an upgraded version of Java, Java 1.6 update 35.   The previous EPM version,, shipped with Java 1.6 update 29 which contained a bug related to the SQL Server JDBC Driver.  The issue, known as the BEAST vulnerability, caused SQL Server JDBC connections to hang unless you made a fix to the Windows registry.  I, for one, am glad that issue is resolved.

Categories: BI & Warehousing


Chet Justice - Thu, 2013-04-18 21:00
I always find myself putting an asterisk (if only mentally) next to certain statements. I shall now put all those statements here and link back.
  1. I don't know everything
  2. I'm not the best developer in the world, but I constantly work at getting better...
  3. If I make a statement about something, that's been my experience. Your results may vary.
  4. I am not a salesman.
  5. I do not work for <insert company name which I just pitched here>
That's it...for now.
Categories: BI & Warehousing


Subscribe to Oracle FAQ aggregator - BI &amp; Warehousing