SCD Type 1, SCD Type 2, SCD Type 3,Slowly Changing Dimension Types,Advantages & Disadvantages

The Slowly Changing Dimension problem is a common one particular to data warehousing.There are in general three ways to solve this type of problem, and they are categorized as follows:

• Type 1: The new record replaces the original record. No trace of the old record exists. Read more
• Type 2: A new record is added into the customer dimension table.Thereby, the customer is treated essentially as two people. Read more
• Type 3: The original record is modified to reflect the change. Read more

SCD Type 3,Slowly Changing Dimension Use,Example,Advantage,Disadvantage

In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.

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

Customer Key	Name	State
1001	Williams	New York

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

• Customer Key
• Name
• Original State
• Current State
• Effective Date

After Williams moved from New York to Los Angeles, the original information gets updated, and we have the following table (assuming the effective date of change is February 20, 2010):

Customer Key	Name	Original State	Current State	Effective Date
1001	Williams	New York	Los Angeles	20-FEB-2010

Advantages

• This does not increase the size of the table, since new information is updated.
• This allows us to keep some part of history.

Disadvantages

• Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Williams later moves to Texas on December 15, 2003, the Los Angeles information will be lost.

Usage

Type 3 is rarely used in actual practice.

When to use Type 3

Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.

You might also like:

SCD Type 2,Slowly Changing Dimension Use,Example,Advantage,Disadvantage

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

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

Customer Key	Name	State
1001	Williams	New York

After Williams moved from New York to Los Angeles, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Williams	New York
1005	Williams	Los Angeles

Advantages

• This allows us to accurately keep all historical information.

Disadvantages

• This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
• This necessarily complicates the ETL process.

Usage

About 50% of the time.

When to use Type 2

Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

SCD Type 1,Slowly Changing Dimension Use,Example,Advantage,Disadvantage

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	Williams	New York

After Williams moved from New York to Los Angeles, the new information replaces the new record, and we have the following table:

Customer Key	Name	State
1001	Williams	Los Angeles

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 Williams lived in New York 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.

Application Roles in OBIEE 11g

Default OBIEE Application Roles
Application Role	LDAP Group	Description
BIConsumer	BIConsumers	Base-level role that grants the user access to OBIEE analyses, dashboards and agents.
BIAuthor	BIAuthors	All BIConsumer rights, grants and permissions + allows users to create new analyses, dashboards and other BI objects
BIAdministrator	BIAdministrators	All BIAuthor and BIConsumer rights, grants and permissions + allows the user to administer all parts of the system.

By default, OBIEE 11g have three application roles:

• BIConsumer : The base-level role that grants the user access to existing analyses, dashboards and agents, allows them to run or schedule existing BI Publisher reports, but not create any new ones. That means user can view the existing reports and dashboards.
• BIAuthor : A role that allows users to create new analyses, dashboards and other BI objects. This role will recursively granted the privileges of BIConsumer role.
• BIAdministrator : This role has the privileges of BIAuthor &BIConsumer roles. In addition to that this role allows the user to administer all parts of the system, including modifying catalog permissions and privileges. This user is the most privileged user in OBIEE environment.

In some cases, a need may arise to add another role that suits well between the BIConsumer and BIAuthor roles; one called BIAnalyst, that allows users to create and edit analyses, but not creates new dashboards.

How to display the reporting pattern in an organization in ORACLE

There is provision to display the reporting pattern in an organization.  In the select query we can use the level (pseudocolumn), which returns 1 for a root row and 2 for a child of a root and so on.The start with clause specifies the root of the rows. Connect by specifies relationship between the parent and the child prior clause is required for a hierarchical query.

Example:

[Walking The Tree Bottom To Up]

SQL> select level,ename,job,mgr from emp start with job='PRESIDENT' connect by prior mgr=empno;

     LEVEL ENAME      JOB              MGR

     ---------- ---------- --------- ----------

         1 KING       PRESIDENT

[Walking The Tree Top To Bottom]

SQL> select ename ,' reports to ', prior ename    "walks top down" from emp  start with ename='KING' connect by prior empno=mgr;

ENAME      'REPORTSTO'  walks top

---------- ------------ ----------

KING        reports to

JONES       reports to  KING

SCOTT       reports to  JONES

ADAMS       reports to  SCOTT

FORD        reports to  JONES

SMITH       reports to  FORD

BLAKE       reports to  KING

ALLEN       reports to  BLAKE

WARD        reports to  BLAKE

MARTIN      reports to  BLAKE

TURNER      reports to  BLAKE

ENAME      'REPORTSTO'  walks top

---------- ------------ ----------

JAMES       reports to  BLAKE

CLARK       reports to  KING

MILLER      reports to  CLARK

14 rows selected.

SQL> select ename ||' reports to '||prior ename    "walks top down" from emp start with ename='KING' connect by prior empno=mgr;

walks top down

--------------------------------

KING reports to

JONES reports to KING

SCOTT reports to JONES

ADAMS reports to SCOTT

FORD reports to JONES

SMITH reports to FORD

BLAKE reports to KING

ALLEN reports to BLAKE

WARD reports to BLAKE

MARTIN reports to BLAKE

TURNER reports to BLAKE

walks top down

--------------------------------

JAMES reports to BLAKE

CLARK reports to KING

MILLER reports to CLARK

14 rows selected.

SQL>  select level ,ename ||' reports to '||prior ename    "walks top down" from emp start with ename='KING' connect by prior empno=mgr;

     LEVEL walks top down

---------- --------------------------------

         1 KING reports to

         2 JONES reports to KING

         3 SCOTT reports to JONES

         4 ADAMS reports to SCOTT

         3 FORD reports to JONES

         4 SMITH reports to FORD

         2 BLAKE reports to KING

         3 ALLEN reports to BLAKE

         3 WARD reports to BLAKE

         3 MARTIN reports to BLAKE

         3 TURNER reports to BLAKE

     LEVEL walks top down

---------- --------------------------------

         3 JAMES reports to BLAKE

         2 CLARK reports to KING

         3 MILLER reports to CLARK

14 rows selected.

How to Unlock the Locked Table in ORACLE

Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.

SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that 'EMP' table is locked,

SELECT object_id FROM dba_objects WHERE object_name='EMP';

 OBJECT_ID

----------

   7401242

If there are no locks present for the table 'EMP' this query won’t return any values.

SELECT sid FROM v$lock WHERE id1=7401242

SID

----------

   3434

SELECT sid, serial# from v$session where sid=3434

       SID    SERIAL#

---------- ----------

      3434      92193

ALTER SYSTEM KILL SESSION '3434,92193' ;

Once the session is killed you will be able to carry out any DDL activities on EMP table. Also you can check in TOAD if there are any active sessions associated to the SID that we killed, to make sure that the session has been killed.

Display a list of dashboards based on permissions OBIEE11g

A way to display a list of dashboards based on permissions :
   
Create a text box on a dashboard page. enable HTML check box in the text box.copy below html/js code to the text box. just save and run the dashboard you can see the list of Dashboards.

<img id="loading" src="/analytics/res/sk_blafp/catalog/loading-indicator-white.gif" />
<div id="dash_list"></div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
<script type="text/javascript">
$.ajaxSetup({
    beforeSend:function(){
        $("#loading").show();
    },
    complete:function(){
        $("#loading").hide();
    }
});
$.ajax({
url: "saw.dll?getDashboardList"
}).done(function( data ) {
    var start = data.indexOf('[');
    var end = data.lastIndexOf(']');
    var len = end-start+1;
    var json_str = data.substr(start,len);
    var json_obj = jQuery.parseJSON(json_str);
    var str = 'You have access to the following dashboards:<br/> <table align="left" border=1>';
   var loopend=0;

str+='<tr>';
    $.each(json_obj, function() {

        if (loopend%5==0)
{
str+='</tr>';
str+='<tr>';
}
loopend++;
        if (this.folderName!=='Welcome'){
            str += '<td valign="top" width="15%" height="15%" ><div style="float:left;margin:5px 10px;";><b>' + this.folderName + '</b><br\>';
            $.each(this.portals, function() {
                    str += '<a href="saw.dll?Dashboard&PortalPath=' + this.portalPath + '">' + this.portalName + '</a><font color="red"> / </font>';
              });
            str += '</div></td>';
        }
    });
str+='</tr>';  
str+='</table>'
$('#dash_list').html(str);
});
</script>