Oracle Business Intelligence 11g has finally gotten going with a whirlwind release tour that started in early April 2010.  Today’s launch party takes place in one of the largest business cities in world, New York, NY USA.

After much beta testing, and hush-hush, behind the doors secrecy, this clandestine tool can now be seen via a myriad of marketing paraphernalia online as presented by Oracle.  There are a lot of great resources, break-out session videos, keynotes, etc provided on the Oracle site.  One of my favorites is a side link at http://ht.ly/2bADM which provides some great webcasts on the topic.

Get ready to see some great in-depth detail  on ArtOfBI.com.  We are stoked about the EPM/BI integration that now doesn’t have the limitations that 10g had.  Everything is better in this version as one could only expect from the amount of delay and anticipation this release has brought upon the Oracle BI communities.  We are looking forward to sharing more detail on the topic.

Take a look at some heads up resources from Oracle, et al.  I think you’ll find most of the initial collateral most exciting.

• http://www.oracle.com/us/corporate/press/143363
• http://www.oracle.com/oms/businessintelligence11g/index.html
• http://www.oracle.com/oms/businessintelligence11g/business-insight-075567.html (Demo Videos)
• http://blogs.oracle.com/robreynolds/2010/07/reminder_obiee_11g_north_ameri.html
• BICG Twitter Live from OBIEE 11g Launch

The latest version of the Oracle BI Applications, 7.9.6 and 7.9.6.1 have not been out for that long.  That goes as well for the newest version of the DAC clearly. And, perhaps it is that this release is so new and there are just enough changes that several bugs still got out in this release.  Although these bugs are subtle and minor, they will pop-up on you in the damnedest of places, just when you thought you were sailing along swimmingly.

Now, if you read the systems requirements documentation word-for-word upon each and every OBI Apps release, no matter how minor the release, you may not need this information.  But, if you are human, you may have missed the tiny section on page 12 of the related release document, http://download.oracle.com/docs/cd/E14847_01/bia.796/e14221.pdf. This page briefly highlights the fact that a DAC patch, 8760212, is required for this release of OBI Apps. It’s a DAC patch.
Continue Reading

OBIEE has a smart query logging system and it happens to be mainly based on security logging levels.  This is great from an overhead perspective because you wouldn’t want every single user of the system to have a full introspective query log produced each and every time your hundreds of users run a query requests or dashboard view hit. 

Typically one will use logging during development, analysis, or troubleshooting efforts.  Otherwise there is no need to have logging on any higher than the default setting.  By default each user account’s “Logging Level” is set to 0 (zero) which is basically no logging.  For basic purposes of analyzing SQL or MDX syntax that make up the query, row counts, and recordset deliver time you will want to change the “Logging Level” to a 1 or 2.  These two levels start providing basic logging which can be seen from Settings > My Account > Manage Session in Answers.

Here’s how to change the “Logging Level” for a specific account:
Continue Reading

By default hiearchy drill-down links are removed when an OBIEE Delivers IBot notification is sent out via email.  The report becomes for the most part static in the email whereas if you viewed the report in the interactive Dashboard or in Answers you would by default have the link action associated with it.

The main reason that the drill-down hieararchy link is disabled in an email notification is because OBIEE uses relative paths to know which report to link to or which path to drill-down on.  With the addition of JavaScript, especially for the drill-down capability, it just cause errors when a user click on this time of linkage from an email.  Relative paths are only viable when they are on the same web root as the page launching the link.  Clearly an link in an email falls outside of that scope.  It would be like someone sending you a link to a product the found on Amazon but the only link they sent you in an email is “/product/hasbro-gi-joe-tunnel-rat”  instead of http://www.amazon.com/product/hasbro-gi-joe-tunnel-rat.  For more information on Relative Paths, click here.

OBIEE Delivers Email Inbox
Here is the default behavior of a simple answers report. The left-most column is modeled with a hiearchy and drill-down is enabled when viewed in Answers/Dashboard.

  Continue Reading

Every software tool has its own language and nomenclature that comes along with it.  Most times that language it is plain, makes sense, and ultimately is accepted by the masses. However, sometimes it can be rather esoteric.  Again, this is great for us consultants because we can often appear even smarter than we already are by throwing out some big buzz words that grab our audiences’ attention.

To make a long story short, I was recently asked about integrating Hyperion Essbase with OBIEE for drill-through functionality.  In that conversation I faintly heard a business user mention something about “federating OBIEE”.  With my e-commerce background I immediately thought of federated servers.  I had implemented that infrastructure in the past for high-availability so I know if the faint discussion turned into a key discussion I would have no problem speaking to it.  Unfortunately that mention remained a non-topic.  However, later I thought to myself that that mention of “federating”  could have also been interpreted as a principal technique used in data-warehousing from years past. Anyway…

Just recently I came across a great web tutorial on Oracle by Example that talks about “Federating Essbase and Relational Data Sources in OBIEE”.  At this point, we’ve been doing the integration shown in this tutorial for a good while so this is nothing new to me.  But, there was that buzz word - “Federating”.  After reading the first paragraph of the document I extrapolated that Oracle is basically referring to “federation” as the combination of data sources (seemingly disparate heterogeneous data sources only) through OBIEE modeling.   Furthermore, they have broken the term down into two components, horizontal and vertical.  Here is a brief summary of the two:

• Horizontal Federation
  • Integrating two or more disparate data source having the same level of granularity joined by one or more conformed dimensions
  • Example: Essbase Sales cube with HR relational database
• Vertical Federation
  • Integrating two or more disparate data source having different levels of granularity joined by one or more conformed dimensions
  • Example: Essbase Sales cube to relational Sales detail data (drill-through)

Here is a link to the Oracle by Example tutorial: Federating Essbase and Relational Data Sources in Oracle Business Intelligence Suite Enterprise Edition Plus

I’ll be talking to our Oracle Sales reps here in the next few days to see if this term “Federating, Federation, or Federated” is something they are using going forward for this type of integration in the Oracle Roadmap.  I actually like the use of the term.  Let’s see if it will stick or disappear into the cosmos like so many terms in IT that have come before it.

Let me know what you think.

ASO does not provide an out-of-the-box version of dynamic-time-series (DTS) like its partner in crime, BSO. However, one straightforward approach to achieving this functionality is to implement a time aggregation dimension and leverage MDX functionality.  In this example we will house the Time Periods/Months (i.e.:  Mar, Apr, May, etc.) and the Years dimensions into two separate dimensions.  We then add an additional dimension called “Time Series” to the outline to support our ASO version of DTS.

Continue Reading

I was looking to create a formula that required averaging based on the number of week in the given month. As an example Monthly Sales Total / # Weeks In Month.  Since this is a fiscal calendar the weeks are usually standardized and shouldn’t change that often.  So, if the fiscal calendar is on a 4-4-5 or 4-5-4 the logic is rather straightfoward.

I couldn’t make changes to the underlying Calendar dimension table and I couldn’t recall how to do this programmatically in OBI so I went with what I will call a quick prototyping hack.  Here it is below.

CASE  WHEN “Indirect Sales”.DIM_TIME.MONTH_NUMBER IN (3, 6, 9, 12) THEN 5 ELSE 4 END

This worked out perfectly as a place holder as we can see by a quick ad-hoc look at the new calculated measure.

If anyone has the right or clever way of doing this leave a comment.

Reviewing some disconnected solutions I came across CodeRun.com.  CodeRun.com is basically a web-based development environment that let’s you write,test, and debug code online - in the cloud so to speak.

And, it’s no joke, this is a powerful interface.  It let’s you, with syntax highlighting, write the most popular code syntax out there including ASP.NET, PHP, AJAX, Java, etc.   This is clearly not enterprise-level team based software but if you need to transfer code, test a quick snippet out, or are looking to train users in a classroom setting this is really functional.

The more tools that I have in my arsenal the better versed and prepared I am. So, I’ll keep this in my favorites regardless.

Just thought I’d share.

Developing and testing I use the windows command prompt quite a bit.

Often during testing my binaries and executables build folder is down a path off of the base root about five or more directories deep.  In the command prompt I have to run the common commands to CD (change directory), etc. before I land on the directory where I can then call my test programs.  The main problem with that is that my command prompt window often wraps around or just looks so busy that it irritates me.

I recently ran into a really cool solution that allows you to quickly shorten the command prompt entry line using the SUBST function.  It basically take a couple of parameters and maps an open drive letter for you based on the directory path that you supply.  The below is an example:

subst R:  “C:\development\tools\programming\testing\project1\code\tsf\runnable\path\node\bin\”

Simply take the above example’s syntax and modify it for your next testing episode.  The quotes around the directory path do not always need to be there, however, if you have a folder in that path that contains a space you will definitely need to use the double-quotes.

In the above example simply type SUBST and for the two parameters enter an open drive letter and then the directory path you wish to use.  Hit enter.

The cmd prompt will return cleanly.

Type R: and hit the return key.

Your prompt should return with the R:\ drive prompt and you can run your code from there.

Good Luck.

If you haven’t had a need for utilizing Oracle Data Integrator (ODI) as an ETL (or E-LT source as ODI advertises itself) then you wouldn’t know that it is actually somewhat of a beast of a ETL tool. The interface leaves a lot to be desired and I could really get picky but again it is a beast - very powerful. I recently read a great review by the good folks at BI Quotient about ODI. They did a 3 part series on the topic and really covered some areas not mentioned in most ODI write-ups.  I consider myself well versed in ODI but these guys threw in some great ancillary discussion with links to awesome tools such as PolePOS and SQuirrel SQL that I did not know about. And, we know that I am a big fan of open-source software so this was a great discovery for me.

Okay, well the article by Uli Bethke might not be the “Best ODI Review…” but it is really solid.  I like how he keeps HyperSQL at the forefront of the article.  Since HyperSQL (HSQLDB) is really at the core of ODI you know that ODI has the potential to be powerful as an ETL tool.  As you know, HyperSQL is at the core of another very well known software suite, OpenOffice.org so clearly it works pretty well.

The article introduced me to PolePOS which a database benchmarking tool.  I’ve yet to get it a go but you can see the possibilities there, especially since it is free.  Could this be used against OBIEE as a source?

Lastly, in part 2 of the article Uli takes an investigative look at the in-memory synopsis memory engine of ODI against one of the Microsoft SysInternals tools to see just how severely the server’s memory gets consumed as an ODI process executes.  For some this is not a common-sense benchmarking approach but subtly suggest strong performance tuning technique when working with such an application.

Just to wrap it up, no the article did not provide an immediate tutorial or a step-by-step solution.  John Goodwin and some others have some killer articles like that.  However, this one seemed to be one of the first ODI articles that I’ve read that touched on the tool as a part of whole not just the whole itself.  Give it read if you have the chance.

References:

http://www.business-intelligence-quotient.com/?p=703

http://hsqldb.org/

http://www.polepos.org/