School MIS systems in the England

Updated 10/10/2016 – Added Map using the new Shapes option in PowerBI. Every School in England regularly publish data to the Department of Education and Graham Reed has published a spreadsheet of the software each school used to submit their returns. Below is a PowerBI report I’ve created based on his data. You can also access by clicking here

July 30, 2016 · 1 min · matt40k

Incorrect x-axis dates

So yesterday I was playing with PowerBIand I hit a problem. As you can see, the x-axis is wrong. For some reason it wasn’t creating the correct range. After a little while I thought I’d try out Microsoft’s feedback option – the smiley faces – in the top menu you have a smiley face. Click it, then you can send feedback. Sure enough after a little while, Justin Schneider from the Power BI team replied asking a few more questions – basically asking me to check the data was correct, which was in this case and even offered a solution....

July 27, 2016 · 1 min · matt40k

SQL Auditing sucks

Back in SQL Server 2008 Microsoft introduced auditing, specifically the Database Audit Specification. It’s pretty good – despite the title I do actually think its nice feature, it pretty much works and doesn’t have much of a performance impact, my problem is not much love has gone into it since it was released in SQL2008. It claims to meet various regulations such as the EU data Protection Directive, HIPAA, PCI DSS and to be fair, I’m sure it does....

July 23, 2016 · 2 min · matt40k

Feature request – Add Azure Data Catalog support to SSDT

One of the annoy parts of building a warehouse is building the staging database, its an important first step. The staging database is replica of the source systems. BIMLcan provide away to accelerate this process, however its not perfect. BIML is designed to create SSIS packages – and it does this very, very, well. SQL database objects, not so well. SSDT does this well. The first step in the designing a warehouse is discovery, Azure Data Catalogis an excellent tool for doing the discovery, it allows you to connect to a wide array of data source types and gather the meta data that can be used to build the staging database....

July 17, 2016 · 1 min · matt40k

BI resources

Below is a list of useful BI links 

June 24, 2016 · 1 min · matt40k

Getting a list of AD Groups and their members using PowerQuery

The Power Query Formula Language (informally known as “M”) is a powerful mashup query language optimized for building queries that mashup data. It is a functional, case sensitive language similar to F#, which can be used with Power Query in Excel and Power BI Desktop. A few days ago I got asked to produce a list of users (and their email address) in a number of AD Groups. I already had a SSIS package that had a script task to pull this data from Active Directory and push it into a SQL database and we have a PowerShell script to get the same data in our code repo....

June 14, 2016 · 3 min · matt40k

Is today Day Zero or Day One?

This might sound pretty pointless and silly but when talking about a 20 working day SLA a day can mean alot between a massive fine or compliance. During one of my driving lessonswith Garywe were talking about his recent streak of passes. The conversation then lead on to pass rates and us wondering how this broken down – for example, by gender, ethnicity, age, driving instructor type (intense driving school \ chain \ individual driving instructor \ family etc)....

June 10, 2016 · 2 min · matt40k

Error building SSDT package

Today my colleague had a problem opening our BI solution, the solution had multiple projects, including 3 SSDT projects. Although the project builds correctly on both my machine, the build machine and another colleague machine it refused to build stating that the reference to the object in another project was invalid. After thinking for a few moments, I remembered I had seem this before. The problem was a bug in SSDT....

June 10, 2016 · 1 min · matt40k

Microsoft shows Data Pros some love

Microsoft has released SQL Server 2016 Developer, for free. That’s right, zero, zip, nothing. Completely free. The developer edition is fully featured, it contains the same goodness as the $$$ Enterprise edition. The catch? Its not for production.

June 9, 2016 · 1 min · matt40k

Logging ConnectionStrings in SSIS

Another day, another reference to an old Jamie Thomson blog post– today it was getting the ConnectionString to output to the information. Admittedly I wasn’t looking for how to fire the connection string into an information event, just the ConnectionString, still another great idea and its useful for debugging.  

June 9, 2016 · 1 min · matt40k

Enterprise vs Enterprise Core

This month saw the release of SQL Server 2016, which from a BI\Report point of view is huge. Once we had access to it yeah, already been on msdn. Can't believe we're being made to wait!!! — Dave Kerby (@davekerby) June 1, 2016 I spotted something odd. So whats the diff? Enterprise is only core licensed - #sqlhelp @sqlserver @SQLServerBI — Matt Smith (@matt40k) June 6, 2016 Now, in SQL Server terms there are two types of licensing...

June 7, 2016 · 2 min · matt40k

MDSCHEMA_CUBES DMV not returning all cubes

I had previously created a SSIS package with a simple Process Full on the SSAS MD database, however, as the project has progressed this hasn’t been ideal. Its basically all or nothing. In order reduce the damage a failure can cause I’ve setup a Process Full for each dimension and cube so each is processed independently of each other. I’ve used the data from the Analysis Services Dynamic Management Views, or DMV for short, which I’ve used for the documentationto feed the foreach loop....

June 7, 2016 · 2 min · matt40k

New laptop, error running SSIS package

So today I went to run a SSIS package on my new laptop and bam, error message. Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails. —> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 “Error loading from XML....

June 1, 2016 · 1 min · matt40k

SQL Server vs Containers

I was at event that Simon Sabinarranged, SQL SERVER TOOLS and SSDT shape the futureand one of the questions that was asked, sorry, I forgot who asked (joys of leaving it too long before blogging about it – I want to say Gavin) was, How does (Windows) Containers fit into SQL Server? This is basically Microsoft integrating the Docker technology into the Windows OS, so you in effect, ship a more complete solution....

June 1, 2016 · 3 min · matt40k

Power BI – custom visuals

Power BI is Microsoft new cloud based Business Intelligence platform, one of the cool things about it is it built atop internet standards and leveraging open source libraries like D3.jsfor its visuals. Microsoft has continued on the path of open source-ness by open sourcingsome of its codes allowing Joe Public to submit custom visuals to Power-BI, all via GitHub. Yesterday saw the introduction of 4 new visuals, one in particular looked very interesting – Hierarchy Slicerby Jan Pieter Posthuma....

June 1, 2016 · 1 min · matt40k

SQL Compare failed

Another day, another problem. Todays problem was incorrect column length which was causing a SSIS package to fail. Historically I use a freeware tool to compare the different environments schema, but lately I just don’t bother, I have continuous deployment so its easier just to hit the deploy button then it is to try and figure it out. Well today I decided I wanted to it old school – although I’ve been using the SSDT SQL compare in my deployments, I’ve not actually used it in terms of viewing the differences....

May 12, 2016 · 2 min · matt40k

The connection is broken and recovery is not possible

Msg 0, Level 11, State 0, Line 0 The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. In short, you’ve lost your network connection, either your WiFi has dropped out or your network cable has 🙂

April 20, 2016 · 1 min · matt40k

Auditing – Who’s viewed my record?

As some of you are aware I currently working for a local authority and anyone who has been following local government news in England will know the bonds between local authority and the health authority, aka the NHS, are growing. As are other parts of the government for that matter, both central, other local authorities as well as charities, but for now, I am focusing on the health part. Specifically the part around what that means....

April 5, 2016 · 4 min · matt40k

Auditing – SSAS

There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing. Today I am looking at the SSAS auditing – you can find out more about it on TechNet. Olaf Helperhas published some TSQL code for querying the audit data -on the Script Center. But first, we need a table to store the data, here is a TSQL script for creating the table:...

March 26, 2016 · 1 min · matt40k

Building a test system

So eariler this month I switched from my Azurehosted TeamCityserver to Appveyor, which freed up some much need Azurecredit. This month, it looks like its going to be going on a Visual Studio Ultimate vm for some SharePoint development. Hopefully this won’t take too long to do as I really want to get on with machine learning after a demo from Ric Howe. I’m also looking at building a few test machines locally – old school I know!...

February 20, 2016 · 1 min · matt40k

Showing SIMS Bulk Import some love

So today I managed to move SIMS Bulk Importover to Appveyor. So what does this mean? Well it means I don’t have to worry about going over my Azure credit limit each month for starters! Appveyorhas excellent support for GitHub, so each commit is automatically build, tested, and (when I enable it) a new release created. The next release will include Pupil username creation – you can blame\thank Andrew Mulhollandfor this, I saw his project PiNet and thought we can make this better....

February 14, 2016 · 1 min · matt40k

Web hosting renewal

One of the bad habbits I have picked up from working in Local Government is reviewing your options, regularly. As I approach my renewal for this site’s web hosting, I decided to revaluate the market. (Short version, I’m with Mythic-Beastsfor another year) Currently, I have the site hosted with Mythic-Beasts. They are pretty cool folks, unfortantely one of there DC had a power cut recently – ok, only a few hours and I pay peanuts so I don’t expect much in terms of reduancy or fancy SLAs, but it did make me look at my options for a bit longer then normal....

February 13, 2016 · 2 min · matt40k

Clever SQL Jobs?

Just thinking out load (because its good to get feedback) Just to expand on my tweet. I’ve lot of SSIS packages for load and building my BI warehouse (SSAS cubes), now currently I have a SQL job that has multiple steps For example Load data from Source system into Staging Build ODS intermediate tables Build Warehouse tables And this works for DataMart where they are a denormalized copy of a single source system, but when the source system, or the intermediate tables, are used multiple times, you don’t want to run them multiple times....

February 9, 2016 · 2 min · matt40k

DacPac vs SqlCmd variables

I recently discovered the ability to reference other database project (I know, I know), anyway, this cause my lovely automate build\deployment process to fail. Investigating the error led me to, yes, you guessed it a Jamie Thomson’s blog(from 2012). Thankfully, I managed to figure out a clean solution, the DacPac solution on Octopus Deploy library has an option to pass Profile Name, which is the publish profile XML file. He is the cut-down version I’ve used, just the missing variables

February 2, 2016 · 1 min · matt40k

Automatic build and deployment process for Microsoft BI

I’ve previously blogged about how I was going to detail how I build and deploy my Microsoft BI projects and to be honest, I’ve been putting it off. The main reason is my work has been looking at Visual Studio Team Services, formally Visual Studio Onlineand I was looking at using the Build functionality that is included. This should make the whole setup process a lot easier for anyone else trying to replicate my setup....

February 1, 2016 · 4 min · matt40k