Introduction
MacAllister Machinery is a long-established Caterpillar dealer headquartered in Indianapolis, serving customers across Indiana and Michigan. As the company introduced a new ERP system and expanded its footprint, its small data team struggled to keep up with data warehousing demands. By adopting WhereScape RED for data automation on Microsoft SQL Server, MacAllister transformed a reactive, fire-fighting environment into proactive business intelligence—with an eightfold productivity gain.
Pre-WhereScape Challenges
- Manual, fragile ETL tooling: Early data warehouse iterations relied on SSIS and BIML, which were hard to maintain and slow to refresh.
- Inflexible to ERP change: Frequent ERP releases outpaced the team’s ability to adjust data pipelines and business rules.
- Limited validation and trust: Low visibility into transformations meant the business questioned data quality and rule application.
- Multiple instances and systems: Indiana and Michigan ERP instances plus separate financial systems were difficult to merge.
Why WhereScape
Stephen Jordan, the company’s Database Administrator, needed a way to bring multiple ERP instances and financial data together in a single data warehouse while automating the data flows end-to-end. Hand-coded SSIS jobs and BIML scripts left him spending all his time on plumbing rather than analytics. After exploring alternatives such as Informatica, he turned to WhereScape RED for drag-and-drop data warehouse automation, built-in documentation, and a scheduler that would give him back his time.
“I realized we needed to do something different. We were not able to respond to requests, so I started looking for other solutions and ways of doing data warehousing.”
– Stephen Jordan, Database Administrator, MacAllister Machinery
The Solution
Unifying Multiple Data Sources
WhereScape RED gave Stephen a way to bring Indiana and Michigan ERP instances together, alongside separate financial data, into one SQL Server data warehouse. Data from multiple sources now flows through consistent staging, load, and third-normal-form layers.
Automated Development and Deployment
Instead of hand-writing stored procedures, triggers, and SSIS jobs, Stephen uses WhereScape’s drag-and-drop GUI to define structures and flows. RED generates the underlying code and documentation, making change management and deployment far quicker.
Greater Visibility and Confidence
WhereScape RED provides clear lineage and visibility into the transformations at every stage. This gives Stephen and the wider team confidence that data is being transformed correctly and nothing is lost in the process.
Results
🚀 8x Productivity for the Existing Team
- Data automation has made the existing workforce eight times more productive.
- Stephen now supervises automated processes from WhereScape, instead of manually decoding complex ETL.
- More time is spent helping the business analyse data, not just moving it.
🚀 Faster Response to New Data Requests
- New data requests can be scoped and delivered with predictable timelines.
- Data can be sourced from ERP and deployed across dev, QA, and production in about an hour.
- Previously, building equivalent SSIS jobs took an entire day of effort.
🚀 Stronger Trust and Collaboration
- Reliable, documented data flows have improved relationships within IT.
- The business receives accurate data more quickly and trusts it more.
- Decisions are now based on clean, well-understood data.
🚀 Reduced Risk Through Documentation
- WhereScape automatically documents tasks, processes and lineage.
- Knowledge is no longer trapped in one person’s head.
- A second data warehouse resource can be trained and onboarded quickly.
From Reactive Firefighting to Forward Looking Strategy
Before WhereScape, MacAllister’s data warehousing was all about keeping up with changes and patching fragile jobs. With daily ERP releases and multiple instances to manage, Stephen (Database Administrator) had little capacity for true analytics. WhereScape RED flipped that around, allowing him to focus on how data should flow, how to apply business rules, and how to support better decision-making across the dealership network.
Easier Scheduling and Dependency Management
WhereScape’s built-in job scheduler helps Stephen visualise dependencies far more intuitively than a list of SSIS jobs. Data flows between stages are easier to manage, and failures or delays can be diagnosed quickly. This makes overnight processing and refresh cycles more robust and transparent.
Growing the Data Warehouse Team
Because WhereScape RED is driven through a graphical interface backed by generated code and metadata, MacAllister can now train additional team members to work on the data warehouse more easily. The company is purchasing a second license and expanding the team, spreading knowledge and reducing reliance on a single specialist.
Ready for Data Vault and the Cloud
The rich metadata that WhereScape produces gives MacAllister the flexibility to move to new platforms and modelling styles in future. The team is already investigating Data Vault modelling and potential moves to Snowflake and Azure in line with broader Caterpillar dealer trends. Whatever direction they choose, they know the automation layer will help them adapt quickly.
“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.”
– Stephen Jordan, Database Administrator, MacAllister Machinery
