AWS S3 errors

One of the annoying things about AWS S3 is the error messages. Below is an error, see if you can figure out what it’s saying.

I’ll give you a hint. We’ve connected and authenticated.

Here is another hint, we can read from that bucket.

Give up? It’s permissions. Incorrect AWS keys returns a helpful message, so does invalid bucket, as does no access to read bucket

Nice idea, but pretty holely

So the NHS Hack Day retweet this

Which made me think why is Ubuntu creating a NHS specific distro (OS) and why are they begging for some VPS instances?

Turns out, it has nothing to do with either Ubuntu or the NHS. Its a group of people building on-top of Ubuntu, aimed at the NHS. The company behind it is OPENHEALTHHUB CIC. A Community Interest Company (CIC) – Private company limited by guarantee without share capital. It’s setup correctly, but it’s not exactly cash positive.

On their homepage, they have a video demo’ing the system. To me, I found this pretty pointless. It’s a basic desktop.

Don’t get me wrong, a Ubuntu desktop would be a great setup, but why not Google Chromebook with Google Apps? Why not Windows 10 with Office 365 online? In fact NHSMail2 IS Office 365. So eh? So what are they actually asking money for to do? I just don’t get the value added.

I just don’t get it. Why not create some packages for Ubuntu which has a large support based, is certified by the UK government as meeting the security requirements. Which leads me to

(In a side quest this lead me to).

In terms of the idea, open source is great idea. But it isn’t free. It isn’t free purely to the fact it’s production. If problems occur, we can’t wait for a developer to get home from work. You need to have dedicated developers and developers like most people need to paid with money so they can pay the bills. So if we accept the fact we must spend money we need to agree how to fund development. Is CAL licenses so bad now? Well I guess it depends and I think this my point. Going pure Microsoft is stupid. Going pure Ubuntu is stupid. I think the correct solution is a mix.

Open source is a great way, if you do it correctly, to collaborate, for others to build and improve your system. And remember, Microsoft is the top open source contributor on GitHub.

 

 

 

Glossary

Idempotent – something that is repeatable, that remains unchanged in value when repeatedly executed either independently or on by itself.

SSRS – SQL Server Reporting Services part of Microsoft SQL Server. Used to deliver static reports that can be viewed via a web browser and exported to a number of formats including Excel, PDF and CSV.

SSAS – SQL Server Analysis Services part of Microsoft SQL Server.

SSIS – SQL Server Integration Services part of Microsoft SQL Server. Originally designed as a ETL tool to build a data warehouse but has expanded and is able to automate a number of SQL Server related tasks.

PowerBI – Power BI is a business analytics service provided by Microsoft. It provides interactive visualizations with self-service business intelligence capabilities, where end users can create reports and dashboards by themselves, without having to depend on information technology staff or database administrators.

Kimball -A methodology for data warehousing also known as dimensional modeling authored by Ralph Kimball.

C# – C sharp is a general-purpose, object-oriented programming language developed by Microsoft.

PowerShell -is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language built on the .NET Framework and .NET Core.

MIS – Management Information System

MI – Management Information

BI – Business Intelligence

SQL – Structured Query Language

Cyber Security Insurance

I’ve been thinking lately about how the web industry needs to change in order to improve. Cyber attacks appears to be on the rise and alot of the time they appear to be caused by someone doing something silly. Perhaps this is because everyone is a web developer, it certainly isn’t down to lack of available resources on the matter.

Thinking positively about cyber security, the card companies banded together to form PCI-DSS – Payment Card Industry Data Security Standard, with the goal to help businesses process card payments securely and reduce card fraud. Yes, it isn’t perfect, but it is a real benefit. Ideally this needs to be pushed beyond just card payments, it needs to be embedded (at least in the UK) with the Information Commissioner Office (ICO). It needs to be combined with software vendors like Microsoft, Canonical (Ubuntu), Redhat as well framework developers like Laravel, Revel, Ember.js and the end developers.

Security testing shouldn’t be a tick box at the end of the process. It should be embedded throughout the process and should be more like insurance with the more effort you put in, the less you pay.

Think about your house insurance. If you live in a dangerous area, the more you pay. If you use a “dangerous” software stack (like no framework), the more you pay. If you fit a security alarm, the less you pay. If you pen test your software, use 2FA, whatever, you pay less.

The way I would see it being pay for is at the point of sale to the end customer, the final cost would take into account the risk aspect in terms of the technologies used – are they mature, track record of vulnerabilities, etc. But also how long to fix – ie if (any) one of the upstream developers release a security fix, how long will it take before it is applied. Most companies don’t upgrade for fear of IT problems. Which ultimately results in more problems just later down the line. Forcing frequent updates reduces the risk. Testing is simpler as changes are smaller. Testing happens more frequently so staff are better trained.

This would ultimately give the end customers protection from cyber attack caused from a sloppy mistake, even by a third party. Which is what they are crying out off.

The insurance company would be required to have a solid security background in order to make this work – so much so that it might be easier for a large security company to become a insurance company. They would of course require some massive legal and (ideally) government backing to ensure they can enforce the requirements or at least some pretty major end customers who demand software vendors have the required insurance.

The final point is that the insurance company would need to proactively payout. By this I mean they need to pay bug bounties. After all, it’s like flood defences

_Flood defences_ on average prevent £8 in future _flood_ damages per £1 spent

Paying out for detailed vulnerability is cheaper then paying out damages, fines, repair costs (overtime, contractors, third parties) as well as loss of earnings and not forgetting rep.

SSRS support for both US Letter and UK A4

One of those annoying things about the US is they use different paper sizes to us Brits.

US Letter is 215.9 by 279.4 mm (8.5 by 11.0 inches)

The UK equivalent, A4, is 210 by 297 mm (8.26 by 11.69 inches)

One of our customers is based in the UK but has remote sales offices in the US, so their SSRS report are set to A4 and when they printed them in the US offices, the footer is cut off. The fix? Simple. Change the height to 279.4 mm.

Assuming you have a footer, it will now hover 17.6mm higher up the A4 page, which hopefully won’t be too terrible. It will now be American proof.

Windows Insider – new features

Today’s Windows Insider email included a few new cool features, here are my Top 3

Do more at once with the new Compact Overlay window. Keep watching a movie or video chat on one corner of your screen — even when switching apps to check email or browse the web. When an app window enters compact overlay mode, it’ll be shown above other windows so it won’t get blocked. Look for updates to the Movies & TV app and Skype Preview app to take advantage of this feature in the near future.

A smart way to lock your PC. Dynamic Lock automatically locks your Windows 10 PC when you’re not around, based on the proximity of your Bluetooth-paired phone. If your Bluetooth-paired phone is not found near your PC, Windows turns off the screen and locks the PC after 30 seconds.

Public Preview of Windows Analytics: Update Compliance. Update Compliance is a free service that provides you with a holistic view of Windows 10 update compliance for the devices in your organisation, including installation status for both monthly quality updates and new feature updates. Click here for details on how to set up the service for your organisation.

Rosetta@Home DNS Issue

If you ever need a lesson in the importance of picking a good domain registrar, then read about Rosetta@Home recent problems.

Dear Rosetta@Home participants,

We — like many of you who have contacted us — have been extremely

frustrated by the long project downtime. We (bakerlab.org) had a domain

name registration verification lapse, and our registrar (dotster.com)

and ICANN turned off DNS for bakerlab.org. We went through the steps to

getting it verified again Monday afternoon. What should have been a

quick procedure is now stretching into 4 days. We apologize for the mass

emailing which we have tried to keep to a minimum throughout the course

of the project, but this is an extraordinary situation and we have no

other way of reaching all of you now.

Since being down we estimate that we have lost a total of around 3.1

million computing hours and continue to lose around 540 computing hours

per minute.

We greatly appreciate your help and contributions!  With your help, we

have been making rapid progress in our research which has been

attracting considerable attention, for example:

NOVA:

http://www.pbs.org/wgbh/nova/physics/origami-revolution.html (the 8

minute segment on our work starts at 20:30)

The Economist:

http://www.economist.com/news/science-and-technology/21716603-only-quarter-known-protein-structures-are-human-how-determine-proteins

The Atlantic:

https://www.theatlantic.com/science/archive/2017/01/unravelling-lifes-origami/513638

Geekwire:

http://www.geekwire.com/2017/big-data-rosetta-protein-puzzles

which is titled “Big data (and volunteers) help scientists solve

hundreds of protein puzzles”

Thank you very much for your continued contributions to and support of

Rosetta@Home!

David Baker

More info: http://boinc.bakerlab.org/rah_technical_news.php

Automating WordPress maintenance

WordPress is an amazing blogging platform. However it does require a fair amount of love. Despite Mythic Beasts managing a large portion of my stack (hardware, OS, Apache, PHP, MySQL) and WordPress having automatic background updates I still find myself logging in and finding pending updates for WordPress.

The solution was WP-CLI. With the shell add-on, I SSH onto my account, then

What the above script does is download WP-CLI, grant it execute permission, then downloads my script and again, gives it execute permission.

Then its question of create a cron job using crontab, this can be done by running crontab -e then doing something like: (this runs the script every 15mins and redirects the output to a log file that gets overwrite each time it runs*)

Note:

overwrites the file. >> Appends. I’ve not used append as I don’t want to deal with it growing and really I only want the last run details. Still your mileage may vary.

Below is the final script that executes WP-CLI

MSSQL server failed on Ubuntu on Windows 10

I’ve been getting errors with MSSQL server on Ubuntu on Windows 10. The error,

Failed to connect to bus: No such file or directory

dpkg: error processing package mssql-server (–remove):

subprocess installed post-removal script returned error exit status 1

Processing triggers for libc-bin (2.23-0ubuntu5) …

E: Sub-process /usr/bin/dpkg returned an error code (1)

I managed to fix it in the end by

Of course, you should try uninstalling correctly first, which is latter of the two

I’ve included remove Microsoft GPG key and un-register the Microsoft SQL Server Ubuntu repository. For more read Microsoft doc.

Resident parking

Been trying to find out the following from my local council:

  1. How many resident parking permits have been issued
  2. How many resident parking bays are available
  3. What exactly has the money been spent on in terms of fines issued.

To say they are being evasive is an understatement.

So far, I’ve got, the response of:

  1. _This info is fluid as it is not only static residents who can apply for waivers within a given zone but also agencies who provide medical needs and other statutory services._At this time we have no plans to publish this level of information on our website
  2. We don’t hold this information, we don’t have defined bays, just areas – which I followed up with, well you must have a min size. FYI normally 5m is allowed per vehicle parked at the end of a bay and 6 m for those inside. Still waiting for them to A) publish the map so I can do the math, B) do the math and give me a number
  3. (A) making good deficits

    (B) paying for the provision or maintenance of off street parking

    © If (B) is considered unnecessary  then the provision or operation of facilities for public passenger transport services, highway or road improvement projects within the local authority area or environmental improvements in the local authority area.

Clear as mud. So if its been in place for 10 years and they bring in £400,000 a year in fines that’s £4 million been spent on? What? I assume the cost they charge for issuing the permits to residents counters A and B. Please Lord let it not be those stupid Real Time Passenger information*.

 

  • I like the idea Real Time Passenger information, just not the solution SCC purchased. It runs on an out-of-date operating system that crashes. If it was me, I’d got the Uni to build something using a Raspberry Pi and got the local schools involved (would have looked pretty cool on the children’s CV)

 

Cloudflare parser bug

So once again we have another major security leak. You can read about it, here and below is the email CEO Matthew Prince wrote to customers:

Dear Cloudflare Customer:

Thursday afternoon, we published a blog post describing a memory leak caused by a serious bug that impacted Cloudflare’s systems. If you haven’t yet, I encourage you to read that post on the bug:

https://blog.cloudflare.com/incident-report-on-memory-leak-caused-by-cloudflare-parser-bug/

While we resolved the bug within hours of it being reported to us, there was an ongoing risk that some of our customers’ sensitive information could still be available through third party caches, such as the Google search cache.

Over the last week, we’ve worked with these caches to discover what customers may have had sensitive information exposed and ensure that the caches are purged. We waited to disclose the bug publicly until after these caches could be cleared in order to mitigate the ability of malicious individuals to exploit any exposed data.

In our review of these third party caches, we discovered data that had been exposed from approximately 150 of Cloudflare’s customers across our Free, Pro, Business, and Enterprise plans. We have reached out to these customers directly to provide them with a copy of the data that was exposed, help them understand its impact, and help them mitigate that impact.

Fortunately, your domain is not one of the domains where we have discovered exposed data in any third party caches. The bug has been patched so it is no longer leaking data. However, we continue to work with these caches to review their records and help them purge any exposed data we find. If we discover any data leaked about your domains during this search, we will reach out to you directly and provide you full details of what we have found.

To date, we have yet to find any instance of the bug being exploited, but we recommend if you are concerned that you invalidate and reissue any persistent secrets, such as long lived session identifiers, tokens or keys. Due to the nature of the bug, customer SSL keys were not exposed and do not need to be rotated.

Again, if we discover new information that impacts you, we will reach out to you directly. In the meantime, if you have any questions or concerns, please don’t hesitate to reach out.

Matthew Prince

Cloudflare, Inc.

Co-founder and CEO

So lets be clear

  • …the greatest period of impact was from February 13 and February 18 with around 1 in every 3,300,000 HTTP requests through Cloudflare potentially resulting in memory leakage (that’s about 0.00003% of requests).
  • Only customers who use Automatic HTTPS Rewrites, Server-Side Excludes and Email Obfuscation were affected.
  • …data that had been exposed from approximately 150 of Cloudflare’s customers across Free, Pro, Business, and Enterprise plans
  • CloudFlare is SaaS
  • Security hole was completely closed in 7hrs 11mins from being Tweeted about an issue
  • Security hole was mostly closed off in 1 hr 8mins
  • Production fix and service restored in 3 days 10 hrs 9mins
  • People are jumping on the problem making it sound worse then it was (don’t get me wrong it was bad, but no where as bad as Heartbleed, Heartbleed, still IS a problem)
  • CloudFlare have been very transparent

…And this is why I review code I take on-board, regardless if it works and advise others to review my code

 

Caching woes

Caching always seems to cause problems, still, we can’t have it all. Today’s caching problem was to do with Redgate SQL Prompt, a really amazing plugin that helps you write better SQL code. The problem with it is the cache of database object metadata was out-of-date. I had updated a table so when I typed select * then press TAB to expand the * into a list of columns, I got the old names. Luckily the fix is easy. Refresh suggestions.

As the screenshot shows, its either SQL Prompt > Refresh suggestions or just Ctrl + Shift +D.

Date Format

I’ve had a problem recently when it can to formatting a datetime as just date where FormatDateTime didn’t work

The fix was to change it to Format.

Oddly, despite the parameter being a datetime, I found I still had to cast it as a date.

FormatDateTime was introduced in Vs 2008. I’m not 100% sure why this didn’t work.

TextBoxImpl

Another error I hit

Warning               1

[rsRuntimeErrorInExpression]

The Value expression for the textrun ‘Textbox28.Paragraphs[0].TextRuns[0]’

contains an error: Overload resolution failed because no Public ‘/’ can be called with these arguments:

‘Public Shared Operator /(d1 As Decimal, d2 As Decimal) As Decimal’:

Argument matching parameter ‘d2’ cannot convert from ‘TextBoxImpl’ to ‘Decimal’.

C:\Projects\Reports\1. Report.rdl

Thankfully Google found Qiuyun answer. I was missing the .Value at the end.

So (wrong)

=ReportItems!Textbox1

Fixed (working)

=ReportItems!Textbox1.Value

SSRS copy and paste fail

In case you haven’t heard, I’ve started a new job and one of my first tasks was to speed up a SSRS report. One of the first issues I stumbled across was this:

An error occurred during local report processing.

The definition of the report ‘/1. Report’ is invalid.

The Value expression for the textrun ‘Tx412.Paragraphs[0].TextRuns[0]’ contains an error: [BC30456] ‘RdlObjectModel’ is not a member of ‘ReportingServices’

Looking into it the issue it appears its expanded expressions when it got copied – it’s basically making the it fully qualified.

So

Cint

becomes:

Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions.Cint

The fix was to just edit to code then do a find and replace, replacing the added reference with nothing. Simple.

SSRS 2016 by default

For the past 4 months I’ve been using Visual Studio 2015 rather then Visual Studio 2013 and yesterday, I hit a bug with SSRS (shows how much I love SSRS).

The bug appeared when it came to deployment

The error was deploying SSRS report

Exception calling “CreateCatalogItem” with “7” argument(s): “The definition of this report is not valid or supported

by this version of Reporting Services. The report definition may have been created with a later version of Reporting

Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: The

report definition has an invalid target namespace

http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition’ which cannot be upgraded.

The report that error deploying was the one I had just changed. Looking at the code diff in Visual Studio Team Services you can see its added in some new 2016 goodness. I checked and double-checked my settings. Visual Studio 2015 wants to give you a 2016 file. If you manually deploy from within Visual Studio it works. Checking out the bin\debug folder reveals that it’s using the 2016 version to build a legacy version, which makes sense as the 2016 is the old (2008R2) with extra goodness slapped in – like being able to define parameter location.

So, we now have to build our SSRS reports. To do so, I’ve just created a PowerShell script that builds it using devenv.exe with the parameters for a silent build. This worked great, only it took over 50mins to run. The main lag was every time it loaded the solution it builds\checks\something with the database projects. It takes an age. The fix was to create a dedicated Report solution file (Report.sln) – I did this with a PowerShell script that copying the main solution file, which included all the SSDT (database), Integration (SSIS) packages, analysis models as well as the SSRS reports, then just removing all projects but the SSRS projects. I did this via PowerShell just because I don’t like extra manual steps. It’s a pain, when you add a new SSRS project you’d have to add it to Report.sln, which you’d probably forget and waste spend time debugging a silly mistake.

Creating the Report.sln reduce the build time down to just over 2mins (from over 50mins!), so it was worth doing.

Swollen SSISDB caused by Continuous Deployment

I recently had incident logged where the drive that hosted SSISDB database filled up on the UAT environment. We had SSISDB set to only keep 2 copies of the projects, however the additional copies are only removed when the SQL job is run – SSIS Server Maintenance job.

The SQL job is scheduled to run at 00:00 (midnight) every day, which is the default, which runs two steps

SSIS Server Operation Records Maintenance

TSQL Script: EXEC [internal].[cleanup_server_retention_window]

SSIS Server Max Version Per Project Maintenance

TSQL Script: EXEC [internal].[cleanup_server_project_version]

The problem is we do Continuous Deployment (CD), we use Octopus Deploy to automate our deployment process – it’s so effective we often do multiple releases a day. This in turned caused the log file to swell when a large number of old projects are removed, which results in the drive filling – despite the recovery model being set to simple.

The solution was simple, add an additional log file, located on another drive (I used the backup drive as it has tons of free space), run the job, truncate the log files, then remove the extra log file. Then it was just a question of running the two cleanup stored procedures post deployment.

Authenticating IoT

I was re-entering my password into our NowTV box in the bedroom when it occurred to me. Authentication sucks on the Internet of Things (IoT). The problem is you have a simple device with minimal extras. On the NowTV you have a basic remote that looks like this:

Can you imagine entering a password with a length of over 20 that’s a mixture of numbers, special characters and both upper and lower case characters? Now imagine changing that password. Regularly.

If you have to press the arrows 5 times per character, that’s over 100 presses! That’s insane!

So, what’s the solution? Well I think the technology already exists. And PayPal already had it patented. QR codes. Not sure if PayPal had thought about using it for IoT, I suspect they only thought about using it as a way of paying. So you have a QR code on the door then you scan it via the PayPal app, pay, then get your tickets sent to your Wallet to the club. Or scanning the code from the receipt to pay the bill.

For IoT, the device would generate a encryption key, this would be re-generated when the device is wiped, for example when it is resold, the device would then display a QR code, via a small E-Ink display or such, that would allow pairing (or such) between the device and a user account – via a internet connection to a web service. Unpairing the device from the user account would revoke the encryption key requiring the device to regenerate a new key (and a new QR code). However wiping the device would destroy the encryption key but wouldn’t revoke the key, this would cause some housekeeping to occur. Perhaps trying to unpairing first, however it shouldn’t be dependent on a internet connection to a web service in order to work. If the hard reset button is pressed, it must destroy the encryption key regardless if the unpairing fails or not. It must force this.

It’ll be interesting to see if PayPal expands then authentication  business beyond just for payments in the future.

Pretty time

This week I’ve been working on another new cube which had the time spent, which is in minutes. To pretty it up it wanted to be in days, mins and hours.

Here is a simplified version of the final output

prettytime

and the hopefully easier to understand MDX code

Adding the days option isn’t much more work – it does however make the code look alot more angry. Also you have the issue of what is a day – is it 24 hours? Is it the working hours, or is it less, is it the realistic billable time after you deduce admin time?

Dangers of distribution lists

Distribution list are amazing things, they allow us to send mail to our team without having to remember who exactly is in our team, this is especial true in this modern age where we are part of many different teams, sometimes without even realising. These lists can contain hundreds or even thousands of members and the latest NHS IT bug has left IT departments maybe wanting to double-check their distribution lists – hopefully before the security team comes a knocking.

Luckily, this is really easy in Excel 2016. Built into Excel 2016 is PowerQuery and one of the out-of-box connectors is Active Directory. With a bit of PowerQuery magic, you can easily pull this data out.

So we want to:

  • Connect to our Active Directory
  • List out all our Groups
  • Filter to email-able Groups
  • Include
    • the group owner
    • the members
    • any security \ restrictions on who can email the list

Note: If your unluckily enough to be running an old version of Excel, it possible to do the same thing in Power BI Desktop, which is a free download.

Below is the M code to do this:

For more help on how to enter the M code into Excel see my previous post.

UPDATE: Please note that this only works with Active Directory and doesn’t work with Azure Active Directory. Others have said this is possible via odata or such however I haven’t tried… yet.

Microsoft SQL Server on Linux

This is absolutely amazing, Microsoft, running SQL Server, in Linux Redhat (RHEL)!

Comments

One of the annoying things about working with multiple languages is they each have their differences – obviously, otherwise there would only be 1 language! Although there is good reason to have these differences they still have elements that are a pain. One of the most annoying things, other then “do you require a semicolon at the end of the statement?” is comments. Comments aren’t executed or complied but they help developers read code. Remember, the code is a interpretive language, its designed to be gateway between the machines and humans, if you write something complex your more then likely need to be include more comments explain what it does or look at refactoring so other developers can continue your work.

The few options are:

Double dash (–)

— Single line quote

Single Hash (#)

# Single line quote

Double forward-slash (//)

// string domain = “matt40k.uk”;

All of these are single line quotes, as in each line needs the require comment and the comment continues till the end of the line \ carriage break.

You also got:

Forward-slash and asterisk (/*)

/* Multi-Line

quote */

Forward-slash and asterisk marks (/*) the start of the comment, the comment continues until the asterisk marks and Backward-slash (*/). The code must close any open Forward-slash and asterisk marks (/*) comments in order to compile.

With Forward-slash and asterisk, you can comment in-line, for example

Select * from /* Change the code to the right */ sys.tables

This is valid T-SQL that will return the all the columns for meta data about tables on the selected database

You can also use this across multiple lines, for example

select

TableSchema = schema_name(schema_id)

,TableName = name

/*

You could also add

other tables

 

*/

from

sys.tables

 

Power Query Formula (M) Language

Double dash (–) No

Single hash (#) Yes

Double forward-slash (//) No

Forward-slash and asterisk (/*) Yes

Transact-SQL (TSQL)

Double dash (–) Yes

Single hash (#) No

Double forward-slash (//) No

Forward-slash and asterisk (/*) Yes

C sharp (C#)

Double dash (–) No

Single hash (#) No

Double forward-slash (//) Yes

Forward-slash and asterisk (/*) Yes

 

Fun fact, my most commonly used comment?

To-Do

Where has SIMS Bulk Import gone?

Cut-down version, I’m not working with SIMS anymore and I can’t pass on my work without them getting a large bill from Capita for using the business objects.

Years and years ago I was working on a SIMS help desk at a Local Authority (LA). I had a school log a call asking about importing email addresses. Like many schools this school had just purchased a messaging service that allows sending emails and texts, the problem was they needed them in SIMS .net, as you can imagine the idea of manually retyping 1,000+ email addresses was rather daunting. So, after putting a call into Capita Partner Team asking for the API documentation I spend the evening building a simple import process. The next day I phoned the school to give them the good news and we imported their email addresses. A few days later a few more schools asked the same question and I decided to continue spending my evenings expanding (it imports email, telephone and User defined fields from XML, CSV and Excel spreadsheets) and refining the tool. I’ve always developed in my own time, I’ve never claimed overtime etc and I’ve never charged a penny for using it.

SIMS Bulk Import uses the SIMS .net business objects (API), its uses the the SIMS .net DLLs. Its the exact same .NET libraries that the SIMS .net library uses.

At the time we were in a partnership with BT, who had a procedure for raising new business ideas. The process involved working out what type of change it was – in this case it was a efficiency saving. To put it simply this means you can’t charge more then what you can save, in this case labour.

Capita charge the school. Then they charge the partner for write access, which the partner then charges on the school so the schools end up paying twice for support on one thing.

When you take into account the Capita charge, the handling of money costs (invoicing, collecting and chasing etc), the helpdesk costs, the fact people expect a certain standard, ie you’re going to have to invest alot more in terms of development – including documentation. It just doesn’t make sense. It’s actually more cost effective to hire a temp to manually key in all those details!! Nuts!!

So at this point I basically decide I’ll give it away. I really didn’t want to see my hard work go to waste. I basically managed to wangle it into the public domain without finding a massive Capita bill land on my desk!! Its been in a wild for many, many years (with Capita knowledge) with a grand total of ZERO corrupt SIMS databases. I find this quite an achievement. Don’t get me wrong, SIMS Bulk Import has failed a number of times, but it’s never left your SIMS system in a worse state (unless you’ve done something stupid like successfully imported the same email address to every pupil in SIMS!)

A few years later I switched teams and stopped working with SIMS .net. SIMS Bulk Import has been stable for a while and I’ve had a few commits from individuals. I’m now at the stage where I’m going to leave the LA and go work somewhere else and its unlikely to have a SIMS .net license let alone API access. I needed to find a new owner for SIMS Bulk Import. Anyone who’s talked to me would have described SIMS Bulk Import as the poor man’s Salamander Active Directory, it is simply put the next logical step if you work out how you’d improve SIMS Bulk Import, its what I would have done to make SIMS Bulk Import into a commercial product. Luckily Richard agreed to take it on and even help me recover some of the costs of SIMS Bulk Import. Before you shoot off to SalamanderSoft to download it, let me save you the disappointment. Capita has said they would charge them a license fee for each school using it, ie it wouldn’t be free. At this point I guess you can see where I’m going with this? SIMS Bulk Import isn’t worth paying for and Richard already has the expanded version (that IS worth paying for).

So in short, your options are:

  1. Except you can’t bulk import anymore and get typing or copy and pasting \ hire a temp
  2. Look at automating your processes and buy Salamander Active Directory
  3. Wait for Capita to come out with their own product – I suspect they will and charge. They do a limited SQL script that injects the records directly into the database, ironically bypassing the business objects (but hey, they support it so its all good right?)
  4. Switch MIS who doesn’t charge for partner access \ gives you bulk import routines (Eduware Network has a list of MIS suppliers)

Option 5 is carry on using it. I’m sure even with me saying, no, don’t do that (and I’m sure Capita will agree) – someone will. So a few comments.

Make sure you have the latest (or should that be last) version – its 2.5.0

It should be digital signed, think of it as SSL for your applications. If you right-click on SIMSBulkImport.exe or the .msi installer you should see an extra tab – Digital Signature and you should see its signed by me – Open Source Developer, Matt40k. If your copy doesn’t have the signature its possible code has been injected and it is unsafe.

digitalsignature

You should be OK as it uses whatever version of SIMS API you have installed, so it’ll just break one day and by break I mean it won’t let you login or will just give you all import failed (if it does fail in terms of SIMS database corrupt then some thing terrible has happened with Capita API, but I digress).

A few people have forked my code, for whatever reasons, I would just point out that the most up-to-date fork is 80 commits behind mine. That’s a fair amount of work that missing from those forks.

Anyway, hopefully you’ve found it useful whilst it last.

Are hybrid shops missing the point?

With rise of the internet high street businesses are rushing to adapt so they don’t become a thing of the past. However in their rush are they being a storm trooper? Are they missing the point?

First, lets look at two successful hybrid business. Hughes, a electrical store based in East Anglia and Argos, a British catalogue retailer operating in the United Kingdom. If we focus on Argos as its more well known business.

If you order online you can see the in store stock. You can then go in store and collect it. High street shops have become showrooms and mini warehouses.

Compare this to Game and Staples, these stores compete against each other. Online vs instore. The prices are different, online prices are cheaper. When you order online to collect in store, they post your order to the store. You order comes from the central stock, it can’t come from your local store.

It breeds resentment between local stores and corporate HQ. It leads to poor customer service.

I recently cancelled an online order with Staples because they claimed to do next-day delivery, I arrived in the store the next day only to find my order hadn’t arrived. So I phoned up, cancelled it and bought it instore. So it cost them

  • to answer my phone call, because I couldn’t cancel via the website
  • to post my order
  • to receive my order, instore
  • to post back my order to their central warehouse
  • to answer my phone call chasing the refund
  • to answer my phone call chasing the refund, again
  • to answer my tweet, again chasing the refund
  • to then refund my money (not sure if they have to pay a card charge)

All these actions have a staff cost, after all, staff don’t work for free.

Missing

SQLPrompt

So one of the really nice sponsors at SQL Saturday had a vending machine that was rigged up to release a treat when you sent a tweet. Like so

and give you…

In mine had a SQL Prompt license. At first I was a bit disappointed, I hoping for SQL Test license or a full blown SQL toolbelt but the more I’ve been using it, the more I’m loving it.

Like the IntelliSense… or the fact press table next to the * expands it out into the list of columns

sqlprompt_1

The fact it re-opens the tables you had open the last time you were in SQL Management Studio, that it has a history. I’d like to say I never close the wrong window, especially when I haven’t saved them, but I do. Luckily with SQL Prompt it allows me reopen it.

sqlprompt_2

It even stops DBAs going to jail :p

 

 

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 😉

Toy cars

Darcy (7)
The Darc (7)

Over the summer holidays I got my son to create a spreadsheet detailing some of his toy cars which we then imported into PowerBI.

Created by a 7 year old in under an hour

 

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

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<br /> 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.

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

Feature request - SSDT

 

BI resources

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/

 

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

PowerQuery - Excel > Menu” width=“913” height=“565” srcset=“//matt40k.uk/img/2016/06/pq_menu.png 913w, //matt40k.uk/img/2016/06/pq_menu-300x186.png 300w, //matt40k.uk/img/2016/06/pq_menu-768x475.png 768w” sizes=“(max-width: 913px) 100vw, 913px” /></a></p>

<p>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.</p>

<p><a href=PowerQuery - Excel > Connection” width=“602” height=“215” srcset=“//matt40k.uk/img/2016/06/pq_connection.png 602w, //matt40k.uk/img/2016/06/pq_connection-300x107.png 300w” sizes=“(max-width: 602px) 100vw, 602px” /></a></p>

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

<p>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 <strong>group</strong>, then click <strong>Edit</strong>.</p>

<p><a href=PowerQuery - Excel > Navigator” width=“885” height=“704” srcset=“//matt40k.uk/img/2016/06/pq_navigator.png 885w, //matt40k.uk/img/2016/06/pq_navigator-300x239.png 300w, //matt40k.uk/img/2016/06/pq_navigator-768x611.png 768w” sizes=“(max-width: 885px) 100vw, 885px” /></a></p>

<p>This will then open up the Query Editor</p>

<p><a href=PowerQuery - Excel > Query Editor” width=“1534” height=“582” srcset=“//matt40k.uk/img/2016/06/pq_query.png 1534w, //matt40k.uk/img/2016/06/pq_query-300x114.png 300w, //matt40k.uk/img/2016/06/pq_query-768x291.png 768w, //matt40k.uk/img/2016/06/pq_query-1024x389.png 1024w, //matt40k.uk/img/2016/06/pq_query-1200x455.png 1200w” sizes=“(max-width: 1534px) 100vw, 1534px” /></a></p>

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

<p>Now its a question of filtering the list – if you right-click on the <strong>sAMAccountName</strong> and select <strong>Text Filters</strong> > <strong>Begin With</strong>. 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 <strong>Query Settings</strong> > <strong>Applied Steps</strong>, if you click the cog next to <strong>Filtered Rows</strong> your get</p>

<p><a href=PowerQuery - Filter

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.

Source: FOI request via WhatDoTheyKnow

 

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.

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.

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.

 

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

I spotted something odd.

Now, in SQL Server terms there are two types of licensing

  • Server license + CALs
  • Cores (processors)

Expect for Enterprise, you can only get Core licenses.

The next question was, have they made a edition for Windows Server Core, a cut-down version – but the files sizes are the same, so this was unlikely, Microsoft also hadn’t made any statement about a cut-down version (which you’d expect), so again, this was unlikely.

Once again, Twitter came to the rescue, more precisely Joey D’Antoni did. The answer is legacy. Any old Microsoft licensing agreement with software assurance (SA) can get the latest version. This means that anyone who had an old SQL Server Enterprise Server\CAL license is now on the core based licensing – there are a few gotchas which Joey refers to – your still limited my the number of CALs, but your also limited to a max of 20 cores.

So in short

  • SQL Server 2016 Enterprise Core is limited to 20 cores and is for legacy license holders.
  • SQL Server 2016 Enterprise isn’t limited.

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 documentation to feed the foreach loop.

However there is a major problem with the DMVs. They only list processed cubes (and their dimensions). So how do you get a list of unprocessed cubes (and their dimensions)?

Answer? Using the Analysis Management Objects (AMO). This will return the same list as SQL Server Management Studio (SSMS), rather then just the processed, like the DMV or Excel lists. I’m currently trying out a few ideas to find the best solution – so as a script component (source) or a stored procedure. As the saying goes, it’s production ready, but its not GitHub ready – not yet anyway.

Thanks again to Chris Webb for pointing me in the right direction

 

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. 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.       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents)     at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events)     — End of inner exception stack trace —at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events)     at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package()     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)     at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

Translates as I hadn’t installed the Oracle client and the Microsoft Connectors v3.0. Speaking of the connectors, the Attunity Oracle adapters are amazing, if your connecting SSIS to Oracle, these are a must (especially as they are free!)

SQL Server vs Containers

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. So for example, traditionally you’d ship the .NET application, with “Docker” you’d ship .NET and the OS along with the application – its not quite right, but that’s the basic idea.

At one of my local user groups, Suffolk Devs, back in Sept 2015, Richard Vickerstaff done a talk on Docker. One of the things I took away was this was very dev friendly, Richard was honest in the fact he has yet to deploy to production in this manner and the general feedback from the room at the time was no one else had – this doesn’t mean no-one has since of course, but you start to get the feeling the DBAs back home wouldn’t be happy. The other thing was the dev nature of it, for example, when your developing, you don’t want to be held back by “rules” that protect data, it is after all, development, your not going to have production data in your dev environment, right? So, if you don’t “pin” your storage to a persistent path, it’ll get purge when you stop your docker image. Can you image if you forgot to set the production config correctly and come a reboot all your data disappears? I can already here my friendly DBA screaming.

To get a docker\container for SQL Server, you’d either have to select one from list of images with SQL Server and its going to be huge, at least until they get SQL Server 2016 on Nano Server. Or you’d have to have PowerShell DSC to install and configure SQL Server.

In terms of Microsoft SQL Server, or really any database, I don’t believe you’d need to have it in a container, docker or otherwise. Since SQL Server 2012 you could have contained databases, this is where the login information that is normally stored in the instance master database within the application database. This pretty much made database independent of each other within the same SQL Server instance. I’d admit this was introduced for Azure, but this leads me onto my next point.

The best thing to manage is the the thing you don’t have to manage. So why would you want to spend time setting up SQL Server (as developer), regardless of if its in a container if you can just click a button or run a script to auto provision a Azure DB? Surely if your developing anything new, which is going to use a SQL Server database, surely you should be aiming for Azure DB? Even if you’re not, there aren’t that many types of SQL Server, you don’t often run into dependency hell with SQL Server, none that justifies building individual containers, at least, in my opinion.

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.js for its visuals. Microsoft has continued on the path of open source-ness by open sourcing some 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 Slicer by Jan Pieter Posthuma.

Hierarchy SlicerThis brings in some more missing features into Power-BI. For more information on Hierarchy Slicer visit his blog.

The other one worth a mention – as it adds some comedy to any report is the Meme visual by Sachin Patney.

Meme

If  your interesting in learning more about Custom Visuals, Marco Russo is speaking at the London Power BI User Group where is is going to talk about Using Custom Visuals in Power BI.

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.

Doing so its pretty easy, I already have a my database project setup in Visual Studio, all I then did was right-click then Schema Compare…

The app will then open and you select, from the right hand menu, the target, the database you want to compare your project with. Then you just click the compare button.

This appeared to work but didn’t display any results. When you look at the bottom status bar it reads

Comparison complete. No differences detected. Restricted comparison. See Error List for details.

I knew there we at least some differences. I then click on the Error List table below it which revealed

The reverse engineering operation cannot continue because you do not have View Definition permission on the ‘Warehouse’

Googling the problem lead me to a number of recommendations, some didn’t work (like restricting it to only tables), others I didn’t want to do – I don’t want to give myself db_owner on production. The final solution came as a simple one liner

GRANT VIEW Definition TO [DOMAIN\user]

Just added it to my Database Project as Post-Deployment script and its now comparing ok within Visual Studio (SSDT).

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 🙂

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. Health data has much stricter rules, the data is no more sensitive then some data already held by the local authority in my opinion. Perhaps, they are perceived as stricter when in fact they are, more evolved. Sharing health data is a lot more common nowadays, it’s more defined, it’s been through the growing pains (boy has the NHS had some IT growing pains) and has grown into stable mature process. This is of course making the local authority sound bad, it isn’t, they have been quietly learning from its elder brother, observing what has gone wrong and using the things that have gone right. There is however, still too much manual spreadsheets occurring, it’s a long road and but progress is being made.

So with the new business intelligence platform being developed, we need to review our existing platform, for this post, I’m talking (well writing) about auditing. Auditing on old platform was enabled. But it wasn’t really auditing. It was usage. It had a business focus. 

  • What? Reports / Users
  • When? When was it run
  • Why? Was it worth writing the report

On the new platform we need true auditing. We almost need the same level of auditing as banks. BI is however, read-only, our auditing requirement needs to only be reactive, we don’t need to define triggers on the data to kick off workflow. We can get away with running an ETL job to extract the audit logs and transform it into a standard star fact/dimensions data structure.

The solution I have design for us, is to use the uber fast multi dimensional cube for analysis (which is what it designed for) and Reporting Services (SSRS) using raw T-SQL for detail. Sensitive data is not placed in the cube. The idea is cube has numbers with the ability to slice and dice using a variety of items (dimensions) – to a certain point. So for example, say there are 50 children in care, you could then slice this by gender, then by age group, then by ethnicity and so on. This data in itself isn’t sensitive. If you print this off and left it on a train, what would happen? Not much. This data is often published by the government and quoted by charities. NB: A word of warning however, a direct link into the raw system can be a dangerous things. Entering data is prone to mistakes and giving direct access will remove the quality gate data stewards provide, that said more prompt data more frequently, even with a few mistakes is often going to be better then out-of-date.

This leads me up to the detail. The detail allows a select few to go beyond those numbers, to drill into those numbers into a SSRS report. Now, one of the nice abilities of having an on-prem BI platform is we can store the raw uncut original data from the source system. This means we can work our way down to the bottom grain of data. Which makes debugging and testing a lot easier. It however make the definition of what is sensitive a lot harder. It sounds simple, but it can get rather messy when you get down to the nitty gritty. This nitty gritty gets audited. It gets audited using the standard Audit feature in SQL Server which has been a part of SQL Server since 2008 version. This has, from what I’ve seen from UAT, reading and talking to others, provided a low performance impact binary file audit log. This in turn gets ETL via a SSIS package into warehouse. They was a little design consideration round the to-be audited star schema, luckily the sensitive fields were already in one dimension and bit of ETL magic to make it a bit more bulletproof.

The end result. The business controls access for users to self-serve data analysis, in near realtime, without buying a license, in short they can give users access without access to the raw data. The business can use the same tools and drill down to the raw data. Access to the raw is fully audited and can be reported on by the business so we can be held accountable. It’s all about trust.

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

 

Building a test system

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! I found this article on Oxford SBS Guy really useful for building the template image.

Showing SIMS Bulk Import some love

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.
  • PowerShell module – you can blame\thank Ryan Yates for making me want to do this one
  • Log submission – I’ve finally started pull this together, I’ve desensitised the log file by basically creating two. One for log submissions and other for local debugging. The main issue is testing, both the web service and the application.

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

Recently, I discovered Project Nami, a fork of WordPress that replaces the MySQL database with MS-SQL (which I quite like). As you would expect, its bundled really nice for Azure. I setup a quick test site on Azure and had a play, it worked a exceedingly well, but the lack of IPv6 left me with a bitter taste. Why hasn’t Microsoft added support for IPv6! This would mean I would have to use CloudFlare, which isn’t exactly a problem. The summary outcome was Azure was overly complex, I’m not saying Microsoft hasn’t done a excellent job making it simple to setup and use, its just its overly complex for what I want, my content is static. Again, Project Nami is excellent, its just, an extra complexity. CloudFlare, I’m still 5050 on. I have another site using CloudFlare and I’ve had no problem, its just, again, its overly complex solution to simple problem. Lots of bells and whistles.

Going the other way, I looked at WordPress.com. This would the similest solution, but that comes at a price, at nearly 3x times the cost per year and alot more restrictions – no PowerBI iframes (well they haven’t added support… yet)*

So, now I’m back to Mythic-Beasts, they have IPv6 support, are UK based, have excellent support, good value for money. They’ve also added support for LetsEncrypt. So HTTPS is free and only a few clicks – something that you can do with Azure, but with a £45 or above package. Sigh.

If nothing else this “forced” me to look at Project Nami and look deeper into AzureAzure is a pretty big beast thats getting bigger by the day. I’ve also reconfigured a few bits on my websites on Mythic-Beasts (I still have a few things left to do!), so as always, it was worth reviewing the market.

  • Yes, I am aware I haven’t published any public PowerBI reports… yet!

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

  1. Load data from Source system into Staging
  2. Build ODS intermediate tables
  3. 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. Equally I don’t want to figure this out each time we add a new package.

I basically want a controller task that manages it.

We have a automation build\deploy process,  so you commit the (SSDT\SSIS\SSAS) packages and it deploys it and creates a SQL job for each SSIS package. We could add step to trigger the controller to update the schedules based on the change.

Now we would need to define the dependencies, basically we need build a hierarchy. Technically I could read the SSIS packages then parse the select statements then create some logic to work it out automatically, but for now, it’ll be a static table(s).

So. Do I create a SQL stored procedure to create a SQL job(s) with multiple steps or a SSIS package using something like BIML?

 

 

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

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 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. Unfortunately this doesn’t look like its going to happen this quarter.

How I got here

So when I started in BI we where using Microsoft SQL Server 2008.

SSIS packages used XML configuration files and deployment files were created, manually copied to the SQL Server, then you logged onto the server – via RDP, ran the SSIS deployment file – which basically created a copy of the files and changed the variables in the XML configuration file.

Database changes was done by a giant SQL script – it started out as a simple script out the database objects, but future changes happened by simply updating the SQL script, manually.

Deployments were a pain. Every deployment had to be (re)deployed to DEV, then to UAT before finally getting to PROD. They took time and because they took time you tried to avoid them. Which meant every deployment was bigger. If you had to do something you would try and do it easiest way, ie manually change the raw SSIS package. This of course leads to drift.

When I started my job, one of the first tasks was to bring UAT up-to-date (after setting up auditing). After a quick look at the database schema I could tell the database objects were out-of-date, database compare tools were absolute life saver. Unsurprisingly, they (who shall remain nameless) didn’t commit the code to version control either. The only copy of the code was PROD, DEV had a even more updated version, however it was left in a unfinished state.

Right, enough with the horror stories of how I started my journey in BI.

Fresh start

When we started to talk about upgrade from SQL Server 2008 to SQL Server 2014 I thought about some of the things I wanted.

I wanted a single tool to design the whole end-to-end product. From the database, to the ETL packages to the data model all the way to the end user reports. Guess what? Its Visual Studio.

Previously I had been to my local SQL Pass User Group where Alex Yates and explain the whole continuous integration and deployment methodology, but aimed at the DBA. To put it simply, it forces you to use version control, which I love. The continuous integration basically means build on a dedicated machine – this gets around the whole “well it works me for me” arguments, if you forgot to commit a file and the build fails, its clearly your fault. If it builds OK on the clean build server, but doesn’t build on your colleagues, you know they have a problem, not you. The deployment is pretty straight forward, you don’t really need it when you have a single component, but when you have something like BI, where you have multiple components that have dependencies on one another, like the SQL database objects, the SSIS packages and the SSAS packages, you need to create an “installer”. I also wanted to a “standard” deployment method, if the automated process failed, I want to be able to run it manually.

The solution

For the continuous integration element, the build process, I’ve used TeamCity from the very excellent JetBrainsTeamCity is a very mature stable product, my one “grip” is that based on Tomcat, luckily JetBrains have bundled it very well so its not really a problem. The main reasons for using it are, its a stable product (on Windows OS), its a got excellent support (ie you can find help on the internet) and it has a free tier – Professional Server license gives you 20 build configurations.

For the continuous deployment element, I’ve used OctopusDeploy.

East Anglia SQL Server User Group

Anyone working with SQL Server will be well aware of the mountains of training material, blog posts, videos, code snippetsevents and other helpful resources that helps anyone who comes into contact SQL Server regardless if they are a beginner or a seasoned veteran (even MVPs!). One of the pools of knowledge is SQL PASS.

is an independent, not-for-profit organization run by and for the community. With a growing membership of more than 100K, PASS supports data professionals throughout the world who use the Microsoft data platform.

Not only do they do Virtual Chapters, but they also do Local Chapters. Local Chapters are an excellent way to make connections with (physically) local fellow experts. I would highly recommend if your working with SQL Server to join, both a Virtual Chapter and Local Chapter, as they are both excellent ways to learn and build a network of friends who you can point you in the right direction when your stuck. Best of all, its FREE!!

My Local Chapter is run Mark Broadbent, a Microsoft Certified Master in SQL Server (MCM) and Microsoft Data Platform MVP (Most Valuable Professional). Mark has this year, rebranded SQL Server User Group from SQL Cambs to East Anglia SQL Server User Group. He done this after myself and a few others spoke to him about running more local user groups. I personally, didn’t want to setup a new, separate, user group that will end up competing with the other. Suffolk, Norfolk and Cambridgeshire are neighbours, they should be working together, not competing. Hopefully the new User Group name reflects this. I’ve been working with Mark at setting up the first East Anglia SQL Server User Group, which I am delighted is being held in Ipswich at the University Campus Suffolk (UCS) Waterfront Building. This is lovely modern building that, selfishly, is a 20min walk from my home or work – can’t get much more local!  It is also a 20 min walk to the train station and has free parking in the evenings so hopefully I’m not the only one who likes this venue 🙂

UCS

The first speaker (under the new name), will be Prathy Kamasani who has by some miracle been able to compress all the What’s new in SQL Server 2016 for a BI Professional into a single presentation. If you’ve been living under a rock for the past year, Microsoft has pulled out all the stops with SQL Server 2016 and given the whole Microsoft BI stack a update, even Reporting Services (SSRS)! They’ve been announcing some pretty major new features every month. It’s truly incredible but at the same time rather overwhelming. At the end of the meetup, you’ll up to speed with all the cool new features in SQL Server 2016.

Register here for free for What’s new in SQL Server 2016 for a BI Professional.

Hopefully I’ll see a lot of you there and we’ll have many more User Groups in 2016 and beyond and yes, pizza will be provided 🙂

This East Anglia SQL Server User Group meetup is sponsored by: sqlcloud_image

Automated SQL Server BI deployments with OctopusDeploy

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. I use DbUp, a open source library, for database deployment and a custom C# application for SSIS package deployment. Today, we surpassed 1,000 report deployments to production, we’ve also deploy over 270 SSIS package changes in about a year.

So when it came to upgrading our BI platform from SQL 2008 to SQL 2014, one of the key things I want was deployment automation. The SQL 2008 platform required manual deployments which often lead to mistakes and ended up writing the entire day off, per deployment. Unfortunately, my current process was pretty basic. Database deployments are idempotent, it drop any objects and recreated them, every time. This is fine for interfaces where tables only hold in transit data, but for BI, the idea of dropping a staging table with 140 million rows that takes over 4 hours to load doesn’t make me want to do any deployments. Luckily, the problem is already solved. SSDT. And there is already a PowerShell step template on the Octopus Deploy Community Library.

Also moving to SQL 2014 allowed me to use the new ISPAC, again, there is already a PowerShell step template on the Octopus Deploy Community Library. There is even a PowerShell step template for SSRS.

The only thing missing was SSAS. After watching Chris Webb’s video tutorial – Cube Deployment, Processing and Admin on Project Botticelli, I decided it had to use Microsoft.AnalysisServices.Deployment.exe. After a bit of scripting and testing, I managed to write a PowerShell that updates the xml config files for the deployment – it sets the ProcessingOption to DoNotProcess’. It updates the Data source – where the cube will refresh the data from. The script isn’t perfect. For starters, what if you have more then one data source? Also what if your not using SQL Server 2014? Still the great thing about open source is that other can update it. Anyone can improve it, its not reliant on me having free time. So hopefully by the time we move to SQL 2016 someone will have already updated it to work with SQL 2016.

In a future post I’m going to blog about Octopus Deploy in a bit more detail and how I’ve setup my SQL Server BI deployment process (in a lot of detail). I’m hoping to try using Microsoft Visual Studio Team Services to build the Octopus packages. Currently I use a on-prem TeamCity, which is excellent, its just… I don’t like managing extra servers when I can use SaaS. I like development over administration.

I’ll leave you will a screenshot of my deployment screen in Octopus Deploy, and yes, that’s one button press to get my changes into UAT

octopusbi

GeoJSON

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

Getting collation descriptions

Just an update as I noticed I forgot to say how to get the names and descriptions of the collations your SQL Server supports

Using PowerShell to check setup

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.

Finally, I wanted to check the service accounts that my SQL services were running as, again, PowerShell to the rescue.

Collation

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

collation

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)

  • Glossary

CI – case insensitive – recommended

CS – case sensitive – avoid

Deploying SQL 2014, looking at SQL 2016 \ PowerBI

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. Power BI is out, but… we’re holding back, again we’re going to use it, its just a question of when it becomes the main BI tool.

So, we’ve already got the bulk of the data modelled in a multi-dimensional (MD) format, we just need to built a SSAS MD cube, the little pockets of data will be tabular cubes – users will use Excel for interacting with the data as well as end user reports – SharePoint will be the “portal” – much better then file system. SSRS will be for static reports – such as transactional reports that need to be static.

Going forward, it looks like PowerBI will replace the bulk of the Excel\SharePoint work – this will give a superior end-user interaction with the data, not only will it allow natural language interrogation of the data, but also personalised views – so you’ll see your data off the bat, you won’t have to drill down from your company, then team to get to your data. Data will come off the same ssas cube, so it’s still a single point for the data, its just a new way to interact with the data.

In terms of data security, we can limit PowerBI to just the summary data – via the ssas cube – so the fact PowerBI is hosted outside of the UK shouldn’t be a problem. The detailed stuff will still be on-prem – so SharePoint would still be needed in terms of a repository, but the bulk of the users will be using PowerBI, so its not as key, again, the data will come off the same ssas cube so we won’t have the “PowerBI says this but Excel says this” (insert caveat about people looking at different things – nb: need to check if can add url drill-down in PowerBI to say SSRS report with the detail).

The other bonus of the PowerBI is the friendly-ness towards the micro data sources – like the odd Excel spreadsheet. In terms of SQL2014, I was thinking about pushing towards Access Online – which backends the data into an Azure DB, still this will ultimately depend on policy on the data then the technology. Key is, we’ve got options.

In terms of end-user delivery SQL2014 will be perceived as SharePoint, really it will be Excel. The SharePoint is nothing more than a gloried online file share, we could in theory use OneDrive – at least until dashboards are more common but that won’t occur until the data is more readily accessible. The real power behind Excel will be using it correctly – using Excel as a visualisation tool to interact with the data and letting the SQL Server back end deal with grunt work – both in terms of the database and analysis services – rather then getting Excel to do all the grunt work and wonder why it so pants at it. The “SQL2016”/PowerBI rollout should be easier to land with end-users as there is a new thing being delivered, it’s easier to grasp a new product rather than an enabling some (new) features on an old tool. The key for delivering SQL2014 will be the SharePoint being the main place to go, it could become PowerBI. Plus side it means the SharePoint delivery is less critical as it could be replace if it doesn’t work out, if it does, lets hope this feature request goes head – https://support.powerbi.com/forums/265200-power-bi/suggestions/6740156-embed-in-sharepoint-office365. Does mean I have to have a look at branding and friendly urls for PowerBI.

For public data publication, in terms of “SQL2014”, I am thinking Excel to get the data, then saving as a CSV (or passing onto IT to produce a standard extract, again as CSV) – opening this in notepad or such to validate it contains no unwanted (such as protected fields) data, then publishing (onto public website\github??) – then using Tableau Public to produce graphs for public consumption – Tableau also allows easy export data (as csv). For PowerBI – I would hope you could use PowerBI to interact with the data, then export as a csv – verify it (perhaps in a basic online viewer – similar to the way github displays csv tables), then publish it – ideally on a marketplace type thing so it’s easy for others to find – again as a CSV and ideally with a Tabular Data Package definition file, then allow embedding of a simple interaction of the data on a public website for no cost. So PowerBI license to produce, no license to see \ basic interaction of data (I guess csv file, json definition file, and d3.js) – then a PowerBI license to bring the data into own PowerBI to join with own data.

Problem with ISNUMERIC

Todays problem was brought to you by a highly customizable system with poor user validation and a user who doesn’t know the difference between data and formatting. First the system. The supplier has created a highly user customizable system, they can for example create a web form with zero coding – 100% user config. The problem is, technically, it creates a mess. So your nice new form with a number of questions stores all its answers in a single table with the data going into a nvarchar(max) column – which is fine for free-text columns, but not so good for integer fields. This is especially a problem when you have a form that has drop-down options (luckily stored in a different table more efficiently) which generates an amount which the end user can overtype in order to moderate it up or down, which has zero validation.

The data is “stored” as numeric in the database so, for example, 1200.34, but is formatted as currency – so £1,200.34. The problem occurs when the user overtypes the amount, when they do, they overtype it, say as, 1201.34, but they don’t enter 1201.34. They enter £1201.34. Now this is a problem as when I load the data into the Data Mart, I store the data as a numeric(18,2), which means I need to cast it. This will of course fail if the user has overtyped it as it isn’t a numeric – which has historically happened. The way I resolved it was to strip out the £ sign using a replace then to add a ISNUMERIC statement as a fail safe.

However despite my failsafe it failed today – the problem was with ISNUMERIC – if you read the man, it says “ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($)”. What it doesn’t tell you is it also covers commas – so:

select cast(replace('£1,220', '£', '') as int)

will fail with

Conversion failed when converting the varchar value '1,220' to data type int.

this is despite ISNUMERIC returning 1 (ie valid)

select ISNUMERIC('1,220')

The fix is to replace (well remove) commas as well as the pound sign (£). Going forward, in SQL2012 Microsoft has introduced Try_Cast which might be another option.

Joys of C#

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.

The summary of the requirement change was this, currently we export files to:

\Client Name\Month Number-Month Name\

so, for example, for the April export for Client A it would be:

\Client A\04-April\

What they wanted to change it was:

\Client Name\Fiscal Month Number-Month Name</span>

so, for example, for the April export for Client A would become:

\Client A\01-April</span>

Again, as this was a script task, it was just a question of adding a new method and adding it in. For reference, here is the method I quickly bosh together:

Thanks to some clever upfront work – with both the script task and OctopusDeploy, this change took 5 mins.

SQLSaturday #411 – Cambridge 2015

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. I find Cambridge a nice place to visit, not just because it doesn’t involve me going on the underground.

IMG_0234IMG_0239IMG_0233

IMG_0232IMG_0237

Some how Mark has managed to put together an entire day SQL Saturday event with two precon days – I swear this guy has gotten hold of Mary Poppins bag. Don’t let the precon name fool you, these are (well were) two full days of very good training. After all, its not every day you get a full-days training with a Microsoft Most Valuable Professional (MVP) or the Data Warehouse Architect at UNOPS, the implementation arm of the UN!

One of the downsides of attending was having to get up a 5 am 🙁

Note to self: Next time, stay in a hotel – yes Mark, I should have read your deals on hotels (and looked at the back of the guide book for the map)

IMG_0221IMG_0210IMG_0230

Day 1

IMG_0227

My first day was with Penny Coventry – Automating business processes with SharePoint, Office 365 and Azure. This was a really good session where I got to see some of the power of SharePoint and cool new Azure features from the power users perspective rather then the overly technical developer perspective. I’ve often found users will shrug off ideas and solutions when explained by a “techy” as being too “technical” – and to be fair to them, we do get a big excited and go over the config line and into the development world all too often.

The out-of-the-box just config, no development features in SharePoint like Workflows are amazing. From a “techy” point of view, I’m amazed at how easy it is to do something that would be quite complex to create as a bespoke app. It was also good to see how Microsoft are expanding into the cloud, both with Office 365 and Azure. I’ve previously only really looked into Azure from the “techy” side, like Troy Hunt’s “Have I been pwned?” site but I hadn’t had a chance to have a look at things like Logic App. Logic App which looks like its another workflow type services where you can drag and drop connections (kinda like SSIS) and do things like pulling tweets from Twitter into a SharePoint list, or a MS-SQL database or whatever. Again, this is the sort of thing power users can do without IT or a developers. For me this is great news, creating custom connectors in SSIS is a pain, if the power users can get data into a MS-SQL database without me developing anything, great. Makes my like a lot simpler. Combine this with some of the SharePoint features, like Access Web Apps, I can give users access to the static data so they can keep it up-to-date without the manual process without fear of them wrecking it thanks to some (hopefully) clever business logic.

One last comment about the session, Penny completed the entire thing, including the live demos, using her Office 365\Azure subscription. I was amazed at this as normally I find Office 365\Azure professionals always have a “but” when it comes to them being fully cloud, they always spin up a virtual machine or such. Despite being fully cloud, she knew all the little “this isn’t on the on-prem version – yet” or “you can’t do that in the cloud” or “if you don’t see that option on on-prem you need to..”, as well as latest low down on SharePoint 2016.

Other session going on was A Day with Columnstore Indexes with Niko Neugebauer. To be honest, I wimped out of going to this one – I tend to leave the intimate details of indexes to our Senior DBA and just pick up the summary sheet at end and apply the base config and only update it when he says so. Lazy I know, but I can’t learn everything at once!

Day 2

The second day was with Rasmus Reinholdt Nielsen – Building tomorrows ETL architecture with BIML and MDS today and was the main reason for me attending, I spend most of my time in SSIS. Despite packing two laptops, I still found myself popping out to the shops to pick up a USB mouse as I forgot to pack one. I’ve previously heard about BIML and despite being recommended from a lot of folks I just hadn’t had a proper look, the main reason was the resentment towards Microsoft for not providing such a intermediate language – not that Scott Currie hasn’t done amazing job, its just it should be merged into core product. Rasmus started with a introduction to C# which covered the basics, which he quickly skipped through as we all ready had a working knowledge of C# and got onto BIML. Before too long I had created my first basic BIML script. He then went onto some of the more complex features of SSIS and then how to achieve the same in a BIML script. We then moved onto how we use some BIML features and those C# skills to auto generate a SSIS package, then using Master Data Services (MDS) as your meta library to, In short, automated staging.

Mean while in the other session, they were not only failing over servers…

Day 3

The actual SQLSaturday event was hosted at the very nice The Møller Centre, unfortunately this isn’t as close to the train station as the Cambridge City Hotel the precons were hosted at – still it does have free parking.

The day was amazing, the people were are friendly – I managed to go up to a few and ask a few questions which they kindly answered. They even had people handing out free chocolates 🙂

Being a free event, you expect the sponsors to be spamming you with marketing material – it just didn’t happen, they were all playing it cool and waiting for you to come to them. I did pop over to the Dell Spotlight stand who gave me a demo of some of the cool new features in Spotlight – something we already own but hadn’t had a chance to upgrade – needless to say I’ve been poking our DBAs to upgrade since I got back in the office. I also stopped by the Profisee stand, after Rasmus training I was starting to look more into Master Data Services (MDS) and the word on the street was Profisee was the MDS experts. Even after registering for their free online training, I haven’t had a single sales call.

The sessions were good – there was a nice range this was partly because they had joined up with the SharePoint group. For me personally this was very good as BI sits in the middle. The biggest problem was which one to pick! I managed to pick up a few tips from Chris Webb with my data modelling and MDX which was one of my big ones on my to-do list. I enjoyed Terry McCann session on Reporting on Reporting services, its one of those problem we all have – we have these reports, but are they actually being used? And he explained his journey on how he’s tried to answered that and some of the problems he came across. Its good that he’s shared his experience, its not a particular sexy problem to solve, in fact its a very unsexy, unrewarding, boring chore that all report writers must do and at least now I have a good head start.

Overall I think it was a very worthwhile event. I learnt a lot, my work got some very cheap training, I met a lot of very clever people and had fun doing it all. Can’t wait till the next one 🙂

You can see Rodney Kidd photos on Flickr

DSC_0100_DxO

 

Change text direction – Visual Studio 2013 / SQL Server 2014

I’ve spent a while today trying to change the text direction (orientation) of a header on a SSRS report – the option was rather oddly placed in Visual Studio – the option is within the properties (for the text) Localization > Writing Mode > then change from Default to either Rotate270 or Vertical. I was hoping it would be within the Font options. Guess that would be too easy 😉

Missing Reporting data toolbox in Visual Studio 2013 / SQL Server 2014

Todays problem, missing the reporting data toolbox in Visual Studio 2013 – once again, a different menu options. Open the actual report (.rdl)

View > Report Data

Alternatively press Ctrl + Alt + D

Missing SSIS toolbox in Visual Studio 2013 / SQL Server 2014

Stupidly today, I accidently closed the SSIS toolbox. After a bit of unsuccessful searching in the Visual Studio menus, I gave up and done the IT norm and Google it – hoping I wouldn’t have to reinstall.

Luckily I found James Serra had already blogged about it, the only difference is Microsoft has changed the icon, its still in the same place at least

ssistoolbox

So when is a Visual Studio Toolbox, not a Toolbox? When it’s a SSIS Toolbox.

SSIS jumping in Visual Studio 2013 / SQL Server 2014

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.

Documentation

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.

Over time a small, simple change and evolve into a major change and the documentation can significantly drift out-of-sync. Documentation is an important part of the communication between developer, analysis and the end-user, without it misunderstanding and confusing can occur and errors and mistakes happen.

Whilst investigating how other are documentation work I rediscovered the much unloved extended property MS_Description. As you might have guess, using the description field to enter, yes, you guessed it, a description of the tables, views, stored procedure and other SQL objects. Speaking of naming – it’s also important to name things correctly. A table named dbo.Table1 helps no-one. I tend to name any temporary tables MS_TMP – this then quickly identities me as the owner and the fact it can most likely be destroyed. Any dimensions are prefix with Dim and facts are, yes, you’ve guessed it again – Fact.

I have a TSQL script that pulls this data into a table on the staging database as part of a SSIS package, this is configured a SQL Job, however it is only execute post-deployment – we use TeamCity to build the project and then OctopusDeploy for deploying to the various environments.

As we move towards using Analysis Services, I needed a way to document the cubes, luckily Alex Whittles from Purple Frog has already come up with a solution using the Dynamic Management Views (DMVs) and blogged about it – http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/.

I have however modified it, I’ve followed the standard ETL process, so I’ve extracted the data from the DMVs into a staging data, like SQL objects, this is only executed post-deployment by a SQL job that is executed by OctopusDeploy task. I didn’t like the idea of dynamically querying the data every time and as we use continue integration\deployment I don’t need to worry about the data being out of data.

All I have left to really look at is documenting SSIS. Speaking of SSIS, I found Jamie Thomson post on naming conventions very useful.

Small world

So I’ve been following a few folks on Twitter – one of them is Tim Mitchell from the US, he tweeted a link to some resources he used in a recent SQL Saturday he presented at.

One of them is Konesans.com. Who happen to be based in Holbeach, which is really close to my home town of Wisbech. You go all over the world in search of knowledge, only to find there’s a guru right on your own doorstep.

Failed RavenDB backup

I had a rather odd error with OctopusDeploy where the RavenDB backups were failing. Turns out the problem was because I had assigned 1 IPv4 address to OctopusDeploy and another IPv4 address for TeamCity. Oddly the RavenDB backup routine referenced the localhost address – 127.0.0.1, it appeared to be hardcode with no option other then changing port. This was especially weird as the OctopusDeploy service, was able to access it fine. To resolve – at least long enough to get a backup to migrate to v3 which uses SQL Server – was to setup a port proxy so accessing TCP port 10931 on the local loopback would resolve to the same port but on a the actual public IPv4

To do this run Command Prompt as Administrator (right-click, Run As Administrator) then type

netsh

interface portproxy

add v4tov4 listenaddress=127.0.0.1 listenport=10931 connectaddress={{your ip}} connectport=10931

Remember to replace {{your ip}} with the actual IP address

Moved backup locations

It appears IBM has moved the Cognos backup folder location from:

C:\Program Files\ibm\cognos\c10_64\bkp

To:

C:\Program Files\ibm\cognos\c10_64\uninstall

Swollen Business Objects database

Our new HR system uses SAP Business Objects for transactional reporting, as this was setup by our HR supplier we haven’t really got involved with it beyond writing a few custom reports, turns out we’ve written quite a few, so much so that the Business Objects CMS database has swollen to over 30 GBs. This, as you might have guessed, is causing a few performance problems. After a discussion with the supplier, we found out it was storing all the reports, even the ad-hoc one-off reports and we should have set a parameter to limit it.

After limiting it, the database still didn’t reduce the database size. A quick Google found someone saying that it is because it can’t handle over 1 million rows of old reports, which makes sense, running a SQL command that would delete over 30gb of data would have insane log file grow as well as massive performance problems.

The steps to resolve this, on MS-SQL, was to:

  1. Stop the Business Objects services on the app tier
  2. Ensure you have a backup of the database
  3. Set the recovery model to simple if it isn’t already
  4. Copy out all the required rows

  5. Truncate the table

  6. Re-insert the required rows back into the table

  7. Check everything looks ok, deal with the fact the database is now tiny

  8. Create another backup

  9. Start the app tier backup

New Azure features

Two new Azure features have caught my eye – the first is Logic App. In a nutshell this is workflow. You have connectors and you can add triggers and actions. So you could have a trigger on your HR system that when a new employee starts, it creates a user account then sends a welcome message to the existing staff informing them of the new starters.

The other is Data Catalog, this basically allows you to generate meta data about your data sources. Effectively this becomes your documentation, your point of reference when it comes data. This looks to solve a very real problem large enterprises have. Too much data, silo away.

Parking fines issued in Ipswich

I made the above video using Microsoft Excel using data from a FOI request to Ipswich Borough Council about the number of Parking fines issued since 2010.

Toshiba laptop update

Just an update to my previous post about the Toshiba Laptop, I’ve been thinking about reinstalling the Toshiba laptop with Ubuntu, but before I do that, I want a backup. Luckily, I still have the original 500GB hdd spare, I just need a USB caddy. Luckily my local Curry’s had a USB3.0 one (PNY SSD and 2.5″ Hard Drive Enclosure and Upgrade Kit), before anyone comments about how I could of got one cheaper off eBay, just remember, I was able to get it same day and it included Acronis.

Before I swapped the HDD out for a SSD, I ran a simple PowerShell script to get a list of installed applications, you can see the list here.

Desktop

Desktop
IE
Internet Explorer (Homepage)
hdd
HDD Warning

One of the problems with traditional HDD is the fact they have moving parts, if your moving your laptop around and you bang it, it can get damage, Toshiba have implemented a safely feature where when movement is detected it moved the HDD file header to a safe position. It make sound a little bit extreme, but when your talking about a disc that spins at over 4000 RPM and is wafer thin, you kinda get why they’ve add the feature. Personally, SSD is a better fix. No moving parts 🙂

Technical debt

I was talking to a colleague about one of our suppliers and the progress they were making on a new product they were developing. He was surprised to hear how little progress they appear to have made, they had previously been making huge steps in very little time with very few developers, especially surprising when they have been scaling up the development team over the past few months. I explained they are burning some of the technical debt they have collected whilst they still can. This of course then led to me explaining what I meant by using a common product, which then later prompted this tweet (which irony Capita hit the favourite button on)

For secondary schools who use SIMS .net – which is most schools in the UK – this can be simply be explained by Nova-T. Nova-T6 is a perfect example. Technically, it should be written in C#/.NET, however, there is a no business case, so it’s still a Delphi program. From a end-user perspective if they re-wrote it, they would gain nothing. Personally however, this still doesn’t mean it should be ruled out. It’s easier to maintain C# code when you have a small army of C# developers vs only a hand full of aging Delphi coders. Not to mention the advantage of C# over Delphi. The longer Capita leaves it, the more the interest costs them. Just look at some of the technical problems SIMS customers have had, it can all be tracked back to that technical debt. That debt that Capita needs to pay before the interest becomes to high.

Toshiba R50-B-12Q

UPDATE: I’ve detailed the Toshiba default build a bit here

At the beginning of the month I order a new laptop, for the past few weeks I’ve been wanting something more mobile then my desktop, at first I was toying with the idea of selling my desktop and picking up a new Surface. The new Surface 3 which starts at £419 seemed like a good idea what with its sexy full hd touchscreen, however when you scratch the surface (ha ha) it doesn’t look so go

  • Atom processor
  • 2GB RAM
  • 64GB storage

Not really something beefy enough to be a main computer, add the fact my Blu-ray player is SATA and currently housed in my desktop means I’d need to buy either a caddy or get a whole new external drive.  So I’m now looking at £700 for a half decent laptop, to get something to match my desktop, I’m looking at around £1200. So as you might have guessed. I ruled that out.

I end up looking at cheap laptops, ideally I was after something that was upgradable – I had a spare solid state drive and I wanted to be able to upgrade the RAM later if needed. Ideally I would like a Full HD (1920 x 1280) screen, unfortunately no-one seems to pack a nice screen on a cheap laptop, unless you want a Chromebook like Toshiba Chromebook 2. In the end it came down to the Lenovo ThinkPad E555 and the Toshiba Satellite Pro R50-B-12Q. In the end I let cost decide, the Lenovo was £50 more expensive (after the £50 cash back), so I went for the Toshiba. The Toshiba was just under £200 from Dabs. I also ordered a 4GB stick of RAM (Crucial part code: CT51264BF160BJ, Dabs quick: 8PQFWS00).

WP_20150509_002

The laptop came with a Windows 8.1 Pro license, which is about £100 for a OEM license. It comes with Windows 7 Pro installed and pretty clean build. There was a annoying bits, like a unregistered evaluation copy of WinZip and some annoying favourite websites but nothing as bad as what Lenovo has been installing by default. All the drivers appears to have been loaded via Windows Updates, I then created a installable USB pen drive as there is a no DVD drive on the laptop. You can download the Windows 8.1 installation media direct from Microsoft for free. Once you’ve download it, it will turn a USB pen drive into installation media.

Opening the laptop was simple, they are standard cross screws. This particular model doesn’t have a optical drive.

WP_20150510_001

I’ve removed the hdd, I could buy a caddy to use the empty optical drive space, but I’m not a fan of the old traditional spindle hdd, so it had to go, plus I might add a optical drive later. Its nice to had options.

WP_20150510_002

The hdd wasn’t actually screwed in place. Its just wedged in, I guess they call this shock absorbing technology. Seems to work pretty well.

One thing I will say is to be careful re-assembling it. I stupidly over tighten the screws and now have a few bumps under the keyboard.

Version Control

I’ve been thinking lately about the adoption of version control. One of the common fears of using version control is that its permanent. All mistakes are visible and are in fact, highlight. Originals are updated with new commits which detail only the change and a nice comment. There is no hiding mistakes. The thing is

Mistakes are what make us human.

Everyone makes mistakes, we need to accept that fact. In doing so we can move forward faster.

Keyboard mapping [SQL 2014]

I’ve been working on SQL Server 2014 lately and one of the annoying problems I’ve stumbled across using SSMS is that F5 no longer means execute the SQL query, it now means Debug. Which brings up an annoying popup (least for me) about configuring my Windows Firewall.

This can be changed in SSMS by going Tools > Customize… > Keyboard…

The default is:

Debug.Start (F5 (Global))

And the traditional is:

View.Refresh

You can also do it by setting it to Visual Studio 2010 Compatible

Local Government to go Academy

It appears the Local Government is looking to follow in schools footsteps and do an “Academy” – cutting the red tape and be given more control and freedom.

4th Anniversary of contributing to Rosetta@Home

Tomorrow, Sunday the 7th December 2014, I will be celebrating 4 years of contributing wasted CPU cycles to the excellent Rosetta@Home project.

rosetta_at_home_logo

Rosetta@home project is determining the 3-dimensional shapes of proteins through research, that may ultimately lead to finding cures for some major human diseases such as HIV, Malaria, Cancer, and Alzheimer’s. The project is lead by Dr David Baker at the Univerity of Washington.

You can find out more about the Rosetta@Home project on their website – http://boinc.bakerlab.org/.

Not only will this be my 4th year contributing, I have also surpased the

1,000,000

certificat

credits mark. I think in part this is because of my new i5 CPU I got for my birthday this year.

 

Technical Analysis on Schools websites in England

So in my last blog post I asked, are you ready for IPv6? The post came about when I was looking at Schools MIS data, which Graham, Joshua and myself have being look at to see who are the big movers and shakers in the Schools administration software (MIS) arena. Data is collected by what software suppliers a school uses to submit the School Census (in England) which is requested under the Freedom of Information (FOI) from Department of Education(DfE) (saving having to FOI every individual school). In order to enhance this data I was joining the data onto the general schools data that can be extract from EduBase. Looking at the data I notice that the website addresses listed in the extract was of extremely poor quality. A number even had email addresses listed!

This got me thinking, are schools ready for IPv6? If Sky are running out of IPv4 addresses and offer IPv6 only connections at a lower price, how many parents are going to jump on the deal only to find out they can’t access their child’s school website later on. After a bit of scripting and a support call to Mythic-Beasts to enable a response in JSON that I could automate, I had the results. It wasn’t good.

Still, no-ones ready for IPv6 are they, sure they’ll be ready in time. Won’t they?

We can only judge the future from what we have suffered in the past

Themistocles , 300: Rise of an Empire

To this effect, I’ve gathered data to look at:

  • šDomain registration correctness
  • šContent management systems
  • šDocument type definition
  • šRaw HTML homepage size
  • šGoogle Analytics
  • šIPv6 readiness

At the moment I’m still creating the presentation detailing my findings, but you can download the raw data from: https://github.com/matt40k/SchoolsWebsites-England

Are you ready for IPv6?

For a long while now it has been known that IPv4 will run out of available publicly assignable IP addresses. IPv4 address are 32 bits wide and quite simply can’t cope with the demand of the modern world – despite NAT’ing. IPv6 looks to resolve this by have not only more digits, but also using hexadecimal.

For example, an IPv4 address looks like this

192 . 168 . 0 . 1

4 parts each running from 0 – 255.

Now lets look at Facebook IPv6 record:

2a03 : 2880 : 2110 : df07 : face : b00c : 0 : 1

Now that’s 8 blocks, each one being 4 hexadecimals – so that’s 0 – 9 then a – f (so 16), so that’s 16 x 16 x 16 x 16 – so 65,536 in a block vs IPv4 255, and there are 8 blocks… that’s 128 bits, starting to see how massive the IPv6 range is?

Because of the massive IP range, you in effect get to bypass the IPv4 tax – in fact Mythic-Beasts has already started offering IPv6 only servers without the IPv4 tax. That’s not the only thing, NAT becomes obsolete which means gaming on the XBOX One becomes faster as you don’t have to setup port forwarding and put extra load on your router – also new Microsoft devices (Windows PCs and XBOX One) have a preference for IPv6 over IPv4, this could be because IPv6 has cleaner routing? IPv6 has been designed for the future, security has been account for and isn’t just tacked on.

When is IPv6 coming out? Well, it’s actually out, and it’s been out for a long while. RIPE, who are responsible for issuing IP addresses (both IPv4 and IPv6) in Europe has been doing it since 1999 and World IPv6 Day was back in 2011.

So, are you IPv6 ready?

You can check if your ISP has setup IPv6 so you can access those IPv6 servers from your computer by going to: http://test-ipv6.com/

And you can check if your website is IPv6 ready by going to: https://www.mythic-beasts.com/ipv6/health-check

I’m guessing the answer is no.

Thankfully, you’re not alone, but it’s something to think about, especially as all the major ISP in the UK are gearing up – http://blog.mythic-beasts.com/2014/10/22/ipv6-support-in-the-uk/ and http://www.ipv6.org.uk/2014/11/20/ipv6-council-meeting-oct2014/

Slow progress

I’m currently getting bogged down with other projects and unfortunately my final sprint of SIMS Bulk Import has grind to a halt. On the plus side I managed to swash a few more bugs last month, get a code signing certificate. This will mean all future releases will be signed as coming from me which is great news, it adds a layer of confidence that the code you run is unaltered by a third party. I’ve also started reviewing my other projects, ensure that the code on the public repository is up-to-date, I’ve also switched over to Git and copied them all over to GitHub. At bit more on that later.

Part of the updated process was to use a Continuous Integration server – specifically TeamCity. Simply put, these allowed the builds to occur on a dedicated box which made the whole process quicker and easier – it also added confidence as it ensured that everything required was commit in the repository. The only downside to this is the cost – although renting a VPS from OVH is cheap, it still isn’t cost effective due to the limited amount of time I actually use it, I’m therefore looking at moving it to Azure as you only pay for provisioned resources and your can de-provision servers and only pay for the storage. Alas this means more messing about setting up servers.

TeamCity AssemblyInfo Patcher not working

Tonight I was trying to get TeamCity to auto-update the version number for my SIMS Bulk Import application, however the simple AssemblyInfo Patcher just failed to work. No errors. Nothing.

Then I read the manual, again…

Note that this feature will work only for “standard” projects, i.e. created by means of the Visual Studio wizard, so that all the AssemblyInfo files and content have a standard location.

So I created a new package which placed the AssemblyInfo.cs into the Properties folder, mine was in the main directory, moved it into the subfolder and bang, it works. Awesome.

 

WIX installer why you no MSBUILD no more?

Last night I chopped up my WIX installer project – I basically split the actual installer parts into one file – CoreMsi.wxs, the versions into a variable file – Version.wxi and the file list into a separate fragment file – SimsBulkImport.wxs. This worked.

Great I thought, simple PowerShell script to generate the file list, I can even grab the main repository complied bin as a artifact dependency – got to love TeamCity.

Problem was, it fails to build.

error CNDL0104: Not a valid source file; detail: ‘.’, hexadecimal value 0x00, is an invalid character. Line 2, position 1.

bit of trial and error later I figured it out. It was my old friend Mr Encoding. After adding

-Encoding “UTF8” 

to the end of the Out-File and it builds again.

SIMS Bulk Import

Overview

If your in the UK, the chances are your local school is using SIMS .net. Just checkout the stats on their site. 22,000 schools taking 2,500,000 children’s attendance, every day. That’s impressive.

So what is SIMS .net? Well its a MIS system, but what is a MIS system? In the simplest terms its a database that holds the school records, for both students and staff.  So it makes sense that you are going to want to interface with it as it’s your at your core when it comes to data sources.

Now extracting data is pretty straightforward. Capita have created a custom reporting engine that allows simple report creation that can then be scheduled and produce exports of data. The problem is getting data back into SIMS .net. Take for example identity management, it’s easy enough to export a list of students, then write a PowerShell script to generate user accounts, but wouldn’t it be good to get that username added back into your core data source? Or what about adding in new students home email addresses and telephone numbers? Well it isn’t that straightforward to bulk import. Although Capita provide a API it isn’t as straightforward and certainly isn’t as friendly as a RESTful web service and certainly requires a programming background to understand it which rules out many schools being able to use it.

Back in May 2012 I was working on a SIMS support desk doing technical support and one of our customers asked if he could bulk import email addresses back into SIMS .net. This resulted in me asking Capita for documentation regarding the API which they provided, at no cost, along with a few snippets of example code. I then spent the following nights coding away at home to what has become SIMS Bulk Import.

About

So what are SIMS Bulk Import features

  • It’s free – Thank you Phil Neal for waiving the licensing costs 🙂
  • It uses the SIMS .net Business Objects (no large Capita charges for corrupting your SIMS .net database!)
  • Imports from CSV, Excel spreadsheets and XML files
  • Matches the file fields with SIMS .net fields

The future

I’ve since moved away from SIMS support and I’ve been trying to find a suitable new home for SIMS Bulk Import. Whether that’ll be Capita writing a replacement, or someone else taking up the project it isn’t clear. For now I’ll continue to support it. But to as part of this I’ve moved the source over to GitHub, the releases will still be via CodePlex.  I’ve also created a separate organisation on GitHubSIMSBulkImport, which effectively “owns” the code. I fork the code from  SIMSBulkImport into my personal repository, and do a pull request to merge the code back into the main SIMSBulkImport repository. This will help should the “owner” of SIMS Bulk Import change in the future.

I’ve also created a number of sub-projects – the SIMS interface library will be moved from Unfuddle onto GitHub, also the installer element will become a separate repository. I’m looking at setting up TeamCity to automate the build process so building and creating releases is a lot more streamlined and less time consuming. Also a simple web site setup giving easy advise for getting started.

Another change is a web API – this allows checking for newer versions and secure uploading of log files to the developers (OK, me). Mythic-Beasts have kindly donated a server hosted in Cambridge, UK to host this. This written in PHP using the Laravel framework, I’ve started writing it based on some of Bobby Allen original code.

Going forward I would like some (official) recommendation from schools and support teams, ideally I’d like to put together some sort of list or program detailing where you can get support from, for example:

Your Local SIMS Team supports SIMS Bulk Import!

Your Local SIMS Team is a SIMS Bulk Import silver partner!

It’s just difficult making a business case to people when your product is free. The main reason I want to do this is I’m find the odd problem where someone need to remote in, this is difficult without some sort of support agreement and T and Cs – most of the problems are actual SIMS problems like the SIMS client hasn’t be installed correctly which their SIMS support team could resolve. I appreciate working in the dark isn’t easy and you can’t know about every SIMS third party product, so your going to get a element of from pillar to post. Ideally I need a main backer to act as data controller with regards to the log files, at the moment I’m investigating if I could get around it by limiting the data it could spit out but that’s going to limit the usefulness of the logs. It’ll also been good to get the program digitally signed.

If anyone is willing to help out feel free to drop me a email – matt [at] matt40k [dot] uk

Living in a IPv6 world

I’ve finally gotten round to setting up the VDS Mythic-Beasts have kindly donated to host the API backend for one of my open-source projects. A few nights ago I enabled IPv6 and today when I went to run apt-get update it just hung saying:

0% [Connecting to gb.archive.ubuntu.com (2a01:450:10:1::10)]

As they had previously worked, I suspect it’s IPv6 problem, quick google and…

http://askubuntu.com/questions/272796/connecting-to-archive-ubuntu-com-takes-too-long

I solved this on 12.10 by editing /etc/gai.conf and uncommenting the line:


#
#    For sites which prefer IPv4 connections change the last line to
#
precedence ::ffff:0:0/96 100

Bingo.

BI for IT operations

I always find it funny when IT departments talk about BI for their company and they don’t actually have a BI solution in place for themselves. Surely you should practice what you preach?

So why do IT departments need BI. Well lets look at Rosetta@home.

Jul 30, 2014

We are aware of significant network slow-down between the subnet upon which our servers sit and the Internet beyond the UW campus. We are working with the UW’s Network Operations team to pinpoint the cause. We apologize for the frustration caused. -KEL

Doesn’t sound good does it. Almost like they’ve been hacked or suffering a DDOS.

Aug 05, 2014

Slowdown Update: As it turns out, the slow-down experienced during the last week of July was the result of a very large surge in users joining the project through a new campaign by http://charityengine.com. As the servers were behaving properly – just overwhelmed – and DK was on a well-deserved holiday, it was difficult for the rest of us to pin-point the cause. You can see the recent surge here.
We are looking at changing/expanding our webserver frontend to be more resilient to surges like this in the future. Yet again, we apologize for the frustration 
caused. -KEL

6 days later they twig. Using the third party stats site –BoincStats.com they get a bit of visual analysis. They’ve seen massive new user growth. Having a cloud based solution is only half the solution, knowing when to scale up and when to scale down is the other half of the problem.

PowerShell, DigitalOcean and WordPress

Back in May (2014) I decided to start up a blog, again. I decided to keep it simple and just get a off the shelf hosting package rather then going to extremes like I normally do – configuring a VPS or even a Dedi server! I ended up going with CS New Media – it was just their lite Linux package which I signed up for 3 months. I was tempted to stick with what I know and go with Mythic-Beasts like I normally but I like to tinker and try new things so I went with CS New Media after a failed attempt to spend my dosh with NameCheap – when it came to entering my card details, it just failed to connect to “their” backend and their response was, well it works for me, try another browser – by the time I phoned them I had already tried different browsers, internet connections, computers so at that point I just walked away.

Anyway fast forward two months and I was getting ichy to move away from CS New Media – nothing wrong with them, a bit of downtime, but nothing major and it was all stupid-o-clock, performance was ok, nothing special and support – well I never used it so, can’t comment. Using Plesk was a blast from the past – that was the first web control panel I ever used, so it was nice to see how it’s improved.

Problem was, where do I go, well Mythic-Beasts had fitted SSDs into their web servers since I’ve hosted with them, tempting. Then DigitalOcean did it. They announced a new data centre. In London. With IPv6. And they have that sexy API. Ooo and I have credit with them for some reason or other, happy days. I decided to take it slow and script the whole process – I’ve been playing with PowerShell lately, so I created a PowerShell script that creates the DNS records, builds the droplet. I’ve even managed to get it to convert the Putty public SSH key into OpenSSH format so DigitalOcean will accept it and I can use SSH keys to connect to the server. I was planning on scripting the build process of installing the LEMP stack – but I lost interest to be honest and enough already exist.

Net result, it’s got my PowerShell knowledge up – all the codes in my GitHub repository. One of the off-shot projects of the project was a PowerShell function to generate secure passwords, the idea was my DigitalOcean PowerShell script would generate passwords for your newly created droplet – MySQL root password for example, store it as say CSV on your local machine and only pass the password as an argument to the stack install script over SSH – so it wouldn’t get stored on the remote server discs.

SQLCambs

Well my day is finally coming to an end, my bed is calling me after a long day. If I had thought about it more I would have packed a few cans of Pepsi Max and probability took a half day at work, as it was I spent my lunch break by coming home and taking my son to the park*, then heading off to Cambridge after work for mine and my colleagues first Cambridgeshire SQL Server User Group. I thought I’d write a blog post about the event whilst everything is still fresh in my head and my dinner is still going down.

First up, the venue, Redgate’s office in the Cambridge Business Park (also the same business park that Mythic-Beasts has a data centre in) was fantastic. You really did get the feeling they practice what they preach and they really have embraced the agile culture. It really is visible in their environment – I have to admit the floor to ceiling room divider whiteboard did (sadly) give me a bit of envy and I was thinking if I could get away with turning the cupboards at work into a giant whiteboard.

The Cambridgeshire SQL Server User Group is ran by Mark Broadbent who I assume is doing these events as part of some Microsoft-y requirement of mentoring. Whatever his reasons, it’s great his doing it. This especially rang true in the second half of the event when Alex from Redgate started taking about the importance of Redgate database version control software. To Alex credit, he did do a good job showing Redgate products whilst at the same time not feeling like we just entered a sales pitch meeting and we’re going spend the next 6 months getting spammed with sales calls. I really appreciate when people put these types of events together, the low pressure, honest events where you can see the product but then not have your phone ring every 5 mins asking when the orders going to arrive. It also gives a chance to see how other people in different areas are having the same problems as well as get some honest feedback about products and companies from real end users – ie not managers who will spend 20 mins talking about how great it is despite never using or having any real knowledge about it.

The first session was by Dave Ballantyne on statistics, a subject, if I’m being honest, doesn’t particularly feel me with much excitement. My colleague on the other hand, they are the difference between a quiet day or a day with a number of managers hovering over your desk ask why everything is so slow. Despite my lack of excitement and my colleague previous  attempts to explain it to me, I did feel I left, after a 1 hr session I might add, having a better understanding. I should at least have a clue why I’m having problems later on and where to start looking. Don’t get me wrong, I’m not going to go looking at it until it happens!

The second and final session was by Alex Yates who works at Redgate on “Building an automated database deployment pipeline”. In short, automating how you get the new stuff into production. One of my bug bears is how we deploy. It’s manual. I won’t go into detail, I’ll leave that for another post, this was basically the sales pitch of the day which I suspect might of been the cost of the free venue… food… drinks… books…. lanyards. Still the message was a important one and it needs to be heard. DevOps is the future. And Redgate is your friendly company who will help you get there.

So, would I go again? Yes. Would I recommend it? Totally. Amazing what can come from putting “cambridgeshire” and “sql” into twitter’s search box will lead you too 🙂

 

  • I main issue I have with evening events is childcare, my girlfriend works evenings so I normally playing Lego whilst these sorts of events go on,  luckily (well actually unluckily) she broke her ankle and is signed off work for 6 weeks with her leg in a cast – so I now have childcare in the evenings for the next, well now 5, weeks. I just had to get my son out the house which involved a late long lunch and a trip to the park.

The term ‘Invoke-WebRequest’ is not recognized

I’ve currently been working on a PowerShell script to build a virtual server using DigitalOcean excellent RESTful API. So far this has gone extremely well and I have been impressed with both PowerShell and DigitalOcean’s API. Today however I tried running on another machine (an important part of development) only to find it doesn’t work. The first error that appears is:

The term ‘Invoke-WebRequest’ is not recognized as the name of a cmdlet, function, script file, or operable progr

am. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

At line:1 char:18

  • Invoke-WebRequest <<<<

  • CategoryInfo          : ObjectNotFound: (Invoke-WebRequest:String) [], CommandNotFoundException

  • FullyQualifiedErrorId : CommandNotFoundException

In simple terms it’s saying it can’t find the ‘Invoke-WebRequest’ cmdlet. This should be a built-in cmdlet in PowerShell, so off I went to TechNet to find the details of the cmdlet – 

http://technet.microsoft.com/en-us/library/hh849901.aspx

The key bit of information was this line:

This cmdlet was introduced in Windows PowerShell 3.0.

The other way is at the top of such documents on TechNet it defaults to the most recent version of the, in this case, cmdlet and next to it gives you the option select a older version, clicking it reveals previous versions and does indeed confirm the above statement that it was not present in any version of PowerShell prior to 3.0.

TechNet

TechNet

Next question, is PowerShell 4.0 installed? Well for Windows 8.1 and Windows Server 2012 R2 it’s built-in, you can check by running (in PowerShell)

$PSVersionTable.PSVersion

sure enough, the other machine (which is Windows 7 Enterprise x64) returns

Major  Minor  Build  Revision

—–  —–  —–  ——–

2      0      -1     -1

For help installing PowerShell 4.0 go to: http://social.technet.microsoft.com/wiki/contents/articles/21016.how-to-install-windows-powershell-4-0.aspx

 

 

 

Clear Visual Studio recent projects

Another little gem, clearing out those pesky recent projects from your menu on Visual Studio 2012. Fire up RegEdit and navigate to:

  • Files: HKCU\SOFTWARE\Microsoft\Visual Studio\11.0\FileMRUList
  • Proj: HKCU\SOFTWARE\Microsoft\Visual Studio\11.0\ProjectMRUList

Usual disclaimer

http://nathondalton.wordpress.com/2011/10/13/clear-visual-studio-recent-project-and-files/

Tron

Found this little gem – appears they actually made those really cool interfaces they used in Tron Legacy: http://www.robscanlon.com/encom-boardroom/

Created new user is disabled

I’ve created a new SQL user along with a new role in our warehouse db project using Visual Studio 2012.

CREATE LOGIN [newuser] WITHPASSWORD=‘{{ RANDOM PASSWORD }}’,DEFAULT_DATABASE=[Database]

GO

CREATE USER [newuser] FORLOGIN [newuser] WITHDEFAULT_SCHEMA=[dbo]

GO

ALTER ROLE [newrole] ADD MEMBER [newuser]

GO

 

Problem is, the user is disabled. When you do it manually via SQL Management Studio, it’s enabled and working. Manually enabling the account doesn’t allow it connect either. A quick Google revealed:

http://www.sqlhammer.com/blog/creating-logins-and-users-why-i-cant-connect/

Bingo. Appears I need to add the following to the end of the script:

 GRANT CONNECT TO [newuser]

Why Microsoft Business Intelligence is on my radar

In my day job I’ve been looking at SQL Server 2012 recently (I know SQL Server 2014 out before you say anything!), I’ve had a developer license for a while but unfortunately other priorities have been consuming my time and I’ve only managed to get a dev server build, SQL 2012 installed and not much else up till a few days ago.

Our current Business Intelligence platform is using SQL Server 2008 (not R2) for the database backend and SQL Server Integration Services (SSIS) BIDS for the ETL. We use IBM Cognos Business Intelligence for the presentation layer, which we upgraded to 10.2.1 at the being of the year from 8.4.1, after a massive sprint with a mix of in house and a third party company. In total we created over 100 reports in about 6 months between us! Half of them being created by us in house staff – which was 2 full-time report writers (me being one) and the odd bit of time from the 2 report writers when it got a bit hairy. It was nice upgrading to 10.2.1, other then waiting for Fix Pack 2 to resolve the Tomcat memory leak, as allowed me to sink my teeth into the technical side which is my background – techy. Beyond the techy side of it it was a big team effort to get some of the reports re-written as the method we used to do the drill-downhierarchy was “fixed” in 10.2.1 so it stopped working and after battling for days with ancestors we just re-did it. It did give us really good in-depth knowledge of the reports the third party had written which has helped us supporting them going forward and also helped us improve our techniques and knowledge – we’re always re-factoring our work to bring about improvements!

A few weeks ago, we had to novate our Cognos license, turns out, at the last minute I might add, we couldn’t do it. IBM wouldn’t allow it. So we had to relicense. IBM had just hit our list of suppliers we don’t like. Bad news, big bill, good news, all our licenses now had Query Studio licenses – ie they can run ad-hoc queries. Bad news, our models are really designed for our static reports and our staff aren’t all trained in Query Studio. Starting to see why we’re looking at other BI tools?

So, I basically started looking at Microsoft BI because…

  • I had a developer license for SQL 2012 – cost, less then £20, which allowed me to build and demo anything I created. IE I’m not spend thousands on a tool that I maybe able to use without expensive training
  • Our data warehouse is SQL Server
  • We had just signed a Microsoft Enterprise Agreement
  • Our staff know Excel – everything ends up in Excel data wise

 

 

How not to write a newsletter

I received NameCheap newsletter today full of annoying holes (at least for me), needless to say I hit the unsubscribe button shortly after.

namecheap1

 

First mistake – mark your calendars. Did they not check their calendar before sending it? The June 10 was 2 weeks ago!

$1, one dollar!! You’re trying to promote .UK, use the local currency, using £ (GBP)!

 

namecheap2

 

Final mistake I notice (I didn’t look very hard to be honest) was the “SCHOOLSOUT” code. Sorry, that’s not till next month. Perhaps someone should buy NameCheap a calendar.

SOLUS2, will you not just die already!

Well it appears Capita has announced SOLUS2 death day

 

s2_end_twitter

 

That’s assuming they don’t put it back. Again.

This is bad news. Let me explain why.

I’m using Chrome, it’s updated itself I don’t know how many times. If I go and check, all I know or care about is that it is indeed up-to-date, so at some point, it’s updated itself and its worked.

If I look at my antivirus I can see it’s updated 3 times today alone. Again, this is all in the background without me doing anything.

Now compare this with SIMS and SOLUS2. I wonder how many hours I’ve wasted supporting schools doing SOLUS2 upgrades in my past life. I dread to think.

Before you start waiving the MSI flag, let stop and have a look at the outstanding CR logged on SupportNet today (24/06/2014) for the past year. Extract here.

1,057

1,057,outstanding change request. All requested by end-users. All with positive votes.

1,057. Wow. I dread to think how many have gone unlogged or is older then a year!!

Capita clearly can’t roll them up into a major build. How many re-releases have you seen? Add the fact it doesn’t even seem to be making a dent in CR mountain and you can see why Capita wants to be Agile like the other MIS suppliers.

Not sure I fancy pushing out 1,057 updates… per school…

I’m sorry, but SIMS8 can’t come fast enough

I haven’t really been on SupportNet, Capita SIMS support portal, regularly since changing roles about 20 months ago, the only time I pop on nowadays is to find something when I’m helping out my old team during busyhard times (you never really escape at my work). On this occasion I stopped by the forum and one thing jumped out at me. The lack of progress. Now I appreciate I’m about to unload both barrels into my foot, but I think it needs to be said.

The same issues exist today that exists when I was dealing with SIMS .net on daily bases over 20 months ago. That’s nearly 2 years. There are still people who aren’t using SOLUS3 – you know that free tool Capita created to ease the woes of upgrades and deployments. Despite a large number of users already using it and all the major bugs being resolve. More worrying, Capita has even issued SOLUS2 with it’s marching orders. It’s not a question of if, but when. The last release will be Autumn 2014 unless they are forced to push it back. Again.

I don’t think pushing it back is the answer. Pushing the date back is like putting a plaster over a gunshot wound. OK, it might help stop the bleeding, but it’s not the answer. Now I accept my approach of nagging my boss until he allowed me get on top of SOLUS3 whilst Capita was throwing major development time at it so I can make sure it works for our schools might not be possible for everyone. In fact it’s impossible, there aren’t enough developers to go around. But something has epically failed. Why, why, why hasn’t someone gone. Right, this needs to happen. It’s not going to go away. We have these problems:

  • SIMS Discover needs SOLUS3 – Sorry, you don’t install SIMS manually why? So why is installing Discover manually an option?
  • SIMS .net needs MORE access rights to installupgrade – switching UAC off is just plain stupid
  • Upgrades are just a pain

We need to resolve. Lets get SOLUS3 setup.

Now if your one of the support teams I’m attacking, before you start defending yourselves. Step back. Being busy isn’t an valid excuse. If you weren’t busy, you’ll be at the job centre. Someone, either yourself or your boss should be reviewing whats happening. Regularly. If your trying to do workarounds, you’re never going to have time to implement a fix. See what your doing is a putting a plaster on a gunshot wound and stop, get to the hospital. I know smaller support teams this will be a lot harder, especially when schools are doing their own thing, but I think this actually makes it easier.

SOLUS3 is a bit of a different beast. The way I explain SOLUS3 to new IT staff is, SOLUS3 is your tool. It’s a IT technician’s tool. This tool will manage SIMS .net across your network – it also keeps SIMS .net at a safe distance so you don’t enter the mists of MIS land. At the same time it keeps the MIS manager away from any sort of Windows permissions – you’re not giving any non-IT staff administrator access to YOUR network. System Manager is the DataMIS managers tool, it keeps people out of THEIR database, including IT staff. I’ve found this has been really useful with new IT staff coming from the “real” world and they have found the clear what you do and don’t do has helped keep them out of trouble. You do get the odd, why can’t I get a SCCM package, but when you point out that would require either Capita forcing schools to buy more Microsoft licenses or them reading more Capita documentation, they tend to go quiet.

Now I think the issue is the MIS support people are trying to push SOLUS3, when it should be the IT support people. If you look at the problems people are having with SOLUS3, they are IT problems, firewall and DNS mainly. Which MIS support staff don’t resolve. IT support staff do. If you’re now saying, but we don’t have any IT support people, it’s all third parties. What would happen if something, non-MIS related was required to be pushed out? What I’d do is organise a third party to support it, get the community together and issue training. So, find someone who has deployed SOLUS3 successfully and get them to do some training sessions for those pesky IT support teams. The cost of even getting even a Capita trainer in at £££, booking a venue could be offset by charging fees to delegates and to be honest, it isn’t that much when you compare it to even 1 day of failed SIMS upgradesdeployments. Costs can be cut by using a central school as the venue, by using a non-Capita trainer, by joining forces with other local support teams or even going completely digital. Support forums like EduGeek are a excellent way for the community to support itself with very little effort. I would also not be fussy by who attends, if they support one of your schools, offer them the training. Don’t get caught up in the fact their going to resell your knowledge. Think bigger picture.

So, do I think their will be much progress? Do I think Capita will stick with their Autumn 2014 is the last SOLUS2 release?

Well I also noticed they still had the following documents on the SupportNet homepage as sticky items:

  • Technical Guide – Network Impact of SIMS
  • Technical Guide – Optimising SIMS .net startup speed
  • Technical Guide – Remote Working with SIMS

Sound pretty useful, till you look at the dates, 06 April 2005, 08 April 2005 and 11th June 2004 respectively.

Take the first one, Network Impact of SIMS – to quote a bit of it:

Unfortunately, minimum hardware specifications are set to rise as we cease support for Windows 98.

Realistically Windows 2000 and Windows XP we would recommend a minimum of 256MB.

I’m sorry, didn’t Microsoft and even Capita just drop support for Windows XP? Why haven’t they updated a document that is important enough to warrant being displayed on their support portal? In over 9 years?!?

Looking at Optimising SIMS .net startup speed, why are worrying about turning the antivirus off certain directories? Aren’t our computers these days powerful enough to handle it? Surely it’ll be a good idea if we’re not using SOLUS3 and giving everyone and their dog access to a protected folder location – Program Files. Why are we worrying about SIMSload, it’s does nothing once the SOLUS3 agent gets installed. Why would anyone compress the drive nowadays?

Remotely accessing SIMS .net is a hot topic – the general concession is to use RemoteApp (Terminal Services). But the “official” Capita response is the Remote Working with SIMS document… which says to use a VPN. I’m not a fan of this idea. FMS, the Capita SIMS finance package, for starts doesn’t work well over a VPN, it’s not a speed issue, I’ve got 50MBs and that’s the lowest package I could get, it’s a latency issue. SQL doesn’t like latency. If you think of that 50MBs as the number of lanes and the latency as the speed what the cars are traveling. SIMS .net isn’t immune either, it just more modern, so it copes better.

I’m sorry, but SIMS8, the next generation of SIMS which is rumoured to be in the “cloud”, can’t come fast enough for me to wash away all this silly technical problems. It’s ridiculous that things aren’t further along. Take the file sets for the School Census, I can see 4 file sets that came out in May, it’s still a manual import. SOLUS3 should be deploying file sets. Automatically. The biggest strength of SIMS is the community, it’s also the biggest problem. Viva la revolution!

Cloud revolution

Want a job? Head to Superdrug

One of the things you’ll find when your going to interviews for your first job after leaving education is that they ask the same types of questions. The main one that feels me with dread is “Give an example of good customer service you have provided”. I hate that question. Hopefully your interviewer would have been kinda enough to at least state that, as you are ill experienced, you might want to give examples of good customer service you have received. Sounds simple enough, but what is good customer service?

The first thing that jumps to my mind is examples bad customer service. We all remember bad customer service, they stick with us, they are almost etched in our mind and often good customer service is overlooked and quickly forgotten. It’s harsh fact in the IT world that the best people are those that no-one knows what they do and only after they leave do people realise how good they were.

The example that prompted me to write this post was after visiting my local Superdrug. Now you have to understand, I’m not a fan of shopping, in fact, that’s an understatement. I’m often messing about if I’m not too busy daydreaming. So when the missus has dragged me shopping, and if I’m being honest, Superdrug is possibility the worse shop she could drag me to. The idea of spending any length of time looking at make-up horrifies me, combine that with the lack of entertainment (trolley racingiPad “testing”comfy chairs) combined with the prospect of holding bright print bags and your pretty much got a full-on sulk for the rest of the day.

Superdrug Ipswich

The thing that impressed me was one of the staff, he clearly had spotted my sulky face and had been watching us as my missus perused the aisles. As we ventured near the final stretch towards the tills he moved from the shelves he was stacking at the back of the store, not to the tills but the boxes near the tills and continued unpacking stock. When we finally came to queuing for the tills after what felt like an eternity, he stopped unboxing and headed for the till. He opened the till, despite us being the only person in the queue. He stated that they have a 2 for 1 offer on fizzy drinks and would we be interested – the fizzy drinks being stored next to the tills – when we collected a pair and placed them on the counter, he asked if we wanted them out or bagged.

This was good customer service.

He spotted a unhappy customer, me. He decided the best way he could help was to get me out of the shop as quickly as possible, the less time I spend in the shop, the more likely I was to return and more importantly, with my money.

He stayed efficient. He didn’t stop working, he carried on even when waiting for us to pay – it also meant the missus didn’t feel rushed (at least by the staff!)

He saw a sales opening, he took it.

For me, the ticks all the boxes. It shows common sense, that your able to put your feet into the customers shoes, right up to the end this guy is thinking of me, the customer – I can’t tell you the number of times I’ve had shop assistance put items into a bag when you both know your going to take it straight out of the bag (a rant for another day perhaps). It shows this guy can see the real benefit of good customer service, not only has he increased his sales today, but future sales by turning a unwilling customer, into a sightly more willing customer.

For more helpful tips on Customer Service read Tricia Morris‘s 7 Customer Service Lessons from a Jedi Master.

Why I like Twitter

Applications crash and "AccessViolationException" exception occurs when you use System.Data.SqlClient after you install Visual Studio 2013 or .NET Framework 4.5.1

I have to say I’m really liking Twitter. A lot of my friends can’t see the point. As a social tool it allows gossip to flow really well, how often do we hear that a celeb has tweeted a inappropriate comment or photo. It allows us fans to be alot more closer. As a business tool it allows a new way of interaction, take this example – there is a bug in .net framework 4.5.1 that occurs if your using a certain type of third party firewall (often bundled with anti-virus software these days), it breaks SIMS .net, that uses .net 4.0. Capita have spend ages trying to resolve it, then, @timbo343 posts on Edugeek that 4.5.2 has resolve it, a quick tweet to the Microsoft @dotnet twitter guys and it turns out that it is fixed in the new version. Awesome.

DfE big plans for big data

An interesting debate erupted on EduGeek this month after Graham Reed, a technologist, made a post on Eduware Consulting blog about the changing data landscape and how he saw Department of Education (DfE) data exchange and warehouse projects changing the way MIS work and the way they are used. Now of course we need to take it with a pinch of salt, firstly, this is very much early days. At the moment it’s a vision looking to become a project, and we all know how government projects end up post election time. Add the fact Graham Reed is one of those folks trying to push SIF and the interoperability agenda and you start to get the feeling your walking into a sales pitch. With that said, I think the DfE onto a winner.

The crux of the DfE goal is turn the very manual processes they have:

DfE_Current

into something a lot more streamlined:

DfE_Future

(Thanks to Phillip Hamlyn at Capita for sharing the link to DfE documents)

In short, it won’t take them months to produce something useful that by the time they’ve produced it, it’s already out-of-date. On paper this makes complete sense, it’s what happening in industry across the board, it’s what the cloud is all about, automation.

It also starts to solve a lot of other issues, generating ULNs for example requires manually generating a CTF then uploading to S2S and finally manually updating your MIS, with the new system, your MIS could do API call to the DfELearning Records Service (LRS) web service and automatically request and update the record with the ULN all without leaving your MIS application and a few clicks of a button. Simple. Take it a step further and pupil transfers could be done the same way, select the pupil who’s leaving, select the new school and it automatically sends the pupil records to the new school! Dual registered could occur the same way, just add the other school and the pupil records are synced between the two, regardless of MIS supplier, via the DfE.

Unfortunately, like most things, the problems appear when you start digging into the detail, how is this data used and how is the data currently gathered and why is it so manual?

The main issue, in one word, is Census. The Census, to put it bluntly, is used for funding. Now in a world where budgets are on the decrease the last thing you want is to see it drop even further because of an admin error, so you can see why the idea of gathering the raw data in near real-time is a bit worrying. For me, I don’t see this a show stopper. I accept data will be inputted incorrectly or not at all. I know the pains of ethnicity data. I also know the importance of a “approve” button. I know a Local Authority (LA) will most likely have access to most of their schools MIS systems and be able to produce a School Census return, I also know they would have enough data in their own systems to produce a School Census return. The key fact here is that they don’t hit that approve button. That approve button is always hit by the school. Period.

So long as decisions aren’t formed on unconfirmed facts I don’t see a problem and I’m not just talking about funding, many other decisions are formed from the School Census data, just have a look at the free reports from Arbor and your see what data there is on your school in the public domain. The general public are becoming better informed and schools are almost having to sell themselves to get more pupils on-roll, to get more funding or even stay open. This is even before we start looking at how services use the School Census data to support schools such as transportation, and not forgetting how researchers use the data for analysis. As long as that approve button still exists in the new DfE system, it’ll be no different then what happens now.

Personally, I quite like the idea. The buttons will change but core processes will remain. You will still submit data which will be used for funding, performance tables and various research purposes. How you do it will change, but that is always changing. It’s one of the joys of IT. It’s just this change is the DfE getting his house in order, it’s bring all the various siloed services into one roof, like the government is doing with all it’s websites. So really, the only people who have to worry are MIS suppliers and if you are a supplier, I know a company who can help you out 😉

 

 

 

 

Should I purchase Full SQL or is Express enough?

One of the common questions I used to get asked whilst supporting SIMS .net was, do I need Full SQL. SIMS .net uses Microsoft SQL Server as it’s relational database management system (RDBMS) so it’s important to ensure the engine is correctly sized for job at hand. Microsoft SQL Server has come in a number of editions over the years, I won’t detail all the various editions but the two editions schools should be concerned with is Express and Standard. Express is the free edition that Capita bundles as part of their installation media and Standard edition requires purchasing additional Microsoft licenses.

Standard edition can either be licensed by the number of processor (cores) or by users. I personally tend to go for processor licenses so I don’t need to worry about buying new licenses when adding new users later on down the road. It does however come down to pricing – I tend to find processor licenses break even around the 30 user mark (based on Academic licensing anyway).

So why should you shell out of Standard edition? Now to be fair to Capita a number of features that Standard has over Express, like SSIS and the SQL Agent Capita has created tools to workaround the limitations, they’ve created a data transfer tool to extract the data from the SIMS .net database into the SIMS Discover database for example, they’ve created routines within SIMS .net to create Windows Scheduled  Tasks for SQL jobs like B2B. With that said, the one thing Capita can’t get around is the hard resource limits Microsoft has set. Express is limited to 1GB RAM and 1 CPU (max of 4 cores).

When deciding what edition of SQL to use, I normally follow the following diagram

Which SQL Edition is right for me?

I simply believe a Primary school doesn’t need SQL Standard edition. Secondary schools who use Lesson Monitor, a Capita bolt-on module that is used for recording attendance for individual lessons rather then just AMPM, will be using the system pretty much all day rather then just morning and afternoons, so your concurrent users will be alot higher and therefore require more then 1GB RAM. It’s rare that a Secondary school will not be recording individual lessons, however other bolt-ons exist from third parties that will allow the same thing but not have the same impact on the server, so you may be able to get away with Express by using a web based bolt-on.