e OBIEE Hybrid OLAP Reporting using MS Analysis Services & Oracle

One of our clients called me in today to run an OBIEE workshop. They have a number of Microsoft Analysis Services cubes that they’d like to report on using OBIEE, and if possible they’d like to combine this summary-level data with some more detailed data in an Oracle Database. I thought it was quite an interesting question and so I’ve put together a generic solution based around the Global Sample Schema.

In this scenario, an Analysis Services 2000 cube has sales data dimensioned by product, customer, time and channel. The data comes in from the Oracle database at product item, customer ship to, sales channel and month level, and then gets aggregated up from the product family, customer warehouse, sales channel and quarter level, i.e. its dimensions do not go down to as detail a level as the source Oracle data.

I put the cube together using the Analysis Services Analysis Manager and mapped it to my Oracle data source.

Once I’d processed the cube, I used the Browse Cube feature to check that it’d aggregated OK.

So far so good. Now it’s a case of getting it connected to OBIEE. Microsoft Analysis Services 2000, like MSAS 2005, Oracle Hyperion Essbase and SAP B/W connects to OBIEE through XML/A, an XML-based standard for accessing OLAP cubes via HTTP/HTTPS. Getting XML/A working with Microsoft Analysis Services 2000 is a bit tricky as you’ve got to download the Microsoft XML For Analysis SDK from Microsoft Technet, upgrade Analysis Services to Service Pack 2 or higher, and install Internet Information Services, but once you’re up and running you can start up Oracle BI Administration and connect to your cube.

From the BI Administrator application menu, select File > Import > From Multidimensional, and then select Analysis Services 2000 from the Provider Type drop-down menu. In the URL, enter the address of the virtual server where you’ve installed the XMLA/A DLL, for me I’d named my virtual directory “xmla” and so it was “http://localhost/xmla/msxisapi.dll”.

Once you’ve connected, Administrator shows a list of the available cubes, and I selected the Global cube I’d created earlier and pressed the Import button.

Administrator then parses the XML/A description of the cube and creates a physical model of it in the OBIEE semantic layer.

So now I’m in the situation where I’ve got two sets of Global Electronics data in my physical layer; one that’s from the original set of Oracle tables (this was already in my OBIEE repository) and this new one that points to my summarized, Analysis Services cube. What I want to do now is create a single logical model that includes the summary data from the Analysis Services cube, and the detail-level data from the Oracle database, with OBIEE switching between the two data sources as appropriate. Later on, I’d like to look at having an additional measure, say “Forecast”, in my Analysis Services cube and have that added into the logical model as well, but for now I’ll keep it simple.

Normally, if I was going to build a logical model that incorporated detail-level data and some additional summaries via a second logical table source, I’d start by modeling the detail-level data first. However, as the easiest way to convert the mult-dimensional Analysis Services physical model to a corresponding logical, relational model is to have BI Administrator do it
for me, I start the process of creating my logical model by dragging and dropping the Analysis Service cube from the physical layer to the logical layer, whereapon BI Administrator creates a corresponding logical business model. Notice how we’ve got a logical table and dimension for each Analysis Services dimension, and another logical table for the cube measures – notice also
that none of the dimension tables have IDs either, we’re just left with the dimension members/levels and the measures.

Now that I’ve got a relational representation of the Analysis Services cube, I could just drag it over to the presentation layer and start reporting against it using Answers. What I want to do though is introduce the detail-level data from the Oracle database into the logical model, and I do this by dragging and dropping the UNITS_FACT table from the Global Sample Schema
physical model on top of the Units Cube logical table. This adds a second logical table source to the logical table, adds four new logical columns for the fact table foreign keys, and adds a second logical source to the Units logical column.

Now that I’ve mapped the second fact table source, I can start doing the same for the dimensions. I drag and drop the CHANNEL_DIM table from the Oracle physical source and drop it on top of the Channel dimension logical table.

As the column names coming in from Oracle are different to the logical column names derived from the Analysis Services cube, BI Administrator has added them as new columns. What I need to do now is map the original logical columns correctly to the Oracle physical source, and then I can remove these columns. I do this by double-clicking on one of the original logical columns and selecting the Data Type tab.

I then click on the CHANNEL_DIM logical table source, which represents the Oracle physical table, and double-click on it to assign its column mapping correctly.

I then use the Column Mapping dialog to correctly map each logical column to its physical source. I do this by clicking on the ellipses (…) button next to the un-mapped columns and then use the expression builder to select the correct column in the physical table source.

Once this is done, I end up with all of my logical columns properly mapped to the Oracle data source, including the ones that were present in the Analysis Services model and that didn’t have corresponding names in the Oracle model.

I’ve now finished mapping the relational channel dimension into my logical model.

I then do the same for the CUSTOMER_DIM, PRODUCT_DIM and TIME_DIM tables, and of course for products and times I’m going to have new columns coming across from Oracle that don’t exist in the model already, which represent the Month and Item levels that were aggregated out of the Analysis Service cube.

After mapping in the columns, there’s a couple of more steps to perform as we’ve now got more detailed data in our logical model. The product and time dimensions, which currently go down to family, warehouse and quarter level only, can now be extended down one more level and the new logical columns, which came in via the Oracle import, can be mapped to them, like this:

I also need to include the Item ID column, warehouse ID column and the Month ID column, in their respective logical table keys, like this:

Finally, I need to add the new detail-level columns to the presentation layer, so that they’re visible in Answers.

Now I’m ready to report on the data using Answers. I start off with a query that should be satisfied from Analysis Services.

OK, that looks good. Taking a look in the query log, we can see the MDX being issued to Analysis Services.

-------------------- Sending query to database named Global MSAS 2000 (id: <<95907>>):
With

  member [Measures].[Total Channel DscAnc] as 'ancestor([Channel Dim].Currentmember,[Channel

Dim].[Total Channel Dsc]).name'
  member [Measures].[Total Product DscAnc] as 'ancestor([Product Dim].Currentmember,[Product

Dim].[Total Product Dsc]).name'
  member [Measures].[Class DscAnc] as 'ancestor([Product Dim].Currentmember,[Product

Dim].[Class Dsc]).name'
  member [Measures].[Total Years DscAnc] as 'ancestor([Time Dim].Currentmember,[Time

Dim].[Total Years Dsc]).name'
  member [Measures].[Year DscAnc] as 'ancestor([Time Dim].Currentmember,[Time Dim].[Year

Dsc]).name'
    set [Channel Dim1]  as '{[Channel Dim].[Total Channel Dsc].members}'
  set [Product Dim2]  as '{[Product Dim].[Class Dsc].members}'
  set [Time Dim2]  as '{[Time Dim].[Year Dsc].members}'
set [Q] as 'nonemptycrossjoin({[Channel Dim1]},{[Product Dim2]},{[Time Dim2]})'
  member [Measures].[MS1] as '[Measures].[Units]'

select
  {[measures].[Total Product DscAnc],[measures].[Total Years DscAnc],[Measures].[MS1]} on columns,
  {[Q]} on rows
from
  [Units Cube]

Drilling down on the report to months, the results come back OK (if a little slower)

Again taking a look at the query log, we can see an SQL statement being issued this time.

select T37973.YEAR_DSC as c1,
     T37973.QUARTER_DSC as c2,
     T37973.MONTH_DSC as c3,
     T37973.TOTAL_YEARS_DSC as c4,
     T37912.TOTAL_CHANNEL_DSC as c5,
     T37952.CLASS_DSC as c6,
     sum(T38013.UNITS) as c7,
     T37952.TOTAL_PRODUCT_DSC as c8,
     T37973.MONTH_ID as c9
from
     GLOBAL.TIME_DIM T37973,
     GLOBAL.PRODUCT_DIM T37952,
     GLOBAL.CHANNEL_DIM T37912,
     GLOBAL.UNITS_FACT T38013
where  ( T37912.CHANNEL_ID = T38013.CHANNEL_ID and T37952.ITEM_ID = T38013.ITEM_ID and T37973.MONTH_ID = T38013.MONTH_ID and T37973.QUARTER_DSC = 'Q2-98' and T37973.YEAR_DSC = '1998'

)
group by T37912.TOTAL_CHANNEL_DSC, T37952.CLASS_DSC, T37952.TOTAL_PRODUCT_DSC, T37973.MONTH_ID,
T37973.MONTH_DSC, T37973.QUARTER_DSC, T37973.YEAR_DSC, T37973.TOTAL_YEARS_DSC
order by c1, c2, c3, c4, c5, c6, c8, c9

So there we go. The Oracle detail-level source is being used for detail-level queries, whilst the Analysis Services, aggregated source is being used for queries at a higher level of summarization, which OBIEE does because it knew where in the various dimensions the OLAP data mapped to when it brought it across.

In terms of what to do next, as I said it’d be interesting to bring an an OLAP-only measure an have that included in the fact table, so that both MDX and SQL queries get issued simulatenously and the results combined – so far, what we’ve really showed is just aggregate tables being mapped in, albeit with one of them coming from an OLAP cube. Also, I’d like to try out a mixed logical model where some of the dimensions and facts come from an OLAP source and some from relational, again how they join and how we share dimensions will be interested to see. If I get some time over the next week or so, I’ll try and post a follow-up.

UPDATE: I just tried out one of the above scenarios, creating an Analysis Services cube with units and sales measures, and an Oracle star schema with sales and costs measures, then brought them together in the way I’ve outlined above. Just like two normal data sources, when I ran a report that required data from both data sources, the logical SQL query was converted into two physical queries, one MDX and one SQL, and the BI Server brought them together and displayed the combined results in a single report. Cool.

UPDATE 2: One thing that I’ve found doesn’t work with XML/A sources is the new Time Series functions (AGO, TODATE) – if I try and use these on a logical table with an XML/A source I get error code OPR4ONWY : “Internal error: File .\Src\SQOIMDXGenerator.cpp, line 3042″ which to me looks like the equivalent of an ORA-00600. Of course with OLAP sources you’re more likely to build the time-series calculations directly into the cube, but it’s still something to watch out for. Also, if anyone’s interested, I also tried out the same scenario with Essbase 9.3.1 but I couldn’t get the report to run when working with a combined OLAP/relational logical model – I got the error “Internal error: File .\Src\SQOIUAbstractGenericSql.cpp, line 2424″ which again looks like a bug or an unimplemented feature. Of course with both MSAS and Essbase you can easily set a cube up in HOLAP mode rather than have OBIEE do this for you, but I thought it was an interesting investigation nonetheless.

in