• Hristo Piyankov

Interactive Dashboards – why not do it on your own?

Dashboards are all the rage right now when it comes to Business Intelligence. Nobody wants reports anymore, everyone needs Dashboards. Companies left and right, offer dedicated dashboard solutions, promising to increase your business productivity and insights. While I am not claiming this is wrong, let’s first understand what this stands for. A Dashboard is:

A graphical summary of various pieces of important information typically used to give an overview of a business“.

So in other words – a dashboard is a collection of charts, which show different views of your data. At this point, the overlapping with “reporting” is pretty huge. In fact, you might say it is just a marketing scheme. However, the real distinction between the two comes when we start adding interactivity to the Dashboards. This is the ability to slice and dice the data. This enabling the end-users to find answers on their own.

Coming back to the dedicated Dashboard solutions. Currently, there are quite a few of them like Tableau, SAS Visual Analytics, Qlik View and others. While all of those are great pieces of software, you need to first ask yourself the question: do you really need a dedicated solution? The advantage of those tools is the amount of data they can handle and the level of abstraction which they offer. Nevertheless before spending (quite a lot) money on them, why not try to do it yourself and see if you really need them?

The dashboards below are INTERACTIVE. If there are display problems, please refresh the page. All dashboards are using Loan Club’s loan data.

Overview example - Tableau

The first Dashboard we will have a look at is done in Tableau. This is a great free option in case you don’t mind your data being public. Otherwise, you can choose one of the paid plans. One of the things I enjoy most about Tableau is that the charts (and not only the filters) are interactive. Meaning you can filter all the charts by selecting an element in one of them. In the dashboard below all charts except the one on the top left are interactive. Another thing is that usually, you can just dump all your data AS-IS in the tool. After that, it will take care of the grouping aggregations so you do not have to. The compression level is actually impressive. Furthermore, it has some very nice charts (maps, Gantt, etc.) and basic analytics functions (clustering, forecasts, trends, confidence intervals).

On the flip side, I find a lot of the customization options lacking. Also setting the layout is somewhat tedious. One of Tableau’s main selling points is that it is extremely intuitive to use. Unfortunately, I don’t agree with this point 100%. Some of the options are not in the first place you would look. It is still a fair bit easier than similar other tools. Last but not least, if you need 100% flexibility you will need to look somewhere else. For example complex formulas and calculations

On the dashboard above, we can see the loan portfolio structure overview. It provides a useful view of the loan volume distribution across the US. Also some interesting interactions between the interest rate, loan amount, clients’ annual income and risk (sub) grades. We can also examine the loan purpose and filter by time of loan disbursement and loan status. And on top of this – filter by all the other interactive chart elements.

Overview example - Excel

The next two dashboards are in Excel. Main advantages are that it costs way less than Tableau if you don’t want your data to be public. The second major advantage is the unprecedented flexibility. What you get is an Excel workbook published to a site and inside you can have pretty much everything you can have in normal workbooks (sans macros). This means you can do a simple dashboard. Or a more involved calculator with complicated worksheet functions. With Excel – you can do it.

The main disadvantage of Excel is a direct result of its main benefit. Because of its great flexibility, it is not a dedicated dashboard tool. This means that for a lot of the things which Tableau takes care automatically, you will have to do them manually. For example, optimise the size of the file, decide where and how to host it, adjust the layout manually. Second, some of the charts take extra effort to put in a dashboard. Like one of my favourites – the box and whisker plot. I also miss the possibility to filter by selecting chart elements. A lot. Really a lot.

Here we can look at the main structure of the portfolio. What is the distribution across clients’ risk profiles (risk grades)? Additionally, what is the term of their loans? What are the average interest rates? We can filter and display this information across many different combinations. Instead, it would be very impractical to show as a separate chart for each. Not to mention hard to read. Here we can focus only on the interesting ones. Finally, all charts use the same set of data slicers. As a result, when we select one of them, all four charts update, to give us a holistic view of a subset of the data. The timeline also enables us, to define the period for which we want to see the portfolio composition.

Time series example - Excel

Our second Excel dashboard shows how new sales are developing over time. We can also compare the volumes year on year and understand if there are some seasonal trends. The views enable us to easily check if there is some significant change in volumes or other key performance indicators. In case there is – we can drill-down the data to find where is the change coming from.

Level of complexity

The three examples above are not very complex. Still, they offer much more data and insight that any standard report. Not to mention senior managers love finding insights on their own. Ultimately, the interactivity of the Dashboards enables them to do so. Last but not least this approach will save you a significant amount of time preparing reports. Because the data will be already pre-computed and can provide all the needed answers.

The first dashboard is developed in Tableau and made available with the free license for Tableau public. The process is very straightforward. Register, download the app, develop the dashboard, publish it on the Tableau servers. Finally, either embed it on a webpage or give users with the Tableau address.

The next two examples are developed in Excel, then the file is available to this site with embedding it from OneDrive. If the data in the source file changes – you will see new data in the dashboards instantaneously. In case you do not want to use OneDrive, SharePoint provides similar functionalities for embedding Excel data. If you want a full-fledged web solution, without any offline file system or if you want a 100% free implementation – you can use Google Sheets. I have a hard time calling Excel a paid solution, although it technically is.

There is a lot of potential for adding extra data and dimensions. One of the reasons I kept the examples simple is the file size considerations (in the case of Excel). The above two files take just 150KB each which is ideal for the purpose of this article. It could be even less if both Dashboards were in one file. If you decide to develop a comprehensive dashboard for your business – you can easily fit data 100-200 fold bigger – this means a lot more dimensions/slices of the data and many other KPI. This will likely bring your file size up to 20-30 MB. Still, this is still quite small and easily handled for transfer over the internet for a limited number of users.

And the implementation time? Starting from exploring the data, to asking what you want to achieve and present, going through the implementation, testing and collecting feedback from the end-users, you can have your first complete dashboard build within a week or two at most. So, before starting a months-long project on implementing a market solution, why not first try to do it yourself with the tools which are already available to you? Worst case scenario you will be walking in your project with a lot more experience to go on. Eventually, the principles are pretty similar.

Next steps

All that being said, I want to make a disclaimer – I am not saying market solutions do not have advantages. They do and many of them are significant. Speed and scope to name the major two. I am only saying that they are not the only possible route.

Another point is that making data filterable by slicers is easy. Making a comprehensive dashboard is not. The complexity does not lie within the technology, but rather in the skillset of the person preparing it. It takes a good mix of technical skills and business acumen in order to prepare a dashboard both rich and functionality, refreshing data automatically and as useful to business end-users as possible.

In order to ensure the best possible results, we will go over a series of articles (to follow), covering every step of the creation of really useful Dashboards.

#Analysis #Beginner #Fundamentals #Reporting