Retail Operational Reporting

Issue

Our client, a retail electric provider, engaged MidDel Consulting to determine best course of action to improve reporting efficiency and operational business intelligence. MidDel was tasked to identify and build improved data integrations and reporting processes for use by key business areas. After review of current reporting and multiple process reviews, MidDel proposed a high-level architecture to integrate the multiple divergent data sources into a single view of the truth. A key component of the architecture was to include proofing, data exceptions and tie outs based upon feedback from current Systems of Record, thus building reliability into the overall design.

Solution

MidDel provided an overall design of the dataflow, database architecture for reporting data and built the data integrations to consolidate the client’s data. MidDel also provided design specifications and proposed the solution to reside in the Microsoft Azure cloud. A database instance and virtual server were built out to handle all data storage needs and integration services. Use of Microsoft Azure allowed for future growth and improved reliability with the ease of use of Microsoft platform and Active Directory services. Realtime access to the Azure database and server were handled via site-to-site VPN connection with no external public facing interfaces allowed for security purposes.

MidDel reviewed and determined the best sources to use for the Operational Data Store to ensure accurate and reliable reporting. Several key sources of data were identified to integrate, ranging from raw EDI data files to Oracle databases. Data sources, which were only available via system generated reports from the Billing system of record, were also setup to be exported and imported daily. The target goal MidDel proposed was to get to a clean and reliable view of the system data. The Operational Data Store was established, confirmed operational and configured to be updated daily. Once regular data refreshes were established, MidDel then followed through with the creation of reporting solutions to be used by key business areas. Exception reporting and error handling were built into the daily process to notify system administrators of any failures during the daily data migrations. Completion of the Operational Data Store allowed for creation of several key reporting needs and benefits as stated below.

EC Infosystems Gaps Filled:

  • Historical Usage Acquisition and Cleansing
  • ICAP data validation and monitoring, billing system contained limitations on refreshes of Usage and ICAP values
  • Custom built TrueTrack web scraper to download all EDI files (you own the files now) and consolidate HU into a single data view
  • Reformulated ECI Billing Summary to allow for actionable reporting on related Unbilled, Prorated and Swing analyses
  • Increased ROI and Improved Analysis to estimate budgets more accurately
  • Billing System Automation Web Extender for billing efficiency and billed rate Quality Control

The Operational Data Store solution provided several key benefits:

  • Architecture platform to provide seamless prior day data warehousing capability
  • Clean and Proofed Historical Usage Repository via raw EDI 867 files
  • Integrated raw EDI data which were never before reportable, such as PSEG NJ 814 Credit Hold
  • Billing validation by way of comparing Raw EDI HU Usage against Actual Billed volumes
  • Integrated billing summary report data more efficiently to allow for improved manipulation and reporting. Examples include monthly proration of Charges and GRT/Sales Tax allocation.
  • Daily generated reporting and exception reporting enabled end users to focus on job functions, not report creation

New and Improved Report Targets:

  • Electric/Gas Unbilled Reporting for Month-End Close validation
  • Forward Position Reporting with Renewal and Usage Forecasting, dynamic and inclusive of multiple scenarios.
  • Swing or Bandwidth Tolerance Reporting for Over/Under Usage Exception based billing
  • Invoice Level AR Aging for Auditor Purposes and Requests
  • 814 enrollments/drops/reinstates for reconciliation of contracting and billing system records
  • Reconciliation of Gas as Delivered versus Gas Billed, allowed proof of every Billed Therm versus Delivered Therm
  • Usage Proration Reporting of Historical or Billed Electric/Gas usage
  • Gap reporting and rate checks for billing periods
  • Bill type switch exception via monitoring the 814 EDI transactions of customers moving out of POR and into Dual Bill status, which is then moving the credit risk to the retailer
  • REC/ZECs and Sales tax reporting
  • ECI work around for bill register automated billing selector and rate and usage validator to improve accuracy and speed of the billing process
  • TrueTrack downloader to capture and data warehouse all EDI data from Billing Provider
  • Automated HU requests submitted to ECI FTP site and monitoring of all Usage Needs for active and pending accounts

These recommended solutions provided scalable systems with small delivery risk, as work was completed in small phases. Each phase of work resulted in the client seeing improved results and benefits. The advantages of reporting improvements were proven with each deployment iteration. Many reporting requests were completed quickly or accurately, given the foundation provided via the Operational Data Store. The solution provides the client with the means to increase productivity, work on more important tasks, and reduce manual report creation activities, while also providing system scalability and minimizing the need for additional staff.

Result

The client utilized the solution provided and received several key benefits including streamlined reporting, reliable and accurate report generation, and as a result of the consolidated reporting, allowed the focus of the business to return to improving business operations and improving the overall system data integrity and cleanliness.

The estimated benefits to the organization are:

  • $50,000 in expense savings by producing faster and improved audit results
  • $200,000 FTE cost reduction
  • $500,000 in annual revenue increase by more timely related swing calculations
  • $500,000 increase to the bottom line by improved view of position forecasts and renewals
Request PDF