Data Warehousing & BI

My first formal activity at Oracle Open World 2007 was the Oracle Ace Director Product Briefing. A closed session where 30 odd ACE Directors (and you read that any way you like) had assembled to get some glimpses of what Oracle’s future has in store as well as some in depth insights into key products in the Fusion Middleware area as well as the database. The session was concluded by Mark Townsend (Oracle’s senior director of database product management), who delivered what will probably one of the best presentations during the week.

Mark ran us through some of the key features in RDBMS 11gR1 and in some areas laid down the road ahead to 11gR2. Not all of what I heard was new – we  have been Beta testing 11gR1 since October 2006, so we have seen quite a bit of it – but some of the things became much clearer or useful than they had seem before.

One of the topics that caught my attention – which frankly it normally never does – was Storage. Read the rest of this entry »

Row generation is a very useful technique for many (semi-)advanced SQL Queries. We have discussed various methods for row-generation in previous articles on our weblog. Examples are the CUBE operator, Table Functions and the Connect By Level < #number of records approach, apart from good old UNION ALL with multiple select from dual. These approaches vary in flexibility and compactness. CUBE and Connect By allow for easy generation of a large number of rows with little or complex control over the values in those rows, while the UNION ALL is elaborate and bulky, even though it gives great control over the exact values.

The Oracle 11g Unpivot operator presents us with a new way of generating rows with great control over the values in those rows and a more compact and elegant syntax than the UNION ALL alternative.

Let’s look at a simple example. Read the rest of this entry »

The PIVOT operation is one frequently discussed when talking about more advanced SQL Queries. Pivoting is the process of switching rows and columns, and is for example an advanced feature in Excel. Before Oracle 11g, there were several approaches to pivoting, that were not work-arounds for the lack of the PIVOT operator in the Oracle SQL Language.

With Oracle 11g, this has changed. Now we do have a PIVOT and an UNPIVOT operator at our disposal. Let's first take a look at UNPIVOT. In Oracle, the UNPIVOT operation is the process of turning Columns to Rows. Put simply, by applying the UNPIVOT operator to a number of columns, every row is split into that same number of rows. Each of these rows has two new columns: one for the column that this row stems from – one of the columns the data set was UNPIVOTed by – and one with the value from the column. The original UNPIVOT-columns are no longer part of the newly created records.

Let's take a look at a simple example, using the well known EMP table. We decide to consider the SAL and COMM columns as two different types of INCOME_COMPONENT. We would like to present our employees with an INCOME_COMPONENT column – and two rows for each employee with two income components.

This UNPIVOT query would look like this:

select *
from   ( select ename, job, sal, comm
         from   emp
       )
       unpivot
       ( income_component_value
         for income_component_type in (sal, comm)
       )
/

Read the rest of this entry »