I just finished my latest set of benchmarks using OLAP 11g query rewrite to accelerate our data warehouse demo. I thought while I was at it I would see what hooking Essbase to Oracle OLAP would do. Since I already had a star schema alot of the work was done. I decided to use Essbase Integration Services (EIS) to model the warehouse and then build the essbase outlines and load the data. I wanted to test both loading a full Essbase database and also see how well Hybrid Analysis would work using the OLAP query rewrite functionality. This would allow me to also allow me to use the Essbase compatible front-end tools, such as excel add-in and visual explorer, to look at my warehouse data.

Setting up the Model and Metaoutline using the EIS tools was a little different than using Analytic Workspace Manager (for OLAP) but is easy enough to use. I first defined a standard ASO cube that would contain all the data. I was able to exactly match the design that I have in Oracle OLAP without any problems. Once I built the outline I set off to load the dimensions and the cube. To test the Query Rewrite I first turned rewrite OFF and performed my loads. Times were not very fast… AWM was faster! This may be due to ODBC vs OCI direct loads… but it was not horrible! Once loaded I ran the aggregation wizard to optimize the ASO database this took a while to perform. I then looked at the data in Excel. All my data was there and performance was excellent! So now I wanted to see if Rewrite affects the data loads… So I turned on Rewrite and rebuilt the database. Load performance was slightly better but the aggregation wizard was much faster! This is pretty much what I would expect.

Now for the big test–Hybrid Analysis–what what I came for. I went back into EIS and modified the metaoutline to have the last two levels of my Geography (which had 150,000 members) be in the hybrid (ROLAP) and the last level of my Product in the Hybrid. Rebuilt my database. The Essbase cube is now Tiny and loads in minutes! I turned off rewrite and went into Excel. Drilling at the upper levels was normal… drilling down geography became much slower–the deeper I went, the worse it got. I then did the same test with rewrite turned on. It made a significant difference! Performance using query rewrite was 5-10 times faster than without it. And I did not have to tweak anything or build materialized views or anything. So this looks this has promise! And now I can look at my data using some of the Essbase compatible tools!

So in general using Oracle OLAP 11g with Essbase is very promising! I can see where it has several value propostions. Anyone else out there doing this, please share your experiences.