Why I Love SQL Server 2016’s SP1

Service Pack 1 (SP1) for SQL Server 2016 pleasantly surprised a lot of us by coming out less than 6 months after SQL Server 2016 RTMed. What was even more pleasantly surprising were the several Enterprise-only features that have now been enabled for Standard and even Express editions with this release.

Two of my favorite features that have been enabled for Standard Edition are In-memory OLTP and ColumnStore. Why? In my line of work, there are lots of instances when I propose a business intelligence solution, that I have to envision a BI strategy for the organization that I deal with. This strategy of course, will not be a one time implementation. Business Intelligence is a journey, you would in a lot of cases need to start small, where budget is a primary concern, and you need to show the stakeholders value, and then based on the value you showcase you expand on technology, usage scenarios and more. And in these cases, value should come with costs that are not insane; Insane as in proposing an Enterprise Edition of SQL Server at the inception, just so that you could show value and performance.

And so now you know why I love Service Pack 1 for SQL Server 2016 — It helps me envision strategy with less cost.



SQL Server on a Domain Controller

Much has been said about installing SQL Server on a domain controller. It’s bad practice. Period. If you wouldn’t believe me, then ask the SQL Server installer…


However, there are naughty little girls and boys such as yours truly, who due to lack of funds would want to do the same, maybe on an Azure instance, just so that you don’t have to pay for two VMs, to do some database development. If you indeed choose to go down this path, the default user accounts that are associated with the various SQL Server services would not fly.

Microsoft, in its Requirements for Installation, state that despite it not being recommended, they will still let one go through with installation. It’s just that you need to ensure that you use a local service account to run the SQL Server services. If you do not heed this “warning” and still go ahead with network services and what not, at one point during installation you would error out, and several components would fail to install. This would result in an uninstall of SQL Server and a retry. Hence to make things simple this is all you need to set as the service accounts, and you are on your way to a successful install at the first go itself…


The Analytics Pane in Power BI

The August 2016 edition on Power BI Desktop introduces a new pane named “Analytics” right next to the “Fields” and “Format” panes. Now, this title probably gave you, just as it did for me, a racing heart and goosebumps. However when you actually go to the pane, you just blurt out a disappointed “oh…”. Well, not that it is bad, you have options such as adding a percentile line, median line, trend line and a few more. But one would expect to see a little bit more with respect to analytics.

One thing that you do find is a forecast line, which for now works on a single-measure line chart. It’s pretty neat, but as advanced analytics go requires enough data points in order give you a good forecast. Of course this is just the start. We’re sure to see more analytic capabilities in the future.

Cortana Intelligence Suite – Not just an intelligent personal assistant

Everyone knows Siri. Well, at least those who own an iPhone do. Then again that amounts to almost everyone. So I guess everyone does know Siri. A couple years ago however, we were introduced to Cortana (named after Microsoft’s Halo game character) on Windows Phone. She soon found her way onto Windows 10 devices, and is now even made available for iOS and Android devices. Think of her as something of a primitive version of Scarlett Johansson’s character in Her, if I may. She is Microsoft’s own intelligent personal assistant, and I use her almost everyday.

But what I am going to introduce you to in this post is something bigger. Much much bigger. It’s called Cortana Intelligence Suite; Microsoft’s Azure-based analytics and intelligence platform. Don’t panic just yet! D-Day hasn’t begun, nor have the machines risen, but I bet you can well imagine what Microsoft must be aspiring when they named it that.

Cortana Intelligence Suite (CIS) is a platform and process for building end-to-end advanced analytical solutions. The platform is made up of various tools and services, mainly running on top of Azure. It is not necessary that all the tools that make up CIS be used for your solution, but it is important that you follow a certain process to get it done. This way you know what you are going to do in a methodical manner, and it would be easy to choose the right tools and services from the suite to get things done.

The process that CIS solutions follow is known as Cross Industry Standard Process for Data Mining (CRISP-DM), which is a long-used time-tested means of performing data mining (which is very closely related to analytics, and is often employed to perform analytics).


For a complete overview of CIS and how it can be used, this easy-to-understand video by Buck Woody would be the ideal choice. It also details the tools and services that are part of the CIS platform. Once you are done with the video, you would probably get the gist of how Cortana fits into the business intelligence and analytics vision of Microsoft. Enjoy!

[VIDEO: Cortana Intelligence Suite – Overview by Buck Woody]


Keeping up with Power BI

Power BI, or even other self-service business intelligence tools have very short release cycles. A set of features that you see wished for, magically appears the next month. You deem yourself a Power BI expert, a few months of working on another project, you come back, you see the changes that Power BI has gone through; it is enough to make you feel incompetent a grown man cry. For example, just last month, when I was speaking at SQLSaturday in Nepal, a question was posed as to how one could implement row-level security in Power BI. I answered that it was only possible via Analysis Services live connection. And guess what, perusing through some articles a few days later I come across that Microsoft had in fact announced role based security for Power BI (to secure rows).

To keep up with all these madness, there needs to be a method; and Microsoft provides means for the methods, and here’s a list of them that you could use to keep yourself sane with the changes going on with Power BI:

The Power BI blog: an invaluable site that publishes announcements in detail as to what is coming, what has been released in the latest update, and innovations etc. A bi-weekly read (at the beginning and the end of the week) to keep your mind afresh of what’s being going on.

The Power BI twitter handle: Keeps you up to date as to what’s new with links to the blog, things you can learn about, what Power BI enthusiasts are up to, tips and tricks and juicy tidbits. A daily read in the morning and in the evening is recommended.

The Power BI community: A wonderful way of getting to know what issues people like yourself are coping with, what solutions are out there, or you could even jump in and help. A weekly glance through is recommended, with additional time spent on something tht interests you.

So the next time you feel overwhelmed by what Power BI has become, I’m sure you wouldn’t really feel it.