Saturday, May 11, 2019

Simplified Creation of a Data Summary Glossary

Data summaries or tables that are exported from a database may use column names that are abbreviated and possibly unclear to some users of the data.  Data exports may also include coded values that are not fully defined within the data table itself.  Definitions of those terms can be helpful to users of the data. One way of providing those definitions is to produce a custom glossary to accompany each data export. Execsql scripts to simplify the creation of such a custom glossary table are available at the following Bitbucket repository: https://bitbucket.org/rdnielsen/execsql_glossary/src/default/.

These scripts require a master glossary table that contains terms and definitions that are to be included in the custom glossary.  The master glossary table can be in a different database than the one from which data are being summarized.  Additional terms and definitions, that are not in the master glossary, can also be added to the custom glossary.  After calling an initialization script that identifies the master glossary table, entries can be added to the custom glossary in any of the following ways:

  • By naming a table or view; the column names and definitions will be added to the custom glossary if they are defined in the master glossary.
  • By providing a string containing a comma-separated list of terms; all of the terms that have definitions in the master glossary will be added to the custom glossary.
  • By providing a term and defintion; these will be added to the custom glossary regardless of whether the term is defined in the master glossary.

A view named glossary is automatically created that selects all items in the custom glossary.

Glossary-creation scripts are available for:

  • Postgres
  • MariaDB/MySQL
  • MS-SQL Server.

Complete documentation of these scripts is available at https://execsql-glossary.readthedocs.io/https://execsql-glossary.readthedocs.io/.