Interactive Dashboards – collecting requirements
This article is part of the series for creating interactive dashboards. If you have not checked the post for Interactive Dashboards – why not do in on your own? , I suggest that you start from there.
Every analytical process starts with asking questions. After we have gone through the basic analytics questions, we should move on to the topic-specific ones. Dashboards take more time to prepare than the average static report and the execution required varies depending on what the end result should be – how often it needs to refresh, what kinds of charts it should show, what is the delivery method, are there any limitations in the size and last but not least – when it is the deadline.
Defining the subject area
This is a critical part of the process. Try to focus on one subject area at a time. Yes, most likely business needs to know how sales are going, are there delinquent credit cards, what is the mortgage loans outstanding, how many customers registered for online banking. However, each one of those is likely to warrant a dashboard of its own, because they suppose different dimensions and different time periods. If you create a chart showing new sales of credit cards in a given month and also delinquent credit cards in the same chart, what does it mean? Is the delinquency data total for the month or a subset of the new sales? If it is the latter is it total number over the lifetime or only within this month? Does a ratio between the two, showing “%delinquent” make any sense?
If you absolutely have to mix different subjects in the same Dashboard, focus on simplicity – clear and understandable KPI, which are commonly used in your organisation and will not bring confusion. Agree in advance with the requestor on the definition of the KPI.
Important note to have here is that one Dashboard usually means one data source (one list). One slicer cannot operate on two data sources, even if they have the exact same values for this slicer. I guess it could be achieved with some VBA shenanigans, but I really do not recommend this.
Defining the timeline
This is another area, where people tend to cram everything together. There are a few basic representations of the timeline (which are by no means exclusive):
Multiple points in time – shows what happened over an aggregated period. For example: sales last month, shown as volume day by day.
Vintage view – one of the less commonly used views. For no good reason as it is usually very insightful. Shows change over time after an event. For example – for customers acquired in 2017-01, what is the cross-sell ratio after 1,2,3….12 months.
Offset view – similar to the vintages. However, the main difference that it aggregates all vintages. For example: what is the average customer profitability 1,2,3,4… months after taking a mortgage loan.
Portfolio overview – statistics over the existing stock of the portfolio.
Real-time data – or near-real time. Focus here is obtaining the data as soon as possible. Usually, has no aggregations and very simple views in order to facilitate the frequent updates. Usually, monitors for technical and operational problems.
Defining the frequency
How often should the Dashboard refresh with new data? This is tightly coupled to the above point. Of course, it can refresh as soon as new data arrives or when someone opens the report, but is it really needed? This can create unnecessary load on the systems especially if you operate with a lot of data and means you have less control and checks over the update cycle.
A general rule of thumb is that the Dashboard should not update more frequently than it’s aggregation period (unless this period is more than a week). So for example – if we are aggregating days – no point to update more frequently than once per day, if it aggregates over a week – update it once weekly. If it is over the whole portfolio – it depends on the rate of growth of the portfolio, but unless it grows over 20% month on month, I would not recommend updating more frequently than once per month.
What about if part of the data is really critical to show more frequently? Handle it in a separate entity – most likely a static report which updates at a higher frequency and then has the aggregated data in the dashboard.
Identifying the main dimensions and KPI
Any dashboard source data has to be in list format, so it allows slicing. As we have discussed previously the list format has aggregate dimensions and KPI. Try and discuss with the person requesting the report, how he or she usually looks at the data, what dimensions are interesting for them. Better yet try and find previous reports requests from them to understand what they usually need. Try and ask them questions about the subject area, so you can identify those dimensions together. Some good questions are listed on the right.
Unless your company/department has strict definitions on the calculation methodology of the KPI requested, you need to also discuss the calculation methodology for each and every KPI. Better yet have them in writing. I cannot stress the importance of this enough, as otherwise, you risk misunderstandings and possible double work for you, re-doing things all over.
Some good questions to identify KPI and dimensions
If you see a negative trend in the data, what subject details would you explore first?
When you want to examine progress, how far back do you look for historical data?
Apart from the main KPI what are other significant indicators of success or failure?
What do you hope would change/not change in the future?
Are there any calculation specifics we need to take care of?
How will the Dashboard be used?
This might look like a strange question for a dashboard and after all else so far, but you would be surprised how often there any some hidden needs below the surface of the request. Maybe part of it is needed for a regular report which is sent elsewhere, so there is a fixed format for some of the charts. For a similar reason, there might be naming or calculation conventions. Maybe part of the data is more useful in table format and not as a visual. Could be that the people using the dashboard have some access restrictions (cannot open Google Drive) or are using an older version of Excel. Maybe part of the dashboard is for a static presentation and it would be more convenient if some of the charts have pre-filtered some dimensions, even if there is a slicer for it.
Before starting to develop definitely make sure that you understand how the dashboard will be used and even better – try to explain to the requester what they can expect to receive.
After all the questions have been asked and answered you can get to the interesting part – the development. As a next step, you should prepare your data sources and back-end logic of the Dashboard.
If the request is complex and time-consuming, one thing that can pay off is to prepare a basic demo with dummy data, a proof of concept on what you intend to do. Show the main charts and main data that you intend to use. The look and the level of complexity. This can help save a lot of time and effort, especially if you have reason to suspect possible miscommunication with the person requesting the dashboard.