Connect Microsoft Power BI

Connect Microsoft Power BI

You can easily connect Microsoft Power BI to the Incorta Unified Data Analytics Platform. The Power BI Desktop connection to Incorta UDAP uses the Incorta SQL interface (SQLi) and the PostgreSQL protocol. To learn more about Microsoft Power BI, visit Power BI.

DEPRECATION NOTICE: Incorta Power BI Connector

Incorta deprecated the Power BI Connector in favor of Power BI’s robust support for the PostgreSQL protocol.

About the Incorta SQLi

The Incorta Analytics Service exposes the Incorta SQLi over the PostgreSQL protocol. The Incorta SQLi manages how to process SQL queries.

Incorta natively handle queries that conform to Incorta’s support for left outer joins. For query shapes with inner joins, full outer joins, and right outer joins, Incorta directs the query processing to Apache Spark.

By default, Incorta’s SQLi supports two port addresses:

  • Port 5436 is the default port for accessing in-memory direct data mapping schemas and tables for a given tenant.
  • Port 5442 is the default port for accessing on-disk Apache Parquet tables for given tenant using Apache Spark.

The Incorta SQLi automatically determines how to process queries over port 5436, meaning the SQLi will direct queries to Apache Spark internally when required.

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).

To sign in to the CMC, visit your CMC host at one of the following:

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

The default port for the CMC is 6060. 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 UDAP

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 UDAP:

  • 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 view.
  • For each workbook, use a single Incorta view.
  • Avoid using multiple Incorta views in a single workbook as this may require that you create joins between views in Power BI.
© Incorta, Inc. All Rights Reserved.