I'm currently sitting on a British Airways flight over to San Francisco, getting my demos and presentations sorted out for tomorrow. I've got an early start in the morning with an 8.30 slot as part of the IOUG Forum, doing a session on extending and customizing the Oracle BI Applications data warehouse. After that I've got another session later in the day, as part of the ODTUG Essbase Symposium, where I'll be showing off the integration that's now possible between OBIEE and Essbase 11.1, through the new Essbase Studio IDE. The rationale here is that an Essbase developer might want to take advantage of the data integration and query federation capabilities of OBIEE and load some data from the OBIEE semantic model into their Essbase cube, or alternatively an OBIEE developer might want to improve the query performance of an existing OBIEE business model by extracting the data out of OBIEE, loading it into Essbase and then re-mapping the business model to use the Essbase cube as it's data source rather than it's original relational table sources.
Assuming you've got some data in OBIEE that you'd like to bring into Essbase, the first step is to define a new data source in Essbase and point it to your OBIEE installation. Essbase Studio uses an HTTP connection over in my case port 9703, which is how it gets around the ODBC issue that Essbase Integration Services has that I blogged about earlier in the year. Once you define your connection you can select from any of the OBIEE presentation models that your OBIEE username and password grants you access to.
Once you create the data source connection, you can create a Schema Diagram that allows you to put in place the necessary table joins, as these won't come through from OBIEE.
Now that you've got the data source defined, the next step is to define your hierarchies, as you would do in the OLAP Model stage when using Essbase Integration Services. Unfortunately at least at this stage the dimensions you've set up in OBIEE don't come through to Essbase Studio, so you need to recreate them again before you can go much further.
You're now at the point where, in Integration Services, you'd have enough information to create your OLAP Model. In Essbase Studio, the equivalent step is to right-click on the metadata folder and select New > Cube Schema.
When the New Cube Schema comes up, you can select the particular set of regular, calendar and measure (account) hierarchies that will form your cube. In my case, I've got regular hierarchies for the customer, product, channel and times tables, and a measure hierarchy to contain the fact table measures.
I then select the option to create a new Essbase Model to go with the Cube Schema, and I'm now at the point where I can fine-tune my dimension member names, by right-clicking on the new model and selecting Essbase Properties.
The Essbase Properties page brings up a similar screen to the one you get when working with an OLAP Metaoutline in Integration Services, and lets you transform dimension member names, add aliases, set dimension aggregation rules and so on. I use this dialog to initially set my cube type to Aggregate Storage (as I still yet can't find out where the equivalent of "use default calc script" is in Essbase Studio, so this is the only way I can get my cube to aggregate properly), and then I transform the various "Total" columns in my dataset to make sure that their values are unique across the cube.
Once you've defined all your transformations, set all your aliases and so on, you can then verify your model and then use the Cube Deployment Wizard to create and load your Essbase cube.
The Cube Deployment Wizard brings up a screen similar to the Member and Data Load screen in Essbase Integration Services, except as I mentioned the various options around aggregate calculation have now gone (the "use default calc script" option). To get around this I defined my cube as using Aggregate Storage which automatically aggregates data on demand, but if anyone knows where this option is gone then let me know by adding a comment to this posting.
Once the cube is created you can then view the outline in Administration Services, like this:
You can also use the Preview function to view the data in the cube, to check that it's loaded and aggregated correctly.
Other than this, you can then bring the cube back in to OBIEE, so that for example you can replace an original relational data source for your logical model with a multi-dimensional one; you can add aliases to the dimension member names to pick up the descriptions in the logical model; you can add calculations, forecasts and so on using MDX (in the case of ASO cubes) and the Essbase Calculation Script for BSO cubes.
That's it for me now, we're landing in about half an hour, then it's off into San Francisco and the start of Open World week.