Success Story: The Energy Authority (TEA)

Problem #1: Validating Slow-Changing Dimension Loads

The Challenge

After developing core dimensions for the data warehouse and unit testing with a business, The Energy Authority (TEA) found inconsistencies with how the fact load was relating to the dimensions. Several hours of troubleshooting later, they learned that some of the slowly changing dimension type 1 and type 2 loads were not working properly.

The Solution

Utilizing LegiTest, the TEA team and Pragmatic Works consultants wrote tests associated with each dimension load. For each dimension, they inserted test records to see the behavior when new SCD2 updates, SCD1 updates, and SCD2 updates with SCD1 changes scenarios occurred.

The Benefits

By having assertions for the time it takes the test to run, and comparing the current, expired, and business key row counts, TEA is able to quickly identify and correct bugs. Moving forward, they utilize the dimension load template within LegiTest to catch bugs prior to releasing solutions for business testing.

Problem #2: Continuous Daily Validation of Multiple Data Feeds and Sources

The Challenge

TEA implemented tests against the most used dashboard in the company where traders and clients can view meter, generation, load, profit and loss, settlement, and other data sets for various locations in one of the markets they trade in. This allows them to decide how to operate for the next day. Because this application has 4 data sources and over 17 data feeds, troubleshooting if there is an ETL or data issue can become very stressful and time-consuming.

The Solution

TEA utilized LegiTest to compare average row counts over the past 30 days against the data that is expected today for each data feed. If the data for today is 20% less than what was averaged over the last 30 days, the tests fail, and a notification is emailed out at 6am.

The Benefits

Having this automation allows BI to catch the issue before the traders, and TEA is able to easily tell what data feeds are affected and who to call to address the issue. In the past, it would take 1-2 hours, and at least one trader, one BI individual, and the data feed in question’s database administrator or systems analyst to identify and resolve the issue. With a minimum of 3 people, it can take a total of 6 hours to correct an issue, whereas, with LegiTest, it reduces that time to roughly an hour.

Problem #3: Testing the Impact of Code Enhancements to Stored Procedures

The Challenge

TEA began utilizing LegiTest for regressions testing on one of their big application ETL processes after writing tests to help with identifying errors in the initial development of the Enterprise data warehouse and monitoring of operational data feeds. This process is used by TEA's settlement team to verify statements which are submitted into the journal. Because of the way the data is provided from the vendor, TEA has many stored procedures that transform the data in stages. Making any minor change or code enhancement took hours to test, and many times a new bug would appear after it was deployed to production.

The Solution

With LegiTest, TEA is able to compare each staging table and stored procedures between environments to ensure the results are the same. The team has saved on average 6 hours of regression testing prior to being released to the business for testing.

“Adding these types of tests across our entire BI footprint will help us reduce operational costs for troubleshooting data issues and allow us to focus our time on developing robust solutions.”

— Cameron Hunt, Business Intelligence Engineer with The Energy Authority

The Energy Authority is a public power-owned, nonprofit corporation with offices in Jacksonville, Florida, and Bellevue (Seattle), Washington. As a national portfolio management company, we evaluate challenges, manage risks, and execute solutions to help our clients maximize the value of their assets and meet their goals in a cost-effective manner.

Download the Case Study