from the gut

Here’s a little BI humor for anyone from one of my favorite BI writers: Donald Farmer.

Also on the subject of checksum testing, this query will generate a checksum query for a given table. Use this query to generate the checksum for each table, then customize the sum() clauses if you did any transformations on your data.


select case a.xtype
when 167 then 'SUM(LEN('+a.name+')) as sum_len_'+a.name+','
when 175 then 'SUM(LEN('+a.name+')) as sum_len_'+a.name+','
when 231 then 'SUM(LEN('+a.name+')) as sum_len_'+a.name+','
when 106 then 'SUM('+a.name+') as sum_'+a.name+','
when 56 then 'SUM('+a.name+') as sum_'+a.name+','
when 104 then 'SUM(CONVERT(int,'+a.name+')) as sum_'+a.name+','
when 61 then 'SUM(MONTH('+a.name+')+DAY('+a.name+')+YEAR('+a.name+')) as sum_'+a.name+',
SUM(LEN(MONTH('+a.name+'))+LEN(DAY('+a.name+'))+LEN(YEAR('+a.name+'))) as sum_len_'+a.name+',' -- datetime
else a.name+' '+convert(varchar,a.xtype)
end col_type
from syscolumns a, sysobjects b
where a.id=b.id and b.xtype='U'
and upper(b.name) = 'MYTABLE'
order by b.name, a.name, a.colid



select upper(b.name) as table_name, upper(a.name) as column_name,
a.colid, a.length, a.xprec, a.xscale, a.xtype,(CASE WHEN a.xtype=167 then 'varchar'
WHEN a.xtype=175 then 'char'
WHEN a.xtype=61 then 'datetime'
WHEN a.xtype=106 then 'decimal'
END) as col_type
from syscolumns a,
sysobjects b
where a.id=b.id
and b.xtype='U'
and upper(b.name) <> ‘DTPROPERTIES’
order by b.name, a.name, a.colid


Here is a useful query for getting a list of failed jobs from SQL
Server. If you are responsible for tracking and logging job failures, this administrative work can be overwhelming in a busy week. This query will list all jobs that
failed during a given time period. It uses a function that I created,
convert_run_datetime.

<code>
select a.name, b.step_id, b.step_name,

    max(master.dbo.convert_run_datetime(b.run_date,b.run_time)) max_rundt,

    min(master.dbo.convert_run_datetime(b.run_date,b.run_time)) min_rundt,

    count(*) number_of_failures

  from sysjobhistory b, sysjobs a

 where master.dbo.convert_run_datetime(b.run_date,b.run_time)

    between ‘07/24/2006 8:00:00 am’ and ‘07/31/2006 9:00:00 am’

   and b.run_status = 0

   and b.step_id > 0

   and a.job_id = b.job_id

 group by a.name, b.step_id, b.step_name
</code>

powered by performancing firefox

Loading your target incrementally offers a huge performance benefit over running full truncate/reloads, but there is a danger of missing inserts or updates in your source system. It can take hours or days to track down the source of these problems (if it can be done at all!) and problems are generally not found until the customer picks up on it.

So anything you can do to verify the target data with the source is good for your business. You can do simple rowcounts, but that’s an unreliable test. For example, if you’re missing one inserted row and one deleted row, your rowcount test will still pass.

A good checksum technique is a lightweight alternative that can be run asyncronously or run after the load is complete. In this example, I am querying a SQL Server target and using ODBC to connect to an Oracle source to get a list of keys that are not syncronized.


SELECT T.key, S.key, T.checksum, T.num_rows, S.checksum, S.num_rows
FROM (SELECT key, count(*) num_rows,
sum(len(char_col)+len(num_col)+num_col+...) checksum
FROM target_tablegroup by key) T
FULL OUTER JOIN
OPENQUERY(source_server,'
SELECT key, count(*) num_rows,
sum(length(char_col)+length(num_col)+num_col+...) checksum
FROM source_table
GROUP BY key') S ON T.key = S.key
WHERE S.num_rows <> T.num_rows
OR S.checksum <> T.checksum


I don’t burn ISO images onto disk very often, but every time I do it seems like I spend an inordinate amount of time downloading, installing, and trying to use several utilities before I finally find one that works.

The search is over. ISO Recorder is free, fast, and easy to use. Dead easy. Thank you Alex Feinman. What a fantastic tool.

Donald Farmer is blogging again! Donald works as Project Manager of the SQL Server Integration Services team at Microsoft. Donald is a very smart guy; gifted at explaining technology in an engaging and enjoyable way. One technique Donald uses is to heavily salt his speeches with good personal anecdotes. This quote aptly demonstrates why I enjoy watching Donald’s presentations and why I am excited that he’ll be writing more regularly now.

… don’t expect formality. Chances are, I’ll be writing this with a glass of wine to hand. Often I’ll be writing at the end of a long day of difficult design decisions or working through customer issues. On other days, I’ll just have put down a new book of poetry, or have come in from the garden, and those will be bubbling in my mind.