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.

Wednesday, 27 July 2016

5 Awesome Benefits of PowerShell

Jeffrey Snover, Microsoft. Inventor of PowerShell
If you thought that the command line had been confined to history, think again. There’s a not-so-new kid on the block that is getting a lot of attention. It’s called PowerShell, and was first released with Windows 7 and Windows Server 2008 R2. The latest release, version 5.0, will be included in Windows Server 2016 which is currently in technical preview.

PowerShell is a scripting language that is designed to automate server tasks. It can run interactively or in scripts, and is super useful for all sorts of things. It was a ground-up redesign, and has a refreshing elegance to it.

First, a few basics. PowerShell is based on .Net classes and is implemented using cmdlets. A cmdlet has a verb-noun syntax that is designed to be descriptive, and as far as is possible, intuitive. Examples of cmdlets are get-help and get-command.

Cmdlets are organized into modules, with each module containing cmdlets for a particular product. There is a SQL Server module, an Azure module, and an Active Directory module, plus many, many more. Modules are either loaded, or unloaded.

But why would you use PowerShell rather than the GUI? There are a number of awesome benefits:
  1. A PowerShell script is testable. Once developed, it can be tested and signed off to say it does what it says on the tin.
  2. A PowerShell script is repeatable. It may be quick to do something once using the GUI, but it is slow and error-prone to do it many times.
  3. A PowerShell script can be saved. For tasks that are not done very often, or need to be done by different people, having a script ensures that the job gets done in the same way every time.
  4. You can do more with PowerShell. Not everything is built into the GUI, so PowerShell lets you do more stuff than is pre-built into the GUI.
  5. PowerShell is less exciting than the GUI. If you’ve got complex changes to put into production, PowerShell takes much of the risk out of the deployment. You can test the scripts multiple times, and in different situations. You can be sure that the script does exactly what is intended, without any variation. And even if it is run at 3am, it will do exactly what it was intended to do. So much less exciting than hoping it is all going to go OK.
As the move to the cloud is gaining momentum, and Microsoft Azure is increasing in popularity, PowerShell is proving its worth. It is ideal for working with Azure, enabling resources to be commissioned or removed easily.
But perhaps the most awesome aspect of PowerShell is its irrepressible inventor, Jeffrey Snover. With his bulging wardrobe of bright ties and impish grin, you would think he had just invented ice cream. Maybe he has – the Windows version of it anyway.