New York Solar Initiative

Completion Date:

05-04-2022

Project Overview

The New York Solar Initiative has been collecting information on solar energy projects across New York Since 2001. This project uses Microsoft SQL Server and SSIS to produce an ETL and Data Warehousing solution that harvests, cleans, and organizes the raw data collected from that Initiative so that it is suitable for analysis. The analysis made possible by the solution will provide insight into what adjustments could be implemented to improve the outcome of solar projects. The solution consists of a Staging, Operational Data Store, and Data Warehousing Layer. The Staging Layer extracts data from a JSON source using a Script Component. The Dictionary Tables in the ODS Layer are loaded and updated using Lookup Transformations. The Data Warehouse Layer is designed with 3 fact tables, 8 Dimension Tables, and a Star Schema that includes a Date Dimension. All the tables in the Data Warehouse are loaded using Merge Statements implemented using Stored Procedures and Execute SQL Tasks.

Role:

I was the lead ETL and Data Warehouse developer.

Problem Statement

Despite taking only 88 minutes for Earth to receive enough energy from the Sun to power world-wide consumption for an entire year, solar energy only accounts for 3% of electrical production in the United States. Instead of solar, environmentally problematic sources of fuel such as natural gas (40%), coal (20%), and nuclear (20%) account for 80% of its electrical production. Practices need to be adopted that can increase the efficiency of solar projects, reduce their cost and help to evaluate the feasibility of large-scale solar energy production.

Executive Summary

Data Dictionary

Key Takeaways

Next Steps

1. Utilize the most efficient equipment in a high percentage of future projects. Test new equipment in the remaining projects for better efficiency.
2. Use the Data Warehouse to perform analysis to evaluate the most efficient contractors.