From Azure Queues to model-driven Power Apps for social media analytics

In previous articles [1,2,3] I shared how we leveraged Power Automate to integrate Twitter with a range of Azure services to create an immersive and AI enriched social media monitoring and analysis model driven app to monitor chosen hashtags or mentions This is the second part of the technical deep dive on creating an immersive, AI enriched social media monitoring and analysis model-driven app. This article will focus on how we retrieving messages from an Azure queue which is full of AI enriched data and displaying it to our users in meaningful and enjoyable ways through a model-driven Power App.

The ins and outs behind the scenes to get from Azure to model-driven Power App

This post will focus on:

Power Automate

Retrieve and store the enriched data from Azure into the Common Data Service (CDS)

Common Data Service

Leverage out of the box functionality to:

• Create many to many relationships between Key Phrase & Tweets so drill down can be seen from both the tweet and the particular key phrase

Part 1: retrieve and store enriched data from Azure in CDS

This is a job for Power Automate and of course, there is a re built connector for that!

When a message arrives on the Azure queue we use the Parse JSON action to first extract the message from the queue which contains our Tweet and Key Phrase array

{ "type": "array", "items": { "type": "string" } }

Then we extract the records from the Key Phrase array - each tweet can have one or many of these.

{ "type": "object", "properties": { "id": { "type": "string" }, "keyPhrases": { "type": "array", "items": { "type": "string" } }, "sentimentLabel": { "type": "string" }, "sentimentRating": { "type": "object", "properties": { "positive": { "type": "number" }, "neutral": { "type": "number" }, "negative": { "type": "number" } } } } }

A sample message looks like this:

{
  "id": "e257ee89-57d4-ea11-a813-000d3a58d840",
  "keyPhrases": [
    "Wim Geukens",
    "veripark",
    "PowerPlatformShow",
    "nz365guy",
    "Intellectual Property"
  ],
  "sentimentLabel": "positive",
  "sentimentRating": {
    "positive": 0.78,
    "neutral": 0.22,
    "negative": 0
  }
}

So lets deal with the Key Phrase data first.

For each Key Phrase returned from the array we need to determine if it already exists in CDS or not using the ‘Get Records’ action and an OData filter query

We want to check the output from the search to see if the Key Phrase exists, we do this by checking if the list records returned any results using a Condition Control action. If this is TRUE it means that the search returned no results - we need to create the Key Phrase record, otherwise it already exists.

empty(body('List_Key_Phrases')?['value'])

If it already exists we extract the Key Phrase GUID. As I mentioned in my previous article when using the ‘List record’ feature, even when you limit the top count to 1 the outputs will automatically create a loop for each item returned, it gets messy and we only want the first match, we do this using the following formula:

first(outputs('List_Key_Phrases')?['body/value'])?['aeh_keyphraseid']

Now we can create the many to many relationship between Key Phrase and Tweet using the ‘Relate Records’ action for CDS. We can include the include the IDs from each condition above either the new key phrase or the existing one because we are guaranteed that only one of them will ever be populated. This is the final step in our For Each loop.

Now we can use a Switch action update the tweet record with the sentiment rating (Positive, Negative, Neutral, Mixed) and the confidence score about that particular rating which is returned as a score for each (positive, negative, neutral). Using a switch statement ensure that if the rating values returned ever change, the update will still come through and not cause a failure in the flow.

Finally we delete the message from the Azure Queue so it doesn’t get processed again creating necessary API calls and data updates

Part 2: explore many to many relationships in CDS

Above we used the CDS (current) connector to create many to many relationship records which was previously not possible with the previous connector nor with classic workflows (without custom plugin steps). The addition of this functionality in the new connector combined with the new unified interface makes many to many relationships a much smoother experience technically and visually. Many to many (N:N) relationships are nothing new but I look forward to tapping into this relationship type much more often now, limitation free.

We created a many to many relationships between Key Phrase & Tweets so users can drill down from both the tweet and the particular key phrase - magic!

Whats next?

In the final part of this technical deep dive we will look at how we can leverage more out of the box model-driven app & CDS functionality to surface all this amazing data data in a model-driven app. Then leverage dashboards & charts for data visualisation and drill down. Finally, we will extend the model-driven apps user experience with Power Apps Component Framework (PCF)
Summary. If you missed previous parts of this series or just want to see them again, you can find them here:

[1] VIDEO: When Power Platform and JavaScript worlds collide - https://www.ameyholden.com/articles/javascript-power-platform-powerful-devs

[2] Azure, JavaScript & PCF: Level Up Social Media Monitoring & Analysis - https://www.ameyholden.com/articles/azure-javascript-pcf-social-media-monitoring-analysis

[3] Using Power Automate with Azure Queues & Cognitive Services for AI fuelled social media analytics https://www.ameyholden.com/articles/power-automate-azure-social-analytics