New With OBIEE 11.1.1.7 and SampleApp v305 - Essbase Cube Spin-Off
Now that the new v305 OBIEE SampleApp that we previewed in May is out, I thought it'd be a good time to take a look at an experimental feature added into OBIEE 11.1.1.7's logical SQL that gives you the ability to create, or "spin-off", an entire Essbase cube from an OBIEE dashboard or RPD.
If you read my coverage of the new Essbase integration features in OBIEE 11.1.1.6.2 just over a year ago, you'll remember that this release gave OBIEE the ability to persist aggregates to an Essbase ASO cube as well as regular Oracle, SQL Server or IBM DB/2 databases. This was impressive stuff but it came with one significant limitation: the Essbase ASO databases that the aggregate persistence wizard created contained just a single aggregation, for example sales by product category, month and customer type, rather than all levels and aggregations as you'd expect for a multi-dimensional database. The reason for this was obvious - this is how the aggregate persistence wizard worked with relational databases, and Oracle just took the same paradigm but allowed it to persist to Essbase as well as the various relational stores.
So fast-forward now to OBIEE 11.1.1.7, and the new v305 SampleApp has an intriguing dashboard page within the "8.21 Oracle Essbase Interaction" dashboard called "Cube Spin Off". What this page demonstrates is a kind of preview of where OBIEE is going in the future, where it'll be easy for users to take data within a subject area, spin it off as an Essbase cube and then automatically report against it, enabling faster reporting and access to MDX functions, forecasts and so on. In this first iteration, put together by the SampleApp team to show off the new logical SQL "CREATE CUBE" command, we've got an example of a command that will spin-off the Essbase cube along with an analysis created against the result of that command.
To create, or "spin-off" an Essbase cube using this command, cut and paste the logical SQL displayed in the analysis description, then select Administration > Issue SQL, and then paste the logical SQL into the text box provided, like this:
Note how I've bumped-up the logging level to 3, so I can take a closer look at what the BI Server does when I press the Issue SQL button.
Looking at the logical SQL command itself, what's interesting is that it's referencing logical dimension levels from the RPD in the command, rather than logical tables as would have been the case with the aggregate persistence wizard. Looking at the SampleApp v305 RPD in question, you can see that there's more logical dimensions in total within this business model, so the logical SQL command can pick an arbitrary subset of the dimensions within the model, pick an arbitrary level to aggregate up from, and then pick one or more of the measures to create the cube definition.
Pressing the Issue SQL button, and then switching over to Essbase Administration Services (http://obieesample:9704/easconsole), you can see the Essbase database and application created by the CREATE CUBE logical SQL command.
Looking at the database (cube) definition itself, you can see that it's a BSO (Block Storage Option) database rather than the ASO type that the BI Administraton tool's Aggregate Persistence Wizard creates, and its also set up to allow duplicate member names across dimensions and levels, something that's usually required when building cubes against relational sources that don't have this member name restriction.
Looking back at the NQQuery.log file entries create by the logical SQL command, all you can see is the CREATE CUBE statement itself, like this:
[2013-06-12T05:07:09.000-04:00] [OracleBIServerComponent] [TRACE:3] [USER-0] [] [ecid: 274d73ee2c4a9d00:42d39d4a:13f2e44ecb4:-8000-00000000000018c7] [tid: 2e497940] [requestid: 37b70014] [sessionid: 37b70000] [username: weblogic] ############################################## [[
-------------------- SQL Request, logical request hash:
c88166e2
set variable LOGLEVEL = 3;CREATE CUBE "ESSCUBE2" for "B - Sample Sales Exa".."Base Facts"
("2- Billed Quantity","11- Fixed Costs")
AT LEVELS (
"B - Sample Sales Exa".."Products"."Products Hierarchy"."Product",
"B - Sample Sales Exa".."Time"."Time Hierarchy"."Month",
"B - Sample Sales Exa".."Offices"."Offices Hierarchy"."Offices");
/* QUERY_SRC_CD='rawSQL' */
]]
All of the complexity of the cube creation is abstracted away by the BI Server, with (presumably) the embedded Essbase Studio servlet used by the Aggregate Persistance Wizard also being used to define the Essbase database and load data into it.
When you open up the BI Administration too and view the repository that provided the source for the CREATE CUBE statement, what's particularly impressive is that the new Essbase database has been mapped into the Physical layer of the repository, and then into the Business Model and Mapping layer as an LTS for the source logical tables, as the aggregate persistence wizard would do with regular, single-aggregation aggregate tables.
Very interesting stuff, and presumably at some point it'll be incorporated formally into the Aggregate Persistence Wizard UI rather than having to run it from a logical SQL script. I was warned also that this feature is "experimental", so expect there to be issues, limitations etc, but it's an interesting glimpse into what appears to be Oracle's original vision for data mart automation in OBIEE - just press a button and your collection of mapped-in sources becomes the design for an Essbase cube load, with the cube then taking the place of the original sources within the RPD model.
Finally and on a similar topic, you can read about the wider changes and improvements to Essbase integration in OBIEE 11.1.1.7 in my earlier post on this blog, as well as full details on the new SmartView integration in OBIEE 11.1.1.7 that also came along with this new release.