Processing Feedback Evaluations (paper) automagically with SmartDocumentor OCR, Microsoft Flow & Power BI

by Sandro Pereira | Sep 13, 2018 | BizTalk Community Blogs via Syndication

Some time ago I wrote a blog post on how to Process Feedback Evaluations (paper) automagically with SmartDocumentor OCR, Logic Apps, Azure Functions and Power BI, at that point there weren’t so many of the functionalities that we have at the moment both on Logic Apps and Microsoft Flow: like concept of variables, Case operations, more expressions an so on. I will not address here in this blog post if can we redesign to be different or better. The question I raise and will address here is: can we do the same with Microsoft Flow, instead of using Logic Apps?

And the reason I ask this question is that Microsoft Flow is more targeted to Business Users which fits perfectly with scenarios in which we want to extend a product to fit each business users’ requirements/scenarios. If you have an Office365 you can use Microsoft Flow and you will have 2,000 runs per month, so you don’t need to pay extra for using Logic Apps, you can instead use Microsoft Flow.

And the answer is: Yes, you can!

The problem and scenario will be exactly the same: How can we easily convert paper in data to generate additional value? How can we perform operations on it and easily gain insights?

But in this case, to solve this problem, in which I wanted to have the evaluations forms to be processed in real-time, i.e., as the attendees handed in the forms, the results were presented in a public Power BI dashboard in a matter of seconds, we will be using:

• DevScope SmartDocumentor OCR that, not also allowed me to extract the data from my documents and easily integrate with other systems, but also to intelligently set my OCR streams (flows), defining:
  • Different receive locations, like FTP, file or directly from scanner devices;
  • Create/test my recognition templates and review/ validate the data which is gathered;
  • But also, enabled me to connect and send the metadata, XML or JSON, through any kind of HTTP service, I could even extend it by using a PowerShell provider that would enable me to execute a PowerShell script.
• Microsoft Flow that allows all type of Business Users to create and automate workflows across multiple applications and services without the need for developer help in a very simple and fast way. These automated workflows are called flows.
• And finally, Power BI to create interactive data visualization (dashboards and reports)

Processing Feedback Evaluations: The solution

SmartDocumentor: to process and extract metadata from paper

Again, I’m not going to explain in details how the solution is implemented inside DevScope’s SmartDocumentor for it is not the point of this article, and if you want to know more about it, you can always contact me. However, let me contextualize you:

• SmartDocumentor OCR flow will be listening in two receive locations: Share Folder and directly from the scanner device;
• After receiving and extract the data from the scanned documents (paper), SmartDocumentor will send the metadata to a Microsoft HTTP endpoint.

• Inside the SmartDocumentor processes, we optional can specify if we want to review the documents – SmartDocumentor Review Station – before you sent to the Microsoft Flow (or any other system).

Power BI to deliver interactive data visualization (dashboards and reports)

Regarding Power BI, Microsoft Flow Power BI connector (is the same of the Logic Apps connector) only accepts you to use streaming datasets (this has advantages and some disadvantages that we will see further on), that allows you to easily build real-time dashboards by pushing data into the REST API endpoint. To create your streaming datasets, you should access to Power BI with your account:

• Select your ‘Workspace à Datasets’, and then on the top right corner click ‘+ Create’ and then ‘Streaming dataset’

• In the ‘New streaming dataset’, select ‘API’ and then click ‘Next’
• In the second ‘New streaming dataset’, give a name to your dataset: “FeedbackForm” and then add the following elements:
  • SpeakerName (Text) – represents the name of the speaker that is obtained in the evaluation form according to the session.
  • ContentMatureFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
  • GoodCommunicatorFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
  • EnjoySessionFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
  • MetExpectationsFB (Number) – a value between 1 and 9 that is obtained in the evaluation form
  • SpeakerAvrg (Number) – A simple average calculation (sum all the above metrics divide by 4)
  • WhoAmI (Text) – represents the type of attendee you are (developer, architect, …) that is obtained in the evaluation form
  • SpeakerPicture (Text) – picture of the speaker according to the session that is that is obtained in the evaluation form.

• And because we want to create interactive reports in order to have more insights from the event. We need to enable ‘Historic data analysis’ and then click ‘Create’

Unfortunately, streaming dataset is meant to be used for real-time streaming and is a little limited in terms of want we can do with it. For example, it doesn’t allow you to combine different sources, for example, a “table” that can correlate speaker to get their pictures, or to make aggregations of metrics like “Speaker average”. Which means that we need to send all of this information from Microsoft Flow.

Microsoft Flow to create an integration process flow

At the beginning of this article, I told that one of the advantages of Microsoft Flow is that allows Business Users to create and automate workflows across multiple applications and services without the need for developer help. So, for this example, I will try to abstract from the fact that I am a developer and “try” to implement as if I were a business user: no custom code is allowed!

Microsoft Flow it will be the component that will allow us to extend a product (OCR software) that, as all products, are limited to certain features and capabilities to be able to communicate with a huge range of SaaS applications that appear all day on the market.

In order to integrate SmartDocumentor OCR with Power BI, we need to create a Microsoft Flow that:

• Accept a JSON through an HTTP Post. For that, we use a ‘Request – When a HTTP request is received’ trigger.

• • And, because we do have friendly tokens to access the elements of the message, we will use a sample of the JSON message to be able to generate the correct JSON Schema. For that you should:
    • From the trigger configuration box, select the option “Use sample payload to generate schema” and past the above sample:

[
{
“Key”: “RecognitionRating”,
“Value”: “100”
},
{
“Key”: “RecognitionStatus”,
“Value”: “Final”
}
]

• • • To generate the JSON schema, click “Done”

• Next, we need to create the following support variables, that we will use to store the data extracted from the SmartDocumentor JSON message – that is a Name/value typed message – to be sent to Power BI dataset:

• • For that, we need to add a ‘+New step’, ‘Add an action’ and then enter Variables’ into the search box. Select “Variables – Initialize variable” from the actions list.

• • And repeat the same task for all the variables.

Note: Unfortunately, to the date, there is still no way to create multiple variables using a single shape. In my opinion, this could and should be made as a table approach instead of the existing approach.

Now that we have all our variables that we need to store the information that we need to extract from the SmartDocumentor JSON message, we need to start extracting based on the Evaluation form. The first part of the form is: “What session are you evaluating”:

and for us to find out what session is being selected so we can “map” the speaker and picture name we need to look for the keys “S1”, “S2”, “S3” and “S4” and see which on them is filled. To do that we need to:

• Add a ‘+New step’, ‘… More’ and then select ‘Add a switch case’

• • On the “On” property of the switch condition configuration, select from the list of tokens the “Key” token from the “When a HTTP request is received” trigger

• • Because this is a Key/Value messages with multiple records, the Flow designer will automatically place this switch condition inside a Loop that will iterate thru each key/value pair.
• In the first case, let rename it ‘Case S1’ branch configuration:
  • On the “Equals” property, type “S1”
  • And then add a new condition by selecting ‘… More’ and then select ‘Add a condition’
    • On the ‘Yes’ branch
      • Choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – Set variable”
      • And set the “Picture” variable to the desired value
      • Do the same steps this time to set the “speakerName” variable
    • Leave the ‘No’ branch as it is

• Add 3 more new Case branches and repeat the same steps, this time configuring the values to look for “S2”, S3” and “S4”.

The second part of the form is: “What is my evaluation”:

To extract this information, and because we already have a Switch case condition in place, what we need to do is:

• Create 4 new different branches in the Switch case condition, one for each question – “Q1”, “Q2”, “Q3” and “Q4” – and then:
  • On the “Equals” property, type “Q1”
  • And then Choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – Set variable”
    • And set the “ContentClear” variable to the rate provided by selecting from the list of tokens the “Value” token from the “When a HTTP request is received” trigger
  • Do the same steps for
    • “Q2” to set the variable “GoodCommunicator”
    • “Q3” to set the variable “EnjoySession”
    • “Q4” to set the variable “MetExpectation”

Finally, the last section is about “Who am I”

This is probably the more complicated section. Here, we basically want to travel from a range of position and if this is selected we append to a string separated by a comma, something like “Developer; Student”. To do that we need to:

• On the Default branch, add a new condition by selecting ‘… More’ and then select ‘Add a condition’
  • In the condition expression, select “Edit in advanced mode” and then type the condition to treat the key/name pair if “count” variable is >13 and less or equal with 28

@and(greater(variables('count'), 13),lessOrEquals(variables('count'), 28))

• • On the ‘Yes’ branch
    • We need to check if “tag” is selected or not. If yes set we need to add to the “whoAmI”, otherwise we don’t do nothing. To do that we need an extra if condition: Add a new condition by selecting ‘… More’ and then select ‘Add a condition’
      • On “Choose a value” property select the “Key” token from the “When a HTTP request is received” trigger.
        • On the condition set “Is not equal to”
        • And in the other “Choose a value” property leave it empty

• • • However, to set properly the “whoAmI” variable we need another additional condition to check if the “whoAmI” variable is empty or not. If empty set the “whoAmI” variable with the value, otherwise append to the existing value a comma and the new value. To do that we need to:
      • Add a new condition by selecting ‘… More’ and then select ‘Add a condition’
        • On “Choose a value” property select the “whoAmI” token from the “Variable” context.
        • On the condition set “Is not equal to”
        • And in the other “Choose a value” property leave it empty
    • On the “Yes” condition, choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – Set variable”
      • And set the “whoAmI” variable to the desired value – select the “Key” token from the “When a HTTP request is received” trigger.
    • And on the “No” condition, choose the “Add an action” option and on the “Choose an action” window, enter “Variables” and select the trigger “Variables – append to string variable”
      • And set the “whoAmI” variable to the desired value – select the “Key” token from the “When a HTTP request is received” trigger.

• Finally, after the Switch case condition, and just before finishing the “Apply to each” cycle
  • Choose an action” window, enter “Variables” and select the trigger “Variables – Increment variable”
    • On the “Name” property select the “count” variable
    • And on the “Value” set as 1

To finalize the entire process, we just need to:

• Calculate the rate average – the sum of all question results divided by the number of questions.
• And send it to Power BI

To accomplished that we need to:

• Add a ‘+New step’, ‘Add an action’ and then enter “Variables” into the search box. Delect the trigger “Variables – Initialize variable”
  • On the “Name” property select the “SpeakerAvrg” variable
  • On the type property set to be Integer
  • And in the Value property, from the context dialogue box, select “Expression” tab and set the following expression:

int(div(add(add(int(variables('ContentClear')), int(variables('GoodCommunicator'))) , add(int(variables('EnjoySession')), int(variables('MetExpectation')))),4))

• In the last step of the Logic App: we push the data into the Power BI streaming dataset created earlier by using the new ‘Power BI’ Connector. To do this we need to:
  • Add a ‘+New step’, ‘Add an action’, and then enter ‘Power BI’ into the search box. Select “Add row to streaming dataset” from the actions list.
    • Select the name of the workspace and then the name of the streaming dataset
    • The next step is to select the Table titled “RealTimeData”
    • And finally, map the properties with the different variables tokens has in the picture

Give a proper name to the flow and save it

The end result

After saving the Microsoft Flow and process the Evaluation forms, the result is this beautiful and interactive report that we can present in a monitor during the breaks of our events:

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira