One of the cool recent announcements by Microsoft is the ability to write T-SQL queries against a Common Data Service database using SQL Server Management Studio. This also includes the ability to connect to the Common Data Service using Direct Query in Power BI.

Let’s look at how to enable this preview feature and how to use it. Note this is a preview feature from Microsoft, so expect things to change.

Check out the video on my YouTube channel here, where I walk through this functionality with several examples:

Why is this Cool?

Previously when dealing with data in the Common Data Service (or Dynamics 365), we had a few options for retrieving data for reporting. These included:

• Using FetchXML to pull data for reports
• Exporting Common Data Service data to a SQL database using the Data Export Service or other export methods
• Connecting to data using various Power Platform connectors

However, if we wanted to write T-SQL against a Common Data Service database, we were unable to without using 3rd party tools. And each of the above-mentioned methods had their limitations such as real-time data access.

With the introduction of this new feature, we can connect directly to a Common Data Service database and write queries in T-SQL! We are restricted currently to read-only access, but this opens a few scenarios already for reporting and analytics. In short, this has been a long time coming.

Preview Setup

First, check the version, which should be 9.1.0.17437+. Sign into https://make.powerapps.com/:

And then a second Settings, then About:

In my case, Version 9.1.0000.17754 online:

Now, let’s enable TDS in the Power Platform Admin Center. TDS is the Tabular Data Stream protocol used to transfer data between clients and database servers.

To enable this in the Power Platform, log into http://admin.powerplatform.com/  and select your environment:

Then click on Settings:

And under Product, select Features:

In the bottom right, we see TDS endpoint (preview). Click On to Enable TDS endpoint, then click Save:

SQL Server Mangement Studio

Ensure you have the latest version of SQL Server Management Studio, and open it and click Connect, then select Database Engine:

Now enter your server information.

• Server type = Database Engine
• Server name = your dynamics org, 5558. Do not include the https. For example, crm342455.crm.dynamics.com, 5558
• Authentication = Azure Active Directory – Password
• Username = your Azure AD user, e.g. admin@yourorg.onmicrosoft.com
• Password = your Azure AD password

Then click Connect.

If you didn’t enable TDS in the Power Platform, you will see the error “TDS Protocol endpoint is disabled for this organization”:

Otherwise, it should connect properly:

Expand Databases, and expand the org, then Tables. We see the Common Data Service tables:

Let’s select the top 1000 rows from the Accounts:

We see the data is returned:

And let’s run a query to get the Accounts by Revenue, e.g.

SELECT [name], [revenue]
FROM [dbo].[account]
ORDER BY [revenue] DESC

Security

Let’s take a look at the Leads in SSMS as logged in by an admin account. We see all leads:

Let’s log in as a different user, Jeff Hay. Jeff is assigned the Salesperson role, with the ability to only read his leads:

Jeff creates a new Lead:

Also note we have the Industry field populated

And if he goes to the All Leads view, we can see this is the only lead he can see:

Let’s log into SSMS as Jeff:

And go to Leads. We see there is only one Lead returned:

Analyze in Power BI

In the Power Platform maker. go to Data->Entities and click on Analyze in Power BI:

This downloads a Power BI Desktop Source (PBIDS) file:

Let’s open it in VS Code in order to see what it contains. We see it contains JSON with details of how to connect to our data source, in this case :

Sign in and Connect:

Select the entities you would like. We will select lead. You may see “Preview is evaluating” – this appears to be a glitch as the data may not load. Ignore this for now. Click on Transform Data:

This takes us to the Power Query Editor. You may see “Preview.Error: The type of the current preview value is too complex to display.” – this also appears to be a pre-release glitch:

If we go to the Advanced Editor, we see our data source. Everything looks pretty normal:

Click Close and Apply and let’s see if the data loads in Power BI Desktop:

Upload to Power BI and Real-Time Reporting

Let’s add the Lead’s Subject and Revenue, and upload to Power BI:

In the PowerBI.com service, we see the message The data source crm342455 is missing credentials and cannot be accessed:

Click on the Dataset Settings page link:

And Click Edit Credentials:

Select OAuth2, and we will select End users use their own OAuth2 credentials. This will use SSO to pass their creds:

Now, when Jeff Hay is signed in, and he clicks on the Leads report, we see the one lead belonging to him:

Now let’s create another new opportunity in the system, which will create another record to the Leads entity in the CDS:

If we refresh Power BI, without refreshing the datasource, we see it is automatically refreshed on the report. I.e. the user didn’t have to do anything to get live data from Dynamics 365:

This is a big step forward from the CDS connectors which require a scheduled refresh.

The other way to connect here is to add a new SQL Server datasource:

Enter the server information as above, with the server name and port 5558:

Sign in:

Select your data:

And continue as above.

What’s Next?

It’s hard to say how much Microsoft will build out this feature, but there are several things that can be added to this to enhance its usability. These include writing to the database as well as reading, enhancements for SSRS, etc.

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin