Power Platform Community Forum Thread Details

My goal is to use Cognitive Search to index data created in a PowerApp. I have my tables syncing fine via Synapse Link and am able to query the data in the Serverless SQL pool. In order to prepare the data for ingestion by Cognitive Search I need to denormalize a Parent:Child relationship, grouping Child records into a JSON field. I am able to create a view similar to this one below:

CREATE SCHEMA vw

CREATE VIEW [vw].[HotelRooms] AS SELECT *, (SELECT * FROM dbo.Rooms$ WHERE dbo.Rooms$.HotelID = dbo.Hotels$.HotelID FOR JSON AUTO) AS Rooms FROM dbo.Hotels$

(https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-create-view)

The query runs fine but when I run a SELECT on the view I get an error: The query references an object that is not supported in distributed processing mode Selecting the first column only works fine.

This might be more a Serverless SQL Pool question than a Dataverse one but I’m curious if anybody is doing the same thing, or if there’s another way around this problem? I’m really trying to avoid an additional step of transforming the data with Data Factory although that might be my last resort. Thanks for any thoughts people have.

Categories:

Microsoft Dataverse with Power Apps