Non-Appropriated Funds Prime Vendor Data Mart (NAFPVDM)
Overview
The Non-Appropriated Funds / Prime Vendor (NAF/PV) Data Mart was designed and built for the Air Force Services Agency. It encompasses purchasing data from over 300 Air Force clubs, bowling centers, and other NAF facilities. The main data sources are purchase history data from the NAF Prime Vendor warehouses because the USAF NAF facilities do not have standardized computer systems. The data mart is used to monitor the Prime Vendors to ensure they are adhering to established pricing contracts and to identify potential rebates from food product manufacturers.
Technical Solution
The data mart is housed in SQL Server 2000, and reporting and analysis is conducted using Business Objects. Other technologies utilized include: ER/Win, Microsoft (MS) SQLServer stored procedures, MS Access and MS Visual Basic for dimension maintenance screens.
For this project, Diligent’s personnel were responsible for the following:
- Project Management
- Needs Assessment
- Data Mart design and development
- System architecture design
- Data Extract, Transformation, and Load process design and development
- Business Objects setup and training
- Business Objects report design and development
The Data Mart was designed around a star schema model, with a small number of detailed fact tables and several dimension tables. Some of the dimension tables included multi-level structures for summarized reporting.
The customer had an established method for accumulating data from the NAF/PV Warehouses. This data was loaded into a staging database where it was cleansed and transformed prior to loading into the Data Mart. There were numerous variations in the data from different vendors, so a system of translation tables was designed to homogenize the data prior to loading it into the target tables. New variations were automatically identified, and the users were provided with an application for selecting appropriate standardized translations for the new variations.
The end-user reporting environment included desktop design and management tools for creating reports and reporting-tool metadata structures and for administering the tool’s web-based reporting environment. The web-based reporting environment was implemented on an internal agency web server with LAN access to the Data Mart database server.
Diligent Impact
The project team conducted a preliminary Needs Assessment to create a project plan for implementing the Data Mart. The project team worked with the Program Manager to identify key technical and business personnel. Those individuals were interviewed to identify the practices used to gather and analyze the purchasing history data from the NAF/PV Warehouses. The project team discussed data requirements with the business personnel and data availability with the technical personnel to identify gaps. The resultant Needs Assessment document included a prioritized set of Action Items, a project plan for implementing the Action Items, and a high-level Data Mart design.