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.
Step | Action | Screenshot |
1 | Open Power BI and choose Get Data. | |
2 | Choose Basic and use this URL structure: https://tenantname.odata.bluedolphin.app. Click OK. | |
3 | The following screen will appear: | |
4 | 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. Click Connect. | |
5 | The following screen appears. | |
6 | The data is now loaded. | |
7 | By default in the Objects table, only default fields for an object will be shown due to the performance. | |
8 | To access the full set of data (questionnaire fields), the query must be edited. In Power BI, click Transform data. | |
9 | In the Power Query Editor on the left side, select the Objects table. | |
10 | Then click Advanced Editor in the Query section. | |
11 | Change the line that starts with Source to: Source = OData.Feed("https://tenantname.odata.bluedolphin.app", null, [MoreColumns=true]), Remove any other argument in the statement. Click OK. | |
12 | The More Columns column has now appeared. | |
13 | 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. |
If you're encountering problems with credentials and access, try clearing your saved credentials. To do this, navigate to Transform data > Data source settings. Then, proceed to Global permissions and select Clear permissions.
For some tips on working with OData in Excel, take a look at this community post (including the comments).