Question on Deleting records from dimension tables

View previous topic View next topic Go down

Question on Deleting records from dimension tables

Post  mru22 on Tue Jun 14, 2011 9:18 pm

I have created a Type 2 Slowly changining dimension and have realized that some of the records that are in the dimension get removed from the source table after a long period of time.

Rather than remove the record, I was thinking of adding a "Deleted" flag since I already have an acitve flag that has to do with an active status. Other than a deleted flag is there a better way via dates or some other process to conider the record deleted without physically deleting it? Or would i be simply better removing it from the dimension table buy using the natural key in the stage and determine if it exists in the source ?

Thanks,


mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Question on Deleting records from dimension tables

Post  ngalemmo on Wed Jun 15, 2011 9:27 am

Sure, you can put a flag on the dimension row, but is there any particular reason one needs to know the data was purged from the operational system?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Question on Deleting records from dimension tables

Post  mru22 on Wed Jun 15, 2011 9:55 am

I was going to add the flag in for a record that was in the source system and they no longer want to ever know it existed or report on it. I could have used an active status flag but I already have that column for something else.

Thanks,


mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Question on Deleting records from dimension tables

Post  BoxesAndLines on Wed Jun 15, 2011 1:01 pm

Yes. I have to do this all the time when the application deletes data from the system. It's commonly called a logical delete.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Additional Question

Post  depuurt on Thu Aug 18, 2011 2:58 am

Continuing on the previous. If you detect the deletion of a dimension record in the source you would flag the dimension record on the deleted flag and set the deleted date on day of detection. In a SCD Type 2 situation, what would you do with the start / end dates. In particular the end date : should you keep it on 99991231 or should you change it to the deleted date? And what about the active flag?

DP

depuurt

Posts : 1
Join date : 2011-08-18

View user profile

Back to top Go down

Re: Question on Deleting records from dimension tables

Post  BoxesAndLines on Thu Aug 18, 2011 9:28 am

Treat it like any other change. No special logic is required other than setting the delete flag.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Question on Deleting records from dimension tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum