GeekInterview.com
Series: Subject: Topic:
Question: 111 of 734

Dimension Table Vs Fact Table

What is the main difference in the logic when you create a mapping for a dimension table with that of a fact table in Informatica.
Asked by: goutamhere2interact | Member Since Jun-2008 | Asked on: Jun 18th, 2008

View all questions by goutamhere2interact

Showing Answers 1 - 14 of 14 Answers

Fact table contains numeric facts. i.e., key performence indicatiors. A dimention table is a primary key foregin key relation to fact tbale.

  
Login to rate this answer.
dwhlabs

Answered On : Jul 23rd, 2008

View all answers by dwhlabs

Dimension Table features

1. It provides the context /descriptive information for a fact table measurements.
2. Provides entry points to data.
3. Structure of Dimension - Surrogate key , one or more other fields that compose the natural key (nk) and set of Attributes.

4. Size of Dimension Table is smaller than Fact Table.

5. In a schema more number of dimensions are presented than Fact Table.

6. Surrogate Key is used to prevent the primary key (pk) violation(store historical data).

7. Values of fields are in numeric and text representation.

Fact Table features

1. It provides measurement of an enterprise.

2. Measurement is the amount determined by observation.

3. Structure of Fact Table - foreign key (fk), Degenerated Dimension and Measurements.

4. Size of Fact Table is larger than Dimension Table.

5. In a schema less number of Fact Tables observed compared to Dimension Tables.

6. Compose of Degenerate Dimension fields act as Primary Key.

7. Values of the fields always in numeric or integer form.

 

Yes  2 Users have rated as useful.
  
Login to rate this answer.

Dimention Table - A pure dimention table is a collection of primary keys
Fact Table- A pure fact table is collection of foreign keys.

Yes  1 User has rated as useful.
  
Login to rate this answer.
raj_infadev

Answered On : Aug 7th, 2008

View all answers by raj_infadev

I think there won't be any logic difference in a mapping to load dimension table & fact table. We can load the dimension table directly but we can't load the fact table first. So, to load the fact table we need to load the dimension table first. Also while loading the fact table we will make a lookup on the dimensioin table, cause the fact table contains the measures/facts & the foreign keys which are primary keys in the dimension tables surrounded to that fact table. We can load the dimension table & fact table in one mapping by using the "Target Load Order/Target Load Plan" in informatica.

Example:

/ Target 1 (Dimension Table)
Source - Transformations -
Target 2 (Fact Table)

etc.....

Yes  4 Users have rated as useful.
  
Login to rate this answer.

The main difference between dimension and the fact table is that Dimension preserves the historical data (like in case of type2) we will have to use update strategy and other transformations to make that happen but fact will be a direct load with few one or more lookups from the dimension and also since the fact and dimenision has the foriegn key relationship the dimension has to be loaded first before the fact

  
Login to rate this answer.
Kadhirvelu

Answered On : Oct 23rd, 2008

View all answers by Kadhirvelu

Dimensions are particular angle or perspective that you see the facts i.e aggregates or measures. as mentioned in previous replies, facts cannot be loaded unless until dimensions got loaded.

  
Login to rate this answer.
sahan

Answered On : Jul 16th, 2009

View all answers by sahan

You can load the dimension table directly, but you cannot load the fact table directly, You need to look up the dimension table, b'coz fact tables contains foreign keys, which are the primary keys in dimension table. You can load the dimensions and facts into one mapping using target load plan.

  
Login to rate this answer.
Karin Divens

Answered On : Aug 19th, 2011

.. But you are aware of the fact that the Target Load Order is only available if you have different pipelines, right?

  
Login to rate this answer.

The data in the dimensions table is generally static and descriptive. The fact table contains two columns one is foreign key and the other fact data generally the data in the fact tables contains numeric data

  
Login to rate this answer.

Difference between dimension and fact tables

1) dimension data is denormalized where as fact table contains normalized data
2)dimension table contains many columns where as fact table contains less columns only 2 in fact
3) the data in the dimension tables are less compared to the data in the fact tables
4) The data in the dimension table is static and descriptive in nature where as the fact table contains numeric and will change regularly
5) dimension tables generally called as lookup or reference table as well. facts tables are the key performance indicators of the business

  
Login to rate this answer.
JEEVAN KUMAR

Answered On : Nov 18th, 2011

Dimension table:::>it contains denormalized data and it takes less memory because in which we have more no.of columns and less no.of records.

fact table::::> it contains normalized data and it takes more memory because in which we have less no.of columns and more no.of records(i mean it maintains historical data)

  
Login to rate this answer.
Nitin

Answered On : Nov 30th, 2011

Please specify the definition sir

Code
  1. printf("hid");

  
Login to rate this answer.
ASHU

Answered On : Jan 21st, 2012

A Dimension table contain descriptive (detailed) data and a fact table contain measurements (facts) which are used to evaluate a business, all numeric are not a facts but the numeric which are key performance indicators is known as facts.. fact table contain summarized data....

ASHU (KARIMNAGAR)

  
Login to rate this answer.
Amar

Answered On : Nov 30th, 2012

The main logical difference is that we use connected lookup while loading dimensions and unconnected look up while loading facts

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.