OBIEE and Essbase a few observations

I’m sitting in a hotel room in Panama writing this post. Why you might ask. I’m in Panama as part of an Oracle Technology Network (OTN) Latin America tour. I will be going to 6 countries in 15 days.. Perhaps at the end I’ll blog in detail about it.  As I write, Edward Roske is in Brazil doing the same thing. Why am I writing this instead of out exploring? I will be later today as the local host arranged a tour for the visiting Oracle Ace Directors.

This post on OBIEE and Essbase is based on my creation of my KScope 14 presentation and quirks I noticed Note I was using Essbase 1112.3.5 and the latest patched version of OBIEE 11.1.1.7.1.X (I don’t remember the X). I was using a VM instance of the Sample Oracle supplies and was using Essbase Studio with it to create the cube. I then brought the cube back into OBIEE and also into Publisher to create a Mobile dashboard.

My initial problems came trying to install the BI Admin console on ly laptop. I run Windows 8 (not by choice) and IE 11. BI admin would not install no matter what I tried. I also had problems with Enterprise Manager and Analysis. I worked around these by using remote desktop to an environment that had lower versions but only after spending hours trying to make these applications work.

After I got things working I created a relational schema in an ORacle instance and tried to import the DLL and sample data from Essbase Studio. The tables created with no problems and created the keys and indexes as needed. When I tried to load the data, I got errors The import did not account for transforming dates into the correct format. I added cast statements to each row needing it and it worked fine, but I should not have had to do it.

Next, I brought in relational tables into OBIEE for creating my Essbase cube through Studio.  That was very easy until I started talking to Wayne Van Sluys a great OBIEE resource. He wanted me to create all sorts of logical tables and aggregations in my business mapping layer to turn my snowflake schema into a star schema and allow aggregation summaries.  If I had wanted to use the data source to do anything but feed Essbase Studio, (report from the source, do federated Essbase/relational reports) I would have had to go this work which seemed very daunting. Luckily, I only wanted to feed Essbase Studio, so it was easy. Just bring in the source, move it to the business model and presentation layers and I was done.

Next, in Essbase Studio, when I brought in the OBIEE tables, Studio did most of the work for me. In the Minischema, Studio would not let me do joins between the tables (This was defined in the relational source) but it would and needed me to set up the self join for the parent/child accounts table. It was easy-peasy (as Cameron Lackpour would say) to create the hierarchies and build the Essbase cube from this. Of course the sample tables were set up with Essbase in mind

I brought the completed cube back into the OBIEE RPD just because I could. I actually did not use it there. I just wanted to show for consistency of reporting how to do it. there were a few little quirks. I had multiple alias tables and although I only asked that the default table be used, When I got to the presentation layer all of the aliases showed up. I did remove them easily enough, but it was extra steps. Also for accounts,

I had set it up a a value dimension so I would not worry about if additional generations were added. In the presentation layer, I changed the ugly wording of “gen7,accounts” to “All Accounts” as it is more meaningful to the end users.

Working with Mobile App Designer (MAD)proved to be more of a challenge than I thought it would be based on the demos I’ve seen. It could be a distinct possibility that I don’t know enough about it, but the multiple steps were difficult. For those of you how don’t know, MAD bypasses the RPD and uses queries from BI Publisher.  My first issue was trying to create the MDX query. The Query designer gave me problem after problem. I finally bypassed it, wrote my query in EAS , tested it and pasted it into the the query editor in Publisher. I found I  had to do very simple queries as complex queries with multiple row or column members would not work in subsequent steps.

Once I had the query in place, I went into the actual query designer). It is a simple drag and drop interface and I got the basics pretty quickly. Because the input query, you are limited in what you can display. I will say I spent hours trying to get a single graph with three dimensions represented, Products, Periods and measures. I could never figure out how to do it, so I ended up with a graph for each quarter. It would have been more impressive if I did figure it out. I ended up going back to the MDX and making it simpler after trying multiple different things. Just before KScope, a new version of MAD came out, but I’ve not had a chance to see if it would have been solved my problems.

I know this is a long post without pictures, but I thought you might be interested in my ramblings about using OBIEE and Essbase for my demo. Using OBIEE against Essbase is much easier than using it against relational as I found out and I was able to complete the demo it a fairly short amount of time even if I did have issues. If you are going to go down this path, don’t travel alone, bring along a friend who knows OBIEE to help you with the obstacles along the way, you will be happier for it.