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

Advertisements

SQL Server 2016 SP1 and Dynamics AX 2012 R3

Here are some ideas on SQL Server 2016 SP1 and Dynamics AX 2012 R3

Enterprise Features in Standard Edition since Service Pack 1

There was a major change in Service Pack 1 for SQL Server 2016. While most cool features were Enterprise-Edition-Only for a very long time, many features like Column Store Index and Compression are now available for Standard Edition too. Have a detailed look at this Blog. SQL 2016 also introduces new features like the Query Store and Power BI Integration with Reporting Services

Reporting Services

SQL Server 2016 Reporting Services require Dynamics AX R3 CU12 and an additional KB3184496 hotfix. Otherwise the installation will fail. The typical AX user won’t see the difference between SSRS 2016 and older versions. However, there are some features that might be interesting for us AX folks too, namely Power BI Integration.

Right now (January 2017) Power BI Integration is not so useful. You can place your Power BI files at the SSRS, which is actually only a better alternative to place the .PBIX file on a file share. However, it is said SSRS will be able not only to store but also to render Power BI files On Premises. This might be interesting for customers who are not willing to use Power BI in the cloud.

Host Power BI files in SSRS 2016

Right now in SSRS 2016 SP1 you can pin SSRS reports to your Power BI (Online) dashboard. This means, you can integrate your SSRS reports in Power BI. This might not sound very useful for Dynamics AX users. Why should I pin an invoice to a Power BI dashboard? But if a customer is already using SSRS for reporting, this might be a good option to start with Power BI and reuse the existing reports. Some Dynamics AX reports with OLAP data source can also be pinned to the Dashboard.

There is a Power BI Button in the SSRS report portal

image

This will pin your report to one of your Power BI (Online) dashboards

image

 

Query Store

This is a very useful feature. All of us are familiar with performance problems reported by some users. The problem is to identify and reproduce the query which performed badly and find the reason. Query Store can be used to store information about such problem-queries, like the SQL statement executed, the used execution plan, etc. In SQL Server Management Studio you can view reports based on execution time, logical and physical write/reads, memory usage, etc.Query Store therefore is a very useful feature in SQL 2016 to identify performance issues.

SQL 2016 Query Store

Column Store Index

Column Store Indices were introduced in SQL Server 2012 too speed up aggregation queries (e.g. sum). However, CSI hat a lot of limitations and  was an Enterprise Edition features till 2016 (non SP). In SQL 2016 SP1 we can now use CSI in combination with Dynamics AX at our customers who have licensed Standard Edition of SQL Server.

In contrast to traditional Row Store Indices where records stored in 8 KB pages (e.g. CustInvoiceJour records), CSI store column values (e.g. LineAmountMST) together in 8 KB pages. Therefore aggregation functions can perform faster because less pages have to be read.

Here is an example:

select CustGroup, year(InvoiceDate) as YR, sum(LineAmountMST) as Amount
from CustInvoiceJour
group by CustGroup, year(InvoiceDate)

When executing this query against a Dynamics AX Contoso Demo database, 2158 logical reads were required.

Query Dynamics AX 2012 R3 database without Column Store Index

Next, create a non-clustered Column Store Index on the fields CustGroup, InvoiceDate and InvoiceAmountMST which are used in the query

Create a Column Store Index in Dynamics AX 2012 R3 database

The same query now utilizes the Column Store Index to fetch and aggregate the data. The IO statistics show that less reads were required to get the result. The query performs faster than with the traditional Row-Store index.

Colum Store Index with Dynamics AX 2012 R3

Be aware that Dynamics AX removes the Column Store Index from the database when you synchronize the data dictionary. This might not be such an issues in a production environment. When you deploy a new application version from Test to Live, make sure to recreate all lost CSI.

Stretch Database

With stretch database you can migrate cold data (aka. existing but hardly not used) from your on premises expensive high performance storage to the cloud. This means you can split the data in large table and move old records in SQL azure. The application doesn’t recognize this split. Only if you query cold data, it will take longer to fetch the result. This sounds good. however there are some very crucial show stoppers.

  • You can’t UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
  • You can’t INSERT rows into a Stretch-enabled table on a linked server.

So right now, this feature is not useful for Dynamics AX on premises installation

Dynamic colored R Diagram in Power BI using Earthtone

Power BI integrates R to perform complex analysis and sophisticated visualization. Earthtones is an R library which takes a screenshot from Google Maps of certain geo coordinate and extracts the landscape colors. Earthtones can be used to color diagrams based on the local color schema.

Earthtones

The package can be found on github. There is also a description how to donwload and install the package. Using earthtones is easy. The function get_earththones takes the parameters longitude and latitude, zoom and the number of colors to extract. The earthtones for Steyr look like this:

get_earthtones(latitude=48.045,longitude=14.422,zoom=15,number_of_colors=8)

Steyr Earthtones

Power BI Data Model

The data model in this example is very simple. There are two excel sheets, one for the revenue by city and item group, another for the geo coordinates (longitude / latitude) and optimal zoom level per city.

Excel Sheet Revenue per City and Item Group

City Geo Coordinates

The Power BI model is very simple, both data sources are linked by the city name

Power BI Data Model

R Boxplot diagram in Power BI

In this example a simple boxplot is used to visualize the revenue by item group. A data slicer for the column city is used to filter the data. The R diagram takes the following columns as input:

  • Longitude
  • Latitude
  • Zoom
  • City
  • Price
  • Group

If only one city is selected, the R script shall gather the cities earthtone colors and format the diagram. If more than one city is selected, the diagram shall be formatted in red, blue and green. The following script loads the earthtone library and gets the distinct number of city names from the dataset. If there is more than 1 distinct name in the dataset the color variable is set to red,blue,green. Otherwise, earthtone is used to get the city typical color schema.

library(earthtones)

numCities <- length(unique(dataset$Stadt))
if(numCities > 1) {
color <- c(„red“,“blue“,“green“)
} else {
color <- get_earthtones(latitude = dataset$Lat[1],
longitude=dataset$Lon[1],
zoom= dataset$Zoom[1],
number_of_colors=3,include.map=FALSE)
}

boxplot(Preis~Gruppe,dataset,col=(color),ylab=“Revenue“,xlab = „Item Group“)

The R script in Power BI looks like this:

R Script and Boxplot in Power BI

If a city is selected, for example San Francisco, the diagram is formatted in the colors blue, gray and brown.

R Diagram in Power BI with dynamic color

The colors fit the blue sea, the bay and the city seen from space.

R Earthtone for San Francisco

If another city, for example Cairo, is selected the diagram gets formatted in dark green, dark- and light brown.

R Diagram in Power BI with dynamic color

That fits the cities local color schema, the brown buildings, the green plants along the Nile and the desert sand.

R Earthtone for Cairo

Create a Power BI Dashboard for Dynamics AX 2012 Sales

This is an update to the previous published articles on Data Visualization, OData Feeds, Power Map, Power Pivot in Office 2013 and Power Pivot in Office 2010. It shows how to use Power BI for Desktop to create a Sales Dashboard for Dynamics AX 2012 (R2).

Power BI Dashboard

Get Data

Start Power BI for Desktop and start with an empty report. From the ribbon on top click “Get Data”, choose SQ Server and provide your server and database. In this example I’m using a single server installation. However, in a production environment you might need to provide <SERVERNAME> \ <INSTANCENAME> , <PORT> e.g. SRVSQL\PROD,2303.

Get data into Power BI

In the next step you have to provide credentials. In my case I’m allowed to access the server with my domain account. In a production environment it is recommended to create a separate Login which is only used for BI Purpose. Don’t get confused if you get a warning that your SQL does not support encryption. If the connection was established successfully, the data wizard presents you a list of tables. Select the following tables:

  • CustInvoiceJour
  • CustInvoiceTrans
  • CustTable
  • InventTable
  • LogisticsPostalAddress

Select tables for Power BI

Click Load, and choose “Import” to load the data in Power BI for Desktop.

Transform Data

In Power BI for Desktop, at the Ribbon click “Edit Queries”. This will open the query editor. We don’t need all columns for this Demo. For each table click the “Choose Columns” button and the select only the following columns:

Choose columns for Power BI

CustInvoiceJour CustInvoiceTrans CustTable
  • InvoiceAccount
  • InvoiceDate
  • InvoiceId
  • NumberSequenceGroup
  • SalesId
  • InvoicePostalAddress
  • DataAreaId
  • InvoiceId
  • InvoiceDate
  • NumberSequenceGroup
  • ItemId
  • LineAmountMST
  • SumLineDiscMST
  • DataAreaId
  • AccountNum
  • CustGroup
  • DataAreaId
InventTable LogisticsPostalAddress
  • ItemId
  • ItemType
  • DataAreaId
  • Address
  • CountryRegionId
  • ZipCode
  • City
  • RecId

Click “Close & Apply” to finish this task.

Choose columns for Power BI

 

Create Relations

In Power BI for Desktop switch to the Data View (with the table symbol on the left pane). It is required to create primary keys and foreign keys before linking the tables. From the list of tables (on the right) select the CustTable and at the Ribbon click “New Column”. Type the following definition:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & „-“ & CUSTTABLE[DATAAREAID]

Create Foreign Keys in Power BI

This will create a new column with a customer account which is unique for all company accounts. Repeat this step for the following tables and columns:

InventTable:

PK_Item = INVENTTABLE[ITEMID] & „-“ & INVENTTABLE[DATAAREAID]

CustTable:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & „-“ & CUSTTABLE[DATAAREAID]

CustInvoiceJour:

FK_Cust = CUSTINVOICEJOUR[INVOICEACCOUNT] & „-“ & CUSTINVOICEJOUR[DATAAREAID]

PK_Invoice = CUSTINVOICEJOUR[INVOICEID] & „-“ & CUSTINVOICEJOUR[SALESID] & „-“ & CUSTINVOICEJOUR[NUMBERSEQUENCEGROUP] & „-“ & CUSTINVOICEJOUR[DATAAREAID]

CustInvoiceTrans:

FK_Item = CUSTINVOICETRANS[ITEMID] & „-“ & CUSTINVOICETRANS[DATAAREAID]

FK_Invoice = CUSTINVOICETRANS[INVOICEID] & „-“ & CUSTINVOICETRANS[SALESID] & „-“ & CUSTINVOICETRANS[NUMBERSEQUENCEGROUP] & „-“ & CUSTINVOICETRANS[DATAAREAID]

Save, and open the relations by clicking on the relations item in the navigation pane on the left. You can drag&drop columns from one table to another table to create relations. Link the following columns:

  • CustInvoiceTrans,FK_Item > InventTable.PK_Item
  • CustInvoiceTrans.FK_Invoice > CustInvoiceJour.PK_Invoice
  • CustInvoiceJour.FK_Cust > CustTable.PK_Cust
  • CustInvoiceJour.InvoicePostalAddress > LogisticsPostalAddress.RecId

Your data model should look like this:

Create relations in Power BI data model

Name the ItemType

In the data view, select the InventTable. From the ribbon create a new column and name it “TypeName”. Add the following code to translate the Enum based ItemType Integer Value to a meaningful name.

TypeName = IF(INVENTTABLE[ITEMTYPE] = 0; „Item“; IF(INVENTTABLE[ITEMTYPE] = 2; „Service“; „Not an Item“))

The InventTable should look like this:

Name item type column

Create a Discount measure

Next we will create a measure which calculates the given discounts as the percentage of the total price. Open the data view using the second button on the left navigation pane. Select the CustInvoiceTrans. From the ribbon, click “New Measure” button in the “Modelling” tab. Provide the following code:

M_DiscPerc = sum(CUSTINVOICETRANS[SUMLINEDISCMST]) * 100 / ( sum(CUSTINVOICETRANS[LINEAMOUNTMST]) + sum(CUSTINVOICETRANS[SUMLINEDISCMST]))

For example:

Qty = 1 € , Unit Price = 1000 €     –> Price = 1000 €
Discount = 100 €                    –> Price = 900
Discount Percentage 3 %             –> Price = 873 €

CustInvoiceTrans.LineAmountMST = 873
CustInvoiceTrans.Discount = 100
CustInvoiceTrans.LinePercent = 3.0
CustInvoiceTrans.SumLineDiscMST = 127

M_DiscPerc = 127 * 100 / (873 + 127) = 12,7

Visualize

Switch to the empty report view using the first button on the left navigation pane. From the Visualization toolbox click the “Card”. This will place an empty card on the report. Drag&Drop the LineAmountMst from the CustInvoiceTrans on the empty card. It should look like this:

Power BI card chart

Next, place a map from the toolbox on the report. Drag&Drop the fields CountryRegionId, City and ZipCode from the LogisticsPostalAddress table on the Location. Drag&Drop the LineAmountMST from the CustInvoiceTrans on the Values Field. The map should look like this:

Power BI map chart

Add a new Gauge to the report and use the Measure M_DiscPerc as value. You cannot set a hardcoded Min. and Max. value in the data properties. Switch to the Format view using the pencil icon.  In the group Gauge Axis, set the Min. Value 0, the target value to 3 and Max. Value 100. Depending on your data, the gauge may look like this:

Power BI gauge control

Next a donut chart to visualize the revenue per item type. Drag&drop the LineAmountMST from the CustInvoiceJour on the value property of the donut chart and drag the TypeName from the InventTable.

Power BI donut chart

Place a column chart on the report to visualize the revenue per customer group. Place the LineAmountMST in the Value field. Use the CustGroup from the CustTable as Axis. Change the sort order to LineAmountMST by using the […] Dropdown Menu in the upper right corner of the chart.

Power BI column chart

Finally, add a line chart on the report to visualize the revenue per year. Place the LineAmountMST from the CustInvoiceTrans on the charts value field and put the InvoiceDate from the CustInvoiceTrans on the Axis field.

Power BI line chart

Fine-tuning

Give each chart a meaningful name. Change the size for the text to fit your report style. Switch to the data view. Change the column names into something more meaningful for an end user e.g. LineAmountMST to Amount. Change the columns formats e.g. Currency for LineAmountMST, date format for the InvoiceDate.

Multiple Aggregations in X++ Join

At work we’ve recently discussed how to calculate a customers invoice amount for multiple years. One idea was to loop over the customer accounts (while select)  and individually calculate the sum for each year with individual statements (select sum). However, this can be done by a single statement.

CustInvoiceJour     custInvoiceJour;
CustInvoiceTrans    custInvoiceTrans1;
CustInvoiceTrans    custInvoiceTrans2;

while select InvoiceAccount,InvoiceId from custInvoiceJour
group by InvoiceAccount
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1
where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId &&
custInvoiceTrans1.InvoiceDate >= str2DateDMY(‚1.1.2011‘) &&
custInvoiceTrans1.InvoiceDate <= str2DateDMY(‚31.12.2011‘)
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2
where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId &&
custInvoiceTrans2.InvoiceDate >= str2DateDMY(‚1.1.2012‘) &&
custInvoiceTrans2.InvoiceDate <= str2DateDMY(‚31.12.2012‘)
{
info( strFmt(„Customer:%1 2011:%2 2012:%3“,
custInvoiceJour.InvoiceAccount,
custInvoiceTrans1.LineAmount,
custInvoiceTrans2.LineAmount));
}
//Customer:DE-001 2011:3.814.145,50 2012:3.842.223,00

This will result in the following output

Mutliple sum() in X++ Join

Check values for customer DE-001 with excel

Check Mutliple sum() in X++ Join

Stacked Bar chart in Access

Recently a colleague asked how to display a stacked bar chart in Access.

Create a table where one column is used to group data e.g. Year, and additional fields containing values to be stacked e.g Revenue. For example, the columns Revenue by Item Group will be stacked by Year.

Access bar chart data

Next, create a new form and add a diagram object to the form

Add a chart to access

In the diagram wizard select your table with data. Next move the Year and the Revenue fields from the table to the diagram data box.

Add data fields to diagram

Next choose a bar chart. There is no stacked bar chart in the wizard.

Choose bar chart

Drag and drop the year to the axis drop box. Drag and drop the Revenue fields to the data box on the left. By double click on field in the left box you can choose the aggregation function e.g. Count, Sum, None

Assign fields to data and axis

Preview the diagram, it should look like this

Preview bar chart in access wizard

Finish the wizard. A diagram is placed on the form.

Bar chart in access form

Double click the diagram will open the designer view. Here right click on the diagram an open change the diagram type to stacked bar chart

Change chart type to stacked bar chart

Format the diagram as you like. Save your design and open the form

Stacked bar chart in access form

Using Bing Maps app in Excel to visualize inventory on hand data

This video shows how to use Bing Maps app in Excel to visualize on-hand data