Using PowerBI with Neo4j

There’s an excellent post by Cédric Charlier over at his blog about hooking Neo4j into PowerBI. It’s simple to follow and get’s you up and running, but I (as a PowerBI newbie) had a couple of spots where I ran into trouble – generally with assumptions I think that are made assuming that you know how to navigate around the PowerBI interface. (I didn’t).

So, here is a simple tutorial to get us non-BI people up and running!

I’ve written a Data Connector for Neo4j now – and I would heartily recommend you have a look at the new post I’ve written about it here:

The Setup Steps

First – we’ve got to install PowerBI – now, I didn’t sign up for an account, but downloaded it from the PowerBI website, and installing was simple and quick.

We also need to have Neo4j running, and you can use Community or Enterprise, it matters not – and we’ll want to put the ‘Movies’ dataset in there, so run your instance, and execute:

:play Movies

Now we’re ready to ‘BI’!

Step 1 – Start Power BI Desktop

This is pretty obvious, but in case you need it – click on the ‘Power BI Desktop’ link in your start menu – or double click on it if you went and put it on the Desktop. Crazy days.

Step 2 – Click on ‘Get Data’


That way we can get data!

Step 3 – Select ‘Blank Query’

Why not ‘web’ you ask? Well as we’re going to do some copy/pasting – it’s easier from a blank query point of view.


Step 4 – Advanced

In the query editor window that pops up, select ‘Advanced Editor’


Step 5 – Get Data!

We’re going to use the same query as Cédric as you can then use this post to augment his, so in the query editor simply paste:

    Source = Web.Contents( "http://localhost:7474/db/data/transaction/commit",
                          ""statements"" : [ {
                          ""statement"" : ""MATCH (tom:Person {name:'Tom Hanks'})-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors), (coActors)-[:ACTED_IN]->(m2)<-[:ACTED_IN]-(cocoActors) WHERE NOT (tom)-[:ACTED_IN]->(m2) RETURN AS Recommended, count(*) AS Strength ORDER BY Strength DESC""} ]

Oh noes! The same error as Cédric got – authentication. You can’t send the login details via changing the URL to be something like:


as that also fails, but you can send in the auth as a header, by adding this line:

Headers = [#"Authorization" = "Basic bmVvNGo6bmVv"],

What is this bmVvNGo6bmVv? Well, that’s the base64 encoded user/pass combo – which is a bit uh oh as you have to generate this 🙁

I’ve got two options here – LinqPad and Powershell


Using this bit of C# – obviously – you can write your own C# app in VS or whatever, but typically I use LinqPad for quick scripts.

var username = "neo4j";
var password = "neo";

var encoded = Encoding.ASCII.GetBytes(string.Format("{0}:{1}", username, password));
var base64 = Convert.ToBase64String(encoded);



This does pretty much the same, but can obviously be run in a Powershell prompt – which is nice!





$password ) $encoder = [system.Text.Encoding]::UTF8 $token = $username + “:” + $password $encoded = $encoder.GetBytes($token) $base64 = [System.Convert]::ToBase64String($encoded) Write-Output $base64

which is then used like:

GetAuthCode.ps1 –username neo4j –password neo

So, with this information, our new ‘Get data’ bit looks like:

    Source = Web.Contents( "http://localhost:7474/db/data/transaction/commit",
                 Headers = [#"Authorization" = "Basic bmVvNGo6bmVv"],
                          ""statements"" : [ {
                          ""statement"" : ""MATCH (tom:Person {name:'Tom Hanks'})-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors), (coActors)-[:ACTED_IN]->(m2)<-[:ACTED_IN]-(cocoActors) WHERE NOT (tom)-[:ACTED_IN]->(m2) RETURN AS Recommended, count(*) AS Strength ORDER BY Strength DESC""} ]

which when we ‘preview’ gives us this:


Step 6 – Read as Json

Select the ‘localhost’ file and then choose ‘open as Json’ from the top menu:


You’ll notice once you’ve done this – your ‘Source’ has changed to now be ‘Json.Document(Web.Contents…)’


Step 7 – Navigation

First click on the ‘List’ of ‘Results.

This will take you to a screen that looks like this:


Note, you now have another ‘Step’ in the right hand bar – by the way – if you ever ’lose’ the Settings side bar – click on ‘View’ at the top and select ‘Query Settings’ to bring it back.

Then click on the ‘Record’ link, and then the ‘List’ for data:


Worth noting here, we’re still in the ‘Navigation’ step

Now you should have a list of ‘Record’s –


Step 8 – Table-ify

Go ahead and press the ‘To Table’ button, and then just ‘OK’ on the dialog that pops up:


Step 9 – Expand the Column

Records aren’t useful to Power BI (apparently) so – we need to expand that column out and to do that we click on the ‘Expand’ button – and in our case – we only want the ‘row’, not the meta, so unselect the ‘meta’ and press OK


Now you should see a row of ‘List’ and an extra step in our ‘Applied Steps’ list:


Step 10 – Add a custom column

So now we need to get the information out of these new ‘Lists’ – and to do that we need a custom column, so click on the ‘Custom Column’ button in the ‘Add Column’ tab:


In the dialog that pops up we want to have it say:

= Record.FromList([Column1.row], type[Name = text, Rank = number])

Then press OK, and you’ll have another Column called ‘Custom’, and another item in our Applied Steps:


Step 11 – Expand Custom

More records eh? Let’s expand it out, so as before, click on the ‘Expand’ button:


and in this case, we want all the columns:


Now you should have two new columns, and another step added:


Data! Yay!

Step 12 – Remove that non-useful row

Right click on the ‘Column1.row’ column and select Remove


Step 13 – Close & Apply

Now we have data in a format we can use in Power BI, let’s close and apply that query.


Step 14 – Use that data

Now – I’m no Power BI user – so this is super simple and pointless, but should get you going for experimenting.

After applying that query we’re back in the main desktop view, but now in the right hand side – we have some fields with our Query there:



I’m going to pick a ‘Treemap’ – because.


Empty treemap – Check!


Let’s set some data, I want to group by ‘Rank’, so I drag ‘Custom.Rank’ to the ‘Group’ section which is in the ‘Visualizations’ bar:


And then for ‘Values’ I’m going to drag the ‘Custom.Name’ field


Oooooh – colours:


Let’s expand our visualization by pressing the ‘Focus Mode’ button:


Boom! Full size

Now, if I hover over one of those boxes I get the brief info displayed:


Ace, only 2 names with a rank of 5, and to see who they are, right click and select ‘See Records’


And here they are:


No More Steps

If you want to just copy/paste the code, you can! Create a new blank query and open up the advanced editor and just paste the code below in. (NB There are probably loads of things which are rubbish about this implementation, lemme know!)

    Source = 
                Headers=[Authorization="Basic bmVvNGo6bmVv"],
                Content=Text.ToBinary("{""statements"" : [ {
                        ""statement"" : ""MATCH (tom:Person {name:'Tom Hanks'})-[:ACTED_IN]->(m)<-[:ACTED_IN]-(coActors), (coActors)-[:ACTED_IN]->(m2)<-[:ACTED_IN]-(cocoActors) WHERE NOT (tom)-[:ACTED_IN]->(m2) RETURN AS Recommended, count(*) AS Strength ORDER BY Strength DESC""} ]
    results = Source[results],
    results1 = results{0},
    data = results1[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"row"}, {"Column1.row"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each Record.FromList([Column1.row], type[Name = text, Rank = number])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "Rank"}, {"Custom.Name", "Custom.Rank"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column1.row"})
    #"Removed Columns"

Writing a Stored Proc in Neo4j for .NET Developers

I’m a .NET developer and I have been for about 13 years or so now, predominantly in C#, but I originally (in my university days) started off programming in Java. Now, I’ve not touched Java for roughly 13 years, and I’m pretty happy with that situation.

3 years(ish) ago I started using Neo4j – as you might notice from previous blog posts. The ‘j’ does indeed stand for Java and I had the feeling that some day – some dark day – I would have to flex those Java muscles again.

Turns out they had atrophy-ed to nothing.


I’m guessing that I’m not the only .NET dev out there who fears the ‘j’ – so here’s a quick get-up-and-go guide to writing a Neo4j Stored Procedure.

We’re going to write a stored procedure to return a movie and all it’s actors from the example ‘movies’ database you can add to your instance by running ‘:play movies’ in the Neo4j Console.

The Tutorial

We’re going to go through the steps to create a super simple stored proc that just get’s the actors for a given movie.

Step 1. Install the JDK

I never thought I’d have to write that. Ho Hum, so I google for ‘Java SDK’ and pick the ‘Java SE Development Kit 8’ link (top one for me). Then download the appropriate SJDK for your environment, x64 or x86. Then install that bad boy. Oooh, Java is used on X Billion devices good to know!

Step 2. Install Gradle

Just go to and follow the instructions – if you have ‘scoop’ or ‘chocolatey’ installed, then you can use them, I went manual install.

Side note 1 – WTF is Gradle?

Gradle is a build-automation system for Java, I guess a bit like MSBuild – with Nuget built in. As we’ll see a bit later on, you add the dependencies which are then pulled from Maven (the Java Nuget (I think)) – most of the posts you see online referring to how to create stored procs for Neo4j will use a Maven setup, but APOC (a large community driven set of stored procs) uses Gradle, and I reckon if they’re using Gradle, it’s probably better than Maven. Or it’s newer and shinier. Either way – I’m going Gradle.

Step 3. Choose your IDE – IntelliJ

To be honest, if you go with anything other than IntelliJ IDEA – you may as well stop reading – as this is all written from an IntelliJ point of view. I’m using the Ultimate edition, but I have no doubt this will be pretty much the same on the Community (free!) version.

Download and install.

Step 4. Start IntelliJ


It does have a nicer splash screen than Visual Studio – and JetBrains write Resharper – so hopefully the changeover isn’t as jarring (ha!) as it could be.

Step 5. New Project!


But what new project?


What we’re going to go for is a ‘Gradle’ project, choosing Java and using the 1.8 SDK:


Step 6. GroupId and ArtifactId

Pressing ‘next’ gets us to a window allowing us to set the groupId and artifactId of our project.

Step 7…

Wait what? GroupId? ArtifactId? What on earth are they??? Shouldn’t there just be ‘Name’?

OK, you can think of these as kind of like a namespace and a dll (jar) name.

GroupId – a name to uniquely identify your project across all projects. Typically (it seems) this usually follows the convention of ‘org.<companyName>.<projectName>’ so, (going all MS) I might have: ‘org.contoso.movies’.

ArtifactId – Basically the name of the JAR file that is created, minus any versioning information. Lowercase only folks – cos it’s Java, and I guess to optimise keyboard usage they opted to shun the shift key.


As you can see, I’ve got a company name of ‘cskardon’ and a JAR name of ‘movies-procs’. I’ve left the Version as it was. Just because. Hit Next, next.

Step 7. More settings!

Don’t worry we’re nearly there,

I turned on ‘Auto-import’ and ‘Create directories for empty content roots automatically’. I’m using the default gradle wrapper – this basically (as far as I know) puts a copy of gradle into your folder so you can run ‘gradle.bat’ from the command prompt and have it do all the things. Either way, it does mean you don’t have to install gradle if you’re just using the code.

You will need to make sure the Gradle JVM is set to ‘1.8’ (see the picture below) it won’t work with the JAVA_HOME option.


Step 8. Locations!

Finally! Locations! Name wise – we’ll stick with what we selected for the artifactId in step 6, this makes life easier – and location wise – go for wherever you like – it’s your computer after all.


Note, we now have a ‘finish’ button – no more ‘next’ HUZZAH!

Step 9. Expand and config files

First off, let’s expand the ‘movies-procs’ node:


Now, double click on the ‘build.gradle’ file. We need to add some things here to get access to libraries for Neo4j. First up is a ‘project.ext’ element:

project.ext {
    neo4jVersion = "3.2.0"

This needs to be below the sourceCompatibility element, and above the repositories element. Speaking of which, we need some more repositories, so set the repositories element to:

repositories {
maven { url "" }
maven { url "" }

Now we need to change the dependencies so we can use all the goodies.

dependencies {
compile group: 'commons-codec', name: 'commons-codec', version:'1.9'

compile 'com.jayway.jsonpath:json-path:2.2.0'

compileOnly group: 'net.biville.florent', name: 'neo4j-sproc-compiler', version:'1.2'

testCompile group: 'junit', name: 'junit', version:'4.12'

testCompile group: 'org.hamcrest', name: 'hamcrest-library', version:'1.3'

testCompile group: 'org.apache.derby', name: 'derby', version:''

testCompile group: 'org.neo4j', name: 'neo4j-enterprise', version:neo4jVersion

testCompile group: 'org.neo4j', name: 'neo4j-kernel', version:neo4jVersion, classifier: "tests"
testCompile group: 'org.neo4j', name: 'neo4j-io', version:neo4jVersion, classifier: "tests"
compileOnly(group: 'org.neo4j', name: 'neo4j', version:neo4jVersion)
compileOnly(group: 'org.neo4j', name: 'neo4j-enterprise', version:neo4jVersion)

compileOnly(group: 'org.codehaus.jackson', name: 'jackson-mapper-asl', version:'1.9.7')
testCompile(group: 'org.codehaus.jackson', name: 'jackson-mapper-asl', version:'1.9.7')

compileOnly(group: 'org.ow2.asm', name: 'asm', version:'5.0.2')

compile group: 'com.github.javafaker', name: 'javafaker', version:'0.10'

compile group: 'org.apache.commons', name: 'commons-math3', version: '3.6.1'

By the way – I’d like to point out I have largely got this from the APOC library, so it’s probably bringing in too much, and is probably overkill, but later on when you need something obscure, it’s probably already there. So… Win!

Step 10. Package 1

10 steps to get to programming, but on the plus side – each stored proc you add to this project doesn’t need the setup, and it’s a one-off for each project. Anyhews.

So we’re going to add a package, which is a namespace. In this case we’re going to add one called ‘common’:

Expand the ‘src/main’ folders – and right click on the ‘java’ folder, then add –> new –> Package


Now you have the package there:


Step 11. A class

Now time to add a Java file – called ‘MapResult’ – this is entirely taken from APOC.


Type is in this case a class:


Highlight everything in the class that is created, and paste the below into it:

package common;

import java.util.Collections;

import java.util.Map;

public class MapResult {

private static final MapResult EMPTY = new MapResult(Collections.<String, Object>emptyMap());
public final Map<String, Object> value;

public static MapResult empty() {

return EMPTY;

public MapResult(Map<String, Object> value) {

this.value = value;

This allows us to map this result of our query.

Step 12. Package 2

OK, now we’re going to add another package to the ‘java’ folder, this time called ‘movie’, and a class within that called ‘ActorProcedures’ – not necessarily the best named class :/


Step 13. Code!

I’m just going to ask you to paste the below into your code window, and we’ll go over it in a minute or two:

package movie;

import common.MapResult;
import org.neo4j.procedure.Context;
import org.neo4j.procedure.Name;
import org.neo4j.procedure.Procedure;

import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import static java.lang.String.format;
import static java.lang.String.join;

public class ActorProcedures {
public org.neo4j.graphdb.GraphDatabaseService _db;

public static String withParamMapping(String fragment, Collection keys) {
if (keys.isEmpty()) return fragment;
String declaration = " WITH " + join(", ", -&gt; format(" {`%s`} as `%s` ", s, s)).collect(Collectors.toList()));
return declaration + fragment;

public Stream getActors(@Name("title") String title) {

Map&lt;String, Object&gt; params = new HashMap&lt;String, Object&gt;();
params.put("titleParam", title);
return _db.execute(withParamMapping("MATCH (m:Movie)&lt;-[:ACTED_IN]-(a:Person) WHERE m.title = {titleParam} RETURN a", params.keySet()), params).stream().map(MapResult::new);

Step 14. Build

Yes – I know we’ve eschewed tests, we’ll come to those later, for now we just want to do the standard ‘build’ – because we’re using gradle. we’re going use IntelliJ to help us run the build, first go to ‘View’ then ‘Tool Windows’ and select ‘Gradle’


In the window the pops up, expand the ‘Tasks’ and then ‘build’ collapsed elements, and double click on ‘build’:


You should get a ‘Run’ window popping up at the bottom of the screen, looking a bit like this:


You should also now have a ‘build’ folder in your project window, with a ‘libs’ folder inside, and hopefully inside that – the .jar file


Step 15. Manual Testing

I’m going to cover unit testing the procedure in another post, to try to limit the size of this one, but obviously now we have a .jar, we want to put that into our DB.

Right-click on the .jar and select ‘Show in Explorer’


Copy that .JAR file and place into the ‘plugins’ directory of your version of Neo4j. Now, if you’ve used the ‘zip’ version – it’s just in the root already there, and if you’re using the installer version – you’ll need to create a ‘plugins’ folder in the location listed in the application:


So copy the location and open it in explorer:


New folder called plugins.

Now paste the .Jar file into the plugins folder and stop (if you need to) Neo4j, then start it again to load it.

Go to the neo4j browser and login if you need to.

We’re now going to run ‘call dbms.procedures’


We get a list of the procs in the DB, so far so good – now it’s time to scroll on down the list…



Now, let’s call that bad boy. BTW – I’m assuming you have the movies DB installed – if not, run

:play movies

now and get it all there. Done? Good.

To call our proc, we run:

call movie.getActors(“Top Gun”)


Which gets us results:


Now, that seems tested and working. But we probably want to start getting some unit tests in there asap, so I’ll cover that next.

Moving House

I’m currently in the process of moving my blog from it’s old home of geekswithblogs to this new location, so please bear with me. I’ll be putting new posts here, and migrating my older ones as and when needed.

So – if you’re here and you expected to be at the old blog – you need to head over to: