EIS Drill-through Issue With Stored Procedure

I worked with a user on the Essbase.org forum this week who was reporting an issue with EIS Drill-through. Specifically, the drill-through report used a stored procedure but the substitution tokens used to pass cell context information in EIS caused the procedure to throw an error. Here is the stored procedure:

exec essbase_detail_gldate_well $$COMPANY-VALUE$$, 'JAN 2008', 'W0077047', '0501.0005'

This format led to a confusing SQL error: Error -1: WHERE clause is missing in the SQL. Very strange. The stored procedure itself contained non-standard SQL and essentially passed the query over to the AS400 to do the actual query.

set @sql = "select * openquery(AS400PROD,''Select * from lib.file where glcono='+substring(@company,1,3)+ ' and glidno='+ substring(@well, 1,7)"

exec (sql)

Note that the user reported significant performance improvements when using this format and it worked when tested with hard-coded values instead of the EIS tokens, so it was important to try to make it work with the non-standard SQL.

I suggested that perhaps the issue was in parsing the SQL and that adding some conversion functions to properly type the token values being passed in may help. I don't exactly know how the user, Brian Howell, figured this out, but he tried putting a comment in the SQL that seems to have fooled EIS. It appears that EIS may look for the 'WHERE' keyword before starting to substitute tokens. In any case, here is the SQL that worked:

/* select * from where */

exec essbase_detail_gldate_well $$COMPANY-VALUE$$, 'JAN 2009', 'W0077407', '0501.0005'

Congratulations Brian on figuring that one out!

Gas Free Commuting Update

Another non-Hyperion thing (but I plan to post Hyperion stuff tomorrow)..

I have not been able to commute on my bike this week due to a shocking development. I was riding to work on Monday morning and felt some weird wobble coming from the back of my bike. I thought to myself "Hmmm.. Probably have wheel bearings going bad or something; maybe I will call my bike shop and ride gingerly down there at lunch so they can do a quick repair...". I rode somewhat carefully the remaining 3 miles through some neighborhoods to the office. At the office I trying to see if it was really the wheel bearing and I found the real issue:

I broke the frame on my mountain bike!!!

How? I have no clue! I was riding on a sidewalk at the time and didn't hit anything.. Amazing. So now I am in the market for another Trek 850 (19.5 inch frame if you have one you want to sell).. I may end up getting another frame under warranty but it won't be for the same old model I have. I probably will get another backup bike.

This has really pushed me over the edge as I am now regularly checking craigslist.org for the cities near me; it seems to be the place to buy a used bike. I also registered for an eBay account today as there may be new replacement frame available there.

My son that is at the University of Michigan always wants to ride with my wife and I when he is home but we didn't have enough bikes. Maybe now we will.

Kaleidoscope Hands-On Labs / Dare to OLAP

I have been working with some of the Oracle Domain Group on the hands-on labs for Kaleidoscope and have some good news to report. We have lined up Mike Nader, a good friend for many years now, to teach many of the labs on Essbase Studio. Some of you may remember Mike from his days as the Hyperion Product Manager for Smart View. The labs will be based on content from a program called Dare to OLAP which was created to teach Oracle partners about the new features of Essbase 11.

The labs will have 25 laptops setup to run each session with two people sharing a laptop for each lab. If you are interested in doing the hands-on labs, make sure you sign-up early for them; the key Essbase Studio labs will, however, be available twice.

Further, I have been working with my Oracle contacts to make the Dare to OLAP materials available on the Oracle Technology Network website and recently got approval for it to be posted there. We are working out the details of where it will presented, etc. We are also working on ways to make it easier for people who don't have Essbase 11.1.1 installed to try out the new version. How many people are interested in the Dare to OLAP materials?

ODTUG Kaleidoscope 2009 Discount Codes

ODTUG has offered discount codes to partners who are participating in the Kaleidoscope 2009 conference as a way to help their customers attend the conference. The codes, which entitle attendees to register for the Kaleidoscope conference at the ODTUG paid member early bird discount rate of $1225, are available from a number of Hyperion partners.

If you want to attend Kaleidoscope, have a Hyperion consulting partner working with you, and want to save some money, call your partner and get the code.

If you are a Hyperion partner and don't yet have a code, contact me and I will direct you to the ODTUG person who can get you your own code to give to your customers.

Finally, if you don't have a relationship with, or know any, other Hyperion partners, Applied OLAP has a code. Send me an email at timtow@appliedolap.com and I will email you our code.

See you in Monterey!

BI Challenge 2 Go

There has been much interest in Hyperion since the Oracle acquisition and many partners have been working to install Hyperion systems. Oracle has a program that can make it easier to learn the products. The program is called the Oracle Business Intelligence Challenge to Go program or, shortened BIC2G.

Oracle recently released virtual machine images to help people get started. The images were put together with the help of longtime Oracle Partner Dan Vlamis at Vlamis Software Solutions. I spoke with Dan about the program and he told me about one of the most difficult parts of creating the images on Windows. Apparently, Microsoft has a pretty good legal department so making sure the images had a proper Windows license took some effort. As a result, there are two different images types available.

First, you can get an image, with Oracle EPM preinstalled, that runs on Microsoft Virtual Server and has a 30-day Windows demo license. That means the image works for 30 days after you first log on, then it expires. You can reuse the 'base' image any number of times but each copy you fire up expires after the same 30 days. We are planning to use this image for the ODTUG Kaleidoscope hands-on labs.

Alternatively, you can get a VMWare image, again with Oracle EPM preinstalled, that has an OEM copy of Windows 2003 Server and does not expire. I believe you can only use this image *once*.

There is a small cost associated with the program but that basically covers costs most of which are Microsoft licensing fees. You can get more information on the BIC2G program at http://bic2g.oracle.com/ or at http://www.vlamis.com/blog.php/?p=87.

I am encouraging Oracle to make this image available to customers as well; perhaps it will be available in the future.

ODTUG Hyperion SIG LinkedIn Group

The ODTUG Hyperion Special Interest Group ("SIG") now has it's own group on LinkedIn. The group was created by longtime Arbor salesperson Suzanne Hoffman. Suzanne now heads up sales for industry veteran Quinlan Eddy at Star Analytics. You can join the group here.

For those of you who don't know about LinkedIn, it is a business networking site that is somewhat similar to Facebook (for serious people). OK, I am probably going to get slammed with comments on that statement; some serious people actually have Facebook accounts too. Enough said.

Cascade Views in Dodeca

There is a question up on the Network54 board about the availability of a cascade utility for Essbase that will copy charts along with the Essbase cascade. To tell you the truth, I didn't remember that the classic add-in functionality didn't allow you to copy charts, so I fired up good old Excel 2003 to try it out. First, I had to work around the bug where Essbase doesn't work with multiple instances of Excel running. I can't believe users put up with that bug!. Once the Excel add-in was running successfully I quickly found that the classic add-in doesn't copy charts even when you check the checkbox to copy formatting.

The post also reminded me of a time back in the the mid-90's when I was at Lex Software. Arbor Software hired us to to write an obscure utility to automate the generation of cascades. If I remember correctly, the utility wasn't carried forward into 32-bit Windows. Due to a change in how printing worked, it basically would have caused a significant rewrite. Note that I wasn't the primary developer on that utility as I was busy working on Essbase consulting gigs for our friends at Arbor.

Of course, I was pretty sure we could create cascades with charts in Dodeca so I went to the Dodeca admin screens, exported the Excel template for our sample cascaded Income Statement view, added a chart, saved the file, re-imported the file and committed/saved it to my Dodeca server. As expected, it worked fine:

(click on the graphic to see a larger version)

When we generate a cascade view, we literally make a copy of the base worksheet including all of the formulas, formatting, range names and objects including charts, so despite the fact that I had not tried a cascade with a chart in Dodeca previously, I was confident it would work.

Cascade functionality is controlled by the administrator in Dodeca. Essentially, any Excel Essbase view may be cascaded with the administrator controlling which dimensions are to be cascaded. Further, the user typically gets to choose from members anywhere in the outline for the cascade and may, optionally, have a summary sheet generated that automatically generates subtotals for the selected members. Here is a screenshot of the Cascade category properties in the Dodeca View Metadata Editor, which is used by an administrator to turn on cascade functionality:



The other thing that always bugged me in the Excel add-in cascade functionality is the annoying numbering of the worksheet (or workbook) names. When I spec'ed the cascade functionality in Dodeca, I made sure that annoyance was fixed.

Oh, and about that other annoyance. The one with the multiple Excel instances being open and causing the Excel add-in to just not work... Dodeca doesn't have that problem either.