Wooo!!! Fixed in SQL Server 2016!

https://twitter.com/matt40k/710468378050339000

It’s nice to see Microsoft fixing old bugs – the I noticed today they updated the MSDN article about Actions in Multidimensional Models.

SSAS supports HTTPS actions

It now supports HTTPS!

Woot!!

https://connect.microsoft.com/SQLServer/feedback/details/692837/cannot-specify-https-in-an-analysis-services-report-action

 

 

 

Another year, another SQL Saturday

Unfortunately this year I’ve not been able to make any of the pre-cons, however the (free) community day looks epic – the hardest part is going to be selecting which session to attend, the final one looking the worst. Aghhh!!! I’m going to have to roll a dice or something!

CDC – Change Data Capture in Detail
Uwe Ricken

What’s new in the SQL Server 2016 for a BI Professional
Prathy Kamasani

Exploring the Azure SQL Database
Murilo Miranda

All things time-related
Kennie Pontoppidan

Data-driven personal decisions with PowerBI and Machine Learning
Ruben Pertusa Lopez

One session that really peak my inter-geek, which had me looking at getting a Microsoft Band – only to find out they are running the stocks low at the moment (they release the first and second one in October, so it looks like the 3 will be out soon).

The (Consumer) Internet of Things on your arm – MS Band & PowerBI connected

The Internet of Things (IOT) gets more and more attraction - not only on the business but also on the customer side. Connected fridges, cars and smart watches - always and everywhere connected! In this session Wolfgang will show you some possibilities of the Microsoft Band 2 SDK: how-to connect and read sensor data out of this device. But what should be done with that data? 
Power BI seems to be an ideal candidate for analyzing and presenting those kind of data. The different types of real-time analytics (Stream Analytics, Power BI API, ..) will be presented and their pros and cons will be envisioned. 
The challenge: Let's prepare a real-time dashboard of Band2 data in Power BI in 60 minutes!

Wolfgang Strasser

It’s also good to see the a small local company getting heavily involved :p

And not forgetting the Micro:Bit sessions on

Imagine If – Train the Trainer Micro:bit session

Want to help inspire young people and deliver a Micro:bit workshop?

If you are a teacher, parent or anyone interested in inspiring the next generation of technical experts; then come along to the Micro:bit session to get you started on how you could deliver a micro:bit workshop for either Primary or Secondary school children to code and create. 

In this session you will be taken through a workshop setup and approach (introduction, 3 coding tasks and a quiz) as well as getting hands on with the BBC micro:bit

This session requires external registration via http://microbit_opt1.eventbrite.co.uk

Amy Nicholson

Care Quality Commission (CQC)

Today one of our older SSIS packages failed which loads data into our warehouse, it turns out one of the fields had been expanded as the business had started to use a different standard for one of the codes. Despite not being told about this, it causing it fail and generate extra work on an already busy Monday it was actually a really good thing.

Firstly, once we identified the problem (and fixed it), our main business contact was aware of it, was able to explain why it was happening. The change in a nutshell was to change from using a internal-only generated code to using the national standard – Care Quality Commission (CQC) ID.

Part of the next step of the change will be to bring on-board the CQC data which include the Care Home ratings. This will create a unified view of placements and ensuring no-one is in Care Home rated as inadequate.

CQC provides the data either by CSV\Spreadsheet or an API.

Initial I thought importing the data dumps would be a good way forward, however after playing with the API, it looks like this might be a much better way forward. Below is a PowerShell script I knocked up to test the API out.

 

It’s really good to see that APIs are being generated, its just a shame these aren’t being embedded directly into the software. Still, its a long journey, but at least its started.

AD tools on Win10

A while ago I got a new laptop at work with Windows 10 Enterprise and for a while I just kept using my old Windows 7 Enterprise laptop for bits I hadn’t yet transferred over. The last thing on the list was AD tools. This should just be a question of installing Remote Server Administration Tools for Windows 10 (KB2693643). Unfortunately

Remote Server Administration Tools for Windows 10 is available only in English (United States) for this release.

So we need to install English (United States) language package. We can do this by

  • Start > Settings > Time & Language > Region & Language
  • Click on Add a language and select English (United States)
  • Under English (United States) select “Language pack available
  • Click on Options
  • Click the option to download and install the Language Pack
  • Restart Computer

Why does Bing sucks

The only thing you search for on Bing is Google.

Microsoft has for a long time been in the search engine business with numerous rebranding exercises – MSN Search (1998 – 2006), Windows Live Search (2006 – 2007) then Live Search (2007 – 2009) and finally Bing (2009 – present). Despite all the time and money Microsoft has poured into its search engine Google still has 3 times the number of of the search engine market share as Bing. This is even with Satya Nadella backing it, it being the default search engine on Windows 10 and things generally improving.

So why is this? I once asked my driving instructor why he had quite a lot of students passing first time (with him), what was he doing that other driving instructors were missing. His answer was simple

I give honest, blunt feedback. If you forgot to check your mirrors, I don’t sugar coat it, I tell you.

This is one of the key parts of machine learning. You need have a feedback loop. It might work perfectly today, but what about tomorrow? How do you improve it? How is that going to work when you’ve got thousands of users? What about millions? What about billions?

You might think about putting in some web form so users can provide feedback. This is a dumb idea. Twitter does this for ads and I can honestly say I just randomly pick reasons, I don’t care, I just want the ad to disappear because it has annoyed me.

So what is the answer? Simply put, I think its Google Analytics. Most websites have it enabled and why not? Its free, it provides highly valuable insight into your website and its simple to setup and use.

From Googles point-of-view, its a win-win, they know how popular your site really is, it doesn’t matter if you’ve found the site via Bing or any other search engine, if the site got Google Analytics enabled,  Google knows and it knows what keywords got the end-user to the site. It also knows how long a user spent on the site and if they hit the sites target – ie did they buy anything.

Google Analytics is Google feedback loop.

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.

Add “skip_tags: true” to your appveyor.yml.
-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.

XAxis-Error

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.

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

XAxis-fix

And voilà its fixed!

XAxis-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

error-audit-ssdt

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.