Connect Power BI


#1

We use an analyst who uses Power BI to run analyses across our users databases. He wants us to expose a URI with an API key to connect Power BI.

We’re not linux devs. Are there step by step instructions on how to expose a URI with an API key to connect Power BI to Realm Cloud?


#2

@Nosl Are you looking for a REST API? We are looking to expose our REST API endpoint with Realm Cloud in the next few weeks.


#3

That’s exactly what I’ve been told to give him. Will that include an API key? He said he prefers to work with an API key rather than username password.


#4

@Nosl Yes we will support a token based auth


#5

Has there been any progress on a REST API endpoint with a token based auth for Realm Cloud?


#6

@Nosl Yes I believe so:
https://docs.realm.io/platform/graphql-web-access/using-the-graphql-client#authenticating-the-user


#7

Thanks @ianward. The docs you link to seem to require server-side config. We are using Realm Cloud. How do we give our Power BI guy a URI with an API key so he can connect to our Realm Cloud instance?


#8

@Nosl The URI would be your Cloud Instance DNS with the graphql endpoint shown in that doc. And the API key would be a valid user token as obtained following this:


#9

Doesn’t all that javascript run on the ros server? Where do we put it if we are running Realm Cloud?


#10

No, this is more of a reference implementation on how to use a username/password (or a different authentication mechanism) to obtain refresh and access tokens to read data from a Realm. Once you obtain the access token, you can start querying the GraphQL API for information. What programming language do you intend to use to read the data?


#11

We have a Power BI guy. He uses Power BI to visualise the data. He requires a URI with an API key. No programming involved.

When we run a database server we give the Power BI guy a connection string for the server. He can then pull whatever database tables he needs into Power BI to run analyses for us. This is an important service we offer our clients.


#12

I’m not familiar with PowerBI so I’m not sure how it’s able to download data from any API without any programming involved. Are you certain that it doesn’t expect the API to conform to some protocol?


#13

Power BI does seem to connect to pretty much any data source. There are listed at Data sources in Power BI Desktop


#14

I don’t see Realm or GraphQL in this list. I’m guessing you can use the Web option to load the results from a single table using the GraphQL API, but I’m not sure if that’s what you’re looking for.

Perhaps a more out-of-the-box solution would be to use the data adapter to synchronize Realm with a SQL server/PostgreSQL database which will then be read by your PowerBI infrastructure?


#15

Having to add a sql server just to be able to get access to data, changes the costings for Realm significantly.

GraphQL might be nifty, but it does not have the widespread support of JSON, CSV etc, so an endpoint that gave us data in a format that had widespread support would be good.


#16

GraphQL responses are JSON, so not sure what you mean regarding the format. Regardless of how we structure the REST API, it will have some structure that may or may not be easily compatible with PowerBI.

In any case, you can spin up a simple app to convert the GraphQL responses to something that PowerBI is familiar with.


#17

Forgive me for being obtuse … web auth just isn’t my world. It looks to me that for us to use the GraphQL endpoint it must be called from an app that can configure credentials.

We just want to give our PowerBI guy a URI that he can use to connect to our instance. Can we give him a URI with username & password, rather than configuring an authentication token? If he can get json from that URI then he can massage it himself within Power BI.


#18

You can use an admin refresh token to read data from the GraphQL service - by default, it has a lifetime of 10 years, but you have to be careful not to leak it as it would allow an attacker to read all data until you revoke the token.

Looking at this thread, it appears it’s possible to configure PowerBI to pass this token in the Authorization header. Then, you can create your GraphQL request as an HTTP GET request and execute in PowerBI, for example:

https://unbranded-granite-chees.us1.cloud.realm.io/graphql/%2F__admin?query=query%20%7B%0A%20%20users%7B%0A%20%20%20%20userId%0A%20%20%20%20isAdmin%0A%20%20%7D%0A%7D

is the equivalent of

query {
  users {
    userId
    isAdmin
  }
}

which will give you the userId and isAdmin values for all User objects stored in the __admin Realm.

* Note: While replying, I noticed that there’s a validation bug in the GraphQL service that will reject access for admin users. I’ve fixed it and expect an update to be rolled out to Cloud instances in the next few days.


#19

Thank you for following this through @nirinchev. This is a big thing for us. I’ll pass this information onto our Power BI Guy. How will we know the fix is in production?