In the previous chapters, we filled out a metric spreadsheet. We took a vague ask from a Point Person and turned it into a well-defined list of metrics, calculations, and data sources. We will now use the completed metric architecture to create various SQL queries.
The columns of the metric architecture map to a SQL query.
Take a look at a couple of sample queries we could create from this spreadsheet for the Operations Cost metric:
Total Operations Cost
Total Operations Cost by Department
(When we introduce a GROUP BY statement we must include any column there in the SELECT statement as well)
Total Operations Cost by Department by Month
One of the beauties of SQL is that it can do the logistical work of finding the columns in the data sources, and it can also compute mathematical equations. Most other methods require you to first access the unaggregated data via SQL and export the data into the tool so that you can create the calculations. Since SQL is tied to accessing the database when the underlying data changes, you can rerun the query and see the latest data. This is more efficient than exporting data into another tool.
If you are new to SQL check out Chartio’s tutorial here:
If you are struggling with understanding how Aggregations or Subqueries work check out:
If you are running into errors or are getting 0 rows returned check out:
Do not assume your query is perfect. You should check it by looking at other peoples’ queries and/or by having the Data Gatekeeper review it.
Depending on the BI tool that you are using you can see other people’s SQL queries. This can be very insightful. You can take note of data sources they used that you were not aware of. You can also see if other people have complexity in their queries.
Complex Query example:
Complexity in a query typically suggests the data is nuanced, messy, or certain business logic needs to be adhered to. If you come across a complex query that is for the same or a similar metric as the one you are working on reaching out to the creator. You should try to understand what the extra parts are all about so you can incorporate what is relevant into your own query.
On the other hand, if other people have similar looking queries for similar metrics you are probably in the clear. However, you still will want to get someone else's eyes on it for verification.
Getting a code review on your queries is a best practice. Reach back out to the Data Gatekeeper to validate your queries are calculating their metrics correctly. Having the metric spreadsheet facilitates this process since they can see your work and how you go to the query you wrote.
Take the tables of data line them up with where they fit into your design.
Go through each table and create the corresponding data visualization in your BI tool. Put all the visualizations together into your final dashboard.