There is quite a bit of background information about the scoring system that is necessary to explain here. Wanna skip the hoopla and get straight to the technical stuff? Jump to here.
I will spare you hours of your life by skipping over the highly opinionated process of how judges create a quantitative assessments of an art form, but rather explain the actual data itself and the details that that need to end up in the database.
The scores for every event are presented in a “recap”, as shown below.
Single panel recap
At the top, there are the general details for the particular event: name, date, location, round (Prelims / Finals), and competing class i.e., ”Independent Open” (there are 6 different classes of competition).
Jumping into the actual recap, each competing unit has a row with all of their respective scores. Above these scores are the 4 different “captions” (categories they are judged on) and the judge’s name. Each judge gives a rating 0-100 in two different “sub-captions”. Using a weighted system, those two sub-caption scores are calculated into a total caption score, which is then added together with the other caption scores for the unit’s subtotal. There are penalties and deductions as well, which are assessed after the subtotal, creating the final total on the right.
Within each box is a second number, a ranking of that individual score or total as compared to all of the groups. Although they do not affect the actual scores, these rankings are important datapoints provided to compare all of the units amongst themselves.
To further complicate things, at some high profile events and the World Championships, there is a double panel of judges (every caption has two judges). Each judge still gives their ratings as before, however, the total between the two judges in each caption is averaged for a final caption score, which is then added up together with the other 3 captions for the final subtotal and total score. This variable of single vs. double panel events will affect a number of items later.
Double panel recap
Did you get all that?… Great! Now let’s get this data into an actual database.
When determining how to structure this database, I first had to unpack how much information was really in these recap tables. I quickly realized that these recaps are actually quite complicated, albeit well designed, compacting a lot of information into one digestible table (shoutout CompetitionSuite!). They are essentially a multi-layered pivot table with each cell containing not one, but two values. To get this information into a database, it needs to be deconstructed.
In total, there are 29 numerical values that need to be extracted for each unit, and 61(!) if a double panel. And that is just the numbers. There are also the show details, judge names, captions, etc. This led to a decision to create either extremely wide or tall data. Ultimately, it seemed more logical to have wide data and keep one unit per row, with a column and corresponding value for every detail.
The actual process of extracting all of this data for an entire season of performances was done using Python. You can read more about that in the Extracting the Scores section.
Once I found myself with spreadsheets full of this extremely wide data, it did not take long to realize I would not be uploading all of this as is into one table and calling it a day. Trying to maintain that, let alone query it later, would be a mess for a whole variety of reasons.
Rather, I realized there are two main types of data included here, the quantitative numbers of the scores and ranks, and a few categories of repetitive qualitative details. This would lend itself perfectly to having a structure of fact and dimension tables.
I categorized all of the various details into six main dimensions, each with their own table:
https://dbdiagram.io/e/69f1674bddb9320fdc823226/69f16809ddb9320fdc823daf
Some of these dimensions are standardized, individual event data is not necessary to know the different values (Season, Competitive Class, Caption). For these, it was as simple as manually adding the information and assigning dimension IDs.
However, details such as event information, judges, and the competitive units can vary in each recap, and total hundreds of values by the end of the season. These needed specific queries of the “raw” extracted score data from earlier in order to load the data into the respective dimension tables. A mini “ETL” if you will…
(The SQL code is admittedly a bit cumbersome, but the result is beautiful clean data!)
DIM_SHOW
# Upload event details to DIM_SHOW
INSERT IGNORE INTO DIMENSION.DIM_SHOW
SELECT DISTINCT ''
, WEEK(STR_TO_DATE(DATE, '%W, %M %d, %Y'), 7) - WEEK(FIRST_WGI_SHOW, 7) + 1 SEASON_WEEK
, `SHOW` FULL_NAME
, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(`SHOW`, 'WGI ', -1), ' Percussion', 1)) SHORT_NAME
, LOCATION
, TRIM(SUBSTRING_INDEX(LOCATION, ', ', 1)) CITY
, TRIM(SUBSTRING_INDEX(LOCATION, ', ', -1)) STATE
, STR_TO_DATE(DATE, '%W, %M %d, %Y') DATE
, ROUND
, DOUBLE_PANEL # A 1/0 flag created during the score extract
, 0 WGI_CHAMPIONSHIPS
FROM STAGING.STG_RECAP SR
JOIN DIM_SEASON DS
ON EXTRACT(YEAR FROM STR_TO_DATE(SR.DATE, '%W, %M %d, %Y')) = DS.YEAR;
# World Championships events require a separate DIM_SHOW query
INSERT IGNORE INTO DIMENSION.DIM_SHOW
SELECT DISTINCT ''
, NULL SEASON_WEEK
, `SHOW` FULL_NAME
, TRIM(SUBSTRING_INDEX(`SHOW`, ' - ', -1)) SHORT_NAME
, LOCATION
, TRIM(SUBSTRING_INDEX(LOCATION, ', ', 1)) CITY
, TRIM(SUBSTRING_INDEX(LOCATION, ', ', -1)) STATE
, STR_TO_DATE(DATE, '%W, %M %d, %Y') DATE
, ROUND
, DOUBLE_PANEL
, 1 WGI_CHAMPIONSHIPS
FROM STAGING.STG_RECAP SR
JOIN DIM_SEASON DS
ON EXTRACT(YEAR FROM STR_TO_DATE(SR.DATE, '%W, %M %d, %Y')) = DS.YEAR;
DIM_UNIT
# Upload units to DIM_UNIT
INSERT IGNORE INTO DIMENSION.DIM_UNIT
SELECT ''
, UNIT NAME
, UNIT_LOCATION LOCATION
, TRIM(SUBSTRING_INDEX(UNIT_LOCATION, ', ', 1)) CITY
, TRIM(SUBSTRING_INDEX(UNIT_LOCATION, ', ', -1)) STATE
, CLASS
FROM STAGING.STG_RECAP;
DIM_JUDGE
# Upload judges to DIM_JUDGE
# Recaps online include only the first initial last name.
# Full names are added later using WGI data
# https://www.wgi.org/percussion/p-judges/
INSERT IGNORE INTO DIMENSION.DIM_JUDGE (RECAP_NAME, FULL_NAME, CAPTION)
SELECT RECAP_NAME
, FULL_NAME
, CAPTION
FROM (--
SELECT DISTINCT TRIM(CONCAT(SUBSTR(`Head Judge`, 1, 1), '. ',
SUBSTR(`Head Judge`, INSTR(`Head Judge`, ' ') + 1))) RECAP_NAME
, `Head Judge` FULL_NAME
, 'Head Judge' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Effect - Music 1`
, NULL
, 'Effect Music' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Effect - Music 2` RECAP_NAME
, NULL
, 'Effect Music' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Effect - Visual 1`
, NULL
, 'Effect Visual' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Effect - Visual 2`
, NULL
, 'Effect Visual' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Music 1`
, NULL
, 'Music' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Music 2`
, NULL
, 'Music' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Visual 1`
, NULL
, 'Visual' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Visual 2`
, NULL
, 'Visual' CAPTION
FROM STAGING.STG_RECAP
UNION
SELECT DISTINCT `Timing & Penalties`
, NULL
, 'Timing & Penalties' CAPTION
FROM STAGING.STG_RECAP) A
WHERE RECAP_NAME IS NOT NULL;