Top 10 Ways SQL Server R Services Helps to Optimize Supply Chain Operations

Page 1 | Top 10 ways SQL Server R Services helps to optimize supply chain operations

Top 10 ways SQLServerRServices helps to optimize supply chain operations

Consideringthe increasing pressure that manufacturers, retailers, and suppliers face to deliver products on time, it’s crucial to understand and predict factors that affect customer satisfaction, costs, and inventory.

To learn how Microsoft SQLServer2016RServices helps optimize supply chain operations, the field strategy and operations team in the Cloud + Enterprise group at Microsoft reached out toCore Services Engineering (CSE, formerly Microsoft IT). InCSE, we’ve been exploringa broad range of opportunitiesfor enhancing supply chain management.

Our goals include applying answers to strategic questions like these:

• How can we tap into the wealth of data inside and outside Microsoft, for better real-time decisions and more accurate predictions that are related to the supply chain?
• How can we apply SQLServerRServices to data platforms that are the backbone of manufacturing operations?
• How can we get insights to improve the business value and processes in supply chain management at Microsoft?

• How can we use SQLServerRServices to improve customer service levels and reduce costs?

R is a popular open-source programming language for statistical data analysis and is at the heart of RServices. It is a key feature in SQLServer2016 that supports enterprise-scale data science across businesses and industries.

By usingSQLServerRServices for supply chain operations, we can optimize inventory, do predictive maintenance, proactively monitor service levels, predict products that are more likely to be returned, identify environmental factors that might affect operations, and much more.

How this exploration got started

The inspiration for this work stemmed from excitement over investments that the company is making in R across the product portfolio—in particular,SQLServerRServices. And the Microsoft companywide hackathon—where employees tackle challenges in projects where they apply Microsoft technology—was the perfect time to try out RServices on a business problem in the supply chain.

Who uses SQLServerRServices and for what?

Typically, data scientists use R for data exploration and for building predictive models, which use statistics to predict outcomes. They test and tune until they develop an optimal model. RServices (In-Database) client components provide the needed tools for experimenting and developing, with a database service that runs outside the SQLServer process and that communicates with the R runtime.

Data scientists who are testing and developing solutions can communicate with the server from a remote development computer to run R code on the server, and can deploy completed solutions to SQLServer by embedding calls to R in stored procedures. It’s possible to train R models, generate R plots, perform scoring, and easily move data between R and SQLServer.

Top 10 ways SQLServerRServices optimizes supply chain operations

InCSE, our primary use for data in the supply chain is to optimize operations—to improve customer service levels andreduce costs. We can use RServices fordata that’s in our SQLServer data warehouse to tackle these and other challenges and scenarios, like these top 10 examples:

• Real-time weather data. For example, you can pull in real-time weather data from tweets from the National Weather Service. And then you can overlay that data with your supply chain operations to see whether a specific tornado or earthquake will affect your operations.
• Linear programming. R supports linear programming—a mathematical technique that determines the best outcome (like maximum profit or minimum cost). It’s widely used in supply chain optimization for inventory, strategy, logistics, cost optimization, and other scenarios.
• Geospatial data. Often, a supply chain's data warehouse doesn't have geographic data in it. So you have addresses, but you don't necessarily have the longitude and latitude to understand exactly where things are positioned in space. You can use R to get the geospatial data, and incorporate it into your data warehouse. You can have much deeper analytics across several scenarios.
• Text analytics. For example, if you have feedback from your customers in your database, you can do root-cause analysis on why they're returning products.
• An ensemble of forecasting models.You can use a broad range of forecasting models together in a group—commonly referred to as an ensemble. This way, you can create more accurate forecasts—whether that's demand forecasting or supply forecasting—than you’d have with an individual model that’s working in isolation.
• Proactive monitoring of service levels. Imagine you have a warehouse that’s packing products and shipping them to customers. You have a daily goal for how much product you're supposed to ship. You could implement a process that uses RServices to constantly collect information from that warehouse and forecast whether you’ll meet that demand. And if the target isn’t being met, you can alert a supply chain manager, who cancommunicate with someone in the warehouse tolearn what's happening from an operational perspective.
• Association rule mining. In the warehouse, a very common process is to pick a product, pack it, and ship it. If products that people often buy together are close together in the warehouse, the process is more efficient. With RServices, you can use association rule mining to determine products that are most commonly bought together.
• Inventory optimization. In addition to linear programming, another technique you can use with RServices is clustering. You can look at your patterns of demand and shipping, start clustering, and determine if you should change your inventory mix or inventory strategy based on the actual behaviors that you see in the supply chain.
• Predictive reverse supply chain models.Reverse supply chain, or reverse logistics, involves any process or management that takes place after a product is sold or delivered, such as when a customer returns a product. You can build and train machine learning models to predict—based on customer, product, geography, and channels—what purchases are likely to be returned. You can understand what those behaviors are, and be more proactive to prevent future returns on purchases.

• Predictive maintenance models. If your company builds products that are costly or difficult to service in the field, predictive maintenance models are very useful. Previously, you might have waited until something broke down before fixing it. But, you can be more proactive. You can use machine learning to create a model and—based on product information—send someone to do maintenance on it, before issues arise or escalate.

Bringing these capabilities into production

We actively identify opportunities that give our partners insight.Because of what some of our business partners saw in our hackathon project, theybuilt and put certain capabilities into production. Oneexample is collecting data about the weather, earthquakes, tornadoes, or other environmental factors that can disrupt the supply chain.

Another example is mining social media in near real time for other disruptions that might affectthe supply chain. Suppose a strike is imminent or there's political upheaval. With RServices, these partners can map data to the global footprint of the supply chain where factories and shipping lanes are located and where the products are going. And they can examine whether any of these potential disruptions are close to supply chain operations.

What we learned

Some of the things we’ve learned and practices that we’re incorporating include:

• Accepting that we’re starting with hypotheses.Like a scientist, when you're working with data, you postulate a theory or hypothesis. But you have to validate your assumptions. And if everything lines up, you need to create a model that reflects those hypotheses and theories. That's exactly what we, as data scientists, are doing.The hypotheses of data scientists are just tests, and—similar to scientists—we expect that our tests will sometimesfail. The point is to validate our assumptions, and—if the assumptions are proven wrong—adjust course.
• Separating our role in CSE from the role of production teams.We seek opportunities for insight that improve supply chain operations, enhance customer service levels, and cut costs. However, the production teamscarry out the actual implementation—not us. We want to study and expose possibilities for these teams.
• The data warehouse for the supply chain is a key asset.Because we had access to architected, curated data in SQLServer, we concentratedour efforts on adding business value with RServices rather than on managing data.
• Using R to get geospatial data and ArcGIS mapping in Microsoft PowerBI for visualization. ArcGIS is a geographic information system (GIS) that’s used for creating and using maps, compiling geographic data, analyzing mapped information, sharing and discovering geographic information, and managing geographic information in a database. With ArcGIS mapping in PowerBI, we can use R to get geospatial data in a data warehouse and create visualizations that very efficiently communicate to supply chain managers what's going on.
• Mining Twitter data. The natural inclination is to think of this as a big data problem. But we found that the Twitter data that we were mining for the supply chain wasn’t very big. SQLServer RServices scaled to that level of data without any issues.

• Ongoing feedback for quick iterations and continuous improvements. Lastly, we learned a lot about working with a product and quickly iterating on it. We collected feedback and gave it to the product team, so that they can make continual improvements that benefit our customers outside Microsoft.

Looking ahead

We have another exciting data experiment on the horizon. For online retail deliveries in North America, we hypothesize that there may be behavior patterns—especially around the busy holiday season—where we can apply the clustering capabilities of R to our data. We’ll explore ways to optimize our inventory strategy, so that we further improve customer service levels and reduce costs.

For more information

Microsoft IT Showcase

microsoft.com/ITShowcase

Video: Supply chain management with R Services

SQL Server R Services

IT Showcase data analytics content

©2017 Microsoft Corporation. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

IT Showcase Article

microsoft.com/ITShowcaseJanuary 2017