Our most recent work with FIMC has been focused on implementing improved methods for sharing data with their vendors and customers. Specifically, we wanted to create a way for FIMC to share data that could be more quickly and easily analyzed by their suppliers and distributors, preferably using a method that is less labor intensive for FIMC.
FIMC has an existing web portal that allows its suppliers to check on the status of their contracts and learn of upcoming raw material needs. On this portal, suppliers are able to download this information as an Excel workbook containing a spreadsheet with a high-level summary of the data as well as several additional spreadsheets with more detailed information. While this is an effective way for suppliers to receive and analyze their data, the process of updating these workbooks is labor-intensive – updates are currently being done every two or three days. This system also relies heavily on one person to do the updating, which is problematic. Case in point: in January, FIMC’s procurement manager went on vacation and the spreadsheets weren’t updated for two weeks!
FIMC currently emails static reports to their distributors that contain information on their sales for the past month. These reports are sent in the latter half of the following month. They do not include any comparison to the previous year’s sales or allow distributors to compare their performance to others in their region – both features highly desired by distributors. Because of this, most distributors spend hours entering data from the emailed report into their own spreadsheets to see year-to-date performance. Therefore, by the time distributors have the data in a format they can analyze, it is almost a month old. Distributors are clamoring for better tools for timely, strategic decision making.
FIMC also maintains an intranet for their distributors, but it is used only to note birthdays, acknowledge work anniversaries, and publicize the annual sales convention.
Custom Web Application
To solve these problems, we developed two closely-related solutions for FIMC to meet the unique needs of both suppliers and distributors. In both cases we decided to create a web application that serves as a custom-branded wrapper to distribute reporting and analytics information. One version of the custom web application integrates seamlessly with the security, look, and feel of the supplier web portal; the other version does the same for the distributor intranet site.
For the Supplier solution, we used Microsoft SQL Server Reporting Services as the data visualization and reporting platform. A Reporting Services report that presents a summary of the data using charts and gauges serves as a dashboard landing page. Users can click on a chart or gauge to navigate to detailed reports showing the transaction-level information necessary for decision making. All of this occurs within the custom user interface created by the web application. Up-to-the-minute information is now available to suppliers!
In addition, the use of an existing, mature reporting environment, namely SQL Server Reporting Services, allows FIMC to make changes to the dashboard and existing reports as well as create and deploy new reports within hours of approving a user request. Integrated security ensures that a supplier only sees their own data in the dashboard and reports. The best part? Rights to use Reporting Services are included in the SQL Server license that FIMC already owns.
For the distributor solution, we decided to use Microsoft Power BI as the data analytics platform. Power BI reports were created to present year-to-date numbers, performance vs goals, and year-over-year comparisons. Slicing was implemented in these reports to enable distributors to drill down into their sales networks or drill up to compare their performance with other FIMC distributors or with FIMC as a whole.
The Power BI reports pull data from the existing Microsoft SQL Server Analysis Services data model. This leverages the work already done to create a robust and accurate analysis environment. Any changes made to the definition of business metrics or KPIs is immediately reflected both in internal FIMC analytics and in external communication to their distributors, resulting in more up-to-date information for everyone. Further, row level security implemented in the data model ensures the distributors only see the data to which they have rights.
These Power BI reports are presented within the custom web application using Power BI embedding. All of the interactive analysis capabilities of Power BI are available in the custom environment. Beyond that, the embedding technology allows for enhancements such as carrying over data slicer and filter settings from one Power BI report to another as users perform their analysis. As with SQL Server Reporting Services in the supplier solution, using an established analytics environment like Microsoft Power BI enables FIMC to quickly respond to requests from users for changes and enhancements.
This Power BI embedded solution does require a monthly licensing fee from Microsoft based on usage. However, FIMC has found that the additional sales resulting from better-informed decision making has far exceeded the cost of this solution.
With these solutions, we have ensured that both suppliers and distributors will receive current, relevant information that is easily analyzed for their needs. Even better, these new systems are much less labor-intensive for FIMC, with very little added cost. It’s a win-win on all sides.
Disclosure: Foam Injection Mold Company is a fictional organization created to allow SCS to more fully convey the details of business needs and technical environments related to actual solutions created by SCS without infringing upon the privacy of our clients. All SCS consultants, technical expertise, consulting services provided, and implemented solutions referred to in the Foam Injection Mold Company articles are real! Only the client names and lines of business have been changed to protect the innocent.