Case Study: Leveraging Microsoft Power Platform to Meet Federal Regulatory Requirements

A large food company had to meet several federal regulatory requirements for tracking refrigeration cylinders. To do so, they needed to be able to track and manage their inventory across multiple geographic locations and on their fleet of ships. The client approached our Protiviti On Demand Team to create a custom application that would accommodate their specific needs while staying within their current Microsoft 365 licensing.

Comprehensive solution

After discovery, our Protiviti team recommended utilizing the tools already available through the client’s current Microsoft 365 license and designed and developed a comprehensive solution that included:

  • Microsoft SharePoint Online was used to interface with the data and as a repository for the inventory and transactions data.
  • Microsoft PowerApps was leveraged to create a customized form to collect the data needed for different product transactions.
  • Microsoft Power Automate was used to handle the logic of each transaction and appropriately update the refrigerant inventory.
  • Microsoft Power BI was used to tie all the data together in visually appealing reports and dashboards, gaining insights into the inventory and transactions built using Power BI.

Automating the transaction process

A key goal of this initiative was to allow users within the organization to easily and efficiently record and track changes to the refrigerant inventory for their specific location. The final design was a web-based form, built in PowerApps, that could be accessed at any location with internet access. Refrigerant transactions are captured from the form, accounting for the acquisition, transfer and disposition of each container. A SharePoint list stores data on each transaction and PowerApps customizes the list to create an interactive form.

From a landing page, users select the type of transaction being performed. Depending on the type of transaction selected, different fields are shown on the form.

Adding inventory to a location

As inventory is added at a location, the form allows for entry of information for each container.

Purchase transactions

Non-purchase acquisition transaction

Transferring containers from a location

For transactions dealing with containers already in the location’s inventory (such as transferring a container), the appropriate containers are selected from a filtered display of containers that are registered to that location. To accomplish this, the Protiviti team took advantage of PowerApp’s ability to connect to other lists as data sources, create dynamic collections and display individual form controls for each container using galleries.

Logging the transactions

Once the refrigerant transactions are entered into SharePoint, Power Automate takes the stage. The designed flow processes each transaction separately to update the inventory in the SharePoint list. Depending on the type of transaction, the flow logic updates the inventory for the location specified in the transaction. As a result, the inventory for that location increases or decreases. The condition and contents of the containers identified in the transaction may also be updated from the form.

Snapshot of Power Automate flow

Once the flow is complete, the SharePoint inventory list will have an accurate accounting of the refrigerant for each location. Now, we can analyze the data with Power BI.

Reporting with Power BI

Data sources

Using Power BI, we were able to report on the data captured in the process described above. Power BI is a great visualization tool that can bring together many data sources including SharePoint lists. In this case, we focused on two lists – location lookup and inventory tracking. Location lookup serves as a dimension table while inventory tracking is the heart of on the type of information the client wanted to gather.

There were a few key metrics that were important to the business owners, such as the number of containers, the weight of containers and number of containers in transit. The number and weight of containers are based on outside filters like location or location type. However, the in-transit number would always stay the same regardless of filters. This was accomplished by creating measures in our data model to include/exclude certain filters.

Reviewing data

One of the many benefits of Power BI is its ability to slice and dice data. We can quickly see the total number of containers by location. If we were more interested in a particular location type, once selected, the rest of our data is shown as a mixture of filtered and highlighted.

Location details

Power BI integrates seamlessly with Bing Maps since they are both Microsoft products. Business users can see a high-level view of locations and drill into a specific location to view more details. This feature works on a variety of location data and does not require specific latitude and longitude.

Location-based reports

While the first page is primarily focused on actions, the second report focuses more on location. We presented the users with an option to immediately drill down to a specific location. This page also shows some key metrics for a particular location such as total inventory, contaminated inventory and empty containers.

The interactions between visualizations makes Power BI so unique compared to previous reporting tools. In the example below, we can choose to look at container type, contents or action and see the other two filtered.

Valuable solution

Thanks to this new Power Platform solution, our client will have a more structured process in place, allowing them to easily input inventory changes and then track and report on that inventory at any given point, allowing them to meet a pivotal goal in achieving federal regulatory compliance. The new dashboard reporting will allow them to visualize the container locations and provides valuable insights from a high-level perspective. And, they will be able to reduce manual workload and increase the accuracy and accessibility of the data for this process.

To learn more, check out these resources:

To learn more about our Microsoft consulting solutions, contact us.

Becca Ratliff

Software Services

Tim Hare

Software Services

Joshua Jackson

Software Services

Subscribe to Topics

Protiviti’s Sharon Stufflebeme and Ramesh Gupta share advice in @InformationWeek with organizations looking to update #LegacySystems and adopt the right amount of #EmergingTechnology to balance business needs. #ProtivitiTech

We understand the challenges organizations face regarding #DataManagement and security. A structured data protection approach centered around people, processes and technology can help you tackle those challenges. Learn more: #ProtivitiTech #Data

What is the #Metaverse? What does it mean for business? And how should companies prepare? @Protiviti’s Kim Bozzella tells @Forbes why now is the right time for businesses to leverage this immersive technology. #ProtivitiTech

Is your organization post-quantum ready? Join Host @KonstantHacker for a chat with Skip Norton of @QuintessenceLab about real products available today that will be ready for post-quantum #encryption by 2024. #QuantumComputing #ProtivitiTech

Maximize the value of your organization's #Data by building a modern enterprise #DataArchitecture. Find out how to get started with Protiviti's latest whitepaper: #ProtivitiTech

Load More