Forum Discussion

alibega's avatar
alibega
New Member
3 years ago

DuckDB file connection

Hello!   Recently started using DuckDB with Python. It really is very fast and comfortable. But now I need to connect the DuckDB file, ie database file, to PB, but can't seem to figure out how.  ...
Need Help
TheoC's avatar
TheoC
Community Champion
3 years ago

Hey alibega 

 

Would love to know whether the following works - it's a straight dump out of ChatGPT4 (just in case you couldn't tell it was written by a robot).  I've never worked wsith ODBC but wanted to test ChatGPT's understanding of this stuff...

"Connecting DuckDB to Power BI can be achieved using the ODBC driver. To set up the connection successfully, follow these steps:

  1. Install the DuckDB ODBC driver: Download and install the DuckDB ODBC driver from the DuckDB website (https://duckdb.org/).

  2. Configure the ODBC driver: After installing the driver, configure it by creating a Data Source Name (DSN). Here's how you can do it:

    • Open the ODBC Data Source Administrator on your machine (you can search for it in the Start menu).
    • Select the "User DSN" or "System DSN" tab (depending on your preference).
    • Click the "Add" button to create a new DSN.
    • Choose the DuckDB ODBC driver from the list of available drivers.
    • Provide a name for the DSN and specify the path to your DuckDB database file (.db extension).
    • Complete the configuration process by following the on-screen instructions.

  3. Connect to DuckDB in Power BI: After configuring the DSN, you can connect to DuckDB in Power BI using the ODBC connector. Here's how:

    • Open Power BI Desktop.
    • Click on "Get Data" in the Home tab.
    • Select "More..." to access additional data sources.
    • Choose "ODBC" from the list of connectors.
    • In the ODBC dialog, select the DSN you created in the previous step.
    • Click "Connect" to establish the connection.
    • Power BI will prompt you to enter credentials if authentication is required.
    • Once connected, you can select the desired tables or write custom queries to import data from DuckDB into Power BI.

If you encounter any issues during the process, double-check the ODBC driver installation and ensure that the DSN configuration matches the DuckDB database file you want to connect to. Additionally, make sure you have the necessary permissions to access the database file."

 

Hope it's of use!


Theo

pheller's avatar
pheller
Regular Visitor
2 years ago

SQLite3 works but DuckDb fails (not Marked????) I have an existing DuckDB database which I would like to using excel PowerPivot
This is from Claude.ai 3
If you have an existing DuckDB database that you want to use with Excel PowerPivot, you can follow these steps to connect to the database and import tables into your PowerPivot model:
  1. Ensure DuckDB ODBC Driver is Installed:
  1. Open Excel and Navigate to PowerPivot:
  • Open Microsoft Excel and navigate to the PowerPivot window (Data -> Manage Data Model).
  1. Connect to DuckDB Database:
  • In the PowerPivot window, go to "Home" -> "Get External Data" -> "Other Sources" -> "ODBC".
  • In the "From ODBC" dialog box, select "DuckDB" as the data source and click "Next".
  • Enter the connection details for your existing DuckDB database:
  • Data Source Name (DSN): You can provide a name for the data source connection.
  • Database: Specify the path to your existing DuckDB database file.
  • Click "Next" to proceed.
  1. Select Tables to Import:
  • In the "Navigator" dialog box, you will see a list of tables available in your DuckDB database.
  • Select the tables you want to import into your PowerPivot model by checking the corresponding checkboxes.
  • You can preview the data in each table by clicking on the table name.
  • Click "Load" to import the selected tables into PowerPivot.
  1. Build and Customize the Data Model:
  • Once the tables are imported, you can create relationships between them based on common columns to establish a logical data model.
  • You can also create calculated columns, measures, and hierarchies using DAX (Data Analysis Expressions) to enhance your data model and perform calculations.
  1. Use the Data Model with DAX:
  • After building the data model, you can create PivotTables, PivotCharts, and other visualizations in Excel using the imported data from DuckDB.
  • Use DAX functions and expressions to perform calculations, aggregations, and data analysis on the imported data.
Here's an example of a DAX measure that calculates the average price of products:
Average Price = AVERAGE(Products[Price])
This measure calculates the average of the "Price" column from the "Products" table.
Remember to save your Excel workbook to persist the PowerPivot model and any changes you make to it.
By following these steps, you should be able to connect Excel PowerPivot to your existing DuckDB database, import tables, and build a data model for analysis and reporting purposes using DAX.