Design Query on Dimensions
2 posters
Page 1 of 1
Design Query on Dimensions
The query I had is based on Dimensions as mentioned in the books -
(A) The Data Warehouse Lifecycle Toolkit - Second Edition
(B) The Data Warehouse Toolkit - Second Edition
(A) - Chapter 6 - Introducing Dimensional Modeling (More on Dimensions - Mini Dimensions Page 259, 260)
(B) - Chapter 9 - Financial Services (Context-dependent dimension outriggers Page 213)
In (A) Page 260 (Fig 6-9 - Fact Table with a mini-dimension to track customer demographic changes), mentions about a mini-dimension that has been physically separated from the core dimension. It is further explained that the demographics data (mini-dimension) is linked to the core dimension (customer) only through the fact table.
This approach allows you to associate demographics with the customer only when a fact table row occurs. If no fact event happens, then no linkage is established. This then leads on to the argument that if there is a danger of missing a demographics association, a supplemental factless fact table can be used.
In (B) Page 213 (Fig 9.7 - Context-dependent dimension outriggers), the diagram shows the core dimension (Product Dimension) being linked to the mini dimension (Mortgage specific Product Outrigger), rather than through the fact table (Monthly Mortgage Snapshot Fact).
I am bit confused about the two differing design techniques mentioned here.
Why are we not linking the fact table to the mini-dimension in (B) AND/OR
Why are we not joining the core to the mini dimension in (A)?
Maybe, I am missing some important link here. It would be great if you could throw more light on this and clear my doubt.
(A) The Data Warehouse Lifecycle Toolkit - Second Edition
(B) The Data Warehouse Toolkit - Second Edition
(A) - Chapter 6 - Introducing Dimensional Modeling (More on Dimensions - Mini Dimensions Page 259, 260)
(B) - Chapter 9 - Financial Services (Context-dependent dimension outriggers Page 213)
In (A) Page 260 (Fig 6-9 - Fact Table with a mini-dimension to track customer demographic changes), mentions about a mini-dimension that has been physically separated from the core dimension. It is further explained that the demographics data (mini-dimension) is linked to the core dimension (customer) only through the fact table.
This approach allows you to associate demographics with the customer only when a fact table row occurs. If no fact event happens, then no linkage is established. This then leads on to the argument that if there is a danger of missing a demographics association, a supplemental factless fact table can be used.
In (B) Page 213 (Fig 9.7 - Context-dependent dimension outriggers), the diagram shows the core dimension (Product Dimension) being linked to the mini dimension (Mortgage specific Product Outrigger), rather than through the fact table (Monthly Mortgage Snapshot Fact).
I am bit confused about the two differing design techniques mentioned here.
Why are we not linking the fact table to the mini-dimension in (B) AND/OR
Why are we not joining the core to the mini dimension in (A)?
Maybe, I am missing some important link here. It would be great if you could throw more light on this and clear my doubt.
dwbi_rb- Posts : 17
Join date : 2009-02-19
Re: Design Query on Dimensions
The two modeling techniques you describe are meant to solve two different problems. The mini dimension (A) is meant to reduce the change rate of a dimension by taking the more dynamic attributes out of the dimension and joining them directly to the facts. This ties those attribute values directly to the fact events and we don't have to use Type 2 change tracking in the dimension.
The outrigger dimension (B) is a structure designed to support heterogeneous dimensions; dimensions that bring together objects that are only similar at a high level (such as financial services products in the book example). The outrigger dimension is meant to isolate a set of attributes that relate only to a subset of dimension rows, like mortgage accounts.
The confusion may come from the fact that we describe in (A) how you can join the mini dimension back to the main dimension by putting the appropriate key value in the main dimension. The mini dimension would then look like an outrigger dimension, but it still serves the primary purpose of joining directly to the fact table to preserve the historical attribute values. Since the goal is to not track changes in the main dimension, the key to the mini dimension would need to be a Type 1 value, and would correspond to the "current" value for each dimension row. As the text goes on to say, if you want to keep a history of the mini dimension values associated with a row in the main dimension, you need to create a separate factless fact table with an effective_date and end_date that sits between the main dimension and the mini dimension.
--Warren
The outrigger dimension (B) is a structure designed to support heterogeneous dimensions; dimensions that bring together objects that are only similar at a high level (such as financial services products in the book example). The outrigger dimension is meant to isolate a set of attributes that relate only to a subset of dimension rows, like mortgage accounts.
The confusion may come from the fact that we describe in (A) how you can join the mini dimension back to the main dimension by putting the appropriate key value in the main dimension. The mini dimension would then look like an outrigger dimension, but it still serves the primary purpose of joining directly to the fact table to preserve the historical attribute values. Since the goal is to not track changes in the main dimension, the key to the mini dimension would need to be a Type 1 value, and would correspond to the "current" value for each dimension row. As the text goes on to say, if you want to keep a history of the mini dimension values associated with a row in the main dimension, you need to create a separate factless fact table with an effective_date and end_date that sits between the main dimension and the mini dimension.
--Warren
warrent- Posts : 41
Join date : 2008-08-18
Re: Design Query on Dimensions
Thanks a lot for looking in to this for me Warren!
This would hence mean that we use a mini-dimension specifically to store the more dynamic attributes and such that these attributes can be handled through Type 1 SCD. For a historical record, we may refer to a factless fact table in this case.
Whereas we use outtriggers to denote a sub-type of a main dimension and may use Type 1/2/3 SCD depending on the requirement.
Also that outtriggers are connected to the core-dimension whereas mini-dimensions CAN be connected to the core dimensions.
This would hence mean that we use a mini-dimension specifically to store the more dynamic attributes and such that these attributes can be handled through Type 1 SCD. For a historical record, we may refer to a factless fact table in this case.
Whereas we use outtriggers to denote a sub-type of a main dimension and may use Type 1/2/3 SCD depending on the requirement.
Also that outtriggers are connected to the core-dimension whereas mini-dimensions CAN be connected to the core dimensions.
dwbi_rb- Posts : 17
Join date : 2009-02-19
Similar topics
» Query on Dimensions
» Design all dimensions as conformed dimensions
» Relating dimensions together and fact table design
» Datamart Design for multiple Dimensions containing History
» Transfer Courses Fact/Dimensions Design
» Design all dimensions as conformed dimensions
» Relating dimensions together and fact table design
» Datamart Design for multiple Dimensions containing History
» Transfer Courses Fact/Dimensions Design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum