Search This Blog

Wednesday, July 25, 2012

Snow Flake Schema

Snow Flake Schema is the Structure which is widely used in Database Design.
The main drawback in Star Schema is it can contain one fact table and many dimension tables but which have to be connected with the fact table .So whenever we need a Subdivision of a Field in Dimension (a Lookup) it will create a problem as it has to be added to the same dimension table which will Reduce the performance of DB.
For eg : A field called State_cd (state code) needs a description field Say State_name , Now this can be given using one more dimension table which links with the Parent dimension using a Pk-Fk relationship(using State_cd)

Let us understand this by using the Below Example:
Here we can see that the item dimension table is further joined with another dimension table called Supplier which gives the Supplier type. So whenever the Supplier type column is needed , we have to join item dimension table with the Supplier Dimension table using the Field called Supplier_Key .


Tuesday, July 24, 2012

Star Schema Example

Star Schema as shown in the above design will be having a single fact table and many dimension tables.The fact table as the name implies contains the facts /measures and also the foreign keys of all the dimension tables.
Above we can see the Fact table : Fact_Sales
It contains the Foreign keys such as Date_id which is connecting to Dim_Date Dimension table , Store_id which is connecting to Dim_Store dimension table and the Product_id which is connecting with the Dim_product dimension table.
Apart from them it has one more field called Units_sold which is having the Sum of the units based on whichever Dimension we join.
So , If we want the units sold based on the Product name then by joining the Dim_Product and Fact_Sales we will get the Sales of units based on that particular Product.
By Joining Dim_Date and Fact_Sales we can get the Number of units sold in a particular time.

Sunday, July 15, 2012

Union Transformation

Lets Learn about one of the often used Transformation in Informatica. Union Transformation , by reading the name we can say that this transformation is going to combine the data's .Yes this transformation is nothing but the Union operator which we will use in Oracle.
pic1
Eg:
Select ename , eno , sal from emp1
union
Select ename2, eno2 , sal2 from emp2

This is just a simple example of how union is implemented in Oracle.

Lets see how the same is implemented in Informatica.
  • Create the Source by selecting the create icon or by importing from Database.
  • Create the target in the target designer by using the create icon or by importing.It can be a table or flatfile.
  • Create the union transformation and link the columns from the Two sources as shown below
  • Create the mappings by linking the Source -> t/f -> Target as shown below
  • Create the Workflow in Workflow Manager by selection the session and giving the proper mapping name.
  • Run the Workflow and check the Status in Workflow Monitor.
The Main point one has to remember here is the Union Transformation in Informatica Works like "UnionAll" of Oracle. 

Friday, July 13, 2012

Creation of Relational Connection!!

The First Question which comes to our mind when we are hearing the Word Relational connection is "How it is different from ODBC Connection".

The Answer is: ODBC connection is nothing but just a mapping to Database (Oracle or SqlServer whichever we use). It is responsible for importing the meta data but in other hand the relational connection is something which holds the data itself, It will fetch the data from Database to Target whichever we select(Target here is Nothing but the Source (in case of importing data) or Target (in case of exporting Data).

Please Find the Below Screen to Know how to create a Relational Connection :
  • Open the Workflow Manager , Connect to the respective Repository & Folder.
  • Click the Connections Menu in the Menu bar.
  • Click on the Relational Connection Option.
  • Now , Click "New".
  • Select the Database , Currently we are working on Oracle , So we will select Oracle in dropdown.
  • Click ok
  • Now Below Screen Will appear
  • In Name Text box , Give any meaningful name to describe the Connection.
  • In the Username and password boxes , please fill appropriate Schema name and password.
  • In connection String box , Fill the Oracle DB name . DB name which was used to create Database.(eg: ORCL)