Articles

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.

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

AdventureWorks Power BI Visualization Samples

Power BI conducted a best visuals contest during the month of September.  Now, they have added a large portion of those visuals to a community gallery and added the ability to download and import those into the Power BI online services or Power BI Desktop.

Click here to see our article on people’s choice awards and top three entries.

Power BI allows the development of custom content utilizing Power BI developer tools. Utilizing the tools, developers can code and deploy custom visuals.  The existing code for current visuals and other developers’ submissions can be seen on the Power BI GitHub site.

On October 19th, 2015, Microsoft created a community site (visuals.powerbi.com) that allows creators to upload new Power BI visuals and for users to browse, select and download those visuals.

As of now, there is not much in the way of documentation so utilizing some of the visuals is difficult.  I will outline my experience with building sample visuals on top of the AdventureWorks 2012 DataWarehouse data.  At the end, I will also include the .pbix file with all of the visuals as well as sample reports built on top of the data regarding Sales, Product data, and Customer profiles.

Here is the end result, but afterwards, I will go through each of the visualization types individually.

First page of standard visualizations:

Power BI Standard Visualizations

 

Second page of standard visualizations:

 

Power BI Standard Visualizations

 

Third page of standard visualizations:

 

Power BI Standard Visualizations

First page of custom visualizations:

 

Power BI Custom Visualizations

Second page of custom visualizations:

 

Power BI Custom Visualizations

Third page of custom visualizations:

 

Custom 3

Individual Standard Visualizations:

100% Stacked Bar Chart

100 Percent Stacked Bar Chart

100% Stacked Column Chart

100 Percent Stacked Column Chart

Area Chart:

Area Chart

Bar Chart:

Bar Chart

Card:

Card

Clustered Bar Chart:

Clustered Bar Chart

Clustered Column Chart:

Clustered Column Chart

Column Chart:

Column Chart

Donut Chart:

Donut Chart

Filled Map:

Filled Map

Funnel:

Funnel

Gauge:

Gauge

Line and Clustered Column Chart:

Line and Clustered Column Chart

Line and Stacked Column Chart:

Line and Stacked Column Chart

Line Chart:

Line Chart

Map:

Map

Matrix:

Matrix

Multi Row Card:

Multi Row Card

Pie Chart:

Pie Chart

Scatter Chart:

Scatter Chart

Slicer:

Slicer

Table:

Table

Treemap:

Treemap

Waterfall Chart:

Waterfall Chart

Individual Custom Visualizations:

Aster Plot:

Published by Microsoft

An Aster plot is a twist on a standard donut chart, using a second value to drive sweep angle.

In this example, Internet Total Sales by Country is utilized.

Aster Plot

Bubbles:

Published by Dharminder Kumar Dhanda

Bubble chart encodes the data in area of circles. Size of the bubble represents measure and multiple bubble represent dimension attribute.

The original contest entry (here and pictured below) was much better visually than what is currently available.  In the current iteration, you are not able to alter individual colors; instead, you are only able to alter the title, label color, or background color.  I hope for an update to this soon.

Bubbles

Bullet Chart:

Published by Microsoft

A bullet chart that includes four orientations and a few customization options. Use to feature a single measure against a qualitative range.

First, you have to have a measure that is a target and a measure that is your actual.  In this case, I utilized sales quota vs reseller total sales.  In order to get your red/yellow/green color scales (colors are able to be customized), you have to have a field/measure for minimum, satisfactory, good, and maximum or enter hard values for each one in the format options.  This visualization has many formatting options.

Bullet Chart

Calendar Visualization:

Published by Elastacloud Limited

Visualize your time series data in an abstract calendar so that you can see heatmap.

As of now, there are no formatting options for this visual.  It definitely needs a scale and it would be nice to at least have color options.

Calendar Visualization

Card with States by SQLBI:

Published by SQLBI

Based on the Power BI builtin Card, this visual allows you to bind a performance value and define up to 3 states that determine the color of the main label. Moreover, the category label is fully customizable.

This visualization is nice….a quick look will tell you the state of the measure.  Allowing measures for the state thresholds would be a nice touch.

Card with States by SQLBI

Chiclet Slicer:

Published by Microsoft

Use this slicer to display image and/or text buttons that act as an in-canvas filter. Define additional properties for the layout & selection to customize this slicer to meet your specific needs.

This is great!  This is a very enhanced version of the Excel and Power View slicers we are all used to.  Added are even more formatting options including colors, borders, layout, and the use of images.  This is definitely done right.

Chiclet Slicer

chiclet2

Chord:

Published by Microsoft

A chord diagram is a graphical method of displaying the inter-relationships between data in a matrix.

This is a nice looking visual but does take a moment to understand the data.  In this case, it is showing the relationships between product category and country by looking at sales total.

 This visualization does not allow drill-down.

Chord

DotPlot:

Published by Microsoft

A dot plot is used to show a representation of the distribution of frequencies. It is most often used to show counts of an occurrence.

This one could use more format options as well….currently, the only options are altering the title and background.

This visualization is filtered appropriately by report/page/visual level filters but does not cross-filter other visualizations if a bucket is selected.  This visualization does not allow drill-down.

Dotplot

Enhanced Scatterplot:

Published by Microsoft

A few more properties were added to the existing scatter chart visual, including shapes as markers, background image support, and developer crosshairs for positioning elements onto an image background.

 This visualization does not allow drill-down.

Enhanced Scatterplot

Enlighten Aquarium:

Published by Enlighten Designs

Data should be simple and engaging. Data should tell a story at a glance. Data should be FUN! Enlighten aquarium tells a story through making your data simple, fun and engaging. It can be used in various situations like a dashboard screen in a room where everyone can see it and notice changes day to day and empathise with the data to rally efforts.

Again, the design contest entry had more features like the second measure would be added as another fish shape.  As well, it would be nice to be able to drill down on the fish to another level.

It seems as though the Aquarium hover does not work.  It shows the first in the list of attributes for each fish but with the correct measure value.  This visualization does not allow drill-down.

Enlighten Aquarium

Hexbin ScatterPlot:

Published by David Eldersveld, BlueGranite

The Hexbin Scatterplot from BlueGranite is a variation of the traditional scatterplot that involves clustering points onto a uniform grid of hexagons. Rather than relying on size to indicate differences in values like a bubble chart, the hexbin plot instead uses variation in bin color similar to a heat map. A higher saturation or darker color indicates higher density when working with two measures. A third measure can be added so that the plot colors change from indicating point density to instead encoding the value of the third measure. Bin radius can also be adjusted.

This visualization has nice formatting options including the color and size of the hexbins, title and background, and whether to show the dots, the hexbins, or the rug.

The hover over of this visualization does not provide any valuable data.  For the value in the ‘Value’ field, it seems like it should provide meaningful data (i.e. mean/median/min/max value) but does not….instead showing the same value for each.  This visualization does not allow drill-down…..it does filter data but not at the hex level, instead at the plot point level.

Hexbin Scatterplot

Histogram:

Published by Microsoft

A histogram chart plots data ranges into intervals. Useful for estimating density.

You are able to specify the number of buckets but it would be a nice addition to allow for custom sizing of the buckets.  As well, it would be nice to show a legend showing the scale of each bucket and different colors for each bucket.

This visualization is filtered appropriately by report/page/visual level filters but does not cross-filter other visualizations if a bucket is selected.

Histogram

KPI Indicator with Status, Deviation, and History:

Published by Fredrik Hedenström

This visualization is all about visualizing Key Performance Indicators. The status is presented as a color indication, comparing the actual and target values. Deviation is presented as distance in percent of actual from target. The history (trend) is presented as a line or a bar chart. It is up to the user to decide the granularity of the data displayed. Any dimension attributes can be used, but it’s recommended to stick to the ones in your date dimension.

This incorporates one of the best features of Datazen into Power BI.  This makes it very easy to see the status of a KPI as well as the history of the measure.  Improvements could be made to not require text entry for each of the General formatting options.  For example,

Banding Type (IIB, DIB, or CIB) looks to specify whether an increase is good or bad and show green or red accordingly.

Banding compare type (ABS or REL) looks to round the deviation if REL is entered while ABS shows the true deviation number to all decimal places (typically, not preferred and takes up real estate).

Chart type (LINE or BAR) shows the measure history as a line chart or a bar chart.

These should all be drop down selectors with more understandable options.

KPI Indicator

Radar Chart:

Published by Microsoft

A simple radar chart supporting multiple measures plotted over a categorical axis. Also known as a web chart, spider chart, or star chart. Use to display performance metrics for quality improvement.

You need a little real estate for this one to be visually appealing or useful.  The only formatting options are title and background.  Adding the ability to change colors would be a nice addition.

This visualization is filtered appropriately by report/page/visual level filters but does not cross-filter other visualizations if a bucket is selected.  This visualization does not allow drill-down.

Radar Chart

StreamGraph:

Published by Microsoft

A stacked area chart with smooth interpolation. Often used to display values over time.

This visualization does not have hover over information and it would be nice to have the category as the x-axis to make more sense.

Streamgraph

Sunburst:

Published by Microsoft

Sunburst is a multilevel donut chart, used to visualize hierarchical data, depicted by concentric circles.

The only formatting options are title and background.  Adding the ability to change colors and possibly size of each level would be a nice addition.

Sunburst

Synoptic Panel:

Published by SQLBI

The Synoptic Panel connects areas in a picture with attributes in the data model, coloring each area with a state (red/yellow/green) or with a saturation of a color related to the value of a measure. Starting from any image, you draw custom areas using http://synoptic.design/, which generates a JSON file you import in the Synoptic Panel. You can visualize data over a map, a planimetry, a diagram, a flow chart….

I started with a much more difficult synoptic panel (map of the United States in order to create regions) but the tool was not very conducive to non-linear areas (or I was too impatient).  Therefore, I went with a very simple example of squares aligned to product category.  Again, a great visualization and adds lots of capability when thinking about utilizing portions images to drive filtering (see @5h15h‘s example in his tweet).  This visualization includes lots of great formatting options.  My only suggestion would be the allowance of measures for saturation.

Synoptic Panel

Table Heatmap:

Published by Galiya Warrier

You can use this custom visual to build a table heat map that can be used to visualise and compare data values in an easy and intuitive way. You have a built-in option within this visual to specify the number of buckets used for splitting your data. Additionally, you can also customise it by choosing a colour scheme in line with your brand colours.

Another great visualization but limited on formatting options (title, background, colors, and number of buckets).  Again with this visualization, in order to see column headers and buckets, you need more real estate for this visual.  The entry of colors is text based and I had to go to the contest entry page to get the variable options for both colors and buckets:

– Available color schemes options:
“Purples”, “Blues”, “Greens”, “Oranges”, “Reds”, “Greys”, “YlGn” (Yellow-Green), “GnBu” (Green-Blue), “BuGn” (Blue-Green), “PuBu” (Purple-Blue), “BuPu” (Blue-Purple), “RdPu” (Red-Purple), “PuRd” (Purple-Red), “OrRd” (Orange-Red),
“PuOr” (Purple-Orange), “BrBG”, “PRGn”, “PiYG”, “RdBu”, “RdGy”, “RdYlBu”, “Spectral”, “RdYlGn”, “Paired”, “Pastel1”, “Pastel2”

– Accepted value for number of buckets: 3,4,5,6,7,8,9

This visualization is filtered appropriately by report/page/visual level filters but does not cross-filter other visualizations if a bucket is selected.

Table Heatmap

Tadpole Spark Grid:

Published by Angry Koala Pty Ltd

This visualization draws a grid (using Matrix data) of spark lines which have a particular color scheme.  Each line segment represents a period (such as month) and a measure value.  The line segments are colored (black-positive) and (red-negative) based on a comparison of the current period value with the previous period value.  Usually up for positive and down for negative.  There are 2 toggle switches in the visualization, one called “less is good” will switch the negative and positive tests making down good.  The other toggle “overlay mode” can take two measures such as last period and current period or budget and actual and overlay the two.  The first measure will be in light grey and the second measure will be colored based on the comparison between the two measures rather than just the previous period.  In order for this visualization to work correctly, the time dimensions must be the last place in the list of dimensions.

This visualization provides historical sparklines in a matrix style grid….pretty cool.  Again limited on formatting options (title, background, and less is good/bad to change red/grey color when increasing/decreasing) and it would be nice for options for colors or layout options (i.e. moving columns to rows nested under categories).

This visualization is filtered appropriately by report/page/visual level filters but does not cross-filter other visualizations if a bucket is selected.

Tadpole Spark Grid

Tornado:

Published by Microsoft

A bar chart with category values listed vertically. Use for comparing the relative importance of a variable between two distinct groups.

Another great visualization from Microsoft with a good set of formatting options.  For this visualization, you compare two measures against one dimension.  Add one dimension to the Group field and two measures to the Values field.

This visualization is filtered appropriately by report/page/visual level filters but does not cross-filter other visualizations if a bucket is selected.

Tornado

 

Again, I am hoping for documentation to be added to the visual gallery.  Hopefully, this will help you get started on using these.  Examples are in this sample .pbix file using AdventureWorks data: download.

I am also interested in how they will handle updates to the code.  Will Microsoft take over the development of some of these or will the community add to the base code as needed?  Who has the final say in what feature is added?  It is interesting to think through.  Will users of the custom visualizations be notified if their .pbix file contains custom visualizations and there is an update available?

Also, if anyone from Microsoft reads this, don’t make users enable custom visualizations each time they go away from a published report on powerbi.com and come back to it…..minor nuisance but still a nuisance.

 

 

Continue Reading

Power BI Visual Contest

Power BI conducted a best visuals contest (http://community.powerbi.com/t5/Best-Visual-Contest/con-p/best_visual_contest) during the month of September.  There were around thirty entries in the contest.  It was impressive to say the least.

Winners were announced on October 8th, 2015 and included three people’s choice awards, a third place winner, a second place winner, and a grand prize winner:

1st People’s Choice Award

Bullet Chart by SQLBI

Code: https://github.com/danieleperilli/PowerBI-visuals

2nd People’s Choice Award

Power BI Aquarium

 

Code: https://github.com/enlightendesigns/PowerBI-visuals

3rd People’s Choice Award

Breakdown Trees

The Breakdown tree makes it possible to visually display the full drill-down path of a measure. By keeping all levels in the drill-down path visible you will get a good overview of how your numbers break down, from the top level to the details at the bottom.

Code: https://github.com/fredrikheden/PowerBI-visuals

3rd Place Award

Hexbin Scatterplot

The Hexbin Scatterplot for Power BI is a variation on a traditional scatterplot that involves clustering points onto a uniform grid of hexagons. Rather than relying on size to indicate differences in values like a bubble chart, the hexbin plot instead uses variation in bin color similar to a heat map. A higher saturation or darker color indicates higher density when working with two measures. A third measure can be added so that the plot colors change from indicating point density to instead encoding the value of the third measure. Bin radius can also be adjusted.

Code: https://github.com/deldersveld/PowerBI-visuals

2nd Place Award

KPI indicator with status, deviation and history*.

This visualization is all about visualizing Key Performance Indicators. The status is presented as a color indication, comparing the actual and target values. Deviation is presented as distance in percent of actual from target. The history (trend) is presented as a line or a bar chart. It is up to the user to decide the granularity of the data  displayed. Any dimension attributes can be used, but it’s recommended to stick to the ones in your date dimension.

Code: https://github.com/fredrikheden/PowerBI-visuals 

Grand Prize Award

Synoptic Panel by SQLBI

The Synoptic Panel connects areas in a picture with attributes in the data model, coloring each area with a state (red/yellow/green) or with a saturation of a color related to the value of a measure. Starting from any image, you draw custom areas using http://synoptic.design/, which generates a JSON file you import in the Synoptic Panel. You can visualize data over a map, a planimetry, a diagram, a flow chart.

 

Code: https://github.com/danieleperilli/PowerBI-visuals

Opening the application API and allowing customization is one of the features that will push Microsoft ahead of the competition.

See our next article to see how Microsoft utilized these new visualizations!

 

Continue Reading

Power BI Lost Features & Functionality Tracking

With the most recent release of Power BI Desktop / Services and removal of dependencies on Excel, we have lost several features and functionality.  However, the gains and improvements far outweigh the losses.  We gained funnel charts, gauges, donut charts, filled maps, treemaps, waterfall charts, dual axis line and column charts, area charts, custom titles, custom colors, etc.

Really, we need to compare Power BI Services & Desktop with Power Pivot and Power View for both Excel and SharePoint.  I will attempt to list all known limitations of the current iteration of Power BI and tie them to Power BI support improvement suggestions:  https://support.powerbi.com/forums/265200-power-bi

As improvements and updates are made to the Power BI Desktop and Services, we will update and try to keep track of the lost features as we gain them back in future releases.

Below is a table of a comprehensive (hopefully exhaustive) list of Power BI lost features in the current iteration, a short description of the feature/functionality, a link to a site that outlines the feature/functionality, and a link to the improvement suggestion on the Power BI site (if applicable).  We will likely enter in suggestions for those that do not have one (n/a below).

Continue Reading

How to Profile SQL Server Data Using Power BI

With the announcement of General Availability for Power BI 2.0, Microsoft made BI to the masses possible. There were some steps back (chart/map drill down, tile slicers, etc.) but many more steps forward. They removed the reliance on Excel, added formatting options, new visualizations, and more.

With the new release, we wanted to create a dashboard so we started with SQL Server data profiling.  This helps in analyzing your data source for optimizing the data model.  What columns have high or low cardinality?  What is the uncompressed data size?  What are the data types of high cardinality columns?  This helps in analyzing the data to see if data types need to be modified, if columns need to be split, if tables are at a lower granularity than needed, etc.

Here is the sample dashboard created on top of the data model using the AdventureWorks Data Warehouse database AdventureWorksDW2012:

Power BI SQL Metadata

We included the SQL Server logo (this same method with altered SQL could be used for Oracle, Teradata, etc.), the number of schemas for the database, the number of tables, the number of columns, the size in MB for each table, the number of distinct elements and corresponding size by column, the number of distinct elements by table and column in a heat map, and a data grid with corresponding information.

We created a stored procedure to extract this data but it could also be passed in as a query to the SQL Server database.

Download SQL Here

declare @results table
(
ID varchar(36),
SchemaName varchar(250),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
Shortest int,
Longest int,
Minimum varchar(1000),
Maximum varchar(1000),
NullCount int,
NotNullCount int,
DistinctCount int,
SizeMB decimal(12,2),
FullSQL varchar(1500),
FullSQLBit varchar(1500)
)
INSERT INTO @results(ID,SchemaName,TableName,ColumnName,DataType,MaxLength,Shortest,Longest,Minimum,Maximum,NullCount,NotNullCount,DistinctCount,SizeMB,FullSQL,FullSQLBit)
SELECT
NEWID() as [ID],
OBJECT_SCHEMA_NAME(TB.[object_id],DB_ID()) AS [SchemaName],
Object_Name(c.object_id) as [TableName],
c.name as [ColumnName],
t.Name as [DataType],
case
when t.Name not in (‘varchar’,’nvarchar’,’nchar’) Then ‘NA’
when c.max_length = -1 then ‘Max’
else CAST(c.max_length as varchar)
end as [MaxLength],
0 as [Shortest],
0 as [Longest],
0 as [Minimum],
0 as [Maximum],
0 as [NullCount],
0 as [NotNullCount],
0 as [DistinctCount],
0 as [SizeMB],
CASE WHEN t.Name = ‘bit’ THEN NULL ELSE
‘SELECT ”’+t.Name+”’ as [DataType],
Min(Len(‘ + c.name + ‘)) as [Shortest],
Max(Len(‘ + c.name + ‘)) as [Longest],
case when ”’+t.Name+”’ = ”varbinary” then ”NA” else Min(‘ + c.name + ‘) end as [Minimum],
case when ”’+t.Name+”’ = ”varbinary” then ”NA” else Max(‘ + c.name + ‘) end as [Maximum],
SUM( CASE WHEN ‘ + c.name +’ IS NULL THEN 1 ELSE 0 END) as [NullCount],
SUM( CASE WHEN ‘ + c.name + ‘ IS NOT NULL THEN 1 ELSE 0 END) as [NotNullCount],
COUNT(DISTINCT ‘ + c.name + ‘) as [DistinctCount],
SUM(DATALENGTH(‘ + c.name + ‘)) / 1048576.0 as [SizeMB] FROM ‘ + OBJECT_SCHEMA_NAME(c.object_id) + ‘.’ + Object_Name(c.object_id) END as [FullSQL],
CASE WHEN t.Name = ‘bit’ THEN
‘SELECT ”’+t.Name+”’ as [DataType],
Min(Len(‘ + c.name + ‘)) as [Shortest],
Max(Len(‘ + c.name + ‘)) as [Longest],
Min(‘ + c.name + ‘+0) as [Minimum],
Max(‘ + c.name + ‘+0) as [Maximum],
SUM( CASE WHEN ‘ + c.name +’ IS NULL THEN 1 ELSE 0 END) as [NullCount],
SUM( CASE WHEN ‘ + c.name + ‘ IS NOT NULL THEN 1 ELSE 0 END) as [NotNullCount],
COUNT(DISTINCT ‘ + c.name + ‘) as [DistinctCount],
SUM(DATALENGTH(‘ + c.name + ‘)) / 1048576.0 as [SizeMB] FROM ‘ + OBJECT_SCHEMA_NAME(c.object_id) + ‘.’ + Object_Name(c.object_id) ELSE NULL END as [FullSQLBit]
FROM
sys.[tables] AS TB
INNER JOIN sys.[all_columns] C ON TB.[object_id] = C.[object_id] INNER JOIN sys.[types] T ON C.[system_type_id] = T.[system_type_id] AND C.[user_type_id] = T.[user_type_id] WHERE
tb.name not in (‘DatabaseLog’)
–select * from @results order by MaxLength desc
—————————————-COMBINE ALL (MAX,MIN,COUNT) INTO SINGLE SQL
–LONGEST
DECLARE @id varchar(36)
DECLARE @fullsql varchar(1500)
DECLARE @fullsqlbit varchar(1500)
DECLARE @receiver table(
DataType varchar(250),
Short varchar(250),
Long varchar(250),
Minimum varchar(1000),
Maximum varchar(1000),
NullCount varchar(250),
NotNullCount varchar(250),
DistinctCount varchar(250),
SizeMB decimal(12,2)
)
declare @receiverbit table(
DataType varchar(250),
Short varchar(250),
Long varchar(250),
Minimum varchar(1000),
Maximum varchar(1000),
NullCount varchar(250),
NotNullCount varchar(250),
DistinctCount varchar(250),
SizeMB decimal(12,2)
)
DECLARE length_cursor CURSOR
FOR SELECT ID, FullSQL, FullSQLBit FROM @results –WHERE MaxLength != ‘NA’
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @fullsql, @fullsqlbit
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (DataType,Short,Long,Minimum,Maximum,NullCount,NotNullCount,DistinctCount,SizeMB)
exec(@fullsql)
UPDATE @results
SET Shortest = (SELECT Short FROM @receiver),
Longest = (SELECT Long FROM @receiver),
Minimum = (SELECT Minimum FROM @receiver),
Maximum = (SELECT Maximum FROM @receiver),
NullCount = (SELECT NullCount FROM @receiver),
NotNullCount = (SELECT NotNullCount FROM @receiver),
DistinctCount = (SELECT DistinctCount FROM @receiver),
SizeMB = (SELECT SizeMB FROM @receiver)
WHERE ID = @id
AND DataType <> ‘bit’
DELETE FROM @receiver
INSERT INTO @receiverbit (DataType,Short,Long,Minimum,Maximum,NullCount,NotNullCount,DistinctCount,SizeMB)
exec(@fullsqlbit)
UPDATE @results
SET Shortest = (SELECT Short FROM @receiverbit),
Longest = (SELECT Long FROM @receiverbit),
Minimum = (SELECT Minimum FROM @receiverbit),
Maximum = (SELECT Maximum FROM @receiverbit),
NullCount = (SELECT NullCount FROM @receiverbit),
NotNullCount = (SELECT NotNullCount FROM @receiverbit),
DistinctCount = (SELECT DistinctCount FROM @receiverbit),
SizeMB = (SELECT SizeMB FROM @receiverbit)
WHERE ID = @id
AND DataType = ‘bit’
DELETE FROM @receiverbit
FETCH NEXT FROM length_cursor
INTO @id, @fullsql, @fullsqlbit
END
CLOSE length_cursor
DEALLOCATE length_cursor
SELECT
SchemaName,
TableName,
ColumnName,
DataType,
MaxLength,
Shortest,
Longest,
Minimum,
Maximum,
NullCount,
NotNullCount,
DistinctCount,
SizeMB
FROM
@results

We then renamed columns to make Q&A more user friendly, added measures to their own blank query to delineate dimensions in the main query and measures in the blank query, and hid columns.

At this point, you can create your reports, add text boxes if needed, any images, slicers if needed, and create your visualizations on top of the data.

Once created, we uploaded to our PowerBI.com service online and pin visualizations or images to our newly created ‘SQL Metadata’ dashboard. If you need to keep it up to date because data is changing, download and install the Power BI Personal Gateway application.

Download the .pbix here!

Continue Reading