Working with Database Data

Working with OBIEE Database Objects

Using analytic SQL functions:
Analytic SQL is one of those Oracle Database native features that you can start using immediately after installing the database.
For example, you might need to know how many employees in your organization have been working for the company for 15 years or more. In this example, you might query the employee’s table located in the hr/hr demonstration schema, issuing the following statement:

SELECT count(*) FROM employees WHERE (EXTRACT(YEAR FROM (SYSDATE)) -
EXTRACT(YEAR FROM (hire_date))) >= 15;

The output should look like this:
COUNT(*)
———-
17
Multidimensional data analysis with SQL:
To start with, let’s create a new database schema and the tables within it that are needed for this example. You can perform all these tasks from SQL*Plus. First, you have to launch SQLPLUS. This can be done from within an operating system prompt as follows:

C:\oracle\...>sqlplus

When prompted, you have to connect as sysdba:
Enter user-name: /as sysdba

Once connected as sysdba, you can issue the following statements to create a new schema and grant it the privileges required for this example:

CREATE USER usr IDENTIFIED BY usr;
GRANT connect, resource TO usr;
CONN usr/usr

Before you can proceed to the example, you need to define some tables in the newly created database schema. For example, you might create the following tables: salespersons, regions, and orders related to one another through the foreign keys defined in the orders table. Here is the SQL code to issue:

CREATE TABLE salespersons (
emp_id VARCHAR2(10) PRIMARY KEY,
emp_name VARCHAR2(40)
);
CREATE TABLE regions (
reg_id VARCHAR2(2) PRIMARY KEY,
reg_name VARCHAR2(20)
);
CREATE TABLE orders(
ordno NUMBER PRIMARY KEY,
empid VARCHAR2(10) REFERENCES salespersons(emp_id),
regid VARCHAR2(2) REFERENCES regions(reg_id),
orddate DATE,
total NUMBER(10,2)
);

Next, you have to populate these tables with some data, so that you can perform queries against them. As the orders table refers to the salespersons and regions tables, you first have to populate these two tables with data. You might use the following statements for this:

INSERT INTO salespersons VALUES ('violet', 'Violet Robinson');
INSERT INTO salespersons VALUES ('maya', 'Maya Silver');
INSERT INTO regions VALUES ('NA', 'North America');
INSERT INTO regions VALUES ('EU', 'Europe');

The Intellipaat OBIEE developer training is what you need to grab the best jobs in Business Intelligence!
Now that you have filled up the salespersons and regions tables, you can move on and populate the orders table:

INSERT INTO orders VALUES
(1001, 'violet', 'NA', '10-JAN-2010', 1450.00);
INSERT INTO orders VALUES
(1002, 'violet', 'NA', '15-JAN-2010', 2310.00);
INSERT INTO orders VALUES
(1003, 'maya', 'EU', '20-JAN-2010', 1480.00);
INSERT INTO orders VALUES
(1004, 'violet', 'NA', '19-FEB-2010', 3700.00);
INSERT INTO orders VALUES
(1005, 'maya', 'EU', '24-FEB-2010', 1850.00);
INSERT INTO orders VALUES
(1006, 'maya', 'EU', '04-MAR-2010', 1770.00);
INSERT INTO orders VALUES
(1007, 'maya', 'EU', '05-MAR-2010', 1210.50);
INSERT INTO orders VALUES
(1008, 'violet', 'NA', '05-MAR-2010', 10420.00);
COMMIT;

You now have all the parts required to proceed with the example, and can issue a query that summarizes sales figures for every region for each month. Here is what this query might look like:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY ROLLUP(EXTRACT(MONTH FROM (o.orddate)), r.reg_name);

The query results should look like this:

Changing the order in which the parameters of ROLLUP appear will also affect the report. In our example, if you move the r.reg_name parameter to the first position, the report will generate totals for each region rather than for each month. Here is the query:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY ROLLUP(r.reg_name, EXTRACT(MONTH FROM (o.orddate)));

Here is its output:

As you can see, in the above report there are no longer any month totals. Instead, it provides you with the totals for each region.

Go through this OBIEE tutorial to learn Pivoting Through Data, create a crosstab worksheet in Discoverer Plus, calculations, etc.

Cubing:
The following figure gives a graphical depiction of how CUBE differs from ROLLUP when it comes to generating summary information:

Switching to SQL, you might write the following query using the CUBE function in the GROUP BY clause:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY CUBE(EXTRACT(MONTH FROM (o.orddate)), r.reg_name);

The previous query should generate the following report:

As you can see, you now have all possible subtotals and totals combinations presented in the report. This is why changing the order of columns in CUBE won’t affect the report, unlike with ROLLUP. So, the following query should actually give you the same results as above:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY CUBE(r.reg_name, EXTRACT(MONTH FROM (o.orddate)));

However, the order of rows in the report will be a little different:

Generating reports with only summary rows:
In some cases, you may not need to include the rows that represent the subtotals generated by GROUP BY, but include only the total rows. This is where the GROUPING SETS extension of the GROUP BY clause may come in handy:

SELECT r.reg_name region, TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month') month, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name, EXTRACT(MONTH FROM (o.orddate)));

This query should produce the following report:

Ranking:
To better understand how ranking works, though, let’s first look at the query that simply summarizes sales figures per region:

SELECT r.reg_name region, SUM(o.total) sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name);

Here is the output you should see:

Now let’s look at the query that will compute a sales rank for each region in ascending order:

SELECT r.reg_name region, SUM(o.total) sales, RANK() OVER (ORDER BY
SUM(o.total) ASC) rank
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name);

This time, the query results should look like this:

Windowing:

Windowing functions make up another important group of analytic SQL functions. The idea behind windowing functions is that they enable aggregate calculations to be made within a “sliding window” that may float down as you proceed through the result set.

SELECT TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month')
month, SUM(o.total) sales,
AVG(SUM(o.total)) OVER (ORDER BY TO_CHAR(TO_DATE(EXTRACT(MONTH FROM
(o.orddate)),'MM'),'Month') ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
sliding_avg
FROM orders o
GROUP BY TO_CHAR(TO_DATE(EXTRACT(MONTH FROM (o.orddate)),'MM'),'Month
');

The output should look like the following:

Aside from the AVG function, you can apply the windowing technique using the other aggregate functions, such as SUM, MIN, and MAX.

Discovering SQL in Discoverer:
Discoverer use SQL behind the scenes. Although you won’t see any SQL code immediately after a visual IDE has been loaded, it’s no more than 1 or 2 clicks away. In Discoverer Plus, for example, you can look at the SQL code behind a Discoverer item through the SQL Inspector, which you can launch by clicking the Tools\Show SQL menu. As an example, the SQL Inspector dialog containing the SQL behind the hr.employees item is shown in the following figure:

Wish to crack OBIEE job interviews? Intellipaat’s Top OBIEE Interview Questions are meant only for you!