Excel and OLAP: ODBC vs. MDX

A question that I often get is "what is the difference between using ODBC and the MDX Provider for Oracle OLAP (from Simba Technologies) to query Oracle cubes"? Given that the Oracle cube is easily queried with SQL, it's a reasonable question.
The answer really boils down to leveraging meta data and automatic query generation.
With ODBC, it's up to the Excel user to write a SQL query to fetch data from the cube. Data can be returned in tabular format or a pivot table. When the data is viewed in a pivot table Excel will aggregate data, sometimes with unexpected results. For example Excel might choose to aggregate a measure such as Sales with COUNT or might try to SUM a measure such as Sales YTD Percent Change. Neither make any sense. It's up to the user to get it right.
With the MDX Provider, Excel understands what all the columns mean. It understands dimensions, hierarchies and levels. It's understand the difference between a key and a label. It knows what a measure is. It allows the server to calculate the data. Query generation is automatic. Business users just choose hierarchies and measures and the MDX Provider does the rest.
Here's a list of some of the advantages of using the MDX Provider for Oracle OLAP as compared to using ODBC and writing your own SQL.

Simba previews Cognos8 Analysis Studio accessing Oracle Database OLAP Option cubes

Hot on the heels of support for BusinessObjects Voyager, and in addition to the native Excel 2003/2007/2010 pivot table access, Simba are previewing the same connectivity for Cognos8 Analysis Studio - the dimensionally aware UI in the Cognos BI suite.

Together with the unique SQL access to the same multidimensional data & calculations in Oracle Database OLAP cubes (meaning that *any* tool or application capable of connecting to Oracle and issuing simple SQL can leverage the power of Database OLAP - like Oracle Application Express for example), plus the existing support for Oracle's own BI tools including

• Oracle BIEE 10g and 11g (see http://oracleolap.blogspot.com/2010/07/first-look-at-obiee-11g-with-oracle.html ) and
• Oracle BI Discoverer Plus OLAP (see http://oracleolap.blogspot.com/2010/08/discoverer-olap-is-certified-with-olap.html ),

together with the big functionality and performance improvements in 11g , there is now every reason to move to Oracle Database 11gR2 and to fully exploit the OLAP Option - whatever your choice of front end tool(s).

For Cognos fans: Here is the Video on YouTube:

More information, see the Simba website : http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm

Simba previews Oracle OLAP MDX Provider connectivity to SAP BusinessObjects Voyager

Simba technologies have released a short video to preview 'Using MDX Provider for Oracle OLAP to directly connect SAP BusinessObjects Voyager to Oracle Database OLAP Option'

This will be a great capability for users of both Oracle OLAP and BusinessObjects and will futher extend the reach of Oracle database embedded OLAP cubes.





You can get more details on the Simba website

Special OpenWorld 2010 Cocktail Reception

Join us! Simba Technologies and Vlamis Software Solutions are celebrating Oracle OLAP with a reception for current and prospective users. Please contact events@simba.com for exact location. Tuesday September 21, 2010 from 5-7PM. Walking distance from Moscone.

Simba product managers will also be available at the Oracle OLAP pod in Moscone West to answer questions you might have about Excel Pivot Tables and the MDX Provider for Oracle OLAP.

Excel and Oracle OLAP - Reporting No-Agg Measures

I've run into this a few times recently, so here's a quick tip related to using Excel with Oracle OLAP (via the Simba MDX Provider for Oracle OLAP, of course).

Here's a situation that's been reported as a bug, but you really just need to know the right Excel Pivot Table option to choose. Consider a cube that has measures that do not aggregate but is dimensioned by a dimension with a hierarchy. In this case, there is a cube with a Store dimension with levels Store > Store Type > All Stores. The stores are located in different countries and sell in local currencies. There is a Local Currency measure, with sales reported in whatever the local currencies might be (Euros, Dollars, Yen, etc.) and a Dollar Sales measure with the U.S. Dollar conversation. As a common currency, Dollars can be aggregated. Local currencies can't be aggregated.

Here's a sample report in Excel.



Note that Dollar Sales is reported for Direct and Indirect but Local Sales is not. That's correct because Local Sales doesn't aggregate.

But what if I happen to select only Local Sales (which is null at the aggregate members Direct and Indirect). By default, Excel will display the report as shown below.



This isn't very useful because I can't drill down on the Direct member to get at the stores. The solution is simple, but a lot of people seem to miss it. Just choose the Show items with no data in rows PivotTable option.



Now you will be able to see the Direct and Indirect members, allowing you to drill to stores.



Now, after the drill.