Microsoft Power BI in Immuta
Audience: Data Owners and Data Users
Content Summary: Microsoft Power BI is a tool for exploring data, performing analysis, and creating visualizations. Power BI can connect to the Immuta Query Engine and consume data via Immuta's SQL Access Pattern. This page outlines how to install and connect Power BI to Immuta for Windows and Azure.
The primary Power BI mechanism for working with data is to use the Power BI desktop application for Windows to build analytics and dashboards.
Before connecting Immuta to Power BI in either Desktop or Azure, you must first install and configure the following:
To connect to PostgreSQL, Power BI requires a free and open source helper application, Npgsql.
Obtain the MSI installer for version 4.0.10 of Npgsql. Because of bugs in recent versions of Npgsql, 4.0.10 should be used.
After you've downloaded the MSI file, run it. You'll be taken through a standard installation wizard. Accept the license and choose the Will be installed on local hard drive option under Npgsql GAC Installation.
Note: If the Power BI application was open while Npgsql was installed, you will need to close and reopen Power BI to enable PostgreSQL connectivity.
Power BI Desktop
Power BI can connect to the Immuta Query engine leveraging Power BI's PostgreSQL database connector. To connect Immuta to Power BI Desktop see the following:
- Update Windows Trusted Root Certificates
- Connect to Immuta
Update Windows Trusted Root Certificates
Best Practice: Require SSL Connection
Require an SSL connection to the Immuta Query Engine. To do this, Power BI requires full verification of the server's certificate. In order to verify the certificate, the root certificate authority for the server's certificate must be loaded into your Windows operating system.
It is likely that the root certificate authority for the server's certificate is already trusted by your operating system. If it is not, you'll need to obtain the root certificate and install it per Microsoft's instructions on Managing Trusted Root Certificates.
Connect to Immuta
To connect Power BI to Immuta data sources,
- Open Power BI, select the Home menu, and click Get Data.
In the dialog window that appears, scroll in the right pane to PostgreSQL database and select that option as shown in the image below.
Next, you'll be prompted to enter connection information. You can find this information in your Immuta Profile page, under the SQL Credentials tab.
- If your Immuta Query Engine is not running on
port 5432, you'll need to append that port. For example, if the port is 5433,
you would append
:5433to the hostname you enter.
- For database, enter
- If this is your first time connecting Power BI to the Immuta Query Engine, you'll be prompted for your username and password. Note that this is the SQL username and password set either in your Immuta profile or in a project within the Immuta UI.
- Finally, select the tables from which you'd like to query data.
Additionally, when establishing relationships between data sets
in Power BI, Power BI does not always create SQL
JOIN queries. Instead, it
typically will fetch multiple sets of data and perform JOIN operations within
Power BI itself. As this leads to extra latency in data transfer, and potentially
joins on incomplete data sets, you may want to import the data into Power BI
with a JOIN statement.
Power BI for Azure
Power BI running in Azure can connect to an Immuta instance running within your enterprise or in the cloud via Microsoft's on-premises data gateway. The data gateway can be installed in personal mode or enterprise mode.
Best Practice: Enterprise Mode
Run the gateway in enterprise mode. Personal mode is useful for initial testing on a single laptop without the need for administrative privileges.
Note: Once data is exposed within Power BI for Azure, data sharing must be managed by Power BI for Azure access and data management features.
Follow Microsoft's instructions for installing the data gateway , and choose the appropriate operating mode: personal or enterprise. Be sure that the computer on which you are installing the data gateway has access to the internet on standard web ports and network access to Immuta's Query Engine, typically port 5432 on the server(s) on which Immuta is installed.
Like Power BI Desktop, the Power BI Data Gateway requires Npgsql to be installed on the machine running the data gateway. Follow the Installing Npgsql instructions found above.
Once installed, manage the gateway online by going to Settings and selecting Manage Gateways. Personal gateways will not be accessible in Power BI until a data source is shared by a local Power BI for Windows installation.