Articles

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!

Power View Best Practices

Power View Best PracticePower View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. It is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 Enterprise Edition.

Power View in SharePoint is browser-based Silverlight application, a feature of the SQL Server Reporting Services Add-in for Microsoft SharePoint Server. Creating, opening, and saving Power View reports (RDLX files) all can take place in SharePoint Server.

It lets you quickly drag and drop your way to interactive reports and analytical views in seconds. Each chart and graph in the report is simple to format and work together to allow cross-filtering so you can drill into the insights that matter.

– Microsoft

There are not very many articles on Power View best practices. We will consolidate those that we have found and add what we have discovered in real business situations and on projects as well. This will be over several articles so stay informed and subscribe to receive updates!  Article topics will include:

1. Best Practices for Power View Data Models

3. Best Practices for Power View Development

4. Best Practices for Power View Management

The Most Important DAX Concept

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.

Before moving to DAX calculations and before you can understand how those DAX calculations actually work, you have to understand the most important DAX concept.  What is it?  Keeping the calculation simple so others can understand?  No.  Optimization is key though, right?  Right!  But not the most important.

The most important thing to understand is context.  Context enables dynamic analysis against a data model in which the formula results in a set or subset of rows or cells or even a singular cell. This allows a formula to be manipulated based on attribute elements or filters applied against the formula (either from within the formula itself or from the third party tool – i.e. Pivot Tables, slicers, Power View filters or cross-filter selection, etc.). Understanding context usage and utilizing it effectively is the most important DAX concept to grasp.  Without understanding context, your solution may not be optimized to perform the best, may provide incorrect results, and may be too restrictive in its use.

Row Context vs Filter Context (vs Query Context?)

row context

Row context is specific to the current row in a source table.  Row context takes a column as an argument which is interpreted as a single value.

 

 

 

 

Query context (per Microsoft filter contextbut called Filter Context by most others) is the same concept of a cell in a Pivot Table.  Query context is the end result of a filtered calculation.  Query context is the cell value after filters, slicers, column and row values are applied to the calculated field.

 

 

Filter context (per Microsoft) is when you “specify filter constraints on the set of values allowed in a column or table, by using arguments to a formula. Filter context applies on top of other contexts, such as row context or query context.”  Filter context is applied by utilizing a DAX functions such as FILTER, ALLEXCEPT or the ALL function.  Essentially, regardless of the Query context due to filters, slicers, column or row values, the Filter context is still applied (or overrides the filter context).

dax filter

 

Calculated Column vs Calculated Field

A calculated column is calculated after data is loaded.  It can be placed in columns, rows, filters, and slicers.  A calculated column is evaluated in row context.

i.e. [Full Name] = [Last Name] & “, “ & [First Name]

Calculated Column

A calculated field is created in the Power Pivot/Tabular measure grid or from Calculated Fields button on the Power Pivot ribbon.  A calculated field can only be placed in the values area of a Pivot Table / Report.  A calculated field can have Filter context applied (or not) in the definition arguments of the calculation.  Query context is applied to a calculated field once it is placed into a visualization or Pivot Table.  Even if there are no row or column values on the pivot table, the query context is essentially ALL.

i.e. [Sales] = SUM ( Sales[Total] )

Measure Grid

Calculated Field

This is somewhat of an easy concept to grasp….but not to understand in practice.  Once you understand this concept, truly understand this concept, doors will open.  The lights will come on.  Insert whatever cliche you want here.

You will utilize tables as filters, utilize tables as cross filters, and utilize DAX filters and functions in ways unimaginable before truly understanding this concept.  There will be a person, a project, or a moment where you just “get it“.  For me, it was a truly profound moment (or series of moments?) on a particular project.  My coworker (my DAX sensei), who truly thinks completely outside of the box at times, started utilizing multiple tables as “levers”.  It was like watching the old Lionsgate gears introduction….the doors opening to a beautiful world of DAX.

 

Keep at it!  We will be posting quite a bit more on DAX.  There are several other sources out there (will provide a list of those that I frequent the most).  You will get it!  We hope to help you understand the Power of DAX!

 

Microsoft Article on Context

Continue Reading