Dataverse Virtual Tables using a MS SQL Database

Read this page

0:00

/458.109388

1×

Have you ever wanted to pull in external data into your Power Apps, and be able to use it like any other Dataverse Table? Well, you can.

Jump to the end if you're all about the destination instead of the journey.

Here's the story of how I wasted an evening to prove a point.

Azure SQL Database

I thought all this cloud lark meant you didn't need to spin up a 'server' any more. Yet when creating an Azure SQL Database, I'm required to specify a 'server' for it to run on. How retro 💾.

But then this purple goodness caught my eye...

I'm up for an offer, and after my initial confusion why it still required me to enter a server name, I opted for the old faithful advice of "if all else fails, read the documentation".

For Server, select Create new, and fill out the New server form with the following values:

Server name: Enter myfreesqldbserver, and add some characters for uniqueness. The name of the Azure SQL logical server must be lowercase.

Yep, that's correct 'myfreesqldbserverABC123'. I love a hacky workaround as much as the next, but that is lazy too! Bravo!

Just as my willingness to accept my fate of spinning up a server (real or otherwise), Microsoft got me again with this annoyingly familiar message.

Last time it was some convoluted reasoning to do with the subscription being "Virtual Studio Enterprise Subscription - MPN". Basically one of the remaining few Microsoft Partner benefits that's remotely useful giving $150 'free' credit per month to play with. I couldn't click my way out of it this time.

So I created a support ticket, I'll let you know the outcome if it hasn't already reached the inevitable heat death of the universe by then.

Azure Container Instance

If you can't beat 'em, join 'em.. So I decided to spin up an Azure 'Docker' container to run a containerised SQL Server instance (on Ubuntu no less! I wouldn't want to make it too easy picking Windows).

Image = mcr.microsoft.com/mssql/server:2019-CU12-ubuntu-20.04

So there I was, running a virtual SQL Server instance in a virtual container running on a virtual Docker Host in a virtual datacenter called Azure. How ironic considering the topic of this post is about 'virtual tables'. Well, it made me laugh, if nobody else.

Sample Data Structure

After setting up the most insecure 'sa' password and new database with the most imaginative name I could think of. I created a set of tables with a basic relational hierarchy to play with.

Virtual Tables from SQL Tables requires:

• A primary key to be an int or uniqueidentity data type.
• At least one string field to use as the Primary Name column.

This is a good read on all the various things that Virtual Tables can't do - Known limitations and troubleshooting with virtual tables.

Looking for a Solution?

Now we're getting to it! Reeling from the obvious pun, I cracked open a new solution in my sandbox environment.

Making a Connection

Slipped right in by creating a Connection for the Virtual Table to use for accessing the SQL Server that's going to host the data to be surfaced in the Dataverse.

And then a Connection Reference to, erm, reference the aforementioned Connection.

When Microsoft are not renaming entire divisions, departments, and major product lines every few years, they sure can come up with some absolute crackers, huh?

New Virtual Table From External Data

When creating a new Virtual Table from the Solution, it asks a few simple questions. Firstly, pick a Connection (here's one I made earlier - I bet you're too young to get that reference 💀).

Then pick the table (I'll have to do this for every single one, but I'll spare you the screenshot pain and just go through it once here).

It will auto-populate the values below, but I've decided to add a little 'vt_' midfix to spice things up a bit (and differentiate from other existing spaghetti mess of tables that exist in my sandbox).

Then just next-next-finish your way to glory.

But before you do, can we take a moment to acknowledge the pain everybody went through when CDS was renamed to Dataverse, Entities were renamed to Tables, and Fields renamed to Columns… only for Microsoft to throw it back in our faces by labelling this “Primary Field” 😡.

Dealing with Relationships and LookUp Columns

Out of the box, you get nothing, seriously, what did you expect 😞.

I found it out the hard way when I jumped gleefully over to make.powerapps.com to take my new virtual tables for a test drive. And created a few company records.

And then, I tried to create an account (as a child to a company record), and I'm presented with a standard whole number column where the company lookup should be. Just as I painstakingly defined in SQL. Booo hiss booo 👎.

The trick to this, is you have to DELETE that column (yes, delete it), and then create a new relationship in the Dataverse for that table.

Create a new One-to-Many Relationship, and pay close attention to what you put in the 'External name'. That needs to be the name of the column in the SQL Database that contains the foreign key (the column you just deleted a moment ago).

You should probably set the Lookup column requirement to be business required unless you have total disregard for data integrity like I do 🤠.

Once that's done, go back to the table, (you'll have give it a checky F5/refresh if it doesn't show up automatically). And there you go!

Then it's just the tedious task of customising the Forms, Views, and all that jazz.

Et Voila! Here's a Model App showing the virtual tables with related table Quick Views embedded. (Shame you can't have a quick view inside a quick view, y'know, something that would actually be useful).

And here's the ugly guts version from my virtual containerised SQL Server.

Step by Step

1. Setup an SQL Server in Azure, if you want to use an on-premises SQL Server then you'll also need to sort out a data gateway to bridge from Azure into your network.
2. Have some data - this is fairly important 🙄.
3. Work from a Solution (not strictly required, but why would you NOT want to do this?)
4. Create a Connection / Connection Reference for the SQL Database.
5. Add a New Virtual Table to the Solution.
6. Pick the Connection / Connection Reference you made a moment ago.
7. Select the table you want to turn virtual (one at a time 🤦).
8. Confirm naming and which field you want to be the Primary etc.
9. Don't forget to manually re-create any relationships you have in SQL Server that you also intend to use in Dataverse.
10. Profit $

Conclusion

What's stopping you, why not create a virtual table today!?

Most of the normal interactions you have with Standard Tables also applies to Virtual Tables, so you're good to go in most cases.

If you think it sounds too good to be true, just remember to scroll back up to the bit where I link off to Microsoft's Virtual Table limitations, if you've read that and still up for it, then good for you!

Welcome to the club.