cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Creating an Excel model for use with ThingWorx Analytics Manager

Level 7

Creating an Excel model for use with ThingWorx Analytics Manager

One of the interesting features of ThingWorx Analytics Manager is its ability to run distributed models created in Excel (and more of course).  Most people having been tasked with understanding data have built models in Excel and have sometimes built quite complex models (or even applications) with it.

 

The ability to tie these models to real data coming from various systems connected through ThingWorx and operationalise their execution is a really simple way for people to leverage their existing work and I.P. on a connected analytics journey.

 

To demonstrate this power and ease of implementation, I created a sample data set with historical data, traffic profile, and a simple anomaly detection model to execute with Analytics Manager.  (files are attached)

 

The online help center was quite helpful in explaining the process of Creating the Excel Workbook, however I got stuck at the XML mapping stage.  The Analytics and Excel documentation both neglect to mention one important detail -- you must be using the Windows version of Excel in order to get the XML Source functionality (and I use Mac).  Once using Windows, it was easy to do - here is a video of the XML mapping part of the process (for the inputs and results)

 

This short video shows how to configure an Excel spreadsheet for use with input and output parameters with ThignWorx Analytics Manager, as described in the online help ...
2 REPLIES 2

Re: Creating an Excel model for use with ThingWorx Analytics Manager

Hi ,

 

Could you please explain why did you selected following formulas in excel sheet -->HourlyTravellerStatistics

In Cell Z4: 
<<=MonthlyTicketSalesByDate!N18>>
In Cell Z6:
<<=MonthlyTicketSalesByDate!N19>>

Why cell N18 and N19 are specifically chosen here?

 

Thanks

Tushar

 

Re: Creating an Excel model for use with ThingWorx Analytics Manager

Hi Tusar,

 

I built the traffic model backwards, starting from the statistical data found on the last sheet.  I then made some estimates to how I felt that this could extrapolate to monthly and daily variance, and then some estimates about the daily rider profiles based on those daily totals and peak hours and metro operating hours.  Note that there are a lot of estimates, but I wanted to build a model based on real data which could be used for a number of uses (hourly, daily, monthly statistical lookup and comparison).

 

The figures that you are referring to are a simplification that I did to make the daily rider profile.  It takes those cells as baseline weekday and weekend total tickets sold (MonthlyTicketSalesByDate!N18 = Weekday ticket sale total at station, and MonthlyTicketSalesByDate!N19 = Weekend ticket sales at station).  Those totals, and thus the rider profile does not evolve over the months/days as it could if you worked down the table in MonthlyTicketSalesByDate.  I didn't do that to keep it simple and as it wasn't needed.

 

Cheers,

 

Greg