Design tips for My favorite dashboard.

Dashboards are very popular way to display data. Other popular names are (performance) cockpits. Sometimes I see dashboards for which I think, which vision is used to build such a bad dashboard. If you start building a dashboard without a vision it will end up in a dashboard which maybe looks nice as a first impression, but is useless on a daily basis. In this blog post I will describe my vision how you should build a useful dashboard. First of all I will start with 2 examples of a dashboard. Both will show the same information. The only difference is the presentation of the data. Examples are the best way to explain.
Let's start with a dashboard in which a lot of improvements can be made:

Now the same information in presented in a better way:

I will comment on the dashboard which can be improved. In general one rule can be applied: Keep in mind. Less is better.

1. Report title in big font and printed Bold. Goal of the dashboard is to show attention to the figures not to the title.
2. Gray background in the report. This is visual fluff. The gray color has no function. Keep in mind. Less is better.
3. Axe values. A lot of zero's is expensive space and is difficult to read. It will make the bar chart it self smaller. We read with our brains. Is the axis value 200.000 or 2 million? 
4. Red color of the bar chart. Is something wrong? Colors have a function. Red is a color to grab attention that something is wrong (STOP). In this example nothing is wrong. Revenue is better than previous year.
5. 3D graph. A dashboard is not a painting. The dashboard is used in a business environment. Not in a gallery. Always use 2D graphs. They are easier to read and understand. For example: What is the value for period 2 of previous year?
6. Aqua color with gradient style center. This is visual fluff. Keep in mind. Less is better.
7. Legend takes a lot of valuable space which result in a smaller bar chart. It's not the legend but the chart which need to most space. 
8. Numbers should not be center-justified in the columns. Right-justified is easier to compare when scanning up and down a column.
9. Use grid lines carefully. Keep in mind. Less is better.
10. Matrix header in bigger font, printed bold on a colored background. One way of visualization is enough to emphasize difference between the lines. Using a list level for other lines will show difference between header and sub-lines. Keep in mind. Less is better.
11. Underline the header. This is useless. Keep in mind. Less is better.
12. Repeating currency symbol. One currency symbol is enough. Is will save value space in every column.
13. Hyperlink to other report is printed in the same font color. Use a other color for hyperlinks, so the user can see that a hyperlink to another report is available.
14. Remove the border of the bar chart.

Some more remarks to take into account during the design of your dashboard.

• A dashboard is used on a daily basis. Display only data which needs your attention, so you can take action to improve. Report the KPI's which needs improvement. The dashboard need to help you to achieve your goal. Data content which will not change overtime is use less on a daily used dashboard.
• We do not see with our eyes, we see with our brains. Content on the dashboard should be clear to understand. If people need to think about what they see, you need to change the visualization of your information.
• A dashboard should fit on one screen. Avoid scrolling bars. With scrolling bars, it can happen that something that needs your attention (RED) is outside your screen.
• Do not use shadows. It is visual fluff with no meaning.
• Do not use logo's and pictures. It is wasting your valuable space. If needed make it small and place it somewhere out of the way.
• What do you want to show or compare? Based on this select the best visualization for it. See : http://www.keepitsimpleandfast.com/2012/07/which-visualizations-should-i-use-in-my.html For example using a pie chart to compare 2 values can be useful  However it can take a lot of useful space. Do not use is to compare multiple values especially if you do not know how many values you need to compare. A bar chart is better in this situation.
• Display consolidated information, summaries or exceptions. Do not display details. Details are used in slice and dice reports to explain the consolidated information, summaries or exceptions.
• Put data which is relevant to each other, close together.
• Use a gray color for your fonts instead of black.

Enjoy it to build dashboards to deliver a good user experience for your users. 

Display last refresh time in Power Pivot

With Power Pivot, you can start analyzing your data off-line. After importing the data you do not need a database connection anymore. A lot of people want to know the date of the last time that the Power Pivot data is refreshed. You can do this in the following way:
Add a tab (LastDataRefreshTime) to the Power Pivot window and use next query:

SELECT GETDATE() AS LastDataRefreshTime

After adding the tab to the Power Pivot Window, one record is added in this sheet. This record can be used in you Power Pivot Sheets.

Select the tab in your Excel sheet on which you want to add this LastDataRefreshTime.
Add a Pivot table and select LastDataRefreshTime from the PowerPivot Field List.

By default the measure is added as a SUM. Change this to MAX.

The last data refresh time will now be displayed in your sheet.
Enjoy the Power of Power Pivot.

Exception from HRESULT: 0x800A03EC during starting Power View in Office 2013

In Office 2013 you can enable Power Pivot and Power View. After opening a Power Pivot sheet you can start using Power View. Power View can be found in the Insert Ribbon of Office 2013.

After pressing the Power View icon next error can occur:
Exception from HRESULT: 0x800A03EC

This error can occur in next situation:

• You have created a Power Pivot sheet with Power Pivot version 11.1.3000 in Office 2010.
• You opened this Power Pivot sheet in Office 2013.
• You press the Power View button in the Insert Ribbon of Office 2013.

Solution: Upgrade your Power Pivot datamodel to datamodel of PowerPivot for Excel 201. To do this follow next steps:

• Open the Power Pivot sheet Version (11.1.3000) in Office 2013.
• Select the Power Pivot in the Ribbon.
• Press the Manage Data Model button in the  Power Pivot Ribbon.
• You will get next message:  This workbook has a Power Pivot data model created using a previous version of the PowerPivot add-in. You'll need to upgrade this data model with PowerPivot for Excel 2013.
  
  
• Press OK
• Start the upgrades of your Power Pivot model.
• After upgrading successfully, you can press the Power View button to start using Power View.

Enjoy the Power of Power View.