Confirmed Dimension (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 fact tables. In this example, let us 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.
A confirmed dimension is a dimension which connects to multiple Fact tables across one or more data marts (cubes). A confirmed dimension is physically implemented across multiple data marts with exactly the same structure, attributes,values(dimensions members), meaning and definition. Each instance means exactly the same thing providing the exact same context irrespective of the fact table to which it is connected or the data mart to which it belongs.
Example : A Date dimension has exactly the same set of attributes, same members and same meaning irrespective of which fact table it is connected to or to which data mart it belongs to as long as it belongs to the same organization. For instance, a Fiscal Calendar is exactly the same start and end dates across all the departments within an organization.
Here are some highlights of Confirmed Dimensions:
- Same dimension joins to multiple fact tables or is used across multiple data marts.
- It is a master dimension and is used across multiple dimensional models.
- Each instance of confirmed dimensions are exactly the same in every aspect including the attribute names, definitions, etc.
Role-Playing Dimension :
This is a dimension which is connected to the same fact table multiple times using different foreign keys. This helps the users to visualize the same cube data in different context/angles to get a better understanding and make better decisions.
Example : Consider a Time Dimension which is joined to the same fact table (Say FactSales) multiple times, each time using a different foreign key in the fact table like Order Date, Due Date, Ship Date, Delivery Date, etc. Essentially there is only one single physical dimension called Date Dimension. However, it is joined multiple times to the Fact table to help the users to visualize the cube data in the context of different dates.
Here are some highlights of the Role-Playing Dimensions :
- It is a single physical dimension table.
- Same dimension table connects to the same fact table multiple times using different foreign keys from the fact table.
- When a Role-Playing Dimension is added to the cube, it appears as a different dimension (one instance for each foreign key to which it is joined) to the end users and hence playing multiple roles.
Junk Dimension :
In data warehouse design, frequently we run into a situation where there are yes/no indicator fields in the source system. Through business analysis, we know it is necessary to keep those information in the fact table. However, if keeping all those indicator fields in the fact table, not only we need to build many small dimension tables, but the amount of information stored in the fact table also increases tremendously, leading to possible performance and management issues.
Junk dimension is the way to solve this problem. In a junk dimension, we combine these indicator fields into a single dimension. This way, we'll 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. The content in the junk dimension table is the combination of all possible values of the individual indicator fields.
Example : Assume that there are two dimension table (Gender and MaritalStatus). The data of these two tables are shown below:
Here both the dimension has low cardinality flags. This will cause maintenance of two tables and decrease performance of SQL Queries. We can combine these two dimensions into a single by cross joining and can maintain a single dimension table as shown below in junk dimension.