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

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

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.