Helping Manufacturers Scale and Grow through Modern Data Warehouse Architecture
When a large manufacturer wanted to use more sophisticated analytical tools, it found its current data warehouse not reliable enough to meet its needs. Inspire11 partnered with them to assess the needs of their technology stack and introduced a Modern Data Warehouse Architecture that will scale as the company grows.
The opportunity the client faced
The large manufacturer wanted to advance the use of data and analytics capabilities, but the existing environment began to fail up to 40% of the time, leaving the business in a bind wondering why they should depend upon a tool for core operational needs when the reliability was so low.
The on-premise data warehouse supporting its business analytics became untenable after being continually extended with additional functionality “bolted” onto the solution. Over time, technical debt increased to a point where true modernization was needed. The client engaged Inspire11’s Data Analytics practice to provide a current state assessment and provide a recommended approach forward for their business needs.
Why they turned to Inspire11
During an initial Discovery phase, Inspire11 identified inefficiencies in the core solution and business processes that caused increased costs for the client. Additionally, Inspire11 found inconsistencies in the implementation of business logic flows that increased the complexity of system support and maintenance as well as dependencies on obsolete, unsupported technologies.
- Multiple instances of redundant or unnecessary transformations between data landing and reporting layers
- Business logic was applied unevenly across these different processing steps making it difficult to identify and remediate issues. For example, sometimes business logic would be contained within a BI tool, while other times it was in a staging layer
- Pervasive use of manual, Excel-based business reporting
- Use of obsolete technology that was no longer being supported by the vendor
How we designed the Modern Data Warehouse Architecture
Inspire11 designed the new Modern Data Architecture (“MDA”) platform to address key areas of inefficiency in the legacy system. The new MDA platform simplified the end-to-end processing and improved solution reliability while providing business users with automated reports to streamline internal processes. At its core, our solution was designed for scalability by utilizing a metadata-driven Extract, Load, Transform (ELT) framework and methodology that prioritizes reusable code components configured at runtime with metadata.
Our team relied on the following framework guiding principles:
- Flexible & Extensible: Think big but start small
- Nimble: Fast to set up and easy to adapt to change
- Thrifty: Cost-effective to build and maintain
- Resilient: Processes run without special care and attention; resolve simple issues automatically
- Lean Design: The solution is no more complicated than it absolutely needs to be
In three months, Inspire11 performed the following activities:
- Onboarded data from 5+ sources and hundreds of tables into a cloud-based Microsoft Azure environment
- Eliminated 40% of redundant “hops” between landing and reporting layers
- Enhanced data processing with a variety of cloud-enabled features: change tracking, exception handling rules, parallel processing, and hundreds of business transformations of varying complexity
- Migrated heavily utilized reports into a Microsoft Power BI solution to demonstrate the reliability and performance benefits of the new environment
- Business UAT process to ensure that migrated functionality met the specifications
- Developed new and advanced capabilities with the improved granularity of operations transactions
Impact and Outcomes
With Inpsire11’s support, the manufacturing company was able to improve its processes and output efficiency across the following domains: Quality, inventory forecasting and spoilage, on-time delivery, labor utilization, Operational Equipment Effectiveness (“OEE”), S&OP, sales operations, and cash-flow management. The solution also delivered a 100% reduction in data processing failures, achieved a 90% reduction in total lines of code improving both scalability and reliability, and realized a 75% reduction in end-to-end processing time, decreasing processing time from hours to succeed down to minutes. Furthermore, Inspire11 migrated hundreds of legacy reports into a Microsoft PowerBI, corrected business logic errors represented in the on-premise data warehouse, and built a platform capable of onboarding a new data source in less than 15 minutes that will scale as the business grows.