Connecting a BI tool to an OData feed
When the OData (Open Data Protocol) feature for your BlueDolphin tenant has been activated, it will be possible to get the data from BlueDolphin into any reporting product that can source data from an OData feed and support basic authentication. The examples are Microsoft Power BI and Microsoft Excel.
Note: Currently, the BlueDolphin repository data that is available through OData is limited to objects, relationships, object questionnaires, and relationship questionnaires. Also, please note that relationship questionnaires are not yet available in history.
In this article, we will demonstrate the use of OData in Microsoft Power BI Desktop.
Open Power BI and choose Get Data.
|2||Choose Basic and use this URL structure: https://<sitename>.odata.bluedolphin.app. Click OK.|
The following screen will appear:
In the field Username, fill in the value that can be found in BlueDolphin's Admin > General under Database.
In the field Password field, fill in the API key that you created or create a new key with the OData feed scope.
For more information on how to create a key, see here.
The following screen appears.
|6||The data is now loaded.|
By default in the Objects table, only default fields for an object will be shown due to the performance.
To access the full set of data (questionnaire fields), the query must be edited. In Power BI, click Edit queries.
In the Power Query Editor on the left side, select the Objects table.
Then click Advanced Editor in the Query menu.
Change the line that starts with Source to:
Source = OData.Feed("https://<sitename>.odata.bluedolphin.app", null, [MoreColumns=true]),
Remove any other argument in the statement.
The More Columns column has now appeared.
You can now select the columns that you want to include.
Columns have a specific naming syntax:
<Questionnaire name>_<Field name>, with all spaces removed from the questionnaire and field name.
Select the desired columns and click OK.
|14||Selected columns are now available in the result.|
For some tips on working with OData in Excel, take a look at this community post (including the comments).