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
Below is a list of useful BI links http://www.kimballgroup.com/ https://gqbi.wordpress.com/ https://bipassion.wordpress.com/ https://blog.crossjoin.co.uk/
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 lessons with Gary we 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)....
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 - https://t.co/2NgZG7siUD #sqlhelp @sqlserver @SQLServerBI pic.twitter.com/SP8OC5eLvD — Matt Smith (@matt40k) June 6, 2016 Now, in SQL Server terms there are two types of licensing...
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 documentation to feed the foreach loop....
I was at event that Simon Sabin arranged, SQL SERVER TOOLS and SSDT shape the future and 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....
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 🙂
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....
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 Helper has 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:
So eariler this month I switched from my Azure hosted TeamCity server to Appveyor, which freed up some much need Azure credit. 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!...
So today I managed to move SIMS Bulk Import over 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! Appveyor has 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 Mulholland for this, I saw his project PiNet and thought we can make this better....
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-Beasts for 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....
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....
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
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 Online and 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....
Today, my pull request for the SSAS deployment script was merged into the Octopus Deploy Community Library. So what is Octopus Deploy and why should I care? Octopus is a friendly deployment automation tool for .NET developers. But don’t be fooled. Octopus Deploy isn’t just limited to .NET developers deploying hip new cloud based applications, it can do much, much more. I currently use Octopus to deploy Jasper reports to our 6 HR environments, I also use it to deploy our HR interfaces which are a series of SSIS packages and SQL objects which uses Microsoft SQL Server 2008....
So I’ve been playing wit mapping data, one of the nice things about GitHub is that it supports GeoJSON and automatically renders it With the mapping data already imported into Geography in SQL Server it was easy enough to convert to GeoJSON (Stackoverflow to the rescue again!)
Just an update as I noticed I forgot to say how to get the names and descriptions of the collations your SQL Server supports
So I was handed over some new servers, before I got started I wanted to check everything was as it was suppose to be, so I wrote a few PowerShell scripts to check each server was setup correctly. I wanted my DEV to 100% match my UAT and so on. First, I wanted to check who has local administrator access on the servers. Next, I wanted to check the drives – I wanted to make sure each drive letter was the same across environments and all had a proper label....
I don’t know a lot about Collation – I know how to set it in SQL Server, I know it’s important, it know chaos can ensure if your not careful for example if you set it to CS rather then CI*, so my general tack is to leave it as default and hope it leaves me alone. Today however, this happened Turns out in the VS SSDT package, I have forgotten to set one of the databases collation in project settings to Latin1_General_CI_AS to match the default on SQL Server (rather then the VS SSDT default)...
My current thoughts on SQL2016/PowerBI as deploying SQL2014. Ok, so we’re looking at moving to pure SQL 2014 for our corporate BI solution. I say pure as we normally use SQL server as the backend then other tools on top, so we’re looking at enabling a lot of extra features we’ve technically had for years but just not enabled\setup\whatever. SQL2016 is on the way and we’re most likely going to upgrade pretty quick, lots of new features that look very useful....
On Friday, I had a problem raised, they want to change one of the automatic rename jobs which renames and moves export files to a shared drive. Lucky, I had created a SSIS package, which was a SQL Job that runs on a scheduled every 15 mins, which uses a script task to perform the logic. Because we also used OctopusDeploy, this really was going to be a 5 min change....
For a while now I’ve been going to the Cambridgeshire SQL Server User Group, which I have found really useful especially as we are at moving towards a more self-service model across IT which requires IT to be a bit more cleverer. Beyond the informative speaker talks you get a really good networking opportunity. The group is run by Mark Broadbent (@retracement) and has recently merged with SharePoint Saturday Cambridge. Although Cambridge is about the same time on the train as London....
One really annoying bug in SSIS when using Visual Studio 2013 / SQL Server 2014 is the object jumping, so when you drag an object like a sequence container down the object shoots to the bottom. Thankfully the fix is simple. Close Visual Studio and apply SQL Server 2014 SP1 to your machine.
One of the problems we have is documentation and how to keep it up-to-date. We tend to deliver a new batch of reports as part of a CR (Change Request) then make changes as part of BAU (Business as usual) unless its a particularly complex or time consuming change. The problem is often BAU changes happen rapidly often within a short time frame and time isn’t always allocated to updating documentation....