Articles

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

Twitter Account Created – @powrdax

We now have a Twitter social media presence!

 

@powrdax on TwitterPowerDAX Twitter

@powerdax was not available!  We

Our hope is that we will be able to keep everyone more up to date with Twitter than with articles.  I have such a backlog of articles to share but not very much time to author them.  Twitter will allow us to provide small updates, new and interesting articles, etc. very quickly.

There are so many great people and companies on Twitter and with blogs.  We just hope to bring a different perspective to the market.

Follow us on Twitter and stay updated!

Continue Reading

Power View for Power Pivot / Tabular Data Models

Power View Data ModelPower Pivot or SSAS Tabular data model optimization is important for Power View reporting usability and functionality.

In this posting, we will discuss the following topics:

 

X-Axis Guidelines

Data type and distribution

Utilize a number or date that increases uniformly (primarily for Power BI forecasting but we have seen Power View not normalize the data on the axis due to not being uniform).  Strings are not supported for x-axis values when forecasting.

Power View will typically normalize the date values across the x-axis.  Instead of displaying the date as a date, it will display a partial date (e.g. month year instead of day-month-year).

Advanced Properties

The Power Pivot add-in in Excel allows you to configure advanced properties on the Power Pivot model.  These are configured on the Advanced tab of the Power Pivot model.  This is where you configure:

  • Perspectives (subsets of fields from the model typically defined for particular user groups to improve usability and navigation in large data sets)
  • “Show Implicit Calculated Fields” option (used to show calculated fields that are automatically created when dragging and dropping a field into a Pivot Table that is not an explicitly created calculated field)
  • “Summarize By” option for a column/field (i.e. sum/avg/min/max/etc.).  If you have a field that you do not want to summarize, be sure to set to “Do not summarize” (i.e. Year/Zip or some other numeric column
  • “Reporting Properties” that include the table behavior (details on table behavior below)
  • “Data Category” (ability to define a column/field data type – i.e. geography, image, or URL)
  • “Default Field Set” (set of columns/fields included by default when creating a new table on the Power View canvas and are added automatically when you click on the table.  This is useful if fields need to be included together to make sense to the business user.  The columns are displayed in the defined order but can be changed within each report)

Image Handling

Utilizing images in the Power View report should be done sparingly…and only if it is beneficial to the understanding or usefulness of the visualization / data.  A few things to follow:

  • Column Data Type
    • In order to include the image in the data model itself (JPEG, GIFF or BMP format), the column data type should be binary.
    • In order to save memory, you would include the URL string that points to the location of the image.
  • Data Category
    • Image:  if the column contains the image, which again, must be the Binary column data type
    • Image URL:  if the column contains the URL pointing to the location of the image, which must be the String column data type
  • Default Image
    • See Table Behavior below

Should you utilize an image URL or store the image in memory?  There are pros and cons to each:

Pro Con
In Memory Works w/o Internet
Typically faster
Consumes Memory
(OK if Not Many Images)
URL Less memory Requires Internet
Connection

If you utilize URL(s), remember to utilize short url(s) if possible (i.e. use a URL shortening tool) as this will utilize less memory.  URL(s) also require the http:// prefix but do not necessarily have to include the www prefix if DNS is configured appropriately.

Data Categories

Data Categories allow a method to add semantic knowledge about the content of a table’s column beyond purely its data type.  For example, one can mark a column’s data type but marking columns as Year or Zip provides a little more description and allows Power View to treat the column appropriately.

Later, in Q&A, the data category helps Power BI choose the proper visual display for the column.  For example, for geographical data categories, Q&A will suggest a map visualization.  For dates, it may suggest a line chart or the play axis of a bubble chart.

Table Behavior

Power View acts like a typical Pivot Table.  It will automatically group what is placed in the report field list and on the visualization you are utilizing.  Typically, this will produce optimal results.  However, there are times when a detail table does not need to be grouped at a higher level (where the individual records are of interest and the data does not need to be summarized at a higher granularity).  In these scenarios, table behavior needs to be configured.

Row Identifier

Specifies a column that contains only unique values, allowing that column to be used as an internal grouping key.  This should be set on the unique column in the table (i.e. the business key).  Setting this enables other properties of table behavior such as “Keep Unique Rows”, “Default Label”, and “Default Image”.  This cCn be expensive if configured for a fact table – see Optimize Tabular / PowerPivot Data Model.

Keep Unique Rows

Specifies which columns provide values that should be treated as unique even if they are duplicates (for example, employee first name and last name, for cases where two or more employees share the same name).  This only works for Power View configuration and not a standard Pivot Table.  You should be mindful of this as it could be misleading to the end users to show the same attribute more than once on a visualization.

Default Label

Specifies which column provides a display name to represent row data (for example, employee name in an employee record).  This property specifies the field to use as a label that appears primarily in a card, or together with the default image in the navigation strip of a tile report. When used with a default image, the default label appears under the image.

Default Image

Specifies which column provides an image that represents the row data (for example, a photo ID in an employee record)…..again, primarily used in tiles and/or cards and is useful especially when there are multiple image columns in the table.

 

Field Behavior

Hierarchies

When including a field from a hierarchy, Power View will include all fields from the hierarchy by default.  If you want to utilize a single field, ensure the hierarchy source fields are not hidden (may be listed under “More Fields”).

Sort By Field

For non linear date fields (i.e. month name / day of week), use the sort by field option to utilize a default sort order and Power View will sort these fields properly.

 

Conclusion

That wraps up the Power View for Power Pivot / Tabular data model best practices.  You can also view the Power View for Multidimensional Best Practices.

View other Power View Best Practices!

Power View Data Model Optimization

Power View Data Model

Before a Power View report can be built, you have to have a data model built as a data source.  There are several configuration options to help optimize your data model specifically for Power View.  These configurations and/or optimizations are made within your Power Pivot model or in SQL Server Data Tools on the SSAS Tabular model.

Power View data model optimization is very important for some visualizations to work correctly. We will discuss several of these optimizations in different articles covering the following topics:

  • Power Pivot / Tabular Models
    • X-Axis Guidelines
    • Advanced Properties
    • Image Handling
    • Data Categories
    • Table Behavior
  • Multidimensional Models
    • Measure Groups
    • Dimensions
    • Unsupported Features
    • Other Considerations

DAX Best Practices

DAX

DAX Best Practices 

are vital when utilizing large data models and looking to improve performance.  You can get away with poor practices on small models but you will see performance issues as your data grows.

Data Analysis Expressions (DAX)

is the native query and formula language for Microsoft Power Pivot data models and SQL Server Analysis Services Tabular models. DAX includes some of the familiar Excel functions that you have frequently utilized in Excel formulas, but also includes additional functions designed to manipulate relational data as needed. Much is still to be discovered about DAX optimization. What is known is that it is powerful!

DAX expressions are utilized in writing calculated columns, calculated fields (measures), and in DAX queries performed against a Power Pivot or Tabular data model.

We hope you learn and improve your DAX skills by utilizing our list of best practices. Our articles on DAX Best Practices will include the following topics:

Subscribe to our newsletter and receive new articles as they are posted!  We will likely add to this list of topics as well so don’t miss out!