PECO is an electric and natural gas utility subsidiary of Exelon Corporation, serving 1.6 million electric and more than 500,000 natural gas customers in southeastern Pennsylvania. Each year we continue to invest in our system to serve our customers better. Just like our customers replace equipment in their homes and businesses, in 2012 we began replacing our urrent electric meters with newer technology.
"SSIS is an extract, transform and load tool for database, easy to use and the primary integration tool between the data hubs providers and subscribers"
These new meters will help us provide more information to customers to help them understand how they use energy, and how to save energy and money. We also launched an interactive website that shows customers when they use energy, and what steps they can take to use less energy use and save more money. The new meters generate readings as frequently as every 15 minutes and introduce challenges not seen by the previous generation of meters that provided one daily reading.
PECO’s Meter Data Management System (MDM) is an operational system that validates and transforms meter readings for billing. However, the MDM is not designed to serve data. To fill the gap, PECO’s Information Technology team designed a data hub that supports multiple terabytes of data using the Microsoft Business Intelligence (BI) suite.
• SQL Server Enterprise Database
• Integration Services (SSIS)
• Analysis Services (SSAS)
• Reporting Services (SSRS)
• Master Data Services (MDS)
• Microsoft Excel and SharePoint
The main driver for selecting the Microsoft BI suite was its ability to support fast delivery time to market. The PECO IT team viewed its main focus as delivery of data to its business and external customers. The Microsoft BI suite integrates seamlessly with Microsoft Office and SharePoint providing a platform for a delivery model that enables the business to perform selfservice analytics.
The deliverables included:
• A scalable Star Schema database to store and process daily volumes of ~60 million rows of data to be retained for four years
• Processing that analyzes and aggregates data for multiple, divergent business groups and external consumers
• Simplified code development through a reusable framework to provide faster time to market
SSIS is an extract, transform and load tool used for database load processing. Currently the daily SSIS load of ~40 million meter readings executes in one hour. SSIS is data agnostic and integrates data from disparate data sources. An early success was the conversion of data from Sybase to Oracle. Using SSIS, this task was completed in less than three weeks, much less than the original three month estimate proposed using an alternative integration solution. The team has found SSIS easy to use and it is the primary integration tool between the data hubs providers and subscribers.
The Microsoft BI Semantic Model architecture enables the same data to be viewed and analyzed using SSRS, SSAS, SharePoint and Microsoft Excel for data access, aggregation and analysis. SSAS aggregates the data and insulates the users from the intricacies of accessing data in its raw form. SSRS is used to generate scheduled reports that are distributed to the customer through email and SharePoint.
Experienced team members are able to build reports within hours. Another presentation tool in the suite is PowerPivot. This provides a dashboard view of data that allows the graphical slicing and dicing of data. PowerPivot extends the use of Excel permitting large volumes of data well beyond the capability of traditional Excel. To date all project requirements have been satisfied using the Microsoft BI suite. These tools are integrated out of the box and Microsoft development skills are readily available in the marketplace.