Maintainance columns in dimension table
4 posters
Page 1 of 1
Maintainance columns in dimension table
Hi
These questions might be very basic but I cant resist finding the answers.
1. Does every dimension table (Including Date Dimensions table) needs to have maintainance columns like (CreateDate,UpdatedDate,Is_Deleted - To mark row as logically deleted).
2. Does it also apply to bridge, mini, junk dimension table.
3. How do you handle deleted rows in dimension table, is it a good idea to mark the row as deleted through some flag.
These questions might be very basic but I cant resist finding the answers.
1. Does every dimension table (Including Date Dimensions table) needs to have maintainance columns like (CreateDate,UpdatedDate,Is_Deleted - To mark row as logically deleted).
2. Does it also apply to bridge, mini, junk dimension table.
3. How do you handle deleted rows in dimension table, is it a good idea to mark the row as deleted through some flag.
Re: Maintainance columns in dimension table
i prefer to use an audit dimension for all my tables (Dimension, Fact, Bridge etc) . The audit record can contain all your maintenance columns, in addition to information about load processes, data quality screens etc.
Records should almost never be deleted from dimension tables. In the rare case it is required (usually to correct an erroneous load), I tend to do a physical delete rather than a logical one.
LAndrews- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: Maintainance columns in dimension table
I usually use an audit table as well, but having timestamps local to the table is ok as well.
You can put whatever you need to put into a table to support your processes. You simply don't expose them to end-users if they don't need to see them.
You can put whatever you need to put into a table to support your processes. You simply don't expose them to end-users if they don't need to see them.
Re: Maintainance columns in dimension table
In dimensional modeling, we use SCD Start Date and End Date to keep track of record creation and change for SCD 2 dimensions. However normally you would deal with hybrid dimensions with both SCD2 and SCD1 attributes. So a last update date attribute would be useful.
A bridge table normally keeps correlations between two SCD1 dimensions. If you do need to keep the history of the relationship, you may need a fact-less fact table with a date key (snapshot) or an effective date pair.
Mini/junk dimension should not have any maintenance columns as it is either prebuilt (Cartesian product) or fact driven (incremental build).
If the delete happens to a SCD2 dimension, you expire the record by setting the SCDEndDate to whenever it becomes invalid. If you don't want see the record throughout the whole history, a delete flag in your option 3 makes sense but you may still need to know when it was deleted by SCDEndDate.
A bridge table normally keeps correlations between two SCD1 dimensions. If you do need to keep the history of the relationship, you may need a fact-less fact table with a date key (snapshot) or an effective date pair.
Mini/junk dimension should not have any maintenance columns as it is either prebuilt (Cartesian product) or fact driven (incremental build).
If the delete happens to a SCD2 dimension, you expire the record by setting the SCDEndDate to whenever it becomes invalid. If you don't want see the record throughout the whole history, a delete flag in your option 3 makes sense but you may still need to know when it was deleted by SCDEndDate.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Too many columns in fact table
» Pros and cons of consolidated dimension table Vs. many dimension table ?
» SK and ID columns in a Fact table
» Employee Hierarchy Dimension Table
» Too many columns in fact table
» Pros and cons of consolidated dimension table Vs. many dimension table ?
» SK and ID columns in a Fact table
» Employee Hierarchy Dimension Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum