December 2015 - Posts - Sparks from the Anvil

December 2015 - Posts

Microsoft deliver on their promises, handsomely!

At the SQL PASS Summit in October, James Phillips made a promise that everything he talked about or demonstrated in his keynote would be available by the end of the year.  Well they have delivered, handsomely! 

Today SQL Server 2016 CTP3.2 has been made available which delivers mobile reporting to SQL Server Reporting Services (SSRS).  SSRS has been ignored by Microsoft for years as James Phillips acknowledges in his keynote, but today SSRS gets a complete makeover and lots of new features including the integration of the Datazen mobile technology (screenshot below).  About time on-premise reporting got a makeover!


Report Manager’s new web portal also provides support for Key Performance Indicators (KPIs), which starts taking Report Manager into the realms of the dashboard arena. 


The December update to Power BI desktop includes the ability to include R visualisations into your dashboards (screenshot below).  R is an exciting development for BI professionals as it allows them to deliver practical tools to data scientists who are a demanding and extremely well educated community of end-users, very familiar with the tools and techniques required to big data analysis.  SQL Server 2016 includes R services, so Microsoft are only a small step away from delivering an end-to-end R solution with both the massively parallel data querying to the visualisations.  If you missed it, it is well worth watching Jason Wilcox PASS Summit presentation on The Future of Analytics


Power BI’s SSAS multidimensional support just got better to, with the introduction of support for hierarchies.  Power BI is really galvanising renewed interest in SSAS multidimensional as it is one of the best ways of implementing a user specific dashboard where different users use the same dashboard, but see their own data. I am working on this scenario at the moment for one client where different manages in the firm get to see their own team’s sales data and statistics.  The infrastructure to support this scenario is the newly announced Power BI Enterprise Gateway and the existing Azure Active Directory which provides single sign-on (SSO) access to thousands of cloud SaaS Applications like Office365, Salesforce, DropBox and Google Apps.

Power BI desktop also gets tighter control of formatting and many enhancements to various visualisations.  I love the new Format Painter! Take a look at the release video here.

Microsoft also announced the Data Insights Summit will take place in Bellevue, WA in March.  Maybe worth a trip!

Right, time to install CTP3.2 and get cracking on that mobile BI demo

Posted by DrJohn with no comments

Continuous Integration and the Data Warehouse

SQLSaturdaySouthamptonI am excited to say that over the next two weekends I am speaking at PASS SQL Saturday events in Southampton and Slovenia on Continuous Integration and the Data Warehouse.  Over the coming weeks I hope to provide some supporting posts outlining exactly how to set up CI in the data warehouse context.  However, I am taking a new contract next week, so my time may be sparse!

In my view, the key to implementing Continuous Integration is to move away from the old migration-based development of your SQL database to the declarative model approach offered by SQL Server Data Tools (SSDT).  In my opinion, without this transition you will be fighting an up-hill battle!

SQL Saturday SloveniaI recently attended a SQL Supper session by Alex Yates of RedGate where he discussed the pros and cons of state-based declarative model versus migration-based database development strategies.  Although he outlines the problems inherent in both approaches, he did not outline how SSDT and good source code management overcomes all these issues as his talk was tool-neutral.  He is also speaking at Southampton, so please go along.

With the declarative model approach, problems typically arise when you:

  1. Add a non-nullable column to an existing table which contains data
  2. Rename an existing column
  3. Two developers change the same objects at the same time one adds a new column to a table and a related proc, and the other developer renames the same table. This is the example Alex uses in his presentation.

I am sure there are a few other scenarios, but none immediately come to mine on this a cold November morning.

SQL Server Data Tools overcomes these issues by the following specific functionalities:

  1. Simply select “smart defaults” when you publish your database.  This will add a new nullable column to the table, then populate it with zeros and finally make it non-nullable.  All automatically!  You can then run your own data fix to update the zeros with the correct values. Clearly on a table containing a large amount of data, populating the new column with zeros will take a long time, so you may want to think about your deployment methodology. 
  2. Use the Refactor-Rename option on the SQL menu; but remember to check in the newly created refactor file!
  3. Of course, when the second developer checks the code into source control, he will be made aware of conflict and will have to merge his changes into the branch. If he does it wrong, SQL Server Data Tools will immediately identify that he broke the related stored proc, particularly when he compiles the project.  Even if he checks in the broken code, your continuous integration environment will pick up the flaw and highlight the broken code.  If you don’t have CI, come and find out how to implement it at my talk!

So please come along to one of my PASS SQL Saturday presentations or watch this space for more on Continuous Integration and the Data Warehouse

Posted by DrJohn with no comments