Monthly Archives: November 2015

Oracle OBIEE – My First Report

Recently, I created my first OBIEE report. To be honest, my biggest problem was to identify which tools to use. I might overlook the clues, but it took me quite some time before I could identify the important tools that I had to use. Finally, I understood that two important tools must be used: [1] the Oracle BI Administrator which allows us to create the objects that can be used to create the reports and [2] the internet page that must be used to modify the objects into a report.
Let us first discuss the Oracle BI Administrator.
Untitled
This tool starts by inserting physical tables in the right columns. In my case, I inserted three tables: D1_LAND, D2_TIJD en F_FEITEN. I then dragged the tables to the business layer. There, I should be able to create the aggregations. After that I dragged the tables into the presentation layer.
The second tool is a page that links to the OBIEE environment. In my case www.van-maanen.com:7780/analytics. After logging on, I see the items in the presentation layer that I just created. Dragging the attributes that I would like to analyse yields:
Untitled.

This is my starting point for further analysis.

Oracle OBIEE

Recently, I came across OBIEE. This is an Oracle tool that is created to support OLAP – type investigation into a database. I have to confess: I am impressed with it. The first great thing for me as an analyst is that it closely integrated into existing Oracle tool. One may continue to use the Oracle SQL Developer, the data are stored in an Oracle database. So one really extends the usage of existing tools.
Moreover, the tool is relative easy to work with.
To show how easy it is is to work with, I created a small model having two dimensions and one fact table. One dimension is related to date. It has four levels: date, month, year and total. The metadata can be inserted in Analytic Workspace Manager. In that tool, we seen the possibility to add additional dimensions. As attributes, we have to include the levels, the hierarchies and the mappings to an existing table. See the screenshot below:
Untitled
In that screenshot from the Analytic Workspace Manager, one might see three columns: a column where the logical entities/attributes of OLAP are mentioned, with their cubes, facts and dimensions. One has a column in the middle where the value for each of the logical entities/attributes are given. And one has a column on the right where the link is created between the physical tables and the logical/attributes is provided.
How to fill out such values can be found here.

In a similar fashion, one could create a cube that connects facts and dimensions. See the screenshot below.
Untitled2
But this is not all: SQL Developer now has a subset of the functionalities that allow you to maintain these cubes as well. I started out with changing some values in the underlying tables. I was able to load these data in the cube. I must say: a very nice accomplishment from that tool. SQL Developer is yet able to support you adequately if you want o create a new cube. For that task, SQL Developer is no intuively enough. But to maintain an existing cube is perfectly possible with SQL Developer.

Generate table in Oracle

I regularly happens to me that I want to generate a random set of records in an Oracle table. That could happen if we want to assess performance of a certain procedure. Or (other example) if we want to estimate the size of a table. A great thing of Oracle is the random function where random strings and numbers can be generated.
I created a table (GROOT) that contains three fields: an integer, a 10 character string and a date.
This can be loaded with this script. Within the script you will find also a number that indicates the number of records being generated. In my case, it is 2 million.
This scipt work blazingly fast. Within seconds, I had my table being loaded.


insert into SCOTT.GROOT
SELECT ROWNUM n
, dbms_random.string('U', 10)
, to_date('2014-01-01', 'yyyy-mm-dd')+trunc(dbms_random.value(1,1000))
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 2000000
);