Visualise SQLite Data in Power BI

SQLite + Power BI

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

ODBC DriversClick “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

Create DSN in ODBCNow 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

Load Data From ODBC DSN to Power BIClick “Default or Custom” tab then click “Connect”

ODBC DriverSelect the desired tables from the list then click either load

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

Power BI Desktop Relationship PaneNow 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

Import Data From ODBC to Power BI Using Connection StringClick “Default or Custom” tab then click OK

ODBC Driver and Connection String in Power BIThe rest is the same as previous method.

All done! Smile

6 thoughts on “Visualise SQLite Data in Power BI

  1. Hello, thanks for the great post. Is there a way to edit the connection to a different database file after creating it? Thanks!

  2. Hi, this is useful but I kept getting error “SQLSetConnectAttr failed”. Managed to figure out the problem – if any table in sqlite3 has a trigger, it’ll give this error. When I dropped all the triggers, it connected through successfully. May want to add this piece of info in your page here.

  3. Hello, I have installed DB SQLite 3.12.2 64bit, but I don’t have SQLite3 in the list, I have the windows 11. Is it because of the system, because I had SQLite3 when I was using Windows10.

  4. Hi all,

    I am facing issues with the SQLLite ODBC driver. I am able to create the 32 bit version, but unfortunately cannot use it with my 64-bit PBI.

    When I try to create the ODBC connection with installed 64-bit SQLLITE drivers I get the following error:

    The setup routines for the SQLite3 ODBC Driver ODBC driver could not be loaded due to system error code 193: .

    Any help is appreciated, because I am really stuck.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.