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.