Jonathon Eve-O'Connor's Blog

Reflections On PASS Summit

Well it’s been quite a week and this has certainly been a summit with many announcements for the wider BI community. Whilst I really enjoyed my previous visit to PASS and the sessions were certainly very interesting, aside from a few announcements around Power BI there wasn’t much new in the BI landscape.

This summit has been quite a different experience - it really feels that BI features are again high in the priority agenda with SQL Server 2016. We have seen the resurrection of SSRS and significant enhancements across the MS BI Stack.

As usual, as well as the new technology announcements there were some excellent sessions from community members I particularly enjoyed the session from Rob Farley’s on Query Plan Operators and Jason Strate’s talk on the Plan Cache. Both of which contained techniques I will certainly be using in future.

I thought I would write up a summary of the changes which have been announced and my thoughts on them split by feature:


Probably the most unexpected announcement were changes to SSRS. There has been such little discussion of SSRS since release of the 2008 R2 edition of SQL Server I had started to suspect SSRS was going to be left to go the same way as Performance Point. This would have been a great shame as SSRS has been a great tool for providing on-premise reporting and fits nicely in a gap which other parts of the BI Stack don’t cover.

The following new features were announced:

- The SSRS Portal has been rewritten in HTML 5 thus making it cross browser compatible and will work across devices (tablets, phones etc). It  also means the portal looks much newer and more in line with other offerings from MS such as the Power BI Portal.

- It will now be possible to setup the positioning of and grouping of parameters in SSRS reports. This will make quite a significant change to the usability of reports and was something which could not be done without building a custom wrapper to display the reports.

- The portal will support hosting of DataZen reports, the functionality of DataZen will remain and reports hosted on the site will be separate from traditional SSRS reports, however this additional report type will be rolled into SSRS and the current DataZen product will not exist in it’s current form.

- SSRS Report parts can now be pinned to Power BI dashboards.

- Reports can now be exported directly to PowerPoint without requiring the additional use of third party tools.

- Changes have been made to the default charts and report objects which should mean that off the shelf reports immediately look nicer than  previously. This seems like an immediate win as all those who have worked with SSRS will know, it can take some time in order to produce nice looking reports using SSRS and much of this work is down to changing the current default settings.

- The Power BI Mobile App will now support connections to RS, allowing on the move users to view on premise SSRS reports.

- Tree Map and Sunburst chart types have been added providing additional visualisation types.

- Printing no longer is dependant upon ActiveX.

- Support for .Net 4

There were also a couple of things I noticed which were not quite so positive in the list of announcements:

- When asked about the licensing model MS could not confirm how SSRS would fit into the licensing model in SQL 2016. This could be because this has yet to be decided, however it did make me wonder that with all these enhancements is this something which could potentially incurincurr an additional fee.

- Reports deployed to SharePoint will not benefit from all of the new features, for example they will not benefit from any of the parameter changes and I am unsure of the other enhancements such as HTML 5 and new chart types will be supported in reports deployed to SharePoint. Basically don’t deploy your reports to SP unless you have to.

Overall though the picture is fairly positive and certainly far more than I had expected to be announced. The HTML 5 compliance and changes to parameters will certainly appeal to clients. My only criticism is that functionally the navigation and user experience of the reports will be quite similar for a desktop user. Certainly the HTML 5 will improve the look, and changing the parameters will also make some improvements but the reports will still essentially be static parameterised reports – there is certainly no announcement of any new features such as AJAX style interaction with different report parts in the way that is currently supported by Power BI.


Whilst perhaps not quite as major as the changes announced to SSRS, SSIS has some pretty nifty improvements.

- Reusable (parameterised) control flow templates have been added to enable re-use of SSIS logic. Whilst I will have to try this out the implementation of this for myself certainly there are often many similar actions performed using the ETL process which could certainly benefit from this.

- Column names now appear in the data viewer in event of an error! This is a great change and whilst it has always amazed me that this has not been present in previous releases (one might think this is pretty fundamental!) this will be a great enhancement for many people working with SSIS on a daily basis. 

- The latest version of VS 2015 (available now in preview!) will support all versions of SSIS, allowing all SSIS development to take place in the same version of Visual Studio. On a side note, ‘Data tools’ has been rolled into one product allowing for development of DB Projects, SSIS packages, SSRS and SSAS. Whilst this may not sound groundbreaking these are long overdue features which I think will have many people very happy.

-  New connectors allow direct connectivity to Azure storage and Hadoop.

- Packages can now be deployed incrementally to the SSIS catalog, rather than required entire projects to deployed at a time.

- A new role ‘SSIS_logreader’ will be added which will allow access to read logs without requiring Admin permissions.

- Foreach loop will now be able to iterate over blobs in Azure Storage.

- A new property on SSIS packages AutoAdjustBufferSize will, when set to true try to calculate the optimum buffer size for the package.

- Support for Excel 2013 as a datasource.

- ODataSource and Connection managers now support v3 & v4 protocols enabling support for JSON and ATOM.

These features certainly will make some improvements to what is already an excellent product. Fixing some of the major bugbears such as VS support for different versions and showing column names in the event of an error rather then leaving the developer to guess will further improve the product and the additional support for connections to Azure will be very useful for those hosting data in the cloud. Unfortunately, when I asked if the issues of source control around SSIS packages will be addressed (difficulties to collaboratively work & add packages when several people are working on the same project) this is not something we can expect for this release.


Last but not least there were a number of changes to SSAS in both the new Tabular models and the traditional multidimensional cubes.

- Tabular models will have proper support for many-to-many relationships and bi-directional cross filtering. Whilst for many this was an expected feature already being available in the Power BI Desktop app, this is a major improvement. I think that currently one of the major reasons people choose to implement the traditional multidimensional model over Tabular is the complexity around these measure calculations in Tabular.

- Improved designed for Tabular models – this was shown in the demonstration and included some major improvements such as colour highlighting of DAX code, better intellisense and the ability to add comments to measure definitions. Whilst from an end user perspective there will be no change, certainly from a productivity and developer happiness side these features will be hugely appreciated and help make the Tabular model a more mature product. Again I think this is another feature which is likely to make people who had previously rejected the Tabular model reconsider it’s viability.

- Tabular models will support parallel partition processing and the translations, which for Tabular has only been available with BIDS helper as an unsupported feature will become fully supported.

- Direct Query has a number of enhancements. Firstly the queries generated from it are far more efficient than previously (they used to be very inefficient, so I will wait tentatively to see how much of an improvement this is). It also supports calculated columns (which must be used with caution!) and perhaps the largest change is that it has better MDX support meaning that Excel can now be used to connect to the model and query it directly.

- The DAX language has some improvements - it now supports variables and can return multiple result sets from a single query.

- A new Tabular JSON document representing the model will become available and can be used when compatibility level is set to the new 1200 value. From the demo it looked like this format should be far more readable to the eye as well as making changes to the model itself quicker due to an improved structure of the metadata for the model.

- Support has been added to track extended events in AS graphically.

- Power BI Will support connections to Multidimensional models.

- Issues around inefficiencies with MDX queries generated by Excel have been resolved leading to significantly faster performance in the 2016 release of Excel.

Overall this is a really nice set of improvements. We see the Tabular model becoming a more mature product and also some enhancements which will give great benefit to users of the traditional MD models, such as integration with Power BI and faster performance in Excel. There are also some significant improvements both to Power BI and the Database engine which I am looking forward to.

I should also note that Microsoft have announced their BI Roadmap which gives an overview of their future direction here:

Anyways that’s it for now, I’ll leave you with this picture from earlier in the week. When me and colleague Simon took a trip to Mt St Helens :)


SQL PASS Summit - Day 3

Today was the final day of the pass summit. The schedule was a little different in the morning with no keynote speech. Instead I opted to see Jerimiah Peschka’s session ‘Dynamic SQL: Build Fast, Flexible Queries’. This was an interesting talk, which covered the security aspects of dynamic SQL and how to avoid things such as SQL injection and also how to ensure that queries generated from dynamic SQL ran quickly and avoided issues such as ensuring plan reuse on each execution. Whilst I very rarely find a myself situation in which Dynamic SQL is essential I found this an interesting talk and I am sure that when the need does arise it will have been very handy.

Following this I attended a Microsoft lab on Azure Machine Learning (AML) ‘Authoring a hand-written digit recognizer in Microsoft Azure Machine Learning Studio’. This was a hands on session where computers and a set of exercises and sample data are supplied. Essentially the lesson consisted of providing a model with vector images of numbers, training the model to recognise the numbers and then passing a new set of number vector images to the model to see it identify them (> 85%) correctly. The session was interesting and gave me an overview of using AML Studio which was great. My main issue was that the data was already provided in vector format (csv files which were imported), whilst I can see time constraints mean that the conversion from image to vector during the class would have been difficult it would have been very interesting to have seen a few of the examples of the images so that we could see exactly what it was that was being identified and how different the numbers were as this would give an illustration of how clever the algorithms are.

I finished the conference with Brain Larson’s talk entitled ‘Effective Reporting through SSRS Advanced Authoring Features’. As you may know SSRS has not been updated for some time now and with the current focus on Power View I wasn’t sure how much I would benefit from this and what the plans are for this tool in the future. I was quite surprised therefore when the room filled up and overflowed so that there were quite a crowd of people in the room who had to stand in order to watch it. The talk showed some interesting uses of SSRS - images embedded in the background of reports, adding strip lines and markers and an example of creating an interactive report which allowed users to set their preferences for running other reports. The examples given were very good as was the delivery, my only issue here is that without new features added to SSRS (for example ability to do an asynchronous refresh of some report elements) I am not sure what the uptake of SSRS is going to be.

All in all I have had a fantastic and informative time here. I leave you with some pictures, taken earlier in the week :)

20141031_113229  20141030_132631

SQL PASS Summit – Day 2

This morning started with a talk from Rimma Nehme a key Microsoft architect. The talk was informative and clearly Azure and the cloud are something which Microsoft are very keen, it was well thought through and there was a fantastic analogy between pizza and different flavours of Azure which is not what I had expected.

Adam Machanic did a presentation covering different methods which could be used in order to force the query optimiser into choosing more efficient query plans. This was not for the feint hearted and included some ingenious methods which he had developed which could be used to ‘fool’ the optimiser into selecting different query plans. The methods tried to push towards parallel queries and ensuring that the right workload was assigned to each of the threads so they finished at roughly the same time. The only drawback to this was that some of the examples produced code which was quite obfuscated and not something which I thought could be used except in the case that there was an exceptional issue which necessitated their use.

Davide Mauri’s session on Unit Testing and Agile development was very interesting, he showed demonstrations of NUnit, BI.Quality and NBI. These are tools which allow users to create unit tests when following an agile approach, whilst we also use similar methods at Adatis to provide unit testing functionality the demonstrations were very effective and I will be doing some further research into these products to realise their full potential.

Connor Cunningham also presented a fantastic talk titled ‘Managing 1M + db’s – How big data is used to run SQL Azure’. This was a very insightful talk which detailed the issues that had arisen when running the SQL Azure service and the methods used in order to identify, quickly resolve and identify the root cause of the problems. Central to this was both the collection of telemetry data from many sourced which could be used to perform diagnostics and the methods used in order to identify problems. Here we saw a very interesting usage of Azure Machine Learning, which had been setup to trigger Alerts on the occurrence of unusual behaviour. Connor is a fantastic speaker and clearly one of the most knowledgeable people on the SQL engine, I really enjoyed this talk.

I’m off to the evening party now at the EMP museum, looking forward to having a few beers and meeting some new people :)

SQL PASS Summit – Day 1

Having spent several days enjoying Seattle and getting to know and enjoy the city the conference is now underway. Yesterday there were some introductory meetings where we got to meet some of the other attendees and get a feel for the environment, today everything was in full swing.

The morning started with the opening keynote presented to a huge audience. There were demonstrations of some really exciting new features – in one we observed Kinect being used with Power Map in order to track customer movements to observe customer interest in different parts of the store. We saw some great looking Power BI dashboarding functionality with the ability to drillthrough into detailed reports.

As well as this we saw some further enhancements SQL Server Azure and on-premise integration including a new stretch functionality which will allow users to seamlessly ‘stretch’ their data into the cloud, keeping the most frequently queried records on premise and the other ones in the cloud. We also saw a Columnstore index being created on an in memory table!

Miguel Lopes gave a talk on the new features in Power Query where we saw the ability to query using ODBC and support for Analysis services connections, on the whole though whilst I think the ODBC will be particularly useful for some situations, much of this talk was giving an overview of Power query as a whole rather than demonstrating new functionality. The integration of SSIS and power query in future was mentioned, however no dates have been set for this and we are told that this may (or may not) be available at some point in 2015.

Jen Stirrup gave an interesting overview of some of the features available in R, the session was so popular that many people had to sit round the front on the floor! Niko Neugebauer’s contagious enthusiasm when presenting his session on ETL Patterns with Clustered Columnstore indexes was great to see and I picked up a few tricks here that were quite handy when working in 2014 environments. I also very much enjoyed John Welch’s session in Continuous Delivery for Data Warehouses and Marts, this is something I myself have been involved with a lot recently and it was very interesting to see his methods of achieving this and also to discover that in many cases we were both doing things in the same way :)

Overall the day has been very interesting, we have seen some exciting new features announced today and some significant enhancements to the Power BI product range, it seemed to me for some time that the lack of dashboarding functionality in Power View was holding it back and I think many people will be very pleased with this new functionality and the further enhancements to the Azure service.