Delete rows from an outrigger dimension?

I am building out a data warehouse for a project management business.  Among the many dimensions, I have a "Contacts" dimension.  Essentially, a project may have one or more contact references: customers, contractors, employees, etc.  Over time, contacts may be added and deleted from the project.  I currently have a simple structure:

Code:


Projects                   Contacts
--------                   ----------
ContactFK  --1 x many -->  ContactPK


There is no limit to how many contacts may be associated with a project.  Adding contacts is no problem for the DW.  What I'm struggling with is what to do when contacts are removed from a project.  I have to adjust the Contacts dimension somehow.  I could flag the records as "Invalid," I suppose.  Alternatively (and what I'm doing at the moment), I can just delete the corresponding rows from the Contacts table.  

Revisiting the Kimball material, I could not find a discussion of actually deleting things.  Is that considered a poor practice?  If so, what is the best approach that will keep my DW in sync with the OLTP system?

Yes, it is poor practice.

Given a DW is generally used for tactical and strategic analysis, first ask yourself why are contacts in the DW in the first place...

I'm confused, a Project can have many Contacts, but in your post you have the contact_fk in the projects table. Regardless, what you most likely have is a many to many as Contacts will likely work multiple projects over time. When you have many to many relationships you need to start looking at creating new facts or bridge tables.

With regards to deletes, we don't do those. You would need to update some status code on the Contact dimension that says, "no longer on the project".

ngalemmo wrote:Yes, it is poor practice.

Given a DW is generally used for tactical and strategic analysis, first ask yourself why are contacts in the DW in the first place...

Well, the contacts are there because the business users want to see them on their reports

BoxesAndLines wrote:I'm confused, a Project can have many Contacts, but in your post you have the contact_fk in the projects table.  Regardless, what you most likely have is a many to many as Contacts will likely work multiple projects over time.  When you have many to many relationships you need to start looking at creating new facts or bridge tables.

yes, you are quite right. A contact may be associated with more than one project. Thanks for reminding me!

BoxesAndLines wrote:With regards to deletes, we don't do those.  You would need to update some status code on the Contact dimension that says, "no longer on the project".

thanks for that advice. It's just what I need.

gbritton wrote:

ngalemmo wrote:Yes, it is poor practice.

Given a DW is generally used for tactical and strategic analysis, first ask yourself why are contacts in the DW in the first place...

Well, the contacts are there because the business users want to see them on their reports

The reason I asked is the modeling approach should be targeted to what you are trying to accomplish. If someone wants to look at a project to see a list of contacts, it is not something you build star schemas for. Stuff like that is usually handled by an ODS or a query off the operational system itself.

I mean, there is nothing to prevent you from keeping a list of contacts, but if that is the sole reason for it, I wouldn't put much effort into modeling it… and I wouldn't try to fully incorporate other than having a project FK in the contact list.