Facebook Twitter LinkedIn YouTube E-mail
Home Business Intelligence FAA Aircraft Wildlife Strike Data Model and Dashboard in PowerPivot

FAA Aircraft Wildlife Strike Data Model and Dashboard in PowerPivot


This article is the second in a series detailing the use of FAA Aircraft Wildlife Strike Data within Microsoft BI dashboards.  The series installments are as follows:

  1. BI Design for FAA Aircraft Wildlife Strike Data (click here to read)
  2. FAA Aircraft Wildlife Strike Data Model and Dashboard in PowerPivot (this article) – video to be released next week
  3. Power View BI Motion Charts in SQL 2012 with FAA Aircraft Wildlife Strike Data (with video)

The design for FAA Aircraft Wildlife Strike Data was discussed and previewed in the previous article in this series which is linked above.  Once the tables have been pulled into PowerPivot and the primary/foreign key relationships have been established, the PowerPivot Diagram View of the tabular model appears as follows (columns not needed in the reports are “hidden” and grayed-out):

FAA PowerPivot Data Model

With the dimension tables integrated with the fact table in the model, the next step is to add a few hierarchies to the model for use in reporting.  Since the tabular model allows for reporting dimensions to comfortably exist on fact tables, the AIRPORT hierarchy exists on Fact StrikeReport.  The hierarchies added to this example are as follows (child level to the left of parent level):

  • Date > MM > YYYY
  • AIRPORT-RUNWAY(concatenated column) > AIRPORT > STATE > FAAREGION

With the model and the hierarchies complete, the measures can then be added.  The only true numeric columns are AOS, HEIGHT, SPEED, and DISTANCE, but there are several other potentially valuable calculated measures which can be created using DAX code.  Following are the calculated measures, along with the associated DAX code, which I added for the purpose of these reports.  If you’re building your own version of this report, go ahead and cut-and-paste the DAX code from the following list into cells of your calculation bar (DAX code is in red text):

  • INCIDENTS:=COUNTROWS(‘Fact StrikeReport’) – Total count of wildlife strike incidents
  • % of AC_CLASS:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Dim Aircraft Type'[Aircraft Code])) – % of total represented by selected aircraft class
  • % of AC_MASS:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Dim Aircraft Mass'[AC_MASS])) – % of total represented by selected aircraft mass classification
  • % of TYPE_ENG:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Dim Engine Codes'[Engine Key])) – % of total represented by selected engine type classification
  • HEIGHT AVG:=AVERAGE([HEIGHT]) – Average height from ground at time of strike
  • SPEED AVG:=AVERAGE([SPEED]) – Average speed at time of strike
  • DISTANCE AVG:=AVERAGE([DISTANCE]) – Average distance at time of strike
  • % PHASE FLT:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Fact StrikeReport'[PHASE_OF_FLT])) – % of total represented by selected flight phase classification
  • % for DAMAGE:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Dim Damage'[DAMAGE])) – % of total represented by selected damage classification
  • % EFFECT:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Fact StrikeReport'[EFFECT])) – % of total represented by selected effect of the strike
  • % SKY CONDITIONS:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Fact StrikeReport'[SKY])) – % of total represented by selected sky conditions classification
  • PRECIP %:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Fact StrikeReport'[PRECIP])) – % of total represented by selected precipitation classification
  • # SPECIES:=DISTINCTCOUNT([SPECIES_ID]) – Total number of unique species in the query
  • % of SPECIES:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Fact StrikeReport'[SPECIES])) – % of total represented by selected species classification
  • % of BIRDS_STRUCK:=[INCIDENTS] / CALCULATE([INCIDENTS], ALL(‘Fact StrikeReport'[BIRDS_STRUCK])) – % of total represented by grouping classification for number of birds struck
  • TOTAL_COST_REPAIRS:=SUM([COST_REPAIRS]) – Total cost of all queried incidents
  • AVG_COST_REPAIRS:=AVERAGE([COST_REPAIRS]) – Average cost of repairs for queried incidents
  • TOTAL_COST_OTHER:=SUM([COST_OTHER]) – Total costs not associated with repairs
  • AVG_COST_OTHER:=AVERAGE([COST_OTHER]) – Average costs not associated with repairs
  • TOTAL INJURIES:=SUM([NR_INJURIES]) – Total quantity of injuries for queried incidents
  • AVG_INJURIES:=AVERAGE([NR_INJURIES]) – Average number of injuries per incident
  • TOTAL FATALITIES:=SUM([NR_FATALITIES]) – Total number of fatalities for queried incidents
  • AVG FATALITIES:=AVERAGE([NR_FATALITIES]) – Average number of fatalities per incidents
  • # INCIDENTS W FATALITIES:=COUNTAX(FILTER(‘Fact StrikeReport’,[NR_FATALITIES]>0),[NR_FATALITIES]) – Total number of incidents that included at least one fatality
  • % INCIDENTS W FATALITIES:=[# INCIDENTS W FATALITIES] / [INCIDENTS] – Percentage of incidents that included at least one fatality
  • DAMAGE COUNT:=COUNTAX(FILTER(‘Fact StrikeReport’,[INDICATED_DAMAGE]=”TRUE”),[INDICATED_DAMAGE]) – Total number of incidents that included some form of damage
  • DAMAGE INCIDENCE:=[DAMAGE COUNT] / [INCIDENTS] – Total percentage of incidents that included some form of damage

Now that the calculations have been added to the model, the data can immediately be added to Excel 2010 for exploration.  Following are a few examples via screenshots (a video to accompany this post will be posted next Monday):

FAA Wildlife Strike Data at MSP

Above: Over the last 21+ years at MSP airport, medium-sized unknown birds have been responsible for the majority of strikes but less than 10% of those incidents caused damage.  Snow Goose and Tundra Swan strikes caused damage for 100% of incidents which identified their involvement.  As per the graph on the bottom of the dashboard, strikes are more frequent in early Spring and late Summer/early Fall.  Notice that Barn Swallow strikes spike in August, which appears to correlate with this linked article that claims they fly “higher in the air” in late summer as they follow the insects.

FAA Wildlife Strike Data Fatalities

Above: Larger aircraft appear to have lower fatality rates when encountering a wildlife strike.  Wildlife that appear to cause the most injuries and fatalities include Red Tailed Hawks, White Tailed Deer, and different varieties of geese.

FAA Wildlife Strike Data MSP Incidents

Above: View reported aircraft wildlife strike data at MSP airport by runway, likelihood of damage, average repair costs, and likely time of wildlife strike.

Check back next week for a follow-up video to this post, and feel free to pass along any questions or inquiries.


 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
2 Comments  comments 

2 Responses

  1. rtkothe

    Greg – Thank you for posting info like this. I would really like to complete this exercise. But, I find there are too many gaps for me setting up the data model and getting the data into powerpivot. I think the series would be improved by including accompanying videos with the first two articles. Nuts and bolts of how to setup the model and how to get the data into powerpivot. Maybe this info is somewhere else on your site. As a fledgling BI guy, I would appreciate more of the intro material and since I am the first post here – maybe others are in the same boat. Thanks – Ron