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…


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.

Learning Power BI

Two questions that was repeated several times after my presentation at SQLSaturday Nepal this year were “How do I get started with Power BI?” and “Do I need prior knowledge on BI concepts to learn Power BI?”. I’ve had these questions directed at me at other times too.

I’ll answer the second question first. No, you need not know all sorts of BI concepts to learn Power BI. Power BI is BI for the masses. Which means you have a whole bunch of non-technical people in there, and it would not be fair to make them all learn every intricate business intelligence principle and theory. Self-service business intelligence is all about putting data in the hands of the user; the business analysts, power users and end users. Self-service business intelligence follows the principle of data discovery, where the user pulls in data of interest from various sources, mashes them up and then interactively visually analyzing the data to come to a conclusion. To do this the user must not be overwhelmed by principles and theories.

To answer the second question, learning Power BI is made easy, and I will tell you how:

Guided Learning: This is a series of tutorials including video lessons. Each lesson being only a few minutes, it is an ideal way to learn Power BI at your own pace.

Power BI Documentation: With more information on the various aspects of Power BI, the documentation section makes it an easy stop to get more sense into why something may not be working and what you can do to get something working.

Power BI Community: The ever present community around Microsoft products can be found for Power BI too. When you are really stuck, this is where you go to get solutions to your problems solved. And vice versa, you too could help out others with their problems.

Submit Ideas: Looking for a feature that you really need, and Power BI does not have it? You can submit your own idea, and based on the votes you get, there is a chance that you might have that feature incorporated in a future release. In fact a lot of new features do come from ideas submitted as feedback; making Power BI truly the mass’s tool.

Use these resources for your Power BI learning needs, and I think you should be golden.