One of the great things about the latest major version of Primavera P6 is how users can harness the simplistic power of the SQLite database platform. SQLite is a self-contained serverless database engine and was introduced as a database option in P6 8.4. It is truly the perfect database solution for the single user of P6 who doesn’t need the major overhead of a fully-fledged database platform.

Whilst this is certainly an advanced topic, we're going to show you how to connect to a SQLite database and extract information directly to Excel. This neat trick can help you pave the way to some really neat, real-time dashboard reports for your next project.

In this example, we have a SQLite database in use for our projects which we want to connect to.

 

Lets create a new local alias

DB Name

And tell P6 that we want to use SQLite:

DB Name

The file type is a "*.db" file which we've chosen to store in My Documents:

file

Once we finish setup, the new connection is available in the data source selection dialog when we log into P6:

Connections

 

Then, lets install a SQLite ODBC Driver

Windows does not have a native driver to access the SQLite database format 'out of the box'. So we need to download and install a SQLite ODBC driver. For this example we have downloaded an ODBC driver from http://www.ch-werner.de/sqliteodbc/.

Note: Because we are using Excel as a 32 bit application we've chosen to download the 32-bit driver.

This will create a new 32 bit ODBC end-point which we will use to connect to the file.

 

Create a Excel Data Source

We then Launch the Power Query Editor to start grabbing data from the database:

PQ 1

Once the window opens select to create a new ODBC type datasource:

PQ 2

We will then be prompted to define the type of data source we wish to create. In our case we will now have a 32-bit ODBC driver available for SQLite connections:

PQ 3

In the Connection String field we need to define the connection settings for the driver to use. Here's an example:

DRIVER=SQLite3 ODBC Driver;Database=your\file\path\here\filname.db;LongNames=0;Timeout=1000;NoTXN=0;

Clicking OK then brings you to the Credentials view:

PQ 4

We do not need to specify any credentials for the SQLite database, so select "Default or Custom" and then click on Connect.

This will bring you to the Table selection part of the Query!

table select

You are then off and running ready to start building queries for those ever-meaningful dashboards.

query table

 

Find out how GBA Projects can help your project perform.

Contact us.

Clients

  • 01-santos-logo.jpg
  • 02-bhp-logo-new.jpg
  • 03-hansen-logo.jpg
  • 04-sydney-water.jpg
  • 05-beach-energy.jpg
  • 05-kbr-logo.jpg
  • 06-aurecon-logo.jpg
  • 08-riot-tinto-logo.jpg
  • 09-sa-govt-logo.jpg
  • 10-spotless-logo.jpg
  • 11-built-environs-logo.jpg
  • 12-dyno-logo.jpg
  • 13-sa-power-logo.jpg
  • 14-incitec-logo.jpg
  • 15-electranet-logo.jpg