Visualizing AdventureWorks Data with Mathematica Using BarChart
February 12, 2011
Filed under Mathematica, SQL Server
Tags: Bar chart, graphs, Mathematica, SQL
In this post, we’re going to build a basic bar chart that extracts data from a simple query in SQL Server. To build a bar chart in Mathematica, the function we would use is BarChart[]. For the purposes of this example, we’re going to pull a basic select query from the AdventureWorks database and graph it. The target table for this example is one that has easy to graph data: Sales.SalesTerritory.
Great. We don’t want all that stuff. Just Name and SalesLastYear. So when we build our query in Mathematica, we’ll just use, “SELECT Name, SalesLastYear FROM Sales.SalesTerritory”.
As described in a previous post, make sure to import DatabaseLink and set up your database connection to SQL Server.
Next, build the query as described above using the SQLExecute function like so:
Once the results from the database have been returned, we can pass them to the BarChart[] function.
If one looks at the documentation for BarChart[], this example does not appear to follow the same rules at first glance. One of those reasons is that we’ve created lists within the function. The first parameter that BarChart[] expects is a list of values. The table created by SQLExecute[conn, "SELECT Name, SalesLastYear FROM Sales.SalesTerritory"] is actually a table of tables. The pertinent value for the list to graph is the numerical value of each of the sub-tables within the main table. To extract this information, one must reference the table (we called the table, “query”) and then address the part of the table by using Part[]. We’re embedding Part[] within another function called Table[] which will build us a table which will be used to graph the values. What we’re telling Table[] to do here is build a table that’s the length of the list created by our SQL query and for each value i from 1 to Length[query] (which is 10), take the second value from the subtable and make it the value in the ith position of the table. That table creates the numerical values to be graphed. To demonstrate the point, we can run the Mathematica statement by itself to see the table created:
If one scrolls up and looks at the SQL statement we executed, those values will match. Graphing the numerical values of the query won’t give a person an idea of what they’re looking at without labels. The table returned by SQLExecute[] gave us precisely the set of labels needed for the graph. Using the same principle used for creating the values to be graphed, we pass to the option ChartLabels in BarChart[] a list of values created by extracting the first value of each subtable in the table returned by our query. Mimicking what we did with the sales, we can see what the table we pass to the ChartLabels option looks like:
The end result is a clean looking bar chart with labels. Furthermore, since we’re not hard coding anything such as the length of the table, when rows are inserted into Sales.SalesTerritory, the graph won’t break.
