For quite a period of time, our customer was using a SQL Server database that at the long last became disorganized and fairly difficult to arrange. The difficulties with finding and retrieving the right data negatively influenced their performance. In addition, their server was storing duplicating views and tables that utilized space.
The problem
Our customer considered hiring several data analysts to reinforce their team. However, extra people in the team couldn’t address the challenge for they weren’t aware of how and where the server stores the data.
Regarding these problems, our customer could receive analytics based on invalid data. This is the case when the effectiveness of established processes, and in particular, poorly organized database influenced high-level business solutions.
The solution
To clean up their server we leveraged a combination of proven practices to arrange the environment for the data analysts. In particular, we took the following actions:
- Grouped the tables and views within a database by schema based on business logic and function
- Reorganized the databases inside the server by proper naming convention
- Updated the naming conventions
- Defined the most accurate views and tables to leverage for querying results
- Combined the logic of similar views to produce one view and removed the duplicating sources
Here is an example of some of the cleanups you can do.
Organization with Schema
In this example, the dbo.ApplicationGrouping table should have a prefix of dim.
In addtion, the tables are mostly all in the dbo schema. Using the dbo schema is generally not a good idea. Instead, these tables could go into the existing reporting schema.
Thus, we created a clean and well-organized environment for the data analysts, that allows for effectively retrieving the necessary data and makes it easy to brief new hires. What is more, we restored the server space, that allows for saving our customer’s funds.
