Connect Power BI with SQLite and Primavera P6

The Power BI does not have a Certified Connector for the SQLite, and in this article, I explain how to connect Power BI with SQLite.

Why SQLite and Primavera P6?

Primavera P6 has two products since version 8:

1. Primavera P6 Professional Project Management (P6 PPM)

2. Primavera P6 Enterprise Project Portfolio Management (P6 EPPM)

PPM is Windows-Based, and EPPM is Cloud-based, but there are more differences that are not part of this article.

Connecting the Power BI with the P6 EPPM is more straightforward; you need the Server address [Oracle or SQL] and access to it, but the default database for the P6 PPM Standalone is SQLite, and because of that, I selected this topic to feed two birds with one seed.

Continue reading if you’re interested in a step-by-step guide to connecting Power BI with SQLite and P6 Standalone.

Connector Solution?

To connect the Power BI to SQLite/P6 PPM, you need to use the ODBC [Open Database Connectivity] connector.

Update ODBC drivers

Check ODBC drivers and make sure SQLite driver has been installed:

• Open the Run dialog box by pressing Windows+R, type odbcad32 and click OK

• Go to the Drivers tab and check drivers’ names.

If the SQLite is not in the list, it needs to be downloaded and installed:

• Open this link: http://www.ch-werner.de/sqliteodbc/

• Download and install the current version. (install x64 version if your Power BI Desktop is x64)

• Check the ODBC drivers; you can find SQLite divers in the list now.

You can create, edit, and browse SQLite databases with SQLiteStudio.

Add a new DSN [Data Source Name] for your SQLite or Primavera P6 database

ODBC has 2 DSN types, User DSN and System DSN. A User DSN is only accessible by the user who created it on the workstation, but System DSN is accessible by any user logging into the workstation.

To add a new DSN for your database, follow these steps:

• On the User or System DSN tab, click on the add button

• Select the SQLite3 ODBC Driver and click on Finish

In the configuration tab:

• Enter a name in the Data Source Name field

• Click on the Browse button and select your SQLite file

• Click OK

For Primavera P6, you can find your Database file address in the database configuration tab.

You can add DSN for every SQLite or P6 standalone database you have in the same way.

After adding a DSN, open the Power BI desktop, click on the Get Data, click on More, and select ODBC.

Select your DSN from the list, and click OK.

You can see the list of the database's tables in the Navigator, select the desired tables and load data to the Power BI.

Now you can create your report with imported data.

Connect Power BI with SQLite or Primavera P6 Data Using Connection String

After installing SQLite ODBC drivers, Select ODBC connector on Power BI, then select SQLite3 Datasource from the dropdown list, open the Advanced options and type:

“database= your SQLite file path", then click OK.

Enter User Name and Password and click OK.

(If there is no Credential, select Default or Custom tab, and click OK)

Please share your comments or questions with me in the comment section below.