At the end of last week’s post on SSAS 2016 I listed off several features and enhancements that will not be in SSAS 16′, at least the RTM version (SQL 2016 Release). There have been some questions on this and since SSAS Tabular is so central to MSBI (see Why SSAS?) it seems this topic deserves its own post that touches on other MSBI tools and services.
Before I jump in I’ll reiterate that overall I’ve been very impressed with SQL 16 BI tools, the roadmap, and the emergence and integration with Power BI. SQL 2016 represents a remarkable leap forward in enabling entire new use cases for BI (Operational Analytics or In-Memory Columnar OLTP and Power BI Desktop’s development as two obvious examples).
So with that all that said I’ll start with my wish list in order of importance.
Import from Power BI Desktop to SSAS Tabular
If someone (BI developer, Business User) develops a model in Power BI Desktop that needs the enterprise scale and features of SSAS we should be able to import that PBIX file (along with its queries, please) into a new SSAS project. We’ve had ‘Import from Power Pivot’ since SSAS Tabular was released in SQL 2012 and Power BI Desktop is growing more powerful, mature and popular. (This feature is apparently a top priority post SQL 16′ release)
Power Query Support in SSAS and SSRS
In the SQL Server 16′ MS Ignite presentations and marketing materials Power Query integration was touted as a top new BI feature (and it would (will?) be). Chris Webb blogged about it’s value to SSRS specifically (sorry, can’t find this post to link) – if you’ve ever used SSRS lookup functions to integrate disparate datasets into the same report you understand this issue well. There are many scenarios where Power Query as an input to SSAS or SSRS and/or as a component to an SSIS package would make sense and be useful.
There hasn’t been any talk of this for months and you don’t see it as a source in SSDT Preview for Visual Studio 2015.
Conditional Formatting in PBI Desktop Tables
PBIX is fantastic with interactive visualizations and many of these visuals support additional logic to enable easier users consumption/analysis such as color saturation and target values. Tables and matrices have been lacking badly in this area and in many cases a small table or matrix (think Tablix in SSRS) is essential for the report or dashboard.
Recently some formatting of tables and matrices has been added (e.g. alternating row formats, titles, font, borders and grid) and thankfully conditional formatting development has started so hopefully this will be available soon. When you have something as common and standard for Excel reports like conditional formatting, and when many of your users are accustomed to Excel reports (sometimes with complex conditional formatting rules), then having something equivalent or better is important.
Slicers
This is another area where I’d like to see the tools come closer together for a common authoring and analysis experience. Although you certainly can filter report pages via the items in the visualizations themselves and via the filtering window slicers are still essential in many use cases for that quick, intuitive filter context change.
The slicer on the left is standard with Power BI Desktop. It’s limited in terms of formatting options (1 horizontal or vertical column only) and just not as user friendly as other BI slicer objects. The middle slicer is the custom visual Chiclet slicer which supports multiple columns and more formatting but at the time of this writing (since the April PBIX update) the multi-select option doesn’t work. The slicer on the right is from Excel 2016. It’s well -formatted and has intelligence built in ‘out of the box’ and its highly customizable as well. So whether bolting in the Excel 16′ slicer to Power BI is an option or not at a minimum I’d like to see a stable, Chiclet-like slicer (perhaps with search) become a standard visual shipped with PBIX monthly releases.
Excel Services in SSRS 16
In the near future a Power BI Desktop file will render in the browser in SSRS 16 Native Mode similar to the Power BI service currently. In the current state an Excel file will render in the browser in Power BI including any Power View report pages, slicers, etc. In SSRS 16′ you’ll be able to upload and access an Excel workbook but you’ll only be able to download a copy, not view/interact in browser.
Per the roadmap slide below and what I can gather from other sources, there’s not an immediate plan for Excel Services or ‘full fidelity’ Excel to be a part of SSRS. So my point or question is this: Why is Excel being left out of MSBI symmetry plans? SSRS and PBI will both have on-premises and cloud options so why should I have to leave the SSRS portal to view Excel in the browser?
Import Power BI Desktop to Excel
You can import a Power Pivot data model, Power View report pages, and Power Queries into a Power BI Desktop file. You cannot go in the opposite direction – start building in PBIX and then decide that Excel functionality is needed.
You might say ‘Brett, c’mon, why would I need Excel with all the features Power BI offers?’ Yes, Power BI does have many advantages over Excel and will continue to improve but if you spend much time in technology you’ll find that even migrating away from older systems often creates gaps/issues not neatly handled (or supported at all) by the new tool and there are several features of Excel – specifically cube formulas and rich conditional formatting rules that I don’t see Power BI Desktop supporting anytime soon. Furthermore for many business users just getting comfortable with Power BI add ins for Excel is still a leap and introducing a completely different tool to a user/team doesn’t make sense, at least initially.
So it’s great to have the Excel to Power BI import and I understand that Power BI is changing so often that this may be difficult to build but I’d like for the MSBI teams to leave the door open for PBIX to Excel migration.
Metadata Generator
In a modern Power BI/MSBI project involving PBIX and/or SSAS Tabular different components of the model (queries, metrics, sources, reports, etc) are changing rapidly. This is a great thing and a direct benefit of the functional programming languages (DAX, M, R) and the simplified and streamlined design experience.
However, for now there’s not a tool or feature enabling you to quickly explain or describe your solution. You can query the DMVs, you can screenshot Diagram View, but delivering a comprehensive and simple to consume package of documents including data sources, metrics, hierarchies, schema/model, etc is a lot of manual effort – effort that could be better spent designing & developing rather than documenting.
In the future I’d like to see, perhaps as its own dedicated application, a metadata generator that can be configured to leverage the DMVs and other components of MSBI projects (Visual Studio projects, PBIX, Excel) and produce well formatted standard BI documentation.
DAX built into Development Tools
This one gets me a bit frustrated to be honest. If DAX is at heart of Power BI and SSAS Tabular is the flagship analytical model then why is DAX/Tabular not well supported in SQL Server Management Studio and SSRS Report Templates in SSDT? I regularly use DAX Studio (DAX Studio) for development and tuning of DAX code and then have to copy-paste this code into SSDT and SSMS such that I can use other features of these tools.
SSMS still provides the MDX/Multidimensional layout even though I’m writing a DAX query against a Tabular model:
**MDX functions, not DAX. Dimensions, not tables. No DMVs exposed. Basically you’re on your own.
Same story with Templates in SSMS below. Why are there not DAX templates?
Using SSAS Tabular as a data source to SSRS is a powerful, increasingly common use case, particularly given the new DAX query functions. However, building DAX queries in SSDT Preview for VS 2015 in SSRS Templates is even worse than SSMS.
First you see the SSAS Multidimensional/MDX layout and then you have to click the DMX icon (just to make things even more counter-intuitive).
Finally you’re greeted by a blank form (Data Mining DMX window) to write your DAX query. No functions, DMV, intellisense, etc.
The development experience with DAX/Tabular is vastly improved in SSDT for Visual Studio for SSAS Projects as mentioned in prior post (SSAS 2016) but there’s real world use cases for needing SSMS and SSRS and it seems DAX should be in the front seat with Multidimensional development moved to the back in 2016.
Contrast that experience with using DAX Studio in which we have the actual Tabular metadata and DMVs, DAX functions, and even an integrated tracing and tuning mechanism per images below.
**I could make a similar case for M development – it’s also very important to MSBI/Power BI yet lacks a proper development environment/tool (or even Intellisense). Hopefully this will be added as well but it’s not as glaring as DAX since the GUI is incredibly rich
Summary
Again, MSBI with SQL 16′ is a beast – both on premises and in cloud/Office 365 (with a high level of symmetry and hybrid scenarios supported as well) and I’m sure some of the items mentioned in this post will be addressed later this year or in early 2017. My main thought or hope is to try to keep the stack as simple and symmetrical or interoperable as possible, don’t forget about the basics (Tables, Business Users and BI Developers, Excel) amid all the industry hype and to keep listening and improving.
Insight Quest 
2 comments