Building a dashboard for your firm is usually a complicated project. This post will help untangle some of the complexity and provide some perspective about how to approach the challenge.
Before diving into a description of how to build a dashboard, it should be mentioned that the easiest way to get a dashboard system is to simply buy one from your Practice Management System vendor. As you’ll soon see, the dashboard presentation itself is just the ‘tip of the iceberg’. There’s a huge amount of complexity involved in collecting all of the data required to create a simple informative overview.
OPTIONS TO CONSIDER
Much of the complexity involved with building a dashboard derives from the fact that the solution most suited for a large firm won’t be appropriate for a small firm or an individual group within a large firm. Before delving into what’s different in each approach, some technical background in is order.
When you think of a dashboard, you probably imagine a web page with numerous charts and gauges that provide a lot of information at a glance. Every chart or gauge gets its data from one of your business systems (Practice Management System, Accounting System, etc.). These source systems use a database structure that is optimized for transactions. Your dashboard will be best served by a database that is optimized for analytics.
The effort involved in finding the data needed for your dashboard in the transactional systems and building an Extract / Transform / Load (ETL) infrastructure typically constitutes the bulk of the effort in setting up a dashboard. Remember the buzzword ‘ETL’. It will be an important topic in many of your discussions about building a dashboard. The purpose of ETL is simply to:
1. Extract data from the source system,
2. Transform the data into layout suitable for analysis.
3. Load it into an analytic database.
Note that you do NOT want to have your dashboard query for data directly from your transactional systems! Databases usually lock records when they are part of a query, and it’s easy to interfere with transactions by having the dashboard run queries. Interfering with billing or accounting is an obvious career limiting move. The important concept to remember is that the dashboard should use data that is extracted during off peak hours.
We’ve established that you need find the data required for your dashboard, and you need to get it without interfering with normal business operations. If the dashboard is for a large firm, you’ll probably want to put that data in a data warehouse and have a robust automated ETL system constructed. If the dashboard is for a small audience, the cost of a full-blown data warehouse and ETL system might not be justified. In this case, a more creative approach that doesn’t require a data warehouse might be needed. This begs the question ‘Where do I put my data if I don’t have a data warehouse?’. Oddly enough, the answer probably lies in the tool that you will use to display your dashboard.
While it’s possible, and in some cases appropriate, to write a program to display your dashboard, there now exists an entire category of programs for the task – Business Intelligence (BI) Tools. Some of the more notable examples of this type of software are Microsoft’s Power BI, Tableau and Qlik. This category of software has three primary benefits:
1. Presents visualizations that convey information (charts and graphs)
2. Has an internal data structure for analytics
3. Has facilities to import data (similar to ETL functionality)
The most obvious benefit is clearly the ability to build the charts and graphs that are the meat of your dashboard. Modern BI tools have wonderful features to help you tell a visual story which summarizes mountains of data. Imagine Excel’s charting and pivot table features on steroids.
PRO AND CON
The internal data structure used by these tools makes it possible to collect and analyze large amounts of data on a desktop computer. This makes it possible to use a regular PC to perform tasks that once could only be accomplished on a large server. Bear in mind, however, that BI tools create their analytic data structure in memory every time you start the BI program. This contrasts to a data warehouse which is created once and saved on disk, and then updated. The analytic data is always ready in a data warehouse in comparison, you generally must wait for it to be created in a BI tool.
Modern BI tools also have sophisticated features to collect and import data. There is a great deal of overlap between BI tools import features and full blown ETL systems. In some situations, BI tools import, and integration features are better than those found in ETL systems. The downside is that BI tools lack features for robust automation.
TO WAREHOUSE OR NOT TO WAREHOUSE
Now that you have an understanding of the basic features of modern BI Tools along with a big picture overview of what is involved with building a dashboard, we are ready for some perspective about how a BI tool fits into the overall picture and which general approach is appropriate for your project.
As discussed above, the decision about whether to use a data warehouse or not will drive most of the decisions about the overall architecture of your dashboard. A big part of this is with regard to how much of the project is accomplished with a BI tool.
SMALL PROJECT SCENARIO
Let’s assume that you are building a dashboard system for a small practice. Building a full-blown data warehouse and ETL system wouldn’t be practical as it would typically entail many months of full-time technical labor to construct. For a small practice, the entire project could be accomplished with the BI tool alone. The end result would not be fully automated, and it wouldn’t be practical to share the dashboard with a large number of users, but it could be a fully functioning dashboard with a reasonable amount of effort and a reasonable amount of time. As you’d probably expect, there’s a lot more to be said about how to implement this approach. That will be the topic of a future blog post.
BIG PROJECT SCENARIO
The other end of the spectrum is implementing a dashboard for a large organization. In this case, there are compelling reasons to build a full ETL system along with a data warehouse. The primary benefits of the data warehouse approach are:
- The entire process of collecting and storing data is fully automated.
- A data warehouse can house data for more than just the dashboard. It can make reports much less expensive to develop and can make ad hoc analysis much more effective.
- It makes it much more practical to build a rich analytic database design that automatically tracks how descriptive information changes over time.
- It supports large numbers of users.
The general flow of data with the data warehouse architecture is that a scheduled ETL process would extract data (during off peak hours) from transactional business systems and load that data into the data warehouse. The dashboard would be implemented as a web page that would access data in the data warehouse. For a small number of users, the presentation might be done with a BI tool. If a large number of users will view the dashboard, licensing costs might justify the cost of developing a dedicated program. Either way, a good BI tool can play a critical role in prototyping and developing the system.
Describing how to build an enterprise class data warehouse and dashboard system is beyond the scope of a simple blog post. Watch for a future post that will help you build a mental image of how to approach building this type of system.
For a small number of dashboard users, use a BI tool to collect data and present information.
For a large number of dashboard users, build ETL to populate a Data Warehouse and use a BI tool or dedicated program to present information.
Here are the other two parts to this overall series:
How to Build a Legal Finance Dashboard: Large Firms Edition
How to Build a Legal Finance Dashboard: Small Firms Edition#PracticeManagementandPracticeSupport