How not to reverse engineer an Essbase cube to allow drill through

It has been awhile since I’ve posted. No apologies but I was busy getting ready for KScop13 (which was a great conference. Sorry if you missed it). Then I was a bit burned out and needed time to recover.  In this post, I’m going to take a little from my KScope presentation of Advanced Studio Tips and Tricks to hopefully help you. This will deal with reverse engineering a cube to get drill through functionality.

First why would you want to reverse engineer a cube?

• Cube already exists
• Want to add drill through capabilities
• Want to start migrating to Studio
• Want to have hierarchies available for building other cubes

So you can learn from my mistakes, I’ll discuss the wrong way to try to reverse engineer a cube.  I had a client that wanted to do this. I had recommended extracting the hierarchies from their existing cube and loading it into dimension tables. They wanted to try a different route.  Their target table had all of the level zero dimension members in the fact table. They wanted to see if we could just build the level zero members (since they would only drill through at level zero) from the fact table. The actual data load would be done outside of Studio, so there would be no change in that.

The first thing I tried was to create user defined tables that made fake dimensions tables. I used dummy parent names so it looked like a parent/child build.  I created the hierarchies and the Essbase model. In the Model properties, I told it to ignore shared members so they would not build the new relationship.

I encountered my first problem. I could not create the custom SQL in the drill through report. I fixed this problem by making the fake dimension hierarchies recursive.

Then I ran into my second problem I had a dimension (Scenario) that I created as a manually defined hierarchy. The deploy would not refresh the Essbase cube. So thinking swiftly,  I created it as a user defined table and joined to that “table”. That solved that issue (Or at least I thought it did)

This change  did allow me to deploy the cube, but I could not get the drill through intersections to work in my existing test cube. If I built a new cube with the dummy intersections, the drill through report would work. I figured out it was because of the "ignore share members” It was not actually creating the intersections in the cube it just ignored what I was trying to build.

Bummer. What this meant was I could not build the cube from just level zero members. I would need to have at least level 0 and level 1 members to build the dimension tables.

I reminded the client of my original suggestion on how to reverse engineer. They decided to take my suggestion. Basically we extract all the dimensions using the outline extractor from Applied Olap (you could also do it with ODI or other ways) then load the dimensions into the same database as the fact table exists in.

Once they are there, we can do our joins and normal Essbase Studio steps to update the cube and drill through reports.

This is going to be a busy rest of the year from me. I’ve already spoken at a Hyperion Solutions roadshow with Oracle (losing my voice before the final of my 4 presentations). I’m scheduled to speak at the ODTUG sponsored Sunday Symposium at Oracle Open World, Attend Oracle Ace Director meetings in Redwood city, and speak at 4-5 other events in the remainder of the year. This is in addition to trying to do real work. While I love to share information with you all,  my first love is being a technical resource and solving problems in the Essbase/Hyperion world. I do as much of that as I can. 

I’ll try to be more frequent in my blog posts. I think I might share more from my Studio tips and tricks next or perhaps some things from my Thinking outside the box optimizations session from Kscope. That session almost allowed me to beat out Edward Roske for best conference speaker. I bare no hard feelings toward Edward, He deserved to win the award, but I gave him a run for the money (ok wooden kaleidoscope).  Funny both our presentations were on optimization.

Till next time