Data Warehouse Concepts

Dataware Housing Document

1.The fact table mainly consists of business facts and foreign keys that refer to primary keys in the dimension tables. A dimension table consists mainly of descriptive attributes that are textual fields.

2.A dimension table contains a surrogate key, natural key, and a set of attributes. On the contrary, a fact table contains a foreign key, measurements, and degenerated dimensions.

3.Dimension tables provide descriptive or contextual information for the measurement of a fact table. On the other hand, fact tables provide the measurements of an enterprise.

4.When comparing the size of the two tables, a fact table is bigger than a dimensional table. In a comparison table, more dimensions are presented than the fact tables. In a fact table, less numbers of facts are observed.

5.The dimension table has to be loaded first. While loading the fact tables, one should have to look at the dimension table. This is because the fact table has measures, facts, and foreign keys that are the primary keys in the dimension table.



Read more: Dimension Table and Fact Table | Difference Between http://www.differencebetween.net/technology/hardware-technology/dimension-table-and-fact-table/#ixzz4zVLQgywC



Types of Facts
There are three types of facts:

Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.

Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.

Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

Let us use examples to illustrate each of the three types of facts. The first example assumes that we are a retailer, and we have a fact table with the following columns:


Date
Store
Product
Sales_Amount
The purpose of this table is to record the sales amount for each product in each store on a daily basis. Sales_Amount is the fact. In this case, Sales_Amount is an additive fact, because you can sum up this fact along any of the three dimensions present in the fact table -- date, store, and product. For example, the sum of Sales_Amount for all 7 days in a week represents the total sales amount for that week.

Say we are a bank with the following fact table:


Date
Account
Current_Balance
Profit_Margin
The purpose of this table is to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day. Current_Balance and Profit_Margin are the facts. Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information). Profit_Margin is a non-additive fact, for it does not make sense to add them up for the account level or the day level.

Types of Fact Tables
Based on the above classifications, there are two types of fact tables:

Cumulative: This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.
Snapshot: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.

In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.

In our example, recall we originally have the following table:

Customer Key Name State
1001 Christina Illinois
After Christina moved from Illinois to California, the new information replaces the new record, and we have the following table:

Customer Key Name State
1001 Christina California
Advantages:

- This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.

Disadvantages:

- All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before.

Usage:

About 50% of the time.

When to use Type 1:

Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes

UTL File

declare

   f1 utl_file.file_type;

     v_str varchar(100);

 begin

      f1 := utl_file.fopen('TEST123','data.txt','r');

           utl_file.get_line(f1,v_str);

dbms_output.put_line(v_str);

         utl_file.fclose(f1);

end;

/

declare

   f1 utl_file.file_type;

    v_deptno number(10);

    v_deptnm varchar(20);

     v_str varchar(100);

 begin

      f1 := utl_file.fopen('TEST123’,'data.txt','r');

loop

          begin

         utl_file.get_line(f1,v_str);

        v_deptno := substr(v_str,1,instr(v_str,',',1)-1);

        v_deptnm := substr(v_str,instr(v_str,',',1)+1);

    insert into dept_demo(dnum,dname) values(v_deptno,v_deptnm);

   exception

            when no_data_found then

              exit;

       end;

     end loop;

      utl_file.fclose_all;

end;

/

declare

   f1 utl_file.file_type;

 cursor d is select * from dept_demo;

 v_str dept_demo%rowtype;

 v_var varchar(100);

 begin

     f1 := utl_file.fopen('TEST123' , 'data.txt','w');

   open d;

      loop

          fetch d into v_str;

          exit when d%notfound;

         v_var := v_str.dnum ||','||v_str.dname||','

         ||v_str.loc;

          utl_file.put_line(f1,v_var);

 end loop;

 close d;

     utl_file.fclose(f1);

 end;

ff to ff

declare

   fl1 utl_file.file_type;

   fl2 utl_file.file_type;

   v_st varchar(100);

begin

  fl1 := utl_file.fopen('TEST123',

                               'data.txt','R');

  fl2 := utl_file.fopen('TEST123',

                             'cust_trgt.txt','W');

 loop

   begin

       utl_file.get_line(fl1,v_st);

       utl_file.put_line(fl2,v_st);

      exception

         when no_data_found then

         exit;

   end;

 end loop;

utl_file.fclose_all;

end;

/

SQL Loader

SQL LOADER

1. CONTROL FILE.

.  Data File

2  BAD FILE SCENARIO

DATA FILE

BAD FILE:

3.DISCARD FILE SCENARIO

CONTROL FILE

Discard File

Log file

4.TRAILING  NULL COLS

CONTROL FILE

DATA FILE:

5.USING FILLER

6. POSITION BASED DATA FILE(FIXED LENGTH DATA)

CONTROL FILE

DATA FILE

7. Control file without data file

Refcursor Examples

Ref cursors/Cursor variable

Ref cursor is a reference or pointer to cursor

It is explicitly named and can be used for multiple queries

We can open the multiple ref cursors in the same block if one ref cursor is open no need to close before opening the next ref cursor. I will just release the memory of the 1^st ref cursor before using 2^nd ref cursor.  we need to close the ref cursor to release the memory.

Ref cursor can be used in the procedures and functions

Ref cursors can be used as parameters.

They can be used as value and used as an expression.

They can’t accept the parameters

Ref cursor can’t be used in the cursor for loops

They can be strongly typed when the return type is mentioned and weakly typed when the return type is not mentioned.

Advantage of ref cursor is reusability and returns the different data types. It can pass the results of one sub program to another sub program so that it can be used for the centralized data retrieval.

Syntax for Ref Cursor:

TYPE <Ref_cur_name> IS REF CURSOR [RETURN return_type]

Cursor Attributes:

-><cursor>%FOUND

-><cursor>%NOTFOUND

-><cursor>%ISOPEN

-><cursor>%ROWCOUNT

Example for Strongly typed ref cursor:

DECLARE

TYPE  rc_dept is ref cursor return department%rowtype;

Rc_dept_cur  rc_dept;

L_dept_rowtype department%rowtype;

L_id department.deptno%type:=1;

L_dept_id department.deptno%type;

L_dept_name department.dname%type;

BEGIN
OPEN rc_Dept_cur for

Select *from department

Where deptno=L_id;

L_id:=2;

LOOP

FETCH rc_dept_cur into L_dept_rowtype

EXIT  WHEN rc_dept_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(L_dept_rowtype.deptno);

END LOOP;

OPEN rc_dept_cur FOR

SELECT * FROM Department

Where dname=’Accounting’;

LOOP
FETCH rc_dept_cur into L_dept_id,L_dept_name;

EXIT  WHEN rc_dept_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(L_dept_id);

DBMS_OUTPUT.PUT_LINE(L_dept_name);

END LOOP;

CLOSE rc_dept_cur;

END;

USING RECORD TYPE:

DECLARE

TYPE dept_rec IS RECORD(deptno department.deptno%TYPE,dname department.dname%TYPE);

TYPE rc_Dept is REF CURSOR RETURN dept_rec;

rc_dept_cur rc_Dept;

l_dept_rec dept_rec;

l_choice number:=1;

BEGIN

IF l_choice=1 then

OPEN rc_Dept_cur for

 Select deptno,dname from department

Where deptno=20;

Else

Open rc_dept_cur for

Select deptno,dname from department

Where  dname='ACCOUNTING';

End if;

Loop

Fetch rc_dept_cur into l_dept_rec;

Exit when rc_dept_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(L_dept_rec.deptno);

DBMS_OUTPUT.PUT_LINE(L_dept_rec.dname);

End loop;

Close rc_dept_cur;

End;

/

               

Cursor Variables can be assigned and can be used

DECLARE

TYPE  rc_dept is REF CURSOR return department%rowtype;

rc_dept_cur1 rc_dept;

rc_dept_cur2 rc_dept;

l_dept_rowtype department%rowtype;

BEGIN

OPEN rc_dept_cur1 for

Select * from department

Where deptno=10;

rc_dept_cur2:= rc_dept_cur1;

LOOP

FETCH rc_dept_cur2 INTO l_dept_rowtype;

EXIT WHEN rc_dept_cur2%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(l_dept_rowtype.dname);

END LOOP;

CLOSE rc_dept_cur2;

End;

/

WEAK REF CURSOR:

->For weak type ref cursor no need to specify the return type

->They are flexible same cursor variable can be used against the different tables like(dept table or emp table).

->But the trade off is there is no compile time check. there is a possibility of run time error.

->SYS_REFCURSOR predefined week ref cursor.

->weak ref cursor can be assigned to week or strong ref cursor.

Example for weak ref cursor

DECLARE
TYPE rc_weak IS REF CURSOR

Rc_weak_cur rc_week

L_dept_rowtype department%rowtype;

L_emp_rowtype employee%rowtype;

BEGIN

OPEN Rc_weak_cur for

Select * from department

Where deptno=10

Loop

Fetch Rc_weak_cur into L_dept_rowtype

Exit when Rc_weak_cur%NOTFOUND;

DEMS_OUTPUT.PUT_LINE(L_dept_rowtype.deptno);

End loop;

OPEN Rc_weak_cur for

Select * from employee

Where deptno=20

Loop

Fetch Rc_weak_cur into L_emp_rowtype

Exit when Rc_weak_cur%NOTFOUND;

DEMS_OUTPUT.PUT_LINE(L_dept_rowtype.deptno);

End loop;

CLOSE Rc_weak_cur;

END;

Example for weekly typed ref cursor using the SYS_REFCURSOR

DECLARE

TYPE rc_weak IS REF CURSOR;

Rc_weak_cur rc_weak;

Rc_sys_cur SYS_REFCURSOR;

L_dept_rowtype department%rowtype;

L_lower number:=10;

L_upper number:=30;

BEGIN

OPEN Rc_sys_cur for

'Select * from department

Where deptno BETWEEN :1 and :2' USING L_lower, L_upper;

Rc_weak_cur :=Rc_sys_cur;

Loop

FETCH Rc_weak_cur INTO L_dept_rowtype;

EXIT  WHEN Rc_weak_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(L_dept_rowtype.dname);

END LOOP;

CLOSE Rc_weak_cur;

END;

/

ALTISOURCE QUESTION interview

ALTISOURCE QUESTION

2^ND ROUND

1.       BRIEF ME ABOUT YOURSELF

2.       WHAT RDBMS you know

3.       WHAT IS DIFFERENCE BETWEEN SQL & PLSQL

4.       DIFFERENCE BETWEEN TRUNCATE & DELETE

5.       IF I WANT TO UPDATE & INSERT INTO EMP TABLE FROM EMP_TEST TABLE WHAT I SHOULD DO

6.       WHAT IS RANK FUNCTIONS

7.       WRITE SINGLE QUERY USE ALL RANK FUNCTIONS FOR EMP TABLE

8.       WHAT ARE INDEXES

9.       TYPES OF INDEXES . EXPLAIN IT

10.   WHAT IS BITMAP INDEX HOW IT IS STORED

11.   TYPES OF JOIN

3^RD ROUND

1.       BRIEF ME ABOUT YOUR TECHNICAL

2.       WAQ                            I/P                                                                               O/P

EMP ID	SAL
1	18000
2	20000
3	9000
NEXT SAL
20000
9000

 

ID
1	A
1	B
1	C
2	D
2	E
3	F
3	G

3.       WAQ                                  I/P                                            O/P

ID
1	A,B,C
2	D,E
3	F,G

EMAIL

SOWMYA.B@GMAIL.COM

SOWMYA.B2@GMAIL.COM

4.       WRITE A PROCEDURE  I/P                  O/P     

FIRST NAME	LAST NAME
SOWMYA	B
SOWMYA	B

5.       I HAVE A EMP_TEMP TABLE I WANT TO INSERT THE RECORDS & OLD RECORDS SHOULD BE WITH DEACTIVATED FLAG

EMP ID	ENAME	SAL
1	SMITH	18000
2	JOHN	20000
3	TIM	9000
EMP ID	ENAME	SAL	DEACTIVATED
1	SMITH	18000	D
2	JOHN	20000	D
3	TIM	9000	D
4	SMITH	18000
5	JOHN	20000
6	TIM	9000

ID		VALUE
1	HOST
1	PORT
1	SID

4^TH ROUND

1.       I/P                                                              O/P

ID	DATABASE NAME
1	ORACLE
2	MS SQL
3	DB2

2.       WHAT IS V$ SESSION & V$VIEW

3.       DIFFERENCE BETWEEN ROWID & ROWNUM

4.       BREIF ABOUT YOUR PROJECT

5.       WHAT YOU DID YESTERDAY IN OFFICE

6.       HOW COMPLEX QUERY YOU CAN WRITE