Calculate and Visualize the Lorenz Curve in Power BI

The Lorenz curve (1) is tool in statistics to visualize the concentration of values within a dataset. A typical well known example is the concentration of wealth within the population. For example in 2017 the top 1% of the US population controls 36,8% of wealth (2). However, the Lorenz curve can also be utilized to visualize the product groups share on revenue.

Demo Data

There are 5 product groups (A,B,C,D,E,F) and multiple sales. The data is stored in the table “Sales” including three columns ProductGroup, Product and Qty. For demonstration purpose, each product group has the same share on total sold products: 20%

ProductGroup

Product

Qty

A A123 2
A A234 2
B B123 4
C C123 1
C C123 1
C C123 1
C C234 1
D D123 4
E E123 2
E E234 2

The invoices are stored in a separate table called Invoice, it contains three columns: The ProductGroup, SalesId and InvoiceAmount.

ProductGroup

SalesId

InvoiceAmount

A S-0001 5000
A S-0002 6000
E S-0003 1000
E S-0004 10000
D S-0005 11000
B S-0006 4000
B S-0007 4000
B S-0008 5000
C S-0009 5000
C S-0010 5000
C S-0011 4000

In order to visualize the lorenz curve, a data set is required that holds the cumulated frequency of Qty and InvoiceAmount, in ascending order based on the InvoiceAmount. . The first simple SQL Statements and results look like this

select ProductGroup, sum(Qty) as QtySum 
from Sales
group by ProductGroup

ProductGroup

QtySum

A 4
B 4
C 4
D 4
E 4

select ProductGroup, sum(InvoiceAmount) as InvoiceAmountSum
from Invoice
group by ProductGroup
order by InvoiceAmountSum asc

ProductGroup

InvoiceAmountSum

A 11000
D 11000
E 11000
B 13000
C 14000

Now comes the tricky part. A SQL statement is required which creates the cumulated sums. To do this the SQL function row_number is used to create a line number in the result set. Then the same result set is joined with all records smaller or equal the current row number and the InvoiceAmount is summed up. The SQL Statement looks like this.

select temp1.idx, temp1.ProductGroup, temp1.InvoiceAmountSum,
sum(temp2.InvoiceAmountSum) as CumulateSum
from
(

select ROW_NUMBER() over (order by InvoiceAmountSum asc) as idx, ProductGroup, InvoiceAmountSum
from (
    select ProductGroup, sum(InvoiceAmount) as InvoiceAmountSum
    from Invoice
    Group by ProductGroup)
temptable

)
as temp1
inner join
(
    select ROW_NUMBER() over (order by InvoiceAmountSum asc) as idx, ProductGroup, InvoiceAmountSUm
    from (
        select ProductGroup, sum(InvoiceAmount) as InvoiceAmountSum
        from Invoice
        Group by ProductGroup)
    temptable
)
as temp2 on temp1.idx >= temp2.idx
group by temp1.idx, temp1.ProductGroup, temp1.InvoiceAmountSum

The result looks like this

Idx

ProductGroup

InvoiceAmountSum

CumulatedSum

1 A 11000 11000
2 D 11000 22000
3 E 11000 33000
4 B 13000 46000
5 C 14000 60000

Finally  the SQL query needs to bee extended to include also the cumulated sales qty. Moreover, a line starting with 0 values is required to make the Lorenz cure look correct. This is done by adding a row using UNIOIN. Here is the complete SQL statement and the result set:

select temp1.idx, temp1.ProductGroup, temp1.InvoiceAmountSum,
sum(temp2.InvoiceAmountSum) as CumulatedSum, s.SalesQty,
sum(s.SalesQty) as CumulatedSales
from
(

select ROW_NUMBER() over (order by InvoiceAmountSum asc) as idx, ProductGroup, InvoiceAmountSum
from (
    select ProductGroup, sum(InvoiceAmount) as InvoiceAmountSum
    from Invoice
    Group by ProductGroup
    )
temptable

)
as temp1
inner join
(
    select ROW_NUMBER() over (order by InvoiceAmountSum asc) as idx,   
    ProductGroup, InvoiceAmountSUm
    from (
        select ProductGroup, sum(InvoiceAmount) as InvoiceAmountSum
        from Invoice
        group by ProductGroup
    )
    temptable
)
as temp2 on temp1.idx >= temp2.idx

inner join
(
    Select ProductGroup, sum(Qty) as SalesQty
    from Sales as s 
    group by ProductGroup
)
as s on s.ProductGroup = temp1.ProductGroup

group by temp1.idx, temp1.ProductGroup, temp1.InvoiceAmountSum, s.SalesQty

union

select 0 as idx,“ as ProductGroup, 0 as InvoiceAmountSum,0 as CumulatedSum,
0 as SalesQty,0 as CumulatedSales

Idx

ProuctGroup

InvoiceAmountSum

CumulatedSum

SalesQty

CumulatedQty

0   0 0 0 0
1 A 11000 11000 4 4
2 D 11000 22000 4 8
3 E 11000 33000 4 12
4 B 13000 46000 4 16
5 C 14000 60000 4 20

 

Power BI

The next step is load the data into Power BI and calculate the relative cumulated frequency  for Sales and Invoice. The is done by diving the cumulated frequency by the total numbers.

  1. Open a new Power BI report
  2. Select Get Data from SQL Server and provide the connection information
    In the details copy & paste the SQL Statement from above
  3. Rename the data set to SalesInvoice

After this there should be a dataset with the values from the table above in Power BI. Add two additional columns.to calculate the relative cumulated frequency. Format the columns as percentage.

RelativeFreqInvoice = SalesInvoice[CumulatedSum] / 
                      sum(SalesInvoice[InvoiceAmountSum])

and

RelativeFreqSales = SalesInvoice[CumulatedSales] / sum
                    (SalesInvoice[SalesQty])

The dataset in Power BI should look like this

Idx

Prouct
Group

InvoiceAmount
Sum

Cumulated
Sum

Sales
Qty

Cumulated Qty

Relative
FreqInvoice

Relative FreqSales

0   0 0 0 0 0% 0%
1 A 11000 11000 4 4 18% 20%
2 D 11000 22000 4 8 37% 40%
3 E 11000 33000 4 12 55% 60%
4 B 13000 46000 4 16 77% 80%
5 C 14000 60000 4 20 100% 100%

Go to the report designer and add a new line chart. Set as Axis the RelativeFreqSales. To get the diagonal line for the Lorenz curve add the RelativeFreqSales again as Value. Then also add the RelativeFreqInvoice as Value in the diagram properties. Now there are two lines, the 45° line and the lorenz curve underneath. In this example it should look like this:

Lorenz Curve in Power BI

1) https://en.wikipedia.org/wiki/Lorenz_curve 
2) http://money.cnn.com/2017/09/27/news/economy/inequality-record-top-1-percent-wealth/index.html

Advertisements

Last Day Of Month and Year as View Computed Column in AX 2012

View Computed Columns in Dynamics Ax 2012 support the use of TSQL functions to calculate additional view columns. Here is  an example how to calculate the Last Day of Month and Last Day of Year based on the TransDate at the CustTransView. First, Extend the SysComputedColumnDatePart  Base Enum and add two additional values Month and Year. Extend the getDateDiffFormatSelector() method at the SysComputedColumn class.

private static server str
getDateDiffFormatSelector(SysComputedColumnDatePart _dateDiffFormat)
{
    switch(_dateDiffFormat)
    {
        case SysComputedColumnDatePart::Day :
            return ‚d‘;
        case SysComputedColumnDatePart::Second :
            return ’s‘;
        case SysComputedColumnDatePart::Month:
            return ‚m‘;
        case SysComputedColumnDatePart::Year :
            return ‚yy‘;
    }

    return ‚d‘;
}

 

Last Day Of Month

Add a new static method to the view and call it lastDayOfMonth.

private static server str lastDayOfMonth()
{
    DictView    ctView = new DictView(tableNum(‚CustTransView‘));
    str sTransDate = ctView.computedColumnString(‚CustTrans‘,’TransDate‘);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(‚-1‘,
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff(
    ‚0‘,sTransDate,SysComputedColumnDatePart::Month)+ ‚+1′,’0‘,
    SysComputedColumnDatePart::Month),
    SysComputedColumnDatePart::Day),
    ‚DateTime‘);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayofMonth() method as ViewMethod

Last Day of Year

Add a new static method to the view and call it lastDayOfYear

private static server str lastDayOfYear()
{  
    DictView    ctView = new DictView(tableNum(‚CustTransView‘));
    str sTransDate = ctView.computedColumnString(‚CustTrans‘,’TransDate‘);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff
    (‚0‘,sTransDate,SysComputedColumnDatePart::Year) + ‚+1‘,
    ‚-1‘,
    SysComputedColumnDatePart::Year),
    ‚DateTime‘);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayOfYear() method as ViewMethod

The View

Dynamics AX 2012 translates both fields in TSQL Code. The view looks like this

SELECT    
ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION, 1010 AS RECID,
CAST(CAST(DATEADD(yy, DATEDIFF(yy, 0, TRANSDATE) + 1, – 1) AS DateTime)
AS DATETIME) AS LASTDAYOFYEAR,
CAST(CAST(DATEADD(d, – 1, DATEADD(m, DATEDIFF(m, 0, TRANSDATE) + 1, 0)) AS DateTime) AS DATETIME) AS LASTDAYOFMONTH

FROM        
dbo.CUSTTRANS AS T1
GROUP BY ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION

The result in Dynamics AX looks like this

CustTransView with Computed Column

Visualize the firms contribution to total revenue in Power BI

We recently discussed at work how to display the contribution of multiple firms to the total revenue in Power BI, in a way that the firms can be filtered but the total revenue stays constant. We came up with a very simple no-code solution.

Data from Dynamics AX 2012 R3

All the required data can be fetched from the CustInvoiceJour table in Dynamics AX. There are three datasets in the Power BI data model. First, the invoices including the InvoiceAmountMST, InvoiceYear and DataAreaId. Second the sum of InvoiceAmountMST grouped by Year and DataAreaId. Finally, a table that contains the years. Here are the SQL statements to load the data.

select
InvoiceId, InvoiceAmountMST, InvoiceDate, DataAreaId,
year(InvoiceDate) as InvoiceYear
from CustInvoiceJour

Total Revenue:

select
sum(InvoiceAmountMST) as InvoiceAmountTotal,
year(InvoiceDate) as InvoiceYear, DATAAREAID
from CustInvoiceJour
group by year(InvoiceDate),DATAAREAID
order by DATAAREAID

In Power BI create a new dataset and enter the years manually, like 2010, 2011, 2012, etc.

Power BI Years Table

In Power BI go to the data model an connect the Invoices and Total Revenue with the dataset that contains the years

image

Line and stacked Column

Add a new Line and stacked Column chart to the Power BI Report. Set the field Year from the Year table as shared axis. From the CustInvoiceJour set the DataAreaId as Colum Series and the InvoiceAmountMST as Column  values. Set the InvoiceAmountTotal from the Totals table as line values. In the format tab disable the Y-Axis property Show Secondary.

Diagram settings 

Your diagram should look like this. Filtering the companies does not effect the total revenue line.

image

Inplace Upgrade of Windows Server 2012 HyperV to 2016

There is no upgrade path for Windows Server 2012 (non-R2) HyperV role to Windows Server 2016 HyperV. The easiest way would be to setup a new Windows Server 2016 and activate HyperV. However, there may be reasons to perform an inplace upgrade. This is an experience report doing so

Prepare for the Upgrade

First make sure there are no virtual machine related files left on C: drive. Move the configuration files and especially the virtual hard disk files to another drive. I recommend to make list where the VHDX files are place. This can easily be done via PowerShell

Get-VM –VMName * | Select-Object VMid | Get-VHD | select Path | ft

Next uninstall the HyperV role using the Server Manager. Simply select remove roles and features and select the HyperV role. This step requires a reboot and your server will come up a simple plain windows 2012 server.

image

After HyperV was uninstalled make sure to remove NIC teaming in case you are using this feature. I recommend to deactivate all but one network adapter. This can also be done using the Server Manager.

Perform Inplace Upgrade

Insert a disk or mount the Windows Server 2016 image. Start the upgrade process. When asked choose to keep all your data and apps. This will preserve your data and applications e.g. the RAID Manager software. The setup wizard warns you that an inplace upgrade is not the preferred way to setup server 2016. Accept and proceed. The upgrade will take a while and requires some reboots.

image

Setup HyperV on Server 2016

After the upgrade process has finished, its time to setup HyperV again. First configure the NIC teaming again. Activating the HyperV role in Server 2016 is almost the same as in Server 2012. This can be done by using the Server Manager installing new roles and features. Activating HyperV will require a reboot. After the reboot configure a virtual switch so your VMs can access the network again.

Import the Virtual Machines again

By default HyperV on Server 2012 has no clue about the VMs in former Server 2012. The virtual machines have to be imported manually. This can be done using the HyperV console. First, provide the folder where the virtual machine configuration is placed. Afterwards choose to directly register the VM.

image

Next provide the folder where the virtual hard disk files are placed. Don’t get confused because you don’t see the actual VHDX files in the selection dialog. The import wizard will check if the virtual hard disk named in the configuration file can be found in this folder. If the wizard can’t find the virtual hard disk file, take a look a the list of vhdx file paths generated by the PowerShell script.

image

When all the VMs are imported the upgrade is almost finished. The the network connection of the virtual machines and make sure they can access the network via the newly created virtual switch. Removing the virtual network adapter and adding the virtual network adapter again can help Zwinkerndes Smiley  Perform some tests, maybe reboot the server again and install the latest updates.

On Vacation – Tyrol

no new postings in summer

Serles in Tyrol

Integrate an USB Scale with Dynamics AX

I was recently playing with an Dymo USB scale and how to connect it to a Dynamics AX 2012 instance on a virtualized HyperV installation. It turned out that connecting these two is not so hard at all. The Dymo scale was immediately recognized by my PC.

Dymo scale connected to a Windows 10 PC

To access the USB scale, you need to know the Vendor ID and Product ID. This can be found in the Windows device manager. In my case the Vendor ID is 0x0922 and the Product ID is 0x8003

USB Vendor ID and Product ID in device manager

Access the USB Scale via C#

You need the Human Interface Device library, which is also available as source code. Download the Visual Studio project, open the .sln Solution from the SRC folder and build the library: https://github.com/mikeobrien/HidLibrary

Some code is needed to access the scale. Fortunately, there is a code snipped available here: http://r.lagserv.net/scalereader.htm .

  • Create a Visual Studio C# DLL Project.
  • Copy the code for the USBScale class from the website.
  • Add the previous built HIDLibrary to your projects references.
  • Add two int properties to the class, for Vendor ID and Product ID.

    public class USBScale
    {
        public int VendorId { get; set; }
        public int ProductId { get; set; }

  • Change the following line in the GetDevices() method at the USBScale class

public HidDevice[] GetDevices()
{
   //return HidDevices.Enumerate(0x0922, 0x8004).Cast().ToArray();
   return HidDevices.Enumerate(VendorId, ProductId).Cast<HidDevice>().ToArray();

}

  • For convenience, add a new method GetGramm()

    public decimal GetGramm()
    {
        decimal? lb = -1;
        decimal? gr = -1;
        decimal? oz = -1;
        bool? stable = false;

        GetWeight(out lb, out gr, out oz, out stable);

        if (gr.HasValue)
            return gr.Value;
        else
        return -1;
    }

 

  • Build the DLL library

Forward the USB device  to HyperV

There are different ways to pass an USB device to a virtual machine. However, I was using a tool called USB Redirect which is available as trial version for testing. It has two components. The server which manages and shares the USB devices is installed on the physical machine.

USB Redirect

The client is installed on the VM and can access the USB device at the physical machine.

USB Redirect Client

 

Integrate with Dynamics AX

Finally, the last step to integrate with Dynamics AX is easy. Copy the HID Library DLL and the USBScale DLL to the client bin folder. Create a form with a Scale button. At the clicked() method create a new instance of the USBScale class, provide your product and vendor ID and call GetGramm().

<YOUR_NS>.UsbScale scale = new <YOUR_NS>.UsbScale();
scale.set_VendorId(<YOUR_VENDOR_ID);
scale.set_ProductId(<YOUR_PRODUCT_ID);
real value;

value = scale.GetGramm();

info(strfmt(“%1 g”,value));

Dynamics AX with USB Scale

Performance Optimization by using Included Columns and Field Selects

Since version 2012, Dynamics AX supports included columns in indices although SQL Server supports it  for quite a long time. Here are some examples how and why it is good practice to use included columns in an index. I’m using Dynamics AX 2012 R3 Cu12 on Windows Server 2016 and SQL Server 2016 with Contoso Demo data for this example

Cluster Index.

The cluster index can be defined using multiple fields and is used to defined the order of records stored in the table. Even more important is the fact, that if a table has a clustered index all the data is stored in the table, i.e. the cluster index IS the table!

InventTrans

Take a look at the space allocated by the indices. About 219 MB are used to store actual data and 167 MB are used to store index information

image

The following SQL Statement reveals the size in detail

SELECT
ind.name,
SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM
sys.indexes ind
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
INNER JOIN
sys.dm_db_partition_stats AS s ON s.[object_id] = ind.[object_id]
AND s.[index_id] = ind.[index_id]
WHERE
t.name = ‚INVENTTRANS‘
GROUP BY ind.name
order by IndexSizeKB desc

The table data is stored in the TransOriginIdx

name IndexSizeKB
I_177TRANSORIGINIDX    226992 ~ 221 MB
I_177OPENITEMIDX 63720
I_177STATUSITEMIDX 34312
I_177ITEMIDX 24872
I_177RECID 23416
I_177DIMIDIDX 22192

Index Usage with Field Select

Here is an example of a select statement with field select on the InventTrans table

while select ItemId,DatePhysical
from inventTrans
where
InventTrans.ItemId == ‚0001‘ &&
inventTrans.DatePhysical >= str2Date(‚1.1.2011‘,123)

{ .. }

The trace parser reveals the actual SQL Statement sent to the database

image

What happens is what you would expect, SQL uses the ItemIdx for this query

image

Only 5 logical reads where necessary

image

 

Select Non-Index fields

When the query selects fields which are not part of the index, SQL server has to perform a lookup in the Cluster Index for each record identified by the ItemIdx to get all the other fields. For example the Voucher and Qty are not part of the ItemIdx.

image

213 logical reads were necessary to fetch the data

image

This can get even worse, when performing the lookup becomes to expensive. This can happen when the query returns a larger number of records. For example, when querying for another ItemId. In this example SQL server does not use the ItemIdx anymore, but performs a search in the clustered index instead. The ItemIdx became completely useless for this query.

image

SQL server required 1345 logical reads to fetch the data!

image

 

Included Columns

Since version 2012 Dynamics AX supports the definition of Included Columns for indices. These columns are not used to sort the index. These are just fields which are stored within the index to avoid costly lookups in the clustered index. In Dynamics AX you just add columns to the index and set the property IncludedColumn to Yes.

image

You can find the included columns in SQL server when viewing the properties of the index

image

When the statement from above is executed again, SQL server can use the included columns from the index and does not perform costly lookups in the clustered index.

image

Only 6 logical reads are required to fetch the data. This is a huge optimization compared to the 1345 reads without included columns.

image