Voice of the DBA

Author: way0utwest

Data Science Education

One of the good skills to have for a data professional is how to analyze data. Most of us could learn more about data science and data analysis for some aspect of our jobs. We are data professionals, so we should understand how to analyze data. I’d expect that a competent data professional would be able to put together a report on some set of data that means something to an end user. If we administer systems, then analyzing usage (index, space, etc) is a skill we need. If we write code, sooner or later we’re going to write some report for a client. Either way, we need to perform some sort of analysis.

How can you learn more? There are lots of resources available. I thought it was interesting that Microsoft has teamed up with edX for their own data science degree. While there are mixed feelings on this, I think edX has a good platform and strong partnerships for teaching. There are other places, such as Coursera, that are doing the same thing, offering a variety of courses online.

In fact, it appears that quite a few educational institutions and businesses are starting to increase their data science related offerings. There’s a good summary of some of the options in a piece from Dataversity. In response to all the demand, or at least perceived demand, there is everything from a boot camp getting you up to speed on some quick analytic techniques and tools to full degree programs. Some programs have different levels, depending on the amount of statistical knowledge you have.

Learning something about statistics is probably the best way for most people that would like to get started. I’ve seen quite a few people start learning by reviewing some statistics techniques. From Buck to Mala, putting your own learning down in a blog will force you to ensure that you actually understand the principles. As for getting started, there are lots of great books you can buy, or read information online. One note, be careful and double check the information written on random blogs. Or check out places like the Khan Academy.

In some sense this reminds me to the rush to get a CNE in the early 90s, or the many boot camps that appeared to help people a MCSE certification in the mid to late 90s. Lots of people passed the tests, but weren’t very qualified, which has contributed to the general distrust of certifications today. I hope that doesn’t happen too much today, though I’m sure it will to some extent. The chance to make more money, or just find a job, will drive lots of people to look for quick wins.

While I’m sure some of you will get better jobs, perhaps making a lot of money with minimal data science experience, I hope many of you continue to learn and improve your skills in the data analysis area over time, whether you are paid to be a data scientist or not.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Mevio .

September 15, 2016
Quick #SQLPrompt Tips – Expanding Wildcards

I tend to try and get code on the screen quickly and then start to remove things. I’m a visual person, and it’s helpful for me to see some tables, joins, filters, and columns as I’m structuring a query.

One of the ways I work quickly is with SQL Prompt is that I’ll write a query, using the SELECT * to hold the place where columns will appear. Since I’m not always sure what columns exist in a table, using the asterisk allows me to complete a valid query.

However, I don’t want to leave the asterisk there. Let’s put the cursor behind it. As you can see here, a tip pops up.

When we hit Tab (or your completion hotkey), the entire column list expands. All columns, from all tables, qualified if necessary, according to my SQL Prompt settings.

Now I have a well written query, or if I don’t need all columns, I can easily remove those that I no longer want to retrieve.

This is a quick tip, one that doesn’t do a lot, but has the potential to make developers really think about all the data being returned in large queries with a SELECT *.

Give this a try the next time you find yourself writing a SELECT * query and then remove the columns that you really don’t need. You might also check out a similar piece I wrote for the Redgate blog.

If you aren’t a SQL Prompt user, then think about downloading an evaluation and becoming a more efficient T-SQL developer.

You can see a complete list of SQL Prompt tips at Redgate.

September 15, 2016
Down Tools Week 2016

Last week I went to the Redgate Software office in Cambridge, UK. I travel there a few times a year to meet with product groups and touch base with the other people in marketing. However, this trip was planned around Down Tools week, which is an event that Redgate has once or twice a year. This is similar to what other companies have done, like Atlassian ShipIt day, and I had the chance to participate a bit in one of the projects. It was quite fun, and a memorable experience.

The idea is that a project is pitched as an idea for a single week. These are usually ideas that aren’t worth funding as a large project, or would help the world somehow. A team comes together for a long week and has to showcase their work by Friday afternoon. There have been projects just to try something fun at Redgate and investigate something. We had a number of projects, including a charitable image recognition project for Waterscope. That one was really interesting, as some of the software and documentation improvements that were made will be pitched to their investors and taken our for field trials.

I got involved with the Rescue DLM Dashboard project. I like DLM Dashboard as a tool, but it needs some work and should provide more value. A team got together with the idea of seeing where we could add more value and make this a commercially viable product. We also tried to fix a few bugs and get some UX love for the tool. By the end of the week, we had integrated DLM Dashboard with a couple other projects, and had other items to work on. We did win a couple of the contests (best t-shirt, best presentation), but we still have a commercial brief to write and get approved before any more work will be done.

The project structure itself was interesting, with a daily standup at 11am, and teams of programmers working in pairs to add features. I didn’t do any coding, mostly because my C# skills are far below others, and I had other commitments during the week. I was in and out of the dedicated conference room, talking with the project managers and watching developers work through the coding. We had a few interns that worked with experienced developers, and it seemed that people worked well together, sharing ideas and solutions for issues.

I was impressed that the setup of everyone’s workstations, all moved to a conference room, connected, and with cloned git repos was done fairly quickly, with working builds for most people by Monday at lunch. It’s not as simple as one might expect to grab a new project and get a working build, especially on a complex piece of software, and it was fascinating to watch people debugging issues across web pages and local services. I was also pleased to see how open other teams were to lending us a person for a day or two in order to facilitate integrations or extend APIs.

Down Tools week is expensive, but it certainly could be done in different ways. An organization wouldn’t need to cater food every night. Pizza or other alternatives might be fun for some groups. However, I think this can be a great way to create some excitement for your developers, as well as investigate some research that might not otherwise be feasible to undertake. I don’t know that you need to make as big a production as Redgate does, but I’d encourage you to think about taking a week off from normal projects once a year and letting developers work on things that might excite them at your organization.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.7MB) podcast or subscribe to the feed at iTunes and Mevio .

September 14, 2016
T-SQL Tuesday #82–Azure SQL Database

It’s T-SQL Tuesday time and this month we have Jeffrey Verheul giving us the topic of the cloud, or Azure SQL Database. It’s a good topic, given how many people are talking about working in the cloud. There are also the tremendous resources that Microsoft is investing in Azure, so cloud database posts are a good thought for this month.

Whether you are writing today (Sept 13) or at some point in the future, it’s fun and informative to participate in the T-SQL Tuesday blog party. Learn something and write about it.

Azure SQL Database

I have done a little work with Azure SQL Database, but not a log. I have a few ideas, but finding time and focusing has been a challenge. When I saw this topic I had all sorts of ideas, but since I was traveling and busy, I ended up arriving at the deadline without any work done.

However, there was one thing I’ve been wanting to try in Azure, so I decided to make this the post. I wanted to create an Azure SQL Database using Powershell. This is a topic that I think lends itself to better orchestration and development, so here goes.

The first step was a quick Google search, which led me to this post on how to Manage Azure SQL Database with Powershell. That’s what I really wanted to do, so I read a bit. The first step was to download the latest Azure Powershell. I did that and ran an install. This required a reboot, something I rarely like, but I swallowed my annoyance and restarted the desktop.

Once that was done, I ran a couple cmdlets to get connected and check what resources I had. First, I had a login prompt.

Then I got results. As you can see, I had a few, and the list actually scrolls down quite far.

I then tested another cmdlet, Get-AzureRMResourceProvider, to get a list of regions.

That worked, so let’s create a database. I used the New-AzureRMSqlDatabase cmdlet, which worked well. I provided the resource group name and servername I already had with a new database.

That completed, and things looked good. I connected to the Portal, and sure enough, I had a new database.

Of course, an S1 will eat up all my credits, so I changed that right away.

That was pretty easy. I can see how I might now string together the creation of a database with some other work, perhaps adding data, certainly using the Redgate DLM Automation or ReadyRoll scripts to create objects, and then I can run things and tear them down.

Speaking of which.

September 13, 2016