PG&E’s Power Database for Self-Generators

1. PG&E’s Power Database for Self-Generators Team 4 Salina Cheung, Tim Lee, Marc Oman, Avani Shah, Ognen Stojanovski, Sam Wang, Lucas Yun-Nikolac

2. E-NET • A PG&E program that allows customers to install their own solar or wind energy generators • Current paper-based application process is inefficient & potentially costly • Key objectives: speed, accuracy, tracking BACKGROUND California Energy Crisis • In 2000, demand for electricity grew far faster than expected, significantly outstripping supply BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

3. ENHANCED ENTITY RELATIONSHIP (0,N) Vendor (1,N) (1,N) Manufacturer made by made by works with (0,N) (1,N) (1,N) (1,N) (0,N) Contractor (1,N) wind solar (0,N) buys from serves as makes (1,N) finds (0,1) Person o o Backup Contact (0,1) sells Equipment (1,N) (0,N) (1,N) represented by (0,N) utilizes (0,1) (0,N) (0,N) (1,2) Customer (1,1) (1,1) (1,1) (1,N) (0,N) Application for PROJECT needs New meter (1,1) submits (1,N) (1,N) (0,1) (0,N) (0,N) registers as complete sets deadline contacts approves inspects visits (0,N) (0,N) (0,N) (0,N) (1,N) (0,N) GIS Manager Engineering & Planning Field Tester GIS Clerical (0,N) (1,N) (1,N) d manages schedules inspection (1,N) Employee BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

4. RELATIONAL SCHEMA BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

5. NORMALIZATION Sample Relation Currently in 1NF WIND (windID15,made_by4, turbine_model, turbine_rating, no_turbines) Possible Conversion to 3NF: WIND (windID15, made_by4, turbine_model) NO_TURBINES (windID15, no_turbines) TURBINE_RATING (made_by4, turbine_model, turbine rating) Example Relations Currently in 3NF TESTER_VISIT (project1, field_tester9, date_review_requested, actual_review_date, results, comments) CONTACTS_WITH_CUSTOMER (manager6, customer11, date, reason_for_contact, means_of_contact) BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

6. FORMS BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

7. Purpose APPLICATION FORECAST Provides information to PG&E management to prepare for any necessary workforce addition and/or reallocation SQL SELECT0.15*Count([APPLICATION_DATES]![Initial Component received])+(1-.15)*[ExpQuery]! [Expr1] AS [Forecast] FROM APPLICATION_DATES, [Exp Query] WHERE (((APPLICATION_DATES.[Initial Component received]) Between #5/1/2000# And #5/31/2000#)) GROUP BY [Exp Query].Expr1; BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

8. CUSTOMER SERVICE Allows PG&E and its customers to identify the most popular generator type and manufacturer of installation in each region Purpose SQL SELECT [PROJECT].[Area], "SOLAR" AS TYPE, [SOLAR].[Made by], Count([SOLAR].[Made by]) AS [CountOfMade by] FROM PROJECT INNER JOIN ((EQUIPMENT INNER JOIN SOLAR ON [EQUIPMENT].[Equipment ID]=[SOLAR].[Solar ID]) INNER JOIN PROJECT_USES_EQUIPMENT ON [EQUIPMENT].[Equipment ID] = [PROJECT_USES_EQUIPMENT].[Equipment]) ON [PROJECT].[Utility LogNumber]= [PROJECT_USES_EQUIPMENT].[Project] GROUP BY [PROJECT].[Area], [SOLAR].[Made by]; UNION SELECT [PROJECT].[Area], "WIND" AS TYPE, [WIND].[Made by], Count([WIND].[Made by]) AS [CountOfMade by] ….. ….. GROUP BY [PROJECT].[Area], [WIND].[Made by]; BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

9. E-NET PROGRAM CONTRIBUTION Provides important E-Net program statistic and trend illustration to PG&E management, policymakers, and consumers for future decision-making Purpose SQL SELECT DatePart("m",[Initial Component received]) AS [Month], DatePart("yyyy",[Initial Component received]) AS [Year], Sum(EQUIPMENT.[Nameplate rating]) AS [SumOfNameplate rating] FROM GIS_MANAGER_APPROVAL, (PROJECT INNER JOIN APPLICATION_DATES ON PROJECT.[Utility Log Number] = APPLICATION_DATES.[Project ID]) INNER JOIN (EQUIPMENT INNER JOIN PROJECT_USES_EQUIPMENT ON EQUIPMENT.[Equipment ID] = PROJECT_USES_EQUIPMENT.Equipment) ON PROJECT.[Utility Log Number] = PROJECT_USES_EQUIPMENT.Project WHERE(((GIS_MANAGER_APPROVAL.Complete_date) Is Not Null)) GROUP BY DatePart("yyyy",[Initial Component received]), DatePart("m",[Initial Component received]) ORDER BY DatePart("yyyy",[Initial Component received]) DESC , DatePart("m",[Initial Component received]) DESC; BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

10. BOTTLENECK IDENTIFICATION Identifies the greatest bottlenecks of the application process so PG&E may improve upon its greatest deficiencies Purpose SQL SELECT"GIS Clerical Completeness Check" as Process_Name, AVG(((Complete_date-Start_date)-1)/1) as Avg_Percentage_Deviation, (SUM(MeanTime)/10) as Mean_Processing_Time FROM GIS_CLERICAL_COMPLETENESS_CHECK WHERE ((((Complete_date-Start_date)-1)/1)>0.3) UNION ….. ….. UNION SELECT"Tester Inspection Scheduling" as Process_Name, AVG(((Complete_date-Start_date)-2)/2) as Avg_Percentage_Deviation, (SUM(MeanTime)/10) as Mean_Processing_Time FROM TESTER_INSPECTION_SCHEDULING WHERE ((((Complete_date-Start_date)-2)/2)>0.3); BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

11. INSPECTION FAILURE Purpose Allows PG&E to inform its applicants of the steps most likely resulting in application failure and how they could address these issues SQL SELECT PS.Step_Full_Name AS Process_Name, COUNT(PS.Step_Code) AS Number_of_Failed_Application_at_each_ Process FROM PROJECT AS P, PROCESS_STEPS AS PS WHERE P.Project_status="F" And PS.Step_Code=P.Failed_step GROUP BY PS.Step_Full_Name ORDER BY COUNT(PS.Step_Code) DESC; BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

12. INSPECTION FAILURE BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

13. QUESTIONS & COMMENTS.