Introducing 

Prezi AI.

Your new presentation assistant.

Refine, enhance, and tailor your content, source relevant images, and edit visuals quicker than ever before.

Loading…
Transcript

More info:

Case

Study

Tereza Gregorova

rik@neotechnology.com

@rvanbruggen

Graph Databases & BI

Background, Concepts & Case Study

Case Study Presentation:

Implementing Graph Database within Business Intelligence to map student data

Graph Databases & Business Intelligence?

Help address common challenges in the input & output chains

Graph Databases

Graph Concepts

Graphs can act as

Sources and/or Targets

NOT ONLY SQL

Trying to solve some everlasting challenges in relational database systems

  • Volume challenges
  • Complexity challenges

Graphs can provide a more flexible, up-to-date query infrastructure for

certain use cases

Euler conceived Graph Theory in the 18th century

7 Bridges of Konigsberg

Complex questions

Tereza Gregorova

Business Intelligence Graduate Developer

Select the academics who have achieved less than 70 points in Academic Rating and all students that they have taught and whose marks were lower than 13

start n=node:Student("Student_Name:*") match n-[r:takes]->m<-[k:teaches]-o where (o.Academic_Rating<"70" and r.Grade<"13")

return n.Student_Name,r.Grade, m.Module_Name, o.Academic_name,o.Academic_Rating;

Academic

Student

takes

Module

teach

n-[r:takes]->m<-[k:teaches]-o

Properties

n.Student_Name, r.Grade, m.Module_Name, o.Academic_name,o.Academic_Rating

Select the academics who have achieved less than 70 points in Academic Rating and all students that they have taught and whose marks were lower than 13. Also show whether these academics have any grants.

start n=node:Student("Student_Name:*") match n-[r:takes]->m<-[k:teaches]-o-[s:has_grant]->p where (o.Academic_Rating<"70" and r.Grade<"13") return n.Student_Name, m.Module_Name, o.Academic_name,o.Academic_Rating,p.Grant_name,p.Grant_Amount_Used,r.Grade;

takes

teach

has_grant

Grant

Academic

Module

Student

Tereza Gregorova

Email: tg27@st-andrews.ac.uk

Rik Van Bruggen

Email: rik@neotechnology.com

n-[r:takes]->m<-[k:teaches]-o-[s:has_grant]->p

Abstract representation + Computational algorithms

Properties

Show the academics who have achieved less than 70 points in Academic Rating and all students that they have taught and whose marks were lower than 13.

n.Student_Name,r.Grade, m.Module_Name, o.Academic_name,o.Academic_Rating,p.Grant_name,p.Grant_Amount_Used;

Count the number of books that were borrowed by students whose marks were lower than 13 and who were taught by the academics who have achieved less than 70 points in Academic Rating and have some grant.

start n=node:Student("Student_Name:*")

match j<-[t:borrow]-n-[r:takes]->m<-[k:teaches]-o-[s:has_grant]->p

where o.Academic_Rating<"70" and r.Grade<"13"

return

n.Student_Name, m.Module_Name,r.Grade,count(j.Book_Name), o.Academic_name, o.Academic_Rating,p.Grant_name;

borrow

takes

has_grant

teaches

Grant

Academic

Student

Module

Book

Counting the Edges!

Scottish and English Higher Education Systems differ

Select the academics who have achieved less than 70 points in Academic Rating and all students that they have taught and whose marks were lower than 13. Also show whether these academics have any grants.

j<-[t:borrow]-n-[r:takes]->m<-[k:teaches]-o-[s:has_grant]->p

Properties

n.Student_Name, m.Module_Name,r.Grade,count(j.Book_Name), o.Academic_name, o.Academic_Rating, p.Grant_name;

Count the number of books that were borrowed by students whose marks were lower than 13 and who were taught by the academics who have achieved less than 70 points in Academic Rating and have some grant.

Analysis in QlikView

Show me all students that take module "Academic Project Planning" and show me also academics who teach this module

Length of Undergraduate Honours Degree

Flexibility in the first 2 years of study

Following different pathways of study

Loading data from Relational Database to Graph Database

From Relational Database to Graph Database

Loading data from Graph Database to BI tool QlikView

Data model

under

admit_into

part of

Department

Faculty

Institution

Student

offer

takes

Modules

visit

form

access

Subject

Connecting QlikView with Neo4j by using JDBC connector

Award

Programme

differs

teaches

leads_to

Library

Route

has

goes

borrow

Pathway

store

CUSTOM CONNECT TO "Provider=JDBCConnector.dll;jdbc:neo4j://localhost:7474;";

Book

Academic

Data in Graph Database Neo4j

has_grant

Research

Grant

Analysis in QlikView

Loading data

Show me all students that access programme "BSc (Science Faculty)"

QlikView data model

SQL START n=node(*)

RETURN ID(n) as NodeID, n.Student_Code? as StudentID, n.Student_Name? as StudentName, n.Module_Name? as ModuleName,

n.Academic_name? as AcademicName, n.Grant_name? as GrantName, n.Department_School_Name? as DepartmentSchoolName,

n.Programme_Name? as ProgrammeName, n.Grant_Funding_Body? as GrantFundingBody, n.Subject_Name? as SubjectName,

n.Grant_Amount? as GrantAmount, n.Pathway_Name? as PathwayName, n.Grant_Amount_Used? as GrantAmountUsed, n.Award_Name? as AwardName,

n.Grant_Used_by? as GrantUsedby, n.Route_Name? as RouteName, n.Grant_date? as GrantDate,n.Institution_Name? as InstitutionName,

n.Faculty_name? as FacultyName;

Loading relationships

SQL START r=relationship(*)

RETURN ID(r) as RelID, r.Grade? as RelGrade, r.Semester? as RelSemester, r.Year? as RelYear,r.Property? as RelProperty, type(r) as RelLabel;

Loading data from relational database

Building the graph

COURSE_TO_DPT:

SQL

SELECT

R.ROU_NAME,

R.ROU_CODE,

R.ROU_CODE as CAP_ROUC,

RE.ROE_ROUC,

RE.ROE_PWYC,

P.PSD_PWYC,

P.PSD_SUBC,

P.PSD_PERC,

P.PSD_DPTC,

D.DPT_CODE,

D.DPT_SNAM,

D.DPT_NAME,

D.DPT_TYPE,

S.SUB_CODE,

S.SUB_UDF1,

S.SUB_NAME

FROM

INS_ROU R,

INS_ROE RE,

INS_PSD P,

INS_DPT D,

INS_SUB S

WHERE

R.ROU_CODE=RE.ROE_ROUC (+)

AND RE.ROE_PWYC=P.PSD_PWYC (+)

AND P.PSD_DPTC=D.DPT_CODE(+)

AND P.PSD_SUBC = S.SUB_CODE(+);

SQL START n=node(*)

MATCH n-[r]->m

RETURN ID(r) as RelID, ID(n) as StartNodeID, ID(m) as EndNodeID, type(r) as Label;

What are

Graphs

good for?

Analysis in QlikView

Show me all modules that "Becky Blue" teaches, also show me names of all grants that "Becky Blue" has

Why Neo4j

Graph Database?

An open source graph database

Uses the structure of the graph to store, manipulate and represent the complex data

Basic operation is traversal of graph

Uses Cypher to query the graph database

A database with an explicit graph structure using nodes, edges and properties to represent and store data

User works with nodes, edges (relationships) and properties in a flexible graph network

Graph Database Concepts

Student records and degree path

under

admit_into

part of

Department

What was proven?

Faculty

Institution

Student

offer

takes

Deal with

Semi-Structure

Modules

Deal with Complexity

form

access

Subject

Award

Programme

differs

teaches

Cypher

leads_to

Route

has

goes

Pathway

Graph database Neo4j can be implemented with QlikView

Neo4j can be used in educational institution as an extra layer for mapping relationships between data

Great potential for using graph database in BI for analyzing data

Academic

has_grant

Research Grant

Designed for querying complex, interconnected data

Queries for the shortest path

Pattern matching

Modeling, Storing,

Querying, Analysing,

Interacting ...

with a

Simple question

Select all modules that a student with Student_ID=11002233 takes

Finding answer using Cypher

Start a=node:Student(Student_ID="11002233") Match a-[r:takes]->b Return a, r, b;

Student

takes

Module

Cypher vs SQL

Select all modules that a student with Student_ID=11002233 takes

Cypher

Student

takes

Start a=node:Student(Student_ID="11002233") Match a-[r:takes]->b Return a, r, b;

Module

SQL

Select STUDENT.STUDENT_ID, FIRST_NAME, SURNAME, MODULE, MODULE_CODE, MOD_NAME

From STUDENT

Join STUDENT_PROGRAME_ROUTE

On STUDENT_PROGRAME_ROUTE.STUDENT_ID=STUDENT.STUDENT_ID

Join STUDENT_MODULE_TAKING

On

STUDENT_MODULE_TAKING.STU_PROG_ROUTE_CODE=STUDENT_PROGRAME_ROUTE.STU_PROG_ROUTE_CODE

Join MODULE

On MODULE.MODULE_CODE=STUDENT_MODULE_TAKING.MODULE_CODE

Where STUDENT.STUDENT_ID=”110002233”

Information about students stored in a Relational database

ER diagram

Labeled

Property

Graph

Simple question

Select all modules that a student with Student_ID=11002233 takes

Finding answer using SQL

Select STUDENT.STUDENT_ID, FIRST_NAME, SURNAME, MODULE, MODULE_CODE, MOD_NAME

From STUDENT

Join STUDENT_PROGRAME_ROUTE

On STUDENT_PROGRAME_ROUTE.STUDENT_ID=STUDENT.STUDENT_ID

Join STUDENT_MODULE_TAKING

On

STUDENT_MODULE_TAKING.STU_PROG_ROUTE_CODE=

STUDENT_PROGRAME_ROUTE.STU_PROG_ROUTE_CODE

Join MODULE

On MODULE.MODULE_CODE=STUDENT_MODULE_TAKING.MODULE_CODE

Where STUDENT.STUDENT_ID=”110002233”

Thank you!

Tereza Gregorova

Email: tg27@st-andrews.ac.uk

Learn more about creating dynamic, engaging presentations with Prezi