CREATE OR ALTER

One of the fundamentals that we learnt when learning SQL, that we use CREATE to create a database object, and ALTER if we needed to change its structure. It was simple and straightforward.

But it was too much of a problem writing a deployment script, where you had to check if the object existed, and then create or alter it based on the result. Or you could drop and re-create the object but that would make you lose all the permissions that have been set on the object. In short it tended to get a little messy. Or of course you could try a clever idea like this one. Putting it in as dynamic SQL was a clever alternative too,  but then you had difficult-to-read and difficult-to-maintain scripts.

SQL Server 2016’s Service Pack 1 now gives us the CREATE [OR ALTER] statement, with which you could create a database object or modify in just one go. It’s a feature been asked for, for quite a long time, and it’s finally here.

You can use CREATE OR ALTER on the following types of objects: Stored procedures, Functions, Views and Triggers. It would be nice to have this on tables too, but then we have the whole issue of tables being populated, and the effect that it would have on dependent tables would be all too complicated.

Here is a piece of code comparing usage:

THEN:

IF (SELECT OBJECT_ID(‘TestProcedure’)) IS NULL
EXECUTE(‘CREATE PROCEDURE TestProcedure AS PRINT ”Test”’)
GO

ALTER PROCEDURE TestProcedure
AS
BEGIN

PRINT ‘Here”s the real code I wanted to write’
END

NOW:

CREATE OR ALTER PROCEDURE TestProcedure
AS
BEGIN
PRINT ‘Here”s the real code I wanted to write’
END

 

Advertisements

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.

 

Integrating SSRS report items into Power BI

Last month I posted about configuring SQL Server Reporting Services (SSRS) to integrate with Power BI, which is a new feature included with SQL Server 2016, and adds more to the ever-growing capabilities of Power BI.

In this post I will explain the easy steps to publish report items from a report to a Power BI dashboard.

The first step is of course to build a report for SSRS, and then publish it to the report server. Make sure that the report server compatibility is set to the new “SQL Server 2016 or later” value. In my case, I quickly put together this ugly report that connects to the AdventureWorks database:

Reseller Sales report

Once published, and once you navigate to Report Manager and run the report, apart from the new toolbar, you will notice a “Pin to Power BI Dashboard” button on it. Clicking on this will confirm if you want to go through with signing in to Power BI and granting it permission to access your reports as well as other components.

Sign into Power BI

Permissions for Power BI

Once that’s all settled, you will be directed to choosing the report items that you want pinned to the dashboard. Notice that tablixes are disabled and only charts are allowed to be published. This makes sense since data on a tablix can grow to great lengths:

Click Report Item to pin

Clicking on an enabled report item prompts you to choose the dashboard and the frequency the report item should be refreshed with data:

Pin to Power BI Dashboard

Pin Successful

Lo and behold! The two report items are now pinned to an existing dashboard, and clicking on these report items will navigate to the report:

Dashboard with pinned report items

Behind the scenes Reporting Services creates a timed subscription per report item that is pinned. The timed subscription uses a SQL Agent job to refresh the report based on the selected latency. Hence, if you get this error when you are trying to pin a report item to a dashboard, then your SQL Agent is probably not running:

Can't Pin to Power Bi

All in all, I think this is an important feature in Microsoft’s suite of BI tools where components of enterprise reports can be pinned to a self-service dashboard, whereby giving more value and a greater chance for adopting the suite as a complete BI solution.

Configuring Power BI Integration with Reporting Services

SQL Server Reporting Services (SSRS), the de-facto reporting tool in the Microsoft business intelligence stack has been losing attention while its cousin Power BI, has been stealing the limelight with updates and goodies coming out at a regular pace. While, Power BI is a nice little tool for flashy reports and dashboards, it lacks the features to build complex reports. But that is just how Power BI is meant to be used. But once in a while there comes a requirement where someone might think that it would be nice to have a portion of an SSRS report pinned onto a Power BI dashboard – SQL Server 2016 makes this possible.

When configuring Reporting Services in SQL Server 2016 (I’ve noticed it beginning CTP3) using the Reporting Services Configuration Manager, you will see a new tab for Power BI Integration.

SSRS_PowerBI_Configuration_01

SSRS_PowerBI_Configuration_01

All you have to do is hit the Register with Power BI button and sign in to your Power BI account, and you are registered!

SSRS_PowerBI_Configuration_02

The next post on this theme would be more in-depth where I’ll get into the next steps…