Dynamics AX 2012

Pivot Financial Dimensions into a single Record

6. October 2020 Leave a comment

At work we had the requirement to provide a SQL view of PurchLine Records including their financial dimensions. However, since Dynamics AX the financial dimension data model has been enhanced to be more flexible. In contrast to older versions where Dynamics AX 2009 supported by default 3 dimensions, you are now free to configure as much as you want.

Financial Dimensions in Dynamics AX 2012

The tables involved are the DimensionAttributeValueSet, DimensionAttributeValueSetItem, DimensionAttributeValue and the DimensionAttribute. The following statement collects the financial dimension values from the image above (RecID for the set is 52565498264).

select
DIMENSIONATTRIBUTEVALUESET.RECID as DIM_ID,
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = 52565498264

The result looks like this:

The PIVOT is used to switch row values into columns. The following statement creates a single record with columns for the dimensions:

SELECT * FROM
(
select
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from 
DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = 52565498264
) AS SourceTable
PIVOT
(
Max(DIM_VALUE)
FOR DIM_NAME IN 
([Department], 
[ItemGroup], 
[CostCenter], 
[BusinessUnit], 
[Project])
)
 AS PivotTable

The result looks like this:

If some dimensions do not hold values it will be NULL in the SQL but the statement will not fail

In order to join the financial dimension pivot record with the transaction like (e.g. PurchLine) add the RecId from the FinancialDimensionAttributeValueSet and join it on the Dimension reference field from the transaction table. Here is an example:

select
PurchId, ItemId, PURCHQTY, PURCHPRICE,
FinDim.Department, FinDim.ItemGroup, FinDim.CostCenter, FinDim.BusinessUnit, FinDim.Project
from PURCHLINE
left join
(
SELECT * FROM
(
select
DIMENSIONATTRIBUTEVALUESET.RECID as RECID,
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
) AS SourceTable
PIVOT
(
Max(DIM_VALUE)
FOR DIM_NAME IN ([Department], [ItemGroup], [CostCenter], [BusinessUnit], [Project])
) AS PivotTable
) as FinDim
on PurchLine.DefaultDimension = FinDim.RecId
where PURCHID = '000081' and DATAAREAID = 'USMF'

The result set are two records, one for each PurchLine, and the corresponding financial dimension values:

Filed under Development, Dynamics AX Tagged with Dynamics AX 2012

Use Power BI dataflow to decouple report design from ETL logic in an ERP upgrade project

22. September 2020 Leave a comment

A common requirement during an ERP upgrade project (e.g. from AX 2012 to D365 Finance) and transition phase is to include both systems in the BI or reporting environment. Because of its tight integration with Dynamics, in many cases PowerBI is the preferred reporting and BI platform. PowerBI is capable to combine different data sources like OData feeds from D365 and SQL connections via gateway. However, for the person developing reports, it will become complicated to integrate cloud and on-prem datasources. For example, to create a sales report, one would need to include the Customers, SalesInvoiceHeader and SalesInvoiceLine entities as well as the CustTable, DirPartyTable, CustInvoiceJour and CustInvoiceTrans tables.

Different data sources in one PowerBI report

One way to address this issue can be to separate ETL logic from report design. PowerBI supports this approach by using dataflows. By using dataflows you can place PowerQuery logic direct in the Microsoft cloud and offer reuseable data artefacts. People designing reports simply connect to the dataflow but are not concerned with the ETL logic required to combine data from the old AX installation and a new Dynamics 365 ERP cloud environment.

Use PowerBI dataflow to decouple ETL logic from report design

Example

From PowerBI workspace create a new entity using dataflow. Choose the OData feed for Dynamics 365 and provide the URL for the CustomersV3 entity.

OData feed for entities from Dynamcis 365 Finance

Clicking next will open the Power Query editor and load the customers from Dynamics 365 Finance. Remove all the fields you don’t need in your application. In this example I’m using the DataAreaId, Account, Name, Group, Address and Delivery mode + terms.

PowerBI dataflow based on Dynamics 365 Finance OData CustomerV3 entity

For an on-premises AX 2012 installation you need to install a data gateway, so PowerBI can access the local SQL database. If you already have a gateway, create a new dataflow in PowerBI and use the SQL connection. I’d recommend to create a view on the database instead of loading tables in PowerBi.

CREATE VIEW [dbo].[PBIX_Customer] AS
select
DataAreaId, DirPartyTable.NAME, ACCOUNTNUM, CUSTGROUP, TAXGROUP, LogisticsPostaladdress.ADDRESS, DlvTerm, DLVMODE
from CUSTTABLE
join DIRPARTYTABLE
on CUSTTABLE.PARTY = DIRPARTYTABLE.RECID
join DIRPARTYLOCATION on
DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY
join LOGISTICSPOSTALADDRESS
on DIRPARTYLOCATION.LOCATION = LOGISTICSPOSTALADDRESS.LOCATION
where
LOGISTICSPOSTALADDRESS.VALIDFROM <= GETDATE() and LOGISTICSPOSTALADDRESS.VALIDTO >= GETDATE()
GO

Choose SQL Server data source for PowerBI dataflow

Select the data gateway and provide a user to access the database

Connect a PowerBI dataflow to your on-premises AX 2012 database using a gateway

Select the view and load the AX 2012 data to PowerBI. Save the dataflow

Dynamics AX 2012 customer data via data gateway

After you have created both dataflows return to your workspace, go to your dataflows and refresh both to load the data.

Refresh dataflow from Dynamics 365 Finance and Dynamics AX 2012

Next, create a third dataflow to combine the data from the Dynamics 365 Finance and AX dataflow. This time choose to link entities from the other dataflows:

Select both dataflows

In the Power Query Online editor rename the fields in both dataflow entities so you can append both queries. Be aware that Power Query is case sensitive and dataAreaId is not the same as DATAAREAID. When you have done this, append both queries as new one.

From the new query make sure to remove duplicate customers

If your have a PowerBI Pro but not a Premium subscription, deactivate load of the underlying queries.

Save and refresh the dataflow. From the settings schedule the refresh and endorse the dataflow as “Promoted” or “Certified”. This is not necessary but it adds a label to dataflow and your report designer users see that they can trust the datasource. In PowerBI Desktop open Get-Data and choose PowerBI dataflow as data source:

Select the merged Customer data source.

Promoted and certified PowerBI dataflows

You can use the dataflows in your PowerBI datamodel but dont have to worry about the logic behind

Linked dataflow sources in a PowerBI data model

Conclusion

Using dataflows has some advantages. It helps you to decouple ETL logic from design logic. Especially when working with older versions of Dynamics AX you have to have deeper knowledge about the data structure. Another advantage is the reuse of dataflows. Typically you are not creating 1 single report, but more reports that require the same dimensions e.g. customers. By using dataflows you don’t need to maintain the load and merge in multiple PowerBI files.

Filed under BI, Dynamics 365 Finance and Operations, Methodology, PowerBI Tagged with Dynamics 365 FO, Dynamics AX 2012, PowerBI

Start and open a specific record in Dynamics Ax 2012

1. July 2020 Leave a comment

At work we recently discussed ways to startup Dynamics AX 2012 and navigate to a specific record. The requirement was to open Dynamics AX from a DMS client that manages invoices and other documents.

There are different approaches to achieve this goal. One way is to use the Startup Command framework which is used to instruct Dynamics to execute several functionalities during startup e.g. compile, synchronize or navigate to a menu item. In order to startup a menu item, you provide an XML file which contains the menu item name and point to this file from the .axc Dynamics AX configuration file.

Startup Dynamics AX 2012 with an XML configuration file

Reference the record in the startup XML file

For many forms in Dynamics AX it is sufficient to call the corresponding menu item with an Args object that holds the record. To specify a record in Dynamics AX you need to provide at least the TableId and the RecId. For example the Customer “Adventure Works” can be defined by using TableId 77 (CustTable) and the RecId 22565422070. Add two additional attributes RecId and TableId to the XML file which is used to open the CustTable form. The XML file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<AxaptaAutoRun 
 exitWhenDone="false"
 version="4.0"
 logFile="$HOME\axEXProd.log">
<Run 
 type="displayMenuItem" 
 name="CustTable" 
 RecId="22565422070" 
 TableId="77"/>
</AxaptaAutoRun>

Modify the SysAutoRun.execRun() method

At the SysAutoRun class, open the execRun() method. At the top declare the following variables:

RecId recId;
TableId tableId;
Args arg = new Args();
Common common;
DictTable dictTable;

At the bottom, find the place where a menu item is started. Before the if(mf) statement add the following code to read the RecId and TableId from the XML file and select the corresponding record:

recId = str2int64(this.getAttributeValue(_command,'RecId'));
tableId = str2int(this.getAttributeValue(_command,'TableId'));
if(recId != 0)
{
   dictTable = new DictTable(tableId);
   common = dictTable.makeRecord();
   select common where common.RecId == recId;
   arg.record(common);
}

Within the if(mf) block, add the Args object when the menu fuction is called to pass the record.

mf = new MenuFunction(name, menuItemType);
if (mf)
{
   this.logInfo(strfmt("@SYS101206", mf.object(), enum2str(mf.objectType())));
   mf.run(Arg);
   result = true;
}

Test your configuration

Now you can test your configuration. Create a new .axc file and point it to the XML file. Make sure the XML file has a valid TableId and Recid property. Start Dynamics AX using the .axc file and the defined menu item should open and view the record.

Filed under Admin, Development, Dynamics AX Tagged with Dynamics AX 2012

SSRS: Error when opening design of duplicated report

5. March 2012 1 Comment

When you duplicate an SSRS Dynamics AX 2012 report in Visual Studio, and you try to open the design node you may get an error message like “object instance not set to an object”. The reason is that the data set parameters contain the name of the report e.g. SalesQuotation. Now that you have duplicated the report its name is CopyOfSalesQuotation and the report is broken.

Compiling the report project brings up a list of errors.

Navigate to each data set parameter and change the name of the report to match the new name e.g. CopyOfSalesQuotation instead fo SalesQuotation

The fixed parameter should look like this

Finally the report designer is working

Filed under Dynamics AX Tagged with Dynamics AX 2012, SSRS

Dynamics AX 2012 on Windows Server 8 Beta with SQL 2012 RC0

3. March 2012 Leave a comment

Microsoft recently released Windows Server 8 Beta and Windows 8 Consumer Preview. So my first though was about running Dynamics AX 2012 on Server 8 and Windows 8. However, installing AX 2012 on Server 8 failed due incompatibility with windows installer. But Windows Server 8 and Windows 8 Betas still come with an upgrade option. So I had the idea install AX 2012 on Server 2008 R2 and upgrade to Windows Server 8 Beta.

Prolog (the IBM x3400 thing)

We’ve bought an IBM x3400 M3 server for a customer. We’re still waiting for some hard disks to deliver, so the server is unused and becomes my testing platform for the moment. Installing Windows 8 Beta was straight forward, but as mentioned installing AX 2012 failed. So next I failed installing Windows Server 2008 R2 because no hard disk was found (missing driver). The solution:

Download drivers from IBM IBM ServeRAID M Series and MR10 SAS Controller Driver for Windows
Extracting them, copy the win2k8r2-64 directory on USB stick (2nd Image, Yellow USB stick)
Start Windows Server 2008 R2 installation from DVD and provide drivers from USB

IBM x3400 M3 and Raid driver on USB for Windows Server 2008 R2 installation

Windows Server 2008 R2, SQL Server 2012 RC0

Next I’ve joined the new 2008 R2 server to our domain. Loving Betas, Previews and other unstable stuff I’ve installed SQL Server 2012 RC0 to serve as database server for AX.

Installed .NET Framework 3.5.1 Feature using Server Manager
Installed .NET Framework 4.0 Full package
Installed NDP40-KB2390372-v2-x64 patch for .NET 4.0
Installed C++ Runtime Environments from AX 2012 DVD
Installed SQL Server Native Client
Installed Report Viewer 2012 from AX 2012 DVD
Installed Identity Foundation from AX 2012 DVD
Installed Open XML SDK for Office from AX 2012 DVD
Installed SQL Server 2012 RC0 full featured

SQL Server 2012 RC0 Full Featured

Pizza Break

Ham, Eggs, Bacon, Onion and Corn, Coke and Tiramisu

Admin’s workplace

Dynamics AX 2012

AX on SQL 2012 RC0 is a little bit tricky, because even database creation failed. So I’ve set up an AOS using another SQL Server 2008 R2 with an existing AX 2012 database and baseline. Next I restored an AX 2012 database from a backup in SQL 2012 RC0 and configured the AOS to use it. Works as it should. However, the reporting extensions are not compatible with RC0 and installation failed.

AX 2012 using SQL 2012 RC0

Upgrade to Windows Server 8 Beta
Installed Windows Server Backup Feature from Server Manager and create a backup
Downloaded Server 8 ISO
Extracted it and copied it onto an USB stick using xcopy *.* /s/e/f u:\\
Started Upgrade from DVD or USB
The Upgrade took quite a lot of time until windows was ready

Windows Server 8 Beta Data Center Edition with GUI

Upgrade an existing Installation

Windows Server 8 Beta is starting up, …

Finally! Windows Server was upgraded from 2008 R2 to 8 Beta

Repairing Dynamics AX

Well, Windows as upgraded successfully but Dynamics AX is not working any more Trauriges Smiley Starting the AOS results in an error. Reviewing the windows event error logs brings up the details:

Object Server 01: Error accessing registry: Cannot open key SYSTEM\CurrentControlSet\Services\Dynamics Server\6.01. Error code: 0

Object Server 01: The directory "C:\Windows\system32\\bin" does not exist or access to it has been denied by the operating system.

Object Server 01: The home directory for Axapta (C:\Windows\system32\) does not match the required structure or can not be accessed. Please check installation, configuration and access rights.

Object Server 01: Server terminated unexpectedly with 10 exitcode.

The reason is an missing registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\DynamicsServer\601

I solved this issue by importing an AX configuration from another server’s registry

Opened an RDP session to an other 2008 R2 server running an AX 2012 AOS
Start > Run > regedit
Navigated to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\DynamicsServer\60
Exported the registry key as file (Context Menu > Export)
Copied the .reg file the Server 8 Beta desktop and imported it
There are a lot of settings and paths in the registry, make sure these settings apply to your w8 server
Started the Server Configuration Utility and changed the database setting back to localhost
Started the AOS
Started the Client