This case study defines how SPK and Associates assisted a client within the medical device industry to create a lean and automated approach for essential report production by developing a custom adapter.
Identifying the opportunities ripe for automation.
Our med-device client is a global developer and manufacturer for innovative medical products and services. The labor intensity and complexity for reporting on various internal procedures was creating inefficiencies.
A particular division within the company is responsible for defining the requirements, tools and systems. These are then utilised by the company’s manufacturing facilities, including their Research and Development (R&D) department. The division is also responsible for validating the delivered systems meet their requirements.
As the personnel of this division plus the R&D teams it supported, totalled up to 100 users, reporting had become cumbersome and time consuming. This was further impacted by limitations within the systems to build extensive reports from numerous disparate sources.
These extensive reports were not only manually collated, but could include screen shots of waveforms from analytical programs. They could also include downloaded testing equipment images.
To further compound the time-intensity, these reports could be in multiple formats, including Word and Excel. Once the reports were collated, the next step of the process required collation into a singular cohesive report, which could at times exceed 100 pages. And after all that work, the division were then required to convert it into a final PDF report to be used as their design history file (DHF) within the company’s document-management system.
In order to support their development and manufacturing activities, the client used PTC Windchill RV&S (Requirements, Validation & Source) – previously known as PTC Integrity. This was used to manage the definition, qualification, and, the commissioning of tools and systems.
Identifying there was an opportunity to streamline and simplify the process, the client requested expert support from SPK and Associates.
Creating a custom adapter for PTC Windchill.
The first course of remedial action required the implementation of a reporting solution based on the SPK Trace Report Engine. This could take the PTC Windchill RV&S data to provide test case requirement reporting, This also provided the ability to trace artefact relationships within the PTC Windchill RV&S system. It could also generate Excel reports.
In order to create an optimised reporting solution that could include rich-text and embedded images, SPKA proposed a custom enhancement—an “adapter”—for an existing component in PTC Windchill. This adapter was named Gateway.
Gateway had the sustenance to handle the import, and export, of individual Word and Excel documents, and the ability to export a Word document addressed the need for rich-text support and attachments included as embedded images. However,this did not address the need to collate:
- test-execution session details;
- testing results;
- exception reports, along with the test-protocol document used for the test-execution session.
Therefore, there was a need for a custom adapter with the correct functionality. We leveraged the proven ability of PTC Windchill Gateway to export RV&S documents into Word format.
Our “adapter” approach was scoped out in a matter of days, and achieved development and validation within one month. This is a circa five month reduction in the time that would have been required to create a custom tool from scratch.
The “Push-Button Future”
Today, users of the new Gateway adapter can generate test-session reports in less than five minutes, with just a few clicks in their RV&S client.
The test-protocol data extracted from RV&S is automatically augmented with related test-session data, test results, and exception reports which correspond to the selected test-protocol document and test-session item.
Furthermore, with the simple click of a button, users can convert that rich, all-in-one Word doc into an equally rich, all-in-one PDF for document-management system upload.
Client benefits achieved:
- Manual effort dramatically reduced.
- Faster solution implementation to drive the right insights and reduce time wastage
- Customisation and cost were within agreement due to leveraging existing, proven, technology. customization, and expense, to a minimum.
1-Click Reporting
20% Cost Reduction
8% Efficiency Improvement
Digging To The Bottom Of The Mystery
Our clients Dynamics ERP system was running via an on-premises server. Their data was spread across numerous different systems and data warehouses. SPK (based in California) set up a data gateway to work on the project remotely.
The insights showed there were two root-cause issues. The first was related to their on-prem ERP system:
The company’s reporting system had been created by a third-party consulting firm; it wasn’t part of the original, internal Dynamics software, therefore, it was applying undocumented filters to the company’s own data. The users were left unaware of this data manipulation. For example, if a new labor class of employee was created after the reports were set up, those employees would be excluded from any reporting.
The second source of the incorrect reporting, was due to their Power BI setup:
Power BI was used to import data from several different sources (i.e. Dynamics ERP and Salesforce). These also had their own undocumented filters. Again, this led to data manipulation,
For example, their purchase order (PO) budget forecasting report failed to indicate when additional POs were issued; it only provided the combined PO totals. Our client had no sight if they were over-budget at any point in a project, or, forecast the need to request an additional PO.
To make matters worse for the CFO, the Power BI reports themselves were not documented or configured to her business insight needs.
The Solution
The introduction of a data gateway from the clients pre-existing ERP system, alongside the importing of data into Microsoft Power BI created a ripple effect that would turn the data manipulation into business intelligence.
By analyzing the clients data and generated dashboards allowed them to view their raw data business metrics. The undocumented filters (and, incidentally, their internal IT person who had installed them on Power BI) were history.
Finally, the company had the KPIs it craved, including:
- Employee billable utilization percentage.
- Contractor billable utilization percentage.
- Utilization percentage compared to target.
- Non billable time allocation.
- PO budget forecasting totals—indicating when PO budgets will be exhausted versus resource forecasting.
As with all businesses that evolve, so too did our clients needs. Shortly after completing this project, our client required migration to an entirely new ERP system: Oracle NetSuite. We were able to use our expertise and knowledge of their business to seamlessly migrate all of the Power BI dashboards.
To accomplish this project, SPK and Assosicates provided an expert team, including a Senior Engineer, Director of Finance, Senior Application Integration Engineer, Applications Services Engineer, and a Mechanical CAD Engineer.
In true collaborative style, our SPKA expert team worked alongside our client CFO, Senior Business Analysts, Director of Systems and Technology, as well as a Senior Revenue Accountant.
The initial data and project analysis required two months of work, followed by a further four months successful implementation.
Bountiful Benefits
Post the data engineering being complete, the data was then clean and immediately available in easy-to-navigate dashboards. We were able to provide the CFO with the configuration for the most important business intelligence insights. The clean data and dashboards now highlighted interesting lagging indicators which could drive business decisions:
- A removal of flat-rate manually calculated, billable hours. This was now replaced with a revitalised logic factoring in real-world data, (i.e. actual work-days per month, holidays, mid-month personnel changes, and contractor insights which were previously unavailable.
- Approx 20 percent of employees were underutilized
- Around eight percent of employees were underperforming.
The leading-indicators now showed with their current resource forecasting, they would exceed their PO budget—with about 22 percent of projects on track to be over-delivered.
The CFO now had the business intelligence required to take action.
This was a complete game changer for us. We were making decisions based on bad data, when we were even able to get the data. SPK discovered the problems, cleaned up the data, and then put together effective and beautiful visualizations. We can finally make decisions based on something real!
Get a no-obligation initial assessment
Whether you’re trying to solve technical problems, implement new technology tools and strategies, or improve efficiency, SPK and Associates can be a key part of your strategy. We can provide a free, no-obligation initial assessment on almost any technology or engineering situation.
Contact us today at (888) 310-4540 or info@spkaa.com.