March 26, 2020 Increase Customer Trust with Data Warehouse Automation

By automating its data warehouse, MacAllister Machinery was able to transform its business for customers.

The Challenge

The concept of a data warehouse is not new, but the diversity of software used to store, move and analyze data today creates challenges for even the most talented developers. Often issues arise when disparate data sources must be combined to create a timely single source of truth, and such was the case at MacAllister Machinery, a Caterpillar dealer covering Indiana and Michigan.

Several years ago, MacAllister started using a new ERP system. The company’s Database Administrator, Stephen Jordan, required additional reporting capabilities and so decided to build a data warehouse. Stephen tried basic SSIS packages but found them hard to maintain, while they couldn’t be refreshed more than once a day. He then experimented with BIML tools but found they couldn’t maintain his data warehouse properly – every time the ERP system was changed it took Stephen too long to respond.

Such manual ETL tools meant that early iterations of the data warehouse had very little validation, so Stephen didn’t have confidence in the data that flowed into the data warehouse. The business relied on the data for reports but they weren’t sure business rules were being applied as expected.

In contrast to the data warehouse’s lack of agility, MacAllister’s ERP was actively developed and releases would often happen on a daily basis. Stephen didn’t have the bandwidth to work on and respond to this level of workload, while he relied on the ERP developers to advise him on how to get the data out of the ERP.

To add to the complexity, MacAllister’s Indiana and Michigan dealerships operate on the same ERP but on separate instances, so Stephen needed a way to merge them, along with financial data that was coming in from a different system altogether.

The Solution

Stephen started looking for a way to combine
all data sources and instances into one data warehouse, and to manage these data flows automatically so he could focus on how to use the data rather than just collect it.

Stephen came across Data Warehouse Automation from online searches. He considered Informatica but it wasn’t quite right for needs, so he contacted WhereScape and booked an hour-long online demo. Stephen quickly saw that WhereScape was the solution he was looking for, and started using WhereScape RED to make load, stage and third normal form tables.

WhereScape RED negates hand coding by allowing data architectures to be built via a drag-and-drop GUI. The tool then writes the underlying code that builds the desired structures on the target platform and documents the whole process.

“WhereScape gives us lots of visibility into the transformation of the data throughout the staging process, so we are very confident the data we are pulling out of the ERP system is being transformed correctly throughout the process and there is nothing getting lost anymore.

“Being able to quickly drag and drop tables from the source system and having all the data types come over quickly, with all the tables already defined, has significantly reduced our time to get new data sources into the system. The ability to take those and flow them all the way through the process has been great.

“The WhereScape job scheduler helps me visualize the dependencies much more than a list of SSIS jobs, so I can manage those dependencies much easier and track the data through the various stages of the process.”

The Results

“We weren’t sure how we could bring two instances of ERP together and WhereScape made that possible. We now combine three data sources with WhereScape and are gradually getting the fourth one on. I spent most of my time solving puzzles that WhereScape solves automatically. Today I focus on how to flow the data coming into the data warehouse, cleaning it up and helping the business make better decisions based on that data.

“I can source data from the ERP and deploy it through my development, QA and production data warehouse environments within an hour. Before WhereScape I had to spend a full day figuring
out how to build that into an SSIS job, get it into some sort of job scheduler somewhere with its dependencies and so on.”

Data Automation has made Stephen’s job less repetitive by enabling him to supervise complex processes he once had to decipher and commit himself. This makes him more productive and the data warehouse more effective, and has also improved personal relationships and trust within MacAllister’s IT department.

“We can now respond to new data requests very quickly and give the business a timescale for when they can expect the data. They now trust us more, not just because we can provide the data faster but also in the confidence we have in that data. The business can make better decisions based on the data they’re getting.”

Whereas previously the data warehouse was
a complex network of manually created SSIS jobs that only Stephen knew, now tasks and processes are automatically documented so they can be understood by other team members that touch the data, and transferred should Stephen leave the company.

“The documentation gives MacAllister some security. We have a reporting team and data analysts that can use that documentation when they have questions of how the data comes through to the data warehouse. With WhereScape pretty much anyone that knows the data can use the drag and drop interface to move it around. Now we can train a second person to work on the data warehouse with WhereScape much easier than before we started using automation, so we’re now purchasing a second license and expanding the data warehousing team.”

The metadata WhereScape produces enables users to lift and shift their entire data infrastructure to a new platform and change modelling style. This gives the agility to adapt to industry demand or to capitalize on the latest technological advances. So what next for MacAllister?

“We’re investigating Data Vault and moving to the Cloud with Snowflake and Azure. We’re watching what Caterpillar might do and they might do a snowflake Data Warehouse for dealers so we’re looking into that. While we haven’t settled on a plan we know WhereScape will allow us to switch platform or modelling style easily.”

About the author
Matt O’Connell is general manager at WhereScape, the leading provider in data infrastructure automation software. Prior to his time at WhereScape, he also worked for companies such as Microsoft, BMC Software and Oracle.

Subscribe to Industry Today