 Power BI agent

The Power BI agent is a destination-only agent that enables you to load data from the Staging database (a MySQL source) into a Power BI dataset.

Add a connection

To add a connection for a Power BI agent:

1. In the navigation panel, select  Admin Setup.
2. Select a text agent or peer connection, and click  >  Add Connection.

3. Connection Name must be unique. Up to 40 characters.

     Note:  All the parameters listed below are required; none have character restrictions.

4. Client ID: Up to 36 characters.
5. Client Secret: Up to 50 characters.

6. Resource URL: Up to 80 characters.

   The default URL for Power BI cloud is https://analysis.windows.net/powerbi/api.

7. Tenant: Up to 36 characters.

8. Click Verify Credentials.

     Note:  To obtain the required settings for the connection screen, refer to the Microsoft documentation on setting up a service principal, adding the service principal to a security group, enabling service principal access in Power BI, and adding the service principal to your workspace.

9. Click OK.

No query

Power BI is a destination-only agent; a query cannot be set up for this agent.

Extract the data from FP&A or FP&A Plus

To extract the data and transfer it into Power BI, in Data Integration in the Job page, you create a task.

More precisely, the task you create extracts the data from a staging database and loads the data into a Power BI dataset.

In the Data Integration workflow, the procedure below comes after creating the following:

• your site
• your Power BI connection (see above)
• a MySQL connection (your source)

  Example:  You can export data from a Financial Planning & Analysis Cube into the Staging database using a Process Manager export process, and then load this data to a Power BI dataset using the Power BI agent.

To create a Power BI task

1. In the navigation panel, select  Job Definitions.
2. In the Job Definitions panel, select an existing job or create one.
3. Click  >  Add Step; name the step.
4. Click  >  Add Task.
5. Task name: Up to 250 characters
6. Under Source, from Connection select the MySQL connection that you want to use.
7. From Query select the specific source query that you want to run.

8. The Filters table lists parameters that you can use to filter the query. Accept the default values or provide your own.

     Tip:  Hovering over a Value cell reveals its expected input/format.

9. Under Destination, from Connection select a Power BI connection.

10. From Group (workspace) name select a workspace.

    If your workspace does not appear in the list, this is because the application service principal does not have access to that workspace.

    To learn how to add the service principal to your workspace, refer to the Microsoft documentation.

    Select an option for loading the data into the Power BI datasets:

    • Create/Refresh: Select to do one of the following:

      • To create a new dataset and table to load with the source data, type their names in Dataset name and Table name.

      • To refresh the data in an existing dataset/table with new source data without having to delete and recreate the dataset/table, from Dataset name and Table name select the options existing_dataset and existing_table.

    • Append: Select to have the source data appended to the existing data in the dataset and table.

        Note:  You can only have one table per dataset; you cannot use two or more tables to create or append a dataset.

11. From Dataset name, select a dataset from the chosen workspace.

    (If your dataset does not appear, this is most likely because the dataset was not created by the Financial Planning & Analysis data integration process.)

12. From Table name, select a table from the chosen dataset.
13. Under Logging, to add extra debugging details to the Job Log, turn on Verbose logging.