Posts

New Year, New Site

When you don't blog very often, it turns out that you don't tend you check your blog very often either. So it was with a bit of surprise that when I had a look at my site just before Christmas, I saw no content whatsoever. I logged into WordPress and had a look at the stats. Turned out that it had been that way since May, zero visits since then. It appears that an automatic update meant the theme was not compatible, and therefore served no content.

Return and Recap

So I've been meaning to get back to this blog for some time, and just…well I just haven't. Anyway, here we are, so it's time for a quick recap of what I've been up to technically the past couple of years. (Quick because I'd written a long version and MarsEdit lost the draft…said software is now uninstalled! What follows is the very brief re-write). Education, education, education Learning has been my thing.

Women’s Euro Qualifiers – stats in Power BI

With the all-important Women's Euro Qualifier between Scotland and Iceland coming up on Friday, I thought it would be a good time to grab some of the data from the UEFA website and have a play with it in Power BI. I've got the data for all teams imported, the report below is filtered to show the key statistics for the two teams of interest. When I get a bit more time to spare, I'll detail the PowerQuery steps I took to get and transform the data.

SQL Server Developer Edition – now free!

There was good news for data developers last week, as Microsoft announced that SQL Server Developer Edition will be available as a free download from now on. It's part of the Visual Studio Dev Essentials program, which I highly recommend signing up for (it's free!). You can grab SQL 2014 right now, and when 2016 is released later this year, it will be available too. The Dev Essentials program also gives you $25 worth of Azure credits for 12 months – well worth joining for this alone.

Analysing Football Data in Power BI

Two passions of mine that go hand in hand are sports and data. Fans nowadays can access a whole host of statistics for every sport imaginable. This data can be used for any number of reasons, including for gambling purposes and to help win fantasy leagues. Most premier sports teams now employ data analysts to look for something that will give them an edge and an advantage over their opponents. (On a related note, I'm currently reading Big Data Baseball by Travis Sawchik - a really interesting insight into how the Pittsburgh Pirates used previously untapped data to improve their fortunes).

SSIS – Change the Protection Level for all Packages in a Project

Here's a quick one I found this morning while fixing an execution issue with one of my SSIS packages. I needed to change the Protection Level of all the packages in a project from Encrypt With User Key to Don't Save Sensitive. The project contains over 30 packages, and I didn't really fancy opening each one, navigating to the properties and amending the Protection Level for each one - that's too time-consuming and frankly, boring.

Execute SSIS package as a Service Account

At my current client, I have a very specific set of parameters to work within regarding security. Chiefly, I need read permissions on the underlying database for a Microsoft Dynamics AX 2012 R2 installation. I have a series of SSIS packages that extract data from AX on a daily basis, executed from SQL Server Agent. The AX database, however, is hosted on a remote server and neither myself or the client team have permissions directly on this box.

Try R, a free course

I've been branching out a bit in my spare time with regards to other tools and languages for manipulating and analysing data. The R language has interested me for a while, and I came across this free tutorial which serves as a pretty good introduction to its basic usage and syntax. It does so in a rather nice interactive web interface. Highly recommended if you have a spare hour or two to go through the course.

Table variables, identity columns and reseeding

Ever tried to reseed an identity column in a table variable? No? Let's give it a go… Let's declare a simple table variable, @country, and insert a few rows. DECLARE @country TABLE ( CountryId INT IDENTITY(1,1), CountryName VARCHAR(50) NOT null ); INSERT INTO @country ( CountryName ) VALUES ( 'Scotland' ), ( 'England' ), ( 'Wales' ), ( 'Northern Ireland' ) ; SELECT CountryId , CountryName FROM @country ; Truncating a normal table will remove all the rows and reseed the identity value.

T-SQL Tuesday: A Day In The Life

This month's T-SQL Tuesday is hosted by Erin Stellato, and the theme is A Day In The Life. The premise is to track what you do over the course of a day as a SQL professional. My days can differ greatly, depending on the role I'm currently working in, and for what client. My current role is mainly database development and data migration work for an upgrade to an existing application.