OceanakSoup-2-Nuts

Alaska Department of Fish & GameCommercial Fisheries

October, 2011

Soup-2-NutsAn Overview of the Commercial Fisheries Data Warehouse and Business Intelligence Structure

Project ObjectivesComponentsShow MeProject Status & In

Process

Project LinksProject Site: http

://dcf.dfg.alaska.local/it/sp/oceanak/Project Phases:

http://dcf.dfg.alaska.local/it/sp/oceanak/SitePages/Project%20Phases.aspx

Project Objectives1. Provide a Single Reporting Tool for All

Commercial FisheriesEliminate multiple technologies and make

better use of our programmers and end users time

Provide end users with a common interface where we can integrate data entry applications

Provide users with both powerful ad-hoc capabilities and ‘canned’ reports

Project Objectives1. Provide a Single Reporting Tool for All

Commercial Fisheries (continued)Provide users with scheduled reports -

supports low bandwidth areasProvide state-wide views and access to data

across regionsProvide ongoing workshops in this common

tool & use staff to teach staff as well as share designs.

Project Objectives2. Provide a Data Warehouse as a Reporting

SourceIntegrate dataTransform data when requiredDivorce Reporting from Data Entry‘Reporting Friendly’ source of data

3. Data Rescue SupportPer Subject Area, review data inventory and

provide a home for endangered data

ComponentsSource DatabaseOracle Data Integrator (ODI)Oracle Data WarehouseOracle Business Intelligence

AdministratorOracle Business Intelligence (OBI or BI)

=

Source Database

Source DBFisheries

Management Data Entry

Oracle Business

Intelligence

Data WarehouseOracle Data Integrator

Original storage point of data entryRelational Database - Supports fast and valid

data entry

Source Database

IFDBAlex

Venus FTDB

Neptune

Zephyr

Triton

Many ‘sources’ in CFMany types of

databasesMany input

applicationsNone ‘talk’ to each

otherDuplicated data

Source Systems

IFDBAlex

Venus FTDB

Neptune

Zephyr

Triton

Crystal Reports

SQL

MS Access

Excel Text

Hard Coded Reports

What you don’t see: The plethora of

individual reporting technologies

The inability of staff to cross application boundaries

Man-hours lost manually manipulating data

Man-hours lost duplicating effort

Data IntegratorHas one job – Map from point A to point B. Can change the shape of the data to optimize

for reporting Can apply logic and combine source

databasesIs optional!

Source DBFisheries

Management Data Entry

Oracle Business

Intelligence

Data WarehouseOracle Data Integrator

Data WarehouseCan consolidate from many sourcesCan be rebuilt from scratchScheduled updates according to needsIs optional!

Source DBFisheries

Management Data Entry

Oracle Business

Intelligence

Data WarehouseOracle Data Integrator

OBI Administrator A tool used to prepare the data for use from

various sources for use in the OBI InterfaceUses data direct from source databases or

data warehouse (ODI and Data Warehouse optional)

Can add value: Calculated fields, HierarchiesOrganizes data by “Subject Areas”

Source DBFisheries

Management Data Entry

Oracle Business

Intelligence

Data WarehouseOracle Data Integrator

OBI InterfaceThe web based end-user tool

DashboardsCustom and Ad-hoc reportsPublic web reports & charts

Source DBFisheries

Management Data Entry

Oracle Business

Intelligence

Data WarehouseOracle Data Integrator

All TogetherSource DatabaseData IntegratorData WarehouseBI Administrator BI User Interface

Oceanak

eLandings

Data Warehouse

eLandings Interface

ETL

Oracle BI

Input ->

<- Output

What is ETL?ETL means “Extract, Transform and Load”Executed by Oracle Data Integrator (ODI)

Extract – Grab the data from the source(s)Transform – Any restructuring to make

reporting easier (i.e. Merge data from multiple tables)

Load – Store the data in the data warehouseOnly used if data is required to be integrated

with other sources or transformed. (General rule).

Why Transform the Data?

eLandings Source Database

ETL Using Oracle Data Integrator (ODI) – Loading data warehouse

ODI Task – LOAD_ITEM_FACTS

Target Data Warehouse Table

Show ME – OBI Admin

BI Admin – Physical LayerDefines the physical data

model of your data source

BI Admin – Business ModelAllows you to set up

Fact table with measures

Dimension tablesHierarchies

BI Admin – Presentation LayerThe structure that is

presented to the userAccessed through

logical sqlNo key columns

Show Me – OBI Interface

Show Me – OBI to the Public

Will All Data Use the Warehouse?

Oceanak

DatabaseZander

Oracle BI

Input ->

<- Output

• Data that does not require transformation may not use the data warehouse

• Much simpler &quicker to build

Can join later!

Current StatusRegion I waiting Oracle release (bug)Region I Public reporting in productioneLandings is in Production

Staff trained Dutch, Homer, Kodiak – SE this winter

BlueSheets is in ProductionMariner V2012 is in design

Initial staff workshop September – more to follow

Fish Tickets Detail – Iterative Testing of ETLMTAL in training

FutureCOAR/ITO

Waiting eLandings planningASL Repository Move/Expand

Work in conjunction with ASL projectExpansion of Mariner V2013 to Region III

IT staff trainingFish Ticket Summaries – Historical

Incorporate views of regional summaries for identifying data cleanup issues