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.

If anyone is interested in seeing the data for other teams in the qualifying rounds, pop me a message via Twitter or use the contact form.

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.

sqlserverdev2016The Dev Essentials program also gives you $25 worth of Azure credits for 12 months – well worth joining for this alone.

I’m really happy to see Microsoft make this move and ensure that SQL Server is more readily available to developers. Not that it was expensive before, but removing any barriers to adoption can only help.

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).

For the past year or so, I’ve been collecting, cleaning, transforming and analysing football results data, mainly for leagues in Scotland and England. I’ve been using a few of my favoured tools to do this – namely Power BI, PowerPivot and R. The ability to embed reports in a web page was added in a recent Power BI update, so I can now post an example of the kind of thing I’ve been building. Power Query is used to consume raw data from CSV files and scrape data from the web, then cleanse and shape it into a useable format. Relationships are then created in Power BI and I’ve added a set of useful measures and calculations using DAX. See below for my interactive Scottish Premiership report, showing a small subset of data going back to the 2000-2001 season.

This report currently allows a user to view league tables; to analyse shots, shots on target and goals; to view historical team matchups and to analyse monthly team form.

Please give it a go, have a play – any comments and suggestions are welcome. Let me know if you spot any errors! I’ll be adding more analysis to this over time, as well as adding in data from other leagues across Europe and lower league data.

Sources of the data for this include:

Note: I’ve cross-posted this at my business blog over at FocusBI.co.uk.

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. I initially figured I’d write a PowerShell or Python script to amend the relevant XML in each of the .dtsx files. But a quick Google came up with this MS Technet page, which details how to make the change via the command line, for both individual packages and for all packages within a project.

A quick amendment to the command to use the value for DontSaveSensitive (0) and to remove the password parameter was all that was required, so running this…

for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0

…and pressing Y to confirm each change as prompted, resulted in the change being made to all packages. Result! Well almost. After going back to Visual Studio and changing the Protection Level of the Project (it must match that of the Packages), I tried to build the solution. It returned an error stating that for all the Packages, the Protection Level was incorrect and had to match the Project. But I’d just changed all of that! Turns out I had to open all the packages in Visual Studio (for some reason), and then it would build without making any further changes. Odd.

While this is useful to know for future reference, I’m much more likely to be developing SSIS projects using BIML, which would make changes like this far easier, as it would be a simple change in the BIML template, re-generate the packages and job done. It’s the future…

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. Our only option for connecting to the SQL Server is via a service account which has db_datareader permissions on the AX database. So how to execute the Agent jobs running as the service account? By using a Credential and a SQL Agent Proxy.

The first step is to set up a security Credential on the SQL Server that executes the jobs. These can be found under Security>Credentials in SSMS (see Figure 1). The Credential identity should be set to be the name (including the domain)of the Service Account, and must include the password. To create the Credential in T-SQL:

CREATE CREDENTIAL < CredentialName >
	WITH IDENTITY = '<domain>\<service account>'
		,SECRET = '<password>';

Figure 1

Next, a Proxy for SSIS must be created in the SQL Agent. These are found in SQL Server Agent>Proxies>SSIS Package Execution (Figure 2). To create a proxy in T-SQL (note that subsystem_id 11 specifies that it is for SSIS Package Execution):

USE msdb;

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'<ProxyName>'
	,@credential_name = N'<CredentialName>'
	,@enabled = 1;

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'<ProxyName>'
	,@subsystem_id = 11;



Figure 2

Agent Job
Finally, the Agent job needs to be set up to run as the Proxy. In the Run As drop-down in the relevant Job Step, select the newly created Proxy (see Figure 3).


Figure 3


Create a SQL Agent Proxy