In the previous section we illustrated the use of the PI integrator for business analytics to
publish data into Power BI for analysis. In this section we will illustrate how PI
Datalink and Excel can also be used to analyze our dryer data set and how we can use basic
Excel functionality to generate a linear model to predict regenerating times for each dryer,
which we can use for our simple predictive model.
At this point in the analytics project it's important to get assistance from subject matter
experts or SMEs who understand the process we are modeling to ensure that we end up with helpful
and meaningful results. Our subject matter expert in this case has indicated that the
drying cycle barrels variable that we have, which corresponds to the number of barrels dried in the
previous drying cycle, will be the best one to predict proceeding regeneration cycle time. For
a brief discussion and explanation on why we decided on drying cycle barrels as a predictive
variable to inform regeneration time see the appendix section in the workbook, section 7.1.
On the desktop open up the Excel file titled PI world dryer regen duration. There are three
sheets in the PI world dryer regen duration file the first sheet titled dryer A has been
built out for you. Since we've chosen drying cycle barrels as our dependent variable for
our linear prediction model we are focusing on that variable here. The event frame search has
brought in the duration and drying cycle barrels data for the entire year of 2019. We've also
filtered out any event frames with a prior drying cycle duration of less than 10 hours.
You can see how this can be done in the event frame search in the next step. The purpose of
this filter is to remove any false starts in the data when a drying cycle is shorter than
typical. This would result in a skewed prediction and we will account for this in our regeneration
prediction forecast. This sheet plots the regeneration cycle duration as a function
of the drying cycle barrels variable. The number of barrels the dryer has processed in the previous
drying cycle. The scatter chart on the right has a linear best fit line indicating the relationship
that the drying cycle barrels variable has on the regeneration cycle time. We will be using this
equation of the derived linear regression line as our model to predict regeneration cycle duration.
It is important to note that this is clearly a rather simple predictive model that could
be more advanced. But for the purposes of this course we are focusing on the
methodology of starting analytics and data science projects with the common tools at
our disposal to simply get us started and familiarized with the basics. The second
sheet titled my dryer B has not yet been built out for you. To give you experience
importing event frame data into Excel using PI Datalink follow the steps below to build out
the same charts as dryer A and to develop our linear regression model for dryer B.
If you'd prefer to use a pre-built sheet for dryer B it is the last sheet in the
workbook titled dryer B. First off let's bring in all the event frame data for the
year 2019 using PI Datalink. Make sure you're in the PI Datalink ribbon add-in
to Excel. Click on the explore events function under the events tool section of PI Datalink.
Enter in the following parameters for your event frame search such that it looks like
the one on the screen. For the database be sure you're connected to the dryer's
database. For the search start enter January 1st 2019 and for the search end
enter December 31st 2019. Enter the event frame template as the dryer regeneration
cycle template. Filter your search to only bring in event frames for dryer B,
add a filter to only bring in event frames where the drying cycle duration is greater than 10. To
do this expand the more search options window and enter an attribute value filter such that
the dryer regeneration cycle duration is greater than 10. Finally, select the columns to display.
We want to bring in the event name, the drying cycle barrels variable, as well as the duration.
Next, add a column to the end of the data set, in our case it should be
column D. The goal of this new column is to convert the duration into hours. To do this
simply multiply the imported duration by 24 and change the format of the cells in
that column to be of type number with two decimal places. Once the event frame data
is brought into the dryer B sheet we can now use Excel's charting functionality
to plot the regeneration cycle duration as a function of each variable. Within Excel click
on the insert ribbon at the top and choose the scatterplot, right click on the scatterplot and
click select data. On the Select data window the range of values for duration will be the
dependent y-axis on this chart and the drying cycle barrels variable will be on the x-axis.
On the chart click to add a chart element of a linear trend line. Format the trend
line to display equation on chart and to display r-squared value on chart.
Once complete you should have a chart that looks like the one on the screen.
We now have a linear regression model for each dryer using the variable of drying
cycle barrels or the number of barrels dried during the proceeding drying cycle.
We will use this linear regression model to
predict the proceeding regeneration cycle duration in the next section.