Data Warehousing and Data Academy ®

Heading For The EX-it

2012-02-10T12:00:00.000Z

Getting data into your data warehouse is easy, using Data Academy - fire up LOAD-it, select a data source, select the tables and "bang" - job done!

But what about getting data out again?

(Why would you want to get data out? In the case of our NHS clients, for statutory reporting!)

Until now, the only way to do this was by writing a custom SSIS package and pulling it into the Data Academy framework with ORCHESTRATE-it - all perfectly do-able, but we figured that there had to be a better way; and now there is... or rather, there will be!

The Data Academy 6.2 is set to include a new module for exporting data from Data Academy; and we've been really zooming-in on ease-of-use - just set up an export definition, select the tables and "bang"! (Does this sound familiar?)

The only question remaining for us is to decide on a module name:

EX-it?

Data Academy 6 Sneak Peeks: Source Code Control

2012-02-03T12:00:00.000Z

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

With Source Code Control, team working is easier than ever: you can check modules in or out; 'diff' selected versions; revert changes you don't want to keep; and see the full history of an object - who changed something, what they changed, and when they changed it.

Data Academy 6 Sneak Peeks: The Warehouse Explorer

2012-01-27T12:00:00.000Z

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!

2012-01-20T12:00:00.000Z

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

2012-01-13T12:00:00.000Z

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

2012-01-06T12:00:00.000Z

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!)

2011-12-30T15:38:00.000Z

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

2011-12-24T12:00:00.000Z

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

2011-12-23T11:13:00.000Z

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!

All Errors Are Equal

2011-12-16T10:16:00.000Z

All errors are equal... right?

That was certainly the way I looked at it when I started work on Data Academy; and it is why a Data Academy 'Event' has only two possible 'Complete' states - Successful or Failed.

But the truth is that that is over-simplistic - different errors mean different things to different people.

If you are using LOAD-it to import a series of data tables from System X, then the failure of Table Y might be a critical problem... or it might not matter a dime!

This was the genesis of the introduction of On Error Resume Next in ORCHESTRATE-it - functionality we've recently extended into LOAD-it. It allows you to choose which errors really matter, and which you can quietly ignore.

Because all errors might be equal... but some errors are more equal than others!

Why Is Siri's Head In The Cloud?

2011-12-09T12:00:00.000Z

Siri, the on-board voice assistant for the iPhone 4S, runs ‘in the cloud’, which as we discussed last week has some serious implications.

But for me, the really interesting question is why?

Why does Siri run ‘in the cloud’ and not on the iPhone 4S itself?

When they were asked this question at the time of the product launch, Apple stated something as sophisticated as Siri could only be achieved by harnessing the massive processing power of the super-computers which run the cloud service.

I’m not so sure about this!

The voice-recognition system on which Siri is (apparently) based – Dragon – ran fine on an x-386 20 years ago, and would run just fine on an iPhone 4S, with its dual-core A5 chip. Admittedly, there is more to Siri than voice recognition, but I remain highly sceptical that this is the real reason.

I think there are some other possibilities!

Perhaps Dragon refused to licence their software for client installs, at least at a cost Apple was prepared to pay. Dragon’s desktop product retails for several-hundred dollars a pop, and they might have been reluctant to byte (sic!) (tee-hee!) into this market by enabling a Siri which could handle things like an entire novel!

Another possibility is that Apple wants to retain control of the way that you use Siri. In particular, they reserve the right to start charging additionally for Siri; or to turn it off if you don’t upgrade to the iPhone 6 in two years time.

You think Apple wouldn’t be this evil? Think again! They already did it, to users of the original Siri – an iPhone 4 app, which Apple summarily killed after the 4S announcement!

Don't get me wrong - I think Siri is awesome!

But she'd be more awesome (and I'd trust her a little more) if she didn't have her head in the cloud!

Fogbugz OLAP Cube

2011-12-06T09:50:00.001Z

Download our free Fogbugz OLAP Cube Definition and start doing OLAP Analysis on your Fogbugz database!

Click HERE to download CUBE-it Zero. (The eval is free!)
Click HERE to download the Fogbugz OLAP Cube Definition.
Open fogbugz_cube.ciz in CUBE-it Zero
If necessary, edit the data source connection to point to your fogbugz server/ database. (The pre-built definition assumes localhost/ fogbugz.)
Click 'Process The Cube' to create a local ".cub" OLAP cube.
Click 'Open In Excel' to open your cube in Excel.
Err...
... that's it!
(As they say in Private Eye!)

Siri & The Cloud

2011-12-02T12:00:00.000Z

Siri, the much-hyped robotic voice assistant for the iPhone 4S, runs ‘in the cloud’. That means that when you ask Siri a question, your iPhone doesn’t answer the question itself – instead, it passes the question to another computer, running ‘in the cloud’ which then passes back an answer.

This raises a number of interesting issues.

Firstly, it means that Siri requires an always-on internet connection - no internet = no Siri! This is fine for Americans, who never leave the boarders of the United States, but for us Europeans it could have a serious implication on the cost of running our iPhones. I spend a fair bit of time in Spain, where I’ll be charged hefty ‘roaming rates’ for any internet access made via my phone, so I usually turn data roaming off… which means I won’t be able to use Siri, even if I’m just adding an item to my on-phone calendar!

Second, Siri requires not only an internet connection, but a very specific internet connection, to Apple’s servers in wherever-they-are! So, as thousands of Apple’s American customers found out a few weeks ago, if those servers go down for any reason, so does Siri.

This second point is actually more significant than the first, because you could argue that the first is just down to penny-pinching on my part; whereas the second means that you can’t rely on Siri as a mission-critical piece of software, which (if you are partially sighted, for example) you might be very tempted so-to-do!

So, Siri is a luxury good, which can’t actually be relied upon to deliver - it is a bit like employing a butler… and then finding him asleep in the broom cupboard with a bottle of your finest sherry!

And in England we have a word for butlers like that: FIRED!

If You Don't Test It... Someone Else Will

2011-11-25T12:00:00.000Z

A customer recently raised a tech-support issue that his LOAD-it module was failing due to square-brackets in the field names.

Impossible!

(As I didn't actually reply to the customer, but did comment to the guys in the office!)

That's unit tested!

And so it was - there is a test for LOAD-it called CheckColumnNameSquareBracket() alongside CheckColumnNameBracket() and CheckColumnNamePercent().

However, it is in the very nature of unit tests that they won't cover every possible condition; and sure enough, when I looked closer I saw that these tests didn't include JAIL-it. And guess what? When I added JAIL-it, the module failed just as the customer had described! Cue humble pie for me in the office, alongside a fix for the customer!

All of which goes to show that if you don't test it... someone else will!

Coding With Two Fingers (WPF + MVC = QWERTY)

2011-11-18T12:00:00.001Z

We've been working with WPF + MVC* for a fair while now, and I have to admit that I'm a bit frustrated. Although the results are looking good - look out for some sneak previews of Data Academy 6, on the blog soon - it has taken a looooooong time to get here - too long, for an old "rad" developer like me! You can't just drop a control onto a form - you have to code-up the view-model,then design the view and finally wire the two up!

At first, I wondered if we were doing something wrong, but I've now come to the conclusion that on the contrary, we are doing it just right - WPF + MVC is a slow way of developing UIs, because... get this... THAT IS THE POINT!

In short, it is like QWERTY!

The original QWERTY keyboards were designed to overcome a flaw in the mechanics of typewriters, where the keys would jam if you typed too fast; so QWERTY was designed with the explicit goal of slowing people down in the short term, in order to speed up the overall process by precluding jams.

WPF + MVC is the same - it slows down the development process in order to ensure that you do things right over the longer term - reducing your susceptibility to bugs, and actively preventing the kind of short-cuts which an old hack like me might take, but which would damage the structural integrity of the system over the longer term.

It might be frustrating in the short term, but like QWERTY I'll get used to it... even if I do find myself having to code it with two fingers!

--------------
* Technically, MVVM.

Silverlight Killed By Flash-Mob

2011-11-11T12:00:00.000Z

Adobe recently announced that they were ceasing development of Mobile Flash.

Several news sites declared this a posthumous victory for Steve Jobs, who refused to allow Mobile Flash on the iPhone; and there is certainly an element of truth to this - it didn't make sense for Adobe to continue to push a solution which was locked-out of such a significant proportion of their potential end-client base.

(Significant proportion? Yes! Android may be winning the war of sheer numbers, but it is doing so at the low-end. Look around you - the techies and the temps might have Androids, but the decision-makers all have iPhones!)

Oddly, however, the biggest impact of the decision to cease development of Mobile Flash will be seen not in San Jose CA, where Adobe are HQ'd, but 850 miles to the north, in Redmond WA.

The end of Mobile Flash means Silverlight - Microsoft's Windows-centered alternative - is now almost-certainly dead too.

Why?

Because HTML5 is now a no-brainer - the only choice to run your application across all platforms.

Until recently, there were reasons to stick with Flash (legacy and leverage of existing knowledge and IP) even if it didn't support the iPhone; and this in turn offered a sliver of credibility to Silverlight, which was similarly hobbled by lack of full cross-platform support.

But Adobe's embrace of HTML5, combined with hints coming out of Microsoft they they-too are now more interested in HTML5 as the go-forward platform for mobile, means that anyone who begins a new development in Silverlight today is officially insane.

Which means that Silverlight is (albeit slightly less officially) dead - killed by a Flash-Mob!

Remembrance Day

2011-11-11T11:00:00.000Z

...

Stack Overflow Exception

2011-11-04T12:00:00.000Z

We're on Twitter!

http://twitter.com/#!/ASPDataAcademy

And thanks to the dark arts of RSS, every post on this blog with automagically be tweeted, including this one.

Yup - that's right folks: this post points to Twitter... which points to this post... which points to Twitter... which points to this post... which... etc. etc.

So if the internet terminates in a bizarre Stack Overflow Exception at midday on November 4 2011, you know who to blame!

What's On The Menu?

2011-10-28T12:00:00.005+01:00

Customization - that's what!

You probably already know that Data Academy allows you to customize your Data Warehouse!

(There wouldn't be much point if it didn't!)

But did you know that you can also customize Data Academy itself?

Take a standard Data Academy screen:

Now let's see the same screen through new eyes!

This is the exact same Data Academy screen, but with a custom UI:

It's Data Academy, Jim, but not as we know it!

Our sister-company, Insource ITC have taken the standard Data Academy UI, and not only customized the logo and CSS styling - they've even moved the menu to the left-hand side of the screen.

Moving the menu took a little VB.NET know-how, but adding a custom logo and CSS styling is really easy - have a look our article on Customizing The Data Academy UI to see how!

Division By Zero

2011-10-21T12:00:00.002+01:00

A few weeks ago, a friend of mine posted the following on Twitter:

EGVroom
You'd think, at this point, computers would be able to chill the hell out when asked to divide by zero. No need to make a scene!
26/08/2011 00:39

And he's got a point - it drives me up the wall too!

Try the following in SQL:

SELECT 1/0

SQL Server will reply:

Divide by zero error encountered.

Grrrr!

The problem is that the correct 'logical' answer to this question is an error - you can't divide anything by zero!

From a 'math' perspective, the answer could be argued to be infinity... but unfortunately that isn't very helpful either, because a SQL numeric variable can't express 'infinity' either!

Perhaps SQL Server could return a NULL, rather than throwing an error? This would make some sense but would be inconsistent with how other systems handle the problem, which I'm guessing is why they didn't do it!

So, what to do?

Personally, I'd be happy if the world just accepted that anything divided by zero is zero and computer systems began reflecting that - after all, this is what we already do in most reporting output:

iif(y = 0, 0, x / y)

So, end-user expectations are already inclined in this direction!

But I can hear the wailing from the Math Department even as a type this, not to mention the gnashing of teeth from the Computing Department, who have to change the way their compilers work!

So I guess we're probably stuck with Division By Zero errors for the moment.

(But that doesn't mean we have to like it!)

Too Many Keys?

2011-10-14T12:00:00.003+01:00

Q: Why is my motorcycle like a Data Academy DIMENSION table?

A: They both have a lot of keys!

My motorcycle has a whopping eleven keys - two ignition, two top-box, two paniers, two d-lock and three disk-lock!

Of course, most of these keys are duplicate keys, so I can throw them away... or at least keep them in a draw at home.

But what about the duplicate keys in a Data Academy DIM table?

My first thought was to put them in a draw at home... but the data protection people got all uppity about that, so I needed another solution!

The answer is the 'Handle Key Errors' check-box in Data Academy - this will take any duplicate keys and put them in the data warehousing equivalent of a 'draw at home' - an _KeyErrors table, which you can then use to trouble-shoot your key problems. (It will also ensure that Data Academy doesn't attempt to insert NULL keys into the DIM table.)

Too many keys?

Not in my DIM tables any more!

(But, sadly, still for my bike!)

SQL Azure Transparency

2011-10-07T12:00:00.003+01:00

As you might have guessed from a number of recent posts, I've been spending a bit of time looking at SQL Azure - Microsoft's new cloud-computing database platform.

One thing which has particularly impressed me is the TRANSPARENCY of the platform, right down to a driver level.

When I first started working with it, I assumed that I would have to install special drivers, as I would for any other new database platform. If I want to start using Oracle, I have to install Oracle drivers; if I want to start using MySQL, I have to install MySQL drivers; etc. etc.

So I certainly would have had no complaints if Microsoft had asked me to install SQL Azure drivers to get my .NET code up-and-running on the new platform!

However, I was amazed to see that this is not the case - the SQL Azure platform is completely transparent - as far as your application is concerned, it is just another SQL Server database, which can be accessed via the standard ADO.NET drivers for SQL Server!

This means that you don't even need to re-compile your code to take advantage of SQL Azure - just point it at the server and away-you-go!

Now there are some limitations to be aware of - for example, SQL Azure requires a clustered index on all tables, or it will error when you try to do an INSERT.

But broadly speaking, the difference is transparent to the end-client - it 'just works' and that is pretty impressive!

SQL Azure Authentication

2011-09-30T12:00:00.003+01:00

Going back almost 15 years now, SQL Server has supported two kinds of authentication: SQL Server and Windows.

Of the two, SQL Server Authentication is the 'old school' method, and Microsoft have been busily pushing us towards Windows Authentication for some years now.

Why? Because it is Windows! And Windows is good! (More Windows!)

So when you install SQL Server 2008, Windows Authentication Only is the default option! SQL Authentication is still there... but only if you really want it!

So you probably won't be too shocked to learn that if you use SQL Azure - Microsoft's hip, young, cloud computing platform, which brings Windows to the web - your authentication options are reduced to a choice of one!

As Henry Ford is alleged to have said, you can have any color you like, as long as it's black!

However, it may surprise you that the 'black' option in SQL Azure is, in fact, SQL Server Authentication!

If you think about it, this makes sense - SQL Azure lives 'on the web', beyond the scope of Windows.

But that doesn't stop it being ever-so-slightly ironic, that in bringing this little corner of Windows to the web... Microsoft had to make it a bit less Windows!

Conflict Resolution

2011-09-23T12:00:00.002+01:00

Time to call in the United Nations - we've got a conflict that needs resolving!

The conflict is in the Data Academy source code - it is a VB.NET class file (FWIW, the View DataSet form) which we've changed in both 5.6 and (separately) in 6.1, so the two versions are now 'in conflict'.

This kind of conflict used to terrify me, back in the days of SourceSafe - so much so that I used to go millions of miles out of my way, taking detours past Pluto, in order to avoid them!

This mentality persisted until quite recently - even though we've been using SubVersion at Data Academy for almost 5 years, my natural instincts were still distrustful of automated conflict resolution - known as 'merging'.

But as I've learned to trust SubVersion so I've come to fear conflict less and less, because I know that SubVersion will (almost always) be able to resolve it, without having to call in any soldiers in blue berets.

Top Of The Tops

2011-09-16T12:00:00.004+01:00

A common problem during the development phase of a data warehouse is that an end-to-end run of the system can take hours - not something we want to be waiting around for during the work-day!

One strategy for mitigating this is to develop with smaller data sources, using a WHERE clause to limit the amount of data being imported.

However, this can get confusing when your imports also need to include a 'real' WHERE clause, in addition to the 'artificial' dev-time constraint.

To make this all easier, Data Academy now includes a SELECT TOP parameter for LOAD-it, allowing you to constrain the size of your test data by limiting the number of imported rows.

This functionality is included in Data Academy 5.6 for SQL Server and MS Access imports; and Data Academy 6 will add support for Oracle too.