Fulfillment Company Consolidates Siloed Data Sources for Advanced Reporting Dashboard
Overview
A 20-year-old fulfillment services company wanted to integrate three disparate data sources into a single reporting view that would allow the CFO to quickly and efficiently determine key performance indicators. The fast-growing company provides inventory warehousing, kitting and assembly, customized packaging, shipment, and package return services for a wide variety of clients across the United States.
In its day-to-day operations, the fulfillment firm must manage an inordinate volume of data. Prior to working with Softensity, the company had unsuccessfully engaged with other consultancies that were unable to deliver on the advanced reporting requirements.
Challenge
The company was challenged with consolidating data from QuickBooks, Deposco and InOrder within Excel, which made it difficult to create meaningful reports in a timely manner. Pulling reports from these siloed data sources was an inefficient process that failed to provide actionable insight in real time.
This created a lack of visibility into what was going on in numerous warehouses across the country, where 200 employees work. It was impossible to get a real-time snapshot of data points on demand. At the same time, the company was migrating their clients from a legacy system to Deposco, which was a cumbersome manual process due to the various sources of data.
The Solution
The company had an idea to use Power BI to consolidate the data into an advanced reporting dashboard, and turned to Softensity for expert guidance and assistance with implementation. The engagement began with an evaluation of the company’s needs and an in-depth analysis to determine that Power BI was the best solution.
Softensity’s Project Manager and Data Engineer / Power BI expert worked in close collaboration with the company’s CFO and Chief Architect / Senior Programmer Analyst to ensure that all of the company’s reporting needs were met. The team used Power BI and Azure Data Factory in an Azure SQL database environment to create a data schema that brought all of the disparate data sources into a single comprehensive report.
This involved creating a unique key between all of the different data sources, and a special rules table that made it simple to add new companies or owners in a semi-automated process. These features made it much more efficient for the company to migrate clients to Deposco.
The Results
The consolidated reporting dashboard provides real-time business insights that can fuel data-driven decisions about profit and loss, efficiency and profitability. The tool includes five separate tabs, each representing a single dashboard with required KPIs, grids and timeline trends that provide detailed analysis on fulfillment, freight and revenues / expenses.
The company is now able to look at real-time data for any client on any sales channel from a single dashboard. All reports can be filtered to produce dynamic data ranging from gross profit and fulfillment margins to employee productivity and the average minutes spent per order. Users can create special rules along with graphics, charting and more. Reports are dynamically updated every day and provide the latest information in real time.
Upon completion of the four-month engagement, the fulfillment company retained Softensity’s well-respected data engineer to continue to develop the dashboard and add additional capabilities.