# Presentation: 355055

## Cash Registers & Satellites  

**Briefing to the 2006 NOAATech Conference**
**November 2, 2005**
** **
** ****Stan Cutler**
_**[Stanley.cutler@noaa.gov](mailto:Stanley.cutler@noaa.gov)**__** **_
- 301-457-5210 x 163
**Mitretek Systems/NESDIS/OSD **

**Improve communication between NOAA’s developers and the wider community of data management professionals**
**Introduce vocabulary**
**Identify NOAA applications that can be described using common vocabulary**

**Purpose**

**Agenda**

**Universal Data Management Challenges**
**Notional Data Warehouse Architecture**
**Data Modeling Approaches**
**Relational**
**Dimensional**

## I. Universal Data Management Challenges

## Data Mining Example: “Market Basket Analysis”

- _Decisions_:
- Move beer display closer to the diaper display
- On Thursdays, sell beer & diapers at full price
- _Rationale:_
- 1) When men bought diapers on Thursdays and Saturdays, they also tended to buy beer
- 2) Men typically did their weekly grocery shopping on Saturdays
- 3) On Thursdays, they only bought a 	few items

## Many Disciplines Mine Their Data

- Law Enforcement  -  Optimal Deployment
- Health Care – Coverage Risks
- E-Commerce – Pop-up/Link Selection
- Medicine – Gene/Disease Associations
- Etc.

_***Data Management Goal***_
***Develop systems in which the data and procedures are configured to answer questions that are important to the enterprise***

## Integrating Global (Environmental Observations) and Data Management 
Ensuring Sound, State-of-the-Art (Research)
Developing, Valuing, and Sustaining a World-Class Workforce

**Ensuring Sound, State-of-the-Art ****(Research)**
**Developing, Valuing, and Sustaining a World-Class Workforce**

**NOAA’s Future**** **

***We are not unique*****. Any enterprise that collects large amounts of data has the same kind of challenges and goals  **

## Ask the same kinds of questions as those challenged with similar problems 

Understand the constructs and vocabulary
Architectures  
Data Modeling

**Understand the constructs and vocabulary**
**Architectures  **
**Data Modeling**

**We can find valuable expertise outside the NOAA community**

## II. Notional Data Warehouse Architecture

## “Hub and Spoke Architecture”

- Application Specific “Data Marts” use ”OLAP” Technologies
- ()

**Data**
**Staging**
**Area**

**Data**
**Ware-house**

**External**
**Data**

**Internal**
**Data**

- Transform
- &
- “**Cleanse**”

- Application Neutral

- “ETL” = **E**xtract, **T**ransform and **L**oad

- “**OLAP**” = Online Analytical Processing

## Retail ApplicationHub and Spoke Architecture

**Retail Application****Hub and Spoke Architecture**

- OLAP Data Marts
- (Application Specific)

**External**
**Customer**
**Lists**

**Sales**
**Data**

- Transform
- &
- Cleanse

- Application Neutral

## Notional NOAA Hub and Spoke Architecture

**Notional NOAA Hub and Spoke Architecture**

**NOAA Applications**
**(Data Marts using OLAP)**

**Data**
**Staging**
**Area**
**(Rich**
**Inventory?)**

**Data**
**Ware-house**

**Other **
**Satellite**
**Archives**

**CLASS**

- Transform
- &
- Cleanse

- Application Neutral

**Ecosystems**
**Management**

**Commerce &**
**Transportation**

**External**
**Customers**

- ESPC

**Data Centers**

## III. Data Modeling Approaches

## “Relational”  Vocabulary 

**“****Relational” technologies**
**Relational Data Base Management Systems (RDBMS)**
**COTS Products (INFORMIX, DB2, ORACLE, MS/SS, etc.)**
**Proprietary data management/manipulation *****software***** **
**RDBMS Extensions (Most COTS products built on an RDBMS) **
**GUIs, CASE Tools, COOP,  Application Generators, Security, etc.**
**“****Relational” Data Models - Evolutionary approach to data base design**
_**Conceptual**_** Entity Relationship Diagrams (ERD) used to identify data requirements, relationships, rules **
**Diagrams**
**Data Dictionaries**
_**Logical**_** ERDs used to normalize (eliminate redundancies)**
_**Physical**_** models are the Table Schema entered into the RDBMS  **
**Online Transaction Processing (OLTP) **
** ****e.g., CLASS**

## Entity Relationship Diagram (ERD)

- Entity

- Relationship

- Attributes

- Cardinality
- (1, Many, or 0)

**The foundation of all OLTP systems, such as CLASS**

- Attributes, entities, and relationships are described in the data dictionary

- Entity
- Class

## Object Models “inherit” ERD constructs

- Object
- Class

**key**
**...**
**...**
**Behavior:**
**>>>>**
**>>>>**

## Pros & Cons of systems based on Relational models

**Strengths  **
**Referential integrity**
**Data locking**
**Fast Look-up and Retrieval**
**GUIs**
**Weaknesses **
**Entity proliferation**
**Users don’t understand them **
**Complex code must be written to accumulate multiple instances (****Hard to use for Data Mining)**

## Dimensional Data Models 

**Fact**
**An instance of numeric data**
**Dimension**
**Foreign key **
**Fact Table**
**Key is a concatenation of foreign keys (dimensions)**
**An instance can have dozens of foreign keys**
**Millions of instances (rows) often required**
**Programmers revenge on Data Base Administrators**
**Break many relational “rules”**
**Re-invented often**

## A “Dimensional” Data Model for  Retailing  

**Who (buys, sells) **
**Customer (age, gender, marital status, occupation, etc.)**
**Sales person ( “ , “ , training, etc.)**
***Cash Register*****  **
**What (products) **
**Brand, color, size, type, etc**
**When **
**Time of day, day of week, season **
**Where  **
**Store (location, size, type), Shelf **
**Why**
**Promotions, advertising, discounts, economic trends**
**How much (was spent)**
**Per product, per total sale**

## Classical Star Schema: Point of Sale 

**Classical Star Schema: Point of Sale **

**Time_key**
**Customer_key**
**Store_key**
**Clerk_key**
**Promo_key**
**Product_key**
**Register_key**
**Dollars Sold**
**Units Sold**
**Dollars Cost**

**FACT**

## Snowflake Schema: Point of Sale 

**Snowflake Schema: Point of Sale **

**Time_key**
**Customer_key**
**Store_key**
**Clerk_key**
**Promo_key**
**Product_key**
**Register_key**
**Dollars Sold**
- Units Sold
- Dollars Cost

- Product_Type_PK
- Product_Type_Desc

**Product Dimension**

**FACT**

- Sub-Type_PK
- Sub-Type-Desc

- Sub-Type_PK
- Sub-Type-Desc

- Sub-Type_PK
- Sub-Type-Desc

- Model-Num_PK
- Model-Desc

- Brand-ID_PK
- Maker-Desc

- Sub-Type_PK
- Sub-Type-Desc

- Model-Num_PK
- Model-Desc

- Brand-ID_PK
- Maker-Desc

## Metadata in Dimensional Modeling

**NOAA usage:**
**If it’s not a fact**
**If it’s not a key**
**It’s metadata**
**Conventional Dimensional usage: **
**      ****If it’s not a fact**
**      ****If it’s not a key**
**      ****It’s documentation **
**BUT**
**     ****If it’s a key**
**     ****It’s ****metadata ****(because it describes the fact)**

## Dimensional Models for NOAA 

**Which**
**Satellite**
**Instrument **
**When **
**Orbit, UTC, Season, decade, epoch, etc**
**Where  **
**Geospatial coordinates**
**Who**
**User affiliation**
**Developer affiliation**
**FACT: How much? **
**Temperature, moisture, radiance, color, etc.**

## A NOAA Star Schema?

**A NOAA Star Schema?**

**Time_key (fk)**
**Location-key (fk)**
**Altitude key (fk)**
**Product_key** **(fk)**
**Satellite_key** **(fk)**
**Instrument_key ** **(fk)****   **
**Temperature**
** **

**FACT TABLE**

## Pros & Cons of systems based on dimensional models

**Strengths**
**Very few “entity types” needed **
**Decision Support Systems (DSS)**
**End-Users construct complex queries by selecting dimensions from a GUI **
**Statistical analysis of very large data bases**
**Artificial Intelligence (AI) **
**Automated scheduling of continuous executions **
**System identifies (“discovers”) new relationships **
**Discoveries shape successive execution **
**Weaknesses **
**Development Cost **
**Storage**
**Operational Cost - Requires much “care and feeding”**_** **_

## False Dichotomy: Relational “vs.” Dimensional 

**Relational and dimensional systems are not mutually exclusive **
**Data warehouses usually extract fact tables from relational data bases**
**Data warehouse capabilities are extensions in RDBMSs**
**Depends on the business **
**Feasibility: Is the application data good enough for ETL?**
**ROI: Does the business benefit outweigh the cost?**

## SUMMARY:   

**NOAA’s data mining challenge is similar to that of other enterprises**
**A world-wide community of IT professionals uses a particular vocabulary to address the challenge**
**Relational technologies & models are the essential first step**
**Dimensional technologies & models come next**

**Notes:**
October 29, 2001 issue of Forbes Magazine 

## Questions

- Stan Cutler
- Mitretek System/NESDIS/OSD
- Stanley.Cutler@nooa.gov
- 301-457-5210 ex 163