Author Topic: How to delete data before loading new one (resolved)  (Read 4278 times)

Offline erwink

  • Senior Member
  • ****
  • Join Date: Jun 2015
  • Posts: 57
  • Forum Citizenship: +0/-0
How to delete data before loading new one (resolved)
« on: 06 Apr 2016 11:13:25 am »
Hi there

I've a fact build where the datasource query is as follow
SELECT "Id", "InternalOrder", "Value", "Date",
FROM   "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

using UPDATE/INSERT

My problem is that it may happen that in the source a entry is deleted. As I can not detect this deletion I would like to extend this build or create a new one to run

delete from "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

beforehand.

Can I include this delete statement in the Query of the data source. Or ..

Thank you for your help
erwin
« Last Edit: 07 Apr 2016 07:56:37 am by erwink »

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,864
  • Forum Citizenship: +647/-10
  • Cognos Software Muppet
Re: How to delete data before loading new one
« Reply #1 on: 06 Apr 2016 11:46:29 am »
Hi there

I've a fact build where the datasource query is as follow
SELECT "Id", "InternalOrder", "Value", "Date",
FROM   "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

using UPDATE/INSERT

My problem is that it may happen that in the source a entry is deleted. As I can not detect this deletion I would like to extend this build or create a new one to run

delete from "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

beforehand.

Can I include this delete statement in the Query of the data source. Or ..

Thank you for your help
erwin

Hi,

I'm not quite able to understand your logic here. If you run the following SQL before the build:

delete from "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)


Then wouldn't the build's query retrieve no rows? You said this is your build's query:

SELECT "Id", "InternalOrder", "Value", "Date",
FROM   "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)


The SQL you want to run beforehand would effectively eliminate all rows your build would have read? Is this what you want?

MF.
Meep!

Offline erwink

  • Senior Member
  • ****
  • Join Date: Jun 2015
  • Posts: 57
  • Forum Citizenship: +0/-0
Re: How to delete data before loading new one
« Reply #2 on: 07 Apr 2016 01:13:16 am »
Oups, was definitively to tired yesterday.

No, what I'd like is to delete the rows in the target database. The update/insert doesn't work because I do not get deletion from the source.
So my idea was to delete the same time window in the target and use APPEND afterwards

Hope it's clearer now

Thank you
erwin

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,864
  • Forum Citizenship: +647/-10
  • Cognos Software Muppet
Re: How to delete data before loading new one
« Reply #3 on: 07 Apr 2016 03:06:37 am »
Oups, was definitively to tired yesterday.

No, what I'd like is to delete the rows in the target database. The update/insert doesn't work because I do not get deletion from the source.
So my idea was to delete the same time window in the target and use APPEND afterwards

Hope it's clearer now

Thank you
erwin

Ah ok - that makes more sense. Obviously I'd advise extreme caution in deleting anything from your data warehouse - is that really what's required, or does your client really want to destroy history like this?

Assuming it's a requirement the client is insisting on, you could run your build from a node in a jobstream, and precede the build node with a SQL node which performs the delete?

Cheers!

MF.
Meep!

Offline erwink

  • Senior Member
  • ****
  • Join Date: Jun 2015
  • Posts: 57
  • Forum Citizenship: +0/-0
Re: How to delete data before loading new one
« Reply #4 on: 07 Apr 2016 07:55:33 am »
Thank you!!

Was somehow not aware of the SQL node.

The job run's every day to populate latest data. Historical data is just required on last day of every month. So I run a 2nd job separately for that with another target db

APPEND did not make sense for this reason and for the amount of data
TRUNCATE was something I wanted to avoid, in case somebody manipulated the past
UPDATE/INSERT was way to long, so i decided to go for current month -1 upwards

But then got the issue of deleted entries in the source which stayed in the target. Not nice

Thank you again for your help
erwin

 


       
Twittear