Common
These are tables of things that are commonly used in analysis or data requests. They are csv files that we store in the pipeline to be used with other data from the data warehouse. They are manually updated and maintained. Program and Region names in these tables are consistent with the Census to help minimize cleaning. If one adds a new row to the raw csv files they need to ensure that persists or otherwise add in cleaning. The common tables are in the soload pipeline.
The raw tables are located here GitHub/soi-dwh/soidatawarehouse/soload/soload/commontables
Currently, only the MultipleEventOwners table is used in the HAS pipeline. Others are standalone tables that you can join to for analysis. Look at the attached Excel file for the specific field information.
If you have any questions, please contact John or Karly.
WorldGames
Known world games. This helps with data cleaning to identify these events.
| colname | colvalues | coltype | detail |
|---|---|---|---|
HAS_EventNameRaw |
Exact spelling and capitalization of the EventNameRaw from HAS | varchar | Exact spelling and capitalization of the EventNameRaw from HAS for a world games event |
EventName |
Freeform | varchar | What the desired name is for this event |
Program |
Program in exact spelling from Census | varchar | Program in exact spelling from Census that hosted this event |
Region |
Region in exact spelling from Census | varchar | Region in exact spelling from Census that hosted this event |
City |
Freeform | varchar | City of the event |
State |
Freeform | varchar | State of the event |
Country |
Freeform | varchar | Country of the event |
RegionalGames
Known regional games. This helps with data cleaning to identify these events.
| colname | colvalues | coltype | detail |
|---|---|---|---|
HAS_EventNameRaw |
Exact spelling and capitalization of the EventNameRaw from HAS | varchar | Exact spelling and capitalization of the EventNameRaw from HAS for a regional games event |
EventName |
Freeform | varchar | What the desired name is for this event |
Program |
Program in exact spelling from Census | varchar | Program in exact spelling from Census that hosted this event |
Region |
Region in exact spelling from Census | varchar | Region in exact spelling from Census that hosted this event |
City |
Freeform | varchar | City of the event |
State |
Freeform | varchar | State of the event |
Country |
Freeform | varchar | Country of the event |
ProgramsCDC
List of programs that are related to the CDC
| colname | colvalues | coltype | detail |
|---|---|---|---|
Program |
Program in exact spelling from Census for programs related to the CDC | varchar | Program in exact spelling from Census for programs related to the CDC |
MultipleEventOwners
In Legacy GAS sometimes there are events with multiple event owners for a single event. This table is used in the HAS cleaning process to combine those events with multiple owners into a single owner.
| colname | colvalues | coltype | detail |
|---|---|---|---|
HAS_EventNameRaw |
Exact spelling and capitalization of the EventNameRaw from HAS | varchar | Exact spelling and capitalization of the EventNameRaw from HAS for an event with multiple event owners in legacy HAS |
Host |
Program that hosted the event in exact spelling from Census | varchar | Program that hosted the event in exact spelling from Census |
HostRegion |
Region that hosted the event in exact spelling from Census | varchar | Region that hosted the event in exact spelling from Census |
Notes |
Freeform | varchar | Freeform notes on the event |
Country_ISO_Codes
List of countries and their ISO codes.
| colname | colvalues | coltype | detail |
|---|---|---|---|
SOI_Census_Country |
Program in exact spelling from Census | varchar | Program in exact spelling from Census |
Country |
Country name from source | varchar | Country name from source |
OfficialStateName |
Official State Name from source | varchar | Official State Name from source |
Sovereignty |
Sovereignty from source | varchar | Sovereignty from source |
ISO_3166_1_Alpha2 |
Two letter ISO Code | varchar | Two letter ISO Code |
ISO_3166_1_Alpha3 |
Three letter ISO Code | varchar | Three letter ISO Code |
ISO_3166_1_Numeric |
Numeric ISO Code | numeric | Numeric ISO Code |
CountriesCDC
List of countries and their ISO Codes that are related to the CDC.
| colname | colvalues | coltype | detail |
|---|---|---|---|
Country |
Program in exact spelling from Census | varchar | Program in exact spelling from Census for countries in the CDC |
ISO_3166_1_Numeric |
Numeric ISO Code | varchar | Numeric ISO Code |