12
$\begingroup$

I am trying to figure out how to use Mathematica as a real-time interface.

I've used Excel/ODBC connection to a SQL database to use the graphing functions within to get my real-time dashboard. I also know there are many Python implementations (http://dashing.io/), Javascript (d3), Ruby() on watching data. I'm currently not looking to do this at a big data implementation.

The first piece I am looking for to do is have an updated top 10 SKU Sales that are over 30 locations that are synced back to a SQL Server Database. My thought was to use a simple SQLExecute statement to the database every 5 minutes or so. Currently, I am trying to figure out how to reissue that command to the database to get my new numbers and automatically update the table.

The second piece is to watch transaction flow at each of these locations. Here I just simply want to graph by the hour which I'd update an array with the data. I just don't know how to trigger the data execution.

After some searching online, I haven't found anyone who has done this with Mathematica. Does anyone have links/examples where a dashboard is implemented into Mathematica?

Improve this question
$\endgroup$
6
  • 2
    $\begingroup$ Please give us more details regarding what you are trying to accomplish. Where is the data? What kind of metrics are you looking for? How do you want to display them? $\endgroup$
    – mfvonh
    Commented Jul 17, 2014 at 17:32
  • $\begingroup$ Is that more understandable? I've added my example in the question above. $\endgroup$
    – C Ried
    Commented Jul 17, 2014 at 17:58
  • 2
    $\begingroup$ Yep. The more specific you can be the more help people will be able to provide. You can use RunScheduledTask and its ilk to periodically query the server. You could store the results in global symbols and use Dynamic expressions for the SKU list and transaction flow graph, and they would then update each time the server is queried. Depending on what you want you could attach these to the notebook with DockedCells or create a palette. $\endgroup$
    – mfvonh
    Commented Jul 17, 2014 at 18:07
  • $\begingroup$ A bare bones version of what you want is not too difficult. But the devil is in the detail. Is this a hobby or a consulting job? A full description, beyond the tips and pointers and relevant code fragments, of how to do this is beyond the scope of this site IMO. $\endgroup$
    – Mike Honeychurch
    Commented Jul 17, 2014 at 22:33
  • $\begingroup$ I suppose I was looking for barebones which i will post tomorrow when I can answer my own question. I just needed a pointer to getting to the RunScheduledTask.. $\endgroup$
    – C Ried
    Commented Jul 17, 2014 at 22:37

1 Answer 1

Reset to default
7
$\begingroup$

So thanks to @mfvonb I was able to get a start building a preliminary form to create such a dashboard:

Mathematica by default needs to load the database functionality into memory (like any header file) in order to function. 

Needs["DatabaseLink`"];

Creates the connection to the database from which the data can be pulled.

conn = OpenSQLConnection["XXX"];

Following is where all the magic happens, it creates a scheduled task by which the query runs every 30 seconds to pull information which it assigns to the bass variable. 

RunScheduledTask[bass = SQLExecute[conn, "  
    SELECT DATEPART( HOUR,CAST(a.Invoice_Date as time)),  
SUM(b.Price_As_Sold_QTY_Total) as NetSales
     FROM Invoice_Header as a INNER JOIN Invoice_Detail 
as b ON a.Invoice_Header_ID = b.POS_Invoice_Header_ID
     INNER JOIN Inventory as c ON b.SKU_ID = c.SKU_ID 
     INNER JOIN Deals as d ON c.Deal_ID = d.Deal_ID
     WHERE a.Invoice_Date >= CAST(GETDATE() as date) 
     GROUP BY DATEPART( HOUR,CAST(a.Date as time))
    "], 30];

And lastly, a list plot will continually update as new data has been pulled. 

Dynamic[ListPlot[bass, PlotTheme -> "Scientific", Filling -> Axis,
Joined -> True, PlotLabel -> "Day Sales (updated 30 sec)"]]

And that is it, it will continue to update the graph in real-time.

a simple output

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.