Gold in the BI hills

Savvy developers are keeping beady eyes on the BI job market

  • alert
  • submit to reddit

Bridging the IT gap between rising business demands and ageing tools

Comment Organisations are rarely short of data, but the information it contains is often elusive.

Business Intelligence (BI) gives the business user an amazing tool: it turns data into information, making BI an area of huge growth and one where skilled developers are in short supply. It is worth knowing the job roles in a standard BI project so that you can see where you fit in and can secure a lucrative (sorry, useful and effective) role on a BI project.

ETL developer

The first task in any BI system is pulling data from a variety of disparate source systems (Finance, HR, Sales etc), cleaning it, standardising it, and finally loading it into a central repository (often known as a data warehouse). This whole process is known as ETL, Extract, Transform and Load. A new and crucial role, therefore, is that of ETL Developer.

There are many tools for this work: Integration Services within Microsoft’s SQL Server 2005, ETI Solution and Cognos DecisionStream to name but three. Apart from knowing how to drive a particular ETL application (and once you’re familiar with one, conversion to the next is relatively easy) you need to be good at devising the algorithms that will effectively clean a particular set of data.

Proficiency with SQL and the ability to use it imaginatively are vital as you tease out inconsistencies, duplications and other horrors from source system data. It’s a fascinating challenge, especially to anyone with a penchant for problem-solving: I love it. There is no step-by-step ‘do this, this and this and the data’ll all be fine’ approach available for data cleaning; instead you build up a collection of techniques which you apply as and when seems appropriate. If this all sounds a tad informal and lacking rigour, that’s because data cleaning is as much an art as a science.

Before the source data can enter the central repository, someone (or probably a committee of someones) has to agree a set of terminology and business rules to which the cleaned data must comply. It’s common for different systems within an organisation to use the same term to mean different things, and the differences can be fundamental. What is a ‘product’ within the organisation? One that’s manufactured in-house? One you buy in? Both? Neither?

Business rules describe limits or processes: for instance, orders for less than £500 can never attract more than five per cent discount. Without an all-pervasive set of definitions to which all data must conform, information derived from the central data store will lack accuracy and consistency.

Once a set of rules is agreed, cleaned data from the sources are ‘transformed’ in order to meet those rules: achieving this consistency is likely to involve tweaking data types, extensive use of look-up tables and domain checking. Finally the data is ready for loading into the data warehouse.

Once the data warehouse has been constructed, it requires maintenance. The data in the data warehouse is refreshed on a scheduled basis, nightly in many cases, to ensure that analysis is performed against the most up-to-date set of data possible. As a business evolves, changes are inevitable in the source data so new or re-written ETL routines will be required from time to time.

Ultimately the data warehouse is a just complex database and the clues for the next job roles are right there. All databases need to be developed (so BI systems need DBDs) and, once they have been developed, databases need to be administered (DBAs).

OLAP developer

Another role is that of deploying BI solutions as OLAP (On-Line Analytical Processing) cubes. The data in OLAP cubes is not held in familiar relational tables but based on a way of structuring data that involves denormalised data held in dimensional tables which are grouped around a central fact table. The structure is often described as a star schema. The resulting cube of multi-dimensional data is ideal for analysis: it’s dedicated to servicing analysis and rapid response times make it possible for the user to follow ‘train of thought’ analysis paths. Data can be inspected from as many angles as there are dimensions: you might look at sales over time, or by geographical region, by product (now you know what one of those is) or outlet or sales personnel, or indeed all of those or any subset thereof.

Think about DBDs for a minute. They need to understand the relational structure, normalisation, indexing, referential integrity and so on, and also the language for manipulating relational data, SQL.

Developers who are designing and creating OLAP cubes will often need to extract data from relational tables, so they need to have all of the skills of a DBD. In addition they have to be able to think about, and manipulate data, as multi-dimensional sets.

The fundamental difference is that relational data is unordered while multi-dimensional data is highly ordered. This doesn't sound like much of a difference, but it is huge. Think about a very simple problem. A user asks to see the sales figures for two different product groups over a year but displayed as a monthly running total.

Graph showing results of MDX in action

A simple (and perfectly reasonable request) that happens to be very difficult to deliver using pure SQL because SQL knows nothing about the order of the data. When we put data into a multi-dimensional cube, it is perfectly ordered so the problem is trivial to solve, as long as we have a language (essentially an equivalent of SQL) that allows us to manipulate multi-dimensional data.

That language is MDX (Multi-Dimensional eXpressions) – and knowledge of MDX is a very saleable skill. With MDX you can manipulate data (for tasks such as calculating sums, averages and so on) and also query it with commands that ‘understand’ the hierarchically-ordered data in the cube. For example, the MDX to create the running total shown above is:

Sum(YTD(Time.CurrentMember), Measures.Sales)

So for developers, the business intelligence is suggesting that BI is not only here to stay but is also presenting a host of opportunities. Now is the time to take the plunge. ®

The Power of One Brief: Top reasons to choose HP BladeSystem

More from The Register

next story
BBC goes offline in MASSIVE COCKUP: Stephen Fry partly muzzled
Auntie tight-lipped as major outage rolls on
iPad? More like iFAD: We reveal why Apple fell into IBM's arms
But never fear fanbois, you're still lapping up iPhones, Macs
Nadella: Apps must run on ALL WINDOWS – PCs, slabs and mobes
Phone egg, meet desktop chicken - your mother
White? Male? You work in tech? Let us guess ... Twitter? We KNEW it!
Grim diversity numbers dumped alongside Facebook earnings
Microsoft: We're making ONE TRUE WINDOWS to rule us all
Enterprise, Windows still power firm's shaky money-maker
HP, Microsoft prove it again: Big Business doesn't create jobs
SMEs get lip service - what they need is dinner at the Club
ITC: Seagate and LSI can infringe Realtek patents because Realtek isn't in the US
Land of the (get off scot) free, when it's a foreign owner
Dude, you're getting a Dell – with BITCOIN: IT giant slurps cryptocash
1. Buy PC with Bitcoin. 2. Mine more coins. 3. Goto step 1
There's NOTHING on TV in Europe – American video DOMINATES
Even France's mega subsidies don't stop US content onslaught
prev story


Designing a Defense for Mobile Applications
Learn about the various considerations for defending mobile applications - from the application architecture itself to the myriad testing technologies.
How modern custom applications can spur business growth
Learn how to create, deploy and manage custom applications without consuming or expanding the need for scarce, expensive IT resources.
Reducing security risks from open source software
Follow a few strategies and your organization can gain the full benefits of open source and the cloud without compromising the security of your applications.
Boost IT visibility and business value
How building a great service catalog relieves pressure points and demonstrates the value of IT service management.
Consolidation: the foundation for IT and business transformation
In this whitepaper learn how effective consolidation of IT and business resources can enable multiple, meaningful business benefits.