Penn Computing

University of Pennsylvania
Penn Computing << go backback
Cross-Collection Home    Data Warehouse Home

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..


Cross-Collection Home    Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania