Is this a leap year? Simple test in SQL

DECLARE @YOURDATE DATE = Getdate() 

SELECT Isdate('02/29/' 
              + Cast(Year(@YOURDATE) AS CHAR(4))) AS isTHISISALEAPYEAR 

Very quick, very simple test.  If February 29th is a date, then it is a leap year.  Let SQL server do the work for you on this one.

Here is an expanded example to just get the information for any set of years.

DECLARE @StartYear INT = '1990' 
DECLARE @EndYear INT = '2020'; 

WITH cte_makedates 
     AS (SELECT @StartYear YEAR_NB --Start Date  
         UNION ALL 
         SELECT year_nb + 1 
         FROM   cte_makedates 
         WHERE  year_nb + 1 < = @EndYear --End date  
        ) 
SELECT *, 
       Isdate('02/29/' + Cast(year_nb AS CHAR(4))) AS isTHISISALEAPYEAR 
FROM   cte_makedates 

Comments on Source Tracking in Critical ETL processes.

This is more of a comment on current state in many companies on ETL, with a small suggestion at the end.

In the past, I have worked on ETL processes that consume data from a data source, perform work on that data, and then submit the data further down the line with aggregation and other attributes from other sources added to it.  This is your basic Extract, Transform, and Load type of environment.  You take data from one place, and you move that data to another place.

Recently I have had the misfortune to work on many ETL processes that are designed without failure in mind, and no method of tracking data from the original source that it came from, to the final destination it arrived at.  In the best case scenario you can "re-run" the data, and during debugging somewhere 3/4 the way through the code you can determine what record each individual transaction WOULD have made in the destination, so that you can then figure out what destination should be (if there is a reported error).

This, of course, is a terrible way to go at it, especially if you have to process several millions of records at a time, and the system deletes the source data on every single run and the original data may not even be available within a timely manner (IE it could literally only exist on a tape drive).

The best method for all of this is to simply bite the bullet, and give yourself the data that you need to determine what went wrong, or how to identify the original data so that you can limit it.

In a recent version of this problem, I had sales data coming in from a source, and the data went through considerable amount of process (think 50 rules or so, all inter-dependent) and the final output had no individual record or identifier that linked it back to the original record that was generated. (IE if the input was a, the output might be 4 records of 2.5,2,.25,.25).  

The problem with this system was that it took 4 hours to run, and if you wanted to debug even a single record, the only way to do so was to eat 3-4 hours of processing time. 

Solution?  In my case I separated out the long running pieces into defined segments of code, and made them independent of one another.  When consuming 3 million records but you need to only identify what happens on the 2.5 millionth record (when order of processing matters) I broke out the first 3 hours of processing into its own chunk, so that I could run the process for 3 hours, and then have unlimited time to asses the single record and how it was flowing through the system.  

The best solution?  Don't design systems where you get rid of your originating data on a daily basis, and make sure that you can track your data back to its original source.

If you have an input record of 5, and it creates 4 output records, have a record id or source linkage back to that original record.  Sure, it will take up space, but even if your record id is an identifier to a physical archived file (IE record position in the file, if your files are too large to store) this is better than not having any linkage at all.

Then MAKE SURE YOU TEST IT.  I have seen a situation where someone tried to do this, but the method they were using for reading the file was doing a sort and they were doing the record count AFTER the sort had been done.  The idea was great, but the code was literally misplaced in the order.

If you create something for a purpose such as testing, make sure that you use it for testing to make sure it fulfills your needs.

In the end, make sure you have the ability to test something, and link its results, at every single step of the way.  This means you have an understanding of the code, an understanding of what it is doing, and you can write good documentation and explain it to others later if need be.

SSIS vs Informatica

So I have been thinking a lot about Informatica vs SSIS lately, and I have decided I do finally have some opinions about where Informatica is much better and SSIS is better.

If I could say Informatica was better than SSIS in one major way it is in Debugging.  In SSIS the ability to debug a problem is relatively difficult if you need to get down into the guts of a data flow transformation, but this is something that is relatively easy when you are using Informatica.

SSIS though has some bonuses in that it has much better such as configuration and logging.  SSIS has the ability to do package configurations at the database level, and do so relatively dynamically.  The fact that SSIS also maintains each package as its own distinct "program" also helps.  Informatica has some issues when it comes to logging, especially if you have something that can run multiple times but not error consistently, if you don't configure it right you lose the instance that you errored on, where SSIS logs to the databbase per run, and isolates its logging as such.

Informatica also does a much better at handling large amounts of data in its built in components.  IE when you are doing 10 million or so rows through a lookup transform in Informatica, if you configure it right, it is going to outperform SSIS hands down.

SSIS beats it in one major way though, you can leverage SQL server much more efficiently, especially in the case of pulling data from stored procedures.  In Informatica you just don't have the SQL server integration, which means you can't have quite the power of SQL server at your fingure tips the entire time without having to do some hokey hacks to get around how Informatica is built.

SSIS and Informatica both have built in scripting language ability, but SSIS far out performs because it lets you use C# and import C# libraries.  I know there are those who believe Java is powerful, but there are a lot of things you can do with .NET frameworks that come easy to install, and generally standard, that gives SSIS a big advantage. Both Informatica and SSIS have issues when it comes to handling debugging of the languages.

Just some thoughts as I sit down.  I have had the opportunity lately to go back and do some difficult SSIS tasks while doing some particularly hard tasks in Informatica along the same line, and I find both to have interesting ups and downs when it comes to development.

Data Dictionary

A found a great product that produces some nice data dictionary's.  There is sql code provided, as well as a document creation tool.

http://www.csvreader.com/posts/data_dictionary.php

I like it quite a bit, it makes it easier to produce a data dictionary for the user to view when requested, and gives the ability to manage descriptions at the database level so generating documentation on-going is easy to do.

Heads up for ETL parsing C# library

I just wanted to give a heads up to the product from http://www.csvreader.com/.  I have worked with the creator of this product, and I have worked with the DataStreams product he sells quite a bit, and it is hands down the best C# ETL product I have ever worked with.  It simplifies all of the "hard" stuff you have to work with when it comes to managing data in C#, from parsing of data files to creating correctly formatted outputs.

Since it implements the parsing as an IDataReader, it makes importing data into databases dead easy, and it allows for record by record manipulation as the data flies by as well.

Inserting the results of a stored procedure into a temp table.

I recently had a co-worker who was unable to duplicate the results of a well known, and assumed working, stored procedure, and wanted to find out if I could help them with getting data from a stored procedure.  The problem?  The stored procedure had nearly 100 columns, and many of the discussions on the internet basically made it very difficult to figure out the best way to get the information.  There are a LOT of methods out there.  One uses OpenRowset, but this has configuration issues in many places. Others use fantastic methods revolving around the use of XML parsing, or even dropping it into C#.  These are OK, for their needs, but in reality if you have access to the stored procedure, this is one other method that you can use that is MUCH quicker as long as you are using sql server management studio.

The method I use is to do this.

1.  Copy the code for the stored procedure, do this is in a test environment where you have full access.
2.  Change the name of the stored proc (creating a new one) and add a unique word to the end, I call my procs that I do this with <procname>_CREATETEMPTABLE.
3.  Open the procedure, find the result set that you want to get the results from, and instead of return the select statement to the proc.  Add a an insert into statement into a dbo.<insertclevernamehere>
4.  Open SQL Server Management Studio and find the table dbo.<insertclevernamehere> and right click, and get the schema by going to Script Table As->Create->New Query Window.
5.  Copy the guts into a create table script for a temp table.
6.  Run the stored procdure by doing

Insert into #mytemptable
(
...columns
)
exec <yourstoredprocedure>

And it should work every time.

So, how does this work in real life?

Lets say I have a stored procedure, call it.

dbo.MyExampleProc

And lets say it has 2 columns returned, but we will do this as if it had 100.

The proc is defined as

create proc dbo.myexampleproc

as

SELECT

cast(1 as varchar(10)) as col1,cast(2 as int) as col2

When we run the proc, it does not return the schema, and in our case we can't figure out what that Schema SHOULD be without a lot of manual work or looking at documentation that no longer exists.

The next step would be to make it so that the data goes into a temporary, but accessable database table.

So we change the proc and do.

create proc dbo.myexampleproc_CREATETEMPTABLE

as

SELECT

cast(1 as varchar(10)) as col1,cast(2 as int) as col2

into dbo.myexampleproc_CREATETEMPTABLE_OUTPUT

Now that the data is in the table dbo.myexampleproc_CREATETEMPTABLE_OUTPUT we then script the table out using Script Table As->create->New Query Window.

This provides the data definiton:

CREATE TABLE [dbo].[myexampleproc_CREATETEMPTABLE_OUTPUT](

       [col1] [varchar](10) NULL,

       [col2] [int] NULL

) ON [PRIMARY]

Then I delete the table myexampleproc_CREATETEMPTABLE_OUTPUT and the stored procedure myexampleproc_CREATETEMPTABLE.

So then we can stick this somewhere and have a useful script with it to look at data, for example, the below, and we are done.  

USE ETLExamples

/* generate schema step, create a new stored proc exactly like the old one, execute it but make the result set go into a dbo.<temptablename>

For example, I created a temp one with _CREATETEMPTABLE, and in the final result set i had it go to a table dbo.<temptablename>

I ran it with the parameters (example below) that are the same paramters I want to capture.

I then deleted the stored proc, then used sql server management studio to grab the schema of the table (right click on the table, script)

Then I took the table column definitions and inserted them into the create table script for the temp table below.

Then when you execute the

INSERT INTO <temptable>

EXEC <stored proc name>

It just inserts the results of the proc into the temp table.

*/

--exec dbo.myexampleproc

IF object_id('tempdb..#myreallyfancytemptable') IS NOT NULL

BEGIN

   DROP TABLE #myreallyfancytemptable

END

--- STep 1, create the definition of the table that exactly matches the definition of your stored procedure

create table #myreallyfancytemptable

(

       [col1] [varchar](10) NULL,

       [col2] [int] NULL

)

-- insert into the temp table.

INSERT INTO #myreallyfancytemptable

EXEC [dbo].[myexampleproc]

select

* from #myreallyfancytemptable

The results from the table are:

col1        col2

1              2

Are "Normal" humans excluded from programming?

http://developers-beta.slashdot.org/story/14/07/09/131243/normal-humans-effectively-excluded-from-developing-software/

I was reading the above article about how a programmer felt that "normal" humans were blocked from programming, and I wanted to initially think to myself "Of course they are!" After this initial feeling of personal superiority, I realized all of the examples of how false this is.

The great majority of good programmers that I know in the field are not fantastic savants, they are regular people doing a job.  The only caveat I have in programming is the difference between a programmer, and a good programmer.  Your average, run of the mill, programmer that can do most tasks, and do research to find basic solutions, is someone who is either self trained or had a small associates degree in software development.  The good programmer is one who enjoys it, loves finding out new problems, and has a good memory for how things have been solved in the past and can see how to use their experience to solve new problems, and can also determine when a new approach SHOULD be used, even though an old one CAN be used.

An average programmer will approach a problem in the "I have a hammer, everything is a nail" solution.  Lets take something like SSIS.  A lower level SSIS developer, trying to figure out how to get data from one system to another, and needs to do a lookup against a very large data set on the same data base, might pull both sources in and use a join or lookup transform to get the required data.  A good programmer, with experience in both (and finding out the SSIS package chokes on such large sets) might make the next step and just use a select statement with a join against the data from the sql server database.

This seems obvious that these two solutions exist, but in the real world you will see hundreds of examples of people using a non optimal, obvious to them, solution repeated over and over simply because it works, but not because it is good, or easy to understand to anyone else.

In reality everything has a good, better, best solution set, but no one is going to ever hit the "best" solution for non trivial problems, especially the first time.  What you pay for in a "good" programmer, vs your regular programmer, is the experience to get "close" to the better solution, and sometimes even between the better and best categories.  Your software will be more reliable, your hardware less strained, and your coding time much shorter, and it will be worth every penny, but in the end you don't have real access to truly good programmers with enough experience in all of the technologies you have to really shine in this field.

There are so many factors in the idea of "good", "better", "best" in programming that the ideal of having some kind of elitism toward the entire field as shown in the article doesn't make sense.  Programming is a trade, most of the jobs in programming are trade style jobs with specific scopes of specialty.  You aren't going to take an ETL developer, toss him at a web development problem, and have as good an experience as using a dedicated web developer, even if the ETL developer is "the best ETL developer you have ever had!"

Programming is NOT rocket science, you can be very competent at programming if you have the ability to visualize your problems and properly define them, at some point it just starts to be syntax, and the competence to know what needs to be created vs re-used.

Kind of a ramble, but I just wanted to get my views on it out there on my little informal blog space.