Tag Archives: Dashboard

The Little of Power BI Visualization Design – Part 2

Datavizualisation, PowerBI, Technical, , , ,

Continuing our journey on applying Andy Kirks tips and tricks from his series “The Little of Visualization Design” we are now at part 2, Clever Axis Scaling. As last time I suggest you read his post first so we already have some common ground.

Why use clever axis scaling

Clever axis scaling is a tool in order to create some drama in your visualization. It can also help you highlight values and draw your consumers eye towards it. Things that stand out will get attention, our brain is simple in this regard, and in this case that is what we are after.

In the example the y-axis is set to 50, but the maximum value is 76. Now, my quick thought was “Great! this is easy, lets just set the y-axis to 50 so the helper line is 50”. This is easy to do, however the chart actually gets cut at 50. So it ends up looking the image below, which is not what we want at all. We are now hiding the most interesting data!

Max y-axis value set to 50 truncates your graph

So I tried setting it to 100. Now, that works okay, but the highest line on the y-axis is now not 50 so the dramatic effect of 76 shooting way above the last helper line on the y-axis disappears even though we still see the biggest increase on the chart to the left. So what is the solution? You need to try out what works best with your data. In this case it seems to work fine to choose 76, the maximum value in the chart. Now this will not always be the case because we can not control how many lines on the y-axis we get. If I make the chart higher you can see that the y-axis changes with numbers as well.

Trying out different max values on the Y-axis
Different heights can remove some of the effect we are after

Use it carefully

This solution also has the drawback that you are hardcoding the minimum and maximum value. So if you suddenly have a value higher than 76 you will loose it! In the end it comes down to what you want to tell with your chart, if your chart is going to change values often and how dramatic you want it. If you have no idea how your numbers will behave in the future I will not advice you to hard code min/max values unless you need it for a specific occation like a presentation. When you are done with that spesific occasion I suggest you turn them back to automatic to minimize confusion.

Final result

As with all tools PowerBI has some limitations compared to custom code and for example using something like D3.js where you can do absolutely everything you want! Having these limitation can make it a challenge to use all these tips and tricks  going forward, but we will do the best we can! In this case we might have some problems trying to create a more dramatic effect in our storytelling. Both with your axis’ as we have seen here, but also with data labeling as PowerBI does not let you choose which data points to highlight. So if you try to label the highest value without hovering over it it is not possible. Or at least I didn’t manage to, but if you do please let me know how you did it.

Also, if anyone has a way of hiding the ESRI logo in Power BI Desktop please let me know. They are not pretty and are driving me crazy!

Creating a dynamic card with text value in Power BI

Datavizualisation, PowerBI, , , ,

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.

Creating a Dynamic Dashboard of Datazen Dashboards

Datazen, Technical, , , , ,

One of the projects I’ve used Datazen was a project that needed operational reporting. These dashboards was to be updated at least every 10 minutes and was placed on big screens around the work area where it was needed. We also created an overview, or landing page, for super users that needed to have quick access to all dashboards without waiting for the dashboards loop on the big screens.

In the beginning this landing page was just a static html site, as we didn’t want people to use the Datazen portal at that point. Every dashboard had a static thumbnail and when a user clicked on a thumbnail they were brought to the respective Datazen dashboard.

This worked fine, but it required every user to go into each dashboard to have a look at the status since the thumbnail did not change. So we decided to spend some time to create a dynamic landing page, or a dashboard of dashboards that showed real time thumbnails of the dashboards. By doing this they couldn’t necesserily see the numbers on all of the dashboards, but they would be able to see status and where things where green or red.

Datazen can be embedded in web pages by the use of iframes. Doing this gave us a more dynamic page that was refreshed when a user entered the site or after a given interval if they stayed on it. What you will notice if you try this is that a click on a dashboard will not open the dashboard itself, but instead you will be able to interact with the dashboard in the iframe. In our case, this was not what we wanted as the purpose was to just see the status and when a user clicked on a dashboard they were sent to that specific dashboard to get more details and in full screen.

In order to solve this we created a new <div> called clickCatcher with the same size as the Datazen “thumbnail”, and made transparent. This allowed us to display the dashboard in the size of a thumbanil, but open the dashboard when it was clicked instead of interacting with the small version. The code for one thumbnail is posted below.

It’s a neat little trick that made us able to create a solution that was easier, an better, for the customer. Too see how you we auto refreshed dashboard pages you can take a look here.

HTML

<div id="kpi" class="kpi">
<a href="LinkToDashboardPage" class="thumb">
<div class="clickCatcher"></div>
<iframe src="LinkToPublicDatazenDashboard" style="overflow:hidden;overflow-x:hidden;overflow-y:hidden" frameborder="0" height="157px" width="300px"></iframe>
</a>
</div>

CSS

.clickCatcher{
display: block;
background-color:rgba(255, 0, 0, 0.0);
height: 157px;
width: 300px;
position: absolute;
}

LoadingDashboards
All dashboards are refreshed when the landing page is opened
DashboardDashboardExample
How the dashboard might look. Clicking on a thumbnail will take you to the full size dashboard

 

Datazen Guest Authentication Will Be Missed

Datazen, Technical, , , ,

About a week ago a blog post was released called “Announcing End of SUpport for Datazen Products“. Now, this shouldn’t come as a surprise since Datazen is a part of Reporting Services in SQL Server 2016, more specifically mobile reports. Overall this is great news, there was however a few points I was a bit disappointed in seeing was not part of the initial release.

A really great thing that is mentioned is that a focus area has been that Datazen customers should be able to migrate to SQL Server 2016 Reporting Services easily. This is great, no need in recreating everything you have done so far, and there has been mentioned a migration tool earlier so I hope this will be a simple case once customers start migrating HUBs, users and dashboards.

My biggest dissapointment was to see that Custom authentication and Public/guest authentication is not part of the initial release, but at least on the roadmap. I have struggled with SSRS on public webpages before, and Datazen gave me an easy way of doing this. Hacking a way so that a public Sharepoint portal had to impersonate a user when a user went to pages with SSRS elements was not an easy task, and that hack also did not work when the site was migrated from SharePoint 2010 to SharePoint 2013 so we had to do it all over again. And this was even the solution that Microsoft themselves said we should go for!

Authentication via SSRS have some clear advantages, but I’m still dissapointed that there was not room for guest authentication. In the end, a lot of information is usable and important for the public, and if it is not seen what use is it? I’m crossing my fingers that this will be implemented relatively quick. If we’re lucky maybe they are able to let us use SSRS paginated reports on public sites as well!

One way of auto refreshing Datazen in your browser

Datavizualisation, Datazen, PowerBI, , , ,

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

Datazen, , ,

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

Datavizualisation, PowerBI, , , , , ,

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

Datavizualisation, , , ,

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