Monday, 17 September 2018

How to Design a Business Dashboard

8 Simple (but not easy) Steps

An effective dashboard can help you achieve your goals faster, and more efficiently. But not all dashboards work as intended, and some are actively unhelpful. So how do you get the design right? And how do you decide what should be included, and what should be omitted? Dashboards originated in cars, and the car is a great place to look for inspiration.

When driving, the objective is to get from A to B safely, efficiently, and without unnecessary expense. So a car’s dashboard communicates the essentials: speed, fuel, and problems with the engine - in that order. Sure, there’s more information such as whether safety belts are on, and doors are closed, but when you are driving the focus is on the essentials.

Varying weather conditions, hazards on the road, and the stress of navigating in unfamiliar places mean that car dashboards have to be clear and uncomplicated. This is not the place for clever, interactive graphics. Car dashboards have data that is large enough to be seen, and simple enough to be understood. But as with many things that seem simple, their elegance hides some clear thinking.

So here’s eight simple, but not necessarily easy, lessons from car dashboards:
  1. Clarify the goal. A different goal will result in a different dashboard.
  2. Identify the biggest risks to reaching the goal.
  3. Identify the single more important metric to help you reach the goal (or avoid a mistake).
  4. Identify other important metrics that will mitigate risks, or communicate progress.
  5. Remove unnecessary metrics.
  6. Communicate information simply and clearly, giving prominence to the most important metric.
  7. Be disciplined in designing the dashboard around these decisions. If the design comes out differently, back up to check each decision.
  8. Do the first things first; don’t start designing graphics before understanding the goal and important metrics.
Business goals are more varied than driving goals, and so don’t benefit from the "one size fits all" dashboard design. And as with driving, the potential to get distracted is constant, making the dashboard a vital tool. Business dashboards should be as well thought out, and effective, as dashboards in cars. After all, none of us want to run out of petrol before we reach the goal.

Friday, 20 April 2018

Effective Performance Measures

Creating an effective performance measure can have a positive, and sometimes dramatic effect. Good measures can:
  • Motivate and inspire people to work towards an important target.
  • Focus attention on what’s important.
  • Identify problems.
In short, a well-designed performance measure can improve your ability to manage a business, project, or task.

A badly designed measure, on the other hand, can:
  • Lead to harmful behaviours (also known as unintended consequences).
  • Have no effect on reaching targets.
  • Give the illusion of progress where no real progress exists. Wheels spin, but forward momentum is non-existent.
In short, a bad measure can cause damage in subtle and invisible ways.

Designing effective performance measures is important; really important. I recently came across a star rating that at first sight had all the hallmarks of a well-designed measure, but on closer inspection was anything but.

It is a rating for teachers – designed to help potential students to choose a new teacher. The star rating is from one to five, and calculated from existing students’ evaluations. After each lesson students are asked to rate their teacher on how well prepared the teacher was for the lesson, how punctual the teacher was, and how happy the student was with the lesson. The calculated result is displayed as a number of stars next to the teacher’s name.

What could be better? This is feedback from the people who are best placed to tell new students what the teacher is like. Well, maybe …

The difficulty is twofold: firstly, the close relationship between teacher and student, and secondly the subjective nature of the rating.

The close relationship means that if the student gives a poor rating, it might hurt the teacher’s feelings, or lead to the student having to explain what was wrong with the lesson. Students are likely to only give bad ratings when a number of lessons have been disappointing, and they have already decided to find another teacher. The close relationship means that only rarely, and in extreme conditions, would students give a poor rating.

The subjective nature of the rating is also problematic. The student might feel good at the end of the lesson, but have actually learned very little. Yes, the teacher was on time, yes, the teacher new their material, but how does a student rate how much they retained? That’s more difficult, and also relies on the student doing their homework and concentrating during the lesson.

As a result, a large number of teachers have a five-star rating, making the rating meaningless. Worse than that, poor teachers believe themselves to be “five-star teachers”, with no incentive to improve.

Yet the star rating system is widely used; often with the same in-built flaws. But could a better measure be designed?

There’s an old saying that actions speak louder than words. So measures that are designed around student loyalty might give better insight into effective teaching, such as measures designed around:
  • The number of active students.
  • Repeat bookings.
  • The length of time a student has worked with a teacher.
  • Students who are working towards an exam, and their exam marks.
These are tougher measures, but might give students a better idea of the quality of teaching.

This is just one example of a measure that looks good on a dashboard, but in reality doesn’t mean a great deal. Unless, of course, your objective is to sell the services of a lot of five-star teachers …

Wednesday, 14 February 2018

How to Improve Data Quality

Is he 98 or 298? It all depends on the data type ....
Designing a new database is exciting; new ways of looking at data are being created. But there is often frustration mixed in with the excitement as there is a certain amount of groundwork to get the project off the ground.

This includes figuring out what information needs to be stored. It is a process of going through the existing business processes, envisioning the new system, and deciding what is or is not needed. All now with an eye towards GDPR.

And making the right decisions about your data will help make the new system a success.

Having decided what data needs to be stored, there are more questions. What range of values do you expect to hold? Will you always know the value when a new record is entered? Could the value ever be negative? What is the largest value the file might hold? It is at this point that the frustration invariably turns to annoyance. "It doesn’t matter", comes back the reply. "We will think about that later" is another favourite. "After all, we’re not short of disk space, so what’s the problem?"

Yet each one of those unmade decisions is an opportunity to improve the quality of the data being held. And potentially the possibility of a bug. These decisions are not about running out of disk space, but about making sure the data you hold is the data you intended. The data type for each field is the most basic and most valuable constraint in a database. It is what separates relational databases from other ways of storing data, such as spreadsheets.

The data that can be entered into a particular field is constrained by a number of things:
  • The data type. This ensures that, for example, a date value cannot be entered into a field designed to hold an integer.
  • Primary and foreign keys. As well as providing relationships between tables, they also check data as it is being entered. A value entered into a foreign key field must match the value in another table’s primary key.
  • NULL or NOT NULL. Fields that allow NULLs risk introducing hard-to-debug issues when data is added to the system. It is often necessary to allow NULLs, because the information may not always be available when records are added. However, fields that allow NULL can produce query results that are not the expected results.
  • Allowable ranges. Whilst some data types automatically limit the range that can be added, others do not. For example, a SQL Server tinyint cannot take values greater than 255, or values that are negative. This might make a tinyint a good data type for a field holding age values. A SQL Server smallint, on the other hand, may be negative or positive, and allows values between -32,768 and +32,767, making is a poor choice for an age field, as incorrect data could easily be entered.  
There are very many more examples of how data types and constraints help keep data quality high. Whilst I fully admit that the age example is not ideal (it would be far better to hold date of birth rather than age), it is easily understood and illustrates the point.

Of course, things can be changed in a database system. You can add constraints later, or change constraints you got wrong. But everything comes at a price. And sometimes that price is reliability because every change in a system has consequences - sometimes unexpected ones.

So if you are getting impatient with never ending questions about your data, think of it as an investment in keeping the quality of your data high for years to come.

Tuesday, 20 September 2016

Encryption and the SQL South West User Group



It is a long and beautiful drive from Reading to Exeter. As the miles crank up, the scenery becomes more lovely. The motorways signs to Glastonbury and Bodmin Moor are a reminder that I really should spend less time in front of my PC, and more time in my hiking boots. But as Exeter cathedral appears in the distance, my thoughts turn to encryption, and SQL Server. I’m here to talk to the SQL South West SQL Server User Group about Always Encrypted, the new encryption feature in SQL Server 2016.

The group is attentive and interested. The discussion soon turns to key management, an important issue when considering encrypting data. No one was sleep walking through the presentation: the young stars of tomorrow had already realized that the inside threat of losing keys was at least as great as the outside threat of being hacked. The balance between security and availability is an issue that faces anyone with an interest in data security. Yet the unrelenting wave after wave of cybercrime makes it hard to ignore the message that the only safe data, is encrypted data. Particularly where personal data is concerned.

So we munched pizza as we talked about backing up encryption keys, azure key vault, and the way the data world is changing.

After eating far too much anchovy pizza we then listened to Rob Sewell’s presentation on PowerShell, and in particular the new SQL PowerShell module. As always, I learnt much, and my head was spinning with ideas as I drove back through Taunton, headed to Bristol, and then back on terra firma as roadworks on the M4 reared their cone-like head. It was a late night, but worth it. You are a lovely lot in Exeter, and I’ll be back. And next time I’ll have a lighter lunch so I can manage a bit more pizza!

Wednesday, 3 August 2016

Writing Microsoft Official Curriculum for SQL Server 2016

SQL Server 2016 was a big new release, with a host of eagerly awaited new features. And like many people, we installed the various beta releases. But for this release we had a bit more of an incentive to try out all the features, including the corner cases, and generally put SQL Server through its paces. The reason? I was on the team writing the Microsoft Official Curriculum to accompany the new release. What a fun project!

Now I don’t know about you, but I’m always conscious of the massive development effort that goes into a new release. We know from the blogs and videos released by Microsoft that there are quite a few development teams, managers, and others working across the globe developing SQL Server. What I don’t think about quite so much, is all the other activity that is required. The marketing, the training courses, and the pile of technical documentation that is also needed.

Microsoft Official Curriculum is just a part of the training documentation needed, but it was still a big job, with a team of writers, a dozen or more courses, with each course containing a dozen or more modules.  There was a lot of researching, a lot of writing, a lot of testing, and more than a few late nights. The team came from different backgrounds including data warehousing, SQL Server development, report writing, and programming. There was a great team spirit, with everyone happy to help who were struggling with something. There was also pressure to get everything written on time – pressure that wasn’t always welcome when something wasn’t working. With pre-release software it isn’t always obvious whether it is a bug, whether you have missed something, or plain and simply have made a mistake.

I wrote a number of different courses including part of the Upgrade Your Skills to SQL Server 2016 course. This course is designed for people who already know SQL Server, and just want to learn about what’s new. It was a challenging course to write because features were still in development, and the documentation was incomplete. And just occasionally something would change after a lab exercise or demo had been written, but before it was tested. It didn’t happen very often, but it did happen, and it added a bit of not always welcome added spice.


The module I enjoyed working on the most was security. In our digital and cloud world, security has become a hot topic, with cybercrime showing no signs of abating. The new features in SQL Server 2016 are much needed, and will help a huge number of organizations to better protect their data.