A Quick Look Back at 2016: Banner Year for MSBI
2016 was a great year for the MS data platform – for customers, partners, and just MSBI developers and data enthusiasts generally. I remember some fairly dark days in 2014 and at least the first half of 2015 when the future and energy of the overall ecosystem just wasn’t like this (BI in SharePoint, yikes). In 2016 we saw Power BI rapidly mature to erase existing gaps with competitors (and more), SSAS Tabular 2016 became an enterprise-ready OLAP product, SSRS was completely modernized, Advanced Analytics or R Services is now built into SQL Server to support new ‘intelligent apps’, and enhancements in the DB engine for memory optimized tables and columnstore indexes open up completely new scenarios and solution designs (ie SSAS DirectQuery, Real Time Operational Analytics, etc). I overheard Adam Machanic, MVP (and from Boston) identify SQL Server 2016 as the first major release since SQL Server 2005 and SQL Server was named DBMS of the Year so yes, among other reasons we have a lot to feel good about. (I haven’t even touched on Azure services and hybrid BI as a full 2016 review is far outside the scope of this blog post)
All of this said, there were some notable and somewhat painful gaps and unanswered questions in 2016, a few of which I mentioned around GA release of SQL Server 2016 (See MSBI Wishlist (Post SQL Server 16 Release)) For example, the migration path built for Power Pivot (to SharePoint, to SSAS Tabular) didn’t (and doesn’t yet) exist for Power BI Desktop models. The M functions (and Power Query knowledge generally) were still isolated from the primary MSBI tools (SSIS, SSAS, SSRS) despite communication they would be included in SQL 16′. Additionally, in my mind at least, there was an inconsistency in the roadmap in which MS Excel, which was identified as one of the three go-forward MSBI tools with distinct workloads (Reporting Services and Power BI the other two), was a 1st class citizen (Excel Online) in the Power BI Service but didn’t have a home in the new on-premises BI solution in SSRS 16. Of course, Power BI Desktop itself didn’t (and doesn’t (yet)) yet have a have a home in SSRS on-premises either.
Note: Two of the Wishlist items for Power BI – conditional formatting for tables and slicers were addressed recently (feature docs) and I’m excited to see continued emphasis on matrix and table visuals.
Top 10 MSBI Enhancements for 2017
So with that brief background, as we stand in late January of 2017, here are 10 enhancements to MSBI that I’m currently most interested in:
1. Azure Analysis Services
As Christian Wade might say (multiple times), ‘this is insane’. 🙂 BI is inherently and increasingly ‘elastic’ and thus is a natural fit for a cloud service that can be scaled up/down and paused as needs quickly change. With more data sources being cloud-borne and analysis queries coming from cloud services such as Power BI it makes sense to keep the model in a highly available, managed service in Azure (avoiding on-premise gateway traffic at query time). I’ll be particularly interested in the Scale Out deployment option, currently identified as ‘Planned’ per the ideas page. The Azure AS (Preview) main page is here.
2. Power Query/M Integration in MSBI Tools
Anyone whose ever had to use one of the SSRS lookup functions to relate two datasets in a report will greatly appreciate this. Anyone whose taken some time to learn (or at least experiment with) Power Query for Excel and/or Query Editor for PBI Desktop (maybe some light ‘M’ too) will enjoy knowing their investment will pay serious dividends soon. Per the image below (which I’ve referenced multiple times, from the SQL 16′ marketing materials), this didn’t make it into SQL 16′ but will provide a massive boost to MSBI projects and teams. It’s not just the additional data sources and the rich transform capabilities though these are both very nice but it’s the symmetry and flexibility that the common connectivity will bring – ‘good’ M functions built for PBIX could be re-used in SSRS or Azure Analysis Services as just one example.
3. SSRS vNext (Power BI On-Premises)
The full ‘story’ of the on-premises Power BI option was provided by Dan English in late 2016 on his blog. In short, a reasonable person that follows MSBI events and guidance closely could’ve assumed that Power BI on-premises would be available now for SQL 16′, not a technical preview. Nonetheless (and I truly don’t intend to come across as ranting or complaining here), the Jan 2017 technical preview is where we are. For all the organizations where cloud isn’t an option (even if it’s only query results), this is a big deal. Additionally, as mentioned before, I’m very hopeful that full fidelity Excel Online will be part of the on-premises option thus aligning with the PBI/Azure option.
4. Scale Out SSIS Packages
Practically all of this blog has concerned analytical models, reporting and visualization tools but a good chunk of my non-blogging (working) time is spent with people and tools like SSIS that manage the processes that prepare this data for analysis/consumption. These processes need to scale to support increasing demands from BI/Analytics projects (ie tighter refresh windows, more data (and sources) to move and process) and the Scale Out SSIS feature available in SQL vNext could be a fantastic answer to this problem. Simply configure a ‘master’ and multiple ‘worker’ SSIS servers, control which package is parallelized and by which worker, and all the downstream design and development work can pay off.
5. SSAS Tabular vNext
SSAS Tabular 2016 SP1 in late 2016 provided NUMA Awareness and thus scalability to SSAS Tabular models on top of all the benefits SSAS Tabular 2016 already provided. The modern ‘Get Data’ experience is essentially the same as my #2 point above so though it’s very important to SSAS I can’t double-dip here – the new ‘Detail Rows Expression’ property is my favorite enhancement communicated so far (see What’s New in SSAS) – defining one custom expression for all measures of a table (or per measure if necessary) will greatly resolve scenarios where users reach a filtered point of interest and want to quickly drill (‘show details’) to see the components of this number. DAX enhancements (e.g. ‘In’ operator instead of multiple ‘||’) and Ragged Hierarchy support will also make SSAS models more valuable assets.
One of the real bottlenecks on DirectQuery in SSAS 2016 is the limit of a single database from a single source. Even if performance and scale would be limited, I’d love to see some support for DirectQuery models on top of two or more databases or sources such as a SQL database and an Excel file (the budget/forecast) or better yet a SQL Server and an Oracle database.
Clearly multidimensional SSAS models will be supported indefinitely (in Azure AS as well) but some official guidance on the future of SSAS multidimensional (future enhancements?) would be instructive as the Tabular versus Multidimensional conversation is still relevant for many BI teams and projects.
6. BI Data Tooling Improvements (SSDT and SSMS)
I’ve enjoyed using the Tabular Model Explorer for 1200 compatibility level models in SSDT per last year’s blog and the integrated workspace server was also a significant improvement to the model authoring lifecycle. I’m cautiously optimistic that further enhancements will come to SSDT in 2017 including a completely new DAX authoring window/dialog (no more Excel/Power Pivot grid) and, ideally, this same UI could be leveraged when authoring DAX queries for SSRS reports.
Additionally, I’m hopeful an SSMS release later this year will offer better (not necessarily great or ‘DAX Studio-like’) support for authoring DAX queries and working with Tabular 1200 CL models generally (TMSL).
7. New Power BI Desktop Features and Enhancements
It’s been quite a ride with PBI Desktop – probably next week the February 2017 update will be released and I’ll happily update my feature reference docs again. Who knows what the Power BI team will deliver in 2017 but at this point it’s a pretty safe bet that it will be very well aligned with both user feedback and the market for modern analytics and visualization.
My favorite features likely to come (ie Under Review, Planned, Started) are PivotTable-like behaviors from Matrix visuals, Global slicers (Slicer selection on Page 1 filters visuals on Page 2 (optionally of course)), a synchronized drill experience in which drilling on one visual impacts the other visuals on the page (optionally of course), and auto update to the latest version of PBIX.
8. Power BI Service ‘Experience’ and Features
As the content from PBI Desktop and SSAS gets even richer in 2017 and there’s simply more content in PBI to browse, share, and explore we need to see the service UI adjust to handle all this. This is already under way and I’ve been pleased by the Preview features made available so far. Configurable display folders that could organize content across workspaces would be nice and a ‘global dashboard’ slicer that filters tiles from disparate reports (or maybe even datasets) thus limiting the need to leave the dashboard experience would be big as well.
9. Excel Portability and Bidirectional cross-filtering Parity
It was advised at MS Ignite that we would have the ability, at some point, to import a model from Power BI Desktop to Power Pivot for Excel like we can currently import a Power Pivot model (including queries and any Power View visuals) to Power BI Desktop. This feature, along with the guidance that bidirectional cross-filtering relationships would also be available in Power Pivot as it is in SSAS Tabular and Power BI would also be helpful. If these two enhancements aren’t delivered or only becomes available in late 2017 I won’t be terribly disappointed but they both add important options to certain projects and scenarios where Excel-specific functionality is highly prized.
I’m especially hopeful that the Excel Online experience in Power BI.com is also available in SSRS native mode (Power BI on premises). It would be unproductive and just odd to have Excel reports in SharePoint but Power BI and SSRS reports in the on-premises option.
10. Unified Gateway Manager
Whether it’s data refreshes/loads, Live Connections and DirectQuery, or PowerApps, MS Flow or other services (Azure SSAS), increased utilization of on-premise data sources by cloud services ultimately requires more gateways. This means aligning individual gateway server resources with the expected or observed workload and managing and monitoring these gateways to ensure availability and performance. Per the following Azure AS idea a unified gateway is on the roadmap so this will be an important component of the architecture going forward.
Note: There are many, many other features and enhancements that could’ve made this list. I don’t have anything about the DB Engine and it’s various in-memory, columnar options, improvements to R Services, PowerApps and Flow, Azure ML and Stream Insights and more – feel welcome to provide your thoughts on what should’ve made the cut for top 10. This is a good problem to have.