An overview to the top Microsoft Business Intelligence Roadmap Enhancements announced thus far for Power BI Services and Mobile, Microsoft Excel 2016, SQL Server Reporting Services 2016, and SQL Server Analysis Services 2014-2016.
I will not be the first to say this (I will be added to a long list of others), but SQL 2014 was a bit disappointing for the Business Intelligence community…..not to take away from the pure SQL improvements (in-memory OLTP, columnstore indexing improvements, etc.).
But, if you look at the list of “What’s New in Analysis Services and Business Intelligence”, you will see it is a…..very…..short list: https://msdn.microsoft.com/en-us/library/bb522628(v=sql.120).aspx.
If yours is like most organizations, you may likely still be on SQL 2012 R2. My organization is finally moving to SQL 2014 as a standard. Don’t get me wrong, I am happy to get the one improvement that we will utilize but I am really itching for SQL 2016 to be released.
Why did I include Power BI? Well, again, many organizations may only be utilizing Power BI Desktop…like mine. I want to show the top capabilities that your organization will gain from utilizing Power BI Services and Mobile.
POWER BI
POWER BI SERVICES
Content packs
Service-related content packs (preconfigured data models, reports, and dashboards) for major saas vendors
Natural language Q&A
Watson style artificial intelligence capable of answering questions posed in natural language
Windows 10 Native
Cortana voice-enabled Q&A as well as native windows application (pin dashboard tiles to home screen)
Mobile access
Design once, consume everywhere (iOS, Android, Windows) responsive interface
Personalized dashboards
Users are able to create personalized dashboards using data combined across multiple data sources / business segments that is most important to them
Quick Insights
Allows you to run a variety of analytical algorithms (outliers, trends, seasonality, change points in trends, and major factors within your data) on your data to search for potential insights.
QR Code scanning
QR codes in Power BI can connect any item in the real world directly to related BI information (dashboard/reports)
Real-time / streaming data source support & open API
Create custom Power BI dashboards with true real-time data push and custom visualizations to extend Power BI capabilities
Groups offer a powerful collaborative experience built on Office 365 groups.
Power BI makes creating dashboards and reports extremely simple, and now users can publish this content to the organizational content gallery.
Dashboards can refresh in real time, so you don’t need to refresh them manually.
Available in your native language
Power BI is now available in 44 languages.
POWER BI MOBILE
Dashboards refresh in real time, so you don’t need to refresh them manually.
SQL Server 2016 Reporting Services mobile reports
Now you can view your SQL Server mobile reports in the Power BI app on your mobile device.
QR Code scanning
QR codes in Power BI can connect any item in the real world directly to related BI information (dashboard/reports)
Set data alerts for your data.
Annotate and share tiles.
MICROSOFT OFFICE EXCEL
2016 ENHANCEMENTS
Power View for Multidimensional Models
In Excel 2016, you can connect to OLAP cubes (often called multidimensional cubes) and create interesting and compelling report pages with Power View.
Native Support for Power Query
Excel 2016 now comes with built-in functionality that brings ease and speed to getting and transforming your data—allowing you to find and bring all the data you need into one place. These new capabilities, previously only available as a separate add-in called Power Query, can be found natively within Excel 2016. Access them from the “Get & Transform” section on the Data tab.
Native Support for Power Pivot and Power Pivot Enhancements
Excel 2016 made data analysis more discoverable, consistent and streamlined, so that you can focus less on managing your data and more on uncovering the insights that matter:
- Automatic relationship detectiondiscovers and creates relationships among the tables used for your workbook’s data model, so you don’t have to. Excel 2016 knows when your analysis requires two or more tables to be linked together and notifies you. With one click, it does the work to build the relationships, so you can take advantage of them immediately.
- Creating, editing and deleting custom measurescan now be done directly from the PivotTable fields list, saving you a lot of time when you need to add additional calculations for your analysis.
- Automatic time groupinghelps you to use your time-related fields in your PivotTable more powerfully, by auto-detecting and grouping them on your behalf. Once grouped together, simply drag the group to your PivotTable in one action and immediately begin your analysis across the different levels of time with drill-down capabilities.
- PivotChart drill-down buttonsallow you to drill in and out across groupings of time and other hierarchical structures within your data.
- Search in the PivotTablefield list helps you get to the fields that are important to you across your entire data set.
- Smart rename gives you the ability to rename tables and columns in your workbook’s data model. With each change, Excel 2016 automatically updates any related tables and calculations across your workbook, including all worksheets and DAX formulas.
- Multiple usability improvements have also been made. For example, delayed updating allows you to perform multiple changes in Power Pivot without the need to wait until each is propagated across the workbook. The changes will be propagated at one time, once the Power Pivot window is closed.
In previous versions, only linear forecasting had been available. In Excel 2016, the FORECAST function has been extended to allow forecasting based on Exponential Smoothing (i.e. FORECAST.ETS() …). This functionality is also available as a new one-click forecasting button. On the Data tab, click the Forecast Sheet button to quickly create a forecast visualization of your data series. From the wizard, you can also find options to adjust common forecast parameters, like seasonality, which is automatically detected by default and confidence intervals.
Excel 2016 provides a new set of modern charts with the consistent look and feel of the familiar charting experience that Excel provides natively, to expand the ways to visualize your data.
Power Map, a 3-D geospatial visualization tool, is now accessible to all Excel 2016 users by delivering its features right out of the box. This innovative set of storytelling capabilities has been renamed to 3-D Maps and can be found along with other visualization tools on the Insert tab.
Publishing and sharing in Power BI
Once you’re finished preparing your data analysis, you can share it with your workgroup or clients through Power BI with just one button. Once published to Power BI, use your data models to quickly construct interactive reports and dashboards. With Excel Online support built into Power BI service, you can also display your fully formatted Excel worksheets as well.
Taking advantage of Excel Online
With rich features in Excel Online, your peers and clients are able to view and interact with your Excel data analysis and reports from anywhere. All you need to do is share your workbook through Power BI, SharePoint or OneDrive. Recently, we’ve included new features to make it even easier to consume and interact with your workbook from the web, including Search in Filters and the ability to change the settings of a value field in a PivotTable. With Office 365, we are providing more features and improvements to Excel Online on a regular basis.
SQL SERVER REPORTING SERVICES
2014 ENHANCEMENTS
The SQL Server 2014 release of Reporting Services adds support for the Google Chrome browser.
2016 ENHANCEMENTS
New KPI delivery is also based on the Datazen acquisition. These KPI visuals are created and managed entirely within the new Report Portal. In additional to the standard traffic light style comparison of actual vs target values, KPIs can include a trend line or segment chart.
Standardized, Modern Browser Rendering
The HTML renderer has been completely overhauled and updated. Now, reports are rendered to HTML 5 standards and therefore, should consistently maintain the same appearance and behavior in all modern browsers that support the HTML 5 standard such as Microsoft Edge, IE 11, and newer versions of Google Chrome, Safari and Firefox. This change is a welcome improvement which should clear up many problems with inconsistent and quirky report layouts while using different web browsers and devices.
[CTP 3.2]Starting with CTP 3.2, a new Reporting Services web portal is available. This is an updated, modern, portal which incorporates KPIs, Mobile Reports and Paginated Reports. Currently, the portal is for consumption. For adding new data sources, data sets and paginated reports, you will need to switch to the classic Report Manager. To create Mobile Reports, you will need the Mobile Report Publisher.
[CTP 3.2] The SQL Server Mobile Report Publisher allows you to create and publish SQL Server mobile reports to your Reporting Services web portal.
SQL Server mobile reports hosted in Reporting Services available in Power BI Mobile app
[CTP 3.2]The Power BI Mobile app for iOS on iPad and iPhone can now display SQL Server mobile reports hosted on your local report server.
Pin Report Items to a Power BI Dashboard
[CTP 3.0] While viewing a report in Report Manager, you can select report items and pin them to a Power BI dashboard. The items you can pin are charts, gauge panels, maps, and images. You can (1) select from your available dashboards and (2) configure how often the data is refreshed to the dashboard tile.
PowerPoint Rendering and Export
[CTP 2.4] The Microsoft PowerPoint (PPTX) format is a new SQL Server 2016 Reporting Services (SSRS) rendering extension. You can export reports in the PPTX format from the usual applications; Report Builder, Report Designer (in SSDT), Report Manager. For the example the following image shows the export menu from Report Builder. For more information, see Export Reports (Report Builder and SSRS).
Custom Parameters Pane
[CTP 2.4] You can now customize the parameters pane. Using the design surface in Report Builder, you can drag a parameter to a specific column and row in the parameters pane. You can add and remove columns to change the layout of the pane. For more information, see Customize the Parameters Pane.
New Report Builder User Interface
[CTP 2.3] The core Report Builder user interface is now a modern look and feel with streamlined UI elements.
| New | Previous |
 |
 |
[CTP 2.3]. Enhance your reports with Tree Map and Sunburst charts, great ways to display hierarchal data.
SQL SERVER ANALYSIS SERVICES
2014 ENHANCEMENTS
Power View for Multidimensional Models
The ability to create Power View reports against multidimensional models was first introduced in SQL Server 2012 Service Pack 1 Cumulative Update 4. Power View for Multidimensional Models functionality is now included as part of SQL Server 2014.
Power View for Multidimensional Models requires the built-in Power View reporting capability in SQL Server 2014Reporting Services (in SharePoint mode). Other versions of Power View, specifically the Power View Add-in in Excel 2013, do not support multidimensional models.
2016 ENHANCEMENTS
Many to Many for Tabular models
Many to Many relationships will be natively available in SSAS Tabular 2016.
[CTP 2.0] Parallel processing for multiple table partitions in Tabular models
SQL Server 2016 Analysis Services (SSAS) includes new parallel processing functionality for tables with two or more partitions, increasing processing performance. There are no configuration settings for this feature. For more information about configuring partitions and processing tables, see Tabular Model Partitions (SSAS Tabular).
With formula fixup on a Tabular 1200 model, SSDT will automatically update any measures that is referencing a column or table that was renamed
[CTP 3.0] New data sources for DirectQuery mode
Data sources supported for Tabular 110x models in DirectQuery mode now include Oracle, Teradata and Microsoft Analytics Platform (formerly known as Parallel Data Warehouse).
[CTP 3.0] Improved DAX formula editing
Updates to the formula bar help you write formulas with more ease by differentiating functions, fields and measures using syntax coloring, it provides intelligent function and field suggestions and tells you if parts of your DAX expression are wrong using error ‘squiggles’. It also allows you to use multiple lines (Alt + Enter) and indentation (Tab). The formula bar now also allows you to write comments as part of your measures, just type “//” and everything after these characters on the same line will be considered a comment.
[CTP 3.3] Apply row level security to a DirectQuery model
Besides adding a calculated column to models in DirectQuery, you can now also can add DAX filters to them. In the same example as above we want the sales team to only see products with key values larger than 400. I now can go to the role manager and add a new role that includes a DAX expression that filters the products:
[CTP 3.3] Language Translations
In this CTP we now also allow translations of the SQL Server 2016 Analysis Services Tabular model into different languages to be consumed by any client tool connecting to SSAS. Very often the translations themselves are not done by the modeler but by either someone from the business users or a translator. The modeler or BI professional can now export the model metadata to be translated and reimport it back into SSDT after it is translated.