Newfoundland Labrador Liquor:
Newfoundland Labrador Liquor Corporation (NLC) is responsible for the importation, sale and distribution of beverage alcohol within the Province of Newfoundland and Labrador.
On November 23, 2017, the Government of Newfoundland and Labrador authorized NLC to regulate the possession, sale and delivery of non-medical cannabis. Included in these new responsibilities was the authority to list products, set pricing and to be the exclusive online retailer of non-medical cannabis in the province.
This new mandate spawned the need to create a unique brand to differentiate the lines of business within the Corporation, which includes NLC Liquor Stores, Liquor Express, Rock Spirits, and now Cannabis NL. NLC developed a Request for Proposals (RFP) with the primary objective of establishing a safe and secure network of licensed cannabis retailers (LCRs). The RFP provided 4 different tiers of service which allowed for flexibility and gave applicants a number of options to consider in determining the best fit for their proposed business model.
Problem Statement:
NLC is in the process of migrating to the Azure cloud environment and are building key data integration solutions using Azure technologies. A key integration point that NLC needs to automate is the upload of Cannabis supplier sales information that is a regulatory requirement for both NLC and their Cannabis suppliers.
Currently, a monthly file upload is provided to NLC via an SFTP server by seven independent Cannabis companies. The processing of the file is fraught with manual processes and follow up conversations with each supplier to get the correct data in the required format to be able to be consumed by the NLC systems.
As part of their business model, Bridgeworks partners with a key consulting partners to deliver jointly deliver work where there is a client need or when requested by our implementation partners. In the case of NLC, Sensible Data Integrations (SDI) required assistance with cloud-based Azure solution implementations for their client, NLC. As such, Bridgeworks and Sensible Data Integrations partnered on the following Azure solutions.
Engaging:
Newfoundland Labrador Liquor Corporation (NLC) needed an implementation partner to build a web site that allows Cannabis supply companies to register and log into the site to upload their sales and inventory data in an automated fashion. NLC needed the web site to be fully-hosted outside of their current infrastructure and built holistically leveraging Azure technologies. The solution needed to satisfy the following key requirements:
- The online website needed to maintain a listing of users and securely validate them prior to allowing them to upload information to the site.
- The cloud-based technology is required to be hosted in Canada as close to the NLC headquarters as possible.
- The data upload had to be user-friendly and support file validation prior to upload to NLC systems.
- If the data upload is found not to pass key validation checks, the user gets an error message that tells that what needs to be corrected in order to re-upload their file.
- The data uploaded needed to be stored in a secure Azure data store.
- The process would post all files that pass validation into Azure Blob storage and then write the data within the file to an NLC SQL Azure database.
- An Application Interface (API) would need to be provided through the web site that would allow Cannabis suppliers to automatically integration and send data to NLC via a custom integration.
- The API needs to securely validate which Cannabis supplier is leveraging the API and audit which user, when the data is uploaded and whether the data upload passed audit checks or not.
- Failures to API audit checks would be sent back to the Cannabis supplier application leveraging the solution for data upload.
- The entire solution needed to be hosted by the NLC technology partners until such time they are able to take over ongoing maintenance and solutions within the Azure environment.
The Approach:
Both SDI and Bridgeworks resources worked with the NLC technical team to gather sample sales spreadsheets, further define validation requirements and design a solution that aligned with the NLC stated requirements. Bridgeworks and SDI then engaged in an iterative implementation cycle to build and demo the solution to NLC key stakeholders and sponsors as the solution build phase progressed. As a result, NLC resources had full visibility, on a weekly basis, into the implementation process throughout the engagement yielding a solution that fully aligned with their needs.
The Solution:
Given Azure solutions were required to satisfy the NLC requirements, the SDI and Bridgeworks team first designed which Azure solution components would be necessary. As a result of the design, the following solution components were chosen to satisfy the NLC needs:
- Azure B2C Active Directory solution for authentication and access to online solutions
- Custom web-based open source application to support file uploads and data structure validations
- Blob Storage for web site content and uploaded sales files and JSON submissions through the web API
- SQL Azure databases for all back-end data validation and storage
During implementation, SDI and Bridgeworks worked jointly to implement and demo the key components to NLC resources on a weekly basis. They team gathered feedback and quickly worked through updates and new development to closely align with NLC solution needs.
The finalized implementation provided the following capabilities:
- A fully hosted Azure solution running on the joint Bridgeworks/SDI integration platform.
- A web-based login leveraging Azure’s B2C Active Directory solution.
- A data upload information page that provides the schedule of updates required by Cannabis Suppliers and templates necessary to integrate with the web API or upload files to the site.
- A data upload page capable of allowing Cannabis Suppliers to upload their sales update using a pre-defined template.
- An API page that allows Cannabis Suppliers to generate a secure integration key to leverage for automated data integrations and information on how to use the web API programmatically to submit data updates.
- Blob storage solutions that can be updated to change the web site content as well as store a fully auditable history of data submitted to the site.
- Azure SQL solution to store the sales data submitted by Cannabis Suppliers.
Bridgeworks and SDI offered NLC and their customers a full set of services around data integration, web site and platform hosting that allowed NLC to automate a painful manual process. The Cannabis suppliers are now able to submit their quarterly sales data updates without involvement from NLC technical resources, eliminating the costly, labor intensive and manual work previously required by NLC each quarter for each Cannabis Supplier.
In addition, Bridgeworks was able to provide a service that helped NLC customers take advantage of the advanced integration capabilities not previously available through the implemented web API.
Bridgeworks and SDI were able to quickly stand up a web-based Azure solution which greatly increased the speed NLC was able to have a web site available to their Cannabis Suppliers. This assisted the NLC in proving the viability, value and affordability of Azure as a future state architecture direction.
In providing hosting services for the solution, Bridgeworks and SDI were able to provide the NLC with could-based platform capabilities and support they would otherwise not have access to. This approach also allowed the NLC to try the Azure environment without risking the need to fully re-tool their technical support staff in addition to implementing a new solution to their supplier partners.
