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

A first look at OBIEE 11g with Oracle OLAP

For those who missed it, the global launch for the 11g release of the Oracle Business Intelligence Enterprise Edition suite (OBIEE) took place in London on July 7th.

And the fantastic news for Oracle OLAP customers is that OBIEE 11g will work out-of-the-box with Oracle OLAP in almost exactly the same way as OBIEE 10g does - with just one additional configuration step required to enable the new OLAP-style front-end functionality.

Of course, there are other features that are relevant such as the WebLogic application server, and the new security model, but these have already been well blogged elsewhere so the focus of this posting will be Oracle OLAP integration.

To illustrate how easy it is, I will use a trusted old friend as a starting point - the 11g Global sample schema. I have installed this in an Oracle 11.2 database instance, created an Oracle OLAP Analytic Workspace, and then refreshed this AW so that the dimensions and cubes are built.

With an AW in place, the next step is to use the OBIEE plug-in for AWM to generate the metadata required for the OBIEE Server. For those who have not used the plug-in before, check out this excellent demonstration of how it works. While this particular version of the plug-in was originally released to work with OBIEE 10g, and presumably an updated version will be released in due course, it can be used in exactly the same way in OBIEE 11g to import metadata into the Administration tool.



And at first glance, aside from a few updated icons, this version of the Administration tool looks very similar, but the biggest change related to the administration of OLAP data sources (relational or MOLAP) is the ability to map hierarchy objects right through into the presentation layer.

Here is the Metadata generated by the plug-in for the Channel Dimension in both the Business Model and Presentation layers


The new 11g OLAP-style front-end functionality is enabled by adding these hierarchies into the Presentation layer too. This can be achieved by a simple click-and-drag for each hierarchy like the following which is again for the Channel Dimension



Hopefully, the next release of the plug-in will handle this additional step automatically (and also provide support for value-based hierarchies which were not supported by the front-end in OBIEE 10g) but in the meantime it really is just a simple click-and-drag for each dimension.

Once all the hierarchies are mapped through into the Presentation Layer, the cube is ready to query. I can log into the OBIEE 11g home page and create a new analysis based upon my Oracle OLAP subject area. The new hierarchies are available for selection when I construct a query




I can then select all of the 'columns' I need for my query and view the results as a pivot table. Here is a really simple example showing Sales by Time. I have also added some calculated measures which have been created inside the AW and derive really useful analytics from the Sales measure. This is a classic reason for using the OLAP Option in the first place - it facilitates the easy creation of calculations that are difficult (or often impossible) to express in SQL. And by having them embedded in the cube, the only thing that the SQL tool (in this case OBIEE) needs to do is select the calculation as a field in a view. How easy is that?!



Once a pivot table with Hierarchy-based columns has been created, this is where the new front-end features really come into play. Some highlights include Calculated Items (derived Dimension members) and a new Selector (which allows dimension selections to be built up as a series of steps based upon add/keep/remove logic):



For those familiar with Discoverer OLAP, or Sales Analyzer, Financial Analyzer and Express Objects/Analyzer, these aren't exactly revolutionary features, but combined with all the other great features of the OBIEE suite, this is now a very compelling platform for your Oracle OLAP data.

Finally, I would guess that there are probably thousands of old Oracle Express/OLAP systems that have been waiting for a BI platform like this. If you work on one, what are you waiting for?

***OBIEE 11g can now be downloaded from OTN***

New Oracle OLAP White Paper released to OTN

A new Oracle OLAP white paper has been released to OTN titled "Using Oracle Business Intelligence Enterprise Edition with the OLAP Option to Oracle Database 11g"

This contains a guide on how to configure the OBIEE metadata layer to leverage the 11g Oracle OLAP option, both indirectly via OLAP cube based materialized views, and directly via OLAP cube views. For those working with 10g OLAP (cube views only), the best guide to configuring OBIEE is found in the online tutorial on OTN.

Personally, I think that it is great that this white paper captures an explanation of how to write SQL that is optimised for Oracle OLAP cube views. This is something I find customers initially struggle with - they write what they believe is a simple query and then cannot understand why the performance is not good.

This is because there are a few golden rules to writing optimal SQL for OLAP cube views and while they are simple to understand, they are not obvious to those who are new to the technology. I hope to write a more detailed Blog entry on this subject very soon, but for the time being take a look at the white paper (particularly pages 10 & 11) to see what I mean.

Best Practice Tips : SQL Access to Oracle DB Multidimensional AW Cubes (#2)

One of the most useful features introduced with Oracle Database OLAP is the ability for the powerful multidimensional calculation engine and the performance benefits of true multidimensional storage in the Analytic Workspace (AW), to be accessed and leveraged by simple SQL queries.

This single feature dramatically increases the reach and applicability of multidimensional OLAP – to a vast range of BI query and reporting tools, and SQL-based custom applications that can now benefit from the superior performance, scalability and functionality of a first class multidimensional server, but combined within the Oracle Database with all the other advantages that derive from that.

This post is the second in a series that I will use to share some general best practice tips to get the most out of this feature, so that you can deliver even better solutions to your business end-users:

Best Practice Tip #2: General AW Object Naming Conventions for dimensions, levels, hierarchies and attributes…(Oracle Database 10g and 11g)

The following advice will result in much easier to understand and use relational views over your AW. It makes the implementation much cleaner to visualise, and easier for other users to understand what they are looking at. It also saves a lot of typing for developers that are writing their own SQL queries!

The objective is to ensure that the generated column names in your views are easy to read, and also to avoid the possibility that generated column names may get truncated to fit within the limits for a column name in Oracle Database (when that happens your views get really ugly really quickly). Finally, it has the additional desirable side effect of making it easier and therefore quicker to do the mappings in AWM because the screens are less cluttered with long-winded object names!

Note: this advice follows both for Oracle Database 10g OLAP (eg views created by the AWM10g View Generator Plug-in) and for Oracle Database 11g OLAP, where views are auto-generated (eg when creating your Standard From AW via AWM11g).

Here is the idea:

• Keep the names used for dimensions, levels, hierarchies, and attributes as short as possible, while still meaningful of course.
• If possible (simply for readability in the resulting relational view and column names), avoid the use of the "_" char especially for dimension, hierarchy, level and attribute names.
• If possible (also recommended if Oracle OLAP API clients such as OracleBI Spreadsheet Add-in , OracleBI Discoverer Plus OLAP and OracleBI Beans will be used on the same AW), create the AW in its own schema.
  

Don't be seduced into thinking it is a good idea to put "DIM" in the name of everything that is a dimension, or "ATT" into the name of all the attributes. You don't need to do this. The AW knows what objects are what, and you can very simply query the AW if you need, for example, to find out the names of all the Dimensions in an AW. (Another topic for another day is to walk thru all the Data Dictionary stuff that helps with this).

In other words: If you have a Product Dimension, it is self-evidently a dimension, so clogging up its name with "_DIM" or "_DIMENSION" is just extra wear and tear on your keyboard!

Example:

To illustrate the impact this advice can have, here are two Product Dimensions, which apart from the fact one follows best practice advice and one does not, are identical (example is from Oracle Database 11g AW) (you can click on the picture to see it full size):

First – two ways I could have created my Product Dimension:

Second – what the resulting dimension views for the Main hierarchy would look like in each case:


Third – how much harder it is to read and write the SQL to query the AW’s dimension as a result:

Which of these functionally identical examples is easier to read, easier to understand and easier to query?

I rest my case. Giving a bit of thought to the way you build your AW before you build it nearly always pays dividends later.

Best Practice Tips : SQL Access to Oracle DB Multidimensional AW Cubes (#1)

One of the most useful features introduced with Oracle Database OLAP is the ability for the powerful multidimensional calculation engine and the performance benefits of true multidimensional storage in the Analytic Workspace (AW), to be accessed and leveraged by simple SQL queries.

This single feature dramatically increases the reach and applicability of multidimensional OLAP – to a vast range of BI query and reporting tools, and SQL-based custom applications – BI and operational – that can now benefit from the superior performance, scalability and functionality of a first class multidimensional server, but combined within the Oracle Database with all the other advantages that derive from that. Bottom line: if you have a tool or application that can (a) connect to an Oracle Database instance, and (b) fire simple SQL at that Database, then you can get benefit from the AWs in that tool or application.

This post is the first of a series that I will use to share some general best practice tips to get the most out of this feature, so that you can deliver even better solutions to your business end-users.

If any of you have tips and advice of your own that we can share, please contact us – we’ll be happy to publish your good ideas and experience with this feature of Oracle Database OLAP.

Anyway. Enough pre-amble. Let’s get on with it. Here goes:

Best Practice Tip #1: Creating your views (Oracle Database 10g and 11g)

Basically the first tip in the series boils down to two things:

1) Always build your AWs to Oracle Database OLAP ‘Standard Form’. This is what happens if you build them with AWM, OWB (10g-only at the time of this post, but support for 11g target AWs is due in OWB very soon), or the supplied AW API if you need to programmatically build and maintain your AW.
2) Use the free-ware “View Generator” plug in for AWM10g to build your 10g views, and leverage the automatically generated views in 11g, unless you have a very good reason not to.

Together, if you follow this advice you will save a lot of time on your project, and also increase your ability to support the application going forward. And it will be a lot easier for others (such as Oracle Support, or your local friendly Oracle OLAP Consultant) to help you if you have any problems.

More detail:

In Oracle Database 10g, there is nothing to stop you coding your own views using the SQL OLAP_TABLE() function. And, if you have an entirely custom built AW this is pretty much your only option. However, if you have developed your AW to Oracle’s OLAP Standard Form specification you can save yourself the time, by using a handy dandy little plug-in for AWM10g. The plug-in is free shareware for AWM10gR2 & can be downloaded from here, with the associated ReadMe here.

The plug in steps you thru a simple wizard within AWM, allowing you to choose which measures etc you need, and then creates the views for you (storing the biggest lump of syntax – the ‘limitmap’ parameter which describes which AW objects show up in what columns in your view – inside the AW itself, in a multi-line text variable/measure).

In Oracle Database 11g, while OLAP_TABLE() is still available for you to use if you like (and sometimes it is perfect for your needs as it has lots of very clever hooks by which you can trigger various OLAP actions whenever a user selects from the view), for most cases, the new CUBE_TABLE() function added in Database 11g is much easier and therefore recommended.

CUBE_TABLE() views are what AWM11g automatically creates for you when defining the objects inside the AW. Assuming you have a valid Standard Form 11g Database AW, such as you might build in AWM11g, CUBE_TABLE() is much, much easier to use than OLAP_TABLE().

For example, the entire syntax required to create a Dimension View, for a specified hierarchy of that Dimension in an AW (not that I even have to type any of this in, as the AWM tool does it automatically for me) is as follows:

CREATE OR REPLACE FORCE VIEW MYDIM_MYHIER_VIEW AS
SELECT *
FROM TABLE( CUBE_TABLE('MYSCHEMA.MYDIM;MYHIER') );

How easy is that?!

All you need to know about your AW is the name of the Hierarchy (MYHIER), Dimension (MYDIM) and schema that the AW is built in (MYSCHEMA). All the object mappings that you have to tell OLAP_TABLE about, in the limitmap parameter, are automatically done as a result of improvements in Database 11g’s Data Dictionary (which is now fully aware of the details of the contents of the AW).

Here (below) is what an example Product Dimension looks like in AWM11g, and the resulting View:

Note that the OLAP Option only allows one Dimension or Cube (and therefore Dimension View, or Cube View) of a given name in each SCHEMA. For this reason, it is our recommendation that each AW be built in its own schema if possible. This will allow you, if you ever need to, to have a PROD dimension or SALES Cube in more that one unrelated AW. This tip will be included again, in an upcoming Post on Best Practice AW Design practices, and naming conventions.