DuckDB file connection

[alibega]

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. 

Tried with ODBC, but I am doing something wrong I guess:

After registiering ODBC and indicating db path, PB does not recognize the ODBC driver.

Using :memory: does take me to another step where I can select it, but, ofc, it is empty.

Anyone has succeeded with DuckDB connection?

Thanks!

[LuanMoreira]

Hello @alibega !

Follow this steps and you'll solve this problem:

1. Download and install the ODBC driver: https://github.com/MotherDuck-Open-Source/duckdb-power-query-connector/releases/latest/download/duck...
2. Go to powerbi desktop -> File -> Options and settings -> Options -> Security -> Data Extensions, Enable "Allow any extensions to load without validadtion or warning"

3. Download the custom connector: https://github.com/MotherDuck-Open-Source/duckdb-power-query-connector/releases/latest/download/duck...
4. Put this .mez file in the custom connector directory (e.g.: C:\Users\Documents\Power BI Desktop\Custom Connectors)
5. Open your PowerBI Desktop, click on get data and search for Duckdb, set the database and other configs.

To working in Power Bi service follow this steps:

1. Install a gateway

2. Open services and find the On-premisses data gateway and open this (double-click).

3. Go to "log on" tab and click on "Local System Account" and check the "Allow service to intera...." flag.

4. Restart the service.

5. Open the gateway, do your login and in the connectors tab put your custom connectors path (as the step 4 above).

6. Go to your browser, login in your Power Bi service account, click on settings -> Manage connections and gateways -> On-premisses data gateways

7. Click on the three points in the gateway -> settings -> check "Allow user's cloud data source to refresh through..." and "Allow user's custom data connectors..." -> Save

8. Create a new connection -> select the gateway -> select duckdb -> put you database path -> Anonymous auth -> check skip test connection -> create.

Now you'll be able to refresh the data.

for more information see:motherduckdb/duckdb-power-query-connector: DuckDB Power Query Custom Connector by MotherDuck

best regards!

[TheoC]

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]

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:

• Make sure you have the DuckDB ODBC driver installed on your system. If not, download and install it from the DuckDB website (https://duckdb.org/docs/api/odbc.html).

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.

[jazzking1202]

This works in Report Builder or SSRS, it doesn't work in Power BI Desktop. It connects but doesn't show any tablesNo files on the right panel

[jaryszek]

@jazzking1202 

exactly. It is not working. 

Anybody solve the issue?

Best,
Jacek

[LuanMoreira]

Yes! I've solved, check my comment and try the steps.