This year at SQLBits Adatis decided to create two technical demos to fit in with the magic theme, these were the Adatis Sorting Hat and the Adatis Photo Booth.

My blog post will look at the Adatis Photo Booth which was designed to allow us to print a wizarding student ID for participants; I’ll give an overview of the technology stack that was used and then do some analysis of the data we collected using Power BI.

The Requirement

The first place to start when designing a technical solution are the business requirements. In our case the requirements were:

The user should have an app running on a tablet that will ask them for some basic details such as name, email address and twitter account before allowing them to take a selfie to be printed on their card.

The image of the user should be uploaded to the Azure Cognitive Services Face API to capture their emotions.

The final data to be placed on the card should be added to a database, in the required format, for our card printing software to pick up.

The solution should require the smallest amount of coding possible.

The solution should be fully automated and require the least amount of interaction from the Adati on the stand.

The Technology

To satisfy our requirements we chose to use Power Apps for the front-end and Logic Apps for the backend making our solution completely serverless.

A brief overview of the steps in the process and the tech used at each stage is below:

At a high level the technology being used is:

Power Apps – allows us to build a cross-platform, front-end application without writing any code.

Azure SQL Database – allows us to store the data captured from the application. Power Apps natively integrates with Azure SQL DB.

Azure Cognitive Services – a set of APIs that allowed us to very easily add some intelligence to our application. The face API was easy to integrate and we were using machine learning to map the emotions of the face within minutes.

The Technical Challenges

While building the solution we came across a couple of limitations that are useful to raise, these are:

The cognitive services connector for Logic Apps doesn’t currently return the emotion data returned by the API. To get around this we made a web request within the logic app and parsed the JSON returned to extract the attributes we needed.

Apart from the API to upload the images no coding was required to get this solution to function making Power Apps and Logic Apps a very good choice when you need to create a solution that needs to be delivered quickly.

The Data

Now we’ve looked at the technical solution we can use Power BI to look at some of the data we collected at SQL Bits.

To analyse the data I connected my Power BI desktop to the Azure SQL Database we were using, I then created some measures and visuals against the data to give the below result:

The visuals we have are:

Top Left - tree map that look at the count of students by their primary emotion (the emotion that scored highest)

Top Middle – % of happy (where primary emotion is happiness) and % of unhappy (where primary emotion is anger, sadness or disgust) over each 3 hour period.

Top Right – total number of students we produced cards for, overall % of happy and % unhappy students.

Bottom – primary emotion logged over time

As you can see, we did quite well with over 46% of our students having a primary emotion of happy, with only 13% being unhappy. It seems that over Thursday and Friday we had some unhappy students but on Saturday everybody was happy!

Conclusion

To conclude, we created our tech demos for SQLBits so everybody can have some fun on the stands however, as you’ve seen, the demos are backed by the cloud technology that we use all day, everyday. Power Apps and Logic Apps are an excellent set of tools and allow you to build applications quickly and easily while connecting various services together; all while being part of the Azure PaaS and SaaS offering without the headache of provisioning servers. The Cognitive Services APIs also present a great offering and allow you to quickly and easily add some intelligence to your application without needing to roll your own solution.

As always, if you have any questions or comments then please let me know.