What is PL/SQL?

What is PL/SQL?

PL/SQL stands for Procedural Language extension of SQL.

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.


The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

     
About This PL SQL Programming tutorial

This Oracle PL SQL tutorial teaches you the basics of programming in PL/SQL with appropriate examples. You can use this tutorial as your guide or reference while programming with PL SQL. I will be making this Oracle PL SQL programming tutorial as often as possible to share my knowledge in PL SQL and help you in learning PL SQL better.

Even though the programming concepts discussed in this tutorial is specific to Oracle PL SQL. The concepts like cursors, functions and stored procedures can be used in other database systems like Sybase , Microsoft SQL server etc, with some change in syntax. This tutorial will be growing regularly; let us know if any topic related to PL SQL needs to be added or you can also share your knowledge on PL SQL with us. Lets share our knowledge about PL SQL with others.
A Simple PL/SQL Block:

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

A PL/SQL Block consists of three sections:

    * The Declaration section (optional).
    * The Execution section (mandatory).
    * The Exception (or Error) Handling section (optional).

Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

This is how a sample PL/SQL Block looks.

DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;

SOURCE:http://www.plsql-tutorial.com/

Oracle Programming in PL/SQL

Programming in PL/SQL

Databases have been in use long before the personal computer arrived on the scene.  IBM developed the Structured Query Language standard (dubbed SQL, and pronounced “See-Quel”) over 30 years ago as a way to retrieve data from their new “relational” database.  A decade later, Oracle released the first commercial relational database that used SQL, and SQL has become the de-facto query language for the vast majority of popular database products.

For the complete story, we recommend the book “Easy Oracle PL/SQL Programming”.  Once you have mastered basic SQL you are ready for the advanced book “Oracle PL/SQL Tuning” by Dr. Timothy Hall.

Even though SQL is the standard language for interacting with any modern database, it does not mean that SQL is without limitations.  If we want to retrieve a set of records from the database and modify them according to a set of rules, (updating some and returning to the calling program others), we can’t do this with a single SQL call to the database.  Complex processing requires the ability to compare values (often called Boolean logic) and implement programmatic flow control.   In other words, some type of programming language was required to process the returned rows and implement the program rules.  To achieve this capability, Oracle introduced the Procedural Language extensions to the Structured Query Language or PL/SQL.

Oracle PL/SQL was based on the ADA programming language which was developed by the Department of Defense to be used on mission critical systems.  Although not a “sexy” language like Java or C, ADA is still being develop and used for applications such as aircraft control systems.  ADA is a highly structured, strongly typed programming language that uses natural language constructs to make it easy to understand.  The PL/SQL language inherited these attributes making PL/SQL easier to read and maintain than more cryptic languages such as C.  For example, below are two loops, one in PL/SQL and the other in a programming language called C.
for x in v_start..v_finish --PL/SQL
loop
   v_int := v_int +1;
end loop;


As opposed to:

for (x = str; x< fin; x++) {i++}  --C

As we see, the PL/SQL statement is more verbose but also easier to understand.

PL/SQL is also portable within the Oracle database family and runs on all supported Oracle platforms including Oracle10g grid database. Even more important is platform independence, where programs developed in PL/SQL on a Windows Oracle database will load and run in a UNIX Oracle database.  With each release of the Oracle database, Oracle Corporation enhances the capabilities and performance of PL/SQL.  Remember, PL/SQL is an Oracle only product, and no other database management system will run PL/SQL.

Unlike other languages that execute externally, PL/SQL executes inside the database.  This means that you can take advantage of PL/SQL’s exceptional ability to manipulate data in the database without paying the network penalty of retrieving the data out of the database and them updating it back to the database.  Because PL/SQL runs inside the database it takes advantage of the capabilities and capacity of the database server.

Traditionally, PL/SQL has been a compiled/interpreted language similar to Java.   When PL/SQL code is loaded into the database it is compiled into an intermediate form, similar to the way Java is compiled into byte-code.  This intermediate code is portable across Oracle databases.  Later versions of Oracle (Oracle9i and 10g) will compile PL/SQL into native code for over 60 hardware platforms.  This natively compiled code runs more efficiently, but it loses the ability to move to other Oracle databases without recompiling.

Lastly, placing the code that interacts with the database in PL/SQL makes better use of the database resources.  PL/SQL packages are loaded as a package so as your program calls for data, the procedures and functions are already in cached memory.  Using PL/SQL will also result in your application using bind variables.  The only way not to use bind variables in PL/SQL is to implement dynamic SQL (discussed in Chapter 5).  The Database Administrator (DBA) also benefits when developers place their SQL inside PL/SQL because they have access to the statements for tuning.  For example the DBA can tune the SQL (adding hints, reordering the WHERE clause) without impacting the existing application.  Placing SQL inside PL/SQL also allows the code to be protected by the recovery capabilities of the Oracle database.

PL/SQL is the most common language for Oracle the world, and developers are realizing the benefits in both application performance and database performance by implementing the database interaction in PL/SQL.  There are even websites built entirely using PL/SQL.  For example, Oracle’s HTML-DB product is installed in the database and consists primarily of PL/SQL packages and Java scripts.
PL/SQL Basic Structure

Like the ADA programming language, PL/SQL is based on blocks, and   PL/SQL provides a number of different blocks for different uses.  The characteristics of a block include:

* A block begins with a declarative section where variables are defined.

* This is followed by a section containing the procedural statements surrounded by the BEGIN and END key words.  Each block must have a BEGIN and END statement, and may optionally include an exception section to handle errors.  The exception section is covered later in the book.

Here is an example of a simple block:
SQL> declare
  2    v_line varchar2(40);
  3  begin
  4    v_line := 'Hello World';
  5    dbms_output.put_line (v_line);
  6  end;
  7  /

Hello World

In the example above, the variable v_line is defined in the declarative section on line 2.  Like SQL statements, each line ends with a semicolon.  Once v_line is defined, it can be used in the procedural section.  First, v_line is assigned the literal string ‘Hello World’ on line 4.  Strings are surrounded by single quotes in SQL and PL/SQL.  The v_line variable is then placed in the output buffer using the procedure dbms_output.put_line.

In PL/SQL, the semicolon defines the end of a line of code.  To execute the PL/SQL block, use the forward slash “/” on a line by itself as shown on line 7.    If you forget the forward slash, SQL*Plus will simply wait for the next line to be entered.

Note:  If you execute a PL/SQL script and SQL*Plus returns a number, it probably is an indication that you forgot to place the”/” at the end of your script.  SQL*Plus is actually waiting for the next line.  Entering a “/” will execute the script.

A PL/SQL block with no name is called an anonymous block.  It starts with the declare key word to define the declarative section.
declare
    …  define variables here
begin
    …  code goes here
exceptions
end;

A named block is a procedure or a function.  The name portion defines the declarative section so the DECLARE key word is not used.
create procedure my_proc
as
    …  define variables here
begin
    …  code goes here
exceptions
end;

A procedure can be passed and change variables.  A function can be passed variables and must return a variable.
create function my_func (v_name varchar2)return number
as
    …  define variables here
begin
    …  code goes here
    return n_jobNum;
end;

When variables are passed to a procedure or function they can be IN, OUT or INOUT.  An IN variable is passed into the procedure or function and is used, but can not be changed.  An OUT variable is passed to the procedure but it can be changed and left in the changed state when the procedure ends.

An INOUT variable is passed to a procedure or function, and it can be used by the block, changed by the block, and left in a “changed” state when the block ends.  A function can only be passed an IN variable and must return a variable.  If this is confusing, don’t worry.  When we get to name blocks and provide some examples it will be much clearer.

SOURCE:http://www.dba-oracle.com/concepts/programming_pl_sql.htm

PL-SQL Interview Questions with Answers

1. Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.

2. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

4. What packages (if any) has Oracle provided for use by developers?
Expected answer: Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

5. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

6. When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the NOTFOUND cursor variable in the exit when statement? Why?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can
also be used.

11. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of
combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and
ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc. 

source:http://www.indiastudychannel.com/resources/8518-PL-SQL-Interview-Questions-with-Answers.aspx

PL/SQL interview qiuestions

1. Which of the following statements is true about implicit cursors?
   1. Implicit cursors are used for SQL statements that are not named.
   2. Developers should use implicit cursors with great care.
   3. Implicit cursors are used in cursor for loops to handle data processing.
   4. Implicit cursors are no longer a feature in Oracle.
   
   
2. Which of the following is not a feature of a cursor FOR loop?
   1. Record type declaration.
   2. Opening and parsing of SQL statements.
   3. Fetches records from cursor.
   4. Requires exit condition to be defined.
3. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
   1. Use employee.lname%type.
   2. Use employee.lname%rowtype.
   3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
   4. Declare it to be type LONG.
4. Which three of the following are implicit cursor attributes?
   1. %found
   2. %too_many_rows
   3. %notfound
   4. %rowcount
   5. %rowtype
5. If left out, which of the following would cause an infinite loop to occur in a simple loop?
   1. LOOP
   2. END LOOP
   3. IF-THEN
   4. EXIT
6. Which line in the following statement will produce an error?
   1. cursor action_cursor is
   2. select name, rate, action
   3. into action_record
   4. from action_table;
   5. There are no errors in this statement.
7. The command used to open a CURSOR FOR loop is
   1. open
   2. fetch
   3. parse
   4. None, cursor for loops handle cursor opening implicitly.
8. What happens when rows are found using a FETCH statement
   1. It causes the cursor to close
   2. It causes the cursor to open
   3. It loads the current row values into variables
   4. It creates the variables to hold the current row values
9. Read the following code:
   

   CREATE OR REPLACE PROCEDURE find_cpt
   (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
   IS
   BEGIN
     IF v_cost_per_ticket  > 8.5 THEN
   SELECT  cost_per_ticket
   INTO            v_cost_per_ticket
   FROM            gross_receipt
   WHERE   movie_id = v_movie_id;
     END IF;
   END;

   Which mode should be used for V_COST_PER_TICKET?
   
   1. IN
   2. OUT
   3. RETURN
   4. IN OUT
10. Read the following code:
    

    CREATE OR REPLACE TRIGGER update_show_gross
          {trigger information}
         BEGIN
          {additional code}
         END;

    The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
    
    1. WHEN (new.cost_per_ticket > 3.75)
    2. WHEN (:new.cost_per_ticket > 3.75
    3. WHERE (new.cost_per_ticket > 3.75)
    4. WHERE (:new.cost_per_ticket > 3.75)
11. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
    1. Only one
    2. All that apply
    3. All referenced
    4. None
12. For which trigger timing can you reference the NEW and OLD qualifiers?
    1. Statement and Row
    2. Statement only
    3. Row only
    4. Oracle Forms trigger
13. Read the following code:
    

    CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
    RETURN number IS
    
    v_yearly_budget NUMBER;
    
    BEGIN
           SELECT  yearly_budget
           INTO            v_yearly_budget
           FROM            studio
           WHERE   id = v_studio_id;
    
           RETURN v_yearly_budget;
    END;

    Which set of statements will successfully invoke this function within SQL*Plus?
    
    1. VARIABLE g_yearly_budget NUMBER
       EXECUTE g_yearly_budget := GET_BUDGET(11);
    2. VARIABLE g_yearly_budget NUMBER
       EXECUTE :g_yearly_budget := GET_BUDGET(11);
    3. VARIABLE :g_yearly_budget NUMBER
       EXECUTE :g_yearly_budget := GET_BUDGET(11);
    4. VARIABLE g_yearly_budget NUMBER
       :g_yearly_budget := GET_BUDGET(11);

14. CREATE OR REPLACE PROCEDURE update_theater
    (v_name IN VARCHAR v_theater_id IN NUMBER) IS
    BEGIN
           UPDATE  theater
           SET             name = v_name
           WHERE   id = v_theater_id;
    END update_theater;

    When invoking this procedure, you encounter the error:
    

    ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.

    How should you modify the function to handle this error?
    
    1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
    2. Handle the error in EXCEPTION section by referencing the error code directly.
    3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
    4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
15. Read the following code:
    

    CREATE OR REPLACE PROCEDURE calculate_budget IS
    v_budget        studio.yearly_budget%TYPE;
    BEGIN
           v_budget := get_budget(11);
           IF v_budget < 30000
      THEN
                   set_budget(11,30000000);
           END IF;
    END;

    You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
    
    1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
    2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
    3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
    4. All three procedures are marked invalid and must be recompiled.
16. Which procedure can be used to create a customized error message?
    1. RAISE_ERROR
    2. SQLERRM
    3. RAISE_APPLICATION_ERROR
    4. RAISE_SERVER_ERROR
17. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
    1. ALTER TRIGGER check_theater ENABLE;
    2. ENABLE TRIGGER check_theater;
    3. ALTER TABLE check_theater ENABLE check_theater;
    4. ENABLE check_theater;
18. Examine this database trigger
    

    CREATE OR REPLACE TRIGGER prevent_gross_modification
    {additional trigger information}
    BEGIN
           IF TO_CHAR(sysdate, DY) = MON
     THEN
     RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
           END IF;
    END;

    This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
    
    1. BEFORE DELETE ON gross_receipt
    2. AFTER DELETE ON gross_receipt
    3. BEFORE (gross_receipt DELETE)
    4. FOR EACH ROW DELETED FROM gross_receipt
19. Examine this function:
    

    CREATE OR REPLACE FUNCTION set_budget
    (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
    BEGIN
           UPDATE  studio
           SET             yearly_budget = v_new_budget
           WHERE   id = v_studio_id;
    
           IF SQL%FOUND THEN
                   RETURN TRUEl;
           ELSE
                   RETURN FALSE;
           END IF;
    
           COMMIT;
    END;

    Which code must be added to successfully compile this function?
    
    1. Add RETURN right before the IS keyword.
    2. Add RETURN number right before the IS keyword.
    3. Add RETURN boolean right after the IS keyword.
    4. Add RETURN boolean right before the IS keyword.
20. Under which circumstance must you recompile the package body after recompiling the package specification?
    1. Altering the argument list of one of the package constructs
    2. Any change made to one of the package constructs
    3. Any SQL statement change made to one of the package constructs
    4. Removing a local variable from the DECLARE section of one of the package constructs
21. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
    1. When the transaction is committed
    2. During the data manipulation statement
    3. When an Oracle supplied package references the trigger
    4. During a data manipulation statement and when the transaction is committed
22. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
    1. DBMS_DISPLAY
    2. DBMS_OUTPUT
    3. DBMS_LIST
    4. DBMS_DESCRIBE
23. What occurs if a procedure or function terminates with failure without being handled?
    1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
    2. Any DML statements issued by the construct are committed
    3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
    4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
24. Examine this code
    

    BEGIN
           theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
    END;

    For this code to be successful, what must be true?
    
    1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
    2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
    3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
    4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
25. A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
    1. DBMS_DDL
    2. DBMS_DML
    3. DBMS_SYN
    4. DBMS_SQL

Slowly Changing Dimensions: A Data Warehouse Administrator’s Ongoing Challenge

A key pillar of data mart building capabilities is a dimensional framework. Ralph Kimball, a leading data warehouse consultant, describes building data marts with shared or conformed dimensions as a very effective method for rolling out an enterprise solution. For the business user, dimensions such as "customer" or "location" can be described in terms of the business rules that an organization needs to define in order to make decisions about their business (for example, "customers by location" or "sales over time"). Building data marts with common definitions delivers a consistent view of the data. The industry’s most effective ETL solutions are able to define dimensions once and make them available for reuse in subsequent data marts. This best practice methodology quickly facilitates an enterprise roll out of subsequent data marts. A dimensional framework allows data mart designers to quickly prototype and deploy a data mart solution using its dimensional framework.
Tracking Changes Over Time

In the real world, though, many dimensions change over time. But what happens to a dimension when, for example, a customer or product dimension changes? Tracking changes in dimensions, referred to as slowly changing dimensions, can raise interesting questions. Data mart designers need to add new customers or change the addresses of existing customers, the company assigns new regional hierarchies, or product descriptions and packaging change. These sorts of changes need to be reflected in the data mart dimension tables. In several cases, not only does the change need to be reflected, but the history of the changes also needs to be tracked. By remembering history, businesses are able to look at historical data and compare it to their current situation.
Slowly Changing Dimensions

The issues of maintaining the history of this data has profound implications for the design of the data warehouse. Effective ETL solutions are able to simplify the support for Type 1 and Type 2 (as per Ralph Kimball terminology) slowly changing dimensions through automated dimensional management. Furthermore, these dimensions can contain a mixture of both Type 1 and Type 2 dimensions.


Data mart designers have two choices: they can either allow dimension records to be updated in place (no history preservation), or they can create a new dimension record when changes in designated dimension attributes occur (preserving history). The designer must choose based on the users’ needs.


Figure 1: Type 1 (Overwrite Dimension Records)

The first technique does not maintain any history, but is a simple and fast procedure. Overwriting dimensional records is used when the old values are incorrect or unimportant – when there is no value in keeping the old description. For example, when a sales representative changes her last name due to marriage, there is no business reason to keep the representative’s maiden name in history. Thus, her maiden name can be overwritten.


Figure 2: Type 2 (Add Dimension Record)

Type 2 slowly changing dimensions are used to create a new row of dimension data when there are changes in an attribute whose history needs preserving. For example, do the sales for Mary Smith get credited to the new office when she transfers, or do they stay with the old office? In this case it is necessary to keep the history so Mary’s sales (and her commission check) will be assigned to the correct branch.
Surrogates and Slowly Changing Dimensions

Surrogates refer to artificial keys used to connect a dimension table to a fact table and are always used with slowly changing dimensions. Leading data mart creation tools automatically manage the creation of new surrogates and synchronization of these values in fact and dimension tables. The process of surrogate and dimension history management are now reduced to a set of rules that are associated with the dimension table columns in the warehouse. Once these rules are defined, dimensionally friendly ETL solutions automatically maintain the surrogates and updates or adds dimension table records as necessary when changes are made in the corresponding reference sources in the operational system.

Data mart/warehouse administrators that want to rid themselves of the daily headache of slowly changing dimensions need to look to the automation of this important data warehouse function by simplifying the process of handling slowly changing dimensions. For the data mart designer this translates into the ability to respond to changes and provides an efficient method for tracking history.

Peter Griffiths is vice president of Cognos, Inc.

For more information on related topics, visit the following channels:

    * DW Administration, Mgmt., Performance
    * DW Design, Methodology
    * Databases

SOURCE:http://www.information-management.com/infodirect/20010126/2998-1.html

Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

To kick off our first column of the year, we're going to take on a challenging subject that all designers face: how to deal with changing dimensions. Unlike most OLTP systems, a major objective of a data warehouse is to track history. So, accounting for change is one of the analyst's most important responsibilities. A sales force region reassignment is a good example of a business change that may require you to alter the dimensional data warehouse. We'll discuss how to apply the right technique to account for the change historically. Hang on to your hats — this is not an easy topic.

warehouse design presumes that facts, such as customer orders or product shipments, will accumulate quickly. But the supporting dimension attributes of the facts, such as customer name or product size, are comparatively static. Still, most dimensions are subject to change, however slow. When dimensional modelers think about changing a dimension attribute, the three elementary approaches immediately come to mind: slowly changing dimension (SCD) types 1, 2 and 3.

These three fundamental techniques, described in Quick Study, are adequate for most situations. However, what happens when you need variations that build on these basics to serve more analytically mature data warehouse users? Business folks sometimes want to preserve the historically accurate dimension attribute associated with a fact (such as at the time of sale or claim), but maintain the option to roll up historical facts based on current dimension characteristics. That's when you need hybrid variations of the three main types. We'll lead you through the hybrids in this column.

The Mini Dimension with "Current" Overwrite

When you need historical tracking but are faced with semi-rapid changes in a large dimension, pure type 2 tracking is inappropriate. If you use a mini dimension, you can isolate volatile dimension attributes in a separate table rather than track changes in the primary dimension table directly. The mini-dimension grain is one row per "profile," or combination of attributes, while the grain of the primary dimension might be one row per customer. The number of rows in the primary dimension may be in the millions, but the number of mini-dimension rows should be a fraction of that. You capture the evolving relationship between the mini dimension and primary dimension in a fact table. When a business event (transaction or periodic snapshot) spawns a fact row, the row has one foreign key for the primary dimension and another for the mini-dimension profile in effect at the time of the event.

Profile changes sometimes occur outside of a business event, for example when a customer's geographic profile is updated without a sales transaction. If the business requires accurate point-in-time profiling, a supplemental factless fact table with effective and expiration dates can capture every relationship change between the primary and profile dimensions. One more embellishment with this technique is to add a "current profile" key to the primary dimension. This is a type 1 attribute, overwritten with every profile change, that's useful for analysts who want current profile counts regardless of fact table metrics or want to roll up historical facts based on the current profile. You'd logically represent the primary dimension and profile outrigger as a single table to the presentation layer if doing so doesn't harm performance. To minimize user confusion and error, the current attributes should have column names that distinguish them from the mini-dimension attributes. For example, the labels should indicate whether a customer's marketing segment designation is a current one or an obsolete one that was effective when the fact occurred — such as "historical marital status at time of event" in the profile mini dimension and "current marital status" in the primary customer dimension.


Business analysts need to track changes in dimension attributes. Reevaluation of a customer's marketing segment is an example of what might prompt a change. There are three fundamental techniques.

Type 1 is most appropriate when processing corrections; this technique won't preserve historically accurate associations. The changed attribute is simply updated (overwritten) to reflect the most current value.

    Discover how cloud platform services are raising the stakes when it comes to what you can expect from embracing the cloud as part of your architecture.
    Time To Revisit Your Cloud Strategy

With a type 2 change, a new row with a new surrogate primary key is inserted into the dimension table to capture changes. Both the prior and new rows contain as attributes the natural key (or durable identifier), the most-recent-row flag and the row effective and expiration dates.

With type 3, another attribute is added to the existing dimension row to support analysis based on either the new or prior attribute value. This is the least commonly needed technique.

SOURCE:http://www.informationweek.com/news/software/bi/showArticle.jhtml?articleID=59301280

How to handle Slowly Changing Dimensions (SCDs) in data model design?

There are multiple methods to handle the slowly changing dimensions. Which technique to use depends on your business requirements. The choice among these three methods are not a technical design decision since their behaviors are different.

Type One: Overwite the old data with new data

Using this method, you do not store the histoy. For example, that say each customer can have one salesrep at any given point in time. When the salerep of ABC Inc., changes from Sandy to Laura, Sandy was a salerep of ABC will not be kept anywhere. Any report by salesrep will assume that Laura is the salereps of ABC Inc. forever and count all the sales done by Sandy as Lanura’s.

The above example may not sound making business sense. However, if you only report the sales of the current period, and salesrep does not change during the period, this method is ok to be used.

Mary OLTP tables does not need to track the history of changes and thus this method may be used by the source application. However, if you want to report the historical data, even your OLTP does not track history, the data warehouse can still use other methods to track the history.

Type Two: Add a new record at the timeof the change

Using this method, all priorhistory are saved. There are two alternative methods to model the key of this table.

Method A – No surrogate key – Use timestamp

When a change happens, a new record is added into the table. All the attributes are copied from the previous record except the changed values. The nature key is copied as well so the timestamps is used to differentiate the records.

When a fact table is joined with the dimension, if you are interested in the historical data, the timestamp will be used as part of the join condition. To ease the join, the record typically use two date columns – the effective start date and the effective end date.

Method B – No surrogate key – Use version number

Instead of using the date column, a version number is used to differentiate the different versions of the records.

This technique requires the fact table store both nature key and the version number to retrive a given version of the dimension date.

Method C – Use a surrogate key

When an attribue is change, a sequence generated key is used, the fact table will also use this key column as the foreign key.

Type Three: Track changes using a separate column

Using this method, you use a separate column of dimension table to store the values of previous years, in addition to the current year data.

This method does not track all the history, but just one prior version.

If the data is changed, the old value need to be moved from the current value column to the prior column and the new value overwrites the current column.

This method is used when the changes is not randon but a predefined interval such as annual.

This entry was posted on January 13, 2007 at 10:44 pm and is filed under BI, Data Warehouse, OBIEE, Oracle BI Suite EE, SCD, Siebel Analytics. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

SOURCE:http://dylanwan.wordpress.com/2007/01/13/how-to-handle-slowly-changing-dimensions-scds-in-data-model-design/

Data Warehousing - Slowly Changing DimensionsBy nature of the data warehouse, fact and dimension tables are likely to have very different transactional activity. Fact tables will be mostly inserted into as you load data. Very infrequently you might have to update the facts that were loaded incorrectly. It is even less likely that you'll ever delete rows from the fact table; the only time this might happen is if you wanted to archive the old data that is no longer relevant for the current portion of the data warehouse. Dimension tables, on the other hand are likely to see frequent updates. A classic example of this is a consumer dimension which tracks people that buy your products or services. People can change names due to marriage, divorce or another reason, they can change their title of courtesy from Ms to Mrs or to Dr. As people age they also fit a different age group and perhaps even a different education and income level. Therefore you are likely to change dimension table values often. Dimensions that are changing over time are referred to as slowly changing dimensions (SCD). There are several ways to handle SCD; most common approaches are referred to as type 1, type 2, type 3 and type 4. The easiest way to manage SCD is to simply override the existing value with the new value type 1. Indeed if a department changes the title from "Finance" to "Financial" overwriting such a value isn't likely to do much harm. However, you must weigh the consequences of overriding existing values carefully to ensure that your reports don't lose credibility. For example, suppose Mr. Smith who has near perfect credit marries Ms. Jones who has recently declared bankruptcy. Since Mr. Smith had near perfect credit before his marriage your bank has issued him a loan for $200,000. If you overwrite Mr. Smith's credit score with his new, considerably lower credit score you will make decisions from the not very distant past look very incorrect. If your data analysts happen to run reports immediately before and immediately after the credit rating was changed your data warehouse credibility will be compromised. Here is what type 1 SCD records would look like before and after change: Customer_key Customer_name Customer_city Customer_state 123 Ms. Brown Houston Texas 123 Mrs. Green Houston Texas As you can see type 1 SCD does not allow tracking of changes. Once you overwrite the value all historical reports will display Mrs. Green instead of Ms. Brown. The second and more common way of handling SCD is to create a new record when dimension attributes change - type 2. For example, when Ms. Brown becomes Mrs. Green you create a new record for that customer and mark the first record as obsolete. The second approach adds effective date and obsolete date columns to dimension tables. Doing so allows a data warehouse architect to maintain a history of SCD changes. After a change occurs the warehouse will have the following two records (one of them marked as obsolete): Customer_key Customer_name Customer_city Customer_state Effective_date Obsolete_date 123 Ms.Brown Houston Texas 1/1/2000 1/1/2005 234 Mrs.Green Houston Texas 1/1/2005 NULL The third approach is to add columns to the dimension tables in order to maintain the history of changes in a single row. In the case of consumer dimension you would create an old last name and current last name columns along with the date column that indicates when the change occurred. This type of auditing of changes is typically reserved for very few dimensions where change history tracking is critical. For example if your product prices tend to change over time and you must compare revenues based on old and new price then you might wish to use this method. Type 3 of maintaining SCD carries much overhead and is not used very frequently. After type 3 change the warehouse will still have a single record shown below: Customer_key Customer_name Customer_city Customer_state Change_date Old_name 123 Mrs.Green Houston Texas 1/1/2005 Ms.Brown Most environments that use type 3 slowly changing dimensions track 2 iterations of changes; that is you would record the current version, previous version and version before last for the changed column. Type 4 is a special way of handling type 2 changes. Type 2 usually tracks unlimited history by creating new records each time the change occurs. If you know how far back you wish to track history you can add logic to your code to delete any records that go too far back in history. For example, suppose we created an exam and offered it to our students. As time goes on we might decide to edit the exam. We could manage these edits using type 2 method, but if we accumulate 10 versions of the same exam the data might get confusing. So we might decide that we only store up-to 9 records per each exam; any time 10th version of an exam is created we'll delete the 1st version from the warehouse. SOURCE:http://sqlserverpedia.com/wiki/Data_Warehousing_-_Slowly_Changing_Dimensions

By nature of the data warehouse, fact and dimension tables are likely to have very different transactional activity. Fact tables will be mostly inserted into as you load data. Very infrequently you might have to update the facts that were loaded incorrectly. It is even less likely that you'll ever delete rows from the fact table; the only time this might happen is if you wanted to archive the old data that is no longer relevant for the current portion of the data warehouse. Dimension tables, on the other hand are likely to see frequent updates. A classic example of this is a consumer dimension which tracks people that buy your products or services. People can change names due to marriage, divorce or another reason, they can change their title of courtesy from Ms to Mrs or to Dr. As people age they also fit a different age group and perhaps even a different education and income level. Therefore you are likely to change dimension table values often.



Dimensions that are changing over time are referred to as slowly changing dimensions (SCD). There are several ways to handle SCD; most common approaches are referred to as type 1, type 2, type 3 and type 4.



The easiest way to manage SCD is to simply override the existing value with the new value type 1. Indeed if a department changes the title from "Finance" to "Financial" overwriting such a value isn't likely to do much harm. However, you must weigh the consequences of overriding existing values carefully to ensure that your reports don't lose credibility. For example, suppose Mr. Smith who has near perfect credit marries Ms. Jones who has recently declared bankruptcy. Since Mr. Smith had near perfect credit before his marriage your bank has issued him a loan for $200,000. If you overwrite Mr. Smith's credit score with his new, considerably lower credit score you will make decisions from the not very distant past look very incorrect. If your data analysts happen to run reports immediately before and immediately after the credit rating was changed your data warehouse credibility will be compromised. Here is what type 1 SCD records would look like before and after change:

Customer_key     Customer_name     Customer_city     Customer_state
123     Ms. Brown     Houston     Texas
123     Mrs. Green     Houston     Texas


As you can see type 1 SCD does not allow tracking of changes. Once you overwrite the value all historical reports will display Mrs. Green instead of Ms. Brown.



The second and more common way of handling SCD is to create a new record when dimension attributes change - type 2. For example, when Ms. Brown becomes Mrs. Green you create a new record for that customer and mark the first record as obsolete. The second approach adds effective date and obsolete date columns to dimension tables. Doing so allows a data warehouse architect to maintain a history of SCD changes. After a change occurs the warehouse will have the following two records (one of them marked as obsolete):

Customer_key     Customer_name     Customer_city     Customer_state     Effective_date     Obsolete_date
123     Ms.Brown     Houston     Texas     1/1/2000     1/1/2005
234     Mrs.Green     Houston     Texas     1/1/2005     NULL


The third approach is to add columns to the dimension tables in order to maintain the history of changes in a single row. In the case of consumer dimension you would create an old last name and current last name columns along with the date column that indicates when the change occurred. This type of auditing of changes is typically reserved for very few dimensions where change history tracking is critical. For example if your product prices tend to change over time and you must compare revenues based on old and new price then you might wish to use this method. Type 3 of maintaining SCD carries much overhead and is not used very frequently. After type 3 change the warehouse will still have a single record shown below:

Customer_key     Customer_name     Customer_city     Customer_state     Change_date     Old_name
123     Mrs.Green     Houston     Texas     1/1/2005     Ms.Brown


Most environments that use type 3 slowly changing dimensions track 2 iterations of changes; that is you would record the current version, previous version and version before last for the changed column.



Type 4 is a special way of handling type 2 changes. Type 2 usually tracks unlimited history by creating new records each time the change occurs. If you know how far back you wish to track history you can add logic to your code to delete any records that go too far back in history. For example, suppose we created an exam and offered it to our students. As time goes on we might decide to edit the exam. We could manage these edits using type 2 method, but if we accumulate 10 versions of the same exam the data might get confusing. So we might decide that we only store up-to 9 records per each exam; any time 10th version of an exam is created we'll delete the 1st version from the warehouse.


SOURCE:http://sqlserverpedia.com/wiki/Data_Warehousing_-_Slowly_Changing_Dimensions

Slowly Changing Dimensions SCD in Dimensional Modeling

Slowly Changing Dimensions

Entities change over time. Customer demographics, product characteristics, classification rules, status of customers etc. lead to changes in the attributes of dimensions. In a transaction system, many a times the change is overwritten and track of change is lost.

For example a source system may have only the latest customer PIN Code, as it is needed to send the marketing and billing statements. However, a data warehouse needs to maintain all the previous PIN Codes as well, because we need to track on how many customers move to new locations over what frequency.

A key benefit for Data Warehouse is to provide historical information, which is typically over-written (and thus lost)in the transaction systems. How to handle slowly changing dimensions in a Dimensional Model is a key determinant to that benefit.
There are three ways to handle the same:
Slowly Changing Dimension method 1 (In short SCD 1)

The way most of the source systems will handle it- Overwrite the attribute value. For example if a customer’s marital status has moved from 'Unmarried' to 'Married', we over-write 'unmarried' to 'Married'. Similarly, if an insurance policy status has moved from 'Lapsed' to 'Re-instated' the new status is over written on the old status. This is obviously done, when we are not analyzing the historical information.
Slowly Changing Dimension Method 2 (in short SCD 2)

This is the true-blue technique to deliver precise historical analysis. This is used, when there is more than one change in the attributes of an entity, and we need to track the date of change of the attribute.

In this method, a new record is added whereby the new record is given a separate identifier as the primary key. We cannot use the production key as the primary key here as it has not changed (Customer ID has remained the same, while the value of its attribute 'marital status' has changed). This new identifier is called the surrogate key.

Apart from adding a new record and providing a new primary (surrogate) key, the validity period for this new record is also added.

For example- You have a dimensional table with customer_ID '110002' with marital status as 'single'. Overtime, customer gets married and also moved to a new location. The customer dimension record will be:
Surrogate Key     Customer ID     Date Valid     Marital Status     Date of Birth     City
1100021     110002     Sept 23, 2004     Single     Jan8, 1982     Palo Alto
1100022     110002     Oct 25, 2005     Married     Jan8, 1982     Palo Alto
1100023     110002     Nov 23, 2005     Married     Jan8, 1982     San Francisco
Slowly changing dimension method 3 (SCD 3)

This is a mid-way between method 1 and method 2. Here we don’t add an additional record, but add a new field 'old attribute value'. However, this has limitations. This method has to know from the beginning on what attributes will change. This is because a new field/attribute has to be added in the design for every attribute, which can change. Secondly, attribute can change maximum once in the lifetime of the entity OR at least the lifetime of the data warehouse.
Surrogate Key     Customer ID     Marital Status     Date of Birth     City     Marital Status Old     City Old
1100021     110002     Married     Jan8, 1982     San Francisco     Single     Palo Alto

NOTE – The term of 'Slowly changing dimension' is used because of it being a universally acknowledged term. However, the same methods will apply to fast changing dimensions as well.
Surrogate Keys as Primary keys of dimension tables

There is a best practice in dimensional model design to not to use the production primary key as the primary key for the dimension table. This goes against conventional logic, but has a reason.

Data Warehouse has a core need for maintaining historical information and how an entity has moved and changed shape through the passage of time. Typically Source Systems need for this kind of information is quite less. In case of historical tracking in source systems, these systems can have the luxury of using multiple-field primary key (including the key identifier of the entity plus date stamp). For example if an insurance policy is lapsed and after two months it becomes reinstated, one can use the primary key as a combination of Policy number+ date/time +the status in the 'policy history table'. However, Data Warehouse doesn’t recommend the luxury of using multiple field primary key in dimension table.

Therefore, the concept 'surrogate Key' comes into play where the primary key is not the production key, but a key generated by the system. The production key is also used as an attribute within the same dimension table.
The situations/reasons on when a surrogate key is used:

    * 'Slowly changing dimensions'
    * When the primary key itself is repeated.
    * When there is a multiple field primary key. Dimension model typically does not use multiple field primary key to link to the fact table.

Therefore it is always recommended to use surrogate keys. it is difficult to find the organizations, which will not face the situations as highlighted above. If there are, they could as well manage their needs using excel and pivot tables.

SOURCE:http://www.executionmih.com/data-warehouse/slowly-changing-dimension-SCD.php

Slowly changing dimension

Dimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information. Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.[1]

For example, you may have a dimension in your database that tracks the sales records of your company's salespeople. Creating sales reports seems simple enough, until a salesperson is transferred from one regional office to another. How do you record such a change in your sales dimension?

You could sum or average the sales by salesperson, but if you use that to compare the performance of salesmen, that might give misleading information. If the salesperson that was transferred used to work in a hot market where sales were easy, and now works in a market where sales are infrequent, her totals will look much stronger than the other salespeople in her new region, even if they are just as good. Or you could create a second salesperson record and treat the transferred person as a new sales person, but that creates problems also.

Dealing with these issues involves SCD management methodologies referred to as Type 0 through 6. Type 6 SCDs are also sometimes called Hybrid SCDs.

Type 0

The Type 0 method is a passive approach to managing dimension value changes, in which no action is taken. Values remain as they were at the time the dimension record was first entered. In certain circumstances historical preservation with a Type 0 SCD may occur. But, higher order SCD types are often employed to guarantee history preservation, whereas Type 0 provides the least control or no control over managing a slowly changing dimension.

The most common slowly changing dimensions are Types 1, 2, and 3.
 Type 1

The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all. This is most appropriate when correcting certain types of data errors, such as the spelling of a name. (Assuming you won't ever need to know how it used to be misspelled in the past.)

Here is an example of a database table that keeps supplier information:
Supplier_Key     Supplier_Code     Supplier_Name     Supplier_State
123     ABC     Acme Supply Co     CA

In this example, Supplier_Code is the natural key and Supplier_Key is a surrogate key. Technically, the surrogate key is not necessary, since the table will be unique by the natural key (Supplier_Code). However, the joins will perform better on an integer than on a character string.

Now imagine that this supplier moves their headquarters to Illinois. The updated table would simply overwrite this record:
Supplier_Key     Supplier_Code     Supplier_Name     Supplier_State
123     ABC     Acme Supply Co     IL

The obvious disadvantage to this method of managing SCDs is that there is no historical record kept in the data warehouse. You can't tell if your suppliers are tending to move to the Midwest, for example. But an advantage to Type 1 SCDs is that they are very easy to maintain.

If you have calculated an aggregate table summarizing facts by state, it will need to be recalculated when the Supplier_State is changed.[1]
 Type 2

The Type 2 method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. With Type 2, we have unlimited history preservation as a new record is inserted each time a change is made.

In the same example, if the supplier moves to Illinois, the table could look like this, with incremented version numbers to indicate the sequence of changes:
Supplier_Key     Supplier_Code     Supplier_Name     Supplier_State     Version
123     ABC     Acme Supply Co     CA     0
124     ABC     Acme Supply Co     IL     1

Another popular method for tuple versioning is to add effective date columns.
Supplier_Key     Supplier_Code     Supplier_Name     Supplier_State     Start_Date     End_Date
123     ABC     Acme Supply Co     CA     01-Jan-2000     21-Dec-2004
124     ABC     Acme Supply Co     IL     22-Dec-2004   

The null End_Date in row two indicates the current tuple version. In some cases, a standardized surrogate high date (e.g. 9999-12-31) may be used as an end date, so that the field can be included in an index, and so that null-value substitution is not required when querying.

Transactions that reference a particular surrogate key (Supplier_Key) are then permanently bound to the time slices defined by that row of the slowly changing dimension table. An aggregate table summarizing facts by state continues to reflect the historical state, i.e. the state the supplier was in at the time of the transaction; no update is needed.

If there are retrospective changes made to the contents of the dimension, or if new attributes are added to the dimension (for example a Sales_Rep column) which have different effective dates from those already defined, then this can result in the existing transactions needing to be updated to reflect the new situation. This can be an expensive database operation, so Type 2 SCDs are not a good choice if the dimensional model is subject to change.[1]
 Type 3

The Type 3 method tracks changes using separate columns. Whereas Type 2 had unlimited history preservation, Type 3 has limited history preservation, as it's limited to the number of columns we designate for storing historical data. Where the original table structure in Type 1 and Type 2 was very similar, Type 3 will add additional columns to the tables:
Supplier_Key     Supplier_Code     Supplier_Name     Original_Supplier_State     Effective_Date     Current_Supplier_State
123     ABC     Acme Supply Co     CA     22-Dec-2004     IL

Note that this record can not track all historical changes, such as when a supplier moves twice.

One version of this type is to create the field Previous_Supplier_State instead of Original_Supplier_State which will then track the most recent historical change.[1]
 Type 4

The Type 4 method is usually referred to as using "history tables", where one table keeps the current data, and an additional table is used to keep a record of some or all changes.

Following the example above, the original table might be called Supplier and the history table might be called Supplier_History.
Supplier Supplier_key     Supplier_Code     Supplier_Name     Supplier_State
123     ABC     Acme Supply Co     IL
Supplier_History Supplier_key     Supplier_Code     Supplier_Name     Supplier_State     Create_Date
123     ABC     Acme Supply Co     CA     22-Dec-2004

This method resembles how database audit tables and change data capture techniques function.
 Type 6 / Hybrid

The Type 6 method combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6). One possible explanation of the origin of the term was that it was coined by Ralph Kimball during a conversation with Stephen Pace from Kalido. Ralph Kimball calls this method "Unpredictable Changes with Single-Version Overlay" in The Data Warehouse Toolkit[1].

The Supplier table starts out with one record for our example supplier:
Supplier_Key     Supplier_Code     Supplier_Name     Current_State     Historical_State     Start_Date     End_Date     Current_Flag
123     ABC     Acme Supply Co     CA     CA     01-Jan-2000     31-Dec-9999     Y

The Current_State and the Historical_State are the same. The Current_Flag attribute indicates that this is the current or most recent record for this supplier.

When Acme Supply Company moves to Illinois, we add a new record, as in Type 2 processing:
Supplier_Key     Supplier_Code     Supplier_Name     Current_State     Historical_State     Start_Date     End_Date     Current_Flag
123     ABC     Acme Supply Co     IL     CA     01-Jan-2000     21-Dec-2004     N
124     ABC     Acme Supply Co     IL     IL     22-Dec-2004     31-Dec-9999     Y

We overwrite the Current_State information in the first record (Supplier_Key = 123) with the new information, as in Type 1 processing. We create a new record to track the changes, as in Type 2 processing. And we store the history in a second State column (Historical_State), which incorporates Type 3 processing.

If our example supplier company were to relocate again, we would add another record to the Supplier dimension, and we would once again overwrite the contents of the Current_State column:
Supplier_Key     Supplier_Code     Supplier_Name     Current_State     Historical_State     Start_Date     End_Date     Current_Flag
123     ABC     Acme Supply Co     NY     CA     01-Jan-2000     21-Dec-2004     N
124     ABC     Acme Supply Co     NY     IL     22-Dec-2004     03-Feb-2008     N
125     ABC     Acme Supply Co     NY     NY     04-Feb-2008     31-Dec-9999     Y

Note that, for the current record (Current_Flag = 'Y'), the Current_State and the Historical_State are always the same.[1]
 Type 2 / Type 6 Fact Implementation
 Surrogate Key Alone

In many Type 2 and Type 6 SCD implementations, the surrogate key from the dimension is put into the fact table in place of the natural key when the fact data is loaded into the data repository.[1] The surrogate key is selected for a given fact record based on its effective date and the Start_Date and End_Date from the dimension table. This allows the fact data to be easily joined to the correct dimension data for the corresponding effective date.

Here is the Supplier table as we created it above using Type 6 methodology:
Supplier_Key     Supplier_Code     Supplier_Name     Current_State     Historical_State     Start_Date     End_Date     Current_Flag
123     ABC     Acme Supply Co     NY     CA     01-Jan-2000     21-Dec-2004     N
124     ABC     Acme Supply Co     NY     IL     22-Dec-2004     03-Feb-2008     N
125     ABC     Acme Supply Co     NY     NY     04-Feb-2008     31-Dec-9999     Y

The following SQL retrieves the correct Supplier Surrogate_Key for each Delivery fact record, based on the primary effective date, Delivery_Date:

SELECT
  supplier.supplier_key
FROM supplier
INNER JOIN delivery
  ON supplier.supplier_code = delivery.supplier_code
 AND delivery.delivery_date >= supplier.start_date
 AND delivery.delivery_date <= supplier.end_date

A fact record with an effective date of August 9, 2001 will be linked to Surrogate_Key 123, with a Historical_State of 'CA'. A fact record with an effective date of October 11, 2007 will be linked to Surrogate_Key 124, with a Historical_State of 'IL'.

Once the Delivery table contains the correct Supplier_Key, it can easily be joined to the Supplier table using that key. The following SQL retrieves, for each fact record, the correct supplier name and the state the supplier was located in at the time of the delivery:

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.historical_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_key = supplier.supplier_key

If you've utilized Type 6 processing for your dimension, then you can easily retrieve the state the company is currently located in, using the same Supplier_Key:

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.current_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_key = supplier.supplier_key

 Both Surrogate and Natural Key

An alternate implementation is to place both the surrogate key and the natural key into the fact table.[2] This allows the user to select the appropriate dimension records based on:

    * the primary effective date on the fact record (above),
    * the most recent or current information,
    * any other date associated with the fact record.

This method allows more flexible links to the dimension, even if you have used the Type 2 approach instead of Type 6.

Here is the Supplier table as we might have created it using Type 2 methodology:
Supplier_Key     Supplier_Code     Supplier_Name     Supplier_State     Start_Date     End_Date     Current_Flag
123     ABC     Acme Supply Co     CA     01-Jan-2000     21-Dec-2004     N
124     ABC     Acme Supply Co     IL     22-Dec-2004     03-Feb-2008     N
125     ABC     Acme Supply Co     NY     04-Feb-2008     31-Dec-9999     Y

The following SQL retrieves the most current Supplier_Name and Supplier_State for each fact record:

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.supplier_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_code = supplier.supplier_code
WHERE supplier.current_flag = 'Y'

If there are multiple dates on the fact record, the fact can be joined to the dimension using another date instead of the primary effective date. For instance, the Delivery table might have a primary effective date of Delivery_Date, but might also have an Order_Date associated with each record.

The following SQL retrieves the correct Supplier_Name and Supplier_State for each fact record based on the Order_Date:

SELECT
  delivery.delivery_cost,
  supplier.supplier_name,
  supplier.supplier_state
FROM delivery
INNER JOIN supplier
  ON delivery.supplier_code = supplier.supplier_code
 AND delivery.order_date >= supplier.start_date
 AND delivery.order_date <= supplier.end_date

Some cautions:

    * If the join query is not written correctly, it may return duplicate rows and/or give incorrect answers.

    * The date comparison might not perform well.

    * Some Business Intelligence tools do not handle generating complex joins well.

    * The ETL processes needed to create the dimension table needs to be carefully designed to ensure that there are no overlaps in the time periods for each distinct item of reference data.