As the name implies, SQLite is a light weight transactional SQL database engine. It is open-source and free for use either for personal commercial. SQLite is widely used in mobile apps and many other desktop applications that need an internal light weight free SQL database. In this post I explain how to visualise SQLite data in Power BI.

Requirements

To be able to go through the process you need to meet the following requirements:

• Latest Version of Power BI Desktop (Current version: 2.52.4921.682 64-bit (November/2017))
• Install SQLite ODBC driver: Make sure you install 64 bit version if your Power BI Desktop is 64 bit version like mine!

Note: You may install both x32 and x64 bit versions of the driver if your other applications are in x32 bit.

• An existing SQLite database

Note: In case you just want to try this and you don’t currently have a SQLite database you can download a SQLite version of “Internet Sales” of AdventrueWorksDW2016 that I made available for you.

How it works

Like any other Power BI cases, it’s really easy to load data from an existing SQLite database to a Power BI Desktop model. You just need to use ODBC data connection and connect to a predefined “ODBC DNS” OR use a “Connection String”. I’ll explain both methods. After you load data to your Power BI Desktop, you create the relationships then you’re good to go and create flashy reports. Continue reading if you’re interested to an step-by-step guide to visualise SQLite data In Power BI.

Importing SQLite Data to Power BI Using ODBC DSN

• Open the correct version (x32, x64) of ODBC after you downloaded and installed SQLite ODBC Driver
• Click “Drivers” tab and make sure SQLite ODBC Driver(s) successfully  installed

Click “User DSN” tab then click “Add”

• Select appropriate driver from the list, in my case it is “SQLite3 ODBC Driver”
• Click “Finish”
• Enter a name for the data source
• Click “Browse” and locate your SQLite database then click OK

Now you successfully created an ODBC DSN that can be used in Power BI to connect to SQLite.

• Open Power BI Desktop
• Select “ODBC” from “Get Data” then click “Connect”
• Select the DSN you created earlier from the dropdown list then click OK

Click “Default or Custom” tab then click “Connect”

Select the desired tables from the list then click either load

After you imported data into Power BI you need to manually create the relationships between the tables

Now you’re good to go and create reports.

Importing SQLite Data to Power BI Using Connection String

• After you installed SQLite ODBC drivers, open Power BI Desktop
• Select “ODBC” from “Get Data”
• Select “SQLite3 Datasource” from the dropdown list
• Click “Advanced options”
• Now type “database=” followed by your SQLite file path then click OK

Click “Default or Custom” tab then click OK

The rest is the same as previous method.

All done!