Business Intelligence – Oracle

Archive for the ‘Oracle 11g’ Category

Oracle 11g – BLOB and BFile support for SQL Plus

Posted by Venkatakrishnan J on September 17, 2007

I happened to come across one other new feature in Oracle 11g with respect to SQL Plus. SQL Plus now supports the use of blobs and bfiles in your queries. This is what the documentation has got to say about this “Queries in SQL*Plus now support BLOB columns.This feature allows you to verify the contents of BLOB columns.”. Pretty interesting feature.

This is what i get in 10g.    

  

And this is what i get in 11g.     

 

Posted in All Posts, Oracle 11g | 4 Comments »

Oracle 11g – CUBE_TABLE function for Oracle OLAP

Posted by Venkatakrishnan J on September 6, 2007

If you are wondering how Oracle OLAP 11g exposes itself in the form of relational views, its all because of a new function called CUBE_TABLE. This function has been added new to 11g, to enable the OLAP cubes to be a part of the SQL Optimizer. This can be used on both Dimensions and Cubes to convert them into relational data. Lets see the syntax of this new function (taken from the docs) first.

SELECT …
FROM
TABLE(CUBE_TABLE(‘arg’));

As you see above, the CUBE_TABLE takes either a cube name or a dimension name as its parameter. One also has options of specifying multiple hierarchies for dimensions and cubes. Let us try an example here.

SELECT
*
FROM
TABLE(CUBE_TABLE(‘sh.product’));

Here, i am trying to display the dimension product in the form of a relational table. CUBE_TABLE function does that for us automatically.

      

Now lets try specifying a hierarchy clause for the same dimension. This would be helpful if you have multiple hierarchies defined in your dimension.

SELECT *
FROM
TABLE(CUBE_TABLE(‘sh.product HIERARCHY producthierarchy’));

      

Lets try the same with a cube.

SELECT *
FROM
TABLE(CUBE_TABLE(‘sh.salescube HIERARCHY product producthierarchy’));

      

The only difference between the above 2 is that in case of a cube we would have to specify the dimension name and the hierarchy name after the HIERARCHY clause. All the details about the CUBE_TABLE function is available here.

Posted in All Posts, OLAP, Oracle 11g | 3 Comments »

Oracle 11g – PIVOT and UNPIVOT

Posted by Venkatakrishnan J on September 5, 2007

If you had noticed, one of the most important new features of Oracle Database 11g from a SQL perspective is the introduction of PIVOT and UNPIVOT operators into SQL constructs. So, now one can have cross tab views, that we are so used to in reporting tools (like OBI EE), from sqlplus. Also, it would really be great if the reporting tools can take advantage of this construct while building the queries. I will demonstrate PIVOT and UNPIVOT with a small example here. The following picture shows the output of a simple crosstab from OBI EE.

      

What this crosstab does is, it analyzes the amount_sold (Sales table of the SH schema) across 2 dimensions countries and channels. Lets see how a normal query would have been to get this data in 10g or before (simple unpivoted query).

SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME

The result of the above query would look something like this

      

Now, our aim is to build a cross tab out of this. Lets try creating a crosstab using the PIVOT operator directly on the above aggregate query. But before that let me give the syntax of the PIVOT operator (taken directly from the docs) here.

Syntax:

SELECT ….
FROM

PIVOT
(
aggregate-function()
FOR IN (, ,…, )
) AS
WHERE …..Here, table-expr would be our aggregate query that we specified above. Aggregate-function would require the measure which we would like to aggregate (in our case AMOUNT). pivot-column is the column that we would like to pivot (pivot the rows into columns). Alias is optional. Now lets build our query.SELECT *
FROM ( SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME ) A
PIVOT
(
SUM(AMOUNT)
FOR CHANNEL IN (‘Direct Sales’, ‘Partners’, ‘Internet’)
)
ORDER BY COUNTRY      As you see above, we have pivoted the first query into a crosstab. To unpivot this, we would have to create a view on top of the above query so that we can unpivot that view.

CREATE OR REPLACE VIEW
PIVOT_AMOUNT_VW
AS
SELECT *
FROM
(
SELECT COUNTRY_NAME COUNTRY, CHANNEL_NAME CHANNEL, SUM(AMOUNT) AS AMOUNT
FROM
SALES A,
GEOGRAPHY B,
CHANNELS C
WHERE
A.GEOGRAPHY = B.DIMENSION_KEY AND
A.CHANNELS = C.DIMENSION_KEY
GROUP BY
COUNTRY_NAME, CHANNEL_NAME) A
PIVOT
(
SUM(AMOUNT)
FOR CHANNEL IN (‘Direct Sales’ AS DS, ‘Partners’ AS PARTNER, ‘Internet’ AS INTERNET)
)
ORDER BY COUNTRY

         

Lets unpivot the above view.

SELECT *
FROM PIVOT_AMOUNT_VW
UNPIVOT INCLUDE NULLS
(
AMOUNT
FOR CHANNEL IN (DS, PARTNER, INTERNET)
)
ORDER BY COUNTRY

      

Quite an interesting new feature considering the fact that pivoting is an operation that is done in almost all customer scenarios. There are a number of options for PIVOT and UNPIVOT. They can be obtained from the documentation here

Posted in All Posts, Oracle 11g | 1 Comment »