what are the types of dimension tables

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.

 infromational dimension

strutral dimension

conformed dimesion

Flat dimensions and snow flake.

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.

Hi prathiba,

can you tell me how the dimenions are seperated?

Sithu

sithusithu@hotmail.com

there are five types of dimensions:

1. star schema

2. snowflake schema

3.parent-child schema

4.virtual dimension

5. mining model

Hi,

 Can u detail ab't 3 dimensions.

regards

Amit

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

can you explain in detail the following with examples:

1. junkdimension

2.conformed dimension

3degenarative dimension.

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

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

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

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

 

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.

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.

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

Different types of Dimensions:
1. Primary
2. Secondary
3. Conformed
4. Degenerated
5. Junk
6. Dirty
7. Causal
8. Slowly Changing
9. Rapid Chaning.
10. Large

Cheers,
Ravi Kumar Garre

There are 5 types of dimension tables.

1. Conformed Dimensions

2. Slowly Changing Dimensions

3. Rapidly Changing Dimensions

4. Junked Dimensions

5. Degenerated Dimensions.

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:

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.

Conformed dimension, Junked dimension, Degenarate dimension

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

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).

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