Tag Archives: Data visualization

Speaking at SQLSaturday Oslo, September 2nd 2017

I got some great new during my vacation I got a mail saying I was selected to give a talk at SQLSaturday in Oslo this year. I also got notified by the tweet below which I had completely forgotten from last year. Nice surprise to see I have succesfully made a goal of mine, even though I had forgotten I had set it! I’ve been to every SQLSaturday that has been in Oslo and it has always been a great event so looking forward to be able to contribute with a talk myself this year!

My talk is titled “Data Visualization – More Than a Hygiene Factor”, based on a quote from this Medium Post. You can read my abstract below.

"For many companies data visualization is still a hygiene factor; necessary but not crucial"

In a world where everyone wants to use data to drive their business forward it is important to be able to communicate and speak the language of data even though data itself can be complex. One way of doing this is by making good data visualisations. Good data visualisations are engaging, they are informative and they let your data tell you its story. Too often data visualisation gets a low priority making the final result feeling lacklustered and making the users uninspired. 

In this session we look at some data visualisation principles and best practices, in order to deliever your message with a clear point of view and minimize confusion. Lastly we will look at how you can use these practices with Power BI in order to improve how data can be communicated to your end users in the best possible way making them come back over and over.

SQLSaturday is a free 1-day training event for Microsoft Data Platform and SQL Server professionals, providing a variety of high-quality technical sessions. If you work on the Microsoft Data Platform SQLSaturday is a great way to get inspired and hear about new things. You can find more information about SQLSaturday, September 2nd in Oslo here!

 

 

“The Little of Visualization design” – with Power BI

Andy Kirk¬†has an excellent series called “The Little of Visualization Design” where he gives small tips and tricks that can improve your data visualizations. If you have not seen it I strongly recommend it. Now, what I am going to try and do every week after summer vacation is to try and show you have you can take these tricks and use them with Power BI. But let’s kick start it now¬†with part 1, dual labeling. I ¬†suggest¬†that you read the original post by Andy first so we are at a common ground about what we are going to look at which is this pie chart.

Dual labeling. It is suprisingly normal to see and it generates more cluster on your data visualisation than you need. Repeating something will not make things clearer, it will just create more ink on your graph and make it harder to focus on what’s important.

Now if you punch in the data and create a pie chart in Power BI we get what is shown below.

So Power BI does not provide you with a dual labeling issue at front, but it is quite easy to reproduce it with Power BI.¬†In the “Format” pane you have a bunch of options which usually are great, but you have to use it with care and have a clear vision of why you are changing the original chart if not you can end up with all of these different variations.

The one in the bottom left is probably the closest to the one in the original post. It has dual labeling, and it has quite similar colors on the pie slices. Andy Kirk’s proposed solution is to remove the labeling and provide it directly onto the pie since the colors in the original graph is so similar. Now, that doesn’t ¬†sound to far away from the default graph that Power BI provides us with. However the default is not perfect and here is what I would do in order to improve it:

  1. In Label Style¬†choose “Category, data value”. This makes us see the actual number.
  2. Increase font size of detail label.
  3. Increase font size of the title. In general I think all default font sizes in Power BI are too small.¬†I always feel like I need stronger contact lenses¬†when creating a chart…
  4. Sort the chart by value so the slices appear in order of size.
    Note: I had originally made the font size of the detail label a bit bigger. However, this made the detail label for Canada disappear. Probably because it would take up the same space as Israel. So I wish they could make the position of the label a bit more dynamic.

In the end we end up with¬†the chart below. So all in all the default chart Power BI created wasn’t too bad, but it could be improved. And make sure you are aware that not all options in the format pane in Power BI makes your data visualisation better, it could make it worse!

I’m looking forward to some weeks of summer and then I’ll continue this series when I am back! Thanks for reading. If you have any questions or feedback drop me a comment, it is greatly appreciated.

Learning by Sharing

I am currently in the process of re-learning D3.js, a javascript library used for data visualisation. That means I am always looking interesting datasets or existing visualisations I can remake. Over at reddit.com/r/dataisbeautiful some time ago one of the busiest pie charts I have ever seen was posted. It was showing how many players was playing at certain maps in the game Team Fortress 2. Unfortunately there where over 14 000 slices in this pie chart and it just didn’t make much sense.

                                                        Worldst busiest pie chart

I used about 5 minutes in Power BI to create a remake that was a bar chart in order to show why that is a better graph choice to show ratio between different categories. However, as some other users commented that removed some of the complexity which also is quite interesting. The fact that more than 14 000 maps are being played at in a month in a game that is ten years old is quite impressive.

                                                5 minute remake to show ratios better

So I ended up starting to think about a remake and asked the user who posted the pie chart if he had the dataset he had used. The internet is full of awesome people and in about one hour I had a csv files with 14 000 datapoints.

I have¬†always gone code first, but I thought this time I would at least try to sketch some ideas on paper. I’m not the greatest at drawing, but that is also not the point. So I made this sketch. Initially I wanted to be able to create an overview which then could be broken down by some interaction. Unfortunately I did not have both the datasets I wanted at the time I built the visualisation, but I do now so maybe I have to revist this some time.

With that design in mind I built the visualisation. I’m not going to go into detail about how I did that here. You can see my cource code here if you want to have a look. The original is interactive so you can hover over a bubble to see more information.

                                           Finished visualisation

Context, context, context

Now, even though I made an interactive version I ended up deciding to post a static image of the chart. That was a mistake. The original pie chart had created quite a bit of discussion at /r/dataisbeautiful so my head took it for granted that everyone knew what this represented. They did not. Internet is big, and not everyone lives in one subreddit. So many users did not get the background or the context, and even though I had written that in a comment, comments are not read by a lot of Reddit’s users. The chart had no context and as of today the comment with the highest rating still reads: “Looks nice but no fucking idea what anything is representing”.

However, a lot of people did actually get what I was trying to show. How complex and how distrubuted the player base in Team Fortess is. I have gone through the thread and followed up question and such and as long as you can filter out the trolls and take things as constructive feedback you can get some really nice feedback. Where else would you get feedback from many, many thousands of people on your work?

  1. Context is hard on Reddit as a platform. You basically only have the title to explain as many users won’t read anything else, only look at the image.
  2. I should have posted the interactive version. It provides some more detail that people where missing. I also, for some reason, had removed the text stating “hover over a bubble for more information” in the static image. Including that text might have made more people go to the interactive version.
  3. Check more colorblind simulators. I used some simulators, but I think I forgot it with the last version. THere will be colorblind people when you share it with a userbase this big. If you need to test yours you can try this one.
  4. People in general have a lower threshold for saying negative things so you need to be able to filter comments quite hard and which you decide to listen to.
  5. It is written bubble, while boubble, as I had written all over the comments, is a river in France!

All in all, sharing your work is a really good exercise. Working on a project it is easy to go blind, but the internet won’t let you go easy if they see something to pick on.

If you create something you are proud of make sure to post it to /r/dataisbeautiful over at Reddit. We are always looking for original content!

                                    Gif of some steps of development

Creating a dynamic card with text value in Power BI

Alternative title: Finding string value from a dimension with highest numeric value

The company i work for uses Yammer and I have founded a Data Visualization group which I am, manually, keeping some statistics about using Power BI. The other day I found myself wanting a dynamic card in Power BI in order to highlight which day was the most active when it came to amount of posts. In order to do this I had to figure out how to make a measure returning not the max value, but the day which had the maximum amount of posts. My usecase is finding a weekday, but maybe you want to see which product is the most popular, which county has the most purchases or which salesperson has the most sales. All these cases should be able to reuse this measure.

 

First try

My inital thought was to create a table in my calculation and then slice it to return only one row and one column leaving me with one cell which had the day with the most number of posts. I made a measure which used a TOPN returning the row with most posts followed by a SELECTCOLUMN to select only the column which had the weekday in it. Now, this turned out to return me the overall correct day, but it did not work when I added a filter and the POwer BI visualization returned errors so I had to start over.

 

The solution

I have created three measures to solve this.

1) One simple sum of [Number of Posts]:


Number of Posts = SUM(Sheet1[NumberOfPosts])

2) Finding the day with the most posts by using MAXX


MaxPostsPerWeekDay = MAXX(VALUES(Sheet1[WeekDay]);[Number of Posts])

3) Using FIRSTNONBLANK on my WeekDay column and then return the value where the sum equals the maximum value. So in the end my measure looks like this.

Most Popular Day = 
IF(
    ISBLANK([Number of Posts]);
    BLANK();
    FIRSTNONBLANK(
        Sheet1[WeekDay];
        IF(
            [Number of Posts] =
            CALCULATE(
                [MaxPostsPerWeekDay];
                VALUES(Sheet1[WeekDay])
            );
            1;
            BLANK()
        )
    )
)

The first IF is to remove days that has no posts, in my case there is not much activity in the weekends so they will get filtered out. The beauty of this measure is that it is not limited to crads, but also easy to use with your filters and in tables where you would want it.

Datazen Branding

It is possible to do branding in Datazen so you can use your own backgrounds, create a custom color palette, etc. However in the product documentation it is only listed the names of the files you need in this brand package. It does not show you how the layout of the files that are not image files should look like so you can create the images, but not the complete brand package.

I have tried to get a hold of how these files should be created for a while, and yesterday luck finally stroke. So, a big thanks to @cmfinlan for sharing a brand package template. You can find the template below. Enjoy creating custom branding for your needs, and may all your maps be pink!

BrandTemplate

PinkMap

One way of auto refreshing Datazen in your browser

Yesterday I saw Christopher Finlan tweet about one way of making your Datazen dashboard auto refresh. He used a Firefox add-in for this, and I’d like to show you how I have been doing this.

If you want to show Datazen on a big screen there is at this point no out-of-the-box way to auto refresh your dashboard, meaning you have to open it to get new data. If you use the browser viewer you can quite quickly create one of the simplest websites in the world to do this.

In the header of your .html file add the following line:

<meta http-equiv="refresh" content="90" />

It will make your page refresh every 90 seconds. This can also be used if you want to circle some of your dashboards, sort of like a carousel. The line below will after 30 seconds go to the specified URL instead of just refreshing the page. To complete the circle make sure to go to the first dashboard in your last dashboard.

<meta http-equiv="refresh" content="30; URL='NextDashboard'">

So what does the .html file look like for the easiest example? Something like this, just exchange “DashboardURL” with your URL:

<!DOCTYPE html>
<html>
<head>
<title>Name of Dashboard</title>
<meta http-equiv="refresh" content="90 ">
</head>
<body style="margin:0px;padding:0px;overflow:hidden">
<iframe src="DashboardURL" frameborder="0" style="overflow:hidden;overflow-x:hidden;overflow-y:hidden;height:100%;width:100%;position:absolute;top:0px;left:0px;right:0px;bottom:0px" height="100%" width="100%"></iframe>
</body>

Note: If you are going to setup an auto refresh I’d suggest you also set up your guest account so you can have access to those dashboards without having to login. You can then use the public dashboard URL in the source of your iframe.

Datazen: Personal feature requests

I’ve been using Datazen at one of my customers , and spoken about it with a bunch of other people about it, lately. People are starting to really get their eyes up for Datazen now, maybe because it’s free if you have a SQL Server Enterprise Edition license?

Anyways, I’ve met some challenges the last couple of days and I want to highlight some of them. I guess this is my way of putting in a few feature request. Maybe they are already on the roadmap, maybe not.

What am I trying to do? I need to compare two different zones with the same measure and compare it to another measure for each hour for the rest of the day. This comparison measure will change throughout the day and can also differ between the two zones. Great, I know what I am going to do! I’ll choose a “Comparison Time Graph”, choose my measures, choose hour as default time unit as and we should be good to go right? No!

Request 1: Fixed y-axis

First issue is that the axis’ are data driven, which is a nice feature in general, but in this case I need them to use the same axis’ for comparison reasons so I really wish I could have fixed axis’, or be able to use a third measure to define the y-axis. In this case I ended up calculating a third measure to force the graphs to have same y-axis based on the higest value in one of them. So I am now comparing measure 1 with measure 2 AND another measure to push the y-axis. This affects the comparison values below the chart however and wil now never go above 0. If i could chose to remove that difference value that wouldn’t be a big problemas the user could just use the graph, but I am not able to that.

Request 2: Same information in all viewers

Below I have the output from the Win8 app in the top of the image and the web browser viewer at the bottom. They clearly are showing different information. I want the hours to be displayed as in the browser viewer, but I want the tooltip from the Win8 viewer. I want to see the values of each measure, or at least have the option to do this. The browser is also only showing the label of one of the two masures I am actually comparing to.

DifferentBehaviour

Part 2: Real time dashboard using a Raspberry PI and Power BI

Part 2: Using the Power BI API to make a real time dashboard

So you have set up your Azure Active Directory and created a Power BI provision for your organization? If not, you can have a look at part one of this series.

Before we start playing with the Raspberry PI let’s create a simple console application to see that we have set up things correctly in Azure Active Directory in the last post and are able to have real time updates on a dashboard in Power BI. Nothing advanced, but it is a nice test to see if we have set everything up correctly with our Azure account and Power BI. What we will do is create a dataset if it does not already exist in Power BI and insert rows of data into that dataset which will then update the dashboard in real time.

Creating a Power BI application

If you want to take the easy way out to see if you have set up everything correctly you can download a test code made by the MSDN team on github here: https://github.com/PowerBI/getting-started-for-dotnet. After downloading this code you need to insert your own Client ID that you get from your application page in Azure. You also need to set the redirect URI to the same as your application, if you don’t do this you will get an error message like the one below.

BadRequest

When you run the code you will see a green star-thingy appear to the right of your newly created dataset. After the dataset is created you can go and explore that dataset, even if it’s empty, and create a report. From the report you then pin report elements to a dashboard and when you run your application you will see the elements update real time. Nice!

NewDataset

I would suggest that you, either if you choose the easy way out or make a new application  from scratch, do some playing around and create your own dataset and insert some rows into it so you get a better understanding on what is happening and how the Power BI API works. Getting a good understanding of how datasets and inserting rows work will be useful later. We will not be able to reuse the dataset in the example code when using our Raspberry PI so we need to be able to create our own datasets, turn them into JSON and then insert some data. What you create is completely up to you and your imagination. I made a new application from scratch with a simple timer that  every second writes how many seconds it has been running, along with a timestamp, to Power BI. Does it provide great insight? Do I change how we look at this world? No, but it has provided me with some valuable hands on experience with the Power BI API. Other examples I’ve seen people doing is for example looking at clicks on a website and what device the clicks come from. Again it is completely up to you what you want to do.

Next up we will start to play with the Raspberry PI and then connect it to Power BI.

Dashboard

NOTE: When writing to a Power BI dataset there is, at the point when this post was written, no option to export a dataset from the Power BI Preview. This means that if you want to be able to use the data in another format than Power BI you need to store it another place as well. I am going to create an Azure SQL Database to store my data when using the Raspberry PI in case I want to use it other places other than PowerBI. Maybe you should consider doing the same?

Other parts of the series can be found here:

Part 1: Real time dashboard using a Raspberry PI and Power BI

Why I really like Datazen as a visualization tool

I was really excited when I saw that Microsoft had bought Datazen. I have been able to use Datazen for a while already, and I really like the product. At the Gurus of Business Intelligence conference in Oslo in 2014 I was lucky enough to be able to have a session together with Marc Reguera, @marcreguera, where I spoke about mobile and touch based reporting using Datazen as the example app.

Now why do I like Datazen? First of all I think they have a clear vision, a vision I like. They want to create great data visualizations for mobile and touch based devices which are easily designed and shared with others. It is not a data discovery tool like Qlik or Tableau so you need to have your data organized in order to get the full effect. But when you are ready to create visualizations and share those with your coworkers it is really good in my opinion. You have predefined graphical elements you drag and drop onto your design surface and connect your data to. You also have the possibility to create custom maps and color palettes with backgrounds to get the right feel for your dashboards.

Datazen has three major components. You need a Datazen server in order to get data from other sources than local Excel files. When you have created a dashboard you are also able to deploy your dashboard to the Datazen server so it can be consumed by others. The server also grants you access to a control panel where you can setup KPIs, set permissions, set up connections to your data sources and create data views which can then be imported into your dashboards.

This brings us to the other two parts of the Datazen stack, the viewer and publisher apps. The publisher app is only available on Windows 8 devices at this point. This is where you import your data and create your dashboards before you can publish them to the server. The publisher itself is also made for touch so using f.ex a Surface to create visualizations on the go, or in a demo environment, is a real joy! It is quite fast to setup a dashboard design and connect your data to the graphical elements you have available. In my eyes this is a really important feature because it allows you to quickly iterate over different designs. I don‚Äôt know how many times a client has come back and said ‚ÄúBut that‚Äôs not how I visualized the report in my head‚ÄĚ. Now you can sit with your client and quickly make a new version of the same dashboard right there and then. I have several times prepared a dashboard to present in a demo, but have found myself creating a dashboard on the go instead. Because it is easy to do so. Because I can. You can even hand over your computer to the client and let them create the dashboard during your demo!

The viewer app lets you consume dashboards deployed to a Datazen server you have access and is available for all mobile platforms as a native app. If you are used to Android you will still have the Android feel unlike some apps that have been made to fit all platforms, but rarely do. When you design a dashboard you create several versions of the same dashboard to fit different resolutions. A dashboard made for a phone may have to emphasize different things than one made for a laptop, simply because some graphical elements, like a map, is perhaps not that suited for a small screen. Datazen also gives you offline access to your dashboard which I think is absolutely essential for all apps.

You can download the Datazen publisher for free from the Windows 8 store and use local Excel files to create your own stunning dashboards right away. If you want to share those dashboards or get data from your databases or other sources, you will need access to a Datazen server. When the announcement was made Microsoft also announced that SQL Server Enterprise Edition customers with version 2008 or later and Software Assurance are entitled to download the Datazen Server software at no additional cost. Give it a go. It is a really well made product, and a lot of fun to play with! The road from raw data to presenting them on a well made dashboard on someone else’s mobile phone is getting shorter!

For more information go to http://www.datazen.com/.

Example of a dashboard made with Datazen