Certero API as a Power BI Data Source

This quick guide explains how to use the Certero OData API as a Power BI data source.

First, refer to the main article explaining the API:

Create your API key under the Administration menu:

The API Name will be the Power BI data source authentication user name and the Key will be the password.

In Power BI Desktop, choose Get data and find the OData feed option:

Using the Basic option, enter the base URL to your Certero application, appended with /api/odata

In the resulting data source Navigator , you'll now see the Certero data tables:

Selecting a table will prompt you for API credentials. Select the Basic option and enter the API key name and key for the user name and password, respectively:

From here, you'll draw upon your reporting and Power BI expertise to choose and transform the Certero data tables required for your reporting project.

Some useful things to remember:

When you load a Certero table for transforming you will of course see the table columns, and importantly, the OData model will also present related tables as columns that you can delete or keep & expand. In this example, for our AD User base table we're offered the AD Group Memberships and AD Org Unit tables on the right:

In the image above, Table indicates a one-to-many relationship, and if we expanded that we will likely duplicate some rows in base table. This might be undesirable, and you'd perhaps load such an additional table separately, leveraging Power BI's native table relationship management. Record in the image above indicates a one-to-one relationship and it's safe to expand this related table, choosing the columns you need for your reporting.

Be wary of creating circular relationships, e.g. if offered the base table again when expanding a related table.

Also be aware of the API load on the Certero SQL server database and configure your Power BI data source refresh schedule accordingly.