## Build problem and duplicate releases

Yesterday I went to investigate a problem someone had reported only to discover a butt load of releases

As you can see, I have more releases then commits. A build was triggered by a commit, but this doesn’t explain why.

I tried switching it up so it only builds when you put [build] in the commit message – which resulted in having to commit the yaml file as the feature isn’t available via the web interface, it does make sense to have this under version control. However it continued to loop.

Looking at the build history revealed it was looping, per commit

An email off to support resulted in speedy response.

-Feodor Fitsner, AppVeyor

Bingo. It was looping because I create a GitHub release for each build and tag the release. Which was then causing a build, which created a release that then got tagged, which created a release…

I’m grateful I’m not paying per build! Hopefully this will serve as a warning for others.

I’ve started to remove the releases but I’ve hit the API limit last night. Hopefully I’ll clean up my PowerShell script to deal with it, failing that I may have to rely on the kindnesses of GitHub human.

“I have always depended on the kindness of strangers.”

– Blanche DuBois

### Update

I’ve managed to remove the duff tags as well and SIMS Bulk Import is down to two releases on GitHub. I’ve been slightly heavy handed when it came to deleting. Thankfully all the duff releases where pre-release so the problem wasn’t as bad is it could have been.

Below is the PowerShell script I quickly knocked together, the tags I did over lunch at work, thus the proxy bit – note that isn’t our actual proxy address 😉

## Incorrect x-axis dates

So yesterday I was playing with PowerBI and 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.

The problem was the auto options for the X-Axis range was wrong and he recommended setting it manually.

To this is simple, click on the visual with the faulty X-Axis, then on the left hand menu, click the paint brush, then expand the X-Axis and manually enter the start and end

And voilà its fixed!

## 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. Assuming to implement it correctly.

For example, I have it enabled on ACS.DimPersonSensitive – as you can guess, it holds sensitive information about a individual. If I want to break it, all I need to do is use parameters, ie
declare @per_id int = 123456 select * from [ACS].[DimPersonSensitive] where per_id = @per_id

Now if I look at the logs, I literary see @per_id, so I have no idea what id that was pass. There is no column in the audit logs that tell me what that parameter was.

The other issue I was hit with today was SSDT, it really doesn’t support it. Here is the error I got today

It’s basically blocking me from changing the table because it has auditing enabled on it. No nice way around it. It’s another Pre-Deployment script. This isn’t terrible. The terrible bit is it doesn’t reapply the auditing once you’ve removed it and updated the table, despite the auditing being defined in SSDT project.

## 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. BIML can 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 Catalog is 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.

My feature request is to add support for Azure Data Catalog to SSDT. In a nutshell add a item to this menu list

## 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. After work I set about repeating it but using PowerQuery.

Apart from one quirk the process was pretty straightforward. PowerQuery has built in support for Active Directory. Just click the Data tab, then select New Query > From Other Sources > From Active Directory

This will then pop up with a windows asking for you to select your domain, assuming your machine is joined to the domain and your logged on as a domain user, this should be prefilled in with the correct domain.

The next window just asks you confirm your credentials, you’ll most likely want to use your current windows user.

After you’ve connected to your Active Directory, you’ll be able to navigator, your be able to select your domain from your forest and then select the object you want to query. For this example, select your domain then select group, then click Edit.

This will then open up the Query Editor

This will then list out the groups, or at least a sample. Click on the left\right arrow on securityPrincipal column header, this then bring up a filter window, select sAMAccountName and click on OK. This will give you the friendly name your more then likely to know the groups by.

Now its a question of filtering the list – if you right-click on the sAMAccountName and select Text Filters > Begin With. You can select one of the other options, if you make a mistake or you want to refine it, on the right-hand side, you have Query Settings > Applied Steps, if you click the cog next to Filtered Rows your get

this hopefully doesn’t need any explanation. You can of course filter on other columns if required, such as OU group.

Adding the users involves expanding the member

And the final M script looks like field like we did with securityPrincipal until we get to the individual user object.

The last point is where I hit a few snags. Firstly, I couldn’t expand into the user object, luckily, clicking on Advanced Editor on the Home tab revealed the M code. I’ve included my code below which I hope will help.

I’ve personally found this a useful dive into PowerQuery, hopefully you have too 🙂

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

Still curious, I decided to send off an a FOI request, before doing so, I went looking to see if someone else has which lead me to find some rather shocking data (see the bottom of the post). For those who don’t know, FOI stands for Freedom of Information. This is where Joe Public can request (reasonable) information from the public sector – after all, it is your (tax) money that they are spending. Logging FOI is dead straightforward thanks to WhatDoTheyKnow. Anyway, the Freedom of Information Act requires them (public sector folks) to reply within 20 working days.

First question. If I log a call on Sunday, what day is day one? So firstly, Sunday doesn’t count. It’s not a weekday. So Monday’s day 1? That’s what I would think, and it’s even what WhatDoTheyKnow says, but the DVLA say its the Tuesday. After a few emails between them both, I went off an ask ICO, Information Commissioner’s Office who’s responsible for these things. Here is their response

counting the first working day after a valid request is received as the first day

So, if you log a FOI over the weekend, they receive it on the Monday, that’s Day Zero. Day One is actually the second day.

They also say

Working day means any day other than a Saturday, Sunday, or public holidays and bank holidays; this may or may not be the same as the days they are open for business or staff are in work.

So its important that public sector don’t close for extra days as they risk eating into that time they have to respond to FOI request and failure to reply can (ultimately) result in fines.

Going off topic – there are some determined people in Suffolk who really want to drive.

## 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. The solution was to click on Tools > Extensions and Updates, then click Updates from the left-menu on the window that appears.