Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Levien
New Member

connecting a SQLite database

Hi,

 

I'm trying to connect to a (local) .db file, created in SQLite3.

Anyone any idea how this could work out?

Thanks & regards,

 

Levien

1 ACCEPTED SOLUTION
pkoetzing
Advocate III
Advocate III

Hi,

 

You need to install a SQLite ODBC driver (look here: http://www.ch-werner.de/sqliteodbc) on your local machine. Then "Get Data" - "ODBC" and enter "database=C:\mysqlite.db" as connection string. This works at least for the PowerBI Desktop.

 

/Peter

View solution in original post

13 REPLIES 13
AdB_umc
Regular Visitor

The sollution by pkoetzing is correct. You need to install the ODBC driver on you machine. Then you can connect to a SQL lite Db in PowerBi Desktop via the ODBC connection on your machine.

This even works in the PowerBi service: Publish the report in the service. Install a gateway on the same machine who has the ODBC connection. Make sure this machine is powered ON and the same user who used the ODBC connection is logged on and the PowerBi Service can refresh your data via the gateway. This is working stable in our organization for a couple of years now. The only downside: the user needs to be logged in on the Machine with the Gateway for the ODBC refresh to work. Other platforms (SQL server) might not have this problem, however we do not feel the need to change our setup, since ODBC connection works like a charm in our environment (with SAS software creating the dataset and PowerBi reporting it). 

Extra answer regarding the connection in the service since there still are questions regarding SQLlite and PowerBi alltough there are now numerous howto's:

sqlite - How to connect Power BI with the website database - Stack Overflow

 

pkoetzing
Advocate III
Advocate III

Hi,

 

You need to install a SQLite ODBC driver (look here: http://www.ch-werner.de/sqliteodbc) on your local machine. Then "Get Data" - "ODBC" and enter "database=C:\mysqlite.db" as connection string. This works at least for the PowerBI Desktop.

 

/Peter

Perfecto, me funcionó.

hi Sir,

We have a sqlite database on a heroku server.

I want to connect to the database from PowerBI Web using the PowerBI Dataflow. So, give me best solution.

Just to add something to Peters response - I needed to modify slightly as follows:

DSN: none

DRIVER={SQLite3 ODBC Driver};Database=C:\MDL_DASHBOARD.db;

This works for me. Thanks

JSN
Frequent Visitor

Hi, thx for your hint!

How can I use this to be loaded from a folder source? To be more precise how I can I combine the DB files then using this ODBC driver?

Thanks Peter. I found that odbc driver earlier indeed, but was marked by Norton as unsafe. Tried anyway, works fine. Indeed, at the moment only on local file, let's see for the futur!

Best regards, Levien

Anonymous
Not applicable

Yeah Peter that will work on a desktop only because its embedded.  If you want to publish online and set up a refresh, you'll be out of luck

Anonymous
Not applicable

If SQLite DB is in Linux server, is there a way to establish connection from Power BI Desktop in my windows machine to this SQLite DB in lunix server?

Not just establish connection but daily data refesh when clicked on refresh from Power BI desktop should also work?

 

Thank you

Yep - I realized that. But what do you actually mean by "embedded"? The sqlite.db is just a file. Of cause you need to have the proper protocol to understand the data - and this might not be obvious to the PBI Service, since sqlite isn't directly supported. But the same issue occurs with Microsoft Access databases and I actually expected the PBI Service to know how to handle their own proprietary format?

Anonymous
Not applicable

From my understanding SQLite is an embedded database and doesn't allow external connections.

SQLite can be an embedded database. But it does not need to be (only).

See earlier posts. The SQLite .db file is somewhere (in our case a storage server in the cloud for security reasons).

A specific machine (can be multiple machines) connects to that file via ODBC. For ODBC driver: look here: http://www.ch-werner.de/sqliteodbc.

All programs can connect to the SQLite database via ODBC on that machine (so Ms. Acces, Excel, PowerBi Desktop, etc.) and read and write to the SQLite database. (Yeah, succes)

The last step to make the PowerBi Cloud Service connect to the SQLite database is to install the gateway on the machine with the ODBC to SQLite. Test it, and all is done: you can automatically refresh your PowerBi information.

Necisities for auto refresh in the PowerBi Service: the user who installed the gateway needs to be logged on on the machine and the machine needs to be powered on offcourse. Try it, it really works. 

It all has very little to do with PowerBi and most of it is about installing and connecting via ODBC. So i included a picture of my ODBC setup.odbc.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors