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.
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
- 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
- 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
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