Delta extract - newbie question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Derek - 13 Nov 2006 02:10 GMT

I reasonably new to archiving data (as is my company) and need to run a delta
extract on some data to a staging table.

I think I'm reasonably up to speed on the concepts, but what is the best way
of writing the details. Note that I'm working with SQL Server 2000.

Any good websites?

Otherwise I'll probably just write code along the following lines.

INSERT INTO Person_History (PersonID, StartDate, Branch)
SELECT P.PersonID, GetDate(), P.Branch
FROM Person P
WHERE not exists (SELECT PersonID
        FROM Person_History PH
        WHERE PH.PersonID = P.PersonID
        AND P.Branch <> PH.Branch)

Thanks
Derek

Reply to this Message

Marco Russo - 13 Nov 2006 09:47 GMT

If you move to SQL 2005 and use Integration Services, take a look to
the TableDifference component: http://www.sqlbi.eu/TableDifference.aspx
On SQL 2000 your approach is relatively fast.
Anyway you are implementing a way to handle something similar to a Type
II SCD (Slowly Changing Dimension) - read Kimball's books about this
(www.kimballgroup.com).

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Derek ha scritto:

> I reasonably new to archiving data (as is my company) and need to run a delta
> extract on some data to a staging table.
[quoted text clipped - 16 lines]
> Thanks
> Derek

Reply to this Message

Derek - 13 Nov 2006 23:01 GMT

Thanks Marco

I've read a litle of "The data Warehouse Toolkit", but I found it a little
too high level for what I was after. We are currently stuck at a spot where
we have a few useful datamarts, but don't have the time or budget to do BI
properly. There is a project pencilled in for May next year to do it
properly, and I don't want to pre-empt that (especially since we will
probably have SQL Server 2005 as a part of it).

This project is just to start archiving some information to use in some of
the existing reports, and perhaps to help give the data warehouse some real
historical information once it kicks off.

So it looks like I'll have to wait till SQL Server 2005 to do the extract
quickly. No worries, thanks for the advice.

> If you move to SQL 2005 and use Integration Services, take a look to
> the TableDifference component: http://www.sqlbi.eu/TableDifference.aspx
[quoted text clipped - 29 lines]
> > Thanks
> > Derek

Reply to this Message

Peter Nolan - 15 Nov 2006 16:33 GMT

Derek,
if you want a free tool to generate deltas we released a set of open
source and free DW utilities...one of which is delta generation.

Basically if you have two files which are dumps of tables the delta
generator will tell you what happened to the first one in order to get
the second one.......it will compare in the order of 30,000 fields per
second per cpu on 3.2 ghz processors so that is enough for smaller
sites....It is the 'brute force' approach of compare fields including
being null aware.....there are lots and lots of systems out there that
can't provide deltas.....we wrote this for a client who has a few of
them!!

The manual is here:

http://www.instantbi.com/SeETL/LinkClick.aspx?link=SeETL+User+Guide.pdf&tabid=59
&mid=442

Best Regards

Peter Nolan

> Thanks Marco
>
[quoted text clipped - 45 lines]
> > > Thanks
> > > Derek

Reply to this Message

Colin Robinson - 31 Jan 2007 11:26 GMT

you could also try this 3 step approach using binaryChecksum() in sql2000:

Take a table with a primary key (assuming your db isnt as bad as the one im
working on)

1. Select Pkeycolumn,BinaryChecksum(*) into Yourtable_Checksum :this
creates a new table with a checksum of every row in the table
Select * from yourtable into archive table.

Deltas are then every row in yourtable left joined to yourtable_Checksum
with a binarychecksum(*) different to the stored Checksum, new rows are
indicated by a missing checkum value from the left join.

2. move the deltas to the archive.

3. re generate the checksum table with the new checksum values of every row.

With a bit of work you can also use this approach write changed data to
audit Tables !

ColinR

> Derek,
> if you want a free tool to generate deltas we released a set of open
[quoted text clipped - 73 lines]
>> > > Thanks
>> > > Derek

Reply to this Message