Blogs

• Connect Microsoft Power BI Server to Insight and tipsLogDb databases to query the tables and plot graphs using SQL queries externally.

• The Power BI Desktop server can be connected by either using the PostgresSQL connector ( available by default with the server Installation ).
• Or connect the server using the ODBC connector using ODBC drivers from third party vendors or from Postgres.

Connecting to databases using the default PostgreSQL Connector:

1. Navigate to GetData > More

2. Select PostgreSQL Database

3. Specify the Server Details including the database name, and user creds (appexternal account).

1. You could choose either of the option from the drop-down depending in case if would like use the same data source for tipsLogDb then choose option 1.

4. As Power BI uses a strict connection for the SSL mode. Hence, while connecting to CPPM insight or tipslogdb using the default PostgresSQL connector will throw the below error:

5. Hence, will have to export the CPPM db Cert from UI and import it under the Trust Root Certificate Authority under the User Space
   1. Export the cert from CPPM while navigating to:

Administration » Certificates » Certificate Store » Select the Server » Database Certificate.

We could export the cert without specifying the password to get the .pem format and later change it to .crt or .cer to import it in Trusted Root CA list.

Choose the highlighted folder while importing the certificate.

6. Once imported retry the connection from the Power BI and on successful connection it should populate the tables

Connecting to databases using ODBC Connector:

In case if the Power BI Desktop doesn't have the PostgreSQL Connector ( running on older code ) or would like use the existing data source using the ODBC drivers for the connection.

1. Download the ODBC driver from the PostgreSQL Driver source ( Using Postgres ODBC driver for reference, should work with the other third party drivers supported by Power BI ).

https://www.postgresql.org/ftp/odbc/versions/msi/

Choose : psqlodbc_09_06_0500.zip ( Tested and confirmed against CPPM running 6.9.3 ).

2. Extract and run the application to Install the driver package for both 32-Bit and 64-Bit arch.
3. Once, drivers are installed, press Win + R and run 'odbcad32' to run the Administrator tool

Depending on the arch, where the application is running on it will load the respective package.

4. Choose the desired space as whether would like to create source for the logged in user or the system.

5. Add > Select PostgreSQL Unicode 64

6. Configure the data source with the IP, account details and port no ( 5432 ).

7. Make sure to have the SSL Mode set to 'allow' or 'verify-full' ( will require to have the db cert under <user>/appdata/roaming/postgresql/root.crt ) as oppose to default 'disabled' else will get the below error while connecting to the source via Power BI.

8. Once setup is done, launch the Power BI Desktop app, and click on "GetData > More".

9. Search for ODBC:

10. Select the Data Source configured above from the dropdown

11. Choose Default or Custom instead of Database tab and connect ( No need to provide creds as it will default to DSN configured earlier ).

12. Once, connected should see tables populated from the insightdb

Same process can be followed to connect to tipsLogDb

Note:

By Default most of the applications has SSl mode set to 'allow' for the SSL connection between the application like DB Visualizer for PostgreSQL. Hence, it doesn't require importing the cert or changing the SSL mode while connecting to the CPPM databases.

• Connection using PostgreSQL driver

• Connection using ODBC Connector