Forum Discussion

Levien's avatar
Levien
Advocate I
9 years ago
Solved

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

  • pkoetzing's avatar
    pkoetzing
    9 years ago

    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

14 Replies

  • pkoetzing's avatar
    pkoetzing
    Advocate III
    9 years ago

    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

    • ColeStJohn's avatar
      ColeStJohn
      New Member
      4 years ago

      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;

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

      • pkoetzing's avatar
        pkoetzing
        Advocate III
        9 years ago

        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?

    • Levien's avatar
      Levien
      Advocate I
      9 years ago

      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's avatar
    Anonymous
    Not applicable
    9 years ago

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

    • AdB_umc's avatar
      AdB_umc
      Regular Visitor
      3 years ago

      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.

       

       

  • AdB_umc's avatar
    AdB_umc
    Regular Visitor
    3 years ago

    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

     

  • Hugoberry314's avatar
    Hugoberry314
    Regular Visitor
    1 month ago

    If you're stuck because you can't install an ODBC driver in your environment (no admin rights, locked-down machine), here's another route: I've written a pure Power Query/M function that parses the SQLite file format directly.

     

    No ODBC, no drivers, no dependencies at all.

     

    The SQLite file format is stable and well documented, and most of the databases I encounter are tiny, so a full ODBC driver install always felt like a big ask for what is ultimately just reading a file.

    You call it like this:

    let
    db  = SQLite(File.Contents("C:\path\to\your.db")),
    tbl = db{[Name = "my_table"]}[Data]
in
    tbl

    The first call returns a navigation table of all tables in the database; the Data column drills into each one. Since it only needs the raw bytes, it also works with Web.Contents or SharePoint.Files. Which means it can even refresh in the Power BI Service without a gateway.

    Code and documentation here: https://github.com/Hugoberry/PowerQuery-SQLiteReader

    Main caveats: it's read-only, does full table scans (no SQL pushdown), and won't see un-checkpointed data from WAL-mode databases. Details in the README. For small-to-medium .db files, it works great.