Type 2 dimension or type 2 column?
2 posters
Page 1 of 1
Type 2 dimension or type 2 column?
Hello,
As a student and champion of the Kimball method within my organization, I get questions from time to time that require some research:
My data modeling tool provides "dimensional" selections, including a tab where the user can designate the type of slowly changing dimension for a given table. I like this feature from a documentation perspective and it brought up a question: Is it a best practice or "correct" to designate a dimension table as type 2 even though there is usually a mix of type 1 and type 2 columns?
For example, a customer dimension would most likely track type 2 changes on Address, City, State, Zip, etc., but would probably perform type 1 on something like FirstName, or PhoneNumber. Other than DimDate, I can't think of many dimensions (DimOrganiztion, DimProduct, DimAccount) that don't have at least some columns that could/should be type 2. Specifically the question is: If a dimension has any type 2 columns, is the dimension considered type 2? Is it more accurate to refer to a dimension as having type 2 columns? Even in more complex modeling situations where we may split out dimensions to address rapidly changing attributes, cardinality issues and other business requirements, the entire dimension still may have some type 1 columns.
Cheers.
As a student and champion of the Kimball method within my organization, I get questions from time to time that require some research:
My data modeling tool provides "dimensional" selections, including a tab where the user can designate the type of slowly changing dimension for a given table. I like this feature from a documentation perspective and it brought up a question: Is it a best practice or "correct" to designate a dimension table as type 2 even though there is usually a mix of type 1 and type 2 columns?
For example, a customer dimension would most likely track type 2 changes on Address, City, State, Zip, etc., but would probably perform type 1 on something like FirstName, or PhoneNumber. Other than DimDate, I can't think of many dimensions (DimOrganiztion, DimProduct, DimAccount) that don't have at least some columns that could/should be type 2. Specifically the question is: If a dimension has any type 2 columns, is the dimension considered type 2? Is it more accurate to refer to a dimension as having type 2 columns? Even in more complex modeling situations where we may split out dimensions to address rapidly changing attributes, cardinality issues and other business requirements, the entire dimension still may have some type 1 columns.
Cheers.
Last edited by parbie on Wed Aug 25, 2010 12:31 pm; edited 1 time in total (Reason for editing : spelling, grammar)
parbie- Posts : 11
Join date : 2010-04-06
Re: Type 2 dimension or type 2 column?
The dimension would be considered type 2, as it contains versions of dimension rows. The type defines the structure of the table, it doesn't matter how many columns have history.
Similar topics
» Adding New Column to a Type 2 dimension
» rationale behind dimension with Type 0 and missing Type 5
» How do you backfill and SCD type 2 column?
» Incremental Load Testing -Type 1 & Type 2
» Type-2 Dates as Date Data Type ?
» rationale behind dimension with Type 0 and missing Type 5
» How do you backfill and SCD type 2 column?
» Incremental Load Testing -Type 1 & Type 2
» Type-2 Dates as Date Data Type ?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum