In support of a $2.5B business, the California Strawberry Commission has numerous constituents, such as growers, shippers and retailers, all focused on keeping the California berry and fruit market growing and efficient. As part of this effort, the California Strawberry Commission needs to ensure that all constituents are provided up-to-date market data such as pricing and volume movement.
Additionally, the growers and shippers are increasingly tech-savvy, requiring this data regardless of location, including in the fields with their crews, using many different mobile device platforms and device display sizes.
Finally, the commission needed a solution that would allow them to import data daily from the USDA so that these constituents would have the absolute most up-to-date berry market information.
The California Strawberry Commission engaged Stratus Innovations Group as a follow-up from their work with the commission on Office 365 and System Center to develop and implement a plan to enhance and extend their current Business Intelligence solution. The solution that was deployed including a Microsoft Azure Windows Virtual Machine running SQL Server to act as the staging server, that fed a SQL Azure database with several SQL Server Reporting Services (SSRS) reports (Daily Report, District Report and National Berry Report.
The SQL staging process involved an automated ETL (Extract, Transform and Load) process where the commission would simply export volume and pricing Excel spreadsheets from the USDA site and drop them onto a file share on the Microsoft Azure Virtual Machine running SQL Server. This ETL process then incorporates various business rules and business logic scripts to import this data into the SQL Azure environment, which runs the SSRS reports.
Finally, we developed an application that allowed the commission to directly edit the SQL Azure production data tables to address any errors or omissions from the USDA data imports.
The primary benefit to the California Strawberry Commission is the ability to quickly and easily import the latest USDA market data into their SSRS reports so that market constituents have the latest and most accurate data to inform their planting, shipping and marketing business decisions. Once the commission uploads the exported USDA spreadsheets, this data is reflected in the production SSRS reports within minutes.
Additionally, since this reporting solution is hosted in the Microsoft Azure environment, the commission’s constituents only need internet connectivity via a cellular network, Wi-Fi or their corporate network in order to run these reports and get this real-time data regardless of their location or computing device.
Lastly, the commission can now update in real-time the production SQL Azure database tables with the ability to correct any current day errors or typos that may come from the USDA, which ensures that reports do not have any erroneous or invalid data.