How to connect to CARTO from external applications using PowerBI
This tutorial shows to connect with the CARTO database using the ODBC connection. We would strongly recommend reading the SQL direct connection article to know more about this feature before reading this one.
With the Direct SQL Connector, you can use the datasets in your CARTO account for your Power BI reports and dashboards.
- Windows 10 64 bits
- PowerBI Desktop Version: 2.79.5768.1082 64-bit (March 2020)
The steps for adding an ODBC System DSN to CARTO’s database can be followed in this article. Important: the database name in the DSN configuration must be empty. If a database name is included, cross-reference errors will incur while trying to access data.
Open up Power BI Desktop and select Get Data..Other/ODBC. Click on the “Connect” button. (Unfortunately, Using a PostgreSQL connector is impossible here because it is not possible to specify SSL certificates.)
- Select the DSN created in the previous step and click the “OK” button
Click “Connect” and specify the CARTO username and password (= the CARTO API Key). Power BI only asks for username and password the first time a DSN is used and does not use the credentials stored in the DSN. If, for any reason, we want to change the password (API Key), we will need to add a new DSN or rename the existing one, so Power BI asks again for credentials.
In the Navigator window, select the database schema corresponding to your CARTO username and check the table(s) that you want to use in your dashboard. When you select a table, you will see a preview on the right. Then click on the “Load” button.
- In the “Fields” panel, select the field(s) that you want to use in your dashboard and drag them to the dashboard page. If you select a field that can be geocoded (i.e. a country field), a map visualization will be created by default.