Here at 5000fish, we encounter many unique challenges companies face regarding data collection, storage, and reporting. Recently, we took on a customer that was facing a data extraction problem. The customer is a manufacturing company that has been in business for more than 40 years and has grown to the point where it now has multiple locations. In this article, we’ll outline the company’s data extraction pain points and explain how the Yurbi Customer Success team was able to solve this issue.
The manufacturing company had been using a very outdated ERP system that didn’t allow the company to extract its own data. This was because the ERP vendor didn’t provide the company direct access to its database. Additionally, the ERP system had poor exporting tools, which means the company had to rely on the vendor to conduct data extracts in order to access and analyze its data. The ERP vendor was providing the company 15 files of data each week in various formats, including Excel, CSV, .txt. The company had to accept the files as-is because the ERP vendor would charge the company a hefty fee to change the export formats of its data. Because the data was formatted inconsistently, the company then had to cleanse and reformat the data for quality check and consistency purposes.
After this lengthy data collection and reformatting process, the company took the data and dumped it into a master Excel document in which all of the data was stored. They did this monthly because the process of doing it weekly took too much time. This Excel file contained relevant manufacturer information, such as margins per contract for each contract tier, and month-over-month and quarter-over-quarter performance. All of this information was very important, but the process of collecting the data by working through the ERP vendor was incredibly time-consuming and the end reports produced were both basic and static (and a 30-day rear view).
When we began working with the company, its team had invested in a BI solution the year prior that didn’t work out because the solution didn’t have the formula capabilities or level of customer support that the company needed. In order to show the company that Yurbi was the right solution to address its pain points, we initiated a trial where we replicated the company’s spreadsheet and outputs to calculate its formulas.
During the trial process, we knew right away that we had to work out a solution with the data formatting so that the data was delivered in a format that could be seamlessly uploaded into Yurbi. Our goal was to take this data and create a set of web-based dashboards and reports that would allow management and other company stakeholders to view key metrics and analyze the data deeper. During the process of sorting out the data formatting issues, we discovered that the files had to undergo many changes before they could be uploaded into Yurbi. In this situation, Yurbi’s Excel and CSV processing features would be incredibly helpful for validating the data types and converting the raw files into a database format. But we needed the data in a raw spreadsheet format, stripping out header and footer text and other formatting issues.
First, we had to walk the company’s users through the process of cleaning up the files in order for the data to properly upload into Yurbi, with the intention of automating this process. For the trial this step was manual but once the process is documented it was easily automated via Excel macros. Next, we imported all of the company’s Excel files into Yurbi, which automatically populated the data into database tables. Then, we leveraged Yurbi’s architect capabilities to join all of the data files together to form a relational model in the company’s environment that communicated the relationship of all of the files to Yurbi. Finally, we recreated all of the company’s formulas in order to rebuild the master data set, metrics, and pivot tables within Yurbi. Once we recreated the master data set, the possibilities were endless regarding data visualization and analysis within Yurbi. The ultimate result was improved access and visibility into the company’s data.
The Yurbi team successfully streamlined and enhanced the company’s entire reporting process, but the biggest win for the company was the ability to not wait until the end of the month to generate ERP reports, but to leverage the weekly files to have more current information. For Phase 2 the process can be completely automated by implementing a Windows application that queries the FTP server where the vendor places the files and instead of weekly, the processing can be completed daily. This application can automatically run the data through a cleansing program, fixing all of the format issues and removing extraneous data. Then after the data is cleansed, the application can automatically load the data into the proper database tables which Yurbi reads to deliver interactive dashboards and reports.
By smoothing out the company’s data extraction and reporting processes, we were able to save the company a week’s worth of manual effort each month, plus increase their frequency of reports to weekly (and eventually daily). We were also able to turn the company’s initial BI failure into a success because the company can now leverage its data to make more strategic business decisions.
Does your company have a data collection, extraction, or reporting issue (…or all three)? Contact us to see if we can help create a custom solution for your business.
