Extract, Transform, Load (ETL) Data Initiative Project

The Client

Federal Department

The Department has seven databases that contain the unit/position related information. They needed support in the maintenance and enhancement of the system components. The data for this project came from a PeopleSoft system.

The Technology

Federal Department

  • Oracle
  • PeopleCode
  • Java

The Challenge

The challenge was to create an authoritative data source for users within the Department. Our client required a team to construct and populate the data source, through an Extract, Transformation, and Load (ETL) process, from a PeopleSoft Human Capital Management implementation.

The Solution

The Squires Group, Inc. (TSGi) has been supporting this initiative for the past six years. TSGi has designed, developed, and implemented, the authoritative data source initiative for the client. Currently our team is sustaining and operating the system to comply with various governance documents.

TSGi performed 100% of the technical work on this contract. TSGi completed the following main tasks:

  • Assisted the Government team in defining the technical operating environment for the Data Initiative
  • Constructed the authoritative data source using Oracle
  • Developed the Extract, Transformation, Load routines to extract data from various legacy systems and load into the authoritative data source
  • Transitioned from a total replacement update methodology to a line item
    update with the capability to maintain six years of historical data, as well as current and six years of future data

The TSGi team was very involved in the maintenance and enhancement of these six major components.

Personnel Authorization Documentation

TSGi provided the capability to consume, document, and update client’s organization structure data as authorized by Congress in accordance with DoD’s Net Centric Data Strategy. The system was capable of including current year, previous six years, and future six years of organization and authorization structure.

Topic Development
TSGi participated in the client’s organizational server developer’s forum to propose and develop topics in support of messaging services (commonly known as Web Service (WS) Notification or Joint User Messaging (JUM)).

Message Service Implementation
TSGi implemented topics through web enabled services in the unclassified and classified domains in accordance with DoD Net Centric Data Strategy, and Net Centric Enterprise Services (NCES) core services. The team also implemented Web Service (WS) Security through a combination of PeopleSoft built-in capabilities and Java.

Topic Consumption
TSGi built custom functionality using PeopleSoft messaging tools and Java to process inbound XML messages containing the Department data from the component legacy servers.

Automated Data Compilation and Security Marking Demonstration Software
TSGi developed prototype code demonstrating the capability to combine individual tagged data elements and machine marks that combined unclassified elements at a higher classified level (as appropriate). TSGi used notional classification and compilation rule data as approved by the Government. This deliverable provided a proof of concept for automating data classification by compilation and declassification by decomposition. The software was designed to run on Microsoft Windows.

Role and Attribute Based Data Access Control

TSGi developed prototype code demonstrating the capability to limit access of appropriately tagged data by the individual’s role and attributes associated with that role, an emerging technology process commonly known as Attribute Based Access Control (ABAC).

The Success

TSGi provided exceptional technical staff members, with Secret and Top Secret clearances, who continue to advance the client program throughout the Department. We were recognized as the source of technical expertise on this program throughout the Department.

The TSGi developed data source became the authoritative source of organization and authorization information.


Download this case study

Leave a Reply

Your email address will not be published. Required fields are marked *