Category: T-Others

Automating Language Translations using Microsoft Flow

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:

  1. 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”.
  2. 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).
  3. 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”.
  4. 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.

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

02

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

03

04

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.

05

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

06

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

07

“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”.

12

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.

08

We then add a condition step to check if the “status” field is marked as “Completed”.

09

If so, we translate the “Title” and “Reply” fields from English to the language used by the requester.

10

10a

At the end, we send an Office 365 email to the requester with the translated reply.

11

After the “Service Request Reply” workflow is triggered and executed, the requester will receive the email with the translated reply.

13

Advertisements

ABC Classification Analysis with Tableau

In one of my previous posts, I have conducted an ABC Classification analysis using MDX against a SSAS Cube. In this post, I will conduct an ABC Classification analysis using Tableau calculations.

In this example, we want to classify the products based on their sales amount into three categories: ‘A’ (make up the top 5% of total sales), ‘B’ (make up the top %5-20%), ‘C’ (the others).

Firstly, we add the Product Name attribute and Sales Amount measure onto a tableau table and sort the table by Sales Amount in Desc order.

tableauABC-33

We then create three calculations, “Sales Running Total”, “% Sales Running Total”, and “ABC Classification Category”:

1. [Sales Running Total]

tableauABC-3

This calculation sums up the total sales amount from the top ranked product to the product of current row.

2. [% Sales Running Total]

tableauABC-4

While the [Sales Running Total] is divided by the total sales of all products, we can have the related position of the current product against all products.

3. [ABC Classification Category]

tableauABC-5

This calculation checks the position of the current product in the sales amount range and place the product into the corresponding category. Tableau LOOKUP function is used to evaluate whether the total sales of all the higher ranked products above the current product is < 5% of total sales of all products, if so, the current product falls into the category ‘A’, if the total sales of all the higher ranked products > or = 5%, that means the current product does not consist of the top 5% product sales amount and it will falls into the category ‘B’ or ‘C’. Then we can use the same approach to find which product falls into category ‘B’ and the remaining ones will be in category ‘C’.

We can then add the calculations into the table and have the products mapped to the categories.

tableauABC-1

or a nice little chart:

tableauABC-2

Tableau #2 – Visualising Eurovision Voting Path

In the last blog post, I’ve created a box-and-whisker plot to compare UK’s performance on Eurovision contest with others.  That would be good to have a nice Viz to show where the votes from for each country. This can be achieved using Tableau Map and Path.

eurovisionPath-1

It is straightforward to create this Viz using the Path Shelf on Tableau. However, we need have the source data in the right structure. The snapshot below shows the structure of original raw data for Eurovision final voting. Each vote is stored in a single row with the [Country] column storing where the score was given to and the [Giver] column storing where the score was given from.

eurovisionPath-2

To use the Path feature in Tableau, we need transform the vote row into two rows, one row stores details for the start point of the path and the other is for the destination point of the path. the snapshot below shows the required data structure for the Viz.

eurovisionPath-3

To transform the data structure, I have loaded the original Eurovision voting data into SQL Server and create a Stored Procedure to output the data in the required structure.

eurovisionPath-4

The original vote row is UNPIVOT on the [Country] and [Giver] column and generate two rows with details on where the vote is from and to. For each path pair, we need give them a unique [Path ID] and also assign the [Path Order] to the Start and To row. We can have the actual [Score] number on the [Giver] row so that we can assign the SUM([Score]) value to Size of the Start point to represent the size of voting score.

Tableau #1 – Analysis of UK’s Eurovision performance

Eurovision is my favourite song contest programme, not only because of the not bad songs but also the acid humour from the BBC presenter Terry Wogan. I have been watching this show for a few years, from my memory, UK has constantly performed poorly on the final voting which has been blamed as “tactical Eastern bloc voting” by Terry Wogan.

As I came across the raw data of Eurovision results (from 1998 to 2012) a few days ago, it gives me an opportunities to feed my curiosity on how UK has performed in the contest statistically.

I loaded the raw data into Tableau and create a box-and-whisker plot to compare the average place of each country participated in the contest.

eurovision1

From the chart, we can see that UK did perform below average and ranked 9th from bottom with France, Portugal etl. countries below it. The average rank of UK is 16.33, below the overall average rank of all countries (12.70).

eurovision2

From the box-and-whisker plot of UK, we can see that UK’s ranks in the contest from 1998 to 2012 have wide spread from the 2nd place to 26th place. The median of the ranks is 16 and the lower quartile is 11.50 which makes UK at the average-below level compared to other countries. However, the interesting part is the upper quartile which is as high as 24 which indicates that UK has been at the bottom of the contest for quite a few years. In comparison to the box plot of France’s and Portugal’s, although both average and median of the two countries’s ranks are lower than UK’s, the distribution of the ranks are less spread and the upper quartile of the two countries’ are lower than UK’s. That may explain why my perception of UK’s performance on Eurovision is worse than those two countries.