Hi there 馃憢

I’m a Business Intelligence Developer based in UK.

Power BI failed to refresh - exception was raised

I was recently seeing alot of Power BI data refresh failures on newly created environments, however existing environments would be fine. The errors that were occuring were as followed: Failed to save modifications to the server. Error returned: 'Attempt to read when no data is present.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. The command has been canceled....

October 3, 2021 路 2 min 路 matt40k

New Year tech clean up

Well as another weird year begins I鈥檝e managed to break some time off to do a bit of housekeeping. I鈥檝e remove some old forks, added licenses, tags etc. I鈥檝e also been renaming the default branch to main. git branch -m master main git fetch origin git branch -u origin/main main

January 17, 2021 路 1 min 路 matt40k

Invoke-SqlCmd Failing

A really annoying problem I hit recently with a some Azure Runbooks was the following message occuring: Invoke-Sqlcmd : The WriteObject and WriteError methods cannot be called from outside the overrides of the BeginProcessing, ProcessRecord, and EndProcessing methods, and they can only be called from within the same thread. Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer Support Services. After doing some digging, I found out that Invoke-SqlCmd isn鈥檛 thread safe, so other runbooks running on that Azure Automation account, could interfere with each other....

July 16, 2020 路 2 min 路 matt40k

HP Laptop review

It鈥檚 the time again that I go in search of a new laptop to replace my current one. Following on from my previous success with the Toshiba laptop - buying cheap and upgrading the RAM and HDD. I went in search of a cheap laptop. My requirements remained all but the same except for a Full HD screen. I once again settled on a Intel CPU. I did spent a large amount of time trying to find a AMD Ryzen based laptop and although they seem good I just couldn鈥檛 seem to find the right one, most seemed to have a optical drive which nowadays seems like a waste of money and just adds pointless weight to the laptop....

January 22, 2020 路 4 min 路 matt40k

Version control in PowerBI

Power BI is a awesome self-service reporting tool that has a low-level of entry for new comers, that allows end-users (ie Non-IT folks) to deliver elegant insight into data via interactive reports. It鈥檚 biggest flaw however is it doesn鈥檛 have any kinda of code version control support. Like it鈥檚 impossible. Various people/companies have developed hacky solutions however this goes against Power BI core design of being a simple, self service tool....

February 28, 2019 路 3 min 路 matt40k

Auto refreshing PowerBI

One of the problems we hit today was getting a Power BI to automatically refresh. The underlying data is updated every 5 mins, however the actual Power BI only refreshes when the user interacts with the report despite using DirectQuery. To overcome this, we鈥檝e built a simple HTML page that used a bit of JavaScript magic to force a refresh and the new Secure Embed feature. First, we need to get a code for the report, open the report then click File > Embed (You can select 鈥楶ublish to web鈥 - this means you don鈥檛 need to login to view the report, however anyone in the world could access the report)...

January 22, 2019 路 2 min 路 matt40k

Microsoft christmas

Power BI Secure embed codes - allows easy sharing of Power BI reports. The public embedded codes - publish to web - has been out for a while that allows you share the Power BI reports that the anyone on the Internet (unauthenticated members of the public) should be able to see. The key difference is the secure part of the name. Users will need to authenticate, this does of course mean they will require either a Power BI Pro license or the report will be using the Power BI Premium capacity....

December 18, 2018 路 2 min 路 Matt Smith

Monzo

Monzo is a digital only UK bank. It is fully authorised in the UK by the Prudential Regulation Authority (PRA) and regulated by the Financial Conduct Authority and the PRA. Simply put, if Monzo goes belly up, your money is protected. If you are over 18 years old and have a Android or iPhone (as it鈥檚 app only), it鈥檚 worth signing up, it takes minutes. Especially whilst they are giving away 拢10 for new customers!...

December 10, 2018 路 1 min 路 Matt Smith

Power BI folders

One of the annoyingly missing features in Power BI is the ability to organize fields into folders to allow users to easily find them. Luckily the amazingly animated Will Thompson posted an update on Power BI Ideas, which has received over 400 votes, that it has been developed and is available as a preview feature in the Nov'18 Power BI Desktop release. The display folders are part of the new modeling view....

December 7, 2018 路 1 min 路 matt40k

Remember a business is a business

At the end of the day, a business is a business. It鈥檚 sole job is to make money. There isn鈥檛 anything wrong with this, I like getting paid each month, it enables me to be to, you know, buy food and keep a roof over my head. Once you accept you need money to live in today鈥檚 society you can kinda see how a business needs to make money and if you can reduce costs it, brillant....

November 24, 2018 路 2 min 路 matt40k

vardecimal support is enabled for the database but disabled for the project

I had a bit of a weird one where one of the SSDT projects failed to deploy. The package would successfully deploy locally, but failed from the deployment server. An extract from the log: vardecimal support is enabled for the database but disabled for the project. See SQL Server Books Online for more information about how to turn off vardecimal support. The short version of the story is the deployment server was trying to msdb....

October 1, 2018 路 1 min 路 matt40k

API

Towards the end of last year I was writing .net core AWS Lambda. I was super impressed with how .net core (and even PowerShell) works on Linux. I would have to say C# is by far my favorite programming language, the only exception might be T-SQL and the idea of being about to write code and get it to run on Linux is amazing. Mainly because Linux servers are, to be blunt, cheaper....

June 8, 2018 路 2 min 路 matt40k

Renaming a SQL Server

So you鈥檝e renamed your SQL Server, but something isn鈥檛 right. Returns the old name. The fix is simple. You just need to restart the SQL instance and @@servername will return the correct name. More info on the Microsoft Docs. On a similar note, Octopus Deploy has similar annoyance, the server name doesn鈥檛 update for the server node. Again simple fix for this simple annoyance: The other thing that can get missed is the SQL Jobs....

May 7, 2018 路 1 min 路 matt40k

SSRS Error - There is an error in XML document (2, 2)

I got this error when opening an SSRS project in Visual Studio. The solution. Upgrade SSDT. Turns out my colleague had a higher version of SSRS component - if you go, within Visual Studio, Help > About it will give you the version number of Visual Studio then the individual component versions.

March 20, 2018 路 1 min 路 matt40k

Predictions

Here are a few of my predictions based on my experiences over the last few months. First. Azure Data Catalog is going to be massive in the near future (18 months). The (hopefully) future integration with the new SQL Data Discovery and Classification is going to help massively with GDPR. Secondly, Chris Webb is going to become very popular. With Azure Analysis Service, the big question is optimize or scale. The killer feature for any monitoring tool aimed at the BI market is going to have to answer that question....

February 27, 2018 路 1 min 路 matt40k

Bye bye Startcom

In the old days, you would have to throw money at a trusted Certificate Authority (CA) who would sell you a SSL certificate. The alternative was to self-sign or use someone like cacert, the problem is out-of-the-box, no browser\OS supports this so you get a security warning, which isn鈥檛 something you want when your trying to be secure. StartCom, was a trusted CA who issued free SSL certificates. Microsoft Internet Explorer, Google Chrome, Mozilla Firefox all trusted StartCom which means you could get a free SSL certificate without the warnings....

December 3, 2017 路 2 min 路 matt40k

Troublesome CSV

Another day, another source of bad data. This time, it was a CSV. As you can see, the format changes mid file. This makes importing tricky. The way I tend to handle importing into SQL is to create staging tables with all the columns defined as varchar, then, once its imported into SQL, then convert it. The problem occurs when you try to determine the number of columns required. Traditionally, we let the system auto determine how many columns we have, and if we have headers, define the names, unfortunately because the format changes we can鈥檛 do this otherwise it will ignore the additional columns - assuming the first row doesn鈥檛 contain all the columns....

December 3, 2017 路 1 min 路 matt40k

Serverless

Serverless doesn鈥檛 somehow make your code run without physical servers. You still need servers, it鈥檚 just your treating your servers like you would code. However, it goes beyond just infrastructure as code (IaaS), it鈥檚 utilizing the cloud. Let鈥檚 get something clear, the cloud is nothing more than someone else鈥檚 server and for this post, I鈥檓 defining the cloud as any service provider who allows you run compute by the millisecond. The reason the cloud is such a major thing is that it allows you to treat commodity hardware as a commodity....

November 23, 2017 路 4 min 路 matt40k

Turbo SQL 2017 backups

Another day, another Twitter awesome moment, Parikshit Savjaniwas explaining how backups are now faster in SQL Server 2017. How we made backups faster with SQL Server 2017 https://t.co/NNf5cLuBJx 鈥 Parikshit Savjani (@talktosavjani) November 23, 2017 For more info on how, read his post. TD;LR - The wait time before data is actually copied is reduced because the amount of buffers to scan is massively reduced. 鈥溾equires only 250 buffers to scan as opposed to 500 Million buffers with former algorithm鈥︹...

November 23, 2017 路 1 min 路 matt40k

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鈥檚 saying. I鈥檒l give you a hint. We鈥檝e connected and authenticated. Here is another hint, we can read from that bucket. Give up? It鈥檚 permissions. Incorrect AWS keys returns a helpful message, so does invalid bucket, as does no access to read bucket

September 30, 2017 路 1 min 路 matt40k

Nice idea, but pretty holely

So the NHS Hack Dayretweet this We're a UK non-profit dedicated to open source in Healthcare. Can anyone help us with some VPS instances? @rackspaceUK @digitalocean @linode 鈥 NHoS (@_nhos_) April 30, 2017 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....

September 21, 2017 路 2 min 路 matt40k

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

September 21, 2017 路 2 min 路 matt40k

Cyber Security Insurance

I鈥檝e 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鈥檛 down to lack of available resourceson the matter. Thinking positively about cyber security, the card companies banded together to form PCI-DSS 鈥撀燩ayment聽CardIndustry Data Security Standard, with the goal to help businesses process聽cardpayments securely and reduce聽cardfraud....

July 5, 2017 路 3 min 路 matt40k

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

June 13, 2017 路 1 min 路 matt40k

Windows Insider 鈥 new features

Today鈥檚 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鈥檒l be shown above other windows so it won鈥檛 get blocked. Look for updates to the Movies & TV app and Skype Preview app to take advantage of this feature in the near future....

March 13, 2017 路 1 min 路 matt40k