bing
Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up
×
UPTO
50%
OFF!
Intellipaat
Intellipaat

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 employees 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

Interested in taking the Intellipaat OBIEE training for mastering the Oracle Business Intelligence tool?

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:

REGION  MONTH SALES
EuropeJanuary1480
North AmericaJanuary3760
January5240
EuropeFebruary1850
North AmericaFebruary3700
February5550
EuropeMarch2980.5
North AmericaMarch10420
March13400.5
24190.5
March 10420

 

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:

REGIONMONTHSALES
EuropeJanuary1480
EuropeFebruary1850
EuropeMarch2980.5
Europe      6310.5
North AmericaJanuary3760
North AmericaFebruary3700
North AmericaMarch10420
North America17880
24190.5

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.

 

Cubing:

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

 

image-12

 

 

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:

REGION  MONTHSales
EuropeJanuary1480
North AmericaJanuary3760
January5240
EuropeFebruary1850
North America February3700
February5550
EuropeMarch2980.5
North AmericaMarch10420
March   13400.5
Europe6310.5
North America17880
24190.5

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:

REGIONMONTH SALES
EuropeJanuary1480
Europe   February1850
EuropeMarch2980.5
Europe6310.5
North America   January3760
North America   February3700
North AmericaMarch10420
North America17880
January5240
February5550
March13400.5
24190.5


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:

REGIONMONTHSALES
North America17880
Europe6310.5
January5240
February5550
March13400.5

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:

RegionSales
North America17880
Europe6310.5

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:

RegionSalesRank
Europe6310.51
North America178802

 

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:

MONTH    SALES  SLIDING_AVG
February5550 5395
January52408063.5
March13400.59320.25

 

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:

 

image-13

Previous Next

Download Interview Questions asked by top MNCs in 2019?

"0 Responses on Working with Database Data"

    100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.
    top

    Sales Offer

    Sign Up or Login to view the Free Working with Database Data.