Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Delete rows from an outrigger dimension?

3 posters

Go down

Delete rows from an outrigger dimension? Empty Delete rows from an outrigger dimension?

Post  gbritton 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

Back to top Go down

Delete rows from an outrigger dimension? Empty Re: Delete rows from an outrigger dimension?

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

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

http://aginity.com

Back to top Go down

Delete rows from an outrigger dimension? Empty Re: Delete rows from an outrigger dimension?

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

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

Back to top Go down

Delete rows from an outrigger dimension? Empty Re: Delete rows from an outrigger dimension?

Post  gbritton 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

Back to top Go down

Delete rows from an outrigger dimension? Empty Re: Delete rows from an outrigger dimension?

Post  gbritton 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

Back to top Go down

Delete rows from an outrigger dimension? Empty Re: Delete rows from an outrigger dimension?

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

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

http://aginity.com

Back to top Go down

Delete rows from an outrigger dimension? Empty Re: Delete rows from an outrigger dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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