Business Intelligence and Your Database
No matter the industry, it’s a common problem to have more data than anyone knows what to do with. Most of the time data like this is stored in databases, but that does not always translate to information that can be interpreted, or that is actionable. This was exactly the case for one midstream service client after we created two custom software applications to help them manage field ticket and employee data.
Both of these applications use a SQL database on the back end. From this database they were able to perform a basic level of reporting.
Over time as they’ve used the applications and collected a large volume of data, implementing a business intelligence solution became a key priority. Specifically, the client knew that they could enable better decisions about hiring, pricing, and resourcing if they made their information more digestible and easily available.
Why bother with documentation?
The client decided that they should document their databases before beginning the business intelligence project for three main reasons:
1. Establishing documentation creates a clear map of the location of every piece of data, and the correlation between these different pieces in the database.
Benefit: Eliminate the guesswork of digging through the database looking for answers. This also enables improved data analysis and reporting.
2. Documentation makes it possible for anyone to work on and understand the database, even if they weren’t involved with its creation.
Benefit: This can save time and money, because your company doesn’t have to be married to the same vendor. Documentation also makes onboarding new vendors more efficient.
3. If your database isn’t documented, it’s difficult to create an intelligent business intelligence solution.
Benefit: For reasons similar to the above, knowing where everything is and the correlation between data enables your company to efficiently build business intelligence that works.
Documenting the Database
We used Redgate’s product SQL Doc to create what we called a Data Dictionary for each database. This document describes how every table and field are related. In addition, we created a visual representation of the database called an entity relationship diagram.
Both of these assets will be key to accomplishing the three goals mentioned above.