A short tale of… data vizes. It’s about Tableau, R, and Shiny. How to prepare/build an existing and well-known Tableau visualization with R and provide web analysis interactivity. Will be using the raw data from the sample workbook named “Regional” that comes with Tableau Desktop. The following image below is a snapshot of the visualization that we will try to reproduce with R. You can also view and interact with this visualization on my Tableau Public profile page -> http://public.tableau.com/profile/stephanefrechette#!/vizhome/Regional_65/College


You can download a copy of the dataset in CSV format available here on Dropbox

Let’s start by recreating the same visualization (static) with R. Open RStudio, copy and paste the following R code and run it.

The output will produce the following, a nice simple static plot… of course with no interactivity!

Then comes Shiny by RStudio which is a web application framework for R that turns your analyses into interactive web applications. Check out the Getting Started Guide for all the details.

Shiny apps have two important components:

  • a user-interface script (ui.R) which controls the layout and appearance of your app
  • a server script (server.R) which contains the instructions needed to build the app
  • You can actually run the app locally in RStudio by copying the following 2 R scripts

    I have published this app on Shiny for you to explore and interact with https://sfrechette.shinyapps.io/college_admissions


    So from Tableau to R and Shiny we have *almost the same data visualization and interactivity
    *I am using the ggvis package and it seems impossible to add a horizontal line to a plot for the average score… feature apparently not available yet!

    Back to the future – Temporal Tables in SQL Server 2016 a small presentation given at the Ottawa SQL Server User Group (Ottawa PASS Chapter) on Thursday September 17, 2015.

    SQL Server 2016 CTP2 introduced support for temporal tables as a database feature that provides built-in support for provide information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

    Topics covered: What is a Temporal Table?, Why Temporal? How does this work?, When to use (use cases) and demos…

    Yes another exploration and exercise on graph modeling. This time about something I often use… the Cloud: Microsoft Azure. Thought it would be interesting to model the available Azure Cloud Service offerings by category, region, and location. The inspiration and data comes from the following source of information on the following web page http://azure.microsoft.com/en-us/regions/#services. A trivial one, but useful for demonstrating and understanding concepts. This is also a great example of how to store master data and discover new insights in existing data.

    The Cypher, CSV files, and database backup can be found here -> https://github.com/sfrechette/azureservices-neo4j

    You can also get a copy of the raw data in Excel here on OneDrive

    How to approach and model our graph

    Let’s visualize it! A Region ‘East US’ is in a Location ‘Virginia’, that Region offers a Service ‘Stream Analytics’ and that Service belongs in a Category named ‘Analytics’. Also a Service could be a parent or child of another Service. From that analysis we can create the following model.

    Azure Cloud Services Graph Model:

    Graph model built using Arrows http://www.apcjones.com/arrows/

    Importing the Data using Cypher

    Using the CSV files will be using Cypher’s LOAD CSV command to transform and load the content into a graph structure.
    You can import and run the entire Cypher script using the neo4j-shell by issuing the following command:
    bin/neo4j-shell -path azureservices.db -file cypher/import.cql
    *make sure the Neo4j service is not running before you execute the script (bin/neo4j stop)

    The following is the Cypher script that creates the indexes, constraints, nodes and relationships for our graph.

    Once the script successfully executed, start Neo4j and load the web console (http://localhost:7474)

    Some cypher queries




    New to Neo4j and graph databases, follow this link to get you started http://neo4j.com/developer/get-started/

    We are back on September 17 for another great season!
    Looking forward to exciting times with the upcoming release of SQL Server 2016, recent Power BI availability and always evolving Azure cloud offerings.

    Here is the schedule:

    Still some open slots, if you would like to a talk, present at one of our scheduled meetups, please submit your session title and abstract to stephanefrechette@ukubu.com with “Ottawa SQL Server User Group (PASS Chapter)” in the subject header

    For more details please visit our Meetup page -> http://www.meetup.com/OttawaSQLServerUserGroup/ and
    official PASS Chapter page -> http://ottawa.sqlpass.org/

    Follow us on Twitter @OttawaPASS