CLOSED: AKFIN Data Warehouse
AKFIN is a regional program that consolidates and supports the collection, processing, analysis, and reporting of fisheries statistics for North Pacific and Alaskan fisheries. AKFIN integrates information into a single data management system using consistent methods and standardized formats. The Network then reports this information on their website, in various publications, and to researchers using different methodologies. The resulting data helps fishery managers, scientists, and associated agencies supervise fisheries resources more effectively and efficiently. More specifically, AKFIN reports catch data, harvest and value from commercial fisheries in Alaska using the best available data from the data source agencies. Once these data are incorporated into its system, AKFIN reports information from several critical perspectives to decide impacts of changes in fishery management. These include species, area, gear, vessel, processor, community, and fishery participants by season.
An AKFIN Data Warehouse Plan document was completed in the fall of 2001. Much of this document is relevant today and provides a vision for the AKFIN project. An independent study, which reviewed the AKFIN data warehouse implementation proposal, was completed in March of 2002. A follow-up to this report was completed in February 2005.
In the past year AKFIN completed an initial version of a data warehouse. The end product has useful, but limited functionality. All ETL is performed using in-house Oracle packages and procedures. Reports are in the form of data sets created by AKFIN programmers using SQL and Oracle procedures. No metadata system currently exists.
The total exported size of the database is 37 GB; fact and dimension tables account for 3 GB, with source and system data accounting for the remainder. Current warehouse contains one fact table with six years of data, and thirty five dimension and interface tables. Source data originates from multiple agencies with various versions of Oracle databases. Data quality varies between agencies and within years, with older data being less reliable.
A custom web application has been developed, which provides end-users the ability to perform ad hoc queries, view metadata, and retrieve documentation. AKFIN staff is currently in the process of implementing this product.
In the past few years AKFIN has lost key personnel during critical stages of development. This has proven to be especially difficult with a small staff. The purpose of this project is to mitigate future risk by replacing our in-house procedures with a commonly supported Oracle solution.