Neo4j

Get data from Neo4j with Power BI

Power BI offers out-of-the-box connectivity for many data sources but unfortunately Neo4j is not (yet?) in this list. To be able connect to this graph database, you’ll need to use the Transactional Cypher HTTP endpoint. This API from Neo4j lets you submit some REST POST requests (your queries) to the server and get some responses (the return of your queries).

To send a REST POST request from Power BI, you can use the M language and the function Web.Contents. The first parameter is the url: from the documentation of transactional Cypher HTTP endpoint you can easily find out the value (depending of your server’s configuration).

let
    Source = Web.Contents(
        "http://localhost:7474/db/data/transaction/commit"
    )

This API is expecting that you submit your query through a POST. To achieve this with Power BI, you’ll need to supply a second named parameter Content to the method Web.Contents. The value of this parameter will be your cypher query surrounded by a little bit of JSON syntax corresponding to the Neo4j documentation:

{
  ""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 cocoActors.name AS Recommended, count(*) AS Strength ORDER BY Strength DESC""
  } ]
}

Pay attention that this JSON request will need to be binary encoded by Power BI before the submission to the Neo4j server.

let
    Source = Web.Contents( "http://localhost:7474/db/data/transaction/commit")
      [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 cocoActors.name AS Recommended, count(*) AS Strength ORDER BY Strength DESC""} ]
           }")
      ]
    )

If you try this code, Power BI and Neo4j will inform you that your user is not authorized and prompt you to edit the credentials.The user was not authorized

If you provide valid credentials,

Neo4j credentials

Power BI will complain about the usage of Content for a non-anonymous connection.

Web Contents only for anonymous

At the moment, it sounds impossible to submit POST requests with authentication from Power BI. The only way to submit your query is to allow your Neo4j server to authorize anonymous access. To configure your server edit the neo4j-server.propertiesfile.

edit server config.png

and change the value of the parameter dbms.security.auth_enabled from trueto false. This parameter is usually at the top of the file. Be sure that you understand the consequences of his action before going further and don’t forget to restart your Neo4j server to apply the new configuration.

In order to use an anonymous connection to Neo4j, you’ll need to remove the authentication information from Power BI Desktop data source settings. Just click on File > Options and settings > Data source settings, then select the url corresponding to your Neo4j server and click on the delete button.

Remove connection settings

If you try again, the same code should return a JSON document. To explain to Power BI that this document should be imported as JSON, click on Open file as and select the  JSON option.

Open file as JSON.png

Then click on the list corresponding to the  results. This list has exactly one record, just click on it and select the child list corresponding to data.  You will obtain a list of records that you convert to a table by selecting Convert to table.

convert-to-table

When done, you’ll need to expend the column to find … another set of lists. Each list contains the two expected values (the name of an actor and his rank). To convert this list to two columns, your best friend is the function Record.FromList

Table.AddColumn(
  #"Expanded Column1"
  , "Custom"
  , each Record.FromList(
    [row]
    , type [Name = text, Rank = number]
  )
)

Expend the record and remove the column containing the initial list  … and tadam you can now easily use the result of your query!

end-result

Now, you can play with the cool visualizations of Power BI but you can also merge the result of this query with another table or anything else that is possible with Power BI.

neo4j-power-bi.png

Full code:

let
  Source = Web.Contents( "http://localhost:7474/db/data/transaction/commit",
    [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 cocoActors.name AS Recommended, count(*) AS Strength ORDER BY Strength DESC""} ]
    }")]
  ),
  #"Imported JSON" = Json.Document(Source),
    results = #"Imported JSON"[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"}, {"row"}
  ),
  #"x"=Table.AddColumn(
    #"Expanded Column1"
    , "Custom"
    , each Record.FromList([row], type [Name = text, Rank = number])
  ),
  #"Expanded Custom" = Table.ExpandRecordColumn(
    x
    , "Custom"
    , {"Name", "Rank"}, {"Name", "Rank"}
  ),
  #"Removed Columns" = Table.RemoveColumns(
    #"Expanded Custom"
    ,{"row"}
  )
in
  #"Removed Columns"
Advertisements