Integrations → Microsoft Power BI

Connect Microsoft Power BI

You can easily connect Microsoft Power BI Desktop to your Incorta cluster. Using the SQL interface (SQLi) and the PostgreSQL protocol, you can connect Power BI to the Analytics Service.

To learn more about Microsoft Power BI, visit Power BI.

Enable the SQL App in the Cluster Management Console (CMC)

In order for a Power BI Desktop application to connect to Incorta over the SQLi, Apache Spark must be running and properly configured for Incorta. In addition, you must enable the SQL App in the Cluster Management Console (CMC).

The default port for the CMC is 6060. To sign in to the CMC, first visit your CMC host at one of the following:

• http://<Public_IP>:6060/cmc or https://<Public_IP>:6060/cmc
• http://<Public_DNS>:6060/cmc or https://<Public_DNS>:6060/cmc
• http://<Private_IP>:6060/cmc or https://<Private_IP>:6060/cmc
• http://<Private_DNS>:6060/cmc or https://<Private_DNS>:6060/cmc

Sign in to the CMC using your administrator username and password. To enable the SQL App in the CMC, follow these steps:

• In the Navigation bar, select Clusters.
• In the cluster list, select a Cluster name.
• In the canvas tabs, select Cluster Configurations.
• In the panel tabs, select Server Configurations.
• In the left pane, select Spark Integration.
• In the right pane, toggle Enable SQL App to enabled.

Connect Power BI Desktop to Incorta

Using the PostgreSQL protocol and the Incorta SQLi, Incorta exposes a tenant as a database via a connection to the Incorta Analytics Service.

Here are the steps to connect the Power BI Desktop application to an Incorta tenant:

• In Windows, open Power BI Desktop.
• In the Ribbon, in the Home tab, in the External Data group, select Get Data.
• In the Most Common menu, select More...
• In the Get Data dialog, search for PostgreSQL.
• Select PostgreSQL database and then select Connect.
• In the PostgreSQL database dialog, for Server, enter the host IP address for the Incorta Analytics Service using port 5436 by default:
  • <Public_IP>:5436
  • <Public_DNS>:5436
  • <Private_IP>:5436
  • <Private_DNS>:5436
• For Server, specify the Tenant name.
• For Data Connectivity mode, select DirectQuery.
• Select OK.
• In the PostgreSQL database dialog, for the given Incorta tenant...
  • for User name, specify the Incorta login name
  • for Password, specify the Incorta password for the login name
• Select Connect.
• In the Encryption Support dialog, select OK.

Here are the steps to edit the User name / Password permissions for an Incorta PostgreSQL data source in Power BI Desktop:

• In the Ribbon, select File.
• In the menu, select Options and settings and then select Data source settings.
• In the Data source settings dialog, select the specific Incorta PostgreSQL connection.
• Select Edit Permissions...
• In the Edit Permissions dialog, in Credentials, select Edit...
• In the PostgreSQL database dialog, for User name, specify the Incorta login name, and for Password, specify the password for the Incorta login name.
• Select Save.
• In the Edit Permissions dialog, select OK.
• In the Data source settings dialog, select Close.

Creating Analyses with Power BI Desktop

Here are some recommendations for creating analyses with Power BI and Incorta:

• Model data in an Incorta schema and not in Power BI.
• Create analyses from Incorta business schemas views.
• Do not define a primary key for an Incorta Analyzer View.
• For each workbook, use a single Incorta Analyzer View.
• Avoid using multiple Incorta Analyzer Views in a single workbook as this may require that you create joins between views in Power BI.