Country Code Mapping
The country codes mapping tables facilitate reporting and analysis by country across disparate collections in the Warehouse. Data Administration staff administer a process in which country code values from various administrative source systems (e.g., ATLAS, OASIS, PennERA, PennWorks/Payroll, SRS, and others) are regularly compared, reviewed, and mapped to values in a master table. The results of this process are exported to the CCMAINT_ADMIN schema in the Data Warehouse on weekday nights, and stored in the following tables, which are available to all users of the Warehouse.
Refer to the Country Code Mapping collection diagram to see all sources, extracts, mapping tables, and the COUNTRY_CODES_MASTER table.
COUNTRY_CODES_MASTER
Contains one row per country, identified by COUNTRY_CODES_MASTER_ID. In addition, COUNTRY_CODES_MASTER stores the country name and region used for reporting and analysis across data collections in cases where name formats may vary. COUNTRY_CODES_MASTER also includes related ISO (International Organization for Standardization) codes.
| Data element |
Definition |
ACTIVE_FLAG
Indexed - no
Format - char(1)
May be null - no |
Indicates whether the COUNTRY_CODES_MASTER record is active.
|
|
COUNTRY_CODES_MASTER_ID
Indexed - yes (primary key)
Format - number
May be null? no
|
Uniquely identifies a country record in the COUNTRY_CODES_MASTER table.
|
| COUNTRY_NAME
Indexed - no
Format - varchar2(200)
May be null? no
|
The name of the country.
|
| CREATED_BY
Indexed - no
Format - varchar2(20)
May be null? no
|
The PennKey of the user who created the COUNTRY_CODES_MASTER record. |
| CREATION_DATE
Indexed - no
Format - date
May be null? no
|
The date on which the COUNTRY_CODES_MASTER record was created.
|
| EFFECTIVE_DATE
Indexed - no
Format - date
May be null? yes |
The effective date of the COUNTRY_CODES_MASTER record. |
| FIPS10
Indexed - no
Format - char(2)
May be null? yes
|
The associated FIPS (Federal Information Processing Standard) identifier for the country. |
| ISO_ALPHA2
Indexed - no
Format - char(2)
May be null? yes |
The associated 2-character ISO (International Organization for Standardization) identifier for the country. |
| ISO_ALPHA3
Indexed - no
Format - char(3)
May be null? yes |
The associated 3-character ISO (International Organization for Standardization) identifier for the country. |
| ISO_NUMERIC
Indexed - no
Format - char(3)
May be null? yes |
The associated numeric ISO (International Organization for Standardization) identifier for the country. |
LAST_UPDATED_BY
Indexed - no
Format - varchar2 (20)
May be null? no
|
The PennKey of the user who last updated the COUNTRY_CODES_MASTER record.
|
LAST_UPDATE_DATE
Indexed - no
Format - date (7)
May be null? no |
The date on which the COUNTRY_CODES_MASTER record was last updated. |
LAST_UPDATE_TIMESTAMP
Indexed - no
Format - timestamp (6)
May be null? no
|
The timestamp on which the COUNTRY_CODES_MASTER record was last updated. |
REGION_GROUP
Indexed - no
Format - varchar2 (200)
May be null? yes |
The geographical region associated with the country. |
RELATED COUNTRY CODES TABLES
In the course of the mapping process, records for countries in various administrative system lookup tables or external references are related to the COUNTRY_CODES_MASTER table by the COUNTRY_CODES_MASTER_ID. The mapped relationships are then exported to the Warehouse and stored in "LOCAL" tables which associate the COUNTRY_CODES_MASTER_ID with the country code records particular to the various source systems. The LOCAL tables are comprised as follows.
| Mapping Table Name |
Related System and Table(s) |
LOCAL_ATLAS_CC
|
Source System: ATLAS
Related Warehouse Tables: DWGIFTS.PS_COUNTRY_TBL, DWGIFTS.WHSE_CONTINENT_LKUP
WHERE PS_COUNTRY_TBL.COUNTRY = WHSE_CONTINENT_LKUP.COUNTRY(+) |
| LOCAL_BENPROG_CC |
Source System: BEN Financials; program codes designated for country activities |
| LOCAL_BENTERR_CC |
Source System: BEN Financials; system-delivered country codes |
| LOCAL_FIPS10_CC |
Source: Federal Information Processing Standard |
| LOCAL_GAR_CC |
Source System: Global Activities Registry (GAR)
Related Warehouse Tables: DWREG.COUNTRY, DWREG.REGION
WHERE COUNTRY.REGION_ID = REGION.ID(+) |
| LOCAL_IR_CC |
Source: Institutional Research & Analysis |
| LOCAL_IRS990_CC |
Source: Internal Revenue Service, http://www.irs.gov/pub/irs-pdf/i990sf.pdf |
| LOCAL_ISO_CC |
Source: International Organization for Standardization, http://www.iso.org/iso/country_codes.htm |
| LOCAL_OASIS_CC |
Source System: OASIS
Related Warehouse Table: DWUGA.COUNTRY_LOV |
| LOCAL_OIP_CC |
Source System: Office of International Programs (OIP) iOffice
Related Warehouse Table: DWOIP.OIP_COUNTRY_CODE |
| LOCAL_OIPIIE_CC |
Source: Institute of International Education (IIE) Open Doors reporting, http://www.iie.org/Research-and-Publications/Open-Doors |
| LOCAL_PAYROLL_CC |
Source System: Payroll
Related Warehouse Table: DWADMIN.COUNTRY_CODE |
| LOCAL_PENNERA_CC |
Source System: PennERA
Related Warehouse Table: DWSP.PENNERA_PROPOSAL_INTL_LOCATION |
| LOCAL_SRS_CC |
Source System: SRS
Related Warehouse Table: DWADMIN.COUNTRY |
UN_M49_CODES TABLE
In September 2019, we added the United Nations M49 codes table, CCMAINT_ADMIN.UN_M49_CODES to the country codes data collection, as an alterntive to the columns in the LOCAL and COUNTRY_CODES_MASTER tables for regions, continents, etc. Source is https://unstats.un.org/unsd/methodology/m49/overview/
UN_M49_CODES contains one row per country, with a mapping for geographical region, sub-region, and intermediate region. The primary key is M49_CODE, which is a 3-digit code stored as varchar, and may contain leading zeroes; For example '004', not '4'.
The universe uses an outer join from COUNTRY_CODES_MASTER.ISO_NUMERIC to UN_M49_CODES.M49_CODE, but you can join M49_CODE to any table with an iso_numeric column.
NOTE: The table was loaded on Sept 13,2019; There is no scheduled refresh or automated process to reload it. Please notify da-staff if you notice any out-dated codes or want to request a table refresh..
Questions about this page? Email us at da-staff@isc.upenn.edu
|
