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.
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.
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.
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.
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
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
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.
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.
The Energy Authority is a public