azurecloud
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_azure

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

azure_europe

azure_services

azure_ml_cypher

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.

ottawapass
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

Airplane_silhouette A quick exercise on graph modeling. I recently got inspired from one of Nicole White’s post who did something similar with the Dallas Fort Worth Airport (DFW) data. So I decided to create a Neo4j graph database of the shopping places (boutiques, restaurants and services) at the Montreal-Pierre Elliott Trudeau International Airport (YUL). There isn’t any raw data to download on the site. So I will spare you the details of my scrapping efforts and instead share with you my findings and results.

 

 

You can view and download the data here from Google Drive. The code, sample queries and database backup can be found on Github.

Here is a sample from the Data sheet:
yul_data_sheet

Essentially from the raw data collected I was able to create individual sheets for each ‘entities’ and for ‘relationships’, just like tables you would model in your favourite RDBMS and from that exported them to individual CSV files ready for import;

  • area.csv
  • categories.csv
  • gatezones.csv
  • levels.csv
  • area.csv
  • placecategorygatezone.csv
  • places.csv

But first let take a look at how we will approach and model our graph, FYI at YUL airport there is no ‘Terminals’ but rather ‘Levels’…

Case in Point: A Place ‘Tim Hortons’ belongs in a Category ‘Cafés’ and that Place is located at a Gate ‘Gate 3′ in the following Area ‘Restricted area – Canada’ on a certain Level ‘First Floor/Departures’. Pretty straight forward and from that perspective we can create our model.

YUL Graph Model:
yul_propertygraph

Importing the Data using Cypher

With the extracted 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 yul.db -file cypher/import.cypher

import.cypher

Start Neo4j and load the web console (http://localhost:7474), we are now ready to issue some Cypher queries…

Find all places and their categories located at Gate 57 (with Area and Level)

gate57

Find Boutiques in Restricted area – International on First Floor/Departures

yul_resultquery02

Return count of places in each Category

yul_result03

Take time to explore the data and try to write your own queries!

Don’t forget the available online training from Neo4j: Getting Started with Neo4j and recently added Neo4j in Production http://neo4j.com/graphacademy/online-training/

This past May 4 – 8 in Chicago was the Microsoft Ignite 2015 Conference. I unfortunately was not able to attend but was certainly looking forward to all the session material being available and posted on Channel 9.

Some enhancements and new features coming SQL Server 2016 were announced:
– Stretch Database
– Native JSON support
– PolyBase
– Enhancements to Master Data Services and SSIS
– Always Encrypted
– Operational Analytics
– In database analytics with R-Integration
– Azure SQL Data Warehouse

I strongly recommend you keep updated by reading the following Data Platform Blogs and watch the Day 1 Microsoft Ignite Keynote

SQL Server 2016 public preview is coming this summer. To not be left out and stay informed please sign up here and don’t forget to also signup for Power BI Preview

So in no particular order I have quickly put together a list of sessions that are of interest to me and that will consume most of my upcoming evenings and weekends…


NoSQL on Microsoft Azure: An introduction
https://channel9.msdn.com/Events/Ignite/2015/BRK2555

Data Science with Microsoft Azure Machine Learning, Microsoft SQL Server and R
https://channel9.msdn.com/Events/Ignite/2015/BRK3550

What’s Next for Business Analytics in Microsoft Excel
https://channel9.msdn.com/Events/Ignite/2015/BRK2566

What’s Next for Visualizations in Microsoft Office
https://channel9.msdn.com/events/Ignite/2015/BRK3564

Microsoft BI Overview
https://channel9.msdn.com/Events/Ignite/2015/BRK2556

Overview of Advanced Analytics with Machine Learning and Data Orchestration
https://channel9.msdn.com/Events/Ignite/2015/BRK2569

Harnessing the Power of Hadoop: Cloud Scale with Microsoft Azure HDInsight
https://channel9.msdn.com/Events/Ignite/2015/BRK2557

In-Memory Technologies Overview for Microsoft SQL Server and Microsoft Azure
https://channel9.msdn.com/Events/Ignite/2015/BRK2563

Power BI Overview
https://channel9.msdn.com/Events/Ignite/2015/BRK2568

Microsoft Azure SQL Data Warehouse Overview
https://channel9.msdn.com/Events/Ignite/2015/BRK2571

Microsoft SQL Server BI Drill Down
https://channel9.msdn.com/Events/Ignite/2015/BRK2552

Microsoft Azure Machine Learning Marketplace: Real-World Experience to Solve Your Business Problems
https://channel9.msdn.com/Events/Ignite/2015/BRK2560

Microsoft Azure SQL Database: Overview and Customer Scenarios
https://channel9.msdn.com/Events/Ignite/2015/BRK2564

Next Steps in Polybase with Dr. David Dewitt
https://channel9.msdn.com/Events/Ignite/2015/BRK3565

SELECT Latest FROM Microsoft Azure DocumentDB
https://channel9.msdn.com/Events/Ignite/2015/BRK2551

Advanced Analytics: Navigating Your Way There
https://channel9.msdn.com/Events/Ignite/2015/BRK2567

Operational Analytics in SQL Server
https://channel9.msdn.com/Events/Ignite/2015/BRK4552

Big Data for the SQL Ninja
https://channel9.msdn.com/Events/Ignite/2015/BRK2550

Cloud-Based Machine Learning for the Developer
https://channel9.msdn.com/Events/Ignite/2015/BRK3560

Overview of Microsoft Analytics Platform System (APS)
https://channel9.msdn.com/Events/Ignite/2015/BRK1550

Build Hybrid Big Data Pipelines with Azure Data Factory and Azure HDInsight
https://channel9.msdn.com/Events/Ignite/2015/BRK3559

Microsoft Azure DocumentDB and Azure HDInsight: Better Together
https://channel9.msdn.com/Events/Ignite/2015/BRK3562

Real-Time Analytics at Scale for Internet of Things
https://channel9.msdn.com/Events/Ignite/2015/BRK3555

End-to-End Business Analytics Solution: Real-World Scenario & Demo
https://channel9.msdn.com/Events/Ignite/2015/BRK2559

Power BI for Developers
https://channel9.msdn.com/Events/Ignite/2015/BRK3551

Planning Your Big Data Architecture on Azure
https://channel9.msdn.com/Events/Ignite/2015/BRK2576

Optimizing Hadoop Using Microsoft Azure HDInsight
https://channel9.msdn.com/Events/Ignite/2015/BRK3556

Advanced Modeling and Calculations Using the Microsoft Power BI Designer
https://channel9.msdn.com/Events/Ignite/2015/BRK3573

Revolution Analytics Joins Microsoft: Strange Bedfellows or a New Power Tool for Advanced Analytics
https://channel9.msdn.com/Events/Ignite/2015/BRK3553

Azure SQL Data Warehouse: Deep Dive
https://channel9.msdn.com/Events/Ignite/2015/BRK3569

DNA of Data-Driven Organizations: Storytelling with R, Microsoft Azure Machine Learning and Power BI
https://channel9.msdn.com/Events/Ignite/2015/BRK2562

Scalable BI and Advanced Modeling with Microsoft SQL Server Analysis Services and Power Pivot
https://channel9.msdn.com/Events/Ignite/2015/BRK3561

Harness Predictive Customer Churn Models with Azure Machine Learning, Data Factory, and More
https://channel9.msdn.com/Events/Ignite/2015/BRK3570

What’s New in Master Data Services (MDS) and Integration Services (SSIS) in SQL Server
https://channel9.msdn.com/events/Ignite/2015/BRK2578

Microsoft SQL Server Unplugged
https://channel9.msdn.com/events/Ignite/2015/BRK1551

ColumnStore Index: Microsoft SQL Server 2014 and Beyond
https://channel9.msdn.com/events/Ignite/2015/BRK4556