MS SQL Data Warehousing - Delta extract

Author

Delta extract - newbie question

Derek

2006-11-13, 12:12 am

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

Marco Russo

2006-11-13, 5:15 am

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

Derek

2006-11-13, 7:13 pm

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.

"Marco Russo" wrote:

> 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:
>
>
>

Peter Nolan

2006-11-15, 7:14 pm

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/Link...abid=59&mid=442

Best Regards

Peter Nolan

Derek wrote:[color=darkred
]
> 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.
>
> "Marco Russo" wrote:
>

Sponsored Links