Using SQL DDL Triggers to restore read Permissions programatically after Dynamics AX 2009 Synchronization

Recently, a customer using Dynamics AX 2009 implemeted a web service that access a view directly in SQL Server. Therefore they created a new SQL user login and gave the user read permissions on the view.

Read permission on a view

However, when synchronizing the data dictionary in Dynamics AX 2009, the views are droped and recreated and the permission on the object is lost. Therfore the webservice call fails.

One way to address this issue from a SQL perspective is to create a DDL trigger that sets the permissions on the view programmatically. Here is a small SQL script that sets read permissions for the user view_user on the the DIRPARTYVIEW after the view has been created again.

CREATE TRIGGER [VIEW_PERMISSION] 
ON DATABASE 
    FOR CREATE_VIEW
AS 
BEGIN
    DECLARE @name SYSNAME
    SELECT  @name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')

    if @name = 'DIRPARTYVIEW' begin
        GRANT SELECT ON [dbo].[DIRPARTYVIEW] TO [view_user]
    end
END
GO

ENABLE TRIGGER [VIEW_PERMISSION] ON DATABASE
GO

Create an Email Tag Cloud with PowerBI and Cognitive Services

PowerBI and Cognitive Services are a powerful combination. A nice example is a tag cloud based on the key phrases in your daily emails. This example requires the following cloud components:

  • PowerBI (of course)
  • Cognitive Services for Key Phrase extraction
  • Exchange Online
  • Flow and Table Storage in Azure

Cloud Infrastructure

First, go to your Azure portal and create a new Cognitive Services Resource. In the creation wizard place the cognitive services to a data center near your Office subscription. I’d also recomend to creata a seperate resource group where you place all the services.

Cognitive Services in Azure

At the Cognitive Services Overview tab, copy the Endpoint URL. From the Cognitive Services > Key tab also copy the Key1. You need both to connecto to the cognitive services.

Azure Storage Account

Next create a new stroage account. Like in the Cognitive services place it in the same resource group and same data center. After the storage account has been created successfuly go to the overview tab.

Azure Table Storage

Select “Tables” and create a new table. Give it a useful name e.g. keystorage. A table storage can be used to place structured data, which require at least to fields a RowKey and a PartitionKey. It is up to you to provide meaningful values to theses fields when inserting data.

Copy the storage account name and from the Access Keys tab the Key1 value. You will need both to connect to the storage account.

Implement transformation pipeline in Flow (first naive approach)

Now, lets create the extraction logic using Flow. There are some limitations with this approach that will result in errors. A more stable version of the flow is discussed at the end. Go to https://flow.microsoft.com and create a new triggered flow from blank.

Automated Flow from Blank

The trigger for the flow is Outlook > When a new email arrives.

Because almost all my mails are HTML formated, I need to add the Content Conversion > HTML to Text step to remove the HTML code from the email body.

The third step in the flow is the key phrase extraction. Therefore add the Text Analysis > Key Phrase extraction step. There you need to provide the Cognitive Services Account Key and Endpoint. The text to analyze is the output from the HTML to Text step.

The last step writes the key phrases to the Azure Table Storage. Like in the Cognitive Services step, you have to provide the name and a key. From the Table dropdown select the table you have create earlier in the Azure portal. The entity has to be a JSON string. In my example the Partition is always 1 and the Row key is a Guid. Because, one mail will have more than one key phrase, the insert is encapsulated in an Apply-to-each block

{
“PartitionKey”:”1″,
“RowKey”:@{guid()},
“KeyPhrase”:@{items(‘Apply_to_each’)}
}

Keyword Extraction Flow

Test your flow by sending an Email to your account. All the steps should succeed

Keyword Extraction Flow Test

You can use the Azure Storage Explorer in the Azure portal to lookup the phrases extracted from the email. In this example I sent an email from my company account, to my private mail account. The flow extracted the key words from the mail (Signature).

Azure Storage Explorer

Tag Cloud in PowerBI

In PowerBI add a new data source from the Azure Table storage. Again you need to provide the storage name and one of the keys. After connecting successfuly to the table, open the transformation window an take a look at the retrieved keys. You can remove the PartitionKey, RowKey and Timestamp from the data set.

Azure Table Storage in PowerBI

In the PowerBI report window, from the Visuals, klick on the Elipsis (…) and search for the Word Cloud in the marketplace. Add the Word Cloud Visual to PowerBI

Word Cloud Visual for PowerBI

Add the visual to the PowerBI report window. Set the Key Phrases as category in the visual.

Word Cloud in PowerBI Desktop

PowerBI Online Service and automated Refresh

Publish the PowerBI report to your workspace. Within PowerBI Online, go to your workspace and navigate to the dataset. From the Elipsis (…) open the settings page. Provide the Key for Azure Table storage.

Azure Table Storage Connection

Now you can also schedule the automatic refresh

Automatic Refresh from Azure Table Storage in PowerBI Online Services

Implement transformation pipline with a more stable Flow

Unfortunatelly, the text processing in Cognitive Services is limited to 5120 characters. In many cases, Emails contain more characters than this and the flow will fail with an error from the Cognitive Services. One way to address this issue, is to implement a loop that cuts the Email body into pieces of 5120 characters or less before feeding it to Cognitive Services. However, Flow is not very developer focused and requires some workarounds for simple tasks like assigning function calls with a variable to itself e.g substring()

In the first place, delcare 4 variables

Some required variables in Flow

Next execute the HTML to Text block. An optimization is to use the Builtin Data-Operations action Compose to trim() the result to remove blanks from the start and end, and populate the STRLEN and EMAILBODY. Whereas the STRLEN requires a function: length(outputs(‘Trim_Text’))

Set the variables in Flow

Next, create a Do-While Loop from the Control elements in Flow. The condition for the Loop is STRLEN <= 0 because we are cutting the Email into pieces until nothing is left

A loop to cut the Email into pieces of 5120 characters (or less)

Within the Loop, create a IF decision depending on the STRLEN. If the STRLEN variable is less then 5120, the STRLEN is set to 0 to end the Loop. The variable TEXT is set to the EMAILBODY.

Email body is shorter than 5120

If the Emailbody is longer than 5120 characters, the first 5120 characters are copied to the TEXT variable: substring(variables(‘EMAILBODY’),0,5120)

Next the variable STRLEN is reduced by 5120: sub(length(variables(‘EMAILBODY’)),5120)

In the third step, the variable EMAILBODY_SHORT is set to the substring starting at 5121 till the end of the original EMAILBODY. Is is done, because Flow does not support variable asignment by a function that contains the variable itself: substring(variables(‘EMAILBODY’),5121,sub(variables(‘STRLEN’),1))

In the last step the orignial EMAILBODY variable is set to be the EMAILBODY_SHORT. It contains now the body without the first 5120 characters.

Email body is larger than 5120

Within the loop, after the IF condition, Cognitive Services are called with the TEXT variable and the results are written to the Azure Table Storage like in the first naive implementation.

Save Cognitive Services Results to Azure Table Storage

More Optimization

There are three additional ways to optimize this solution.

One may argue, that cutting the text into pieces might cut a releveant word for the Word Cloud into pieces and therefore cannot be recognized by Cognitive Services, e.g. Micros … oft. One way to address this is to modify the substring function, by checking the last index of “_” (Blank) and cut there.

Another issue is that Cognitive Services are not aware of all stop words. Especially if using Non-English Key Phrases you may end up with a messy cloud. However, there are public available lists of stopwords in certain languages out there, that can be loaded into PowerBI and used to exclude certain findings from Cognitive Services. The Word Cloud visual provides an Exclude property where you can provide stop words to exclude.

In the example from above, the language for Cognitive Services is set to DE (german). Howerver, this might not be optimal if you receive Emails in different languages. An optimzation could be to use Cognitive Service to detect the language, and switch the Key Phrase Detection Call for the most common languages in your Email inbox, in my case German and English.

Flow Download (package)

Please find the Flow Package in the Sources Onedrive Folder. Import the .zip File in your Flow Tenant. You need to map Outlook, Cognitive Services, Azure Table Storage, etc. to your configurations.

Object Detection with PowerApps AI Builder

Power Apps recently got the capability to create some computer vision AI models. One of the IMHO most popular ones is called object detection, which is used to detect (predefined) objects on images. For getting started you may use the free edtion of power apps.

Environment and CDM Entity

AI Builder is bound to a Power Apps environment and the Common Data Model. If you don’t have already created an environment, logon to https://web.powerapps.com and create a new environment.

Create new environment

To use the CDM entities you need a new database. In my case, I created a new one with USD and English as preferred language.

Create new database

It may take a while, and you may need to refersh your browser screen, but the AI Builder (Preview) option will appear on the left menu bar.

Create new Power Apps AI Builder Object Detection model

Next, go to Data > Entites and create a new entity for the type of objects you want to identify. In my case, I’m playing around with Nerf guns, therefore I created a new Nerfgun entity. It requires at least a useful name. Feel free to add more fields.

Select entity from Common Data Model

Provide entity data

In a next step you have to provide information about the different elements that shall be identified. In my cases, which Nerfs guns will be on fotos e.g. Rapidstrike, Slingfire, Cyclonshot, etc.

Entity in Common Data Model

There is an option to edit the entity via Excel. However, in my case the Excel addin is not working, and failing to authenticated 😦 If this happens to you, a workaround is to create a simple Power App and edit the entity via Power App.

Fill CDM entity with data using Power Apps

Create and train AI model

In Power Apps go to AI Builder and create a new model for object detection.

Create new AI model for object detection

Provide a name for the model and select the created entity for detection. From the records in the entity select those records that are relevant for detection.

Select object to detect using AI builder

Next comes the time consuming part, take pictures of your objects in different locations, lighting, quality, day time, etc. upload it to the power apps model. You need at least 15 pictures for each element you want to detect.

Upload images to Power Apps AI Builder

Tag each of the pictures with the corresponding object on the picture.

Tag object on images in Power App AI Builder

After uploading and tagging each picture, train the model. This may take a while and will result in a (not very usefull) quality estimation. Right now I didn’t see recall, precision, AUC, or any other more detailed information. If you are satisfied with the models estimated power, publish it so it can be used in your apps.

Train AI model for object detection

Use AI model in Power Apps

It’s easy to use the trained model in a Power Apps application e.g. on the smart phone. Create a new power app with empty layout. From the menu insert the object detection component.

Create a new PowerApps app with AI Builder

Select your object detection model for the component. Save and publish your app, load it on your phone and test it.

Test Power Apps AI Builder in real life

Dynamics 365 FO: Export Entity Store to Azure Data Lake

Since version 10 Dynamics 365 for Finance and Operations supports the entity store export to Azure data lake. The main benefits are reduced costs because Azure Cloud storage is cheap and easy access for Business Intelligence tools like PowerBI.

If you are running a local development VM, the data connection tab in system parameters ist deactived by default. However, this can be actived using the SysFlighting table.

The configuration is pretty well documented by Microsoft. I’ve performed all the necessary steps and recorded a video:

Green IT Consulting

A recent study revealed that living vegan can save up to 670 kg Co2 a year. But not only veganism saves Co2, using public transport like train instead of car also helps to save the environment. Public transport companies, like the Austrian Raiload Company (ÖBB) rely 100% on green electricity and explicity promote the Co2 reduction on their train tickets.

Co2 avoided compared to driving by car on train ticket

Therefore I collected all my train tickets from the last year (2018) and summed up Co2 reduction. Moreover, I looked up the distance in km and calculated the costs for the company I’m working at if they had to pay me for driving by car. I compared it to a mean ticket price, because the price varies on how early you buy the ticket e.g. between 9€ to 35€ for a ticket from Linz to Vienna

DateFromToCo2 Red. (kg)Distance (km)
18.1.2018LinzVienna38,1178
20.1.2018ViennaLinz38,1178
30.1.2018LinzSalzburg26,0130
31.1.2018SalzburgLinz26,0130
11.2.2018LinzZurich119,2581
15.2.2018ZurichLinz119,2581
20.2.2018LinzFrankfurt114,8541
24.2.2018FrankfurtLinz114,8541
11.4.2018Linz Vienna 38,1178
13.4.2018 Vienna Linz38,1178
1.5.2018LinzBudapest91,0434
4.5.2018BudapestLinz91,0434
12.5.2018Linz Vienna 38,1178
13.5.2018 Vienna Linz38,1178
23.5.2018Linz Vienna 38,1178
25.5.2018 Vienna Linz38,1178
1.6.2018Linz Vienna 38,1178
2.6.2018 Vienna Linz38,1178
20.6.2018Linz Vienna 38,1178
21.6.2018 Vienna Linz38,1178
23.8.2018LinzInnsbruck64,5313
24.8.2018InnsbruckLinz64,5313
16.10.2018Linz Vienna 38,1178
19.10.2018 Vienna Linz38,1178
12.11.2018 Vienna Linz38,1178
30.11.2018Linz Vienna 38,1178
1.12.2018 Vienna Linz38,1178
3.12.2018Linz Vienna 38,1178
7.12.2018 Vienna Linz38,1178
Sum:1554,9 kg7380,0 km
€ per km0,42 €3099,3 €
Mean ticket Price30 €870 €

The numbers show that just by using public transport for some business trips (not all) I was able to save a huge amount of Co2. Compared to the study, twice the amount of Co2 like living vegan 🙂 Moreover, using train instead cars save the company a lof of money: 2229€ in one year. My conclusion is that it’s not necessary to change the complete way of living to make a sustainable impact on Co2 reduction.

Connect Azure Data Lake Storage with PowerBI dataflow

PowerBI dataflow performs ETL (Extract Transform Load) workloads in the cloud. PowerBI Pro and Premium Users get dataflow storage without additional charges. However, this storage is managed by PowerBI and you cannot access it directly. Therefor BYOSA (Bring Your Own Storage Account) is support to connect you own Azure storage account with PowerBI dataflow. I’ve made a video, following the documentation, how to connect an Azure storage account with PowerBI. Please find my video youtube:

Configure Azure Data Lake storage with PowerBI dataflow

Send SSRS Report per Mail (.xpo Download)

Like requested by some folks, I’ve added the .xpo file for the SSRS Report Mailer in the source code folder: https://erpcoder.blog/source-code/

Don’t get confused, the folders name is “Dynamics 365” but the XPO is for AX 2012