Microsoft Cognitive Services – Azure Data Ninjago & dqops

The first question pop out of my head when I heard Microsoft Bot Framework is how to build some sorts of bots, which is capable to replace me for any of my responsibilities in my work. Part of my Power BI consulting responsibilities is to answer all sorts of Power BI related questions from clients. That would be pretty cool to have a bot to take those responsibilities from me, on the basis that there is no risk to my job security :-).

After a quick research, I found it is very easy to create a Power BI knowledge base bot using Microsoft Bot Framework and QnA Maker service. This blog post walks through the steps to create the bot.

There are four major steps to build a Power BI knowledge base bot:

Step 1 – Create QnA maker and train the knowledge base
Step 2 – Create a bot with the Bot Builder SDK and deploy to an Azure App Service
Step 3 – Register the created bot with Azure Bot Service
Step 4 – Connect the bot to channels

Step 1 – Create QnA maker and train the knowledge base

QnA maker is one of Microsoft cognitive services that enables developers or business users to build, train and publish QnA type of bot based on FAQ urls, structured documents or editorial content. You can logon to the QnA maker services website through this link, https://qnamaker.ai/, where you can create, manage, test and publish your QnA bot.

To create a QnA maker, you need to click the “Create new service” link on the top navigation bar on the QnA maker service website to launch the “Create a QnA service” form.

You need to provide the name of the QnA maker and to specify the urls of the knowledge base pages or to upload the files that contains your questions and answers.

After the QnA maker is created, you can access it from “My services” page. You can select to edit your QnA maker that brings you to the maker edit page where you can edit, retrain, test and publish your QnA maker.

On the knowledge base tab in the maker edit page, all the questions and answers from the web pages of the knowledge base you specified or from the documents you manually uploaded will be extracted into a table, where you can manually delete the imported question/answer pairs or manually add and edit new pairs.

After you have edited your QnA maker, you can save and retrain it using the green “Save and retrain” button on the top right on the maker edit page, and then test the QnA maker on the “Test” tab.

After you are happy with your QnA maker, you can publish it as a service using the blue “Publish” button on the top right of the maker edit page. After the maker is successfully published, you will be provided with the sample HTTP request for building your bot. you need to note down the QnA knowledge base id and the subscription key (highlighted with red box in the snapshot below) that will be used later when create the bot.

Step 2 – Create a bot with the Bot Builder SDK and deploy to an Azure App Service

There is a couple of ways to create a bot. In this blog post, I will use Bot Builder SDK for .Net to create the bot in Visual Studio 2017.

Before you can use Visual Studio 2017 to create a bot with Microsoft Bot Framework, there are some prerequisite tasks you need to do, such as installing project and item templates. You can follow this doc for those prerequisite tasks: https://docs.microsoft.com/en-us/bot-framework/dotnet/bot-builder-dotnet-quickstart

After you have done all the prerequisite tasks, you can see a new “Bot Application” project template on the “New Project” dialog.

After you created a project using the “Bot Application” template, you will have the predefined controller and dialog files, and the predefined configuration settings in the web.config file.

Open the web.config file, under the appSettings section, add two keys for QnA subscription key and Knowledge base id that you have noted down earlier when you published your QnA maker service.

Under the Dialogs folder in your project, create a new dialog file using the dialog item template and name it as PowerBIQnADialog. Add a reference to Microsoft.Bot.Builder.CognitiveService.QnAMaker, and change the dialog file to inherit from QnAMakerDialog. Call the base constructor of the class and pass the QnA maker attribute data over, including the QnA subscription key and QnA knowledge base id (specified in the web.config file), the bot message text when there is no answer matching the asked question, and the matching threshold for deciding whether an answer is matching the question.

After the dialog file is created, open the existing MessageController.cs file. In the “Post” method, change the Dialogs.RootDialog() to Dialogs.PowerBIQnAdialog() as the snapshot shown below.

After you referenced the right dialog class in the MessageController.cs file, you can publish the bot to an Azure App service. You can create the app service through Azure Portal, or create it in Visual Studio when select to publish the project.

Step 3 – Register the created bot with Azure Bot Service

After you have created a bot and deployed to the Azure app service, you can create an Azure bot service and register the bot you have created to the bot service.

Logon to the Azure Portal, create a new Bot Channels Registration service. Specify the general information such as name, subscription, location, etc. The most important attribute to specify is the “Message endpoint” attribute of your created and deployed bot, which is in the format as {url of the app service where your bot is deployed}/api/messages.

After the bot service has been created, you can change the messaging endpoint in the Settings tab if required.

Also, on the Settings tab, you can get the Microsoft App ID for the bot service and the password (you can click the “Manage” link to generate a new password. The password created previously is not visible if you have not noted it somewhere before). You now need to update the web.config file in your bot Visual Studio project to set the Microsoft App ID and the password to the MicrosoftAppId and the MicrosfotAppPassword key. After the change, you need to republish your bot to update the Azure app service.

After the your bot is updated in the Azure app service, your bot service is ready to use. you can test it in the “Test in Web Chat” tab in your Bot Registration Channels service.

Step 4 – Connect the bot to channels

A bot channel is the connection between the Bot Framework and communication apps which can talk to your bot. You can create channels on the “Channels” tab in you Bot Registration Channels service page. There are quite a number of apps that have been supported by Bot Framework, such as Skype, Teams, Facebook Message etc.

In this blog post, I created a Microsoft Teams channel to communicate to our Power BI QnA bot through just clicking the Microsoft Teams icon on the channel page, and then channel is up running.

On the Chat tab in your Microsoft Teams app, start a new chat to the Microsoft App ID of your bot service.

And now you can start to ask your bot Power BI related questions from Microsoft Team app.

I had a quick play on the preview version of Microsoft Flow, one of the newest toys offered by Microsoft for creating automated workflows linking a broad range of online apps and services. One feature interested me is the Microsoft translator service which can automate the translation process between languages.

So where can we use the Microsoft translator service in real world? My immediate idea was the auto-generation (and possibly distribution) of translated versions of an Office document (Word, Excel or PowerPoint) when it is dropped into OneDrive. I did actually give it a try, unfortunately the translator service cannot parse the Office document format and I can only get the plain txt files translated and generated successfully. As I believe there is really not many people using notepad to author their business documents, this attempt was failed.

As a SharePoint guy, another idea came up was to automate language translations on SharePoint based solutions. One possible business requirement I imagined out was to auto-translate service/support requests and replies between the languages used in a multinational company (assuming not every employee can speak English). In the rest of this blog post, I prototyped a simple service request app to test Microsoft Flow and the language translation feature.

The service request app is expected to work in this way:

An employee from a non-English speaking country raises a service request (using his or her local language) into a SharePoint list, namely “Service Requests”.
Once the request is added into the SharePoint list, a Microsoft Flow workflow (“Service Request Submission”) is triggered to translate the request “title” and “description” into English which is the language used in the enterprise services/supports department. The translated “title” and “description” fields along with other information of the service requester are added into a separated SharePoint list, “Service tracker” (As the current version of Microsoft Flow does not support the update of a SharePoint list item yet, we have to use a separate list to store the translated fields).
The enterprise services/supports department then solves the request, adds reply in the “Service tracker” list in English and marks the status of the request as “Completed”.
Another Microsoft Flow workflow “Service Request Reply” monitors the update of the “Service tracker” list items, if the status of a request is changed to ‘Completed’, the Microsoft Flow workflow translates the “reply” field into the language used by the requester and then sends an email with the reply message to the requester.

In this example, two Microsoft Flow workflows, “Service Request Submission” and “Service Request Reply”, are created. The detailed steps to create these workflows are listed below:

“Service Request Submission” Workflow

05a
Firstly, we add a “SharePoint online – When a new item is created” step and configure the SharePoint site url and the name of the “Service Requests” list.

Then, we add the “detect language” action to detect which language the request is using.

After that, we add the “translate text” action to translate the “title” and “description” fields into English.

Finally, we create an item in the “Service tracker” list with the translated “title” and “description” fields, and also the other information of the requester (name, email and language) that will be used in the “Service Request Reply” workflow later.

After the “Service Request Submission” workflow is created, we can raise a request to the “Service Requests” SharePoint list, something like:

Waiting a short while, an entry is added into the “Service Tracker” list with the translated fields and the other information of the requester.

“Service Request Reply” Workflow

11a

The “Service Request Reply” workflow starts from the enterprise services/supports department adding the reply to the “Service tracker” list and marked the “status” field as “Completed”.

Firstly, we add the “SharePoint online – When a new item is created” step and configure the SharePoint site url and the name of the “Service Tracker” list.