How to Save 1 Million Dollars Using SharePoint
Are we the only ones saying it like Dr. Evil?
Are we the only ones saying it like Dr. Evil?
Everyone knows that SharePoint is a good choice for Intranet portals that require data listings, form inputs, and workflow. SharePoint is tightly integrated with Active Directory, so it makes it easy maintain and control within the Enterprise. However, SharePoint is so much more than an Intranet portal product. In this blog, I want to demonstrate how SharePoint 2013 provides a powerful platform to integrate and interpret data from a variety of sources.
For the basis of our example, let’s consider an automobile production line. Key to the success of the line and output is the Just-In-Time (JIT) arrival of auto parts to line stations. What happens if the parts aren’t arriving in time? Simply put, the line fails to produce their shift quota and there is lost production. There is an obvious saturation point where the part is unable to keep up with the line demand. Every hour the production line is down results in a $250,000 loss.
To solve this problem we will use SharePoint 2013, Business Connectivity Services (BCS), and SQL Server Reporting Services (SSRS). The first step in our analysis is to collect key metrics from a proprietary back end Oracle database that stores the parts warehouse data. We will collect ~20 key metrics, including how many times the robot crane moves per hour and how many times parts are moved per minute. We will leverage SSRS to graphical display the data and BCS to access the Oracle database. Our primary IDE is Microsoft Visual Studio with the Business Intelligence Development Studio plug-in.
Step 1: Shared Data Source
Time to setup a shared data source to the Oracle warehouse database. The following screenshot should be familiar as it follows Microsoft’s standard connection screen editor.
In this case, we have called the Shared Data Source BLD.rds as seen here in the solution explorer: (under the project name WarehouseReports).
Step 2: Parameters
Next, let’s create a graph using two parameters of start date and end date. Set the default start date to today minus 31 days and the end data to today plus 1 day.
Here is the default value set for the start date:
Step 3: Datasets and Graphs
We know our cranes are unable to keep up with the demand. So let’s gather the crane data. Here we’ll create a dataset for the graph. We will display time (m_time) on the x axis along the bottom. And then we will display each crane (Crane_i) for each line in the graph.
Now we have all we need to produce the line graph and use the datasets we created with the series as shown below:
Looking at the VS solution explorer one can see that there are a lot of reports!
Step 4: SharePoint
SharePoint is tightly integrated with these SSRS and BCS. It also provides seamless sharing of data between teams. We use SharePoint to give the users the ability to select which graphs they want to view. In our scenario, the manufacturing teams each perform analysis and come to a conclusion. The conclusion is that saturation appears to be related to the number of pallets moved out of the way of others. It is too much for the cranes to handle and maintain the line at the same time..
Pallets are stored on the warehouse aisle shelves, four deep, so if a part is required from the innermost pallet the robot crane has to move the other 3 pallets to new locations thus slowing down the part access time and increasing the number of crane moves per ten minute period. Another graph is surfaced through SharePoint showing the number of robot crane moves per 10 minute period reaches its maximum just before a crash about 1:30 into the shift.
As the manufacturing team continues to analyze the data, they discover the pallets causing the slowdown and carrying parts not required by the production line. There is no extra space for the unneeded parts, so it is decided to designate alternative space for the unused pallets.
As the teams continue to analyze the data and graphs in SharePoint, additional improvements are made. It is estimated that the unexpected shutdown resulted in at least 4 hours lost production time. Using our original per hour loss, our solution has saved nearly $1M. It is easy to see how the power of SSRS and BCS coupled with SharePoint saves time by providing meaningful interpretation of data that is otherwise scattered across an organization. By centralizing this information and providing a common platform to view the data graphically, the business is able to make real time decisions. It’s obvious how powerful SharePoint becomes when used in this manner. SharePoint is indeed much more than an Intranet portal.