SCD Type 1 with example

SCD Type 1:

In this method the new record will replace the old record and there is no tracking for history.

Example:

In the below table we have an employee named as Anil bearing the the EID 503 and Address as JNTU. Same details reflected in target table/warehouse table. 

Source table: 

Target Table:

Now we have updated the Address JNTU as KPHB for the employee Anil. In this SCD Tpye 1 there is no history tracking, so it will update the target table record with new value.

Below is the updated target table.

Advanteges:

• This is the most simple way to implement SCD in our warehouse, because we no need to store the previous details.

Disadvantages:

• We will loose all the previous history and not possible to track the previous details. In above example, once the target table data updated, the employer couldn't find the previous address of the employee Anil. This is the major disadvantage in SCD Type1.

If we are sure, there is no need to maintain the history of the dimension table, then we can go for this SCD Type1 method.

Please leave a comment, if you have any question or need any clarifications. 

What is SCD and Types of SCD

What is Slowly Changing Dimension:

Slowly changing dimension used to determine, how the historical changes are handled in warehouse tables.
Consider an example, where the employee address is being changed and the employer want to keep the previous address of the employee, that is the situation where SCD comes into picture. we will discuss in detail about SCD in types of SCD.

Types of SCD:

There are mainly 3 types of slowly changing techniques are there. Please click on them to explore.

SCD Type 1

SCD Type 2

SCD Type 3

ODI 11g Installation on windows 7 64 bit

Before we are going to install ODI 11g, we should create the repositories by using RCU (Repository configuration utility).

Repositories creation by using RCU :

Download the RCU setup file from OTN and extract it.

Double click on the RCU file

Please click on the next.

This is the very first time, we are going to create the repositories, so select the Create radio button and click on next.

Provide your database connection details, where you want to create your schemas( repositories)

Click on ignore and proceed.

it will automatically check for the prerequisites. click on OK.

Please select prefix as your wish, here I selected prefix as DEV.
And choose the oracle data integrator.

click on ok.

Provide the proper details as shown in below.

Once you provided the Password and other details click on Next.

Click Next.

Click on OK.

RCU creating the required table spaces.

Click on Create.

Now we are done with repository creation for ODI 11G.

DAC 11G Configuration steps on windows 7

After successful installation of DAC. We need to configure DAC to work properly.

Click on DAC client.

Click on Configure button.

Choose the Create Connection radio button.

Enter the proper details as shown in above.

When click on Authentication file, choose create a new authentication file as shown in above.

Provide the destination path and click on ok.

Provide the proper credentials to generate an Encryption key. click on Generate Random Encryption Key.

Please note down that encryption key, will be useful in further steps.

Click on Test connection.

we have successfully established the connection.

Then again click on DAC client and provide the valid login details. Choose authentication type as DAC.

Provide the tables space if you want, otherwise click on OK.

Please provide your DAC repository user, which we have created early. Click on OK

Give the user name and password as administrator.

That's all !!! we are done with DAC configuration. Just we need to start the server and create containers and play with that all. :) :)

Informatica 9.1 Repository configuration screenshots.

Informatica Repository Configurations:

first change the environmental variables as mentioned below. so may not get any know issues while configuring the repository.

 

 

 

 

After changing the environmental variables, click on start button and click on informatica repository and then informatica administration page. Please look at the below screen shot.

Then you will navigate to a web page, there enter your Administrator details. 

Conformed dimension

conformed dimension:

A conformed dimension is a dimension that should has the same meaning and content when being referred to different fact tables. It can be refer to any number of fact tables in multiple data marts.If two dimension tables to be considered as conformed, they must be identical to each other. There shouldn't be any other type of difference between the two dimension tables.

Why Conformed Dimension?

As we discussed in characteristics of Data warehouse, it should maintain integrity. It is nothing but tight integration between different type of dimension tables. Conformed dimensions help us to make the tightly coupled integration between different dimensions

other wise it will lead to stove pipe data warehouse.

Example:

Customer dimension is the well know example for conformed dimension, because sales and product facts can be refer to the same dimension for different purpose. Because the customer who are using our product will be available in customer dimension and the list of customer who purchased our product is also available in same customer dimension.

If you have any quires please make comment.

Data warehousing basics

What is Data Warehouse:
Data warehouse is a relational database which is used to store as well as analyze the historical data of an organization(s). It usually contains the historical data derived from transactional data of single source system or different source systems. The main purpose of the data warehouse is to serve the reporting needs of higher management.

We can say simply in other words, Data warehouse is a database which contains the integrated data of the different source systems.

Characteristics of a data warehouse: 

1) Subject Oriented: A data warehouse can be used to analyze the particular subject area like HR,Sales,Marketing.

2) Integrated: As we discussed data warehouse is collection of data from different data source, so it should avoid the conflicts like different data types,different type of metrics like that.
Suppose data is collecting from different countries, in this case each country have its own currency standards like rupee,dolor,euro etc.. but when data warehouse storing this data it should store in unique format.

3) Nonvolatile:  As name indicates, once data entered into data warehouse it should not be changed. Because we are using data warehouse for historical data analysis.

4) Time Variant: A data warehouse's focus on change over time is what is meant by the term 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.

DAC 11G Installation on Windows 7 64 Bit:

DAC 11G Installation on Windows 7 64 Bit:

1) Download DAC 11g from oracle official site. (OTN)

2) Then extract the ZIP folder.

3) Open the DAC folder then double click on the DAC setup.exe

4) Click on Next.

5) Provide the root directory of informatica powercenter.

6) Provide the proper path for domin.infa file as shown in above screenshot.

7) Select the destination path for DAC installation folder. Make sure there is no spaces between the folder name, otherwise it will cause installation error futrher.

8) Select any radio button as your wish, and then click on next.

9) Once check the Pre-Installation summary and click on Install button.

10) Installation in progress, it will take a while.

11)  We have installed the DAC software successfully. :) :)

Now it's time to configure the DAC.

Click here for DAC configuration.