Dynamics AX and Power BI

Update – 8th October 2015. Please find my most recent verdict on using Power BI with Dynamics AX here.

Just before Christmas last year, Microsoft announced significant new features under the ‘Power BI’ name. Functionality called Q&A caught my eye. It allows you to type in a question to create a report (or dashboard) and is, frankly, astonishing. Although this had been around for nearly a year, I think it was the first time that it became available to those without an Office 365 subscription. It wasn’t until March this year that those of us in Europe were able to get their hands on it, and I’ve finally got round to finding out what it can (or can’t) do with Dynamics AX.

So what is this new Power BI? The main part is the PowerBI.com website, which you can use to create, host and share your reports. There is also a tool called Power BI Designer, which you can install locally to create reports. These reports can be uploaded to PowerBI.com for sharing. Power BI is FREE (but please check this link for reasons why you might need to pay for it.)

On the blog of the Microsoft Dynamics AX Solution Archtecture team, Clay Wesener has written a series of five blogs about PowerBI and Dynamics AX. Part 1 is here. When I read them, I was surprised to find they didn’t go down the route I was expecting (and more about that next). I also heard that people were finding it was not possible to connect Power BI to their AX data. ‘How hard can it be?’, I thought. ‘We have a SQL database and some SSAS cubes which are ripe for mining’. And this is where Clay’s blog took a different route.

So, to find out just how hard it could be, I thought I’d install Power BI Designer, point it at my SQL database and we’re away. Well, almost. Whilst Power BI Designer can happily connect to the AX database, and you can upload these files to PowerBI.com, there is currently no support for refreshing the data on reports created this way. And equally disappointing – we cannot use the Q&A functionality on Power BI Designer reports. That’s a shame, not least because I feel like we need a screenshot. So here’s a map I created, showing cities which appear in invoice addresses in the AX demo data:

Cities wth sales in the AX Contoso data set
Cities wth sales in the AX Contoso data set

Note – because its a report created using Power BI Designer, there’s no facility here to build reports using natural language questions. And unfortunately, should I want this map to update next month with the latest sales data, I’d have to upload the Power BI Designer file again.

So lets forget Power BI Designer for now. Remember I said you can create reports directly on PowerBI.com? Lets try connecting PowerBI.com directly to the AX database. Nope, that’s not going to work either. To be fair, the idea is somewhat problematic. PowerBI.com is sat somewhere on the internet. My (fictional) AX database is sat in the basement of the building, and I’ve got lots of firewalls making sure my AX data doesn’t go anywhere near the internet!

But PowerBI.com does have a solution for connecting to an on-premise SQL Server Analysis Services database. That’s great – the AX Role Centers and BI use SSAS. You just need to install a Connector. The connector sits on your network waits for PowerBI.com to ask it for data from your cubes. (And you might need to install Azure Active Directory Sync, but only if your users don’t login to PowerBI.com with their work email address).

That’s the answer then! It’s not all of my AX data, but it’s a lot of the juicy stuff, and the cubes are already built for neat reporting. Unfortunately, not! The Connector currently only supports SSAS running in something called Tabular mode. You don’t need to understand what that is; you just need to know that the SSAS database created by AX is ‘multidimensional’, which is different. Oh, and the Connector also does not support Q&A functionality.

So back to the blog written by the AX Solution Architecture team. What does that say to do? The first solution only works for Power BI running on Office 365. It uses something called an OData feed, which allows AX to publish data via a URL. You put this data into Excel, and save this workbook to Office 365. If you install something called Microsoft Data Management Gateway, Office 365 is able to refresh the data from Dynamics AX automatically. I don’t have an Office 365 account – so I can’t use that. (Although if I did, I would be able to use Q&A on my AX data).

One final solution offered by the Solution Architecture team does not require Office 365, and goes back to the SSAS Connector I mentioned two or three paragraphs above. I already said it can’t be used with the AX Cubes, and that is still true. A solution which involves creating a Tabular database and copying data into it from the multidimensional database is proposed. Whilst not impossible, it’s more than I’m prepared to do for this blog post!

So we’ve got to the end, and I still don’t have my AX data in PowerBI.com, and I’m miles away from being able to do Q&A reports.

Should we just forget it then? Well, no. Power BI is amazing and powerful and does much more than just connect to AX data. And it is currently being updated monthly with new features. For example, this month, Google Analytics was added as a data source to Power BI Designer. Yes – include your website stats alongside (or even within) your reports!

Microsoft have already said that work on connecting PowerBI.com to on-premise SQL data has started. They have said that refreshing of reports created in Power BI Designer is planned. Adding support for on-premise, multi-dimensional SSAS databases is also planned. So my advice for now is to hang on and wait. But in the mean time, use the data sources that Power BI does support to get the hang of it.

The Debugger opens, but is blank

I have a terrible memory. Half of the posts on here exist only so I’ll know where to look the next time I can’t remember something. This post is in the same vein.

Ever attempted to use the Dynamics AX Debugger, only to find that when it opens it is blank?

The Debugger opens, but is blank.

You’ve done everything here to enable the debugger. You’ve even remembered to enable debugging in your user options, and disabled CIL (here).

If you check this link, you’ll see the line ‘On some operating systems it is necessary to run the debugger with administrator privileges’. Easy to overlook, but if you’re getting a blank debugger, start the AX Client with the ‘Run as administrator’ option and try again. Et voilà! It works.

The reason this is necessary might be related to something part of Windows’ UAC called ‘Admin approval’, and if you wanted to investigate this further, you might want to find the section entitled ‘Disabling Admin Approval Mode’ at this link.

‘Generate RMA’ in the Call Center Customer Service form

If you’re using Dynamics AX 2012 R3, you might have discovered the Call center ‘Customer service’ form. If you operate a call center environment, where agents are taking orders and queries over the ‘phone, it’s well worth a look at how this form brings relevant information and functionality together. This post on the excellent organicax blog briefly shows the functionality.

One useful feature on the Customer service form allows the operator to Generate an RMA directly from an invoiced sales order.

The ‘Generate RMA’ button.

Or, at least you’d think that’s what a button which is only active when you select an invoiced sales order and is labelled ‘Generate RMA’ would do! However, even with CU8 installed, the button actually has no real functionality behind it. It opens the Return order form, but doesn’t create a return for the highlighted order. In fact, it doesn’t create a return order at all; you’d have to start by using the ‘New’ return order button.

No return order is created.

I thought this was a shame, because the Customer service form allows you to do nearly everything from one place – everything except create a customer return. And this is why I am delighted that Microsoft have released a hotfix to address this scenario.

The hotfix is KB3037364, and I’ve installed it into a test system running CU8.

Installing hotfix KB3037364

Now, when I hit the ‘Generate RMA’ button, a new return order for the selected customer gets created. And all of the items, prices and quantities from the original order are copied to this return order.

A Return order is created

There is no functionality to ask which items/quantities should be included on the return, but it’s a great start. In any case, the return order can be edited as necessary.

If we take a look into the AOT, what has the hotfix changed? First, there’s a new method on the Customer service form which dictates if the ‘Generate RMA’ button is active.

Is ‘Generate RMA’ allowed

There is also a new method on the ReturnTable form, which is called by the init method if it determines that the new return order was created by the Customer service form.

And with those small changes, the Customer service form now does everything!

Location directive failures in Warehouse management module

This is a quick post about ‘Location directive failure’ functionality in the Warehouse management module in AX 2012 R3. It’s not covered in the Warehouse management implementation guide, and is not mentioned on the TechNet pages either, and therefore worth writing about.

The functionality can be configured in the Warehouse management parameters form, or using it’s own setup form at Warehouse management -> Setup -> Work -> Location directive failures. It doesn’t matter which one you use; you’re setting up the same thing.

Setup Location Directive Failures

It is possible to configure Location directives for several different Work order types. Location directive failure functionality can be enabled or disabled separately for each of these Work order types. Ticking the ‘Stop work on location directive failure’ tick box enables the functionality. Un-ticking it disables it, and is the same as not creating the record in the form.

To demonstrate the effect of this functionality, I will create some Work for a Sales order with ‘Location directive failures’ for Sales orders turned off.

Location directive failure is disabled for Sales orders

In the scenario, there is 209 of item A0001 in inventory, but only 100 in the picking location (FL-001).

The scenario with item A0001

I have configured the location directive to use only the Picking location, and Wave demand replenishments are not enabled. Therefore, AX can only create picking work for quantity 100. A sales order for quantity 150 is created, and released to the warehouse. The Wave is processed and released. The work that is created has two pick lines and one put line. One pick line is for quantity 100 from the picking location (FL-001). The other pick line is for the remaining quantity, and has no pick location. The put line is for the full 150.

Pick work with a blank location

If I roll back the scenario, and re-run it with Location directive failure enabled for Sales orders, we get a different result. The first indication comes from the Infolog produced when the order is released to the warehouse: ‘Shipment was not fully allocated’.

Shipment was not fully allocated

If we look at the generated Work, we can see the effect.

Effect of Location directive failure

This time, only one pick is created. It has the quantity 100. The put also has the quantity 100. What of the remaining 50? If the Work is processed through, 100 become Picked and 50 remain on the Load/Shipment. It would be necessary to add the 50 to another wave, and in my scenario I would need to do something (i.e. replenishment the Pick location) before the wave could be processed.

So we can see that the Location directive failure determines what happens when the location directive fails to find a valid location. If the functionality is disabled, work with a blank location is created. If the functionality is enabled, work is only created when a location can be found. Obviously, it’s up to you whether to enable it in your environment, and on which work order type to enable it.

In the case of a sales order, you might want to switch it off. You know the sales order can’t be released to the warehouse unless the inventory is available for reservation. Therefore, a Pick instruction with no location will force the warehouse worker to hunt down the required inventory knowing it is available somewhere.

But in this post on the AX Community forum, you can see a scenario where the functionality is disabled, and a Min/Max replenishment is created to replenish a pick location with a quantity that does not exist in the warehouse. In this scenario, isn’t it better that the replenishment will only use inventory that is available?

Retail Pricing on standard AX Sales order

In the U.K., a supplier of double-glazed windows has an offer which it has promoted on television for more than fifteen years, and as a result of this I cannot hear the phrase ‘Buy one get one free’, without silently repeating “I say, you buy one, you get one free”. This has gotten somewhat irritating over the last few days, since the topic of this post is about how to use ‘Buy one get one free’ functionality on a Sales order in Dynamics AX 2012 R3. The functionality requires the use of Retail ‘Mix and Match Discounts’ which, until R3, was only available to the AX POS application. With R3, we get Call center Retail channel functionality, and some associated enhancements to behaviour of the Sales order. In previous versions of AX, the only way to get BOGOF functionality was to use Supplementary items. This functionality has one well discussed draw back; there is no way in standard AX for the operator to be prompted to add supplementary items to a sales order. And, the operator could remove the ‘Buy one’ part of the deal from the order, and the customer could still get the ‘Get one free’ part! Both of these problems are addressed by using Mix and Match Discounts.

Before we start, please follow the steps in a previous post about initialising the Retail functionality. If you don’t do this, it doesn’t matter that you follow all the steps below – it won’t work.

We’re going to create the Call center Retail Channel first, but there are some mandatory fields to set on the Call center, so we’ll need to create the data for that.

Info codes
We’re going to need three Info codes. Head to Retail > Setup > Info codes and create three, each of with the Input type ‘Subcode list’. Name them as you like, but you can see their purpose from in screen shot below. Aside from being mandatory when creating a Call center, they are not needed for the functionality we are testing.

Create three Info Codes

Warehouse & Customer We’re also going to need to specify a Warehouse and a Default Customer. I assume you’ll already have a warehouse. The Default Customer is, according to the help, used when no Customer exists on the transaction. I don’t know how I will create a Call Center Sales order without defining a customer, but to be on the safe side, I created a customer for this purpose.

Modify ‘Organisation Number’ number sequence (optional)
I’m going to change the number sequence that is used when assigning Organisation Numbers, since I don’t want a meaningless number to be used. You don’t have to do this, but you’ll not be able to change the Operating unit number of your Call Center in the next step if you don’t.

Modify the Organisation number Number sequence

Create the Call Center Head to Retail > Common > Retail channels > Call centers, and hit ‘New Call Center’. Enter all the mandatory data, as per the screen shot below. I’ve also unticked ‘Enable order completion’, since that functionality is not required, and gets in the way of our tests later.

Create the Call Center

In case we forget, we’ll add our user to the Call center now. Click the ‘Channel users’ button, and add your User Id.

Add your user to the Call Center

Now, you’ve done enough to enable Retail functionality on standard AX Sales orders. If you create a new sales order, you’ll see on the header that ‘Retail sale’ is ticked.

Retail Sales Order

From now on, the Sales order can behave slightly differently. But we haven’t done enough to enable Mix and Match pricing. (And, if at any time you want to go back to creating normal sales orders, remove your user from the Call center).

Organisational hierarchy We need to create a Organisational Hierarchy and add our Call Center to it. This setup is required by the Retail Assortment (you can read more about this here) and would normally represent the structure of all your Retail channels. An Assortment dictates the items that are going to be available in a store, and by using a Retail Organisational Hierarchy you can define which items are available to different groups of stores. Head to Org Admin -> Setup -> Organisation -> Organisation hierarchies and create a New record (I’ve named mine ‘Retail organisation hierarchy’). Hit the ‘Assign purpose’ button. In the form that opens, choose ‘Retail assortment’ (on the left) and then hit ‘Add’. Select the Organisation hierarchy you just created, and hit OK. If that sounded confusing, check the screen shot below. Whilst you’re here, you might as well set it as the default Retail assortment hierarchy.

Create an Organisational Hierarchy for the Retail Assortment

Back on the ‘Organisation hierarchies’ form, select your new Organisational hierarchy and hit ‘View’. You’ll see your hierarchy is empty. Hit ‘Edit’, and the Action pane changes appearance. Click Insert -> Retail channel. Select your Call Center, and click OK. Click ‘Publish and close’ to save the hierarchy. Enter today’s date and hit the Publish button.

Publish the Hierarchy

Category hierarchy Next, we’re going to create another hierarchy. This time it’s a Category hierarchy for our Retail Products – these being the items that we wish to sell via our Call Center. Head to Product information management -> Setup -> Categories -> Category hierarchies, and create a New one with a useful name; I called mine ‘B2C Call Center’, since it will contain products we’re going to sell in the Call Center I’ve called B2C. (Thinking back, it would have been better to call it ‘All Retail Products’ or similar, since we can have only one of this type of hierarchy). On the next form, click ‘New category node’ and name it ‘All’. Since it is the top node in our Category hierarchy, it will contain all the products which we add to this Category hierarchy.

Create a new Category Node

I have three products that I want to add to this Category hierarchy, so I use ‘New category node’ to create three categories below ‘All’, with a bit of a hierarchy going on.

Create a Hierarchy

On that form, that’s all we’re going to worry about. But we do have to set the Category as our Retail Product Hierarchy, which we do at Product information management -> Setup -> Categories -> Category hierarchy types (see the screen shot below).

Set the Category as the Retail Product Hierarchy

We now need to add products to our Category hierarchy. Make sure that some Released Products exist in your company, and head over to Retail -> Setup -> Category hierarchies -> Retail product hierarchy. Expand the ‘Products’ fast tab, and expand all your hierarchy nodes on the left. Select each sub-node and use ‘Add’ to add products at each node.  When asked if you wish to ‘Update Product properties’, hit No. When that is done, head to the Released products form, and on one of those products hit the ‘Product categories’ button in the Action pane (‘Product’ tab -> ‘Setup’ area). You’ll get confirmation of the selection you made, and indeed we could have added Products to our Category Hierarchy this way too.

Next, we create an Assortment. Head to Retail -> Common -> Assortments and hit New Assortment. I enter an Assortment Id (again, I modified the relevant number sequence to allow this – it’s not important) and an Assortment name. Then, on the ‘Retail channels’ fast tab, I hit ‘Add line’. Here, you’ll see evidence of the Organisation Hierarchy we created at the start of this exercise. With a full store hierarchy, it would be possible to add a group of stores or call centers to this Assortment. Go ahead and add your Call center to the Assortment.

Create a Retail Assortment

Then, on the ‘Products’ fast tab, hit ‘Add line’. Set the Category to ‘All’, and make sure ‘Include’ is selected.

Add the Products to the Assortment

When you’re done, hit the Publish button in the Action pane. This fires off a Batch job called ‘Retail Assortments’, and you should check to see that the job reaches the status ‘Ended’.

The batch job Publishes the Assortment

Back on the Assortments List Page, your Assortment should have the status ‘Published’.

The Published Assortment

Retail Catalogue
The last step is to create a Retail Catalogue. Here, we choose the Products from our Assortment that we want in our Catalogue. Unfortunately, to Approve a Retail Catalogue, we have to configure Workflow. Whilst it is not hard, I have not included all the steps to do this since it relies on Workflow actually working in your environment. If I get some comments to include this setup, I’ll add it at a later date. Please configure the workflow to approve a Retail catalogue now, since you won’t be able to get past the ‘Validate’ stage if you don’t.

Head to Retail -> Common -> Catalogues -> Catalogues. Hit ‘New Catalogue’. Now, I’m setting this up in an empty instance of AX, and when I did this I got the error ‘Unable to find any record in the DirPartyTable table for current user userid’. I could not create a catalogue. To get past this, I had to create an Employee for myself, and assign that Worker to my AX user id. Then, I created a Catalogue called ‘Spring 2015’. I can see it has stamped my Worker as the Owner of the catalogue. I add my Call center on the ‘Retail channel’ fast tab, and then hit ‘Add products’ in the Action pane. As with the Assortment, I add All products to the Catalogue.

Create a Retail Catalogue

Then, I Validate the catalogue.

Validate the Retail Catalogue

I am them able to submit the catalogue to workflow for Approval. Once approved, Publish the catalogue using the button on the Action pane.

The Catalogue is Published

Mix and Match Discount
Now we get to the setup for the Mix and Match discount that we are going to use to get the Buy One Get One Free functionality. I have already posted a Price/Discount Agreement to set the price of my item at £500 for all customers.

The sales price of my item is £500

Next, I need to create a Retail Price Group. At Retail -> Setup -> Pricing and discounts -> Price groups, create a new Price group and tick the ‘Retail’ tick box.

Create a Retail Price Group

Go back to your Call center (Retail -> Common -> Retail channels -> Call center) and add this price group to it.

Add the price group to your Call center

At Retail -> Setup -> Pricing and discounts -> ‘Mix and match line group setup’, create a line group which will cause the discount to be activated if two products are bought.

Create a Mix and Match Line Group

Finally, we need to create the Mix and Match Discount. Head to Retail -> Common -> Pricing and discounts -> Mix and match discounts. Create a new Discount and give it a name. Hit the ‘Price groups’ button, and select your price group. Hit the ‘Mix and match line groups’ button and select the line group you just created.

Per the screenshot below, set the Discount type to ‘Least expensive’. Set it that 100% discount will be given to the least expensive line. On the Lines fast tab, add your Product(s) and set the Line group.

Create the Mix and Match Discount

Then set the status of the discount to ‘Enabled’. And you’re done! All you need to do now is test it.

Buy one, get one free
Create a Sales order for the item for a Quantity of 1. As expected, no discount is applied:

No discount is applied

Alter the quantity to, say, 2. You are prompted to re-calculate all prices. HIT ‘YES’!

Buy One Get One Free!

You’ll see the line splits into two. One line has the full unit price, and no discount. The other has a discount amount equal to the unit price and zero net amount.

I say, you buy one, you get one free 🙂

Notice that if you attempt to delete the line which has no discount, AX automatically ensures the discount on the remaining line disappears.

So, a lot of setup to get one small result, but it also opens up a whole possibility of Retail functionality on the sales order. But this is enough for one post. Hope it helps!

Windows Mobile Device Portal – bound to Port 0

I don’t know how, but the website for the Dynamics AX Windows Mobile Device Portal on my AX 2012 R3 CU8 solution demo virtual machine occasionally becomes bound to port 0.This seems to annoy IIS, and as a result I get ‘This page can’t be displayed’ when I attempt to visit the mobile device portal:

This page can't be displayed
The mobile device portal cannot be displayed.

In IIS Manager, I can see that this is because the site is stopped:

Site Stopped
This mobile device portal site is Stopped.

Attempting to start the site produces the error ‘Value does not fall within the expected range.’:

Cannot start website
The IIS site throws an error when you attempt to start it.

If you track down the cause of the error, you will find that it is because the site is bound to port 0. It is easy to fix this in IIS:

Remove Binding
Remove the Binding to Port 0.

After removing that particular binding, the mobile device portal site starts without a problem.

Initialise Retail for Call Center functionality

The new Call Center functionality in Dynamics AX 2012 R3 is enabled in the Licence Configuration as part of the Retail functionality.

Call Center Licence Config
The Call Center functionality is enabled as part of the Retail Licence Configuration

You may think that enabling this is all you need to do to take advantage of all the functionality that Call Center offers, but some of it will only work if the Retail module has been initialised. For example, whilst you can use the Call Center -> Customer Service form without initialising the retail functionality, you cannot use Retail pricing on Sales orders until retail is initialised.

How to initialise retail functionality is described on TechNet, at the bottom of this article. The ‘Initialise’ button can be found on the Retail parameters form, and TechNet describes how the Legal Entity should have a Language and Address defined before you begin the procedure. An AOS restart will also be required.

Initialise Retail
Initialise the retail functionality from the Retail parameters form

This process must be done in each Legal Entity that will use Retail functionality. For example, in the Microsoft Dynamics AX 2012 R3 CU8 Solution Demo Package V1.0, retail is initialised in only five of the twenty Legal Entities.
To check if Retail is initialised in the current Legal Entity, you can use the following Job:

 static void IsRetailEnabledAndInUse(Args _args)
     boolean isEnabled;
     boolean isInUse;
     isEnabled = isConfigurationkeyEnabled(configurationKeyNum(Retail));
         isInUse = RetailParameters::isRetailInUse();
             info(strfmt("For the Legal Entity %1, Retail is both enabled and Initialised.",curext()));
             info(strfmt("For the Legal Entity %1, Retail is enabled but not Initialised.",curext()));
         info("Retail is not enabled in the license configuration.");

My thanks go to Mohsin Zia for the code, which you use at your own risk, and only on AX 2012 R3.

If Retail functionality has not been initialised, the Job will display the following message:

Retail Not Initialised
Retail functionality has not been initialised.

After completing Initialisation and restarting the AOS, all Call Center functionality will be available.

Trade Agreement Lines – Error when editing in Excel

In Dynamics AX 2012 R3, it became possible to edit Price / Discount Agreement Journal Lines in Excel.

Edit In Excel
You can edit Trade Agreement Lines in Excel

This is great if you’ve a large number of lines that you want to make adjustments to; for example, you can use the fill handle to populate a field on every record very quickly. It’s also useful if you find the functionality around copying, revising and adjusting trade agreement lines quite clunky.

You may come across an error when publishing changes back into the price / discount journal:

Microsoft Dynamics AX Office Addin
An error occurred. The data in the workbook and the behavior of any add-ins may not be correct. Consider contacting your system administrator about this problem.

There was an error downloading

Publish Trade Agreement Error
Error when Publishing changes to a Trade Agreement

To resolve the problem, you need to deploy the PriceDiscServices Service Group. From within the AOT, expand the ‘Service groups’ node. Find PriceDiscServices, and right-click on it. Choose ‘Deploy Service Group’.

Deploy Service Group
Deploy the PriceDiscServices Service Group

An Infolog will appear when deployment is complete. And you should now be able to Publish the changes back into the to price / discount journal.

Missing Default Order Settings with DIXF Products import

I was importing new Released Products into Microsoft Dynamics AX 2012 R3 using Data Import Export Framework’s ‘Products’ entity. Although the import was apparently successful, I found that fields on the Default Order Settings form were all greyed out.

Default Order Settings
All fields on the Default Order Settings form are greyed out.

I had last encountered this problem nearly two years ago in AX 2012 FP. At that time, DIXF was in Beta, and Microsoft were not accepting bug reports. Thinking about filing one now, I decided to investigate the cause. The Default Order Settings form has three datasources:

  • InventItemInventSetup
  • InventItemPurchSetup
  • InventItemSalesSetup

Looking at these tables, I could see that DIXF had created records for my imported items, but the field ‘InventDimIdDefault’ was blank. With items created using the AX client (and which did not have a default site defined) this field contained the value ‘AllBlank’. I decided, for a quick fix, to add these three fields to my import. In the AOT, I opened the table DMFProductEntity and expanded the Fields node. In another window, I browsed to InventItemInventSetup. I dragged the field ‘InventDimIdDefault’ from this table onto the Fields node of DMFProductEntity. This created a new field in DMFProductEntity called ‘InventDimIdDefault’. I renamed it ‘InventItemInventSetup_InventDimIdDefault’. I repeated this for the other two tables, and compiled DMFProductEntity. This caused three new fields to be created in the Product’s Staging table.

Adding fields to DMFProductEntity

In the Product CSV file that I was importing, I added three columns with the names ‘InventItemInventSetup_InventDimIdDefault’ etc. In each column, the values in all records were set to ‘AllBlank’. In DIXF, I opened my Processing group for the Products import and hit ‘Entities’. I then hit Generate source mapping and confirmed I wanted it to generate from scratch. This ensured the new fields in my data were mapped to the new fields I had created in the Staging table

Staging table mapping
The new fields are mapped to the staging table

Still in DIXF, I then found the ‘Product’ Target Entity and hit ‘Modify target mapping’. I did ‘Refresh diagram’, and then switched to the ‘Mapping details’ view. I hit ‘Generate mapping’, confirming that I wanted to generate the mapping from scratch. When this was complete, I scrolled down to confirm that my three fields were mapped to ‘Target fields’ with the same names.

Target mapping
The new fields are mapped to the target

I then ran the import, and found this time that I was able to use the Default Order Settings form as expected on my newly imported items. Perhaps I shan’t bother logging it with Microsoft after all!

UPDATE 13th March 2015: I notice that Microsoft have released a hotfix for Dynamics AX 2012 R3 which sounds like it resolves this problem with the Products entity in DIXF:

KB 3047371: Cannot edit default order settings after importing product data by using Data Import export framework (DIXF)

UPDATE 16th June 2015: I notice that Microsoft have released a related hotfix for Dynamics AX 2012 R3 for a problem with the ‘Item’ entity in DIXF. It seems AX will assume the InventDim record which has no inventory dimensions is ‘AllBlank’, even if it is not:

KB 3069778 : “item” Target entity import using DIXF assumes that the blank INVENTDIMID is “AllBlank” without interpreting what is in the blank INVENTDIMID record for the company that you are importing into

R3 Demand Forecasting generates a blank Forecasting spreadsheet

You may come across an annoying problem if you’re testing Demand Forecasting using the Microsoft Dynamics AX 2012 R3 Solution Demo Package V1.0. The problem occurs when you ‘Generate statistical baseline forecast’, and happens even though you receive the ‘forecast has been created’ message. The first you notice is when you open the spreadsheet, and find the pivot table contains no data.

If you connect to the Demand Forecasting SSAS cube, and process the cube manually, you will get an error that gives you a clue to the cause of the problem. The sqlanalysis user wants more permission to the Dynamics AX database. I fixed it by heading into SQL Server 2014 Management Studio, and assigning user sqlanalysis to the dbo.owner role on the MicrosoftDynamicsAX database:

sqlanalysis user
Give the sqlanalysis user more permission to the Dynamics AX database

This is possibly overkill (but it is only demo data) so as an alternative, on the Dynamics AX Tip Of The Day blog, Murray Fife has written a slightly different solution.

I assume this only applies to the Microsoft Dynamics AX 2012 R3 Solution Demo Package V1.0, and that any real environment would have correct permissions set at install time.