+1 (317) 804-2330 | info@ballsystems.com | Blog
Small businesses typically don’t have an on-site IT department to help guide them through process automation. The tools employed by the company are generally for a specific purpose, like an MRP system, CAD software or Office365. These purpose-built tools work well for the employees they serve, but don’t always natively integrate with each other. This fragmentation leads to tedious and sometimes redundant workflows.
Ball Systems is continually evaluating our business systems, processes, and quality, and through that continuous evaluation of our own business, we recognized the accumulation of disparate systems was creating inefficiencies on a large scale. Sales uses HubSpot as a CRM, the design team uses Teamwork for project management and time logging, production uses M1 for an MRP, and project managers use self-made Excel dashboards. Internally, team member and manager feedback were demanding streamlined KPIs, automation, eliminating data input redundancies, and faster access to key information and status. All these needs were both surfaced and recognized by key leaders and after being analyzed we recognized a common theme for what we have called data driven decisions. As a result, our project was titled “D3”. Given this is a small company of less than 30 employees, a low-cost solution was achieved by leveraging existing tools (Office365/Excel) and a low tier Azure SQL database, and by internally developing tools (Python).A system architecture plan was created to identify all the data sources and how they would flow. This is represented in the image below.
Figure 1: D3 Architecture map
The first critical step was to identify the architecture and confirm internal customer buy-in. The architecture was derived by identifying all the sources of data, confirming methods to extract data, identifying individual pieces of data within the sources, and finally confirming the findings with the (internal) project customers. I held interviews with these internal customers to test if all required information and needs had been captured. These interviews played a critical role, not only by confirming the data was captured, but also by getting customer-generated ideas for automation, reporting, redundancy elimination, etc. The original concept subsequently became a set of requirements for both the data aggregation architecture and the output usage of this effort.
Implementation began by first compiling the data into a central database, then using that database to drive automation, reporting, etc. Our team’s initial data sources were:
The database design was created to match the data sources as closely as possible, meaning we used the same table and column names and datatypes.
M1 runs on a SQL Express database (yes Express, did I mention we’re small?) and Microsoft offers Data Sync functionality. This tool will automatically synchronize selected tables from your on-premise database to an Azure SQL instance. Working through the kinks, we were able to get this data moving relatively easily.
Next was to tackle getting website data into Azure. This was accomplished by writing a Python program. Python ‘s advantage here was its short development time. In a few weeks, I was able to get a programming environment and workflow established with no prior knowledge. I was also able to accomplish my software needs with only a few non-standard libraries. The result was working code that could be maintained by someone with basic programming skills. Functionally, the code reads web API data, transforms it, and performs Create-Read-Update-Delete operations to the Azure SQL database.
With all of our data sources feeding the central database, the final step was to create metadata to describe how all of this information is related. Previously, this was being done by manually created and managed Excel workbooks. Because we are a custom engineering organization, most our work is in the form of custom design and build projects or build to print projects. As a result, we think of our workflows in terms of projects. As a result, a few new database tables were created that allowed for a project definition. A project can contain many or all of the following data elements: HubSpot Deals, Project Quotations, Teamwork Projects, M1 Sales Orders, and/or M1 Jobs. It also has data such as a description, due date, customer, project owner, etc.
To manage all this metadata, a desktop application was written using Python and the built-in GUI library toolkit. This program, aptly named ProjectLinker, allows for engineers to “link” the various sources together to create a broader project definition. At this point all the data is in one spot and logically mapped together.
Finally, Excel was leveraged to create interactive dashboards. The PowerQuery function was heavily used to query Azure SQL for data, then macros or other tools transformed it into a human usable format. These dashboards and reports now automatically update and, in conjunction with the ProjectLinker, have almost completely eliminated data input redundancies while providing numerous automation opportunities, and quick access to key pieces of information at multiple levels of the organization. Project managers for multiple projects are able to interact and operate at a project level, or across multiple projects they manage. Department managers are able to see overall department status, resource loading, performance, etc. And finally, executives are able to see department level KPIs, as well as drilcl into specific project status and performance with ease.
While the project continues to evolve, the foundational architectural work continues to serve as an effective base for the development of new tools. Ours is a culture of continuous improvement and also consistency of process for our test equipment and engineering customers. D3 will serve as a critical cornerstone of many of those efforts as we move forward.
Need help with a custom engineering project? Learn more here.
Blog Comments