Data Warehousing & Data Academy Blog
http://dataacademy.blogspot.com/
If you're sad enough to have read all the small print, you'll know that Data Academy used to be sold by a company called 'Insource'.
That has changed recently - we've been "spun-off" into a new business called 'Academy Software Products' - but we're still the same team, working in the same offices; and at heart, we are still all about the in-sourcing!
The philosophy behind Insource was always that, rather than 'out-sourcing' your IT services, you should 'in-source' them.
Instead of trusting your data warehouse to a third-party, who might one day let you down, you should bring it in-house, and we will provide the software (and where necessary, the people) to help YOU stay in control.
Data Academy has never sought to lock you in. It is Your data warehouse - with a capital 'Y'. The data is held, openly, in a completely generic SQL Server database; and even if you un-install Data Academy, all of Your data will still be there.
These days, there are a lot of people trying to sell you data warehousing solutions, some of them even hosted in 'the cloud'. But a great solution is about more than some buzz-words, and a Three Letter Acronym beginning with 'X'.
And when a product makes things as straightforward and reliable as Data Academy does, in-sourcing is still the best option.
We might be called 'Academy Software Products' these days, but on the inside...
We're still Jenny from the block!
SQL Server 2005 was a HUGE upgrade from 2000. So much so, that when we upgraded Data Academy, huge chunks of it had to be re-written; and for a while we were supporting two different code-bases.
So, how did things go when we upgraded from 2005 to 2008?
Well, even before I started, I knew that the last thing I wanted was to lumber myself with supporting two branches in the source code, so my first hope was that maybe (just maybe) we could upgrade to 2008, and 2005 would still "just work". After all, the API was supposed to be backwards-compatible.
No such luck! Backwards-compatibility only went so-far - API calls, system table names and even actual functionality had changed, rendering the 2008 version very, very broken on 2005.
But there was good news too - all of the changes (bar one*) were pretty minor - so minor, in fact, that you could pretty-much do a "Find & Replace" on the 2005 source code, swapping-out "IDTSInput90" for "IDTSInput100" etc. etc.
(Yes - the underlying API is still called 'DTS'!)
It isn't the kind of thing you'd want to do every time you need to build the system, though! So I set-about automating the process, via a 'pre-compiler' which takes the 2005-compatible source code and re-writes it for 2008.
It then runs all 400-odd unit tests in the 2008 environment, to check that everything is working as it should; before creating the MSIs which you can download and install.
It isn't quite IDEAL - there are a completely different set of installers for 2005 and 2008 - with the 2008 version having an '08' suffix on the minor build number. So, "5.5.99" is a 2005 build, while "5.508.99" is the 2008 equivalent.
Nevertheless, it does give YOU the 2008 compatibility which you are going to need sooner or later, while I get 'two for the price of one' in terms of source-code management.
So, if you are asking us what kind of music we play, well...
We got country AND western.
-------------------------
* We had to completely change the way ORCHESTRATE-it process CUBE-it Cube Spaces.
In The Pragmatic Programmer, the authors tell the story of a UNIX programmer who spent weeks trying to prove that the "select" call was broken, before finally admitting that the seemingly-impossible was really true - the bug was in his own code!
The moral of the story is one I try to remember every day:
SELECT ISN'T BROKEN!
So when a customer reported that they were seeing an overflow error in one of their LOAD-it modules, my instinctive first reaction was that there must be a bug in Data Academy.
Using our testing environment, I put-together a rough version of the customer's scenario and ran-up LOAD-it.
No bug!
So, I called-up the customer and between us we configured a test system that looked a LOT like their environment.
Still no bug!
Eventually, I ended up working ON their live system, and yes - there was the bug! But what was causing it?
To simplify a long story, it became apparent that SSIS was internally trying to cast a numeric value, with 48 decimal places, to a 24-character string, and this was causing an overflow.
But why? None of the other tables from this source system were experiencing the problem, so what was Data Academy doing differently?
After much unproductive prodding-around, I decided to factor-out Data Academy, and to try to create the same data import routine "old skool" in BIDS.
I got exactly the same error - and furthermore, any attempt to "force" the data type, resulted in a package validation error. SSIS really did believe that the data was a string, because this is what the OLE DB Provider was telling it! For reasons which are still not clear, there is a bug in the Provider which manifests itself in this one table, for this one customer!
There was nothing for it but to work-around the problem, by importing the data initially into a string field, using a CAST at the source.
And, of course, to accept that sometimes... just sometimes... select actually is broken!
Read More...