Opinion: Data professionals shouldn't be quick to mock Excel and Power Query

Knocking Access was a popular sport over the last decade or more. Many data professionals saw Access as a real problem. Lots of silos of unmanaged data grew up across organizations and things could get out of hand pretty quickly. I saw all the expected problems that come from a lack of centralized management of data.

Some issues were quite nasty. I remember doing work for a company that did aircraft maintenance and had depots all over the country. Every depot had a copy of an Access database, but every single one had then modified it in different, and in many cases, conflicting ways. Then they decided to centralize the data, and oh what a pain.

I still see lots of SQL Server people mocking Access.

The upside of Access

However, I think that many have also missed the core advantage that tools like Access brought. Power users were often fed up waiting for IT development teams to build what they needed, and so often, what was then built wasn't what was needed.

Today, I can see so many great SQL Server applications that would never have existed if the power users hadn't started them in Access.

Excel and Power Query

Now, I'm seeing exactly the same reticence regarding Excel and Power Query.  IT teams are worried that so many unmanaged silos of information are growing up around their companies, and that Excel and Power Query are at the center of it.

I've been in the industry a long time. It's really, really rare for me to look at a new tool and be inspired. But Power Query had that effect on me. 

Most organizations have a bunch of people that spend their days in Excel. It's pointless fighting that. And the success rate of introducing brand new BI tooling to those people is abysmally low. What I liked about the first incarnations of parts of Power BI is that if you took a person who already used Excel well, and just added more features to it, you were drastically more likely to see them actually use it.

And that's the beauty of Power Query. It lets those power users import and massage data into the shape they want. And, importantly, they will in many cases, create the start of important new applications that the organization needs.

I'm not talking about dabblers who aren't at the power user level, who could just spend endless hours not getting anywhere. I'm talking about the people who can make progress and start to create applications.

If their work becomes important, there are good options for IT to take over those applications and professionalize them. But without these tools, those applications would probably never exist in the first place, so don't be quick to mock them as though they're not "real" applications.

Opinion: For companies, is there any value left in country-level domain names?

Recently, one of my MVP colleagues was tweeting about the problems he was having dealing with a country DNS name provider, and how much it cost. I've thought this for a long time, but it really does make me wonder if there's much value left in country-level domain names, at least for companies.

The Gold Rush

Awesome image by Lucas Benjamin

Back in the 1990's, I remember the "gold rush" that happened when people where trying to register domain names for their companies and how intense the competition became. If you didn't get in early, you were fresh out of luck. We made a point of getting all our customers to register quickly. Even then, some missed out on their ideal names, particularly if they were just trying to register a set of initials or abbreviation.

The registrars for country-level domains (i.e. somecompany.com.au) were often monopolies, had horrid customer services, and were charging like wounded bulls.

It didn't take me long to realize that a country-level domain is both a blessing and a curse:

  • If you want to have a global presence, it's constraining.
  • It might have value if you want to appeal to other local companies.

Stick with Top Level Domains

I decided that in future, I'd mostly use top-level domains (i.e. somecompany.com).

The registrar system has opened up and improved a bit for country-level domains, and the pricing has improved somewhat, but now, I can barely see the point in them.

Yes, I can go to a site like lenovo.com.au but if I visit lenovo.com instead, it's going to quickly work with me based on my location anyway. And sometimes, I really do want to deal with the global company regardless.

One suggestion is that it's easier to get names from .com.au than it is to get the same names from .com as there are less used. But do you really want that type of confusion?

Avoiding Errors

For our own domain (sqldownunder.com), I do keep registering sqldownunder.com.au but I only do that to avoid errors when someone adds .au to the end of our web address or one of our email addresses. But the .com.au domain still costs way more than the .com domain, so it's getting harder to justify.

Does anyone see any other real value in country-level domains?

 

 

 

Opinion: Can your staff avoid customer problems?

I deal with a lot of different service providers, and something that really sets the good ones apart, is their ability to avoid customer issues.

Can your staff avoid customer issues?

Awesome image by Holger Link

Years back, I used to deal with our largest national airline, QANTAS, a lot. They were actually pretty good at helping you out once you had a problem, if you had status with them. What they were extremely poor at, was avoiding issues in the first place.

The biggest issue in their case was that the people I was dealing with, had no authority to avoid a problem. They had guidelines that let them fix a problem once it had happened, but they had no authority to take actions that would avoid a problem happening in the first place.

Let me give you an example:

Snowy New York

I finished with a series of meetings, etc. in New York, on a Wednesday. I needed (really needed) to be back in Melbourne on the Monday. 30+ people were going to be in a room waiting for me. On Thursday I had a scheduled flight to Los Angeles.

The US weather people were predicting the worst snow storm in 10 years, for the New York area the next day. I was obvious that my flight wasn't going to be happening on Thursday. So I called the airline, told them the situation, and asked them to get me out of New York that day. I didn't even care where I went: Chicago, Dallas, Atlanta, anywhere. I just couldn't be in New York on Thursday.

So what did they tell me?

Them: "Your flight is still scheduled".

I said: "Have you looked at the weather forecast?".

They said: "Your flight is still scheduled".

I said: "Tomorrow, you'll be dealing with hundreds of people with a problem. Can't we just sort it out now and avoid that?"

They said" Your flight is still scheduled".

No matter how I pleaded with them, and no matter how obvious it was to the person on the phone that the flight would end up cancelled, the person had no authority to avoid the problem. I have no doubt that they genuinely wanted to help; they just couldn't.

If you want to have a good customer outcome, you need to empower the people working for you to make sensible decisions, by themselves.

So Thursday morning, I rang QANTAS to find out when I could check in.

They said: "Your flight is cancelled and we've scheduled you for Friday".

Friday had an even worse weather prediction than Thursday…

 

 

 

Opinion: Start meetings ontime – "give it a few minutes" is rude to other attendees

I attend a lot of online meetings nowadays, and I can't tell you how often at meeting that starts at 10 AM actually ends up starting at 10:05 AM or 10:10 AM to cater for people who are running late. Right now I'm in yet another meeting that hasn't started yet, as we're just "giving it a few minutes for stragglers to join".

Now it's a different story if there is a specific person who really is needed in the meeting, and they've let you know they are running a few minutes late. But I see this as routine in pretty much every meeting I attend. Meetings almost never start at the correct time.

Don't do this!

All you are doing is being rude to, and wasting the time of, the people who did turn up at the right time.

I spent a lot of time coaching kids playing baseball, softball, and soccer, and it was the same thing. I always made it very clear to all the parents that practice sessions would start and finish on time. Anything else is just rude to the people who make the effort to be on time.

I understand that things come up and people will be late. Sure. But meetings should run for the people who met with the timings, not for the people who didn't.

 

Opinion – Modern isn't a synonym for Better

I've been in the IT industry a long time. I see trends come and go. (Mostly they go). At this point, I think I'm an OK judge of what's going to fly and what isn't. (Far from perfect but OK).

One thing that always puzzles me though is the way the word Modern is used as a put-down for things that aren't modern, as though it's a synonym for the word Better.

It's not.

SQL Language

My favorite over the years has been the SQL language. I cannot tell you how many times I've heard about its imminent death, and how we need a modern alternative.

And yet, time after time, even the products that claimed they didn't need a SQL layer seem to end up adding one.

I think this comes from a type of brashness that I see in many new developers where they think that the output of their last day's thinking is somehow vastly superior to all the thinking that's gone on in the industry over decades.

Guess what? It usually isn't.

It was pretty funny watching products like MongoDB appear and claiming to not need all that pesky ACID transaction stuff. And after a few disasters from the lack of it, then add it into the product as a feature.

Wow, who knew that transactions and consistency actually mattered in most applications?

We aren't all writing blog post or chat applications.

It was also pretty funny to see NoSQL get redefined as Not Only SQL.

Modern Data Warehouses

I like to keep across most data-related trends and products, even if I don't plan to use them. I want to be able to discuss them sensibly and understand the pros and cons of each.

I spend some time every single day, learning about data-related products, applications, and languages.

Over many years, I've seen what works and what doesn't when it comes to data warehouses, and have lost count of how many shiny new things were going to fundamentally change how we work. The put-down is always that you want a modern data warehouse, not one of those old ones that required hard work.

Guess what? Building a good data warehouse is hard work.

As an example, the next time you hear someone tell you that you should just apply your analytics tools directly over your transactional systems, at least stop and ask yourself how that would be a good idea.

In other areas of your life, I'm sure you have a built-in detector for snake oil salesman. It needs to be applied to technology too.

Image from Clark Stanley (public domain)

Hadoop

Yesterday I was amused reading this article by Derrick Harris on What happened to Hadoop?

When Hadoop first appeared, I spent quite a bit of time checking it out. Then when the SQL Server team became enamoured with it and added HDInsight (their flavor of it), I spent even more time checking it out. I even did the Big Data certification in the Microsoft Professional Program that I wrote about recently.

And the more and more I looked into it, and the more and more that I played around with it, I kept feeling Is that it?

I kept feeling like I was taking part in an Emperor's New Clothes skit.

There were certainly possible use cases for it, but almost every time that I saw it being forced into a solution, it was the wrong tool. So why did the technical people involved want to push it in? Either they just wanted to learn about the shiny new thing to enhance their CVs, or somehow they were bowled over by the hype.

I remember writing blog posts back in 2013 asking if most use of Big Data was mostly just Big Hype instead.

Tools aren't better just because they're considered modern. Don't get lost in the hype.

 

 

SDU_FileSplit – Free utility for splitting CSV and other text files in Windows

When I was doing some Snowflake training recently, one of the students in the class asked what utility they should use on Windows for splitting a large file into sections. They wanted to split files for better bulk loading performance, to be able to use all available threads.

On Linux systems, the split command works fine but the best that most people came up with on Windows was to use Powershell. That's a fine answer for some people, but not for everyone.

Because the answers were limited, and people struggled to find a CSV splitter, I decided to fix that, and create a simple utility that's targeted as exactly this use case.

SDU_SplitFile

SDU_SplitFile is a brand new command line utility that you can use to split text files (including delimited files).

Usage is as follows:

SDU_FileSplit.exe <InputFilePath> <MaximumLinesPerFile> <HeaderLinesToRepeat> <OutputFolder> <Overwrite> [<OutputFilenameBase>]

The required parameters are positional and are as follows:

<InputFilePath> is the full path to the input file including file extension
<MaximumLinesPerFile> is the maximum number of lines in the output file
<HeaderLinesToRepeat> is the number of header lines to repeat in each file (0 for none, 1 to 10 allowed)
<OutputFolder> is the output folder for the files (it needs to already exist)
<Overwrite> is Y or N to indicate if existing output files should be overwritten

There is one additional optional parameter:

<OutputFilenameBase> is the beginning of the output file name – default is the same as the input file name

Example Usage

Let's take a look at an example. I have a file called Cinemas.csv in the C:\Temp folder. It contains some details of just over 2000 cinemas:

I'll then execute the following command:

This says to split the file Cinemas.csv that's currently in the current folder with a maximum of 200 rows per file.

As you can see in the previous image, the CSV has a single header row. I've chosen to copy that into each output file. That way, we're not just splitting the data rows, we can have a header in each output file.

We've then provided the output folder, and also said Y for overwriting the output files if they already exist.

And in the blink of an eye, we have the required output files, and as a bonus, they're all already UTF-8 encoded:

Downloading SDU_FileSplit

It's easy to get the tool and start using it. You can download a zip file containing it here.

Just download and unzip it. As long as you have .NET Framework 2.0 or later (that's pretty much every Windows system), you should have all the required prerequisites.

I hope you find it useful.

Disclaimer

It's 100% free for you to download and use. I think it's pretty good but as with most free things, I can't guarantee that. You make your own decisions if it's suitable for you.

 

Opinion: Please let me have a clean desktop

I might be a bit anal about this but I really dislike clutter all over my computer's screen desktop. You'll notice above that I keep it empty. If there's something on my desktop, it's something that I'm working on really briefly. If I need to do a presentation, I might have a single folder called Desktop that I'll sweep anything on the actual desktop into.

There are two things that drive me crazy in terms of desktop clutter though:

The first is applications that insist on putting icons or shortcuts on your desktop during installation. Hello, it's not 1981 any more.

And worse, if you delete the shortcuts,those applications often put them back every time they are auto-upgraded.

Application writers: Please don't do this.

The other issue is around IT departments. I spend a lot of time working at client sites. And on many of the corporate desktops that I'm supplied to work with, the IT department have both plastered shortcuts all over the desktop, and worse, made it so that I'm not able to delete them.

IT departments: Please don't do this.

Opinion: Are red-haired DBAs unwilling to learn?

I've seen a lot of discussion lately about diversity and inclusion, and rightly so. It's incredibly important. But there are two points that I want to draw attention to. The first is that language matters. And the second is that we shouldn't be generalizing about people, based upon either their physical characteristics, or their race, religion, sexual preferences, etc.

Language that singles out people based on these things is regularly called out. But one that's often missed is age.

I was disappointed the other day to hear one of my friends who is normally a champion for diversity and inclusion, saying:

Quite a significant number of older DBAs I've met just think what they don't know is not their job to learn.

Regardless of how you react to that sentence, ask yourself how you would have reacted to any of these instead:

Quite a significant number of female DBAs I've met just think what they don't know is not their job to learn.

Quite a significant number of Christian DBAs I've met just think what they don't know is not their job to learn.

Quite a significant number of gay DBAs I've met just think what they don't know is not their job to learn.

Quite a significant number of red-haired DBAs I've met just think what they don't know is not their job to learn.

Anyone saying those things would rightly be shouted down. The person's characteristics, beliefs, preferences, etc. have NOTHING to do with their interest in learning. I have many older friends who are always at the cutting edge of modern technologies, and have a thirst to learn. Conversely, I have many younger friends who see a job as a means to getting some money and will put as little effort as possible into their own development.

The person's attitude is the issue here, not their age, no more than it's their gender, sexual preference, religion, or hair color.

When I started in IT, the entire industry was very young. Computer Science degrees in their current form either didn't exist or were extremely rare. The majority of people who entered the industry then were career starters, and they were young. But they aren't now. So while IT has always seemed a "young" industry, it's not now. In IT, we now have a big spread of ages.

Sadly though, ageism is still rampant in our society and seems to be one of the remaining forms of discrimination. And of course the final sentence was:

All old white men, of course.

Opinion: The best apps tolerate user mistakes

Over the years, I've been noticing which apps users love and which ones they don't like, or even hate. And I've decided that one of the biggest indicators of this is about how well they tolerate mistakes by users.

Users tend to learn to use apps in three ways:

  • They watch or read some instructional material (this is actually the least common)
  • Someone else shows them how to use the app (this is really common)
  • They just experiment with the app (this is actually the most common)

How well do your apps allow people to experiment?

  • Can a user click to enter part of an app without consequences?
  • If a user starts to create something, can they opt out at the last moment?
  • If they did create/change/delete something, can they undo it?
  • Can they truly undo the action, not just reverse it with a compensating action? (i.e. can they make it like they never did it in the first place?)

I see new users who are terrified of using applications, and time and again, it's they are actually terrified of "messing something up" or "doing something by mistake". In either case, they're worried that they can't fix what they did. (And in come companies, they're going to be blamed for doing it).

How do your applications rate on this basis?

Opinion: Banks, Governments, Councils – please stop aiding identity theft

I don't know if it's an Australian "thing" but whenever I'm setting up new financial or government accounts, these organizations insist on actually sending things to our street address.

Given how rampant identity theft has become in many places, this is just not sensible.

Awesome image by Mathyas Kurmann

Take a look at letterboxes that people have on local streets, and please try to convince me how sending anything there, is in any way safer than sending it to a post office box.

Post Office Boxes

I know that in some countries, post office boxes are a bit anonymous. That's not the case here in Australia. You have to take a lot of steps to prove who you are when setting one up.

What post office boxes are, is far, far safer than street letterboxes.

There have been countless examples of mail being stolen from letterboxes. Apartment complexes are even worse. Often there are a bunch of them in a place that isn't too visible, and they're often broken into. Even breaking into them isn't all that necessary as I often see mail just sticking out of letterboxes.

And the situation is far worse for people who travel regularly. Do you want your mail being collected in a post office box, or have ever-increasing amounts of it sticking out of a letterbox? (At least many of us have good neighbours who will help with this).

Please don't force us to send mail to a letterbox in our street, when we have a perfectly good post office box!

One final even-worse scenario

I have to close by adding one of my even-worse scenarios that I see banks doing. When we call a bank to tell them we've changed address, often they send details of the address change to the previous address!

Yes, that's the one where we don't live any more.