If you’re new to Business Intelligence (BI), new concepts come with new vocabulary and that can be one of the biggest learning hurdles that you will face! That is, until you run into a database that is more akin to a trash can than a well-organized library – but that’s a story for a different time.
Drill-Up, Drill-Down, Drill-Through, Drill-In, Drill-to-Detail, Drill-Across – what the heck does drilling have to do dashboards and reporting? Was the person who coined these terms related to a woodpecker?
I can’t speak to your woodpecker supposition, but I can help you understand what each of these terms generally means from the perspective of Business Intelligence and how using one in a solution – such as Dundas BI – would look.
Drill-Down
The best way to think about how drill-down works, is to tell yourself that you’re ‘walking through the different levels of a hierarchy’. To drill-down, by definition, requires the use of hierarchical data where values are grouped into levels.
Here are some examples of levels:
- If your data is date-based, your level groupings might be something like:
- Year, Month and Day, OR, Year, Quarter, Day and Hour.
- If your data is geographical, your levels might be something like:
- Country, State/Province, Postal Code and City.
With these levels, lower-level data is essentially rolled up into the next level, which is then rolled up into the next level above it, and so on. There’s no new data being presented, but the data is instead just being unrolled as you drill-down.
Here’s what that looks like:
You can even drill-down multiple times to ‘walk through the hierarchy’ from level-to-level as you wish.
For a drill-down to be possible, there needs to exist at least two levels or else there won’t be a level to unroll. Conversely, there is no limit as to the number of levels that you can traverse. When someone right-clicks on a data point to drill-down, this kicks off the process of drilling-down from the general to the specific.
NOTE - sometimes drill-down is referred to as drill-in.
Drill-Up
If asked, I would hazard a guess that most people would tell me that drilling-up is simply the opposite of drilling-down, to undo the drill-down that you just performed. And that’s correct – people mostly do only use drill-up as an undo system for drill-down. What most people miss is that drill-up can also be used as a data exploration entry point for rapidly changing data.
Think it of this way, if you have daily data, it makes sense to default your dataset to show the bottom level so users can see the latest results. If you do something like this, drill-up becomes a mechanism to allow the users to further explore trends while still quickly being able to see new results.
Here’s what I mean. The following chart contains data coming in at an hourly basis and as a default, it is showing all data from ‘today’ as a sliding window. This is perfect as it will allow the user to see what has just happened.
Having seen the incoming hourly data for the day, the user might be interested in seeing how they are trending at the day level. To see the next level up, they’d click ‘Drill-Up’. In this drill-up solution, we work our way backward in the hierarchy and move from the hour level to the day level.
After drilling-up, the window of ‘today’ no longer makes sense as it would result in a single data point. Instead, the chart is automatically expanded to show the next level available, which in our case is ‘month’. The results are daily data points for this month.
Just like before, you might want to take this a step further and drill up again. The level above ‘month’ is ‘year’ and since there is no level greater than year, we would move up to the year level and show all data for the last decade. This is the maximum level that a user could drill-up to using the hierarchy that we have provided.
Wow. Would you look at that? We’ve been trending down on the year since 2017, and yes, 2020 looks good now, but we mustn’t forget that it’s only January and a lot can change throughout the year, so we’ll keep an eye on it.
Drill-Through
Drilling-through is just a fancy way of saying navigation, but it’s a very important concept as it allows users to further explore related data by clicking on a visualization. In this experience, you are taking what the user clicks and sending them to another dashboard or report to show related data.
In the following dashboard, the user can see a summary of a key metric from 5 different departments on the left half of the dashboard.
As you place your mouse over a department, you can see that the visualization can be highlighted to give our chosen visualization the feel and appearance of a button.
Clicking on the visualization brings the user somewhere completely different: a Manufacturing Dashboard where we can see all the metrics for our Manufacturing Department.
Drill-to-Detail
Have you ever looked at a data visualization and wondered, ‘how could that be?’ In the case of the following data visualization, you can see that there is even a note added to the data point by someone asking for a check. Something is clearly going on with this outlying data point.
Drill-to-Detail is the process of going from an aggregated visualization to raw data for exploration and discovery. The best way to do this is to have a destination view and a lot of filtering capabilities so that the user can further explore data through self-service based discovery.
By clicking on the chart from above, we have implemented just what was described and have taken the user to a new view and given the option to further explore data by filtering.
It’s also recommended to parameterize Drill-to-Detail since there can be a lot of raw data and you might as well at least take the user to the raw data that interests them, rather than giving them all data as an experience.
Drill-Across
Good Dashboards can help users in both viewing insights and taking actions based on those insights. More often then not, the place where an action is being taken is another software system like a CRM or an ERP. Your users might have a chart showing summary data and might want to click on a data point to go back to the source and make a change.
Drilling-across is the process by which you can drill from one application to another from your data visualizations. From the visualization below, a user can automatically drill across into a CRM system using the correct customer number.
Hopefully this helps clear up some common use cases and definitions for ‘drilling’ in Business Intelligence. The reality is that you’re likely going to hear every one of these actions defined as ‘drill-down’, but as you have seen, specific definitions exist for each.
Being concise, especially when it comes to talking about specific requirements is important to make sure that users and implementers are speaking the same language.
About the Author
Jeff Hainsworth is a Senior Solutions Architect at Dundas Data Visualization with over a decade and a half of experience in Business Intelligence. He has a passion for building, coding and everything visual – you know, shiny things! Check out "Off the Charts... with Jeff", his platform for great content on all things analytics, data visualizations, dashboards, and business intelligence. There’s something for everyone!
Follow Us
Support