This is a dimensional data warehouse that seeks to provide insights into the raw data that FEMA provides publicly for its Individual and Housing Program. I used Jupyter Notebook, Python (Pandas, NumPy, Pyodbc), and SQL to perform ETL on the dataset, loading the warehouse based on the schema I designed. I also created visualizations using Tableau from the data warehouse to provide targeted insights that answered the key business questions of the project (see README file). Note: If etl_IHP.ipynb is throwing an error on load, it can be viewed using nbviewer by following this link: https://nbviewer.jupyter.org/github/kristinvmartin/datawarehouse-fema-bu/blob/main/etl_IHP.ipynb, or you can view the CODEONLY file, which has the scripts without the output.
See .docx file for complete report submitted for coursework at Boston University for the CS 689 (Designing & Implementing a Data Warehouse) Final Term Project.
Visualization files from Tableau, SQL scripts, and IPYNB files are included in this repository.
FEMA, or the Federal Emergency Management Agency, oversees the federal response to disasters and emergencies in the United States. FEMA allocates federal resources to municipalities, businesses, and individuals facing disasters and disaster recovery efforts. Although much of FEMA’s effort is focused on large disaster recovery efforts, it also provides aid directly to households affected by disasters through its Individuals and Households Program (IHP). The IHP, according to FEMA documentation, “provides financial and direct services to eligible individuals and households affected by a disaster who have uninsured or underinsured necessary expenses and serious needs” (FEMA, 2019).
This data warehouse seeks to provide meaningful insight into the data collected from households participating in the IHP program, so FEMA (or another third party) can ascertain which areas have been hit hardest, which disasters had the biggest impact, if demographic factors play a role in aid, along with other geographic and demographic insights.
- In the last [time span], what [geographic area] has been awarded the most IHP relief dollars?
- How many disasters were declared in [geographic area] during [timeframe]?
- In which [time span] does FEMA award the most IHP relief dollars?
- What percentage of individuals requesting aid are granted it, and how does that compare with the volume of requests?
- What proportion of dollars awarded were allocated for Other Needs as compared to Housing Assistance in the last [time span]?
- What are the most awarded disaster types? Is time of year a factor?
- What proportion of dollars awarded were allocated for Other Needs as compared to Housing Assistance in the last [time span]?
- Is more aid awarded to Other Needs, or Housing Assistance and has it changed over time?