This is the guide on the management and use of Oracle Business Intelligence Enterprise Edition (OBIEE) database objects. Discover how to work with tables, views, joins, and more database elements while creating business reports and dashboards that really work.
Table of Contents
Introduction
Oracle Business Intelligence Enterprise Edition, or OBIEE, provides users with advanced analytics based on database objects. This tutorial introduces the user to the basic SQL techniques that one must use while working with OBIEE database objects-analytic functions, multidimensional data analysis, and reporting tools.
Using Analytic SQL Functions
Analytic SQL is an extremely powerful feature of Oracle Database available instantly after installation. Such functions allow for sophisticated analysis directly within the SQL query.
For Example: Counting Long-Term Employees
You can use this query to count the number of employees with 15 years of more of service with the company.
SELECT COUNT(*)
FROM employees
WHERE (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hire_date)) >= 15;
output:
17
Multidimensional Data Analysis with SQL
Multidimensional data analysis in SQL is arranging and analyzing data across different dimensions, where patterns, trends, and insights may be observed. This method is mostly used in business intelligence for decision-making purposes. SQL provides more robust tools for structuring data, querying complex sets of data, and summarizing key metrics.
Here are the steps involved in setting up and analyzing multidimensional data:
Creating a New Database Schema
To perform multidimensional analysis, first, create a new schema and grant the necessary privileges:
CREATE USER usr IDENTIFIED BY usr;
GRANT connect, resource TO usr;
CONN usr/usr;
Defining Tables
Define the following tables: salespersons, regions, and orders. The orders table references the salespersons and regions tables via foreign keys:
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)
);
Populating Tables with Data
Insert sample data into the tables:
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');
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);
Summarizing Sales Figures
Summarize sales data by region and month using the GROUP BY clause:
SELECT r.reg_name AS region,
TO_CHAR(TO_DATE(EXTRACT(MONTH FROM o.orddate), 'MM'), 'Month') AS month,
SUM(o.total) AS sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY ROLLUP(EXTRACT(MONTH FROM o.orddate), r.reg_name);
Rollup and Cube Functions
ROLLUP and CUBE in SQL are the advanced grouping functions to produce summary reports using multi-level aggregations. It helps significantly in analytical queries to produce reports over varying granularity levels of data. They have emerged to be an efficient tool used for business intelligence and report writing.
Let us see how one would make use of these:
Using the ROLLUP Function
ROLLUP generates subtotals for a hierarchy. Changing the parameter order changes subtotal priorities:
SELECT r.reg_name AS region,
TO_CHAR(TO_DATE(EXTRACT(MONTH FROM o.orddate), 'MM'), 'Month') AS month,
SUM(o.total) AS sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY ROLLUP(r.reg_name, EXTRACT(MONTH FROM o.orddate));
Using the CUBE Function
CUBE generates all possible subtotal combinations:
SELECT r.reg_name AS region,
TO_CHAR(TO_DATE(EXTRACT(MONTH FROM o.orddate), 'MM'), 'Month') AS month,
SUM(o.total) AS sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY CUBE(r.reg_name, EXTRACT(MONTH FROM o.orddate));
Grouping Sets for Summary Reports
Generate reports with specific summary rows using GROUPING SETS:
SELECT r.reg_name AS region,
TO_CHAR(TO_DATE(EXTRACT(MONTH FROM o.orddate), 'MM'), 'Month') AS month,
SUM(o.total) AS sales
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY GROUPING SETS(r.reg_name, EXTRACT(MONTH FROM o.orddate));
Ranking Sales Figures
Assign ranks to sales figures using the RANK() function:
SELECT r.reg_name AS region,
SUM(o.total) AS sales,
RANK() OVER (ORDER BY SUM(o.total) ASC) AS rank
FROM regions r, orders o
WHERE r.reg_id = o.regid
GROUP BY r.reg_name;
Windowing Functions
Perform calculations within a sliding window of data:
SELECT TO_CHAR(TO_DATE(EXTRACT(MONTH FROM o.orddate), 'MM'), 'Month') AS month,
SUM(o.total) AS 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
) AS sliding_avg
FROM orders o
GROUP BY TO_CHAR(TO_DATE(EXTRACT(MONTH FROM o.orddate), 'MM'), 'Month');
SQL in Oracle Discoverer
Oracle Discoverer integrates SQL queries within its visual interface. It is possible to see the underlying SQL using the SQL Inspector (Tools > Show SQL).
Conclusion
This is a comprehensive and practical guide for understanding the OBIEE database objects and advanced SQL techniques that have helped many Business Intelligence developers in their development processes. Get hands-on training in Intellipaat’s OBIEE developer course and strengthen your career in Business Intelligence.
Our Business Intelligence Courses Duration and Fees
Cohort starts on 1st Feb 2025
₹17,043
Cohort starts on 25th Jan 2025
₹17,043
Cohort starts on 18th Jan 2025
₹17,043