This slide deck provides an overview of the data and resources available in the Sherlock Data Warehouse. Sherlock was developed and is currently maintained by the Aviation Systems Division at NASA Ames Research Center. Sherlock contains a valuable collection of flight, air traffic management, and weather data. But Sherlock is not just a data archive. Sherlock also includes tools and resources to access, download, and visualize data, as well as resources to process the data. This overview summarizes Sherlock data sources, demonstrates data analytics and visualization with MicroStrategy, illustrates disparate data integration using the ATM Knowledge graph, and presents a machine learning use case using the Big Data system

Arneson, Heather M.

Evans, Antony D.

Hegde, Pallavi

Keller, Richard M.

La Scola, Michael E.

Schade, John E.

NASA Technical Reports Server

SHERL   CK
DATA WAREHOUSE
April 8, 2019
https://ntrs.nasa.gov/search.jsp?R=20190025090 2019-08-31T13:32:27+00:00Z
Take-aways
SHERL   CK
• Sherlock contains a valuable collection of flight, air traffic 
management, and weather data
• Sherlock is more than just a data archive
• Pique interest for follow-on workshops
– Data visualization & analytics with MicroStrategy
– Processing using the Big Data system from Jupyter Notebook
2
Outline
SHERL   CK
• What is Sherlock?
• How are data stored? (Why should I care?)
• Sherlock information and data access
• Summary of archived data
• Overviews and demos of resources
3
• Platform for collection, processing, and archiving
air traffic management data
• Sherlock resources include:
– Sherlock home page New!
– File download web UI Reorganized!
– Data visualization & analytics New!
– Jupyter Notebook on Big Data system New!
– ATM Knowledge Graph (experimental)
– Hue Browser
– THREDDS Data Server
– GeoServer
What is Sherlock?
SHERL   CK
Demos
4
Backup slides
How are data stored?
(Why should I care?)
SHERL   CK 5
Where data stored?
SHERL   CK
Operational Data Store (ODS)
Big Data System
File System
• Linux file system, /home/data
• Traditional database
• Distributed data storage and processing
6
How are data stored?
SHERL   CK 7
Format
Functionality
Select rows/columns of interest
View data before download
Download only the data you want
Good for small data sets
Good for large data sets
Available from
File system /home/data
File download in web UI
Tables and charts in web UI
Data visualization & analytics
Jupyter Notebook
Hue browser
File System
Flat files
ODS
Tables
Big Data
Apache Parquet
No
No
No
Yes
No
Yes
Yes
No
No
No
No
Yes
Yes
Yes
Yes
No
Yes
Yes
Yes
No
Yes
No
No
Yes
Yes
No
No
No
No
No
No
Yes
Yes
Sherlock information and data 
access
SHERL   CK 8
Sherlock resources
SHERL   CK 9
https://atmweb.arc.nasa.gov/
SHERL   CK 10
Demo: Sherlock home page
SHERL   CK 11
SHERL   CK 12
SHERL   CK 13
SHERL   CK 14
SHERL   CK 15
SHERL   CK 16
SHERL   CK 17
SHERL   CK 18
Summary of archived data
SHERL   CK 19
FAA SWIM data in Sherlock
SHERL   CK 20
System Wide Information Management (SWIM) Program
• National Airspace System (NAS)-wide information system that 
supports Next Generation Air Transportation System (NextGen) 
goals
• Increased common situational awareness various stakeholders
• Single point of access for aviation data
– Producers of data publish it once 
– Users access the information they need through a single connection
FAA SWIM data in Sherlock
SHERL   CK 21
Flight data
STDDS/ASDE-X Surface data
TAIS TRACON data, including VFR flights
SFDPS En route flight data
TFMData NAS-wide flight data, flow constraints
TBFM Operational metering data
Airport data
APDS Airport Data Service, Runway Visual Range info
NOTAM Notices to Airmen
Weather data
ITWS Terminal convective weather
Data in Sherlock
Weather data
CIWS Convective weather forecasts
METAR Airport current surface weather conditions
RR NOAA Rapid Refresh forecasts
CCFP Simplified convective weather polygons
METAR Airport weather reports
TAF Terminal Aerodrome Forecast
WITI Weather Impacted Traffic Index
PIREP Pilot reports
Obsolete weather data (stored but no longer updated)
RUC NOAA Rapid Update Cycle forecasts
SHERL   CK 22
Flight data
CTAS Text-based format, in Center/TRACON pairs
Obsolete flight data (stored but no longer updated)
ASDI Flight data
Traffic management
ATCSCC Strategic advisories
Facility reports
OPSNET Statistics
Data in Sherlock
SHERL   CK 23
Archived derived data
produced by ATAC
SHERL   CK 24
Processed data from ATAC in Sherlock
Flight data
IFF Flight plan and track
EV Flight event
RD Flight summary
SHERL   CK 25
Legacy STARS (1/2014 to present)
SWIM STARS (TAIS) (11/2017 to present)
Legacy HOST/ ERAM – from 1/2014
SWIM ERAM (SFDPS) – from 5/2017
ARTS –available from 1/1/2014 ~ 11/2015 SWIM ASDE-X – from 1/2016
Analysis-ready track, flight plan, and metadata for 94 individual facilities
TRACON / 
Terminal
TRACON / 
Terminal
ARTCC / 
Enroute
ATCT / 
Surface
SHERL   CK 26
USA Merged data –available from 1/1/2014, *note – data prior to 1/16/2016 does not contain surface data
SHERL   CK
End-to-end trajectories, flight plans, and meta data
27
Performance reports/data in Sherlock
Facility reports Scope (Facility Type) Description
Go Arounds TRACON/Terminal Counts, runways, altitude, return time
Turns to Final TRACON/Terminal Overshoots, glideslope speed/altitude deviations, turn on angle
Fix Passing TRACON/Terminal Fix/Waypoint throughput
Runway Usage ATCT/Surface Runway throughput, arrival/departure rates
Taxi Time ATCT/Surface Taxi out, taxi in time
Instantaneous Counts ARTCC/Enroute Number of aircraft in sectors in 15 minute bins, summary statistics
Sector Stats ARTCC/Enroute Sector count, flight Time/distance, and transitions between sectors
Sector Activity ARTCC/Enroute Sector entries/exits/counts in 15 minute bins
Field10 Reroute ARTCC/Enroute Diversions and reroutes
NAS-wide reports
Jet Airway Flow CONUS Jet Airway counts, flight time, and distance
Best Flight Plan CONUS Synthesized flight plan route closest to what was actually flown
CCFP Sector/ARTCC Coverage CONUS Percentage of Sector/ARTCC covered by CCFP convective weather (per 2hr)
CWAM Sector/ARTCC Coverage CONUS Percentage of Sector/ARTCC covered by CWAM convective weather (per 15min)
CCFP Jet Airway Coverage CONUS Percentage of Jet Airway covered by CCFP convective weather (per 15min)
SHERL   CK 28
Overviews and demos of 
resources
SHERL   CK 29
Sherlock resources
SHERL   CK 30
https://atmweb.arc.nasa.gov/
SHERL   CK
https://atmweb.arc.nasa.gov/
31
SHERL   CK 32
Demo: File Download Web UI
SHERL   CK 33
SHERL   CK 34
SHERL   CK 35
SHERL   CK 36
SHERL   CK 37
SHERL   CK 38
SHERL   CK 39
SHERL   CK 40
SHERL   CK 41
SHERL   CK 42
SHERL   CK 43
SHERL   CK 44
SHERL   CK 45
SHERL   CK 46
SHERL   CK 47
Data visualization & analytics
• What is MicroStrategy?:
– Enterprise business intelligence (BI) application software 
– Allows users to create custom data tables and visualizations
• Sherlock data visualization and analytics with 
MicroStrategy:
– Visualize Sherlock data without downloading
– Visualize user-generated data on Sherlock’s Big Data system
– Create custom visualizations for unique needs
– Perform some basic data analytics
SHERL   CK 48
Data visualization
SHERL   CK 49
SHERL   CK 50
Demo: Data Visualization & Analytics
SHERL   CK 51
SHERL   CK 52
SHERL   CK 53
SHERL   CK 54
SHERL   CK 55
SHERL   CK 56
SHERL   CK 57
SHERL   CK 58
SHERL   CK 59
SHERL   CK 60
SHERL   CK 61
SHERL   CK 62
SHERL   CK 63
SHERL   CK 64
SHERL   CK 65
SHERL   CK 66
SHERL   CK 67
SHERL   CK 68
Graph-structured queryable repository of 
disparate ATM data
(Experimental, with limited data available)
What is ATM Knowledge Graph?
SHERL   CK
• Stored in a Graph Database
o Not in traditional Operational Data Store (ODS)
• Accessible via:
– Web Interface
• Query editor/executor
• Visualization tool
– Programmatic API
– MicroStrategy
Subgraph 
describing a flight
• Highly-interconnected network-
structured data store, where:
o Nodes
• Represent ATM entities 
(flights, airports, facilities, aircraft, routes...)
• Store properties/data of entities
o Links represent interrelationships
69
What is the value of ATM Knowledge Graph?
• Sherlock is not a unified database; it is a data repository
– Cannot generally query across data tables or data sources
• Knowledge Graph merges/integrates/unifies data from 
multiple sources into one large graph structure to enable 
cross-source querying
• Result: You can: 
ü Query Sherlock as a unified database
ü Visualize and navigate through the data graph
ü Download integrated data
70
• ASDI: Flight track data
• TFM Advisories: GDPs, reroutes, Ground Stops,…
• METAR/TAF: Airport weather observation & forecast data
• ERAM adaptations: NAS infrastructure data
(facilities, routes, SIDs/STARs, fixes, airways, sectors,…)
• ASPM: Airport performance (traffic counts, delay stats,…)
• FAA Aircraft Registry: Aircraft Characteristics
(registration, certification, ownership, aircraft & engine models)
• CAST/ICAO Aircraft Taxonomy: Aircraft Models and Manufacturers
• Airlines, Airport Terminals/Gates
Sherlock 
sources
What data are stored in ATM Knowledge Graph?
Non-
Sherlock 
sources
Experimental: Currently very limited data in Knowledge Graph! (only July 2014 for ZNY)
71
SHERL   CK 72
Demo: ATM Knowledge Graph
Flight UAL535 
on 2014-07-15
Aircraft N589UA
Flight Plan
Departure Airport
Trajectory
Track 
PointTrack Points
74
Flight trajecotry
75
Big Data system
SHERL   CK 76
Big Data system
• What is a Big Data system?
– Built on commodity hardware
– Massive storage for any kind of data
– Enormous processing power
– Ability to handle virtually limitless concurrent tasks
• Sherlock Big Data system
– 32-node cluster in Building N233
– SuperMicro Engineered System
• Total of 576 CPU Cores
• Total of 800 TB Storage
– Cloudera distribution of Hadoop
SHERL   CK 77
Data sources currently on Big Data system
Facility flight data
IFF Flight plan and track
EV Flight event
RD Flight summary
USA merged flight data
IFF Flight plan and track
EV Flight event
RD Flight summary
SHERL   CK 78
Please contact the Sherlock team if you would like to 
access a particular data source from our Big Data system.
How to access Big Data
• Query Big Data 
• Hue browser
• Process data on Big Data system
• Requires a cluster-computing framework
• Many to choose from!
• Sherlock team recommends: Apache Spark
SHERL   CK 79
• Unified analytics engine for large-scale data processing
• Performs fault-tolerant distributed computing and parallel 
processing services on a cluster
• Supports 4 languages:
– Scala (native)
– Java (fast)
– Python (easy)
– R
80
What is Apache SparkTM?
SHERL   CK
Traditional
1. Write code on local machine
2. Zip code and scp to sherlock.arc.nasa.gov
3. Run Spark submit job to execute code on Big Data system
Drawbacks:
• Develop with a small local copy of data
• Difficult to debug when deployed on the Big Data system
81
Big Data development workflow
SHERL   CK
Jupyter Notebook
1. Develop code in web browser on sherlock.arc.nasa.gov
2. Break code into segments
3. Run and see output in-line
Advantages:
• Code is running on Big Data system while developing
• Debugging is easier in Jupyter than traditional work flow
82
Big Data development workflow
SHERL   CK
SHERL   CK 83
SHERL   CK 84
Code cell 
input
On-the-fly 
inline plots
Jupyter Notebook
• Open source web 
application for interactive 
computing
• Execute code interactively 
in the browser
• View results and plots 
inline in the Notebook
Machine learning use case: flight track clustering
1. Join and filter track data from Big Data system
2. Process data to generate features
3. Detect and remove outliers
4. Cluster the data using K-Means
5. Evaluate resulting clustering and create plots
SHERL   CK 85
Outlier detection step
Number of 
Flights Platform
Processing time
Hours Minutes
25,000
Personal machine 1 26
Big Data 0 13
50,000
Personal machine crashed
Big Data 0 32
Benefits of using Big Data system
• No need to copy hundreds of GB of data to user machine
• Fast processing (distributed processing with powerful CPUs)
SHERL   CK 86
Big Data System + Spark + Jupyter 
• Big Data Cluster provides enormous processing power
• Spark is a large scale distributed data processing engine and it 
processes large amount of data in memory
• Jupyter is a great tool to code, test, prototype, and share PySpark
programs
87
SHERL   CK 88
Demo: Jupyter Notebook for Big Data
SHERL   CK 89
SHERL   CK 90
SHERL   CK 91
Coming soon …
SHERL   CK 92
Workshops
• MicroStrategy
Create custom visualizations and analytics
• Big Data system
Implement a machine learning use case using
Spark and SparkML in Jupyter Notebook
SHERL   CK 93
Acknowledgements
• Management
Heather Arneson
Antony Evans
Paul Cobb
Karen Gundy-Burlet
• Developers
Pallavi Hegde
Michael La Scola 
• Database & Big Data admin
Dat Duong
Eric Wang
• Data collection, archiving, monitoring
Joe Cisek
Pat O’Neal
• Windows, Linux admin
Matt Ma
• Graph database
Rich Keller
• ATAC data
John Schade
Kennis Chan
Cindy Wong
(the other) Eric Wang
SHERL   CK 94
Home page:
https://atmweb.arc.nasa.gov/
These slides:
Home page à ABOUT à Overview
User support:
sherlock-support@lists.nasa.gov
SHERL   CK
DATA WAREHOUSE
SHERL   CK 96
BACKUP SLIDES
SHERL   CK 97
What is Sherlock?
SHERL   CK 98
Architecture
SHERL   CK 99
How are data stored?
SHERL   CK
File System
• No insight into the data
• Download full file
• Available from:
– File system /home/data
– File Download Pages in Web UI
Flat files
100
How are data stored?
SHERL   CK
Operational Data Store (ODS)
• Select rows and columns of interest
• View data before download
• Download only the data you want
• Good for small data sets
• Available from:
– Tables and Charts in Web UI
– Data Visualization & Analytics
Tables
101
How are data stored?
SHERL   CK
Big Data System
• Select rows and columns of interest
• View data before download
• Download only the data you want
• Good for large data sets
• Available from:
– Hue Browser
– Jupyter Notebook
– Data Visualization & Analytics
Apache
Parquet format
102
Archived data
produced by ATAC
SHERL   CK 103
Flight data and report products available in Sherlock
1. Analysis-ready track, flight plan, and metadata for 94 individual facilities
(available on a next day basis)
• Data types (IFF, EV, RD)
2. End-to-end trajectories, flight plans, and metadata available
(available within 10 days)
3. Performance Reports
(available daily for individual facilities)
4. Aggregated Trend Databases (STREND)
(updated daily)
5. Traffic and weather coverage metrics (available in csv format)
• Sector transition metrics
• CCFP/CWAM Sector/ARTCC coverage
• Sector/ARTCC counts by weather coverage
• CCFP jet route coverage
6. Data completeness tool
SHERL   CK 104
MetaData examples: events in the EV file
Event Type Description
EV_GOA Go-Around Event Occurs when go-around is detected
EV_HOFF Handoff Event Occurs when handoff is initiated, accepted and cancelled
EV_INIT Initialization Event Occurs at the beginning of a flight 
EV_LND Landing Event Occurs when a flight crosses the threshold of its landing runway
EV_LOOP Looping Event Occurs when a flight track crosses over back on itself
EV_MOF Mode of Flight Event Occurs when mode of flight changes (e.g., level to descend)
EV_PASS Passing Event Occurs when a flight passes by a defined navigational element
EV_STOH Stop Handoff Event Occurs when handoff stops
EV_STOL Stop Loop Event Occurs when loop stops
EV_STOP Stop Event Occurs at the end of a flight
EV_TOC Top of Climb Event Occurs when a flight reaches initial cruise altitude
EV_TOD Top of Descent Event Occurs when a flight begins descent from cruise
EV_TOF Take Off Event Occurs when a flight crosses the threshold of its takeoff runway
EV_USER User Defined Event Occurs based on the metrics defined in the metrics file
EV_XING Crossing Event Occurs when a flight crosses an airspace volume boundary
SHERL   CK 105
Event type example
A vertical mode of flight event (EV_MOF) occurs when there is a change in the 
vertical profile of a flight. The processing detects any transition between any 
two of the three possible vertical states - Climb, Level, and Descent
SHERL   CK 106
Flight 
Processor
Flight 
Processor
/home/data/atac/
SVAutoReports
xlsm, 
csv
Collector 
and 
Parser
NASA 
Data Feed
Legacy 
/SWIM
Flight 
Processor
Flight 
Processor
Flight 
Processor
Bond /home/data/atac
(Live Bay)
tb0
/home/ 
transfer/
atac
iff, ev, rd,
if0
Generalized data flow (data feed -> reports)
Report 
Generation
SHERL   CK
STREND
107
ERC
Max Overshoot
Angle at Intercept
Green = ground 
speed <180
Ground Speed / 
Attitude at intercept
Distance at Intercept 12.3nm
Arrival 
Runway
Direction of flight
FAF
Sherlock Performance Report Data
Turn-to-final (TTF)
- Overshoots
- Final approach path intercept
Facility report example: turn-to-final
SHERL   CK 108
Products Coming Soon
• Real-Time Merged Trajectories available in live format to 
downstream applications
• Automated Anomaly Detection of Airport Arrival 
Trajectories (SMARTNAS 2.4 NRA)
FAA SWIM 
Feed
(Solace)
NASA Collection 
and Forwarding
(ActiveMQ)
Transformation 
and 
Normalization 
(Apache Nifi)
Real Time 
Flight Merging
(Apache 
Flink)
YOUR
APPLICATIONS
HERE
SHERL   CK 109
• IFF, EV, RD data file and report documentation and business rules can be 
found here:
https://atmjira.arc.nasa.gov:9443/conf/display/ctas/ATAC+File+Format+and+R
eporting+Documentation
• Other questions or assistance:
John Schade jes@atac.com , 408-736-2822
• Thank You!
Documentation
SHERL   CK 110
Big Data system
SHERL   CK 111
• Unified analytics engine for 
large-scale data processing
• Performs fault-tolerant 
distributed computing and 
parallel processing services 
on a cluster
• Supports 4 languages:
– Scala (Native)
– Java (Fast)
– Python (Easy)
– R (Working)
Distributed Workflow
Driver Node
Cluster
Manager
Task 
Schedule
Resource 
Allocation
Resource 
Request
Partitioned Data
Task 
Schedule
Resource 
Allocation
Worker Node
(executor)
112
What is Apache SparkTM
Worker Node
(executor)
GeoServer
SHERL   CK 113
• Java-based software server that allows users to view, edit, and share 
geospatial data
• Enables users to visualize airspace features from latest adaptation 
data
• Built with the open source GeoServer tool, which includes the Postgis
database
• Connects to the vast amounts of airspace data stored in Sherlock as 
well as polygon representations of the CWIS data 
• Users can form complex queries, view the data, and export it in many 
digital and image-based formats using GeoServer web interface
https://geowiz.arc.nasa.gov/geoserver/web/?wicket:bookmarkablePage=:org.geoserver.web.demo.MapPre
viewPage
114
GeoServer
GeoServer
Example: Fixes around LAX on Google Earth
115
Credit Google, Map data USGS
THREDDS Data Server (TDS)
SHERL   CK 116
THREDDS Data Server(TDS) 
• The THREDDS Data Server (TDS) is a web server that provides 
metadata and data access for scientific datasets, using 
OPeNDAP, OpenGIS Consortium(OGC), Web Map 
Service(WMS), Web Coverage Service(WCS), HTTP, and other 
remote data access protocols.
• Sherlock Data Warehouse stores parsed weather data -
CIWS/RUC/CWAM in a variety of binary Gridded 
formats(NetCDF, Grib1, Grib2, HDF5) in THREDDS server which 
have their own mechanisms for parsing, viewing and 
downloading.
117
THREDDS Data Server(TDS) 
• Sherlock has access to all binary datasets.
• Users may query by hand or write scripts to query across large 
amounts of data and export the data in many digital image and 
formats
• Some users are interested in the content of binary weather files 
such as the rapid refresh and CIWS data. For example, they 
might want to find a RR file with high winds over a certain fix. The 
UCAR THREDDS server provides the ability to query binary data 
written in standard scientific formats such as HDF5 or GRIB..
https://geowiz.arc.nasa.gov/thredds/catalog.html
118
THREDDS Data Server (TDS) 
119
• Open-source THREDDS 
software reads weather 
datasets (CIWS, RR,CWAM)
• Supported binary formats -
NetCDF, Grib1, Grib2, HDF5
• WMS query, visualization, 
export
TDS Architecture
120
TDS
121


Sherlock Data Warehouse

Abstract

Similar works

Full text

Available Versions

NASA Technical Reports Server