Penn Computing
Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

IT Privacy Initiative:
Joining Records in the Data Warehouse

Project Overview

In recent years, Penn and many other institutions have recognized that use of SSNs for identification purposes creates substantial risks. SSNs are sensitive data that can be abused by identity thieves to commit fraud. This abuse can cause privacy harm to Penn constituents and create compliance and reputational risks to Penn itself.

As a part of an overall strategy to mitigate use of the SSN in systems across the University, changes have been made to tables and views in the data warehouse to significantly reduce the use of the SSN in the employee and student data collections. Completed and ongoing efforts are outlined below:


Data Warehouse Structure (effective June 30, 2007)

  • Penn ID column populated for all person records in the student and employee collections
  • Where the Penn ID is not generated by Penn Community, a “temporary” Penn ID, whose value is less than 10000000, is assigned
  • Final four-digit SSN added to those tables and views already containing bio/demo data: PERSON in the Student collection and EMPLOYEE in Salary Management
  • DW_PENNID_CHANGES, an audit table of changes is maintained
  • New database role hides temporary Penn IDs from applications where the assigned Penn IDs would cause operational problems
  • Click here for additional details

Business Objects Universes (effective August 1, 2007)

  • Universes that contained tables previously joined by SSN now join by Penn ID
  • Changes are transparent to end users

Restrictions on Access

  • New database roles:
    • Access to SSN: Full SSN hidden from users who don't have the role (granted only in accordance with OACP policy).
    • Access to SSN4: Final four digits of SSN (granted only in accordance with OACP policy).
  • Effective August 1, 2007, new data warehouse users will not be granted the role(s) without approved access
  • Effective September 1, 2007, all current data warehouse users must apply for access to these roles
  • Click here for additional details

Convert ID Utility (available since March, 2007)

  • An online application allowing users to look up Penn Ids for individuals where only SSN is known.
  • Batch submission and single lookup available.
  • Access available through Office of Audit, Compliance and Privacy (215) 573-4492.

Data Integrity

  • Ongoing identification and correction of records with a temporary Penn ID, including:

    • Records without Penn Community-generated Penn ID
    • Multiple records per Penn Community-generated Penn ID
    • Records from different sources with conflicting data

Additional References:

Current Access Policy

Penn ID in the Warehouse

Past Meetings



[ Data Administration ]

[ Office of Audit, Compliance and Privacy ]


Information Systems and Computing
University of Pennsylvania
Comments & Questions

University of Pennsylvania Penn Computing University of Pennsylvania Information Systems & Computing (ISC)
Information Systems and Computing, University of Pennsylvania