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 possibly 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 turns out to be hit-and-miss as of the early Public Preview circa June 2022. 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 some 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 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 reportedly works but not SSMS 19 Preview.
  • I couldn’t connect with Dbeaver. That’s not really a surprise as Microsoft’s documentation only mentions ADS and SSMS as being supported.
  • 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 could not connect across tenants, I got an “Invalid Credentials” error message even though I tried four times to edit the connection and switch the account. Not sure this issue is unique to datamarts though. So I tried creating a dataflow from Dataverse within the same tenant as my Datamart… and got rejected again, whether I checked “Use Encrypted Connection” or not.
  • I couldn’t connect from Azure Data Factory though I didn’t try very hard so might have missed something.
  • 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. 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 are already clamoring for DDL and DML support, and it sounds like that’s coming.

So Which Tool Should I Use and Why Is It Azure Data Studio?

PQ’s SQL

For ad hoc queries you can use the Visual Editor in the datamart service which generates SQL queries via Power Query. You can access said queries by right clicking on your step and selecting “View data source query”. At the time of this writing, you can’t however save visual queries, but this looks like a ludicrous limitation that hopefully will be lifted by time Datamarts reach General Availability. If you don’t know how to write SQL and just want a one-off result, this is a quick and easy way to do so.

If you plan to save and reuse queries, 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.

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.