Say Hello To MAI
MAI specializes in designing and manufacturing sub-assembly modules for automotives. They aimed to use cloud tools for better reporting & automation because their existing data was scattered across spreadsheets hindering historical analysis and trend spotting, it set back their scalability and delayed decision making. With an expected 40-60% reduction time taken in report making, they needed an impactful solution. So, they partnered with Sunflower Lab to resolve this, most importantly because we had experience with the Microsoft Azure environment and were based in Ohio for face-to-face interactions which was vital to build trust.
Deliveries
– Power Apps
Technologies & Integration
– Power Apps
– Power BI
– Azure Data Lake
♦ MODULAR ASSEMBLY ♦ SUPPLY CHAIN MANAGEMENT ♦ OE OUTSOURCING SOLUTIONS
MAI’s Pain Points
Over 100 hours spent per month on one report (data in several excel sheets)- Fragmented Data slowing down manual data entry, overall leading to delayed data driven decision making.
- Constant crashes in excel based system where in case a file was accessed by one team member crashed it blocked others from accessing the same file.
Over 100 hours spent per month on one report (data in several excel sheets)MAI's Data Management Challenges
The data was unstructured and needed to be aligned for effective use in Power BI reports.
There was data governance gaps to identify which user had which role.
Data Synchronization with vendor’s database needs to be established.
We identified 4 key solutions that would help to solve MAI’s challenges such as:
- Microsoft Azure for data migration: Moving data to the cloud-based Azure platform enables better organization and standardization.
- Power Automate data collection flows: Streamlining data collection from ERP, Operations & Manufacturing systems ensures consistency and reduces manual entry errors
- Power BI data models for reports: Creating structured data models within Power BI facilitates data analysis and visualization.
- Building a web app with Power Apps specifically for OEE data collection further streamlines data collection and analysis for Overall Equipment Effectiveness (OEE).
MAI was looking to move its on-premises system to the cloud. It was considering Azure data lake given its inclination towards the Microsoft suite of products. A few of the workforce in MAI used Linux but 99.9% of the employees worked on Windows 10 or Windows 11.
With most people using Office 365, Microsoft was the preferred choice.
What was expected?
The subsequent step is to mirror the QAD database and flat file to Microsoft Azure.
MAI aimed to recreate/create new Azure SQL reports and Azure Dashboards using existing CyberQuery, Excel and Insequence reports.
Further on, it looked to create data visualization and analytics of these reports using Power BI
What was expected?
Deploy Power Automate data collection flows for ERP, Operations, and Manufacturing data and create Power BI data models for reporting.
Create a web app for data entry using PowerApps to replace existing OEE (Overall Equipment Effectiveness) reports and other manual entry items. With this application users from each department could be a super user and access PowerApps or normal web app to serve their purposes.
The internal department of MAI is using Power Automate to automate its repetitive tasks for Accounting and Finance.
What was expected?
Build PowerApps or normal web apps for OEE data collection.
What was MAI’s scenario?
Before MAI partnered with Sunflower Lab to work on the project, it had already started migrating to the cloud with Power BI licensing. Even though it had all its licensing in place, it hadn’t started using it. The prime reason for this was –
The data was unstructured and needed to be aligned for Power BI reporting.
There was a need to work on data governance to identify which user had which role.
It needed to streamline how frequently the vendor needed to sync its data with the database.
With an interconnected ecosystem of websites and applications where MAI users could access their reports, apps, etc. all in one place and fetch data from legacy systems making the process quicker.
What MAI was expecting?
What won us over was your understanding of the scoping and then really trying to understand our idea is what we’re trying to accomplish because since we don’t have a budget, it’s hard for me to just go in and say hey here’s everything that we want to do and let’s just do it. What helped me was that the Sunflower Lab team really understood that and they were really on board with trying to help us get down that road map and trying to make sure that we could segment things into multiple phases
Cote Watson
ERP Supervisor – Modular Assembly Innovations
Watch Detail Testimony Here
⸻ PROBLEM #1
Data Silos & Outdated ERP
MAI had Isolated Data Due to Decentralized Storage:
1) Multiple Plant Storage Systems: MAI utilizes separate storage systems for each plant. This creates data silos, isolating information and making it difficult to:
- Consolidate data: Gain a comprehensive view of operations across all plants.
- Analyze trends: Identify patterns and insights that might not be evident from individual plant data.
- Standardize practices: Ensure consistency in data collection and reporting procedures.
2) Lack of Common Folder Structure: The absence of a standardized folder structure across plants further complicates data access and analysis. This leads to:
- Difficulty finding information: Time wasted searching for specific data due to inconsistent organization.
- Limited collaboration: Challenges in sharing and collaborating on data due to inconsistent structure.
3) Outdated ERP System Hinders Data Flow: Extracting data from QAD 2010 SE has become difficult. This can lead to:
- Inaccurate reporting: Delays and inconsistencies in data retrieval can result in outdated or incomplete reports.
- Inefficient decision-making: Lack of readily available data can hinder data-driven decision making and hinder proactive problem solving.
- Increased manual workarounds: Time-consuming manual processes may be required to compensate for issues with data extraction.
Our Solution
We used Power Apps as a robust solution to address their challenges across multiple plants. Here’s how
- Centralized Data Collection and Configuration Management: We created a single application with Power Apps for data entry accessible to all plants. This eliminates the need for separate spreadsheets and storage systems.
- Role-Based Access Control: The application can be configured with role-based access control. This allows:
Employees: To easily fill data for production, shift details, and downtime directly within the app.- Admins: To create and edit configurations that define data entry parameters and ensure consistency across plants. These updates will be reflected for all employees.
- Data Validation and Improved Data Quality: This application enforced data validation rules to prevent the entry of inaccurate or invalid information. This ensures the overall quality and reliability of collected data.
- Simplified Shift Reporting: Employees can directly enter production, shift data, and downtime details through the Power Apps interface.
Employees: To easily fill data for production, shift details, and downtime directly within the app.Impact
- Improved Data Quality: Consistent data formats, data validation, and centralized management contribute to more accurate and reliable data.
- Increased Efficiency: Streamlined data entry, automated configuration updates, and reduced manual work save time and resources.
- Enhanced Collaboration: Standardized data collection and role-based access control facilitate collaboration and information sharing between employees and administrators.
- Simplified Report Generation: Consistent data formats and centralized storage enable effortless report generation based on the most recent configuration and entered data.
With reporting we had some reservations. Sunflower Lab had its UI/UX developers take a look at it. What SFL presented was great and we really liked it. If there was any concern SFL went back and solved it. You guys have made it a painless process.
Cote Watson
ERP Supervisor – Modular Assembly Innovations
⸻ PROBLEM #2
Unreliable Reports & Production Downtimes
Manual Excel Reports: MAI relied on manual report generation in Excel, leading to:
-
- Inefficiency: Time-consuming manual data entry and report creation.
- Limited Visibility: Difficulty viewing historical data or analyzing trends across multiple months.
- Production Downtime: The old server struggled with QAD, causing crashes and downtime that impacted critical production processes, including Material Planning and Logistics (MP&L).
Our Solution
We established a direct connection between Power Apps and the database. This streamlined data collection and ensured data accuracy by capturing information at the source. The data collected through Power Apps was structured effectively, making it readily accessible for analysis in Power BI.This centralized and standardized data storage eliminated the need for individual Excel files and improved data consistency.
Impact
- Effortless Report Generation: MAI’s team can now use Power BI’s intuitive interface to explore data with ease. Prompts and filters allow them to quickly find the information they need, while powerful visualizations make complex data readily understandable. This translates to reports generated with minimal effort
- Data-Driven Decisions: Reliable and visually appealing reports generated by Power BI empower stakeholders to make informed decisions and develop effective strategies based on accurate data.
Streamlined Project Management with Jira
By utilizing Jira, we enhanced project visibility, facilitated collaboration, and streamlined the process of achieving project goals. We :
- Tracked Progress and Ensure Transparency: Monitor project progress in real-time, ensuring everyone stays informed and aligned with milestones.
Meet the Project Team
Sunflower Lab handpicked professionals until we were convinced that the proven team would best fit for MAI by assessing our
and MAI’s strengths.
Being Trusted Microsoft Experts & familiarity with Azure, Power Platform (Power Automate, Power BI, Power Apps), and potentially QAD integration ensured a smooth implementation process.
Scattered Data Consolidation & strong understanding of how to organize and structure MAI’s fragmented data across multiple plants.
End-to-End Database Setup with not only design & implement the new database structure but also ensure its ongoing functionality & scalability to meet MAI’s evolving needs.
