Connecting to Power BI Datamart and PowerApps Dataverse’s SQL Server from Desktop Tools and External Services

The main appeal of Power BI’s new Datamart capability is arguably the fact it’s including a SQL Server instance that’s optimized for analytical purposes and is fully managed yet still exposes its content to the outside via a SQL endpoint. This is a welcome change from the approach Microsoft took with dataflows, whose underlying data lake storage is not exposed to the outside unless you Bring Your Own Storage, a fairly tedious exercise that adds costs on top of your Power BI licenses.

Your connection string will look something like this:

abunchofrandomlettersandnumbers.datamart.pbidedicated.windows.net

Armed with this string you’d think you can connect easily to the database, but it turned out to be hit-and-miss as of the early Public Preview circa June 2022. At the end of January 2023 Microsoft announced that connectivity had extended to any SQL client, though I’m still struggling on that front. Before you even try to connect, note that Private Endpoints are not currently supported.

Power BI Datamart SQL Desktop & Cloud Connectivity Options

Here’s what I found after several rounds of tests and googling, starting with desktop tools:

  • I had no issue connecting from Power BI Desktop after choosing SQL Server as the source (not Azure SQL) then authenticating with an organizational (i.e. Microsoft) account that has access to the datamart.
  • Similarly I could easily connect from Excel Desktop.
  • Azure Data Studio wouldn’t work until I updated it to the latest version (1.36.2 at the time), at which point the connection could be established. No need to specify the port, use AAD (Universal with MFA support) authentication, leave everything else to default or empty.
  • SSMS 18 GA works as does SSMS 19 GA (SSMS 19 Preview did not because of a bug).
  • I couldn’t connect with Dbeaver even though it has several AAD options. Meanwhile HeidiSQL doesn’t seem to support AAD authentication.
  • I was not able to set up an ODBC DSN despite trying various AAD login options with the v18 SQL ODBC driver, everything is timing out.
  • If you can’t connect with any of the aforementioned desktop tools then check that your local firewall is not blocking port 1433.

The June 2022 version of Power BI Desktop added connectivity to Datamarts via the user interface, but this listed the auto-generated Direct Query datasets, not their underlying databases. So if you use this connector you’ll create a Live Connection report, not a full-fledged data model. With the July 2022 release, browsing datamarts from Get Data > Datamarts surfaces up data hub info and lets you choose whether to connect to the datamart’s dataset or to its SQL endpoint.

Well done!

Let’s now look at cloud services:

  • I could connect without a hitch from a Power BI dataflow. Do I want to put a dataflow downstream of a datamart? Not sure about that, but that’s a consideration for another day.
  • In a PowerApps dataflow I was able to connect, even across tenants, as of January 2023. This used to fail back in mid 2022. Make sure to sign in when you set up the connection, you can use “master” as the database name.
  • A datamart’s database is not listed under “SQL Databases” in the Azure portal.
  • Power BI is not listed in the list of resources that support managed identities so I doubt that you can connect from an Azure Function or Azure Data Factory. If that’s somehow possible, please comment below!

Regardless of the tool, once you connect you’ll see a database with a somewhat ugly name. Within it, you’ll see nothing under Tables, everything is exposed via Views where you’ll find your data (prefaced with the “model” schema) as well as metadata about any relationships you might have created in the service.

You’ll only be able to run DQL queries so you won’t be able to, say, create your own views via SQL. People have been clamoring for DDL and DML support, and it sounds like that’s coming soon.

I’ve been advising Power BI developers to get some SQL performance-tuning skills, it will be interesting to see what’s automatically managed for you vs. what you might be able to audit or even control yourself.

So Which Tool Should I Use?

PQ’s SQL

For ad hoc queries you can use the Visual Editor in the datamart service – which generates SQL queries via Power Query – or write your own SQL queries. You can access visual queries by right clicking on your step and selecting “View data source query”. It’s now possible to save visual and SQL queries, which was not the case at Preview launch. That said, you can be more productive using a desktop tool.

Azure Data Studio is a good choice as it’s free, relatively lightweight, and cross-platform. SSMS doesn’t add a lot of value in this instance as its administrative and user management tools are mostly irrelevant to the datamart’s already tightly managed SQL server. But of course it’s totally fine to use SSMS if you already have it installed. I noticed that Query Store is enabled by default in a datamart, so it looks like there are valid use cases where SSMS is superior to ADS, and I’ll revisit this in the future.

The Datamart’s database is managed for you, but it’s not an entirely opaque black box. Pictured here: Query Store.

In Azure Data Studio you can save queries individually as .sql files or put your favorites in a SQL notebook like pictured below:

Bonus: Dataverse Connectivity Options

I like to think of Dataverse as Datamart’s peer on the Power Apps side, as they’re both SaaS services meant to provide easy-to-provision SQL options for business users. Dataverse takes care of OLTP use cases while Datamart is an OLAP package.

Dataverse comes with a TDS (SQL) endpoint which allows read-only queries from:

  • SSMS 18.4+
  • Power BI dataflows
  • Power BI datasets
  • Power BI datamarts
  • Excel Desktop

Azure Data Studio is not currently supported though someone recently filed a feature request. I was not able to connect using Dbeaver either.

Can’t do this with the Dataverse connector

Using the SQL connector rather than the Dataverse connector in Power BI has a couple of benefits:

  • You can include a SQL statement in your source step.
  • You can retrieve relationship columns in case you don’t know how tables are connected or need to denormalize several tables into one.
  • You can add failover support in case you replicate your Dataverse database in a SQL Server (presumably, as I never tested this).

The “Navigate using full hierarchy” option on the other hand fails with this error: “Invalid table name SCHEMATA for schema INFORMATION_SCHEMA”

That said for larger query results (above 80MB) you’ll want to avoid using the SQL endpoint. It’s a good exploratory tool for databases you’re just getting acquainted with, provided you have a modicum of SQL skills.

Like with Datamart, only Azure Active Directory authentication is supported, i.e. you can’t use SQL and obviously not Windows authentication.

Note that Dataverse SQL only supports a subset of T-SQL data types, statements, and functions.

Like with Datamart, only Azure Active Directory authentication is supported, i.e. you can’t use SQL and obviously not Windows authentication.

If you wonder was TDS means: Tabular Data Stream (TDS) Protocol is “an application-level protocol used for the transfer of requests and responses between clients and database server systems. In such systems, the client will typically establish a long-lived connection with the server. Once the connection is established using a transport-level protocol, TDS messages are used to communicate between the client and the server.”

Finally, because Microsoft likes to make a spaghetti dish out of their product line, Dataverse can be loaded in Power BI via Azure Synapse Link which will store data in Azure Data Lake Storage Gen 2. Why would you ever want to do that? Here’s a hint:

“The Azure Synapse Link for Dataverse service supports initial and incremental writes for table data and metadata. Any data or metadata changes in Dataverse are automatically pushed to the Azure Synapse metastore and Azure Data Lake, depending on the configuration, without any additional action. This is a push, rather than pull, operation. Changes are pushed to the destination without you needing to set up refresh intervals.”

One thing is sure, your Microsoft Account Manager will love you if you contract Power Apps + Synapse + Power BI Premium…

It also looks like that’s the way to go in case you want to ingest Dataverse data with Azure Data Factory.

Leave a Reply

Your email address will not be published. Required fields are marked *