Legacy Billing Data Integration Process
Integration of your data into the Oracle Utilities Opower platform involves the following phases: sample historical file integration, full historical file integration, and incremental file integration. In each phase, your Delivery Team will work with you to integrate, validate, and approve each of these files. The files will contain the same columns, and will follow the same formatting and naming standards. The files will only differ in the number of customers included and the periods of time they cover.
Oracle Utilities recommends that you extract data from your production billing system. This ensures that the customer contact information and energy usage data you transfer to Oracle Utilities is as accurate and current as possible.
Keep a Copy!
We strongly recommend that you keep a searchable copy of the sample historical, full historical, and incremental files. These files will make it easier for you and your Delivery Team to work together to diagnose issues.
Sample Historical File Integration
Your Delivery Team will work with your technical lead and data extract engineer to review our data specification and map your data elements to the fields in the Oracle Utilities Opower data file format. Your engineering team then generates a sample historical data file. The sample historical file needs to include all the production-quality billed usage data for (at minimum) a random sample of 10,000 - 20,000 customer accounts—both inactive and active—and all their billed usage reads recorded since January of last year. For example, if your Delivery Team planned to launch your program for the first time on December 1, 2016, the team would need data going back to January 1, 2015.
The relatively small size of the sample file allows your Delivery Team to quickly analyze and diagnose issues. Your Delivery Team will provide you with a list of any issues found in the sample file and ask that you provide an updated file. This process is repeated until a sample historical file passes diagnostic testing. It typically takes several rounds of sample file generation, analysis, and feedback to approve a sample historical file.
Keep File Generation Simple!
Your Delivery Team is committed to making it as easy as possible for you to generate the files needed to launch your program. If it is easier for you to skip the sample historical file and generate a full historical file immediately, that is fine. You can discuss the easiest path with your Delivery Team.
Full Historical File Integration
After the sample file has passed validation, we can begin the process of generating, validating, and approving a full historical file. This file needs to include actual production data for all active and inactive customer accounts since January of the previous year. It is not expected that historical data since January of the last year will be available for every customer account.
The data set should also include accounts for customers who have moved in or moved out since January of the previous year. Oracle Utilities needs data for all utility customer accounts, including inactive accounts, even if only a small subset of those customers will be enrolled in the program. Within a given customer population, some customers will not meet the program eligibility criteria for reasons like low usage values or gaps in data. By providing more customer data, you create a larger pool of customers for statistical comparison. This allows Oracle Utilities to compare customers that are as similar as possible. Better comparisons will result in more actionable insights, more energy savings, and a better experience for your customers.
Tip: If possible, write your extract program so that it can receive an “end date” parameter at runtime. You can use this to generate the full historical file as of a few weeks in the past. This will keep a set of data available to use for generating and validating incremental files.
Full historical file approval typically requires several rounds of generation and validation. The approved historical file will be loaded into the Oracle Utilities Opower production application, and the data within it will be displayed to customers in reports, email messages, alerts, and/or web applications.
Data Extraction Flexibility
If your historical data set is so large that you need to extract it into multiple files, that is fine. If you need to send multiple files, your Delivery Team can append them together for analysis. In this case, make sure to follow the row and file-level sorting rules. See Legacy Billing Data File Specifications for more information.
Incremental Data Integration
After your Delivery Team approves and loads the full historical data, the team will generate an incremental file that includes any new customer information and billed usage data recorded since the most recent record in the full historical file that was delivered. Move in and move out data must be included as part of the incremental file.
Avoid Read Gaps!
Incremental file data should start immediately after the last read in the full historical file or files. There should be no gaps in reads for any customers between historical and incremental files.
Your Delivery Team will validate the file with a special focus on gaps, overlaps, and corrections.
After Delivery Team approves the first set of incremental files (preferably a full billing cycle’s worth), your team will schedule an automated file transfer. The file transfers should initially go to the /shared_files folder until you receive approval from the Delivery Team to start transferring to the /uploads folder. (See File Transfer Methods for more information.) During this transfer, your system will push ongoing incremental files to the Oracle Utilities Opower SFTP server. Ongoing incremental files should be sent at least once a week, but daily file transfer is also possible. Because reports are only created when new data is available for a customer, interval data must be provided at least once a week for Oracle Utilities to generate weekly reports. Files need to arrive on the SFTP server by the start of business Monday for any week in which we plan to generate Oracle Utilities Opower Home Energy Reports.
Tip: Send fresh data frequently. Even if we are not generating reports, the more often we receive data, the more often we can refresh the energy insights we deliver to customers across channels. Extracting data directly from your production database and sending it to Oracle Utilities directly after extraction will help ensure that it is as fresh as possible, so customers will receive the most current and accurate data.
After the initial incremental transfer, your Delivery Team will configure automated alerts for missing and incomplete files. These alerts enable both Oracle Utilities and your team to quickly respond to any file transfer issues that may emerge after your Oracle Utilities Opower product is deployed to production.
Once incremental files are approved, the automated transfer is up and running and our alerts are listening for anomalies, then the data integration process is complete.
Tip: Usage data should be extracted based on last update, not last read date, to ensure it includes all reads, updates, and corrections since the last file. This will help to avoid gaps and inaccurate data.