An Aster Plot for the Holidays

Yesterday was Christmas and for those who celebrate Christmas, I hope you had a very happy day. However, I just wanted to ask this. Why do we wait for Christmas to be nice to one another, to wish our fellow man peace on earth and goodwill to all men? And do we really mean it or do we just go back to our old ways the day after. Do we need a holiday to be nice to each other? Why cannot the Christmas spirit spread throughout the year? And why does it have to be tied to a religious holiday. Do we need a religious holiday to tell us when to be nice to others?

On a similar note, New Year’s resolutions are coming up in another week. Could we all possibly resolve to act a little more like Christmas every day of the year? Or is that just as hard as resolving to lose weight?

Anyway, since many of you are on vacation anyway this week, I’m going to do just a short blog on the Aster Plot visualization for Power BI. This visualization, like many of the others I’ve talked about can be found on Microsoft’s Power BI Visualization Gallery page. When you click on the Aster Plot tile, the following diagram displays telling you a little about the visualization which as they state in the description is related to a donut chart. However, unlike a donut chart which defines the size of the donut segment on the value associated with that segment much like a pie chart, an aster plot uses one dimension to define the segments and each segment is equal in the amount of the arc it uses. The value associated with the segment defines the radius of the segment instead. Let’s see how this might work with some of the sales data by product category from Contoso.

I’m not going to go through all the dialogs that appear when you download a new visualization. I’ve done that before. So if you are joining in for the first time, check back through some of my previous blogs for details on that.

After the visualization is downloaded, I can load it into my Visualizations toolset/panel by clicking the ellipsis (3 dots) at the bottom of the visualizations panel.

Power BI will display a warning to caution you about importing custom visualization, so you should always do this on a test machine first to make sure they do not cause any issues. Just because it is on a Microsoft page does not mean that it is safe to use and does not violate any privacy concerns.

Next, I loaded my Contoso data and displayed the Sales Amount by Product Category as shown in the table below. (Again, if you do not know how to do this is Power BI, I’ve covered this several times in the past several months.) I should mention that I specifically sorted the sales data in descending order for this visualization.

Then with my table selected, I can click on my new visualization, the Aster Plot which displays the chart shown below.

Notice that if I hover over any of the segments, a box appears that tells me the product category name along with the corresponding sales amount for that category. One of the things that I don’t particularly care for is that the name of the dimension appears in the center of the chart and apparently behind the arcs so some that some of the name is covered by the chart. I wish I could move the dimension name either to the top of the bottom of the chart at the very least. Of course I could customize the name to make it smaller or to add spaces between the words back on the data page, but for this demonstration, I didn’t feel the need.

You can have other charts on the same page like the one shown here that displays the Sales Amounts by calendar year for Contoso.

Like other visualizations, I can click on any of the Aster plot segments to select that category and the column chart to the right will automatically update to highlight the annual sale for the selected category while still showing in a lighter shade of the column color to total annual sales.

Similarly, I can select a year and the Aster plot will be redrawn to display the relative sizes of each of the categories as shown below.

Well, that is all I’m going to cover for this week because it is time to get back to end-of-year celebrations, visiting relatives, after-Christmas sales, and all those things.

C’ya next year.


Bubbles – A Power BI Visualization

Bubbles – A Power BI Visualization

This week I am going to take another look at a custom visualization. This one is called Bubbles and is in some ways similar to last week’s look at the Word Cloud visualization. At least it looks similar in that the bubbles can appear as different colors, they are grouped together like the words were in a random pattern, and the size of the bubble, like the size of the text in the Word Cloud is a function of another numeric parameter. This time rather than the frequency of the word as in the Word Cloud, I can select any other numeric measure to define the relative size. In this case, I’ll use sales of the items within a product category and let each bubble represent one of the categories.

I’ll start by going to the Power BI Visualizations page found at: https://app.powerbi.com/visuals and click on the Bubbles tile. This displays the following dialog which tells me a little about the visualization including giving credit to its creator. Since I want to use this visualization, I next click on the Download Visual button.

While I will not show it here, there is an intermediate dialog that display the licensing terms for most visualizations. If you accept the license terms and proceed, your browser should download the visualization to one of your local folders. For me that is the Downloads folder. After it finishes downloading, I will move it to a folder where I keep other Power BI Visualization files.

Next I can open my desktop version of Power BI. In the background, I will load sales and product data from my Contoso sample dataset that I pften use and then switch to the Report page. On this page, I can click on the three ellipses at the bottom of the Visualizations group which is the Import from File button.

I will be shown a dialog which lets me locate the visualization file that I downloaded. Before loading the file, Power BI warns me about the perils of importing custom visualizations because they could contain code that could either circumvent the security on my computer or even access private information in my files. The fact that these files are hosted on a Microsoft site is not a guarantee of safety. However, they are more likely to be safe than other visualizations I may find elsewhere on the Internet.

After I start the import, I receive one more dialog when the process is complete.

The Bubble Visualization tile now appears in the bottom row of the Visualizations section. I’ve been asked whether I can rearrange the visualization tiles, perhaps alphabetically, by type or by some other factor. At this time, I have not discovered a way to do that so if someone has, please let me know. Of course the next update of Power BI may include that functionality so who knows.

Next I drag the fields I want to use into a blank area of the Report page. In this case, I want both the Sales Amount field and the Product Category Name. Initially, these data elements may display as a table or column chart (depending on which field you dragged into the report first). In either case, I can then click on the Bubble visualization and the character (text) field will be used to define the bubbles and the numeric field will be used to define the relative sizes of each bubble. The figure below the column chart version of the initial data added to the report page.

As I have shown before, to change the visualization, you only need to click on the tile in the Visualizations panel to change the currently selected visualization to another. Therefore, clicking on the new Bubble visualization results in the following:

If you hover over any of the bubbles with your mouse, Power BI shows the product category name value and the sales value for that category. Interestingly, because it was not what I expected, hovering anywhere outside the bubble but in the overall background (light grey here) displays the name and value of the largest bubble, not the total sales which is what I expected.

Like many other visualizations, you can click on any of the bubbles and that automatically filters any other visualizations on the page by the selected product category. I could also add a second table on the report page such as Calendar Year from the dimDate table and then define it as a Slicer. Then selecting different years in the slicer changes the Bubble visualization to represent data only for the year(s) selected.

The Bubble visualization has a few formatting options which I can get to by clicking on the pen (pencil) in the Visualization panel. I probably would like to see more options to control the colors of the bubbles which sometimes appear all the same color and sometimes as a few different colors. Perhaps if you too would like to see more control over the formatting of this or any other visualization, you should remember that the opening dialog from the Power BI Visualization download page provides a link to the author where you can send your thoughts and suggestions.

That’s it for this week. Next weekend is Christmas and then the week after is New Years. I will try to find time from celebrating to cover two other visualizations before getting back to some hard core data analysis next year.

C’ya next time.


Using a Word Cloud to Analyze Responses

Have you ever had to analyze the results of a survey? If your survey consists of multiple choice, true/false or even Likert Scale type questions, the results are fairly easy to tabulate. All you need to do is to count the number of each unique response for each question and then display the data using a table, column/bar chart, or even a pie chart (please only if there are less than or equal to 6 choices).

But what if you have a question like: ‘Describe with a single word your boss’ and you allow them to enter any word truncating the response at the first blank. I suppose you could count the number of times each word is used and then display the results in a table or a column/bar chart. However, because there are potentially dozens of words that could be used, a column/bar chart could get crowded pretty fast.

Perhaps even more difficult to analyze is asking the responder to describe what they find important in a web site especially when you give then a free-form multi-line text field. Now you have to deal with entire sentences like: ‘The most important thing to me is a strong search engine to make it easier to find the information I want.’ How do you parse that type of response into meaningful data? That is what I am going to look at this week.

To begin, I need to capture the survey data in a format that I can import into Power BI. Generally I will have two choices for this, a simple text file of the column(s) from the survey that I want to analyze or perhaps the survey software (like SharePoint) can export the data to an Excel spreadsheet. In either case, I can then import the data into Power BI.

In terms of how I might want to visualize the data in Power BI, column and bar charts are not an option as I mentioned before because there are simply too many words and thus columns to display anything meaningful. However, a common visualization for just this purpose is something called a Word Cloud. Word clouds have been around for some time especially in blog sites to help identify the most common words used in the blog. Fortunately, Power BI has such a visualization in their Visuals Gallery site which can be found at: https://app.powerbi.com/visuals. This site contains visualizations that have been created and submitted by the Power BI community. There are instructions on the site on how to submit custom visualization that you create using the Power BI Developer tool. The Microsoft team will then review the visualization and if it appears stable and relevant, they will publish it to the site. Anyway, there is a visualization on this site called: WordCloud.

To use a visualization from this site, click on it. This displays a dialog as shown in the following figure which includes some information about the visualization, who created it, the license information, support, etc. However note the Download Visualization button

Clicking the download button may include an agreement to the terms of usage of the visualization and will then download a file with the extension .pbiviz. I recommend creating a separate folder on your machine where you can download this and other visualizations from this site. Next I find the box in my Power BI visualizations Report page and locate the button with the 3 dots (ellipsis).

Clicking on this button first displays a warning about importing custom visualizations as shown below.

Next I will use a standard Open dialog box to locate the file that I just downloaded.

Now with my WordCloud visualization available and my data loaded into Power BI, I could simply drag the column that contains my text onto the blank Reports page (or a blank area on the page if I had other visualizations already started, and then with the data selected, choose the WordCloud visualization.

Initially, the display is cluttered with common words like: the, and, an, a, is, had, have, other, my, I, and many other words that we use in everyday conversation. This noise detracts from the real information we want to gather from the word cloud. So we need to find a way to eliminate (or minimize) the occurrence of these words.

How can we do this? The brute force method (and for now the only method I know) is to return to the Data page, select the Edit Queries button on the Home tab and then use the replace values option in the Any Column group of the Transform ribbon to define values to replace.

Now is where I have to be a bit careful. If I simply try to replace “an” with the empty string: “”, I could alter other words like “change” to “chge” or “analysis” to “alysis”. Neither of these words would make sense. Therefore, I decided to always add a blank as a prefix and a suffix to whatever word I wanted to find and eliminate and replace it with a single blank. (Because if I don’t, a phrase like: “search for an employee phone number” would become “search foremployee phone number”.)

The problem is that if the words I am trying to eliminate begin a line or are followed by a period or comma, my replace will not work (unless I also do replaces with periods and commas.)

Anyway, my point is that this can be a time consuming activity of performing a few dozen replace statements and then switching to the Report page to see what the word cloud now includes. Let’s assume that I’ve already done several rounds of replace statements and my word cloud looks like the following:

Another thing I can do to focus on the most important words is to change some of the properties of the visualization. Click on the Pencil in the Visualizations column to display the properties and open the General property group as shown below:

Notice that you can change the maximum number of words displayed, the minimum and maximum font size and whether word breaking is on or off.

First the number of words displayed greatly affects the appearance of the cloud. The following two images show both 200 words and 50 words. (The previous image was 100 words.)

Obviously changing the minimum and maximum font size will help visually give me an idea of which words occurred the most often. The fourth option is the control that breaks the column into individual words. Otherwise, the entire phrase in the column is used which may be good for short predefined responses, but not so good for my case here.

You should have also noted that each time I change one of the properties, the word cloud regenerates. Another somewhat less obvious thing that occurs is that special characters are converted to blanks when the words are parsed. Therefore, a word like: “site’s URL” gets parsed into three words: “site”, “s” and “URL” which explains why the single letters “s” and “t” often appear even in a 50 word cloud. Of course, it might be possible to go back and replace “‘s ” with ” ” or “‘t ” with ” “. Again, I need to be very creative and very careful to come up with a final word cloud.

Anyway, I hope you found that interesting. In future weeks I will look at additional Power BI features and visualization. C’ya then.