Continuing with the theme from earlier blog posts that ‘firm size matters’, this post will address what is involved with building a dashboard for a large firm. Before proceeding with a description of what’s appropriate for a large organization, it should be noted that the approach discussed earlier for small firms could be used in a large firm. It will just require much more effort to produce on an ongoing basis and it will not create a foundation that can be leveraged for other analytic purposes. Another alternative previously discussed is to purchase a fully developed dashboard. This can be a viable option even for a large organization.
Building a dashboard that is based a Data Warehouse for a large organization is something that is best done by a team of technical people. As a reader of a blog post like this, your role would likely be as a champion or manager of the project. The key to successfully creating a working dashboard will be to have a clear mental image of what you are building and assembling a team that has the skills (and experience) needed to do the job. The balance of this blog post will address those issues.
As to the topic of what you will be building, it’s useful to think of three separate components:
ETL is an acronym for Extract/Transform/Load. As the name implies, the objective is to:
Extract data from a source system (your Practice Management, Accounting, or HR systems).
Transform data from disparate systems to a consistent format so that is can be merged together.
Load the data into a single repository: the Data Warehouse
There is an entire category of software tools used to create ETL systems. In addition, there are programmers who specialize in doing ETL work. It’s well worth the effort to find an ETL developer with experience with the ETL software you plan to use.
A Data Warehouse is a database designed to house data for analysis. As such, the data is organized in a way that is best suited to analysis. The key concept to understand about how data is organized in a Data Warehouse is that it’s separated into the ‘Facts’ that will be analyzed and ‘Dimensions’ which are the different ways to describe those Facts. This style of organization is often referred to as a ‘Star Schema’. There isn’t a specific class of tools that are needed to build a Data Warehouse (other than a database engine). You will need to find a database developer with specific experience in building Data Warehouses as there are specific design patterns and techniques that must be used to arrive at a useful implementation.
The final part of the architecture is the Dashboard. Having already created a Data Warehouse will make the process of creating a Dashboard much easier that it would be otherwise as all of the effort of collecting the data for the Dashboard will already be done. You will also have much more flexibility in choosing the tool you will use to create the Dashboard. While you could use a BI (Business Intelligence) tool, you really don’t need the data collection and integration features that can make those tools so compelling to people that don’t have a Data Warehouse to work with. In fact, Excel has all of the features needed to create high quality interactive dashboards. If you are using Excel to create your dashboard, it’s quite likely that analysts you already have on staff can do the job. If you are creating a dashboard that will be viewed by a large number of people, you may still want the services of a professional programmer or BI developer to build a dashboard that runs on a server and is presented in a web browser.
Now that you have a mental image of what you will be building, we can discuss how you can do it.
As with smaller firms, the process of creating a dashboard should start with a simple sketch of the dashboard layout. Knowing what information the dashboard will display will allow you to build a list of data elements needed from your business systems.
It’s wise to start building your development team as soon as you have a mockup of the dashboard. Contractors can be a great resource when initially building ETL systems and a Data Warehouse. While some BI specialists who can do both the ETL development and the Data Warehouse design, not all can. Be sure that the person you choose has experience with the specific ETL tool you are using and the database developer has experience building databases using a ‘Star Schema’.
Armed with a requirements document for the data you will need, it’s time to start talking to the managers of the systems you need to get data from. As a member of a large organization, you probably don’t have the authority to grant a developer access to your HR, Practice Management, or Accounting systems. You’ll need to have your ETL developer work with each team for each system you need to get data from. Try to organize the work so that all of the programing is done in the ETL tool and pulls the data rather than having separate teams create extracts and push the data to you. This will maximize the control you have over a working system.
While the work may start with ETL development, there will be significant overlap with development of the Data Warehouse. Don’t wait until you are done with the ETL to start work on the Data Warehouse. In actual practice, there will be some back and forth where changes to the ETL will force changes in the Data Warehouse and changes to the Data Warehouse will force changes to the ETL.
Once you have the ETL working and populating all of the data that you need for the dashboard, you have two more issues to contend with before you are done with this phase of development (though you can and should start working on building the actual dashboard).
The first issue is validation. Plan on confirming that the data in the Data Warehouse matches the system you extracted the data from. It’s wise to do this in a way that makes it easy to repeat on a regular basis.
The second is loading history. Your ETL will be designed to extract data on a periodic basis (daily, weekly, or monthly). You will probably need to run a modified extract to collect all of the past data that you desire.
If you haven’t already constructed the actual dashboard, it time to finish that part of the project.
As a final part of the project you’ll need to address security issues and consider training. Once those issues are addressed, you’ll be done with the development and deployment phases. You’ll be enjoying a working dashboard!
In case you missed the other two parts to the series, please view below:
How To Build A Legal Finance Dashboard Introduction
How To Build A Legal Finance Dashboard: Small Firm Edition