As previously discussed, firm size matters when building a dashboard.
It’s not the dashboard itself that’s the issue, it’s all of the supporting infrastructure. Large organizations will devote an entire team for many months to build programs that will automatically Extract data from business systems, Transform the data into a consistent format and Load the data (ETL) into a repository that houses all of the data that a dashboard needs. You probably can’t afford that expense, so some compromises are needed to let you create a dashboard.
As a small firm, you are really left with two alternatives: buy a pre-built dashboard or get creative about collecting data.
If you can buy a pre-built dashboard for your practice management system, it will almost certainly be a better option than creating your own from scratch. Just be sure to ask if it uses a data warehouse and has scheduled jobs to run the ETL needed to keep it up to date. If it does, you’ll have the same basic architecture that a large organization would use.
If you can’t buy a pre-built dashboard, you’ll need to get creative about how you collect data for the dashboard. Before you worry about that, you should lay out what your dashboard should look like. Once you know what information your dashboard will present, you’ll be able to make a list of the different data sets you’ll need to collect. Build this list, and add to it the sources for the data on the list. There’s no need to proceed with anything else until you know what data you need and where you are going to get it. Actually having samples of the data you will need will make the rest of the project easier.
Having identified the information you want to present (and where you’ll get the data from), you need to choose a tool to aggregate data and present information.
Business Intelligence (BI) software is the category of tools designed for this task. Be aware that there are a lot of tools in this category, and the state of the art is changing rapidly. Some products are cloud based, others run locally. Some are open source while others require costly licensing. If you already have Microsoft products, their Power BI solution is a compelling option. For other firms, Tableau has good features for building a dashboard. There are a lot of other BI packages, these two simply provide a place to start your search. Plan on spending some time watching demo videos (YouTube has tons of them). Once you find something you like, see if you can download an evaluation copy of the BI software.
BI software has three primary functions: collect data (ETL), aggregate data and present information. As you’ll recall, the thing that large organizations can afford to do that small ones can’t is automating ETL and building a data warehouse. All BI tools have an internal data structure that replaces some of the functionality of a data warehouse, so ETL features will be the biggest differentiator.
Before covering some of the more creative ways to collect data for your dashboard, a description of the more traditional ways BI systems acquire data is in order. One way that can be dismissed immediately is to use a direct database connection. This is an approach for skilled programmers, so we’ll focus on importing files. It’s the least technically demanding approach. Virtually all BI tools can import data from files, and it’s the most common way of collecting data.
While an in-depth discussion of file formats is beyond the scope of this blog post, a brief overview of terminology is in order. Common file formats for transferring data fall into two categories: Excel and plain text.
The most important point to remember when using Excel files is to keep the layout simple. Ideally, you should have column labels on row 1 and a consistent number of columns in each row. If you stick to this simple rule, most any BI tool will be able to ingest the data. Unfortunately, few spreadsheets in the real world have such a simple layout. Consider recording a macro to remove unneeded columns, headers and footers before saving the cleaned up sheet to a common subdirectory used to collect data for your dashboard. The process of extracting only desired data from a spreadsheet is one area where a modern BI tool might be able to help. This is another topic where a quick YouTube search might help you streamline the effort needed to pull Excel data into your BI tool.
The topic of text files is a bit more complicated than Excel in that there are a few different styles of text layout that are used to transport data. The most common layout is a ‘Delimited’ text file where each field is separated some special character. Of all of the Delimited formats, the most common is often referred to as a CSV (comma separated value) file. Be aware that one potential problem with delimited files is when the separator character appears in the data (such as a comma in a company name). This is another topic where a quick internet search will help get you oriented. Another format is ‘Fixed Width’, where every column has a consistent width for every row. As you might expect, this results in a lot of wasted space. It’s a layout that was much more common back in the mainframe days. Finally, there is XML (eXtensible Markup Language). While it’s a rich, flexible way to transfer data it’s usually much more difficult to configure your BI tool to extract data from it (though this is another area of rapid change). You should be able to view any format text file with a text editor (as opposed to a word processor). If you see any odd formatting or non-text characters in a text editor, your BI tool might have problems ingesting data. If you are using Windows, it comes with a text editor called ‘notepad’.
Having discussed how to package data for you BI tool, it’s time to discuss the ‘creative’ ideas to access data.
One of the best sources of data are the standard reports that came with your practice management or accounting systems. Nearly all systems allow you to save reports as either spreadsheets or text files. Once you have saved a report to a file, you can prepare it for the BI tool using the approached discussed above if your BI tool can’t ingest it directly.
Another approach to extracting data is to see if the source system has a query tool. Query tools let you extract data using a drag and drop approach to selecting data. Once you have a query that produces the desired data, use the same techniques to prepare and save it as you would for standard reports.
It’s also possible to extract data from web pages and regular programs installed on your computer. If the program that you are trying to get data from presents data in a table format, simply copy the table contents into Excel or a text file. You will probably need to do some cleanup on the copied data, but a simple copy and paste will at least get you the data. The ability to extract data directly from the screen is another area where BI tools have been making progress. Check your chosen BI tool to see if it has this capability.
Once you have defined what will be on your dashboard and collected the data you need, you are past the worst part of the project. The rest of the journey will be the forte of whichever BI tool you have selected. Make good use of your preferred web search engine and YouTube for tutorials describing how to import data a create graphs. Once you’ve finished the project, you’ll see that building the actual dashboard is the easy part!
In case you missed the other two parts of this series, please view below.
How To Build A Legal Finance Dashboard: Large Firm Edition
How To Build A Legal Finance Dashboard Introduction