Power BI connector – Auto Refresh!

By Charlotte

The oldest bug (and indeed first) for the Power BI Connector for Neo4j is about how you can’t do an Auto-Refresh on the server side of Power BI.

I’ve tried many things, but, it’s taken until now to get to where I think I have it working.

TL;DR; Use the 2.0.0 version of the connector if you want to use the auto refresh side of things.

I’m not going to go over how to install the connector – as I’ve covered that in previous posts – aside from to say – download 2.0.0 from GitHub and use that version instead.

What we will need to have installed is the Personal Gateway – and yes it does need to be the Personal one – as of right now, I’ve not managed to get the standard one working – though – they did just release a new update, so we can see.

Installing is a case of following the usual ‘next’ prompts until it’s all there and running. Now we need to check a couple of things. The first window you’ll see is the ‘Status’ one:

Signing in to get ‘more information’ as is suggested gets us a page showing:

On-premises data gateway (personal mode) - The status tab logged in, showing that 'The gateway is online and ready to be used'

Which I guess is more information. I think it probably needs to be signed in to be able to attach it to your online service, and to run diagnostics. OK! Other tabs, we’re only actually interested in the connectors one properly, though we will look at one of the others… Connectors then!

On-premises data gateway (personal mode) - Connectors tab is selected, the key bit is some text that reads 'Folder not found' as it's pointing to the default location.

We need to point to our actual Custom Connector folder – now – remember this is in %DOCUMENTS% which can be anywhere on your system – hopefully you know – as it’s where you installed the connector in the first place. Assuming you do know – put in the correct folder and press ‘Apply’ this will ask you to restart the gateway, and you’ll need to do that, so do it!

Nothing will change – the UI doesn’t seem to enjoy updating, so to force an update – select one of the other tabs and then come back to the ‘Connectors’ tab and you should see something like this:

On-premises data gateway (personal mode) - The connectors tab is selected and the tab now shows the Neo4j connector in the list of connectors.

At this point we’ve got our custom data connector discovered and we should be ready to go!

Create your dashboard – as you normally would in PowerBI

In my case, I’ve just got each movie and the number of actors, and grouped them to have a count:

MATCH (m:Movie)<-[:ACTED_IN]-(p:Person)
WITH m, count(p) AS cnt
RETURN m.title AS title, cnt AS actorCount

i.e. the end result looks like this:

A bar chart showing the number of actors, by movie count.

Basically – there are 13 movies with ‘4’ actors, 7 with 3 and so on… So far so MUNDANE.

But hey, let’s publish this:

The publish button on the toolbar

And it’ll publish (once you’ve filled in the required deets)

Showing the local report being published to the server

And once, that is done, go ahead and open your dashboard:

Shows an arrow pointing to the "Open 'TitlesByActorCount.pbix" in Power BI link

Basically – you’ll end up seeing the same thing, but online now.

The problem thus far has been that if you update your data – for example, we could remove the movie with only one actor (The Polar Express) our local Power BI desktop could be refreshed, but our online one, not.

So, let’s close the desktop window, and only focus on our webservice one for now – and let’s get rid of ‘The Polar Express’ from our DB:

MATCH (m:Movie {title: 'The Polar Express'}) 
Shows the vertical ellipsis next to the dataset (TitlesByActorCount) being selected, and then the 'Refresh now' option being chosen.

My guess (unless you’re lucky! Or using Anonymous connections) is that you’ll find it doesn’t refresh. So what we need to do is go to ‘Settings’

Image shows the 'Settings' option on the menu.

Where you’ll see a ‘yellow’ box error – if it looks like the one below – it’s most likely you don’t have the gateway either running, or with the custom connector setup – so check that first!

Shows the error: "You can't schedule refresh for this dataset because the following data sources currently don't support refresh"

Once you’ve made sure the gateway is all correct, when you click on ‘Discover Data Sources’ you’ll hopefully see:

Shows the message: "Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again"

Which is great, as that means we can click on the ‘Edit Credentials’ link and add in our user/pass:

Shows a 'Configure' modal window allowing a user to enter the username and password.

That’ll do some thinking for a little bit, and then you’ll be able to see (inside the ‘Gateway Connection’) the following:

Shows the gateway connection has succeeded

Obviously, with a different gateway name… Unless you too are called Charlotte?? So, once we have the green for go, we can actually do a refresh, (the same way as above – on the ‘Datasets’ part of the side bar).

Shows the 'Refresh now' menu option on the TitlesByActorCount vertical ellipsis menu,

This time, if we now click on the ‘Refresh history’ link

Shows the 'Refresh History' link

We can see the refresh happened and was successful!

Shows that the refresh has completed successfully.

We can even schedule refreshes – though obviously that needs the gateway to be running and accessible – and – well – it would be painful to do it every 30 mins during a day – but I guess that’s an issue with the PowerBI Server UX…

Shows the 'Scheduled Refresh' options on the server - allowing you to refresh daily with 30 minute blocks. The image shows 1 am and 1:30 am set.

The last thing we want to do is actually see the update reflected in our report – because – if we go there now, we’ll see that the ‘Polar Express’ result is still there, so we need to ‘Refresh the visuals’ which is a button in the top right of the report.

Shows the 'Refresh visuals' button on the menu bar.

There we go! Polar Express results gone!

Shows that the Polar Express movie has been removed from the dataset as no longer are there any movies with only one actor.