slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
PG&E’s Power Database for Self-Generators PowerPoint Presentation
Download Presentation
PG&E’s Power Database for Self-Generators

Loading in 2 Seconds...

play fullscreen
1 / 13

PG&E’s Power Database for Self-Generators - PowerPoint PPT Presentation


  • 103 Views
  • Uploaded on

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. E-NET. A PG&E program that allows customers to install their own solar or wind energy generators

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PG&E’s Power Database for Self-Generators


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

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

slide2

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

slide3

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

slide4

RELATIONAL SCHEMA

BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

normalization
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

slide6

FORMS

BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES

slide7
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

slide8

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

slide9

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

slide10

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

slide11

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

slide12

INSPECTION FAILURE

BACKGROUND EER RELATIONAL SCHEMA NORMALIZATION FORMS QUERIES