RSS
 

Posts Tagged ‘data warehouse’

Connect ESSBASE to OBIEE 10g’s repository

18 Oct

1 – Introduction

In this topic we will see how to connect essbase to the OBIEE 10g repository and use it as data source for our reports.

2 – Prerequisites

2.1 The following product versions need to be installed:
OBIEE 10.1.3.3.2 or higher.
Essbase 7.1.6 or higher.

Essbase libraries need to be installed in the same machine where OBIEE server is installed.
We will assume that an Essbase server instance is already running somewhere as well as the OBIEE 10g, so we just need to link the two products; in order to connect Essbase to OBIEE 10g we need to install the Essbase client.

2.2 To install Essbase Client, you need to download the following software from http://otn.oracle.com (aproximately 8 Gb):
Hyperion EPM System Foundation Services (System Installer, Foundation Services, Architect)
Oracle Essbase (Client, Server, Provider Services)

Read the rest of this entry »

 
2 Comments

Posted in Business Intelligence, Data Warehousing, Database

 

Pivot table: generic sql query and Oracle Database 11g PIVOT operator

13 Oct
1 – Definition

A pivot table is the result of the action that allows data tables to be rearranged in many ways for different views of the same data.
Technically, if we compare a table with a matrix we can think to the pivot table as a transposed matrix, where the original row can be read as a column in the resulting table.

2 – How to generate a pivot table

Immagine to have the following data set:
select practice, office, count(1)
from organization
group by practice, office
Now you want the office’s numbers to be columns.  We have 4 offices (1,2,3,4) and we want to have the following columns in our resulting pivot table: OFF_1, OFF_2, OFF_3, OFF_4.
The query to generate the desired pivot table will look like the following:
select practice,
max( decode( office, 1, cnt, null ) ) off_1,
max( decode( office, 2, cnt, null ) ) off_2,
max( decode( office, 3, cnt, null ) ) off_3,
max( decode( office, 4, cnt, null ) ) off_4
from (
select practice, office, count(1) cnt
from organization
group by practice, office
)
group by practice

CNT column has been pivoted by OFFICE across PRACTICE.
Remember that this works only if you know the domain of the column you pivote by.
 
1 Comment

Posted in Data Warehousing, Database, Development