Data Academy 6 Sneak Peeks: The Warehouse Explorer

Data Academy 6 introduces the Data Academy Console - a Visual Studio-like development environment, dedicated to your data warehousing project!

The Warehouse Explorer is equivalent to the Solution Explorer in a Visual Studio project - it allows you to zip around between Data Academy modules with unprecedented ease.

When The Problem Is That You Don't Know What The Problem Is!

One of the most "interesting" aspects of with working on Data Academy is that I often don't understand the full nature of the problem I'm trying to solve.

In short: the problem is that I don't know what the problem is!

This isn't because I'm stupid... not entirely anyway!

It is because I tend to work by taking a micro problem - for example, a transformation with 500 input columns, called Period_1, Period_2, etc. - and solving it at a macro level - by (in this case) adding the capability to match any number of input columns via a Regular Expression.

The difficulty is that, although it is straightforward to test my code against the micro case, there is no macro case until someone takes the functionality and starts actually using it... usually in ways I could never have anticipated!

This is one of the reasons why we (as a business) have to have the ability to turn around code fixes quickly, and for the customer to implement them straightforwardly - and we pride ourselves on this. If you find a bug we will fix it rapidly and provide you with a new binary, which you can install in just a few clicks.

You may find the same thing in your data warehousing project too - you don't know what questions are going to be asked of it; so you need to be able to turn around fixes quickly when it comes up against one it can't answer - which is when Data Academy itself really comes into its own, with its focus on the data warehouse as a development project, complete with source code control and package-release support.

Pattern-Matching In SQL SELECT

Data Academy has had the ability to do a pattern-matched SQL SELECT in Un-Pivot transformations since version 5.6; but we've now added the same functionality to Regular transformations in Data Academy 6.

This means that you can select all columns matching a particular Regular Expression pattern, with a single swish of your Data Academy magic wand.

So the following RegEx expression:

^Col

... when combined with the following RegEx Target Field Name:


+.

... will output all columns beginning with Col - i.e. Col1, Col2, etc. - to a Target Field of the same name.

Median Aggregation in SQL Server

It is one of those standard interview questions: how would you calculate a Median() aggregation in SQL Server?

The usual answer involves temp tables (or if you are especially hip and modern, the OVER clause) combined with a couple of well-judged SELECT TOPs.

And, sure, all this is do-able, but there has to be a better way!

(To be honest, that "better way" really ought to be Microsoft building it into the actual product; but in the meantime...)

Data Academy* now supports a dbo.__Median() aggregation in TRANSFORM-it, which you can use just like COUNT() or SUM().

In Data Academy, this is as simple as setting the Group Function to:

dbo.__Median(#)

So next time someone asks you how to do a Median Aggregation in SQL Server, there is a new reply in town:

Use Data Academy!

--------
* SQL 2008 / 2008R2 versions only - sorry 2005 folks!

The 4:30 Bug (And Why It Says Our Customers Are Smart!)

I'm a bit of a "morning" person - I'm often at my desk before 6am; and by mid afternoon I'm definitely starting to wind-down.

So why is it that bug reports always fly into my inbox at around 4:30pm?

At first I thought I was just being unlucky! Or paranoid! Or both!

But finally I realized that not only was there a genuine pattern of late-afternoon bug reports; but that it said something rather interesting about our customers:

That they are out to get me?

No! (I don't think so anyway!)

Here is what I think happens: 

A problem can occur at any time of day; and when it does, the typical Data Academy customer will try to work things out for themselves: why is this happening? Is it something I've done? Can I work around it?

But eventually, after all these questions have been asked in vain, there will come a point in time when the customer is forced to concede defeat and report the issue to us... and that time is usually about 4:30pm! (Regardless of whether the original problem was discovered at 9 in the morning, or 2 in the afternoon - because it is human nature to give things 'till the end of the day' rather than (say) '3 hours'!)

So, what this says about out customers is that they are pretty smart folks - they are always trying to understand the problem and find the answer themselves... at least until 4:30 rolls around!

I still hate The 4:30 Bug of course - and if I'm honest, I always will!

But at least I can console myself with the thought that there is a very good reason for it!

Christmas Is Coming

Christmas is coming - tomorrow, in fact - and if you're still looking for the perfect gift for the special Business Analyst in your life... then you're leaving it rather late!

So, why not save yourself a last-minute trip to the shops, and click onto www.bi-lite.com where a copy of CUBE-it Zero Personal Edition is only $29!

Feeling extravagant? Why not show them how much you really love them, with a copy of CUBE-it Zero Pro?

It's just $149, and comes with built-in support for full SSAS Cubes!

No?

Oh well... it was worth a try!

Cooking The Record Books

Who is the greatest batsman currently playing test cricket?

The official ICC rankings say Kumar Sangakkara*. However, most fans would probably vote for Sachin Tendulka - the Indian star, who has scored more runs than anyone else in test history: more than 15,000!

However, I'm going to propose an alternative candidate: England vice-captain Alistair Cook.

But, why? Cook has scored less than 6,000 runs, and stands only 3rd on the ICC rankings!

Yes... true... but...

The ICC rankings tell only the sort-term story, so we can discount them!

The all-time run-scoring table is where it matters, upon which Sachin stands atop, but his career is drawing to a close, and he is likely to retire soon, with around 16,000 runs to his name.

Cook on the other hand is still only 26, and currently scoring at at a rate of about 1000 runs/ year.

So let's do some extrapolation!

(If only because it keeps this post vaguely on-topic in terms of data warehousing!)

If Cook retires at 38 - the same age that Sachin is now - we can expect him to have accumulated a massive 18,000 runs!

And that would be one mightily COOKED re-write of the record books!

--------------
* At time of writing!