Graphing a Time Series in AdventureWorks With DateListPlot – Part I

February 19, 2011

Filed under Mathematica, SQL Server
Tags: Graph, Math, Mathematica, Microsoft SQL Server, Wolfram Research

one response

Graphing out data from SQL Server in Mathematica is pretty easy to do. Mathematica has a function called DateListPlot[] that will accept a data set and graph it. All one has to do is pass the data to DateListPlot in a way that it expects; that is, date first and value second.

Before we begin, make sure you’re connected to SQL Server. AdventureWorks2008R2 has a perfect candidate table for using DateListPlot and it’s one that is relatable to real world situations: Sales.SalesPersonQuotaHistory.

DateListPlot will want a datetime value and a scalar value; in this case, SalesQuota. The query we will use to drive the graph will then just be, “SELECT QuotaDate, SalesQuota FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 274″.

Pick any valid BusinessEntityID for your WHERE clause, I just picked the first one I saw. In Mathematica, make sure you include the appropriate package import and database connection details:

Then use SQLExecute to execute the query like so:

Let’s try graphing this. We’ll just use DateListPlot[query, Joined -> True] since we want lines between our points. Unfortunately, Mathematica doesn’t like what we tried to do:

It honks at us because it doesn’t understand the SQLDateTime[] function as a valid value. So an easy way to fix that is to use the Identity[] function in Mathematica (I found the answer to resolving this on StackOverflow). So we’ll fix the issue by applying the Identity function to SQLDateTime by using ReplaceAll (/.). The new function will look like the following:

This will be much easier to graph, all we need to do now is use the same function with the same parameters as before now that the input is palatable to Mathematica:

To export the graph as an image to be used in a report, just input the following into Mathematica and run it:

You should find your image located in your default documents folder, ready for use. Be sure to check out part II where we graph multiple time series!

Comments Feed