Slowly Changing Dimension (SCD)

Slowly changing dimensions are the dimensions in which the data changes slowly, rather than changing regularly on a time basis.

For example, you may have a customer dimension in a retail domain. Let say the customer is in India and every month he does some shopping. Now creating the sales report for the customers is easy. Now assume that the customer is transferred to United States and he does shopping there.

There are many approaches how to deal with SCD. The most popular are: 

  Type 0 - The passive method

  Type 1 - Overwriting the old value

  Type 2 - Creating a new additional record

  Type 3 - Adding a new column

  Type 4 - Using historical table

  Type 6 - Combine approaches of types 1,2,3 (1+2+3=6)

Type 0 - The passive method. In this method no special action is performed upon dimensional changes. Some dimension data can remain the same as it was first time inserted, others may be overwritten. 

How to record such a change in your customer dimension?
You could sum or average the sales done by the customers. In this case you won't get the exact comparison of the sales done by the customers. As the customer salary is increased after the transfer, he/she might do more shopping in United States compared to in India. If you sum the total sales, then the sales done by the customer might look stronger even if it is good. You can create a second customer record and treat the transferred customer as the new customer. However this will create problems too.

Handling these issues involves 3 main SCD management methodologies.They are:

Slowly changing dimension Type 1

Slowly changing dimension Type 2

Slowly changing dimension Type 3

Slowly changing dimension Type 1: SCD type 1 methodology is used when there is no need to store historical data in the dimension table. This method overwrites the old data in the dimension table with the new data. It is used to correct data errors in the dimension.

As an example, i have the customer table with the below data.

surrogate_key  customer_id   customer_name   Location

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

           1                      1                  Marspton           Illions

Here the customer name is misspell. It should be Marston instead of Marspton. If you use type1 method, it just simply overwrites the data. The data in the updated table will be.

surrogate_key  customer_id   customer_name   Location

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

           1                      1                  Marston           Illions

The advantage of type1 is ease of maintenance and less space occupied. The disadvantage is that there is no historical data kept in the data warehouse.

Slowly changing dimension Type 2: SCD type 2 stores the entire history the data in the dimension table. With type 2 we can store unlimited history in the dimension table. In type 2, you can store the data in three different ways. They are

• Versioning
• Flagging
• Effective Date

SCD Type 2 Versioning: In versioning method, a sequence number is used to represent the change. The latest sequence number always represents the current row and the previous sequence numbers represents the past data.

As an example, let’s use the same example of customer who changes the location. Initially the customer is in Illions location and the data in dimension table will look as.

surrogate_key  customer_id   customer_name   Location   Version

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

           1                      1                  Marston             Illions           1

The customer moves from Illions to Seattle and the version number will be incremented. The dimension table will look as

surrogate_key  customer_id   customer_name   Location   Version

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

           1                      1                  Marston             Illions           1

           2                      1                  Marston            Seattle         2

Now again if the customer is moved to another location, a new record will be inserted into the dimension table with the next version number.

SCD Type 2 Flagging: In flagging method, a flag column is created in the dimension table. The current record will have the flag value as 1 and the previous records will have the flag as 0.

Now for the first time, the customer dimension will look as.

surrogate_key  customer_id   customer_name   Location   Flag

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

           1                      1                  Marston             Illions          1

Now when the customer moves to a new location, the old records will be updated with flag value as 0 and the latest record will have the flag value as 1.

surrogate_key  customer_id   customer_name   Location   Version

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

           1                      1                 Marston             Illions           0
           2                      1                 Marston            Seattle         1

SCD Type 2 Effective Date: In Effective Date method, the period of the change is tracked using the start_date and end_date columns in the dimension table.

surrogate_key  customer_id   customer_name   Location   Start_date        End_date

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

        1                      1                 Marston              Illions       01/03/2010   20/02/2011

        2                      1                 Marston             Seattle      21/02/2011       NULL

The NULL in the End_Date indicates the current version of the data and the remaining records indicate the past data.

Slowly changing dimension Type 3: In type 3 method, only the current status and previous status of the row is maintained in the table. To track these changes two separate columns are created in the table. The customer dimension table in the type 3 method will look as

surrogate_key  customer_id   customer_name   Current_Location   previous_location

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

               1                       1                 Marston              Illions                       NULL

Let say, the customer moves from Illions to Seattle and the updated table will look as

surrogate_key  customer_id   customer_name   Current_Location   previous_location

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

               1                       1                  Marston              Seattle                    Illions

Now again if the customer moves from seattle to NewYork, then the updated table will be

surrogate_key  customer_id   customer_name   Current_Location   previous_location

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

               1                       1                  Marston              NewYork                 Seattle

The type 3 method will have limited history and it depends on the number of columns you create. 

Type 4 - Using historical table. In this method a separate historical table is used to track all dimension's attribute historical changes for each of the dimension. The 'main' dimension table keeps only the current data e.g. customer and customer_history tables.

Current table: 

Customer_ID	Customer_Name	Customer_Type
1	Cust_1	Corporate

Historical table: 

Customer_ID	Customer_Name	Customer_Type	Start_Date	End_Date
1	Cust_1	Retail	01-01-2010	21-07-2010
1	Cust_1	Oher	22-07-2010	17-05-2012
1	Cust_1	Corporate	18-05-2012	31-12-9999

Type 6 - Combine approaches of types 1,2,3 (1+2+3=6). In this type we have in dimension table such additional columns as:

  current_type - for keeping current value of the attribute. All history records for given item of attribute have the same current value.

  historical_type - for keeping historical value of the attribute. All history records for given item of attribute could have different values.

  start_date - for keeping start date of 'effective date' of attribute's history.

  end_date - for keeping end date of 'effective date' of attribute's history.

  current_flag - for keeping information about the most recent record.
In this method to capture attribute change we add a new record as in type 2. The current_type information is overwritten with the new one as in type 1. We store the history in a historical_column as in type 3. 

Customer_ID	Customer_Name	Current_Type	Historical_Type	Start_Date	End_Date	Current_Flag
1	Cust_1	Corporate	Retail	01-01-2010	21-07-2010	N
2	Cust_1	Corporate	Other	22-07-2010	17-05-2012	N
3	Cust_1	Corporate	Corporate	18-05-2012	31-12-9999	Y

Logical and Physical Joins in OBIEE

In simple terms I would say most of the times Logical join is created in BMM layer and Physical join is created in physical layer,but there might be some scenarios where we need to create complex join in physical layer and vice versa, these will be discussed later.

What is Logical Join

Below diagram is window of complex join . In here ,you can see that we can't write any expression in expression pane.however ,we can change the type of join and can define the driving table and set the cardinality which means logical join helps BI server to determines only the relationship between the table , how these table are connected it doesn't tell BI server that what physical columns are joining it just tell that what type of join is between tables.

Physical Joins

Whereas,Physical join helps BI server to understand that how to join two tables by specifying physical columns.Below diagram is  window of physical join.In here,as you can see in expression pane we can define that how these two table should be join to each other

In short, Logical join helps to define the relationship between two tables and what type of join is b/w two tables. Whereas, Physical join helps to understand that how two tables are joined.

We use complex join in physical layer mostly in below scenarios.

1 ) When we have to join key column of one table to non key column of other table.
2 ) When the operator is other than equal to operator. 

Here comes a interview question

we already have joins in Physical layer then why we need to create logical joins in BMM layer or question comes in this way that why we need to create logical joins :

The answer is whenever a user runs a report logical query are generated based on the logical joins in BMM layer as OBIEE server understands logical query only.

Other reasons for creating logical joins are

1) Type of join can be specify through logical join only.  

2) Cardinality can set only in logical join.

3) Driving table only in logical join.
4) When the operator is other than the equal " = " operator.

Let's look at complex join in physical layer. 

Although it doesn't happen frequently, it is sometimes needed. Let's say we have 2 tables, promotion fact and contract date dimension. I want to join these 2 tables in such way so that only the dates that are still in contract should return. Therefore, I can't just use a simple join on the date columns from both tables, conditions need to be applied.. In this case, let's use complex join in physical layer:

In the below diagram, I enter 'PTS_DATES.COMPANYDATEID >= PTS_STAR_FACTS.CONTRACTSTARTDATEID AND PTS_DATES.COMPANYDATEID <= PTS_STAR_FACTS.CONTRACTENDDATEID' to satisfy the joining condition. At the front end, when you run a report using these tables, this expression will be included in the where clause of the SQL Statement:

Having physical join in BMM layer is also acceptable, however it is very rare to see that happen. The purpose of having physical join in BMM layer is to override the physical join in physical layer. It allows users to define more complex joining logic there than they could use physical join in physical layer, in other words, it works similar to complex join in physical layer.

Write Back Setting in OBIEE 11G

Write Back is the ability to enter values directly into a report and have those values written in the database & used in calculations and charts in the report.
Please follow the steps listed below to configure Write Back in OBIEE 11g.

A- Repository Level Changes in all 3 layers to set Write Back Column:

Step 1: Physical Layer - From Physical Table Properties Go to-> General tab dialog box. Uncheck Cacheable

Step 2: Business Model & Mapping Layer - Logical Column Properties Go to-> General tab. Check Writable option.

Step 3: Presentation Layer - Presentation Column Properties Go to-> General tab -> Permissions -> Set permission -> Read / Write (Radio Button), on the User / Application Role which you want to authenticate for Write Back feature.

Step 4: Presentation Layer - Go to -> Manage-> Identity Manager -> Identity Management (Left pane) -> Select Application Role (to which you need Write Back Permission) -> Permission -> Query Limits (tab) -> Select Database (to which you need direct database execution rights) -> Change option from ignore to allow to the field named "Execute Direct Database Requests".

Now we are done with Repository level changes for Write Back column. Let us proceed with other changes.

B- Changes in the File Level:

Step 1: Enable Write Back. Add the LightWriteback tag within the server instance tag of instanceconfig.xml file  ( file path - $ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1 )

<LightWriteback>true</LightWriteback>

If this entry already exists then no modifications required, else restart OracleBIPresentationServicesComponent for this change to be effective.

Step 2: Write Back Template. You may give any name for the writeback.xml file. Here for example i am using the file name as writeback_sample.xml

Things to do before you start the Template:

• Identify the columns that are to be referenced. 
• We can use the column position or by column id in the XML definition. 
• We must include both insert and update statements in the template.

Template to be placed in the path:

$ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

Write-Back Template example: Name - eg. writeback_sample.xml
=================================================
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="WriteBack_Template_Name">
      <XML>
         <writeBack connectionPool="Connection_pool_name">
            <insert>INSERT INTO Customer VALUES('@1','@2',@3,'@4',@5)</insert>
            <update>UPDATE Customer set Address='@2' where Name='@1'</update>
<!-- Identify the columns that are to be referenced. We can use the column position like @1 for first column, or by column id as you see them in the XML definition. -->
         </writeBack>
      </XML>
   </WebMessage>
==================================================

[ Note - We need to mention insert & update statements based on our requirement. Above insert & update code is only for sample purpose & content within <!-- #### --> are comments. If we do not want to include SQL commands within the elements, then we must insert a blank space between the opening and closing tags. eg. <insert> </insert> rather than <insert></insert>]

In the above xml file...
Important Tags -

• WebMessage name - The name here will be used as WriteBack template name in analysis -> Table Properties -> Write Back.
• Write Back connectionPool - Connection pool name for Write Back.
• insert - Based on your requirement or leave blank ( if not required)
• update - Based on your requirement or leave blank ( if not required)

Now we are done with changes in the files. Lets proceed further & know what changes we need to make in analysis for the writeback report...

C- Changes in Analytics:

Step 1: Give Privilege to the Role for Write Back (Administration -> Manage Privilege -> Write Back & add the role for write back privilege). 

Step 2: Now you need to edit Column Properties -> WriteBack -> Check 'Enable Write Back' of the field needed for writeback, from criteria tab.

Step 3: Now edit Table Properties view -> writeback & check 'Enable Write Back' & mention Template Name as mentioned in writeback file ( WebMessage name="WriteBack_Template_Name" ). You may also rename Apply, Revert & Done Button & change Button Position if required.

You are now done with all WriteBack settings. You may test the report for set writeback features.

Chronological key

Chronological key is the key which is uniquely identifies the data at particular level whereas logical key is the key which is used to define the unique elements in each logical level.

Logical Level can have more than one key. When that is the case, specify the key that is primary of that level.

All other dimensions don’t care about the order of the values in it.

e.g. In Region_Dim the values are north, south, west and east. Here nobody wants to see whether north comes first or south comes first. i.e. no order is required here.

In the case of Time Dimension there needs to be a particular order for all the values present in it.

e.g. 2010 is earliest and 2004 is older. Dec-10 is earliest and jan-10 is older. i.e. the values in the time dimension needs to follow a particular sorting order. So the chronological key is the key which tells the OBIEE that the data is incrementing based on the chronological column.

For defining a dimension to be a Time dimension, we need to have a chronological Key.

How to find the users list in oracle 11g db

SELECT * from tab;

SHOW user;

SELECT * from dba_users;

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

SELECT * FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;  ------------system

SELECT * FROM NLS_DATABASE_PARAMETERS; -------------- HR user

Data Warehouse Concept

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process.

Subject-Oriented: A data warehouse can be used to analyze a particular subject area. For example, "sales" can be a particular subject.

Integrated: A data warehouse integrates data from multiple data sources. For example, source A and source B may have different ways of identifying a product, but in a data warehouse, there will be only a single way of identifying a product.

Time-Variant: Historical data is kept in a data warehouse. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older data from a data warehouse. This contrasts with a transactions system, where often only the most recent data is kept.

For example, a transaction system may hold the most recent address of a customer, where a data warehouse can hold all addresses associated with a customer.

Non-volatile: Once data is in the data warehouse, it will not change. So, historical data in a data warehouse should never be altered.

How many stages in Data warehousing?

Data warehouse generally includes two stages

1. ETL

2. Report Generation

ETL

Short for extract, transform, load, three database functions that are combined into one tool

·         Extract — the process of reading data from a source database.

·         Transform — the process of converting the extracted data from its previous form into required form.

·         Load — the process of writing the data into the target database.

ETL is used to migrate data from one database to another, to form data marts and data warehouses and also to convert databases from one format to another format

It is used to retrieve the data from various operational databases and is transformed into useful information and finally loaded into Data warehousing system

1.     Informatica

2.     Abinito

3.     Datastage

4.     Bodi

5.     Oracle Warehouse Builders

Report generation

In report generation, OLAP is used (i.e.) online analytical processing. It is a set of specification which allows the client applications in retrieving the data for analytical processing

It is a specialized tool that sits between a database and user in order to provide various analyses of the data stored in the database.

OLAP Tool is a reporting tool which generates the reports that are useful for Decision support for top level management

1.     Business Objects

2.     Cognos

3.     Micro strategy

4.     Hyperion

5.     Oracle Express

6.     Microsoft Analysis Services

 

What are the types of datawarehousing?

EDW (Enterprise data warehousing)

·         It provides a central database for decision support throughout the enterprise

·         It is a collection of DATAMARTS

DATAMART

·         It is a subset of Data warehousing.

·         It is a subject oriented database which supports the needs of individuals depts. in an organizations.

·         It is called high performance query structure.

·         It supports particular line of business like sales, marketing etc…

ODS (Operational data store)

·         It is defined as an integrated view of operational database designed to support operational monitoring

·         It is a collection of operational data sources designed to support Transaction processing

·         Data is refreshed near real-time and used for business activity

·         It is an intermediate between the OLTP and OLAP which helps to create an instance reports.

 

What are the types of Approach in DWH?

Top down approach: first we need to develop EDW then form that EDW we develop data mart

OLTP  —>  ETL  —>  DWH  —>  Data mart  —>  OLAP

Advantages 

·         Cost of initial planning & design is high

·         Takes longer duration of more than an year

Bottom up approach: first we need to develop data mart then we integrate these data mart into EDW

OLTP  —>  ETL  —>  Data mart  —>  DWH  —>  OLAP

Advantages 

·         Planning & Designing the Data Marts without waiting for the Global warehouse design

·         Immediate results from the data marts

·         Tends to take less time to implement

·         Errors in critical modules are detected earlier.

·         Benefits are realized in the early phases.

·         It is a Best Approach.

. 

Why need staging area for DWH?

1. Staging area needs to clean operational data before loading into data warehouse.

2. Cleaning in the sense your merging data which comes from different source.

3. it’s the area where most of the ETL is done

Data Cleansing

·         It is used  to remove duplication’s

·         It is used to correct wrong email addresses

·         It is used to identify missing data

·         It used to convert the data types

·         It is used to capitalize name & addresses. 

Types of systems

Data Mart: - A data mart is a simple form of a data warehouse that is focused on a single subject (or functional area), such as Sales, Finance, or Marketing. Data marts are often built and controlled by a single department within an organization. Given their single-subject focus, data marts usually draw data from only a few sources. The sources could be internal operational systems, a central data warehouse, or external data.

• It is a high performance query structure(HPQS)
• Fast retrieval of data is possible through Datamart

Differences between a Data Warehouse and a Data Mart

Category	Data Warehouse	Data Mart
Scope	Corporate	Line of Business (LOB)
Subject	Multiple	Single subject
Data Sources	Many	Few
Size (typical)	100 GB-TB+	< 100 GB
Implementation Time	Months to years	Months

Metadata is data that describes other data. Meta is a prefix that in most information technology usages means "an underlying definition or description." Metadata summarizes basic information about data, which can make finding and working with particular instances of data easier.

Data integration means combining Data coming from different sources and providing users with a unified view of these data.

Dimensional vs. normalized approach for storage of data

There are three or more leading approaches to storing data in a data warehouse — the most important approaches are the dimensional approach and the normalized approach.

The dimensional approach, whose supporters are referred to as “Kimballites”, believe in Ralph Kimball’s approach in which it is stated that the data warehouse should be modeled using a Dimensional Model/star schema. The normalized approach, also called the 3NF model (Third Normal Form), whose supporters are referred to as “Inmonites”, believe in Bill Inmon's approach in which it is stated that the data warehouse should be modeled using an E-R model/normalized model.

In a dimensional approach, transaction data are partitioned into "facts", which are generally numeric transaction data, and "dimensions", which are the reference information that gives context to the facts. For example, a sales transaction can be broken up into facts such as the number of products ordered and the price paid for the products, and into dimensions such as order date, customer name, product number, order ship-to and bill-to locations, and salesperson responsible for receiving the order.

Features of Data Warehouse:

• A DWH is designed to support decision making process. Hence it is known as Decision Support System(DSS)
• It analyzes the business transactions in order to support decision making
• DWH is a container data
• DWH is the process of developing a data warehouse
• DWH is a read only database because it is designed to read the data for analysis but not for transactional processing
• DWH is a historical database because it can store historical business information
• Father of Data warehousing   W.H.Inmon. In 1987, he designed a data warehouse.