Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Pivoting Strings – Converting Rows into Columns

Posted by Venkatakrishnan J on February 12, 2008

A pretty interesting question came up today in one of our internal forums. I just thought i would share with you all since BI EE does not have this capability out of the box. Look at the screenshot below.

      

The above picture gives you a simple report containing REGION_NAME and COUNTRY_NAME columns. Now the question was how do we convert this report into the one shown below

Africa      South Africa
Americas   Argentina, Brazil, Canada, United States of America
Asia   India, Japan, Singaport
………….
…………

So, basically, the idea is to have a report containing all the list of countries under a region in a single row where each country within a region has to be concatenated using commas. In order to do this, we shall see 2 approaches today.

1.   Using a database function and then calling this function from BI EE using EVALUATE.

2.   Using Direct Database Requests (not recommended).

Lets look at both the approaches one by one. For the 1st one, lets create a database function as shown below

CREATE OR REPLACE FUNCTION PIVOT_OP(p_REGION IN VARCHAR2)
RETURN VARCHAR2
IS
v_region varchar2(1000);
v_geography varchar2(1000);
BEGIN
select
REGION_NAME,
RTRIM(LTRIM(SYS_CONNECT_BY_PATH(COUNTRY_NAME, ‘,’),’,’),’,’) into v_region, v_geography
from
(
select
COUNTRY_NAME,
REGION_NAME,
count(*) OVER ( partition by REGION_NAME ) rowcnt,
ROW_NUMBER () OVER ( partition by REGION_NAME order by COUNTRY_NAME) seqno
from
(select distinct
REGION_NAME,
COUNTRY_NAME from GEOGRAPHY)
where
REGION_NAME is not null)
where
seqno=rowcnt and
REGION_NAME = p_REGION
start with
seqno=1
connect by prior
seqno+1=seqno
and prior
REGION_NAME=REGION_NAME;
RETURN v_geography;
END PIVOT_OP;

What this function basically does is, it does a recursive concatenation of all the countries in a region using the SYS_CONNECT_BY_PATH(this would work from 10g database or above). Check whether the function is working properly.

      

Now the next step is to call this from Answers using EVALUATE. Ensure that the above function is compiled in the same schema from which the source data is coming from.

      

Now you should get what is required in your final report.

      

Lets go to the second approach. In this approach, we will be using Direct Database requests. Remember, this is not recommended at all. I have included it here just for demonstration purposes. Use the following query (change it to your needs).

select
REGION_NAME ,
RTRIM(LTRIM(SYS_CONNECT_BY_PATH(COUNTRY_NAME, ‘,’),’,’),’,’) Countries
from
(
select
COUNTRY_NAME,
REGION_NAME,
count(*) OVER ( partition by REGION_NAME ) rowcnt,
ROW_NUMBER () OVER ( partition by REGION_NAME order by COUNTRY_NAME) seqno
from
(select distinct
REGION_NAME,
COUNTRY_NAME from GEOGRAPHY)
where
REGION_NAME is not null)
where
seqno=rowcnt
start with
seqno=1
connect by prior
seqno+1=seqno
and prior
REGION_NAME=REGION_NAME

      

      

Advertisements

5 Responses to “Oracle BI EE 10.1.3.3/2 – Pivoting Strings – Converting Rows into Columns”

  1. caimino said

    Hi,
    I have a trouble about pivot table in OBIEE.
    I tried to create a pivot table with SAMPLE_ID as rows, YEAR as columns and a measure RESULT. In Discoverer I managed to insert several calculated measures on RESULT (as MIN, MAX, AVG, StdDev)at the bottom of the table. I tried to do it in OBIEE but I see all calculated measures for each SAMPLE_ID instead of one row for each measure at the bottom of the pivot table.
    How can I achieve my aim?
    Thanks.

    Kind Regards

  2. Sumant Sarkar said

    brilliant. thank you. this technique provides a lot of power to those who create reports.

  3. tomw said

    Why is Direct Database requests not recommended?

  4. Venkatakrishnan J said

    @Tom – It is not recommended because your end users can fire DDL’s directly(provided they have the privilege to the schema they are connecting to). Also, we are bypassing all security provided by BI Server. Consider, what would happen if an end user comes and does ‘DROP TABLE ‘ to all the tables.

  5. Tomw said

    I see. I thought only Select statements were allowed. not DDL.

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

 
%d bloggers like this: