Business Intelligence Vs Analytics

I met with so many people in industry having many years of experience in Data Engineering and even in Data Architect role and I simply realized that there is lack of understanding or better to say confusion in terms of Business Intelligence Vs Analytics. Let me clearly depict this based on my experience and understanding to avoid such confusion. Hope it helps to Data Community

Accessing tables in one schema thru another schema

A)Schema Owners and Application Users

In the context of this article, the schema owner represents the Oracle user that owns all your database objects, while application users are Oracle users that need access to those schema objects.

Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far to many privileges, which can easily result in damage to your data and the objects themselves. Instead, it is better to define application users and grant those users the necessary privileges on the schema owners objects.

CURRENT_SCHEMA Approach:

1) First, we create the schema owner and an application user.

CREATE USER schema_owner IDENTIFIED BY password
GRANT CONNECT, CREATE TABLE TO schema_owner;

CREATE USER app_user IDENTIFIED BY password
GRANT CONNECT TO app_user;

2)Next, we create some roles to allow read-write and read-only access.

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;

3)We want to give our application user read-write access to the schema objects, so we grant the relevant role.

GRANT schema_rw_role TO app_user;
4)Now we are ready to create an object in the schema owner.

CONN schema_owner/password

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

Notice how the privileges are granted to the relevant roles.

This method is ideal where the application user is requiring no objects of its own. It is clean and doesn't require management of thousands of synonyms

Synonym Approach:

This method relies on synonyms owned by the application user to point to the correct location of the schema objects.

1)First, we create the users in a similar way to the previous example.

CREATE USER schema_owner IDENTIFIED BY password
GRANT CONNECT, CREATE TABLE TO schema_owner;

CREATE USER app_user IDENTIFIED BY password
GRANT CONNECT, CREATE SYNONYM TO app_user;

2)Next, we create some roles to allow read-write and read-only access and grant the read-write role to the application user.

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;

3)We want to give our application user read-write access to the schema objects, so we grant the relevant role.

GRANT schema_rw_role TO app_user;

4)Now we are ready to create an object in the schema owner in the same way we did in the previous example.

CONN schema_owner/password

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

If we now connect to the application user we are not able to see the object without qualifying it with a schema name

SQL> CONN app_user/password
Connected.
SQL> DESC test_tab
ERROR:
ORA-04043: object test_tab does not exist

SQL> DESC schema_owner.test_tab
Name Null? Type
----------------------------------------------------- ---------
ID NOT NULL NUMBER
DESCRIPTION VARCHAR2(50)

SQL> CREATE SYNONYM test_tab FOR schema_owner.test_tab;

Synonym created.

SQL> DESC test_tab
Name Null? Type
----------------------------------------------------- -------- -------------
ID NOT NULL NUMBER
DESCRIPTION VARCHAR2(50)

When there are a large number of application users, this method is cumbersome due to the huge number of synonyms required.

Obviously, it is possible to use public synonyms, but this can be problematic when you have multiple application schemas on a single instance.

B)Db Link :

DB Links are pretty much the name of the game here. If you can't get one created on your own,
then check if there are any public DB links that you could use.

CREATE DATABASE LINK new_york
CONNECT TO tom IDENTIFIED BY thumb
USING 'NEWYORK.world'; -- entry from tnsnames.ora

select * from emp@new_york
union
select * from dick.emp;

Performance tuning thru table partitions

Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions।
Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes।
Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.

Partitioning the table along defined boundaries and separating the partitions onto separate physical disks.

Most of databases allow horizontal partitioning, while others also include vertical partitioning । Horizontal positioning divides a table based on rows, and vertical partitioning divides the table based on columns.


When a table gets very large, basic operations and maintenance on that table becomes
cumbersome. Table locks persist for too long. All manner of SQL queries begin to take an impractical amount of
time to return results. The classic method of dealing with this scenario is to divide the table into
smaller tables and then use a view to combine the smaller tables into a comprehensive view।

Tables can be partitioned by

a range
Common examples of range based partitioning
schemes might be invoice numbers, dates, or zip codes। Anything with a serial ordering can be divided by ranges.
A list
A list-based partition will divide the tables based on a list of values
a hash
A partition defined by a hash will segment the table based on the value returned from a hash function. Once a table is partitioned based on a sensible boundary, the pieces of the table can be physically located on separate disks.

If you are running into performance issues with large tables, seriously consider optimizing your database by partitioning the large tables onto separate disks instead of over-optimizing your existing queries।

How do we create and manage partitions?

SELECT * FROM schema.table PARTITION(part_name);

This syntax provides a simple way of viewing individual partitions as tables

ALTER TABLE DROP PARTITION

ALTER TABLE TRUNCATE PARTITION

ALTER TABLE SPLIT PARTITION

ALTER TABLE ADD आटीटीण

A striped RAID device may contain multiple physical disks, but it appears to Adaptive Server as a single logical device.
For a striped RAID device, you can use multiple partitions on the single logical device and achieve good parallel
query performance.

Cursor

What is a cursor:

When a query is executed in oracle, a result set is produced and stored in the memory(RAM). Oracle allows the programmer to access this result set in the memory through cursor.

Also we can say it is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.

It is defined within the declaration section of your PLSQL code.

Why use a cursor?

Many times, when a query returns more than one row as a result, we might want to go through each row and process the data in different way for them. Cursor is handy here.

Types of cursors:

Oracle PL/SQL declares a cursor implicitly for all queries and DML statements (including queries that return only one row). But in most of the cases we don’t use these cursors for queries that return one row. Based on this, there are 2 types of cursors

1. Explicit cursor
2. Implicit cursor

Explicit cursor:

The programmer declares a cursor explicitly to process the result set of query (Mostly the query that returns multiple rows as result). The following are the steps to use an explicit cursor.

a. Declare the cursor -- This means give the cursor a name and associate the query that is going to return multiple rows.
b. Open the cursor -- execute the query
c. Fetch the cursor -- Get the result set and loop through to process them
d. Close the cursor -- Close cursor processing.

Implicit Cursor:

Oracle implicitly opens a cursor to process each sql statement that is not associated with an explicit cursor. An implicit cursor is opened for all queries (even if it returns only one row as the result set). Unlike explicit cursors, there is no necessity to use the OPEN, FETCH and CLOSE statements with these cursors. Instead cursor attributes could be used.

Cursor attributes:

Cursor attributes are variables that take some value about the status of the cursor. These values are automatically set by Oracle and the programmer can read them not write values for them. There are four cursor attributes. They are

1. %FOUND
2. %ISOPEN
3. %NOTFOUND
4. %ROWCOUNT

%FOUND Attribute: Has a Row Been Fetched?

After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.

DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
ELSE -- fetch failed, so exit loop
EXIT;
END IF;
END LOOP;
END;
/

If a cursor or cursor variable is not open, referencing it with %FOUND raises the predefined exception INVALID_CURSOR.

%ISOPEN Attribute: Is the Cursor Open?

%ISOPEN returns TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN returns FALSE

DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
the_name employees.last_name%TYPE;
the_salary employees.salary%TYPE;
BEGIN
IF c1%ISOPEN = FALSE THEN -- cursor was not already open
OPEN c1;
END IF;
FETCH c1 INTO the_name, the_salary;
CLOSE c1;
END;
/

%NOTFOUND Attribute: Has a Fetch Failed?

%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%NOTFOUND THEN -- fetch failed, so exit loop
-- Another form of this test is "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/

%ROWCOUNT Attribute: How Many Rows Fetched So Far?

When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row.

DECLARE
CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
IF c1%ROWCOUNT = 5 THEN
DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---');
END IF;
END LOOP;
CLOSE c1;
END;
/

Referencing %FOUND, %NOTFOUND, or %ROWCOUNT before a cursor is opened or after it is closed raises INVALID_CURSOR.

After the first FETCH, if the result set was empty, %FOUND yields FALSE, %NOTFOUND yields TRUE, and %ROWCOUNT yields 0.

Another example of cursor:

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

The result set of this cursor is all course_numbers whose course_name matches the variable called name_in.

Below is a function that uses this cursor.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Cursor with parameters:

CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
from courses_tbl
where subject_id = subject_id_in;

Trigger

An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed. Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified. So it's add additional assistance to manage group of data base tables when ever insert, delete, update operation is performed. For example in banking normal business hours for transactions are from 9 am to 3 pm. suppose some one initiated the transaction after 3 pm. then we can have a mechanism to invoke a trigger by DBMS to raise application error: Transaction is not allowed. So it adds additional features in DBMS system to manage, monitor and control in our data base application. There are 12 kinds (3*2*2) of DML triggers for relational tables:

DML statements: Insert, update, delete

Timing: before , after

Level: Statement level, Row level

Examples:

Before row levels trigger:
1)
CREATE TRIGGER ins_trig BEFORE INSERT ON Emp
FOR EACH ROW
BEGIN
UPDATE Employee SET Salary=Salary-300 WHERE Perks>500;
END;
/
2)
CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee
FOR EACH ROW
BEGIN
IF NEW.Salary<=500 THEN
SET NEW.Salary=10000;
ELSEIF NEW.Salary>500 THEN
SET NEW.Salary=15000;
END IF;
END
/

After statement level trigger:

CREATE OR REPLACE TRIGGER emp_audit_trig
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_action VARCHAR2(20);
BEGIN
IF INSERTING THEN
v_action := 'Added employee(s)';
ELSIF UPDATING THEN
v_action := 'Updated employee(s)';
ELSIF DELETING THEN
v_action := 'Deleted employee(s)';
END IF;
INSERT INTO empauditlog VALUES (SYSDATE, USER,
v_action);
END;

DROP TRIGGER

The general syntax of DROP TRIGGER is :

DROP TRIGGER trigger_name

Static vs. Dynamic testing

Dynamic Testing: Testing software through executing it.

Static Testing: Analysis of a program carried out without executing the program.

Metric

A standard of measurement. A metric should be a real objective measurement of something such as number of bugs per lines of code.