DAX2012R3CU9 – DIXF – Automate import/export without customizations

The Microsoft Dynamics AX 2012 Data Import/Export Framework(DIXF) is an AX module import and export data in Microsoft Dynamics AX. We often use it in data migration projects to load legacy data from old systems. I was wondering if I could use DIXF as an automated integration, without any customizations. I wanted to see if I could have a folder where new customers are dropped in a folder, and then the DIXF automatically picked up the file, and imported it.

My first step is to have a small and minimalistic Excel sheet, that users can paste in the new customer records. This is how my Excel sheet looks like:

Most of these customers exists from before, but the last record is a new customer that don’t exists in my database.

The recommended process of setting up an import/export process is described here.

The first step is to create a source data format:

I then determine what entity to use, and create a target entity

When I do this, the mapping is done automatically for me, and I don’t have to understand all the database related complexity.

My next step is to create the processing group

 

I then click on the Entities in the processing group, and I select my created entity and that I want to use my created Excel source data format. I also select a sample file to see if the mapping is OK.

I then just check the mapping from Excel to the staging format, and make the necessary corrections.

My next step is to go back to the processing group, and to make the necessary batch job for automatic processing.

As you see here, I set the “type” to Directory, that DIXF will scan for new files. I also specify directories for processing, completed and error. I have therefore created the following directory structure for each integration:

The other important thing is the “Execute target step”. This this used for also executing the step that transfers data from the staging table to the target tables.

I then want this to be work in batch, so I enable the batch processing.

And then I need to wait for an entire minute……… I then saw that the file was moved from the 1_new folder, and ended up in the ¤_Completed folder.

I also see in the execution history, that the files was imported into the staging tables, and then imported into the target tables.

In my customer overview, I now see that I have a new customer, but is also made sure that other related data as addresses, and phone etc was created.

This concludes how you can use DIXF to automatically import data. What I can now do to import data, is just to create my Excel file, and then dump it into the right folder (.\1_New), and then the batch system take care of the test.

If you wonder all entities that are “out-of-the-box” supported from Microsoft, then take a look here. If still something is missing, you can always ask a developer to assist in creating the DIXF entities you need.

Happy DIXF’ing J

 

DAX 2012 R3 – Retail Channel POS reports

Both ePOS, mPOS and the upcoming AX7 CloudPOS have a built-in feature for showing small reports and KPI’s directly on the POS. These reports are not running against the Dynamics AX database, but against the retail channel database connected to that specific terminal.

The great thing about these reports, is that it does not require any heavy development to define these reports. They are just XML report definitions, that queries the database for specific columns or stored procedures. This means that we easily can create new reports based on the direct reporting requirements. In the report, shown below a report called “Sales by staff” is shown. Here the staff name, number of transactions, sales amount and average sales amount is shown.

The report can also be shown as graphics.

The report definitions are located in the RetailàSetupàChannel report configuration.

We see here the report definition, and it can look a bit cryptic. But let’s format the XML to better understand the definition. We here see that the retail report basically have 3 sections. The dataset, parameters and the reportcharts.

As we can see here, the dataset is referring to a stored procedure, with 3 parameters; Channel, start-date and end-date. If we open the SQL stored procedure we can see the exact implementation of it.

We see here that the actual source tables are an inner join between retailTransactionTable and retailStaffTable. We can also see that the amount is “retailTransactionTable.paymentAmount”. This amount is the amount of what the customer is actually paying inclusive taxes.

But a US-based customer of me asked if it is possible to make some minor changes, and just show the reporting exclusive taxes. To do this, it means that we must do some changes. We cannot use the existing standard stored procedure. We could surely create new stored procedures, but rolling this out to hundreds of terminals would take a lot of time. But we have the option to use an actual query in the report XML definition instead.

The first step is to try to create a SQL that retrieves the sales amount minus taxes. As far as I have interpreted the table retailTransactionTable, it seems that the field I can use is the -1*[NETAMOUNT]. Here is the query tested directly in SQL Manager, and I have “yellowed” out the differences compared to the script used in the stored procedure CRT. GETSALESBYSTAFFREPORT

It seems to be working, and the next step is to create a new XML report definition. I therefore took the report “101 Sales by staff”, and created a “101_US Sales by staff”, with the following XML report. Here I have colored the differenced to the original report in yellow. As you can see here, I use a query, instead of a stored procedure.

I also localize the labels in the report

To have the new report available to the users, I need to run the CDX Async job to send data to channels.

  1. In AX, go to Retail > Periodic > Data distribution > Distribution schedule


  1. Select job 1110, click Run now


  1. Wait couple of minutes for the job to finish

I then have the new retail report available.

Then I get my POS report, where the amounts is without taxes J

Happy DAX’ing !

DAX 2012 R3 CU9 Retail database version mismatch and GAC issues

When upgrading the Contoso AX 2012 R3 CU8 to AX 2012 R3 CU9 and then trying to open the ePOS, you might get the following error. “Database version mismatch. You must either apply a Retail POS hotfix or upgrade the Retail POS database

In the event viewer you might see the following:

A TechNet article gave me some indications that the issue was related to the CreateDatabase.dll in the Retail Channel Utility.

My trick to make this work again was to open the installation folder for CU9, and apply the retaildatabaseutility.msp

This upgraded the CreateDatabase.dll to include upgrade to version 6.3.105

When I then re-run the Retail Channel Configuration Utility on the channel database and on the offline database

After successfully upgraded the channel database, when trying to startup the POS, I get the following error; Could not load type ‘Microsoft.Dynamics.Retail.Notification.Contracts.SignatureCaptureConfirmation’ from assembly ‘Microsoft.Dynamics.Retail.Notification.Contracts, Version=6.3.0.0, Culture=neutral..

And the POS screen shows no login, just the following “corrupted” screen.

I raisted a service request to Microsoft, and I got my answer today. It is related to All .NET applications will check first if the required assembly is available in the Global Assembly Cache (GAC) before searchin in the AppBase folder.

If we look at the C:\Windows\Microsoft.Net\assembly\GAC_MSIL we see the following

The date is older than the CU9. Microsoft explained me that this issue occures, because we have a “one-box” installation, and As we didn’t redeploy the Online Store after upgrading to CU9, the old assemblies were still in the GAC, causing the error. This would not come up in a real situation where all components are installed in separated server, but with this being an all in one environment we had this side effect.

The solution is therefore to uninstall the assembly from the GAC.

1. Check that GACUTIL is located at C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools in the VM-image

2. Run the command “gacutil /u Microsoft.dynamics.retail.notifocation.contracts”

You may need to do this for all Microsoft.dynamics.retail.* in the GAC_MIL folder

Then you can start the ePOS as expected J

Thank you Microsoft J

DAX 2009 – Shipping overview, a mirror of arrival overview

Image

Dear AX 2009 customers. You are not forgotten, and Microsoft have support options available until year 2021 J. I wanted to write a blogpost to show that there still happens a lot of local innovation at the customers that are relying on this version of DAX.

Often, when we talk about purchase orders and sales orders, we often refer to them as mirrors. On the transaction level Purchase and sales can be seen as mirror processes, with just the opposite sign. But in standard AX the processes to handle the supply chain can be quite different. The question was therefore if it was possible to also mirror the supply chain processes. Taking the ideas from arrival overview and mirroring it to a shipment overview.

For receiving many reply on the Arrival overview to group together expected receipts and to create arrival journals. The Arrival overview is actually a quite good tool when you learn to use it. For the more the more advanced outbound logistics shipments are used to handle the picking, staging and loading of the goods. A customer came up the suggestion to create a “shipment overview”, but based on the arrival overview. This way the warehouse operator can search across shipments, but also have some volumetric indication of the load that is being released to the warehouse floor.

The arrival overview differs from other screens, because it actually consists of temporary tables that is build based on a query. The same thing happens here. The shipments are loaded on the upper grid, while the picking lines is loaded on the lower grid. There are advanced queries to control the loading of the data. Since it is a temporary table, the fields like address, weights, pallet quantity is real fields on the temporary table. This means that after the result have been fetched, the user can perform additional search and filtering. Like sorting and filtering the shipments in a based on addresses, pallet quantity, weight etc. Also the ability to multi select shipments or lines to be able to move lines between shipments. The screen will mainly be used for inquiry and for activating shipments.

And in relation to performance.. It is acceptable J

DAX2012 R3 – Playing with Retail CRT

The Microsoft Dynamics AX commerce runtime (CRT) serves as the engine for a retail channel. It connects to a CRT database that stores business data for the channel.. Dynamics AX uses the CRT Architecture in the Retail ePOS, mPOS and in the Sharepoint based eCommerce solution. Also the SiteCore eCommerce solution uses the CRT as the integration to Dynamics AX.

Let’s look at some of the interesting things with CRT.

1. It is a well-documented framework supported by Microsoft. More details on it is available here.

2. It can make it easier to integrate business logics to other eCommerce solutions. Like Magento.

3. We can create CRT web-services that handles information like very advanced retail prices, on-hand shipment, check-outs etc.

4. And MUCH more. This CRT we will have almost everything we need to interact with external system. It Rocks !

Let’s take an example. I would like to utilize the CRT to calculate a sales price with discounts. Price/discounts in AX is very flexible and almost all options you can imagine. Here is a small example of the possibilities we get in Dynamics AX 2012 R3 CU 8.

Let’s say we would like to expose this price/discount structure as a web-service to external parties. Since the CRT is relying on it’s own channel database, it means that the much of the integration is actually not creating a heavy load on the Dynamics AX database. We also have the possibility to “scale out” on the architecture.

The CRT is developed in Visual Studio, and is basically just a bunch of dll’s. I have to admit, that Visual studio, Web-services and is not my strongest side. So I decided to see it I could access and use the CRT framework directly from Dynamics AX. The new call-center module in AX 2012 R3 CU8 is using this, and I wanted to create a very simple job that exemplifies how CRT operates and can be used. Basically I’m looking for this:

I use the standard AX 2012 R3 CU8 contoso demo data, but added the following quantity discount to the Houston store;

Since I know X++, I’ll use X++ job to try this. The infolog I got was the following:

Here we see that the CRT returned the sales price, discounted price, and also information like total amount. Surely it can give everything else, but I wanted to keep the example easy to understand.

I created the following job to play with the framework, and it gives you an idea of how to interact with the CRT from AX.

static void demo_CRT_GetSalesPrice(Args _args)
{
    //To test this code, use the AX 2012 R3 CU8, and use the USRT company
    Microsoft.Dynamics.Commerce.Runtime.Data.IPricingDataManagerV2                pricingManager;
    Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.DiscountParameters priceParameters;
    Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine                       crtSalesLine;
    Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction                crtSalesTransaction;
    System.Object                                                                 roundingRule, currencyConverter;
    CLRObject                                                                     clrSalesLines, enumeratorSalesLine;
    CLRObject                                                                     clrObjectSalesLines;
    System.DateTimeOffset                                                         activeDateTimeOffset;
    System.String                                                                 lineIdString;
    System.String                                                                 customerPriceGroup = "";
    Price                                                                         price;
    Qty                                                                           qty;
    InventDim                                                                     inventdim;

    RetailStoreTable    storeTable = RetailStoreTable::find("HOUSTON");   //Lets use houston as an example here
    RetailChannelRecId  channelId = storeTable.RecId;                     //This is the recid for a retail channel. A retail store is an extension of a retail channel.
    CurrencyCode        currencyCode = storeTable.Currency;
    appl.setDefaultCompany(storeTable.company(), false);                  //Setting default company to houstons company

    //Creating a unique line ID
    lineIdString = System.Guid::NewGuid().toString();

    InventDim.InventLocationId = storeTable.inventLocation;
    InventDim.InventSiteId     = InventLocation::find(storeTable.inventLocation).InventSiteId;
    InventDim                  = InventDim::findOrCreate(InventDim);

    //Pricing manager : Encapsulates data access to pricing information.
    pricingManager          = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.AxPricingDataManager::BuildDataManager(channelId);

    //Price parameters : types of AX discounts (aka trade agreement discounts, aka not Retail Periodic Discounts) are currently activated and should be allowed on the transaction
    priceParameters         = Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.DiscountParameters::CreateAndInitialize(pricingManager);
    roundingRule            = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.ServiceHelpers::GetRoundingMethod(currencyCode);
    currencyConverter       = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.ServiceHelpers::GetCurrencyConverter(currencyCode);
    activeDateTimeOffset    = RetailPricingEngineHelper::getSessionDateTimeInChannelTimeZone(channelId);

    //Next we create a crtSalesLine, that represents a channel agnostic sales line.
    crtSalesLine = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine();

    crtSalesLine.set_ItemId("0001");        //Lets use product 0001 as our example
    crtSalesLine.set_InventoryDimensionId(InventDim.inventDimId);
    crtSalesLine.set_Quantity(10);          //Lets use quantity 10 as our example
    crtSalesLine.set_LineId(lineIdString);  //Let give this line a uniqe ID

    crtSalesLine.set_UnitOfMeasureSymbol("ea"); //Let use 'ea' as the unit

    //We then create a sales transaction, and add the salesline to this.
    crtSalesTransaction = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction();
    clrObjectSalesLines = crtSalesTransaction.get_SalesLines();
    clrObjectSalesLines.Add(crtSalesLine);                      //This is where the we add the salesLine to the sales transaction

    //Lets calculate the prices
    Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculatePricesForTransaction(
        crtSalesTransaction,
        pricingManager,
        roundingRule,
        currencyConverter,
        customerPriceGroup,
        currencyCode,
        false,
        activeDateTimeOffset);

    //Lets calculate the discounts
    Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculateDiscountsForLines(
        pricingManager,
        crtSalesTransaction,
        roundingRule,
        currencyConverter,
        currencyCode,
        '',
        '',
        '',
        true,
        Microsoft.Dynamics.Commerce.Runtime.DataModel.DiscountCalculationMode::CalculateAll,
        activeDateTimeOffset);

    //And, let's show what the CRT can give us.
    qty   = crtSalesLine.get_Quantity();
    Price = crtSalesLine.get_Price();
    info(strFmt("Normal sales price is %1 when buying %2", price, qty ));

    Price = crtSalesLine.get_DiscountAmount();
    info(strFmt("Discount amount pr unit is %1, when buying %2", price/qty, qty ));

    Price = crtSalesLine.get_NetAmountPerUnit();
    info(strFmt("Net amount pr unit is %1, when buying %2", price, qty ));

    Price = crtSalesLine.get_TotalAmount();

    info(strFmt("Total amount is %1 when buying %2", price, qty ));

Maybe some of you can teach me how to expose the CRT in a web-service, so that we can easily

 

 

 

DAX 2012 end-to-end processes and APQC

With LCS (Life cycle Services), Microsoft have taken steps to introduced the APQC (American Productivity & Quality Center) Process Classification Framework as one of the business modelers.

Microsoft have also created some flowcharts to visualize the APQC processes in dynamics AX.

In some Dynamics AX implementation projects it have been decided to use the APQC model, and the participants(including me) was struggling connecting the APQC model to actual tasks and activities performed in DAX. So I started on a small mental journey to better understand how to use APQC model, and to be able to connect the dots from the classification framework into dynamics AX swim lane process maps.

First I thought that is was possible to take some level 4 APQC try to draw them. It is possible to draw the small and simple activities, but I was struggling drawing an end-to-end processes. Like order-to-cash. I quickly realized that I cannot use the LCS-APQC model to explain end-to-end DAX processes, since end-to-end processes often will involve many different departments, functions and processes.

To visualize a complex processes in an end-to-end process like order-to-cash using APQC classification model I could imagine looking something like this.

Here we see the involved processes visualized with the APQC classification. And it makes sense. But it does not tell you how to use AX in in this process, because that is described using the level 4 (or 5) in APQC. Like in 8.2.2 Invoice customers, would be a specific process, that we can visualize in Dynamics AX/LCS.

The other thing I realized was that APQC is not about implementing a ERP system, like DAX, but it’s about improving the processes you have(regardless of what systems and applications you have). Some of the activities may be handled in DAX, but many is just how to improve the actual steps the company actually does. Very often it can be the working procedure, involving many systems and internal working processes. So you don’t ask “How does this APQC process look in DAX ?”. You should rather ask “What APQC processes is involved in the selected DAX processes (like order-to-pay).

The APQC model will therefore not tell you how to use and setup Dynamics AX. It will just help you to have a common language to classify your processes and to improve them. When it comes to visualizing Dynamics AX processes in swim lane flowcharts it can be a good idea to use the APQC identifiers as process identification.

My tip when using LCS, is to understand the APQC process identification model, and use it as a repository and building blocks when you create your own processes.

By referring to APQC on your own business model you can start on process improvement project for that specific process. You will can also evaluate to implement the benchmark indicators as APQC exemplifies.

My quick summary is therefore;

1. Do I like LCS, and will use it à Yes, and I will use it for implementation and DAX process visualizations.

2. Do I like APQC à Yes, and I will use it for classification, benchmarking and process improvements.

3. Do I think that the LCS APQC processes reflect what we see at customers à No

4. What would be nice to have in LCS business modeler à More end-to-end processes, where the APQC process identifiers is used would be nice.

Happy DAX’ing !!