Aggregator transformation without selecting group by

This is one of the commonly asked interview question in Informatica. What will be the outpyt when we don't select any port for group by?

Aggregator Transformation in Informatica

When Integration service start processing data for aggregator transformation , it first divide input data into multiple group based on the group by port. It start performing aggregate calculations for each row under that group and in the end it will contains last row for that group along with aggregated values ( say sum , avg etc).

When you are not selecting group by option the Aggregator treats the whole data as a single group and in that case you will get the last record, Because if you select the group by in any column so it will return the last record for that particular column so in the same way you will get the Last record when you don't check on group by option in Any column.

I found a detailed article on "Aggregator transformation without selecting group by" topic here.

you can also check some other example of Aggregator Transformation in Informatica 

Informatica Unconnected Lookup Transformation

Role Playing dimension in Data warehouse

In last  few tutorial , we started talking about various Type of dimension in Data ware house  and what role they play in data warehouse.Last time  we talked about Conformed dimension and Junk Dimension . Data warehouse mainly consists of Dimension and Fact tables.   In below article we will go through the Role Playing Dimension in dataware house with some example , why they are important.Hope you will enjoy this small data warehouse tutorial.

Role PlayingDimension in Data warehouse

A dimension is Role playing dimension , with multiple valid relationships between itself and another table . This is most commonly seen in dimensions such as Time and Customer.

For example :In below Order Fact Table  , it has multiple relationships to the Date dimension on the keys Order_date ,shipping_date. Now to handle this situation instead of creating two separate dimension table we can create two views of original date dimensions table one is Order_date_dim and another is ship_date_dim

 

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

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

• Conformed dimension 
• Junk dimension
• Degenerate Dimension

Degenerate dimension in Data warehouse

In last  few tutorial , we started talking about various Type of dimension in Data ware house  and what role they play in data warehouse.Last time  we talked about Conformed dimension and Junk Dimension . . Data warehouse mainly consists of Dimension and Fact tables.   In below article we will go through the Degenerate Dimension in dataware house with some example , why they are important.Hope you will enjoy this small data warehouse tutorial.

Degenerate Dimension in Data warehouse

A Degenerate dimension is a Dimension which has only a single attribute. This dimension is typically represented as a single field in a fact table.The data items thar are not facts and data items that do not fit into the existing dimensions are termed as Degenerate Dimensions.

 For example : In below Fact Table with customer_id, product_id, bill_no, date in key section and price, quantity in measure section. In this fact table, bill_no from key section is a single value, it has no associated dimension table. Instead of creating a  separate dimension table for that single value, we can include it in fact table to improve performance. So here the column, bill_no is a degenerate dimension or line item dimension. -

 

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

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

• Conformed dimension 
• Junk dimension
• Role Playing Dimension

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 .