Junk dimension in Data warehouse

In last tutorial , we have gone through some of the basic concept of data warehouse , what exactly data warehouse store ,importance of data warehouse. We also saw various properties of  data warehouse. Data warehouse mainly consists of Dimension and Fact tables. We will go through different type of dimension mainly used in data warehouse in upcoming articles.  In below article we will go through the Junk Dimension in dataware house with some example , why they are important.Hope you will enjoy this small data warehouse tutorial.

Junk Dimension in Data warehouse

A junk dimension is grouping of low cardinality flags and indicators. This junk dimension helps in avoiding cluttered design of data warehouse. Provides an easy way to access the dimensions from a single point of entry and improves the performance of sql queries.

 For example : For example, assume that there are two dimension tables (gender and marital status). The data of these two tables are shown below:

I found a good article on junk dimension in data ware house here

I also found some good article on other various type of dimension as well.

• Conformed dimension 
• Degenerate dimension
• Role Playing Dimension

Conformed dimension in Datawarehouse with Example

In last tutorial , we have gone through some of the basic concept of data warehouse , what exactly data warehouse store ,importance of data warehouse. We also saw various properties of  data warehouse. Data warehouse mainly consists of Dimension and Fact tables. We will go through different type of dimension mainly used in data warehouse in upcoming articles.  In below article we will go through the Conformed Dimension in dataware house with some example , why they are important.Hope you will enjoy this small data warehouse tutorial.

Conformed Dimension in Data warehouse

In dataware house , conformed Dimension is the dimension which has the same meaning and content when being referred from different fact tables. A conformed dimension can refer to multiple tables in multiple data marts within the same organization. For example : Time is a common conformed dimension because its attributes (day, week, month, quarter, year, etc.) have the same meaning when joined to any fact table. Similarly Customer dimension will have the same meaning irrespective of which FACT table we are referring to.

I found a good article on conformed dimension in data ware house here

I also found some good article on other various type of dimension as well.

• Junk dimension 
• Degenerate dimension
• Role Playing Dimension

Data Warehousing Concepts with Examples

A data warehouse is the concept of data extracted from operational systems and made available as historical snapshots for ad-hoc queries and scheduled reporting. Data warehouse help in determining the effectiveness of business processes, create policy, forecast trends, analyze the market and much more . Below snapshot  gives simple view how data warehouse data is prepared and provide the reporting capabilities to Business analysts.


Data warehouse example

 See also : I found a good article on the difference Difference between Data warehouse data and Operational Data 

Key Features :

A common way of introducing data warehousing is to refer to the characteristics of a data warehouse as set forth by William Inmon:

• Subject Oriented:
• Integrated
• Nonvolatile
• Time Variant :

For more  detailed article on this data warehouse concept , do visit here.  I hope you like this small introductory article on data warehouse concept .

Dynamic Cache in Informatica Lookup Transformation

As we saw in our previous informatica tutorial , that  look transformation cache need to be enabled to boost the performance of Lookup transformation( by avoiding lookup in the lookup source again and again)

Now if Look up source is getting changed , then we  need to also refresh lookup cache. Dynamic cache in lookup Transformation solves our purpose  :).

Dynamic cache:

If you want to cache the target table and insert new rows into cache and the target,you can create a look up transformation to use dynamic cache.The informatica server dynamically inserts data to the target table. 

 

1. You cannot share the cache between a dynamic Lookup transformation and static Lookup transformation in the same target load order group.
2. You can create a dynamic lookup cache from a relational table, flat file, or source qualifier transformation.
3. The Lookup transformation must be a connected transformation.
4. Use a persistent or a non-persistent cache.
5. If the dynamic cache is not persistent, the Integration Service always rebuilds the cache from the database, even if you do not enable Re-cache from Lookup Source.
6. When you synchronize dynamic cache files with a lookup source table, the Lookup transformation inserts rows into the lookup source table and the dynamic lookup cache. If the source row is an update row, the Lookup transformation updates the dynamic lookup cache only.
7. You can only create an equality lookup condition. You cannot look up a range of data in dynamic cache.

Informatica Powercenter Architecture

After so many Informatica tutorial  on so many topics ( transformation  , tuning of transformation), question arises , what exactly is happening behind the scene. So time has come to discuss about architecture of Informatica in details. We will discuss about the various component of Informatica architure , various services and how they are interlinked to each other. Hope you will enjoy this Informatica tutorial. By the way , it is one of the most  asked interview question in Informatica.

 

Component of Informatica Architecture

Domain: Domain is the primary unit for management and administration of services in Powercenter. The components of domain are one or more nodes, service manager an application services.

Node: Node is logical representation of machine in a domain. A domain can have multiple nodes. Master gateway node is the one that hosts the domain. You can configure nodes to run application services like integration service or repository service. All requests from other nodes go through the master gateway node.

Service Manager: Service manager is for supporting the domain and the application services. The Service Manager runs on each node in the domain. The Service Manager starts and runs the application services on a machine.

Application services: Group of services which represents the informatica server based functionality. Application services include powercenter repository service, integration service, Data integration service, Metadata manage service etc.

Powercenter Repository: The metadata is store in a relational database. The tables contain the instructions to extract, transform and load data.

Powercenter Repository service: Accepts requests from the client to create and modify the metadata in the repository. It also accepts requests from the integration service for metadata to run workflows.

Powercenter Integration Service: The integration service extracts data from the source, transforms the data as per the instructions coded in the workflow and loads the data into the targets.

Informatica Administrator: Web application used to administer the domain and powercenter security.

Metadata Manager Service: Runs the metadata manager web application. You can analyze the metadata from various metadata repositories.

I also found a good article on Informatica Architecture here 

Informatica Interview question with answer for lookup Transformation

In below article , we will go through the some the Informatica Interview question based on the lookup transformation in Informatica. In last tutorial , we have gone through the detailed explanation of lookup Transformation.
You can find a good details of Lookup transformation here.

We also checked some of the example of connected and unconnected lookup transformation also.

Lets go through some of the Informatica Interview question on Lookup transformation.

Check here : Difference connected and unconnected Lookup Transformation here .

Que: What is a pipeline lookup transformation??

Ans :A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookups source.

Que: What are the different type of Sources for Lookup ?

Ans :

• Relational source or target definition in the repository
• Flat file source or target definition in the repository
• Table or file that the Integration Service and PowerCenter Client machine can connect to
• Source qualifier definition in a mapping

Ques. What is a lookup transformation?
Ans : A lookup transformation is used to look up data in a flat file, relational table, view, and synonym.

Ques: What are the tasks of a lookup transformation?

Ans : The lookup transformation is used to perform the following tasks?

• Get a related value: Retrieve a value from the lookup table based on a value in the source.
• Perform a calculation: Retrieve a value from a lookup table and use it in a calculation.
• Update slowly changing dimension tables: Determine whether rows exist in a target.

Ques: . What is "Output Old Value on Update"?
Ans : This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

Ques: . How do you configure a lookup transformation?

Ans : Configure the lookup transformation to perform the following types of lookups:

• Relational or flat file lookup
• Pipeline lookup
• Connected or unconnected lookup
• Cached or uncached lookup

Ques: What are the options available to configure a lookup cache?
Ans : The following options can be used to configure a lookup cache:

• Persistent cache
• Recache from lookup source
• Static cache
• Dynamic cache
• Shared Cache
• Pre-build lookup cache

Check here : Difference Static Cache and dynamic Cache  in Informatica here .

I also found a good article on Informatica Interview question on Lookup Transformation  here and here

Slowly Changing Dimension Type 2 Examples (SCD 2)

  SCD Type-2 In Informatica[/caption] Slowly Changing  Dimension Type-2 ,(also known as SCD -2)  tracks historical changes  by  keeping multiple records for a given natural key in the dimensional tables .For example , we may need to track the current location of a supplier along with its previous location just to track his sales in different region .
 Example of SCD Type -2

For Example : If we want to keep track of DEPT of an employee , we can add two extra fields , Say Start-date , End_date to keep track of those records: EMP_SCD2

SRG_KEY	EMPNO	NAME	DEPT	START_DATE	END_DATE
101	1	JOHNSON	OPERATIONS	12/17/1990	4/13/1992
102	2	HARDING	ACCOUNTING	2/2/1998	9/17/1998
103	3	TAFT	RESEARCH	1/2/1996
104	4	HOOVER	SALES	4/2/1990	7/11/1993
105	1	JOHNSON	ACCOUNTING	4/13/1992
106	2	HARDING	SALES	9/17/1998
107	4	HOOVER	OPERATIONS	7/11/1993
108	5	LINCOLN	OPERATIONS	6/23/1994

As you see in above example , we can keep track of records by adding effective _date fields . Other way to accomplish this process is to

• Effective Date
• Flagging
• Versioning

I found a good article on "Slowly Changing Dimension Type 2 Examples (SCD 2)"  here.  and full implementation of SCD Type -2 in Informatica can be found here Other good article on SCD are as below :-

• Slowly Changing Dimension Type 2 Examples (SCD 2) 
• SCD Type 2 implementation in Informatica with example
• Slowly Changing Dimension Type 3 Examples (SCD 3)
• SCD Type 3 implementation in Informatica with example
• Slowly Changing Dimension Type 6 Examples (SCD6)
• SCD Type 6 implementation in Informatica with example

  Hope you enjoyed this small and useful article on SCD Type -2 (Slowly changing dimension type -2 ) and example of SCD Type-2 In Informatica  :)