Second week under COVID-19 lock-down in Austria (28. March)

General Situation

The second week of lock-down has passed. The number of infections are growing, but the rate of new infections is decreasing. However, a small increase of large number is still a larger number and it is expected that we will see the peek in the next weeks. With a incubation time up to 10 or 14 days it looks like the lock down has a positive effect. The number of infections is only an approximation to reality, because only a small number of people are tested. The more tests, the more positive cases will be identifed. Thats why it was announced to use the number of people in hospital and in intensive care as more realisitc indicators.

Increasing number of infections, decreasing rate

The government is publishing actual data on regular basis: https://info.gesundheitsministerium.at/

Austrias Covid-19 dashboard

Remote-, Home-, Distance- Everything

In week 2 almost everything possible has been switched to digital and remote work. Many courses at Johannes Kepler Universtity have been switched to distance learning via video streams. Whenever I prefer presence courses because it’s hard to say concentrated for an hour watch video streams.

JKU Linz Moodle

Companies like SAP took the chance to offer free courses and certifications for students who now have time and stay at home. Not only SAP but many other companies use the forced timeout to gain new customers via free videos, tutorials, training, etc.

Free SAP course and certification

The Austrian Red Cross has developed a Stop Corona app to track social contacts. The app assings an anonymous number to the phone and you can do a handshake with other app users. If one person is tested positive on Covid-19 all other contacts get notified on their phone. The use of the app is (actually) optional. Other countries like chain also have tracking apps, but these apps are not anonymous and not optional.

Anonymous tracking app

Fight for Resources

The huge number of Microsoft Teams users (44 million now) forced Microsoft to increase the resources by x60 times. Microsoft also announced to reduce the functionallity of cloud services like Teams, SharePoint, Stream etc. There are also complaints that deploying new Azure resources in region West-Europe fails. I tried to deploy 2 servers (2 cores, 7 GB RAM) and indeed one deployment failed because no resources could be allocated.

No resources in Azure region West-Europe

More Cooking

I’ve used the time in lock-down to train my cooking skills. Fleischlaberl, Kaiserschamarrn, spinach, Apfelschlangerl, Noodle Gratin, Käsekreiner, Apple rings.

Cooking in Isolation

First week under COVID-19 lock-down in Austria (March 22nd)

General Situation

One week has passed since Austria was set under lock-down. When being outside (Work, Shopping Food, Taking a walk) at least 1m distance has to be kept between people, gathering in groups is not allowed. The Police is enforcing the rules with fines up to 3600€. Students, who party and potentially spread the virus can be expelled from university.

Severe consequences for students

Home Office

Our company is now running 100% from home office. It turned out that Teams is now the backbone for us. On monday some of us experienced problems in Teams. However, this is more than understandable when so many companies had to switch to home office and schools are also using Teams for distance learning. Microsoft reported they have now 44 million users on Teams. The usage of Teams also increased significantly in our company.

Teams usage increased during home office

While the cloud scales pretty good, the local internet connection at our office is relativly fixed. There are peeks during working hours because we still have some workloads on-prem but monitoring the traffic it seams we are pretty fine with the current bandwidth. Same goes for the sizing of our RDS host which can handle the remaining on-prem workload as well. The more you are cloud-based, the less you have to worry about on-prem bottlenecks.

Traffic monitorig for on-prem workloads

During the Corona crisis Microsoft, Google, Teamviewer and others offer their collaboration tools for free. To ensure there is enough bandwidth for home office (e.g. while the kids are streaming videos), Netflix announced (was asked) to reduced the quality of video streaming. The RTR announced that providers may omit net neutrality and reduced the bandwith for video streaming. Even more concerning is that A1 Telekom now gives anonymous(?) movement profiles of cell phones to the government and red cross.

There is also something good to take away from this crisis. It is a massive push for digital transformation because now companies are forced to embrace cloud services like Teams to stay operable. We already heard from cloud-sceptical customers that especially Teams is very welcome. Teams & Co is not only used to enable home office, but also to switch former face-to-face contacts with customers and suppliers to remote contact.

Now, that we the number of appointments and customer vistis have been significantly reduced we use some time for house keeping like patching servers, upgrading operating systems, etc. We were planning to take some exams to meet the Microsoft Competency requirements. Thomas Maurer published a good article about how to take an certification exam online. This is also a breathing time to work on our Blockchain research project and migrate applications from the Blockchain Workbench to the Blockchain Service in Azure.

Evaluating the Blockchain Service (Beta)

Home Office = Home Cooking

Home office means home cooking and I started to enjoy cooking a fresh meal every day like Schnitzel, Cevapcici, Chili, etc.

Mahlzeit

First day under COVID-19 lock-down in Austria (16. March'20)

In order to reduce corona infections in Austria we are under lockdown, meaning everybody should stay at home except working in a critical business, buy food and drugs or help others. At this moment we have 1018 people infected and 3 dead.

Monday morning but most people stay at home

As IT company we can switch pretty easy to home office. Communication is now based on Microsoft Teams and Email. We have clear rules for home office. Sign-in and Sign-out in our Shop Floor Control System, phone, email and Teams has to be enabled. We have set up a seperate company Team to group all employees at home in virtual office.

A virtual office in Teams

We’re utilizing our cloud services like Office365, Remote Desktop Service Hosts, VPN and Facebook as well for instant communication. I’ve also summarized the options today in an email to my colleagues. Although there are reports that Teams is facing issues because of the high usage, I didn’t recognize problems in Teams today.

Many articles regarding home office suggest to make a clear break between private and working time. If decided to use my Surface environment (Laptop, Keyboard, Mouse) for work and my Desktop PC for private time.

Cooking and lunch is done at home, and is far not so much fun like in the office. Especially withouth the office dog Meggie.

Cooking noodles for lunch

Public transport is still operating, but the tramway is almost empty (as it should be). Borders are closed for regular travellers, only trucks and business travel ist allowed. Most passenger flights from and to Linz airport are cancelled, freight is still operating.

Clear sky, no planes

Let’s see how the rest of the week will be. Someone posted this today on facebook:

Everything is going to be alright ❤

Extend SalesTable2Line Framework (Dynamics 365 Finance / SCM)

This is an update to my older post how to extend the SalesTable 2 Line framework. The big difference is that in Dynamics 365 Finance and SCM overlaying is not supported and extensions and delegates need to be used. This post uses the same use case. A sales-notes field from the SalesTable needs to be updated in the SalesLines if it is configured so.

Download the sample source code: https://erpcoder.blog/source-code/

Extend the data model

Create a new string datatype and call it ERPSalesNote. Extend the SalesLine and add the ERPSalesNote datatype to the list of fields. Extend the SalesTable and add the ERPSalesNote to the fields. Also add the ERPSalesNote field to the field group HeaderToLineUpdate.

Extend the user interface

Extend the SalesTable form. Place the SalesTable.ERPSalesNote in the Delivery group of the HeaderView tab.

Add the SalesLine.ERPSalesNote field to the delivery group in the LineView tab.

Prepare the update-order-lines dialog

The dialog to choose if and which fields need to be updates at the lines is generated automatically based on the HeaderToLineUpdate field group. There is some code needed to show the fields name in the dialog. This is done by subscribing a custom method to the delegate SalesTable2LineField.lineUpdateDescriptionDelegate

Create a new class that returns a name for the ERPSalesNote field.

class ERPSalesTable2LineField
{
[SubscribesTo(classStr(SalesTable2LineField), delegateStr(SalesTable2LineField,lineUpdateDescriptionDelegate))]
public static void lineUpdateDescriptionDelegate(FieldId _fieldId, TableId _tableId, EventHandlerResult _result)
{
  if(_tableId == tableNum(SalesTable) &&
  _fieldId == fieldNum(SalesTable,ERPSalesNote))
  {
    _result.result("Sales Note");
  }
}
}

Open the Header to Line update dialog by clicking on Accounts receivable > Setup > Accounts receivable parameters > Tab update > update order lines

Extend the framework classes

Create an extension for the AxSalesTable class and create a parm method for the ERPSalesNote field

[ExtensionOf(classStr(AxSalesTable))]
final class AxSalesTable_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
  if (!prmisDefault(_salesNote))
  {
    this.setField(fieldNum(SalesTable, ERPSalesNote), _salesNote);
  }
  return salesTable.ERPSalesNote;
}
}

Create an extension for the AxSalesLine class. Implement a parm and set method. Use the chain of command pattern to extend the setTableFields method.

[ExtensionOf(classStr(AxSalesLine))]
final class AxSalesLine_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
  if (!prmisDefault(_salesNote))
  {
    this.setField(fieldNum(SalesLine, ERPSalesNote), _salesNote);
  }
  return salesLine.ERPSalesNote; 
} 
protected void setERPSalesNote() 
{ 
  if (this.isMethodExecuted(funcname(), fieldnum(SalesLine, ERPSalesNote))) 
  { 
  return; 
  } 
  this.setAxSalesTableFields(); 
  if (this.isAxSalesTableFieldsSet() || 
      this.axSalesTable().isFieldModified(fieldnum(SalesTable, ERPSalesNote))) 
  { 
  this.parmERPSalesNote(this.axSalesTable().parmERPSalesNote()); 
  }
} 
protected void setTableFields() 
{ 
  next setTableFields(); 
  this.setERPSalesNote(); 
}
}

Test your implementation

Make sure that the update method in the parameter is set to prompt. Open an existing sales order. Change to Header view and switch to edit mode. Change the notes in the delivery tab and save.

A dialog pops up and asks to update the lines. Click yes.

Check the sales note field in the sales line. The note field at the sales line should be updated with your text from the header.

Connect to the SQL database of a Dynamics 365 Finance Test instance

In Dynamics 365 Finance / SCM we can no longer access the SQL database of the production environment directly. However, we can access the SQL database of the Acceptance Test instance. All required information can be found in LCS. I’ve made a video where to find this information in LCS and how to connecto to the SQL database.

Green IT Consulting 2019

By using train instead of car for business travel, I managed to avoid 1000kg+ CO2 in 2019

DateFromToCo2 reduction (kg)
24.2.2019LinzNürnberg63,8
26.2.2019NürnbergLinz63,8
9.5.2019Linz Vienna 38,1
10.5.2019 Vienna Linz38,1
14.5.2019Linz Vienna 38,1
17.5.2019 Vienna Linz38,1
28.5.2019Linz Vienna 38,1
29.5.2019 Vienna Linz38,1
12.6.2019Linz Vienna 38,1
12.6.2019 Vienna Linz38,1
25.9.2019Linz Vienna 38,1
27.9.2019 Vienna Linz38,1
24.10.2019Linz Vienna 38,1
24.10.2019ViennaLinz38,1
28.10.2019LinzVienna38,1
29.10.2019ViennaLinz38,1
9.11.2019LinzBerlin174,4
14.11.2019BerlinLinz174,4
1009,8 kg

Please find the summary for 2018 here: https://erpcoder.blog/2019/06/11/green-it-consulting/

XYZ Analysis for Dynamics 365 FO in PowerBI

XYZ analysis is used to categorize products based on the variance of their demand. Products with a low demand variance, i.e. same quantity demanded regulary, are categorized with X, products with an unstable demand Y and products with a high variance in demand as Z.

The categorization is based on a calculated measure, often referred to as variance coeffizient. This coefficient is calculated by the standard deviation of the demand divided by the mean.

Video

Here is a video tutorial how to build the XYZ analysis in PowerBI

Example

Here is an example of three products with different demand over a year. Toilet paper is needed every month in the same quantity, car tires have a higher demand in spring and autum, firework is demanded only on special ocasions.

Demand of different products
Demand of different products in a year

Prepare Data in PowerBI

The basis of the XYZ analysis will be the SalesInvoiceLines entity. At least three columns are needed. The InvoiceDate, the InvoicedQuantity and the Product Name. In this example I renamed the dataset to “Demand”

In PowerQuery create two new columns, one for the year based on the InvoiceDate and one for the month, also based on the InvoiceDate. Afterwards remove the InvoiceDate column

YEAR  = Date.Year([InvoiceDate])
MONTH = Date.Month([InvoiceDate]) 

Next, remove the InvoiceDate column and group the records by ProductName, Year and Month and aggregate the InvoicedQuantity column. Here is an example:

Aggreage demand by product name, year and month
Aggregation in PowerBI

The second dataset contains the XYZ data template, including the ProductName, Year and Month. For simplicity you can enter the 12 records for Year / Month combinations manually. Add an additional column containing the distinct list of ProductNames and expand the rows.

ProductName = List.Distinct(Demand[ProductName])
Product name, year and month

Finally, merge the two datasets to a new one using a left outer join based on the second dataset. As result you get a list of ProductName, Year, Month, Qty combination for each product and every month no matter if there was an acutal demand or not. If there was no demand, the Qty will be null and needs to be converted to 0.

Left Outer Join on calendar and demand
Merge queries in PowerBI

The resulting dataset has records for eacht month and product. It can be used to calculated the mean, standard deviation and variation coefficient. To do so, create a new measure in PowerBI. It will calculate a coefficient value that can be used to categorize the products in X, Y and Z.

Variation Coefficient in PowerBI