Articles

Power BI Workspace Metrics

Power BI Workspace Metrics @vizbp

How to analyze your Power BI environment and review solution performance

Whether you want to analyze your personal “My Workspace” environment, your gateways, or all workspaces and solutions you are an admin of, you will find the Power BI Workspace Metrics solution invaluable. YOU DO NOT HAVE TO BE AN ADMIN with this product – just an admin of the workspaces you want to analyze!

Until now, the Power BI Capacity Metrics solution was the only real solution provided for this functionality but it is only available for Premium environments. What do you do if you want to document or analyze your solutions in non-Premium environments? You would have to develop a solution like this from scratch. You still can – don’t get me wrong. This product just gives you a huge head start on that development and costs much less than the hours it would take to do so. It even has some capabilities that the Power BI Capacity Metrics solution does not.

    It is a fully functional data model – joining all Power BI artifacts to be able to see lineage, relationships, types of data sources utilized, alignment to gateways, ownership, etc. It also has all of the metrics and visuals built out to easily analyze your Power BI environment.

    If you want to connect to the Power BI API and build this solution yourself, you very well can. You just need to create an Azure Active Directory Application and download some freely available connectors and/or utilize various methods to skirt around the OAuth2 authentication protocol (I will include links below) and you can begin connecting to your Power BI environment. In order to build reporting, you have to do some hi-jinks and jump through some hurdles (and be pretty good at M) in order to build out a functioning model.

    But, believe me, it takes dozens to hundreds of hours to build out a solution like this – not even mentioning the documentation that is required to make a product presentation ready for sales.

    If you have the time, I encourage you to go for it. It is an experience building something like this that is so valuable to an organization or to individuals like me. I want my Power BI solutions and environment (Pro or Premium) to be fully optimized. I thought:

    1. There has to be a way to see all of my solutions I have access to.
    2. There has to be a way to easily filter to find all solutions by owner ( in this case, the person who imported/created the dataset).
    3. There has to be a way to see all of those refresh schedules in a single view.
    4. There has to be a way to see refresh history and performance over time.
    5. There has to be a way to marry those two things to show how many datasets are scheduled to refresh or are still running at a specific time so I can intelligently select an optimal refresh time for a new solution.
    6. There has to be a way to view all failed refreshes that have run today so I can proactively resolve issues.
    7. There has to be a way to review lineage and see all solutions aligned to a specific data source in the case there is a production issue.

    I have built a product that does these things and more!

    Take a look at the product and see if it would be valuable for you or your organization. It is available on vizbp.com:

    Power BI Workspace Metrics

    While there, take a look at other solutions people like me are making available for free or for purchase. Take a look at becoming a seller yourself and offering your solutions in the marketplace.

    https://vizbp.com/sell/

    While here, take a look at my other articles related to vizbp.com:

    Continue Reading
    Power BI Model Analyzer @vizbp

    How to analyze your Power BI data model size

    Whether you want to analyze an individual solution or your full Power BI environment (all workspaces and solutions you are an admin of), you will find the Power BI Model Analyzer solution invaluable. YOU DO NOT HAVE TO BE AN ADMIN with this product – just an admin of the Premium workspaces you want to analyze!

    Premium is required currently until XMLA connectivity is opened up to non-premium workspaces.

    There are other solutions available for this functionality but most are only available for single models (I will reference these at the end of the post).

    What do you do if you want to document or analyze all of your solutions? What if you want to review solutions you did not author but are published in your environment? You would have to develop a solution like this from scratch or analyze each of the solutions one at a time. You still can – don’t get me wrong. The value in this product just gives you a huge head start on that development and costs much less than the hours it would take to do so. It even has some capabilities that the other solutions do not.

      It is a fully functional data model – joining all Power BI models and DMV queries to be able to see data model size, table size, column size, measure DAX expressions, object dependencies, relationships, compression, and encoding. It also has all of the metrics and visuals built out to easily analyze your Power BI environment.

      If you want to connect to a single model, there are a lot of options (I will list those at the bottom of the post). I even built a version that would find the open pbix port via R and query that single model. This works great during development but not when wanting to analyze your full environment.

      It utilizes the Power BI API to query your Power BI environment for all workspaces of which you are an admin and the underlying datasets. It then queries each one utilizing DMV functions to extract the dataset components (tables, columns, measures, relationships and dependencies, compression metadata, encoding metadata, etc.).

      If you want to connect to the Power BI API and build this solution yourself, you very well can. You just need to create an Azure Active Directory Application and download some freely available connectors and/or utilize various methods to skirt around the OAuth2 authentication protocol (I will include links below) and you can begin connecting to your Power BI environment. In order to build reporting, you have to do some hi-jinks and jump through some hurdles (and be pretty good at M) in order to build out a functioning model.

      You also have to know DMV queries and how to join them all together:

      But, believe me, it takes dozens to hundreds of hours to build out a solution like this – not even mentioning the documentation that is required to make a product presentation ready for sales.

      If you have the time, I encourage you to go for it. It is an experience building something like this that is so valuable to an organization or to the individuals like me. I want my Power BI solutions and environment (Pro or Premium) to be fully optimized. I thought:

      1. There has to be a way to see all of my solutions I have access to.
      2. There has to be a way to easily filter to find all solutions by owner ( in this case, the person who imported/created the dataset).
      3. There has to be a way to see all data model sizes in a single view.
      4. There has to be a way to proactively monitor solutions published to watch for solutions not utilizing best practices or over a specified size.

      I have built a product that does these things and more!

      Take a look at the product and see if it would be valuable for you or your organization. It is available on vizbp.com:

      Power BI Model Analyzer

      While there, take a look at other solutions people like me are making available for free or for purchase. Take a look at becoming a seller yourself and offering your solutions in the marketplace.

      https://vizbp.com/sell/

      While here, take a look at my other articles related to vizbp.com:

      References to other Power BI model analyzer solutions (my apologies if I did not reference yours – let me know if I failed to and I will add yours as well):

      Continue Reading

      Copy Power Queries &/or Folders Between Power BI PBIX Files

      I am not sure how I missed the ability to copy Power Queries and/or Folders between PBIX files in Power BI.

      Most users are aware of the template capabilities of Power BI – you can create a Power BI model, export the model as a template, and use the template for new solutions.  In some scenarios, that template has too many queries, sources, etc. and you have to remove quite a bit to get a usable model.  If you have been in a situation where you only want a subset of the queries or have a single query that you reuse again and again in multiple models, this capability is golden.

      You simply open the model you would like to copy a query or multiple queries (either individually or from a folder), open another model (existing or new), and paste the query(s) in to the new model……simple.

      Copy Power Queries

      After notifying my coworkers of the ability, one of my coworkers pointed out that if you copy and paste a single query, the tool is smart enough to copy all related queries (i.e. joins, merges, appended, etc.) along with the original query copied.  As well, if a query already exists in the new model with the same name,

      Hope this helps improve your data modeling experience.

      Continue Reading

      Power BI Bookmarks

      Power BI bookmarks add so much valuable functionality to the application.  They allow so many capabilities that were missing prior.  Some are true workarounds but some are on par with other BI tools.  Take MicroStrategy and their use of panes – historically difficult to work with – with Bookmarks and the Selection Pane, it is very easy to duplicate this capability in Power BI.  This allows you to change visuals dynamically – or make it look like you are dynamically changing the axis by using bookmarks, the selection pane, buttons/tabs, and either multiple visuals or drill down levels to change the axis.

      I have curated a decent list of wonderful blog postings on use cases for bookmarks.  Please visit them, thank them, and give these authors credit.  I hope you find these as valuable as I have.

       

      Use Cases for Bookmarks

      http://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

      Excellent writeup to show useful use cases including dynamically changing the axis, changing colors/types of charts, etc.

      Build a Collapsible Slicer Pane for Power BI

      Build a collapsible slicer pane to show/hide filters w/ sample pbix file.

      https://www.wiseowl.co.uk/blog/s2585/main_menu_bookmarks.htm

      Four examples of use cases for bookmarks (clearing filters, tabbed navigation, expand/collapse menu, page navigation).

      https://www.blue-granite.com/blog/create-an-app-like-experience-in-power-bi-with-bookmarks

      Using bookmarks to create an app-like experience in Power BI.

      Power BI Feature Spotlight: Using Bookmark Buttons to Create Pop-Up Windows

      Using bookmarks to create pop-up windows.

      https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/

      Dynamically changing a chart axis using bookmarks and buttons

      Power BI Feature Spotlight: Bookmarks

      Switching the visible visual shown based on bookmark selection.

      http://microsoft-bitools.blogspot.com/2018/01/power-bi-bookmarking-feature-update.html

      Another writeup on dynamically changing visuals using toggles.

      https://www.youtube.com/watch?v=OcxTUNlyIVw

      Four use-cases for using bookmarks (pagination, clear filters/slicers, switch visuals, and hyperlinks) w/ pbix sample

      https://www.youtube.com/watch?v=_Afcj8mT5_Q

      Excellent overview from Guy In a Cube with pbix sample

      https://www.blue-granite.com/blog/using-data-driven-images-for-navigating-power-bi-bookmarks

      Using html, data-driven images and bookmarks to dynamically change visuals

      How to use Bookmarks in Power BI for Seamless Report Navigation

      Using bookmarks for pagination/navigation

      http://radacad.com/clear-all-slicers-in-power-bi-a-bookmark-story

      Using bookmarks to clear filters – no pbix

      https://www.wiseowl.co.uk/blog/s2585/clear_slicers.htm

      Good walkthrough on using bookmarks to clear filters – no pbix.

      https://www.youtube.com/watch?v=j-KP1G_c8PQ

      Another good walkthrough on clearing filters (video).

       

      Continue Reading

      Blogging Update

      Where do people find the time to blog?  I am envious and appreciative at the same time.  Blogging takes time, initiative, dedication, and a desire to share.

      Starting a website for your consulting practice is fairly standard.  Blogging allows you to show your knowledge about the topic but also to bring potential clients to your site, build a reputation and credibility, etc.

      When I started blogging, I had high hopes (too high).  I wanted to write about topics that were overlooked or provide more detail on topics that were skimmed over, etc.  During that time, the continued growth of Power BI as an industry leader has made the community grow by leaps and bounds.  With that growth, the number of high quality blogs have also grown.  I find it hard to find topics that are original and don’t want to add one more blog post on the same topic being discussed by a dozen others.  Don’t get me wrong, again, I am appreciative of the other blog posts and the multiple different points of view on the same topic definitely adds color and value.

      To that end, I want to help curate those topics on my site and will point readers to those other high quality articles as I come across them.  I will add any blogging topics I come across from quality sources that I think are valuable to me and should therefore be valuable to the community as well.

      I will add value where I can by posting on any topics I see that can use another perspective or may just be a fun topic.

      This community is very giving and I want to give back as well.  I am working on a few other projects in that vein and am looking forward to promote those as well

      Continue Reading

      Using a Card as a Slicer in Power BI

      Using a card as a slicer in Power BI is a common question I receive.  The quick answer is it is not possible – I hear this much too often from others.  However, that is a very rare statement I would make about Power BI.  It is a rare statement that I use with anything data related.  I feel like so much more is possible if you think outside of the box.  Too many people stop when they cannot find the answer they were looking for.

      I will add a quick posting to show a couple of options on how to work around this “issue”.

      • A customized bar or column chart

      Pro(s): standard chart, looks like a standard card

      Con(s): the bar would only look transparent if your background is a solid color, requires re-sizing for larger data labels

      Customization: add the KPI, add the attribute you want to slice by on the axis, use visual filters if necessary, turn off x and y axis, turn bar color to the same color as your background color, turn on data labels with placement centered inside of bar

      You can’t even tell the difference between the original KPI visual and the bar chart!

       

      • A donut chart overlaying a KPI visual

      Pro(s): standard chart, looks clean

      Con(s): have to click on the donut, cannot click inside of the donut, requires proper sizing of the overlay for largest number expected in the KPI

      • Custom visual ‘Ring Chart’

      Pro(s): easy, looks like donut chart option above but should be cleaner for variable measure lengths, etc.

      Con(s): non-standard chart

      Continue Reading

      Power BI Dashboard Filters

      Power BI Dashboard Filters sometimes cause confusion.

      In one of my many communication channels, there was a question posed related to the ability to add Power BI dashboard filters in order to filter Power BI dashboard tiles.  There is some confusion at times and I would like to make a short article to clear some of them up if possible.

      1 – Tiles pinned to a dashboard are displayed based on the filters applied to them via the filter pane OR via slicers OR via cross filtering at the time of pinning.

      For example, I have a report that is currently unfiltered.

      I pin the top combo chart to my dashboard unfiltered.

      I cross-filter for reseller sales, pin the resulting visual to my dashboard, cross-filter for internet sales, and pin the resulting visual to my dashboard.

      The dashboard will show the tiles as they were at the time of pinning….i.e. one visual unfiltered, one filtered for reseller sales, and one for internet sales.

       

      2 – Use dynamic calendar filters for dynamic dashboards

      Create calculated columns on your calendar table that flag your required date periods – i.e. ‘Current Day’, ‘Current Month’, ‘Current Quarter’, ‘Current Year’, etc. or a Period selection table and measures that utilize the period selection (i.e. http://www.bipatterns.com/select-time-period-with-slicers-and-dax/).  Filter the dashboard utilizing the calculated columns or the Period selector and pin those visuals to your dashboard.

       

      3 – Dashboards do not currently have filters that will allow filtering across tiles

      This makes sense as you are able to have tiles from different reports that do not share filters.

       

      4 – If you would like the ability to filter specific tiles on a dashboard, pin live report pages

      However, design the report page specifically for a dashboard tile.

      For example, create one visual w/ on main slicer:

      Pin this visual to your dashboard and you will have the ability to change the filter on that specific dashboard tile.

      and I can now filter the tile from the dashboard w/o going to the report:

      I hope this helps clear up some confusion or assists you in a project.

      Continue Reading

      Power BI Training Resources (Free)

      Power BI Training

      Power BI Training?  My most common request, by far, is for training resources for Power BI.  There have been some posts I have used to acquire more/better/different options for learning Power BI but I have accumulated, filtered out, and singled out the best resources below.  I will add/modify periodically as new resources are added and if any of these sources become stale.

       

      Power BI Guided Learning

      http://bit.ly/2dcMhRc

      “Start your learning journey through Power BI with this sequenced collection of courses, and understand the extensive and powerful capabilities of Power BI.”  Great Power BI training resource – always kept up to date.

       

      Microsoft YouTube Training Curriculum

      http://bit.ly/2es4nnb

      Full Microsoft provided training curriculum delivered in 100+ short duration YouTube videos.

      Microsoft will point you to these videos when asking for training resources.  They cover all of the different aspects of connecting to data, shaping data, calculated columns, measures, time intelligence, hierarchies, working w/ different visualizations, R, Power BI Services, Q&A, Quick Insights, mobile, dashboards, distribution/sharing, gateways, etc.

      My go to Power BI training resource I send to those that ask.

       

      edX Course – Analyzing and Visualizing Data with Power BI

      http://bit.ly/2dJEez9

      In this data science course, you will learn from the Power BI product team at Microsoft with a series of short, lecture-based videos, complete with demos, quizzes, and hands-on labs. You’ll walk through Power BI, end to end, starting from how to connect to and import your data, author reports using Power BI Desktop, and publish those reports to the Power BI service. Plus, learn to create dashboards and share with business users—on the web and on mobile devices.

      Good Power BI training resource for classroom type learning.

       

      udemy Course – Learn Power BI Basics for Free

      http://bit.ly/2dwiX8Z

      In this tutorial you will be learning all basic knowledge required for Power BI and understanding Power BI component.

      7 Lectures – 2 Hours – Beginner Level

      Good Power BI training resource for classroom type learning.

       

      Microsoft Virtual Academy: Power BI Jump Start

      http://bit.ly/2d7Arws

      A bit dated and I always worry about sending this one as this class shows the Power tools in Excel (Power Query, Power Pivot, Power View, Power Map) and calls them “Power BI”.  This typically causes too much confusion.  As long as you understand that this was the first iteration of Microsoft BI Power tools that were merged and improved into Power BI, it is still beneficial.

      Not really a Power BI training resource specifically, but the Excel Power tools utilize the same functionality.

       

      Microsoft Virtual Academy: Data Visualizations with Power BI in Excel 2013

      http://bit.ly/2dWFduq

      Same comments as above.

       

      eBook – Introducing Microsoft Power BI

      http://bit.ly/2dLaj8x

      “Introducing Microsoft Power BI is a book that covers the basics of the tool and, at the same time, shows you what the main capabilities of Power BI are. The first chapters are similar to a step-by-step guide to start using the service, but the goal is also to show you all available features and the business scenarios covered by them.”

      Good Power BI training resource for those that enjoy learning from a book type resource.

       

      Power Pivot and Power BI Reference Card

      http://bit.ly/2dwvmtI

      Great 8-page reference card from an excellent resource of Power Pivot, DAX, and Power BI knowledge…..mainly addressing best-practices and understanding of DAX.

      Good Power BI training reference.

       

      Webinars – SQLBI

      http://bit.ly/2dcH8Zg

      Videos and webinars from two DAX masters:  Alberto Ferrari and Marco Russo.

      Good Power BI training resource for visual type learning.

       

      Webinars – Microsoft

      http://bit.ly/2egiYCZ

      List of upcoming and on-demand webinars covering introductory training, design concepts, content-packs, and feature-specific concepts from Microsoft and community members.

      Good Power BI training resource for classroom type learning.

      Continue Reading

      Power BI On-Premises

      I wanted to make a quick posting on my (still-ongoing) attempt to push Power BI Services to my large organization but having to deal with my organization’s apprehension to the cloud and desire to have Power BI On-Premises.

      * Note: I will update when I have more information on the options below.

      There are tons of large organizations (and I would dare say medium and small organizations) that have this apprehension.  The cloud!  Look what happened with celebrity photos on iCloud and the data breaches that occur with major credit cards so often!

      What are the options?

      1. Wait (w/ Questions)

      According to the Microsoft road map, what looks to be sometime after SQL 2016 general release, we will have the option to publish Power BI Desktop files to SQL Server Reporting Services.

      Questions:

      • Does this include SSRS in SharePoint integrated mode?
      • What version of SharePoint….will it only be in SharePoint 2016 and beyond?
      • Will it take the Power BI local model and create an SSAS Tabular data model on the back end?
      • What features will be available on-premises?
        • Create a new report via web?
        • Natural Language Q&A?
        • Quick Insights?
        • Personalized Dashboards?
        • Content packs? For that matter, will SSRS utilize the idea of Organizational Content Packs?
      • What mobile capability will there be?
        • Will it still have the QR Code Scanning capability?

      2. Hybrid (w/ Caveats & Questions)

      So, the thought here is to utilize Power BI Services only to interface on-premises data sources for a hybrid environment.  This would appease the apprehension of management as data would not be in the cloud from their point of view.

      • We need more DirectQuery data sources (i.e. Teradata, Oracle, etc.) to be made available via the cloud.
      • There is no current method of restricting users from utilizing a Personal Gateway and/or Enterprise Gateway.  We need this capability.  Perhaps we could set firewall rules to disallow any internal PC besides our designated Enterprise Gateway server?
      • There is no current method to restrict users from importing their own data.  Restricting this goes against my beliefs of the power and promise of Power BI but would appease that apprehension we are speaking of.  I would want the capability to restrict that capability and then turn it on when the masses revolted to management and forced their hand as they saw the benefits of true self-service.
      • Lose Features
        • Q&A does not currently work with on-premises data sources
        • Does Quick Insights work with on-premises data sources (will test)?

      3. Pyramid Analytics (w/ Caveats)

      This sounds like it would give us a viable alternative until we reach that point of “beyond 2016”.  This was announced on July 23rd, 2016.

      • As of today, it is still not released.  The button to publish to Pyramid is there but still says “coming soon”.

      Power BI Pyramid Analytics

      The same questions that I have for SSRS 2016 exist for Pyramid Analytics:

      • Will it take the Power BI local model and create an SSAS Tabular data model on the back end?
      • What are the costs involved?
      • What features will be available with the Pyramid server vs. powerbi.com?  I would assume the following:
        • cannot create a new report via web.
        • no Natural Language Q&A
        • no Quick Insights
        • no utilizing Power BI tiles to create Personalized Dashboards
        • no use for Content packs….would utilize Pyramid security
      • What mobile capability will there be?  Would assume it to be utilizing Pyramid’s mobile application and therefore, not have the QR Code Scanning capability.

      Obviously, Microsoft listens to its consumers and there is a large request for this capability.  As well, they probably know the numbers that would come with large organizations that are apprehensive about the cloud utilizing Power BI on-premises.  It would be huge.  So for now, we are at a wait-and-see moment…..what is next for a truly hybrid environment and what are the trade-offs?

      Is a true Power BI On-Premises with all of the current features a fairy tale?

      Continue Reading

      Microsoft Business Intelligence Roadmap Enhancements

      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.

      Without further ado……

       

      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

      Power BI groups

      Groups offer a powerful collaborative experience built on Office 365 groups.

      Organizational content packs

      Power BI makes creating dashboards and reports extremely simple, and now users can publish this content to the organizational content gallery.

      Real-time data support

      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

      Real-time data support

      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)

      Data alerts

      Set data alerts for your data.

      Collaboration

      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.

       

      New forecasting capabilities

      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 Forecasting

      New and modern charts

      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.

      Excel 2016 Sunburst Excel 2016 Waterfall

      Native Support for Power Map

      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.

      Excel 2016 Pin to Power BI

      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

      Support for Google Chrome

      The SQL Server 2014 release of Reporting Services adds support for the Google Chrome browser.

       

      2016 ENHANCEMENTS

      KPI(s)

      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.

      Reporting Services web portal

      [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.

      SSRS 2016 Web Portal

      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.

      SSRS 2016 Mobile Publisher

      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.

      SSRS 2016 Mobile

      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.

      SSRS 2016 Power BI

      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.

      SSRS 2016 Parameters

      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
       SSRS 2016 Report Builder  SSRS Old Report Builder

      Tree Map and Sunburst Charts

      [CTP 2.3]. Enhance your reports with Tree Map and Sunburst charts, great ways to display hierarchal data.

      SSRS 2016 Tree Map SSRS 2016 Sunburst

       

       

       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).

      [CTP 3.0] Formula Fixup

      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.

      Continue Reading