I had a request the other day to integrate External Data with Customer Insights Segment Queries. Unfortunately, I don’t have access to a Customer Insights tenant, but I thought the problem was pretty interesting, so I decided to validate my theory using Marketing Lists.

The Problem: We want to build lists and segments from an external data source in Dynamics.

Solution: Virtual Tables

This was a bonus for me, because I’ve been looking for a way to put Virtual Tables to the test for a long time. Before getting started, Virtual Tables are views of your data from a different system that are not stored within your tenant, a particularly useful feature if you don’t want to move all your data around at once.

Create your Virtual Connection

The first thing to do with a Virtual Table is to create it, which involves connecting to an underlying data source. To do that, I selected a SQL Server and used a database I had lying around with some data that would be a good test.

My Virtual Table

After creating my connection, I then went to access my data. As you can see here, you can’t create columns back to your original schema, only read data one way.

Even when looking at the data on a form, it is only one way.

The Common Link

At this point, I have a table of contacts (that I will build my subscription list from) and a completely unrelated table, so now I have to link the two of them together so I can use the tools in PowerApps accordingly. To accomplish this goal, I created an intersect entity that has one-to-many relationships between both entities.

When complete it looked something like this.

Linking to your Customer Insights/Marketing Lists

One of the best parts of Subscription and Marketing Lists is building dynamic queries to that scale as you do. To this end, I built the simplest of queries that let me intersect my data from my custom table to my virtual table.

And the result, I am now able to query against data not in my PowerApps/CRM system to build segments and queries on based on Virtual Tables.

A pretty cool and simple implementation of to multi-database problem.