PostgreSQL Protocol: Power BI

Connect to RavenDB

Get Data

• Click "Get Data" from Power BI Desktop's startup wizard or menu option.

Select PostgreSQL database

• Select the PostgreSQL database option and click Connect.

Connection Details

• Server
  Enter RavenDB's URL and PostgreSQL port number.
  • Enter the URL and port number in the form: Hostname:Port
    E.g. - a.ravenpostgresql.development.run:5433
  • Do not include the "https://" prefix in the URL.
  • RavenDB's PostgreSQL port number is by default 5433, and is configurable.
• Database
  Enter the name of the RavenDB database you want to retrieve data from.
• Data Connectivity mode
  Select the Import data connectivity mode.

Credentials

• Provide the credentials (user name & password) required by RavenDB to authenticate your Power BI client, and click Connect.

Retrieve Collections Data

The database's collections & documents will show once RavenDB is connected.

Collections

• Select the collection/s whose data you want to retrieve, and click Load or Transform.

Retrieved Collection Data

• Your data is loaded, and you can play with it as you wish.

Query RavenDB Using RQL

Instead of loading collections in their entirety, you can run RQL queries to import into Power BI just the data you're looking for.

RQL Query

• Server
  Enter RavenDB's URL and PostgreSQL port number.
  • Enter the URL and port number in the form: URL:Port
    E.g. - a.ravenpostgresql.development.run:5433
  • Do not include the "https://" prefix in the URL.
  • RavenDB's PostgreSQL port number is by default 5433, and is configurable.
• Database
  Enter the name of the RavenDB database you want to retrieve data from.
• Data Connectivity mode
  Select the Import data connectivity mode.

• Advanced options

  • Open Advanced options.

  • Enter your RQL query into the SQL Statement field.
    

    Avoid using ; in RQL queries.

    • The PostgreSQL library that Power BI uses to transfer your query to RavenDB, interprets the ; symbol as an instruction to split the query.
      To avoid splitting the query, please avoid using this symbol in it.
    • RavenDB queries can include JavaScript code, where ; is normally a valid operator.
      However, to avoid splitting your query, please avoid using the ; operator in JavaScript code as well.
      Using ; is optional in JavaScript, and omitting it will have no effect on your code.

    • RavenDB will throw the following exception if an erroneous query is likely to have been split:
      Unhandled query (Are you using ; in your query? That is likely causing the Postgres client to split the query and results in partial queries)

  • Click OK.

RQL Query Results

• Only the fields resulting from the RQL query will be imported to Power BI.
• One notable field is the rightmost json() field; we placed it there for irregular data items, should there be ones, that don't fit into one of the otherwise regular JSON arrays.