Customer experience | Sales, Customer Insights,...

Use of virtual tables for building segments

(0) Share

Report

Posted on by una

259

Good evening all,

Is it possible to use and query a virtual SQL table in d365 for adding contacts to a segment?

So we would be segmenting based on some contact fields and reaching into this table which can have 30 Million plus records.

Has anybody tried this and what are the pitfalls , I have read the limitations around use of Virtual tables in D365 but wondered if they could work in Marketing?

Thank you

Una

I have the same question (0)

All responses (6)

Answers (0)

Matt Donohoe 5 on at

Like (0)

Report

The context here is that if we have an entity that has both an active set of records (for example, tickets) and as we move on we wish to move those records into an entity that is less expensive (than Dataverse - say SQL as Una mentions) we would maintain an appropriate set of records as "active" and older records over X age into a Virtual Entity (say tickets_historical). We would like to define a mktg journey which encompasses both for the purpose of segmentation of a campaign.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi una,

If your entity isn't available for selection you need to make sure that the entity has relevance search turned on and is synced with the marketing application:

Marketing app --> Settings --> Dataset Configuration --> select the entities to sync:

The Dataset configuration settings allow you to choose which entities to make available for use in dynamic email content, segmentation criteria, and lead scoring in Dynamics 365 Marketing.

Also, You can only sync entities that are configured with Change tracking enabled. Entities without change tacking won't be listed on the Dataset configuration page.

Was this reply helpful? Yes No
una 259 on at

Like (1)

Report

Thanks Leah Ju,

So it looks like we can do it, I was just worried bout limitations of Virtual tables but I guess we can give it a go.

https://docs.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/get-started-ve

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

HI una,

Yes, you can try to test it.

Once the virtual table appears on the dataset configuration page, it means you are getting closer to success!

Looking forward to your good news!

Was this reply helpful? Yes No
magiva 5 on at

Like (0)

Report

interested in knowing how you got on with this ??

Was this reply helpful? Yes No
Suggested answer

una 259 on at

Like (0)

Report
I have found that you cannot enable change tracking on virtual tables so therefore you cannot use them to turn o relevance search andthey never display in dataset configuration in Marketing

is this correct?

Looks like using virtual tables for segmenting isnt an options

Considerations when you use virtual tables

Virtual tables have these restrictions.

Existing tables cannot be converted to virtual tables.

By default, virtual tables contain only a Name and Id column. No other system managed columns, such as Status or Created On/Modified On are supported.

Virtual tables don't support custom columns with the Currency, Image, or Customer data types.

Virtual tables don't support auditing.

Virtual table columns can't be used in rollups or calculated columns.

A virtual table can't be an activity type of table.

Dashboards and charts are not supported with virtual tables.

Many features that affect table table rows cannot be enabled with virtual tables. Examples include queues, knowledge management, SLAs, duplicate detection, change tracking, mobile offline capability, column security, Dataverse search, and Power Pages solutions.

Virtual tables are organization owned and don't support the row-level Dataverse security concepts. We recommend that you implement your own security model for the external data source.

Column metadata properties that validate on update don’t apply to virtual tables. For example, a Whole Number column on a virtual table column may be set to have a minimum value of zero. However, since the value is coming from an external data source, a query will return values less than zero when retrieved from a virtual table. The minimum value property is not implied in the query. You would still need to filter the values to be greater than 0 if that’s what is desired.

Virtual tables don't support change tracking and cannot be synchronized by using a Dataverse feature, such as the Data Export Service or Azure Synapse Link for Dataverse.

Virtual tables that use the included OData v4 data provider are enabled on outbound port 443.

Business process flows are not supported with virtual tables. More information: Unexpected error received when a user activates a business process flow

Was this reply helpful? Yes No