Data Visualization, Microsoft Technologies, Power BI

Design Concepts for Better Power BI Reports Part 3: Gestalt Principles

The Gestalt principles of visual perception describe how humans tend to organize visual elements into groups or unified wholes to help us make sense of visual stimuli. Basically, we perceive the visual world as complete objects rather than a bunch of independent elements.

For instance, you probably see a triangle in the white space in the first image below. And in the second image, you probably see a rectangle.

Your brain is simplifying visual elements into what it commonly finds them to be (the triangle and rectangle).

There are 8 Gestalt principles that are particularly relevant to data visualization:

  • Proximity: We perceive objects that are close to each other as being in the same group
  • Similarity: We perceive visually similar (by color, shape, etc.) as being in the same group.
  • Closure: We perceive objects as being whole even when they are not complete (such as the rectangle above).
  • Symmetry: We tend to perceive objects as being symmetrical and pivoting around a center point.
  • Common Fate: Objects are perceived as lines that move along the smoothest path
  • Continuity: We perceive objects that are aligned with each other as being in a single group or object, creating a single path or shape rather than multiple distinct paths or shapes.
  • Good Gestalt/Prägnanz: We like to find simple patterns. We perceive objects as related if they form a pattern that is regular, simple, and orderly.
  • Past Experience: We categorize visual stimuli based upon our past experience. If objects are usually perceived close together in time or space, we tend to see them as being related. This is how we recognize letters and words.

Here’s a 20-second video I found that illustrates many of these principles.

None of the Gestalt principles work in isolation – you’ll usually find multiple principles at work simultaneously.

Gestalt Principles in Power BI

Gestalt principles can help us highlight patterns and reduce noise in data visualization. They can also help us create a visual hierarchy and employ symmetry in our designs for a more pleasing user experience.

Some Gestalt principles are very similar to our understanding of preattentive attributes. This is demonstrated in the set of 3 charts below.

GestaltContinuity1
This chart is not following the Gestalt Principle of Continuity
GestaltContinuity2
Chart rearranged to better follow the Gestalt Principle of Continuity
GestaltContinuity3
Chart rearranged to follow the Gestalt Principles of Continuity and Similarity

In the top chart, bars are ordered alphabetically by product name. In the middle chart, we changed the bar order to be descending by sales amount. This satisfies our need for the trend to make a smooth descending path as seen in the Gestalt Principle of Continuity. In the third chart, we have changed the color of the bars to all be the same. The color of the bars didn’t have any special meaning, and the entire chart used the same categorical axis and measure. This follows the Gestalt Principle of Similarity. Instead of seeing individual bars, we now see them more as a group of bars.

Here’s another example.

GestaltProximity

In the top chart, try comparing sales for each product within a quarter. Which product had the highest sales in Q3? This should be fairly easy. Now try using the top chart to compare the sales for Product B over time. It’s a bit more difficult because you have to pick out the blue bar in each cluster.

Now try using the bottom graph to examine sales for Product B over time. It should be much easier because the bars for each quarter are close together.

You can see these charts on PowerBI.com here.

What Does This Mean For Report Design?

Here are some things you can do to take advantage of Gestalt Principles in your Power BI reports:

  1. Check your placement of charts on the report page. Are charts that are closer to one another actually more related than other charts that are not as close together? Are you leaving enough white space between visuals so that we can tell where one visual ends and the next begins?
  2. Be sure to group things appropriately for the most common analysis to be performed, such as with the clustered bar chart example above. Proximity of objects within a graph is important.
  3. In simple bar charts with one category and one measure, use only one color on the bars, unless you are purposefully trying to highlight a trend, grouping, or data point.
  4. Make sure your colors have consistent meaning. If you are using light blue to show sales in one chart, try not to use that same light blue to show expenses in another chart on the same page, unless all your charts on the page or in that section use that color for a specific reason. (Note: It’s possible to make a nice Power BI report with only two colors. I’m not suggesting you use multiple colors all the time. But if it makes sense to use multiple colors, make sure your colors have consistent meanings because those colors imply grouping.)
  5. Order bars/columns by descending or ascending value when not trying to show trend over time.
  6. Use low saturation or neutral hue colors as backgrounds in charts so data points stand out more than the background.
  7. Be careful with network graphs. In these graphs, proximity does not necessarily indicate similarity, and spatial position does not always have a meaning. It’s just an algorithm determining where to place objects within the chart. Check for any false conclusions that may be drawn by these properties and change your chart or add explanatory text to help users understand what information you are trying to convey.

More Information

You can find more information about Gestalt Principles at the following links:

 

This post is part of a series. Go here to see the list of posts in the series. 

Data Visualization, Microsoft Technologies, Power BI

Design Concepts For Better Power BI Reports – Part 2: Preattentive Attributes

Preattentive attributes are visual properties that we notice without using conscious effort to do so. Preattentive processes take place within 200ms after exposure to a visual stimulus, and do not require sequential search. They are a very powerful tool in your data visualization tool box – they determine what your audience notices first when they look at your Power BI report.

Four preattentive visual properties have been defined:

  • Color (intensity, hue)
  • Form (orientation, line length, line width, size, shape, curvature, enclosure, added marks)
  • Spatial Positioning (2-D position)
  • Movement

Below is a short video I found that demonstrates preattentive attributes.

 

 

Preattentive Attributes in Power BI

Every chart you build in Power BI uses preattentive attributes, but you must make design choices to use them purposefully. Here are some quick examples.

Preattentive Color Length 1
Preattentive Attributes: Color and Length
Preattentive Color Length 2
Preattentive Attributes: Color and Length

The chart on the top uses a different color for each bar and orders the bars alphabetically by product. The chart on the bottom uses a single color across all bars and orders the bars descending by sales amount. Notice how your eyes jump back and forth between the colors in the chart on the top. When you look at the bottom chart, your eyes more easily follow the length of the bars down and across the categories from largest to smallest.

Here’s another example.

Preattentive Orientation Enclosure 1
Preattentive Attributes: Enclosure and Orientation
Preattentive Orientation Enclosure 2
Preattentive Attributes: Enclosure and Orientation
Preattentive Orientation Enclosure 3
Preattentive Attributes: Enclosure and Orientation

The vertical bar chart on the top has a dark black border. It’s probably the first thing you notice about the chart.

Once we remove the border, as shown in the chart in the middle, we notice that the chart category labels have a diagonal orientation. They stand out because nothing else in the chart is diagonal. It’s a bit distracting and difficult to read.

The horizontal bar chart shows the same information in the same order, but allows the category labels to remain horizontal. Now our eyes focus on the information encoded by the bars.

Click here to see these examples in Power BI.

What Does This Mean For Report Design?

We need to take advantage of the way we process information to create a faster and more natural way of acquiring information through our Power BI reports. Specifically, we can use preattentive attributes to highlight the most important parts of a visual and to create a visual hierarchy of information. Color is probably the most powerful preattentive attribute we have at our disposal, so we should use it strategically.

Here are some things you can do to take advantage of preattentive attributes in your Power BI reports:

  1. Reserve the use of bright colors for items that need attention from your users or those that should be examined first, and use less intense colors for other items on the page.
  2. Don’t use multiple colors for the sake of having several colors. For instance, a bar chart with only one field on the categorical axis generally doesn’t need to have separate colors for each bar.
  3. Don’t settle for rotated axis labels when they won’t fit horizontally. Abbreviate categories and numbers, or switch to a different chart type that supports longer labels if you need to do so.
  4. Start bar charts at 0 to allow your users to accurately evaluate length and differences between bars.
  5. Make sure visuals in a row are exactly aligned. If charts in a row are slightly misaligned by a few pixels, it can be distracting.
  6. Don’t let chart title be the brightest/boldest thing on your page. Let your data in your charts, KPIs, and cards draw the most attention. In many cases, you don’t need a background color on your chart titles.
  7. Avoid adding dark, intense chart borders. Try using whitespace to separate charts rather than adding borders.

There are times when chart borders and background colors on chart titles are appropriate. (I’m currently working on a report where I have a border around a group of charts to indicate that they are all related, but it is light gray rather than black.) But they definitely aren’t appropriate all the time, so I suggest your default be to avoid them and add them when necessary.

Sometimes it’s difficult in Power BI to find a chart that will accommodate even slightly long category labels, since the built-in visuals truncate the values on the axis with no setting to change that behavior. I ask that you lend your votes and comments to User Voice to help change that. You can vote here or here. Until that issue is resolved, you might try the Attribute Slicer custom visual. Although it has some formatting quirks of its own, it has a setting to define the portion of the chart that should be taken up by the bars versus the labels.

I have provided several guidelines that are not hard and fast rules. The goal of these guidelines is to help you use color, form, and position to guide your users through your report in an efficient manner, to help them process the information your report provides.

 

This post is part of a series. Go here to see the list of posts in the series. 

Data Visualization, Microsoft Technologies, Power BI

Design Concepts To Help You Create Better Power BI Reports

I have decided to write a series of blog posts about visual design concepts that can have a big impact on your Power BI Reports. These concepts are applicable to other reporting technologies, but I’ll use examples and applications in Power BI.

Our first design concept is cognitive load, which comes from cognitive psychology and instructional design. Cognitive Load Theory says that when we present our audience with information, we are asking them to use brain power to process it. That brain power (aka working memory) is limited, so we need to be intentional about the information we present to them.

In order to commit information to memory and be able to recall it later, that information must go through sensory memory and working memory and then be encoded into long-term memory.

Image from MindTools Cognitive Load Theory: Helping People Learn Effectively (https://www.mindtools.com/pages/article/cognitive-load-theory.htm)

This process is not automatic nor guaranteed. There is a major constraint imposed upon us in that our working memory can only hold about 4 things at once.

Cognitive load theory identifies schemas, or combinations of elements, as the cognitive structures that make up an individual’s knowledge base. Schemas allow us to treat multiple elements as a single element in order for us to think and solve problems. For schema acquisition to occur, information delivery should be designed to reduce working memory load. Cognitive load theory is concerned with techniques for reducing working memory load in order to for our minds to build new schemas.

Cognitive load can be categorized into three types:

  • Intrinsic cognitive load refers to the complexity of the information itself.
  • Extraneous cognitive load refers to the way the material is presented.
  • Germane cognitive load is the effort a person must expend to learn and build new schemas.

Here’s a short video I found that does a nice job of explaining cognitive load:

What Does This Mean For Report Design?

We need to design such that our audience can efficiently take in the information we are visualizing, commit that information to memory, and use that information to make decisions. This means we should be aware of our audience and their existing knowledge of the information we are presenting. It also means reducing extraneous cognitive load by keeping our design simple and clutter free.

Here are some things you can do to minimize cognitive load in your Power BI report:

  1. Choose a message/purpose for your report and don’t allow anything on the canvas that can’t be tied back to that message. We can’t just say we are building a financial dashboard for our company and put all of our financial metrics on the page. We need to choose which metrics are important and which ones go together in meaningful chunks.
  2. Create charts that take into account your audience and how they think about the subject of your report. If your audience might not know how to approach the subject matter of your visualization, you may need to add supplemental information (either in the report or as links) so they can begin to build schemas to help them think about the subject. If your audience has existing knowledge, use their terminology and approach to thinking about the subject as much as possible so they are building upon what they know.
  3. Remove clutter. Eliminate things from your report that do not make the information memorable. This could include removing decorative elements that do not support information intake (this doesn’t mean remove all color and images, just extraneous ones that distract more than help). Make sure you aren’t using super intense colors everywhere, which makes your report feel busy and makes it difficult to know where to look first. Also, remove redundant information. If you are direct labeling your charts, you probably don’t need gridlines and axis labels. Descriptive chart titles often eliminate the need for axis titles.
  4. Use consistent designs as much as possible, so users don’t have to refer to a guide for each new report you build. This can be applied by putting slicers in a similar location across reports, or using the same color for revenue in reports that show multiple metrics. This removes the cognitive burden of learning how the report works so users can focus on the information in the report.

In addition to paying attention to actual cognitive load, we should also think about perceived cognitive load – how much effort our users think it will take to consume our report. Our users are constantly being distracted by coworkers and children and cell phones, and Dog Rates. They have limited time and energy to consume our reports. If the report looks busy and complicated, or extremely aesthetically unpleasing, they may perceive that the task is not worth the effort and move on without looking at the report we spent hours building. Remember that we are designing for a specific audience, and it is their information needs and their perception of our report that matters more than our own design preferences.

 

This post is part of a series. Go here to see the list of posts in the series. 

Biml, Microsoft Technologies, SSIS

Biml for a Task Factory Dynamics CRM Source Component

I recently worked on a project where a client wanted to use Biml to create SSIS packages to stage data from Dynamics 365 CRM. My first attempt using a script component had an error, which I think is related to a bug in the Biml engine with how it currently generates script components, so I had to find a different way to accomplish my goal. (If you have run into this issue with Biml, please comment so I know it’s not just me! I have yet to get Varigence to confirm it.) This client owned the Pragmatic Works Task Factory, so we used the Dynamics CRM source to retrieve data.

I was ultimately creating a bunch of packages that looked like the below.

There are two pieces to using the Task Factory Dynamics CRM Source: the source component and the connection manager. The code for both is below.

Things to note for the source component:

  •  This is pulling data for an entity in Dynamics 365. If you were to populate this by hand, the Task Factory UI would ask you which entity. There are several entities that are visible, but do not allow you to retrieve the data. You can find a list of internal entities here.  You won’t want to stage data from those tables. I generated all of my packages at once with Biml, using a Blacklist to exclude the internal entities.
  • The entity name that should be the value on line 21 is the entity logical name.
  • I’m iterating through columns in tables in a separate file and holding them in memory, hence the references to Table.columns. You’ll need to write some code to get the entity metadata to feed that, or find another way to provide the table names and column names and data types.
  • You must set the ErrorRowDisposition and TruncateRowDisposition to “NotUsed” as I did on line 10 for each column or your package will not work.
  • In OutputColumns and ExternalColumns collections, I just have if statements to check data types. There may be a more elegant way to do this, but this worked for me and I went with it. Feel free to leave suggestions in the comments if you have a better idea.
  • The Connection element on line 145 should keep the name “DYNAMICSCONNECTION”. If you change it, the connection won’t work. In my actual project, my connection manager is in a separate file and is named “TF_DynamicsCRM”. You can set the ConnectionName property to whatever you want, as long as it matches your connection definition (Line 153 in the gist).

Things to note for the Connection Manager:

  • I tried reverse engineering the connection manager in Biml Studio, and that got me close, but it didn’t get the ObjectData property quite right. I ended up having to create one manually, view the code for it in SSDT, and copy it into my Biml file. I have my packages and project set to EncryptSensitiveWithPassword, which is why you see the p4:Salt value on line 158. Your connection manager will have different values for lines 158, 159, and 160. If you set your project to EncryptSensitiveWithPassword, this value will stay consistent across developers. But if you use EncryptSensitiveWithUserKey, the value may change, which will be fun for regenerating with Biml. So make sure you plan for that if you have multiple developers or multiple computers on which you are working.
  • This connection manager is set to connect to Dynamics 365, hence the ServerHost=disco.crm.dynamics.com. If you have an on-prem or hosted environment that isn’t 365, you’ll have to find the correct ServerHost value and put it in line 165.

I hope that helps someone looking to generate the Task Factory Dynamics Source with Biml.

Conferences, Data Visualization, Microsoft Technologies, Power BI

Data Visualization Panel at PASS Summit

Next week is PASS Summit 2017, and I’m excited to be a part of it. One of the sessions in which I’m participating is a panel discussion on data visualizationMico Yuk will be our facilitator. I’m in great company as the other panelists are Ginger Grant, Paul Turley, and Chris Webb. This session will be on Wednesday (November 1) from 4:45pm – 6:00pm.

We’ll be taking questions on Slack in the #visualization of sqlcommunity.slack.com. So if you need advice or have been curious about some aspect of data viz, join us in room 2AB and send us your question via Slack.

If you are curious about my views on data viz, I wrote a sort of beginner’s guide for data viz in Power BI in the book Let Her Finish: Voices from the Data Platform (Volume 1).

I hope to see you at PASS Summit!

Books, Conferences, Microsoft Technologies, Power BI

Let Her Finish: Voices from the Data Platform

This year I had the pleasure of contributing a chapter to a book along with some very special and talented people. That book has now been released and is available on Amazon!  Both a digital and print version are available. My chapter is about data viz in Power BI, combining platform agnostic concepts with practical applications in Power BI.

The other chapters are:

  • Azure Data Catalog by Melody Zacharias (b|t)
  • Biml for Beginners: Script and Automate SQL Server Integration Services (SSIS) Development by Cathrine Wilhelmsen (b|t)
  • Care and Feeding of a SQL Server by Jen McCown (b|t)
  • Indexing for Beginners by Kathi Kellenberger (b|t)
  • Creating a Disaster Recovery Plan by Rie Irish (b|t)
  • Using Extended Events to figure out why an application is slow by Mindy Curnutt (b|t)

Special thanks to Rie and Melody for putting this together and for being awesome and inspiring.

We may all be women authors, but this book isn’t about WIT issues. We each got to write about an area of the data platform in which we have expertise.

If you’d like to support us, or are just curious what we have to say, you can pick up a copy on Amazon or purchase a copy at PASS Summit. If you are going to be at PASS Summit, you can also stop by the WIT Happy Hour/book release party on Oct 31 or the panel session on Nov 2.

For more information, check out the website (kindly provided by SentryOne, who sponsored the book).

It’s tough to write a book chapter about a technology that changes every month, but I think the content holds up fairly well a few months after writing it. A few things changed on me (e.g., the Office Store for custom visuals is now App Source, and drillthrough actions exist now), but my approach to data viz in Power BI is still relevant and in use today with several clients. I hope you’ll give it a read and let me know what you think.

 

Microsoft Technologies, Power BI, SSAS

The Tabular Model Documenter is now a Power BI Template

A while back I created the Tabular Model Documenter Power BI model that can connect to your SSAS Tabular or Power BI model and display metadata about the model to help you see relationships, calculations, source queries, and more.  I had been meaning to turn it into a parameterized template since templates became available and just finally got around to it.

You can now download the PBIT file here. Note: This works for SSAS 2016/compatibility level 1200, but may need some adjustments for Azure AS and SSAS 2017.

When you open it, you’ll need to fill in the instance and database name, then allow some native queries to run. Then you will have your tabular model documented for you without a lot of manual effort.

tabular-doc-relationships

For more info on how to use the Tabular Model Documenter, see the original post.