Data Warehousing and Business Intelligence

25 December 2010

Comparing Oracle 11g OLAP Option and SSAS

For my next DWBI project, I’m thinking about implementing Oracle 11g OLAP option instead of SSAS, so I’m comparing them.

  1. Browsability: users can use OBI (e.g. Answers), BO (since 9i AW/2003), Excel or other tool to browse Oracle OLAP. Users are not expected to use SQL to query the cube. In AS now we have Crescent to look forward to. In the mean time we could use Tableau (see Jen’s session in SQLBits 8), Strategy Companion, CubePlayer or Excel 2010.
  2. Oracle OLAP cubes are stored as true multidimensional store (I’m interpretting this as compressed MOLAP), not as relational tables with materialized view/MV. This MOLAP storage solves the classic ROLAP problem of “MV aggregates can’t cover every permutation of every member of every attribute against other members of other attributes”.
  3. Oracle mentions compression & cost-based aggregation (see page 7). SSAS is certainly compressed, but I’m not sure if SSAS aggregations are cost-based.
  4. Oracle OLAP has the ability to blend multidimensional and SQL models within the same query (see Bud’s comments below). This “blend concept” is similar to SQL Server’s BISM concept in Denali (2011). BISM can expose itself as multidimensional interface (MDX) or SQL/relational interface (DAX). The difference is that BISM is relational (tables), with “in-memory column” (VertiPaq), where as 11g’s OLAP is compressed MOLAP.
  5. Many-to-many relationship. If you are not familiar with M2M in SSAS, pls read Marco’s paper about M2M Revolution. This is critical for many business situations. Can’t live without it. Have searched the whole library and I couldn’t find M2M in 11g OLAP. Bud said we can do it (see below), and from what I understand here, this is done by simply mapping a base measure to the fact table containing the M2M relationship, just like the way we create any base measure. Not sure how do we hide the measure though (because the measure is dummy, it is only used to establish M2M relationship between 2 dimensions).
  6. Yes 11g OLAP Option can create calculated measures. But as we in SSAS world know, calculated measure such as YTD or growth is not a problem if it’s only 1 or 2 measures, but if we have 50 measures we use date tool aka date calculation dimension (see Chris, Alberto & Marco’s most useful SSAS book here, chapter 6). Seems that we can’t do this in 11g OLAP. Surely we can’t create 50 * 10 calc measures? Well we can, but how about the usability?
    Update 27/12: I have read Bud’s comment below, and I know we can create “measure dim” and “calculation dim”, but in SSAS we need to create a hidden measure and write some “MDX calculations” to establish how the growth/2 yr growth, etc will be calculated. Have read about creating calc measure on 11g here, but couldn’t find how that “MDX calculation” is done in Oracle OLAP. Could anybody point me a direction?
  7. Complex calculation/MDX, i.e. existing (results depends on what’s on the slicer). Hidden named sets which is used by a calc member, etc. How do we do that in 11g OLAP?
    Update 27/11: have explored Simba’s MDX Provider for Oracle OLAP (link, link, link) and could not find comprehensive OLAP/MDX functions such as this. It seems that Simba’s product only provide connectivity, not ablity to do complex calculations. Would be grateful if somebody can enlighten me (list of Simba’s MDX functions for Oracle 11g OLAP). Have also looked at Bizzantz’s DeltaMaster methods, but it’s far below the richness of AS’ MDX e.g. scoping, existing, etc.
  8. MDX interface. 11g OLAP cube does mention MDX interface to OBIEE, but I expect this is basic “select … on columns, … on rows from cube” without the ability to use MS’ MDX functions such as NECJ, parallel period, etc. See page 14.
    Update 27/11: same comment as point 7. Update 28/11: see OLAP DML Functions here.
  9. Measure expression. This is required for example for currency conversion. If we don’t have measure expression in 11g OLAP (and we don’t have M2M either), how do we do currency conversion?
    Update 27/11: we can do this in 11g OLAP using “calculated measure” (see here), just need to specify the correct “calculation type” and “calculation”. It is quite comprehensive functions with regards to period / time series computation, but outside period I’m not sure. Update 28/11: found it. It’s called OLAP DML Functions, see here. It’s very comprehensive.
  10. Dimensional/cell security. In AS I sort of take it for granted that group1 can only see member1 whereas group2 can only see member2, along with giving users different default members based on role. But in 11g OLAP?
    Update 28/11: as Bud said, yes we can do dimensional security in Oracle OLAP, see “Selecting data by criteria” here.
  11. In AS we can control the incremental cube processing, i.e. process changed partition only etc. It is mentioned in 11g OLAP this can be automated (see the end on page 28 re Fast cube refresh), and that they can update incrementally by reading the log files (see last paragraph of page 6). This is a good thing (compared to AS).

Oracle OLAP blog is here. The Wiki is here. 11g OLAP user guide is here. OLAP Option documentation is here.

If you find something incorrect above, I’d be grateful if you could let me know, either via comments or vrainardi@gmail.com. I will correct them. Thanks again for Bud Eggress for your corrections.

Advertisements

11 Comments »

  1. I’m missing the reasons for choosing Oracle instead of SSAS – you only mention Fast cube refresh – but what are other advantages over SSAS?

    Marco

    Comment by Marco Russo — 25 December 2010 @ 8:45 pm | Reply

    • I haven’t found the second one Marco. When I find it I’ll let you know.

      Comment by Vincent Rainardi — 25 December 2010 @ 11:12 pm | Reply

  2. The post might leave readers with some incorrect impressions.

    – Business users are not expected to query the Oracle cube with SQL (just as SSAS users are not expected to query an SSAS cube with MDX). Business users use BI tools such as Oracle Business Intelligence, Business Objects and Excel (or almost any other SQL or MDX based tool) to query Oracle cubes, none of which require that a user write SQL (or MDX).

    – Oracle cubes are not stored as relational tables; Oracle cubes are not a ROLAP solution. Oracle cubes are stored in the Oracle database as true multidimensional data types. Oracle cubes are highly optimized for an OLAP workload, including very fast query performance.

    – Since Oracle supports SQL query of OLAP cubes, and SQL is perfectly capable of many to many relationships, it’s possible to combine the capabilities of SQL and the cube to achieve the desired results. Aside being able to support the SQL based BI tools (which accounts for the vast majority of BI tools), the ability to blend multidimensional and SQL models within the same query provides tremendous flexibility when building BI solutions.

    – Taken from your example, you do not have to create one YTD or growth measure for each base measure (“50*10”). E.g., YTD Sales, YTD Quantity, Growth Sales, Growth Quantity, etc. That is one way to do it (and a common way for those SQL based tools that do not provide pivot table like functionality), but you can also model a cube with a measure dimension (e.g., Sales, Quantity) and a calculation dimension (e.g., YTD, Growth). In this cube, YTD and Growth (and any other calculation) is available for every measure in the measure dimension. This is a common modeling technique for applications like Excel and Oracle Business Intelligence that provide pivot tables, this providing the ability to cross-tab measure and calculation.

    – A full feature MDX Provider for Oracle OLAP is available for use with the Oracle cube. (This is available from Simba Technologies, the same organization that OEM’s MDX providers for the majority of servers that support MDX. See http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm .) This is a full featured MDX provider, complete with calculation capabilities (e.g., parallel period). You might want to check out DeltaMaster from Bissantz. This is a clever BI application that uses MDX on the Oracle cube. It uses MDX for all if it’s on the fly calculations (e.g., time series calcs).

    – Currency conversion is quite simple. People will typically create a currency conversion factor dimensioned by time and country and then multiply whatever the measure is by that conversion factor. It’s a simple expression in the cube.

    – Oracle OLAP supports member level security for any user or role within the Oracle database. E.g., user or group A is allowed to see dimension members 1,2,3 and 4 and user or group B is allowed to see members 3,4,5 and 6. In addition, row level security can be applied using Oracle’s virtual private database (VPD) feature to apply security at the SQL layer. Typically, organizations use a combination of cube and VPD security features.

    – Oracle provides support for incremental cube updates. Only new or changed rows can be presented to the cube using either views with filters or materialized view log tables. Within the cube, Oracle will only process partitions that have new or changed values and will solve (e.g., aggregate) only those branches of a hierarchy that have changed.

    Comment by Bud Endress — 27 December 2010 @ 3:16 pm | Reply

    • Thank you very much for your input Bud, I appreciate you taking the time for correcting me. Apologies for my incorrect interpretation and I will rectify my post. I’m glad that Oracle OLAP has these features and will be exploring them.
      Marco, here we have the other advantages of the Oracle OLAP.

      Comment by Vincent Rainardi — 27 December 2010 @ 10:41 pm | Reply

    • Bud,

      just a note about many to many relationship support.
      Having it supported by SQL is not the same that having a many-to-many support inside the OLAP engine. For example, SSAS supports many-to-many relationships in the model so that a user browsing data with a client tool (like Excel PivotTable) can use such relationships without having to write a specific query. PowerPivot doesn’t have the same level of support and it is not possible to create this type of relationship in the model. This is unlucky and there is a possible workaround by creating measures with DAX expressions that make an equivalent calculation, but there is a big lack in flexibility with this approach. Thus, I would consider the support offered by PowerPivot “a limited support of many-to-many relationship”, and not a full-featured one like the one available in Analysis Services today.
      Creating a query supporting the many-to-many relationship in SQL is not something that a standard tool is usually able to do. If you take a look at my paper (http://www.sqlbi.com/manytomany.aspx) you will see that complexity of SQL necessary in some patterns based on many-to-many relationships is very high also for experienced developers. If there are BI tools that are able to generate such a queries, I’d like to know (they would solve many issues to my customers!).
      Is it possible in Oracle OLAP to create some server-based calculation that makes a workaround for many-to-many relationship calculation similar to that offered by PowerPivot?
      Thanks!

      Marco

      Comment by Marco Russo — 28 December 2010 @ 8:57 am | Reply

  3. Vincent,

    With respect to point 1, you can also use Excel 2007 and Excel 2003 to browse data in an Oracle OLAP cube. As you might expect, however, later versions of Excel have many more features that support its use as a BI reporting tool.

    With respect to point 7, you are correct that the Simba MDX Provider for Oracle OLAP provides connectivity from MDX consumers like Excel to Oracle database with OLAP option. The provider is a proper MDX engine capable of MDX calculations; in fact, Simba Technologies sells MDX Provider Engine SDKs and other software companies use the Simba parser and engine for building out their own fully capable MDX providers for their data source. FYI, the client-side retail MDX Provider for Oracle OLAP that you refer to here is programmed to push most of the heavy lifting as close to the database as possible, as that is where the resources are: CPU, memory, and data. Simba’s SDKs allow provider developers to select what to push and what to calculate, and we take strong advantage of that when implementing specific retail providers.

    We haven’t yet published the list of supported MDX functions in the Simba MDX Provider for Oracle OLAP. We had been focussing mostly on business users who generally only ask “Can I use client X against my Oracle database with OLAP option, and does it support all the features of client X?” They may understand there is MDX under the hood, but that is the degree that they care. The Simba MDX Provider for Oracle OLAP supports the MDX that Excel emits, and has successfully been previewed against the MDX emitted by Cognos Analysis Studio 8 and BusinessObjects Voyager.

    That said, however, I fully recognize that Consultants and Application Developers are a different audience than Business Users. These users can — and indeed need to — write complex MDX statements. I would be prepared to provide a brief writeup of what MDX functions the provider supports.

    In the meantime, have you tried it? You can use an evaluation copy of the provider to try Excel connecting directly to an Oracle OLAP database with no middle tier like SSAS.

    Comment by Mike van der Velden — 7 January 2011 @ 1:47 am | Reply

    • Thank you for your comments and additional info Mike, I appreciate you taking the time to do it. I’ll download and try the evaluation copy.

      Comment by Vincent Rainardi — 7 January 2011 @ 11:40 am | Reply

  4. Hi Vincent,

    Thanks for taking the time (and having the courage!) to explore this topic in detail. I’ve found it very enlightening and it seems to be a very well informed discussion.

    FWIW I firmly agree with Marco that true Many-to-Many functionality is crucial to correctly model many requirements in OLAP – without it you inevitably have to introduce compromises and workarounds to any moderately broad OLAP model.

    So I guess I’ll be sticking to SSAS for now – but the discussion was very interesting.

    Regards
    Mike

    Comment by Mike Honey — 12 January 2011 @ 12:21 pm | Reply

  5. Did u compare your system vs Cognos?

    I can say the difference Cognos vs Oracle BI and will be glad to hear any opinion.
    1. One vendor for BI, equipment, database, ETL -> higher performance and cheaper.
    2. in-memory technology in cube creating is future of OLAP
    3. Oracle has many new acquiries and now working for their integration. No time for new solutions like interactive visualization, search and collaboration (by Gartner).
    4. One platform for BI and planning (like in Cognos) = one metalayer -> less time&money for implementation.
    5. Indepedence from sources and ERP.

    Comment by Alexander Popov — 27 January 2011 @ 12:37 pm | Reply

  6. Many Thanks, Vincent!

    Comment by Alexander Youzhakov — 6 July 2012 @ 11:27 am | Reply

  7. Great Article vincent 😀

    Comment by Thantowi Jauhari U — 10 September 2012 @ 8:47 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: