Integrate Power Query with Astra DB Serverless
Microsoft Power Query is a data preparation and transformation ETL engine that connects to various data sources.
Power Query uses Connectors to connect data sources. To connect Power Query to Astra DB, you use an ODBC connector paired with the DataStax ODBC driver for Apache Cassandra®.
This guide explains how to configure the ODBC connector for Astra DB in Power BI Desktop. You might need to modify these instructions for other platforms, such as Power Query Desktop or command line tools.
Prerequisites
-
You have an active Astra account.
-
You have created a Serverless (Vector) database.
-
You have created an application token with the Database Administrator role.
-
You have downloaded the Secure Connect Bundle (SCB) for your database and noted the path to the downloaded
secure-connect-DATABASE_NAME.zipfile. -
Optional: You have Power BI Desktop installed. This guide uses Power BI Desktop. If you use a different tool with Power Query, you might need to modify some of the instructions.
ODBC custom connector
Create and configure an ODBC connector to your Astra DB database, and then connect to it from Power Query.
-
Download the DataStax ODBC driver for Cassandra. This example uses the Windows 64-bit version.
-
Run the ODBC driver executable and follow the installation instructions.
-
Open the ODBC Data Source Administrator application.
-
On the System DSN tab, select the DataStax ODBC Driver for Apache Cassandra, and then click Configure. If the driver isn’t listed, click Add, and then select the driver.
-
Enter the following connection parameters:
-
Authentication Mechanism: Enter
Cloud secure connect bundle. -
User Name: Enter
token. This must be the exact, all-lowercase word token. -
Password: Your application token, which is prefixed by
AstraCS:. -
Connection Bundle: The path to your database’s SCB.
-
Default keyspace: The keyspace in your database that you want to connect to, such as
default_keyspace.
-
-
Click Test. A valid configuration returns a
Test completed successfullymessage. -
Click OK to close the configuration window.
Next, use the custom connector in Power BI.
Enable ODBC custom connector in Power BI
-
In Power BI Desktop, create a new report.
-
Select Get Data from Another Source.
-
In the connectors list, select ODBC, and then click Connect.
-
Make sure the datasource name is the same datasource that you configured in the ODBC Data Source Administrator application, and then click OK.
Now you can load your database’s tables in the Power Query Navigator.
-
To load data into Power BI, select a table, and then click Load.
-
To publish your data to Power BI, in the Power BI Desktop menu, select Publish, and then create a
.pbixreport. -
Select the workspace where you want to publish the report, and then click Select.
Verify the connection
Confirm that the Power BI Service can read changes from Astra DB Serverless.
Power BI now uses the term Semantic Model to refer to the dataset that is published to the cloud workspace.
-
In your Power BI workspace, find your dataset, and then click refresh Refresh.
The first refresh always fails because there are no gateway credentials.
-
In your dataset, click Schedule Refresh.
-
Click Data Source Credentials, click Edit Credentials, and then and enter your credentials:
-
User name: Enter
token. -
Password: Enter your application token.
-
-
Click Sign in.
-
In your Power BI workspace, find your dataset, and then refresh Refresh again.
The dataset now pulls the latest data from Astra DB Serverless.
