Article

Conservation Data and Reporting Icebreaker

Created 12/9/2011 by Michael Frank

Updated 1/7/2015 by David Shepherd-Gaw

167 views • 2 comments

To get the ball rolling, this group needs a "first post" to break the ice.

At Tacoma Power I oversee several Access databases, "Excelmarts", and various other datasets in various states of repair, to try to put together a clear picture of how much conservation we have attained, what the cost is, and who we should try to market new ideas to. I'm sure I'm not alone in the Conduit community in this regard, so please feel free to post any questions, best practices, tips, concerns, frustrations, or whatever you may have, and we'll work on creating a community that can assist one another.

To get the ball rolling, I'll give you a view into our data landscape. We have two main Access databases, one for residential, and one for commercial/industrial conservation data. These were created around 10 years ago, and in the past couple of years I've been trying to update them to meet our more current needs. We also have around 5-10 Excel spreadsheets where additional data comes in, often from contractors (Washwise, Energy Smart Grocer, etc). Currently we don't have an easy way to bring all this data into one place for easy reporting. I'm currently working on doing this by bringing the data together into a single SQL Server database.

We also get data from several non-conservation sources. This includes our utility billing system (SAP), County Assessor data, Census data (future), and a few smaller sets. We are trying to do some data standardization on fields such as address, in the hopes that we'll be able to merge this data together so that we can get a clearer picture of our customers, as well as what conservation measures each has already performed.

OK, I have stepped out on the ice, and placed a few cracks in its surface. Who dares to step out onto it with me?

Michael Frank
Tacoma Power

Comments (2)

Brian Sinclair on 12/15/11 on 07:59 AM (Pacific Time)

Thanks for breaking the ice Michael.

At Energy Trust of Oregon (http://energytrust.org), for the past 3 years we have been developing a Business Intelligence solution that brings data together from our 3 core systems: Goldmine (CRM), MS GreatPlains (Accounting), and FastTrack (a proprietary project management application) into a single data warehouse for reporting. We use the SAP Business Objects platform which includes Data Services (to extract and transform data into the warehouse) and Web Intelligence, which simplifies both standard and ad hoc reporting for non-IT users.

Our primary reporting needs are to track the incentives we pay to residential, commercial and industrial customers as they adopt energy efficiency practices and/or install renewable energy technology. We are always comparing the energy saving and generation benefits our customers achieve against the cost of acquiring these benefits. This BI project has allowed us to increase the quality and accuracy of the reports we are required to prepare for our various stakeholders, including the Oregon Public Utility Commission, our Board of Directors and the electric and gas utilities on behalf of which we operate.

Energy Trust is also embarking on a enterprise-wide data modeling effort to consolidate our 3 current (and overlapping) data models into a single data model that will address some data architecture and data quality issues and take our BI platform to the next level.

I'd be very interested to hear from other organizations who are engaged in data modeling and/or data warehousing so we can share lessons learned, best practices, etc.

Cheers,
Brian Sinclair
Energy Trust of Oregon