Improving Data Accessibility and Reliability By Moving To Cloud
MAI set its roots as a modular assembly and supply chain management company that was launched as a collaborative venture with Honda-owned Midwest Express, Inc. It went on to acquire multiple firms that provide modular assembly, sequential parts delivery and supply chain management services to manufacturers throughout America.
Deliveries
– Power Apps
Technologies & Integration
– Power Apps
– Power BI
– Azure Data Lake
♦ MODULAR ASSEMBLY ♦ SUPPLY CHAIN MANAGEMENT ♦ OE OUTSOURCING SOLUTIONS
Business Background
Modular Assembly Innovations came to Sunflower Lab because it had huge reports it managed in Excel format, viewing and managing which became unmanageable at a point in time. When a shift report was ready, the responsible employee had to manually email it to the higher management and wait for the approval of the request. Each employee had to manually lock their shift time and downtimes if there was any downtime in the production line. Based on these details a report was drawn up and by the end of the day has to be sent to the manager
It is only imaginable how cumbersome this manual task could be. These reports crash several times a day and in case a file was accessed by one team member, it blocked others from accessing the same file. Since these were internal on-premises files and reports, accessing them was restricted from outside the VPN.
MAI was based on-premises and never worked on the cloud. It was looking for a partner that could migrate them to Azure cloud as a preferred choice. Since its cognizance of the IT front was low, it was looking for a technology partner who could walk it hand-by-hand as the project moved.
Sunflower Lab was adopted by MAI to work on the following pain points:
To improve data accessibility- Migrating to Azure
To improve data accessibility- For generation of reliable reports
- Optimizing Knowledge & Licensing
TIMELINE
Even though the project is rather new, we are ready to deliver the first phase of the project and going strong towards
second phase.
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.
MAI before Digital Revolution
Before MAI partnered with Sunflower Lab to usher 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.
And finally, it had to work on Power BI reporting accessible for MAI.
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?
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 #1
Data Accessibility
Modular Assembly Innovations has multiple plants with each plant having its separate common drive (data storage system). Each plant has its own departments, so the access level is managed at the plant level. None of the plants follow a common folder structure or layout. Out of the common drive folder, an Excel sheet is managed where different data is placed for production, quality, and finance that is managed at plant level leaving behind the synchronicity at organization level.
MAI has QAD 2010 SE which is its ERP software for ordering, purchasing, and scheduling. It is running on an outdated Operating System, that runs into frequent problems and often time falls out. Being of legacy systems, it is not possible to install new packages and APIs thus stunting sync with cutting-edge technology. This makes pulling out data all the more challenging.
MAI has certain 3rd party integrations (CyberQuery) to pull data and store it in Excel and ODBC connectors that enable querying against the progress database QAD is running on. Also, in case one is not on the VPN or connected to the internal network, there isn’t accessibility to the reports.
Our Approach
To resolve the issue of data accessibility we opted for a well managed data source in SQL.
- With PowerApps we can view and change data.
- We maintain all the data logs to easily bifurcate and search data in SQL.
- What PowerApps will offer is that it will have a UI interaction to engage with the data source and fetch results.
- This will make data visibility and management easier than Excel by integrating the database with PowerApps.
- Therefore, we don’t have to really interact with the data source, but we have a UI through which we can talk to the data source without viewing it.
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
⸻ PROBLEM #2
Current Reports Are Not Reliable
The reports are stored in Excel format from where they are extracted in HTML. However, due to the heavy size of reports, there could be issues like – what if the spreadsheet, excel, macro for reports breaks as the process of updating happens every hour.
As the server is old it leads to performance issues with QAD. With the advent of time, the requirement of accounting and finance departments also increased leading to the necessity of more data causing slowdown and performance issues. This results in crashes that drive downtime for production MP&L (Material Planning and Logistics)
Our Approach
To resolve the issue of data accessibility we opted for a well managed data source in SQL.
- The users could fetch data by prompting.
- We structured the data and made it accessible by Power BI.
- We built an application in PowerApps which was connected to the database
- Ultimately the users could easily fetch reliable reports on Power BI making decisions and strategizing easier.
⸻ PROBLEM #3
Knowledge and Licensing: How It Failed and Our Resolution
When we began with the onset of the project, there was a concern with logging in guest users or users who were not a part of the organization. So, we took the step to register users with username and password. However, this idea failed terribly because the portal/application was made in PowerApps which allows only Microsoft login. The user who has access to PowerApps with Office 365 license only then they could access the account.
The reports involving Insequence and QAD require IT teams to set up since they are quite technical, so end users cannot create them on their own
QAD and Insequence also need additional licensing for users to access the system of running reports on their respective environments, which is quite expensive, at approximately $3500 a year.
Our Approach
To resolve the issue of data accessibility we opted for a well managed data source in SQL.
- Our current solution is to follow the approach of providing temporary licenses to guest users so that they can use the application with limited features.
- The guest account we create is linked to Office 365 with a PowerApps license managed in Microsoft Azure.
- This gives the users the authorization to access PowerApps and enter data that can be represented visually.
How We Used Jira for Internal Project Management
We used Jira to track the internal project process. In each sprint, we laid down the foundation to what was the next target to be achieved and tracked progress as the project proceeded.
If I had to rate Sunflower Lab on a scale from 1 to 10, I’d go by 9 which to me seems near perfection. I don’t have any bad thing to say for you guys.
Cote Watson
ERP Supervisor – Modular Assembly Innovations
Brains behind the technology
Sunflower Lab handpicked professionals until we were convinced that the proven team would best fit for MAI by assessing our
and MAI’s strengths.
Where Sunflower Lab Stood Out
Several reasons aligned in the process where Sunflower Lab was a strong contender to work with MAI.
Finding a company that could propel and hold hands with MAI in every step since it was the first time they were going on cloud.
Not many local companies had expertise in the Microsoft suite of products.
Data spread all over the place needed streamlining.
Someone who could handle the database from setup to end.
