Delete rows from an outrigger dimension?

View previous topic View next topic Go down

Delete rows from an outrigger dimension?

Post  gbritton on Mon Dec 16, 2013 9:17 am

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?

gbritton

Posts : 6
Join date : 2013-11-18

View user profile

Back to top Go down

Re: Delete rows from an outrigger dimension?

Post  ngalemmo on Mon Dec 16, 2013 9:36 am

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...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Delete rows from an outrigger dimension?

Post  BoxesAndLines on Mon Dec 16, 2013 10:20 am

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".
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Delete rows from an outrigger dimension?

Post  gbritton on Mon Dec 16, 2013 12:04 pm

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

gbritton

Posts : 6
Join date : 2013-11-18

View user profile

Back to top Go down

Re: Delete rows from an outrigger dimension?

Post  gbritton on Mon Dec 16, 2013 12:06 pm

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

Posts : 6
Join date : 2013-11-18

View user profile

Back to top Go down

Re: Delete rows from an outrigger dimension?

Post  ngalemmo on Mon Dec 16, 2013 1:33 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Delete rows from an outrigger dimension?

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