What are the types of dimension tables

Showing Answers 1 - 19 of 19 Answers

bawa

  • Nov 9th, 2005
 

Dimensions are of 3 types mainly in case of slowly changing dimensions:

Type 1 ->Doesnt maintain any history and is update only.

Type 2-> Maintains full history.Insert row when certain attributes change else update row.The latest record is identified by a flag or date field which is called the MRI or Most Recent Indicator

Type 3->Maintains partial history.History is maintained as columns and not rows.Hence it is predecided as to till how many levels of history you want to maintain.

prathiba

  • Nov 14th, 2005
 

 infromational dimension

strutral dimension

conformed dimesion

  Was this answer useful?  Yes

bs

  • Nov 15th, 2005
 

Flat dimensions and snow flake.

  Was this answer useful?  Yes

Siva K

  • Dec 14th, 2005
 

There are three types of Dimesions

Confirmed Dimensios, Junk Dimesions, Degenerative Dimesions

Comfirmed is some thing which can be shared by shared by multiple Fact Tables or multiple Data Marts.

Junk Dimensiions is grouping flagged values

Degenerative Dimension is something dimensional in nature but exist fact table.

piyush

  • Feb 14th, 2006
 

there are five types of dimensions:

1. star schema

2. snowflake schema

3.parent-child schema

4.virtual dimension

5. mining model

  Was this answer useful?  Yes

amit

  • Apr 17th, 2006
 

Hi,

 Can u detail ab't 3 dimensions.

regards

Amit

  Was this answer useful?  Yes

Ashish Sharma

  • May 8th, 2006
 

What is this? No one know waht are the diffrent types of dimesnsions? Who's answer is correct, everyone is giving there own answers.

Actually the question is wrong and is not mentioned in which case it is talking about.

I think there are following types of dimensions:

1. SCD or slowly changing dimension.

2. SGD or slowly Growing Dimension. ( Type 1, Type 2 and Type 3)

There are other three types of Dimensions:

1. Confirmed Dimensions.

2. Junk Dimensions.

3.  Degenerative Dimensions.

Regards,

Ashish Sharma

  Was this answer useful?  Yes

keerthi

  • Jun 6th, 2006
 

can you explain in detail the following with examples:

1. junkdimension

2.conformed dimension

3degenarative dimension.

  Was this answer useful?  Yes

saba

  • Aug 12th, 2006
 

how to test the reports and how do we appy ad hoc queries while execution

  Was this answer useful?  Yes

vijay

  • Nov 20th, 2006
 

Please explain What are Confirmed Dimensions, Junk Dimensions, and Degenarated Dimensions.

Confirmed Dimensions, Junk Dimensions, and Degenerated Dimensions 

  Conformed Dimensions (CD): these dimensions are something that is built once in your model and can be reused multiple times with different fact tables.   For example, consider a model containing multiple fact tables, representing different data marts.  Now look for a dimension that is common to these facts tables.  In this example let’s consider that the product dimension is common and hence can be reused by creating short cuts and joining the different fact tables.Some of the examples are time dimension, customer dimensions, product dimension. §         

Junked Dimensions (JD):  When you consolidate lots of small dimensions and instead of having 100s of small dimensions, that will have few records in them, cluttering your database with these mini ‘identifier’ tables, all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table.  (Since we are storing all the junk in this one table) For example: a company might have handful of manufacture plants, handful of order types, and so on, so forth, and we can consolidate them in one dimension table called junked dimension table. 

 Degenerated Dimension (DD):  An item that is in the fact table but is stripped off of its description, because the description belongs in dimension table, is referred to as Degenerated Dimension.  Since it looks like dimension, but is really in fact table and has been degenerated of its description, hence is called degenerated dimension. Now coming to the slowly changing dimensions (SCD) and Slowly Growing Dimensions (SGD):  I would like to classify them to be more of an attributes of dimensions its self.   

Although other might disagree to this view but Slowly Changing Dimensions are basically those dimensions whose key value will remain static but description might change over the period of time.  For example, the product id in a companies, product line might remain the same, but the description might change from time to time, hence, product dimension is called slowly changing dimension.  

 Lets consider a customer dimension, which will have a unique customer id but the customer name (company name) might change periodically due to buy out / acquisitions, Hence, slowly changing dimension, as customer number is static but customer name is changing,  However, on the other hand the company will add more customers to its existing list of customers and it is highly unlikely that the company will acquire astronomical number of customer over night (wouldn’t the company CEO love that) hence, the customer dimension is both a Slowly changing as well as slowly growing dimension. Let me know if this helps or if you have any questions. Jack Anderson

bala

  • Mar 9th, 2007
 

Hi,Type1,2&3 only applicable for scd.But u mentioned for sgd.Can u explain degenerative,junk dimension with example.

  Was this answer useful?  Yes

Nagireddy

  • Mar 27th, 2007
 

Degenerated Dimensoion : a dimension which is located in fact table known as Degenerated dimesion

Ex: InvoiceNO,Empid etc..

Junk Dimension: its a dimension table which is used to keep junk attributes

 

  Was this answer useful?  Yes

Shawn Hu

  • Apr 26th, 2007
 

Besides the SCD, there should have rapidly changing dimension like below:

Rapidly changing dimensions (RCDs) also preserve a history of changes in an underlying data source, such as a data warehouse. Members in rapidly changing dimensions may move frequently to different locations within hierarchies, or the attributes used to describe the member may quickly change—sometimes faster than the granularity frequently used to track changes in the data warehouse. Unlike slowly changing dimensions, rapidly changing dimensions cannot establish effective date windows for changes to information because this approach quickly becomes unwieldy.

For example, attributes representing balanced scorecard classifications could be included in a customer dimension. Each week, these classifications are assessed and updated for each record in the dimension table. By using a Type 2 SCD implementation, a typical customer dimension that contains 500,000 distinct customers who each have a new record written every week would quickly explode to 26 million records in just one year—and this explosion does not include any other attribute changes for the contents of this kind 2 SCD. Combine this explosive growth with the simple fact that large businesses regularly have customer dimensions that contain tens of millions of distinct customers, and the Type 2 SCD approach becomes impractical.

To handle the situation presented in the previous example, you can represent all the possible combinations of values for the balanced scorecard classifications in a separate, supplemental dimension table—a rapidly changing dimension. You add a surrogate key to the rapidly changing dimension to identify each combination of balanced scorecard classifications, and then create a foreign key relationship between the rapidly changing dimension table and each fact table related to the customer dimension. In other words, both a customer key column and a customer scorecard key column now exist in the fact table. Each week, the balanced scorecard classifications are assessed for each customer, but instead of creating a massive set of new records in the customer dimension, the value of the customer scorecard key is changed in the fact table to reflect the change in classification for each customer.

Shawn Hu

  • Apr 26th, 2007
 

Besides the SCD, there should have rapidly changing dimension like below:

Rapidly changing dimensions (RCDs) also preserve a history of changes in an underlying data source, such as a data warehouse. Members in rapidly changing dimensions may move frequently to different locations within hierarchies, or the attributes used to describe the member may quickly change—sometimes faster than the granularity frequently used to track changes in the data warehouse. Unlike slowly changing dimensions, rapidly changing dimensions cannot establish effective date windows for changes to information because this approach quickly becomes unwieldy.

For example, attributes representing balanced scorecard classifications could be included in a customer dimension. Each week, these classifications are assessed and updated for each record in the dimension table. By using a Type 2 SCD implementation, a typical customer dimension that contains 500,000 distinct customers who each have a new record written every week would quickly explode to 26 million records in just one year—and this explosion does not include any other attribute changes for the contents of this kind 2 SCD. Combine this explosive growth with the simple fact that large businesses regularly have customer dimensions that contain tens of millions of distinct customers, and the Type 2 SCD approach becomes impractical.

To handle the situation presented in the previous example, you can represent all the possible combinations of values for the balanced scorecard classifications in a separate, supplemental dimension table—a rapidly changing dimension. You add a surrogate key to the rapidly changing dimension to identify each combination of balanced scorecard classifications, and then create a foreign key relationship between the rapidly changing dimension table and each fact table related to the customer dimension. In other words, both a customer key column and a customer scorecard key column now exist in the fact table. Each week, the balanced scorecard classifications are assessed for each customer, but instead of creating a massive set of new records in the customer dimension, the value of the customer scorecard key is changed in the fact table to reflect the change in classification for each customer.

  Was this answer useful?  Yes

abhishek09

  • Aug 6th, 2007
 

There are may kind of dimension which is depend on requrement

Some name I know which I am giving bellow

1.confirm dimension
2.junk dimension
3.dirty dimension
4.degenerated dimension
5.time dimension
6.role playing dimension

These are the dimension used by me and there are many other dimension
if anyone know more then this please share your knowledge

Thanks
Abhishek

  Was this answer useful?  Yes

Pers.L

  • Jan 23rd, 2008
 

There are 5 types of dimension tables.

1. Conformed Dimensions

2. Slowly Changing Dimensions

3. Rapidly Changing Dimensions

4. Junked Dimensions

5. Degenerated Dimensions.

  Was this answer useful?  Yes

VVM.SP

  • Mar 12th, 2008
 

Dimensions mainly 11 types.  They are
1. Changing Dimensions: At least one attribute value would change over the time.
2. Slowly Changing Dimension: Attributes of a dimension that would undergo changes   very rarely and commonly over the time.  ex: Customer Name, Sex
3. Rapdly Changing Dimension: A dimension attribute that changes frequently.
4. Conformed Dimension: Fixed and Reusable dimension .
5. Degenerate Dimension: The value of the dimension stored in fact table insted of the dimension table.
6. Junk Dimension: It is a table with the combination of different and unrelated attributes, to reduce the p.k and f.k relation.
7. Inferred Dimension:
8.Role Playing Dimension:
9. Shrunken Dimension:
10. Out Triggers:
11. Static Dimension:

  Was this answer useful?  Yes

Junk Dimension - There is no measures to aggregate it contains flags and/or text
information
Example: student attendance tracking

Conformed Dimension - The dimension table used by two or more fact tables
Example: date dimensions

Degenerate Dimension - Actually the attributes are dimension but stored in fact tables order number and order line number of product there is no need to create separate dimension tables for this.

  Was this answer useful?  Yes

tsurekha

  • Oct 20th, 2010
 

Conformed dimension, Junked dimension, Degenarate dimension

Conformed dimension is a dimension which can be shared by multiple fact tables
Junked dimension is a grouping of flagged values
Degenarate dimension is like dimension but it is in fact table

  Was this answer useful?  Yes

suraj

  • Oct 20th, 2011
 

In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions.
The same category of information. For example, year, month, day, and week are all part of the Time Dimension.
Types of dimension :
1.Conformed dimension
2.Junk dimension
3.Degenerate dimension
4.Role-playing dimensions

  Was this answer useful?  Yes

Aruna

  • Feb 6th, 2012
 

The Dimensions are Confirmed Dimension, Junk/Dirty Dimension, Degenerated Dimension and Slowly Changing Dimensions(SCD)-(Under slowly changing dimensions we have 6 types, they are Type1, Type2, Type3, Type4, Type5 and Type6).

  Was this answer useful?  Yes

keerthana

  • May 2nd, 2013
 


There are following types of dimensional tables:

1.Slowly changind dimensions(SCD)
a)SCD-1:stores only current values
b)SCD-2:stores both previous and current values
c)SCD-3:stores current and only recent previous data
d)SCD-4:stores current data separately and all previous values in a replica
2)Junk Dimension
3)Roleplay Dimension
4)Degenerate Dimension
5)Confirm Dimension

  Was this answer useful?  Yes

madhu

  • Dec 5th, 2014
 

1>conformed Dimension
2>shrunk Dimension
3>Degenerated Dimension
4>Role Dimension
5>Junk Dimension

  Was this answer useful?  Yes

suresh

  • Jul 26th, 2015
 

I need explanation with example of dimensions.

  Was this answer useful?  Yes

triveni

  • Aug 18th, 2015
 

Conformed dimension,
Degenerated dimension,
Junk dimension

  Was this answer useful?  Yes

softinx

  • Sep 29th, 2015
 

Conformed Dimension is a dimension that has the same meaning to every fact with which it relates. Conformed dimensions allow facts and measures to be categorized and described in the same way across multiple facts and/or data marts, ensuring consistent reporting across the enterprise.

These Degenerate Dimensions are natural keys of the "parents" of the line items. Even though there is no corresponding dimension table of attributes, degenerate dimensions can be quite useful for grouping together related fact tables rows.

In a Junk Dimension, we combine these indicator fields into a single dimension. This way, well only need to build a single dimension table, and the number of fields in the fact table, as well as the size of the fact table, can be decreased.

  Was this answer useful?  Yes

M.Duraishankarraj

  • Jan 6th, 2016
 

Dimension is a three types:
i) Junk dimension
ii) Degenerated dimension
iii) Role play dimension

  Was this answer useful?  Yes

ARUN

  • Aug 12th, 2017
 

There are few types of dimension tables
1) Confirmed dimension
2) Junk dimension
3) Degenerate dimension
4) SCD

  Was this answer useful?  Yes

Johnny

  • Aug 31st, 2017
 

The following are the type of dimensions
Slowly changing
Fast Changing
Inferred
Junk
Conformed
Degenerate, and
Static

  Was this answer useful?  Yes

nareshcnr

  • Nov 6th, 2017
 

Types of Dimension tables :
There are mainly three types of dimension tables available in case of Slowly Changing Dimensions
Type 1: Never maintain historical data. only update the existing data.
ex: city_id | city_Name
KA 01 | Bangalore
Now city_name "Bangalore" changed to "Bengaluru" as per Type 1 it just update the previous data
so after performing SCD Type 1 the result will be like this
city_id | city_Name
KA 01 | Bengaluru
SCD Type 2: it maintains the History of data. If the attribute is new Insertor else change & Insert. No update takes place on existing data.
ex : city_id | city_Name
KA 01 | Bangalore
after performing SCD Type 2 :
city_id | city_Name
KA 01 | Bangalore
KA 01 | Bengaluru
here City_Id is logical key so to avoid primary key violation add one dummy column and make it as a surrogate key.
after performing SCD Type 2 :
S.No |city_id | city_Name
1 | KA 01 | Bangalore
2 | KA 01 | Bengaluru
SCD Type 3: Maintains partial history.
Type of Dimensions :
1. Confirmed Dimension
2. Degenerated Dimension
3. Late Arriving Dimension
4. Junk Dimension
5. Role Play Dimension
6. Casual Dimension

  Was this answer useful?  Yes

Give your answer:

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

 

Related Answered Questions

 

Related Open Questions