Visualization tools are excellent for making pretty displays out of massive amounts of data. This is one in a series of blogs that help use the power of reporting or visualization to catalog in creative ways.
I created this tool out of necessity due to running into the following scenarios:
- Business logic changed. We needed to find all occurrences to update the derived logic.
- A table column name changed. We had to find all reports containing that column and make edits or the reports would break.
- Before rolling off a project, the client wanted a list of all hard-coded items in our data source SQL.
- A table failed to load. We needed a list of all reports with data coming from that table.
The commonality here is that there is a sense of urgency and I never have time to find and manually look through thousands of lines of data source code.
At minimum, a few up-front steps can save you hours during a potentially stressful time.
Solution: Here are three (3) easy steps to create a searchable Code Repository Library in any reporting or data visualization tool. In this example SQL data sources are cataloged in Tableau
- Catalog your data source code and reports in Excel. Sounds crazy, but chances are you have this documentation already.
Recommended columns: Report ID*, Report Name, Data Source Name, Code (pasted), SQL Order**, Hard-coded? (optional if you have time to identify hard-coded values or derived logic).
*Create a Report ID column to associate with your Report Name.
**Assign SQL order for the dashboard to display the SQL properly.
- Import data into a reporting or visualization software. Create Library report with 4 columns:
SQL Order*, Report Name, Data Source Name, Code
*Hide or minimize SQL Order column. It is for display sorting only.
- Add a wildcard filter on SQL Code field. Add any other relevant filters.
Now you have a searchable Code Repository Library!
Result - the next time someone needs to identify items in code, it is not necessary to manually collect and look through thousands of lines of code!
Remember, this catalog needs to be maintained. Code needs to be updated and added as developed.
About the Author
Wendy Greene is a senior consultant in CapTech's Data & Analytics Practice Area. She is a Data Visualization generalist who has also authored Standards and Guidelines for Tableau, and Spotfire. She also teaches Spotfire flipped course and enjoys creating and demoing dashboards.