Loading a Merchandising Hierarchy with No Programming!

By - September 12, 2013

If you are like me, you probably are getting tired of creating hierarchy loaders to setup Retail systems.  The good news is that Microsoft has decided to give us a tool to do this with no programming.  The bad news is that it requires navigating some arcane and undocumented waters.  I have gone through this process and (hopefully) can shed some light on how to get through this.  I think that after you get through this process one time, you will see that it is not too difficult and easy to replicate.  Good luck!

This series of steps is for Dynamics AX for Retail version 2012 R2 CU6.  There are 4 steps in this process.

  1. Create the AIF Inbound Port
  2. Create the Hierarchy Data in Excel
  3. Export the Hierarchy Data in Excel to an XML file
  4. Load the XML file into AX

Create the AIF Inbound Port

We are going to:

1.1   Create an Inbound Port

1.2   Activate the Port

1.3   Deactivate the Port

1.4   Extract the XSD File

1.5   Activate the Port again

The first step to load the merchandising hierarchy is to setup the AIF inbound port.  Do not be intimidated, this is easy to do!  The setup for the inbound port can be found at System Administration >> Setup >> Service and Application Integration Framework >> Inbound Ports

The Inbound Port you are looking for is most likely not in the list so you will need to set this up.  Don’t worry, this is easy!  Within the AOT under services, you will find a service entitled “EcoResCategoryImportService”.  This is the service we will use to setup the inbound port.  I like to name the Inbound port, with the same name as the service so I know what it is for.  I put an “F” at the end of the name to indicate a “File Loader”.

Loading a merchandising hierarchy with no programming-pic1

 

1.1   Create an Inbound Port

Create a new port as follows:

I am assuming an xml file upload from a folder.  The service has the ability to update if the hierarchy changes at a later time but I am assuming you will use this to create the hierarchy initially.

Port Name: EcoResCategoryImportServiceF

Description: EcoResCategoryImportService_File

Adapter: File system Adapter

URI: C:Upload

 

Service Contract Customizations…

Expose Service Operations: Yes

Customize Documents: Yes

 

Loading a merchandising hierarchy with no programming-pic2

Next, select the “Service Operations” button and select the services as per the image below:

Loading a merchandising hierarchy with no programming-pic3

 

1.2   Activate the Port

Next, activate the Inbound Port by selecting “Activate” from the form ribbon.

Loading a merchandising hierarchy with no programming-pic4

 

1.3   Deactivate the Port

Next, deactivate the Inbound Port by selecting “Deactivate” from the form ribbon.  Note: this activation, deactivation sequence is required to populated the XSD schema we need to load the data.  It will make more sense later.

Loading a merchandising hierarchy with no programming-pic5

 

1.4   Extract the XSD File

Next, Select the “Data Policies” button.

Loading a merchandising hierarchy with no programming-pic6

The data policies button will now show an XSD schema definition for the services operations selected.  If you had selected the Data Policies button prior to activating and deactivating the inbound port, no schema would be present.

Loading a merchandising hierarchy with no programming-pic7

The schema that ships with R2 does not have all of the fields enabled that you will require to successfully load a hierarchy so you will need to activate a few more as per the exhibit below.  You should have six fields enabled: Code, Description, FriendlyName, Locale, Name, Parent.

Loading a merchandising hierarchy with no programming-pic8

Once you have enabled all of the required fields, select the “View Schema” button.  Note – it may not be visible without selecting the carrot.Loading a merchandising hierarchy with no programming-pic9

The following schema will be displayed:

Loading a merchandising hierarchy with no programming-pic10

 

Next, select Save As and save the schema to the same folder you created for your inbound port.  You will use the schema later to create the XML from an Excel worksheet.

Note: If you choose to not use the system generated XSD and want to create one yourself, make sure the names of the elements are in alphabetical order.  AIF will expect this and will error if the fields are not in alpha order.

 

1.5   Activate the Port Again

After you save the schema, close the schema and data polices windows.  Next Activate the Inbound port again.  After activating the port, you should receive an infolog as follows:

Loading a merchandising hierarchy with no programming-pic11

Ok, at this point, you have setup the AIF inbound port and retrieved the XSD schema that defines how the port is expecting the XML to be formatted.  Next you will need to collect the hierarchy data and format it into an Excel worksheet.  Once it is in Excel, we can then export it as XML for the port.

 

2. Create the Hierarchy Data in Excel

You will need to create an Excel file to format the data to load into the merchandising hieararchy.  You will need seven columns: Parent, Code, Description, Name, Friendly Name, Class and Locale.  This step is optional and only necessary if you are creating data using Excel.  I found this to be the easiest way to do and most retailers will be able to provide this information to you in Excel.

2.1 Create the XML File

This is the Excel format for the data needed to load a merchandising hierarchy.  This format can be used to load any type of merchandising hierarchy.  There are three different types of fields: codes, descriptions and constants.

Note: The XSD does not enforce string lengths so you will need to do this in Excel.

CODES:

Parent (20 char) and Code (20 char) are both nodes on the hierarchy.   In the example below, “AAA” is the code for Duffle Bag while “AAX” is the code for Accessories.   This is interpreted as the Duffle Bag belongs to Accessories and Accessories is the immediate parent of the Duffle bag.  “AAX” will have a record in the list and will also have an immediate parent.

Note: Make sure that each child only has one parent or the data load will fail.

DESCRIPTIONS:

Name (254 char), Friendly Name (254 char), Description (1,000 char) are all descriptions to fill in what you want.  The Name field is used in the forms when viewing the tree in AX and is used in the BI as well.

CONSTANTS

Class is a fixed value – just set it to “entity” and copy the value to all of the rows

Locale is a fixed value – just set it to “en-us” and copy the value to all of the rows.  If you don’t specify locale, none of the descriptions will show up because it is using a category translation table to figure out which descriptions to display depending on the base language.

Action – I have omitted this field but it can be used to tell the AIF to either Create, Update or Delete.  By the way, if you want to load more than one language, you may want to add the “Action” column and change the description fields and the locale field in subsequent passes.

 

3. Export the Hierarchy Data in Excel to an XML file

This step (and step #2) is only necessary if you want to use Excel to create your XML file.

We are going to:

3.1 Create an XML Map

3.2 Export the Excel file to XML

 

3.1 Create an XML Map

The first step is to get your Developer Tab visible on the Excel file. The next step is to select the “Source” button out of the ribbon.

Once the “Source” button is selected a panel entitled “XML Source” will open in the spreadsheet to the right of your data table.

Loading a merchandising hierarchy with no programming-pic13-1

 

Next, select the XML Maps button from the XML Source panel.  The “XML” Maps form will open.  Next select the add button and select the XSD document that you created earlier.

SIDE NOTE: IMPORTANT!!!!

Note: All of the XSDs that are generated will have an internal reference to a “SharedTypes.xsd”.  This XSD needs to be placed in the same directory as the XSD generated by the inbound port.  You can find this XSD on the AOS server install under:

C:Program FilesMicrosoft Dynamics AX60Server

MicrosoftDynamicsAXBinXppILAppShareServiceGenerationRetailServices

SharedTypes.xsd

Loading a merchandising hierarchy with no programming-pic14

After selecting the XML Maps Button, the XML Maps form will open.  Select the “Add” button in the XML Maps form.  A windows file picker dialog opens.  Select the xsd file that was saved from the inbound port data services screen.

Note: the SharedTypes.xsd is in the same folder

Loading a merchandising hierarchy with no programming-pic15

After the XML source is selected, the XML Maps dialog is updated as follows:  Next, select “OK”.

Loading a merchandising hierarchy with no programming-pic16

Once the file has been selected, the Excel document will be updated and the XML Source panel within the Excel worksheet will be updated as below.  The elements that have a red star next to them are required.  However, I recommend that you populate all of the elements with the file with the exception of the “action” element.

Loading a merchandising hierarchy with no programming-pic17

 

Next click on the “ns1: Code” element in the XML Source panel and drag the element over to the column entitled “Code” in your data table.  Repeat this step for each of the fields with the exception of the “Action” field which we are skipping for this exercise.

ns1: Code = Code

ns1: Description = Description

ns1: FriendlyName = FriendlyName

ns1: Locale = Locale

ns1: Parent = Parent

ns1: Name = Name

ns1: Class = Class

 

As you drag the elements onto the data columns, the data will be reformatted as a table and will have a highlighted header row.

Loading a merchandising hierarchy with no programming-pic18

You have now completed the XML map.  The next step is to test the map to see if it will result in an exportable XML file.  Within the XML Source panel, at the bottom, select “Verify Map for Export”.

 

Loading a merchandising hierarchy with no programming-pic19-1

If your map is deemed exportable by Excel, you will receive the following dialog:

 

If you do not receive this dialog, there is a problem with the data file.  There are some limitations in the complexity of the file that Excel can create.  If you stick with the format provided, you should not have an issue.  I did find out that if you don’t map everything as a column of data, it will not be exportable.  This is why I have the LOCALE and CLASS columns in the data table the way that it is.

3.2 Export the Excel file to XML

Now It is time to Export your XML File.  Select the “Export” function from the Developer tab on the XML ribbon section as below.  Select a destination for your XML file (I exported it into the same directory I want to upload from).

Loading a merchandising hierarchy with no programming-pic21

Once the file is exported, it will look something like this:

Loading a merchandising hierarchy with no programming-pic22

 

4. Load the XML file into AX

We are going to:

4.1 Create the Hierarchy Name and Root Category

4.2 Import the Hierarchy

 

4.1 Create the Hierarchy Name and Root Category

Ok, so if you survived this far, I am very impressed with you!  You are almost done!  Back inside of AX we go to the Product Information management and create a new hierarchy.  You don’t need to worry about the hierarchy purpose at this point.

Loading a merchandising hierarchy with no programming-pic23

Note: You will need to enter the root category prior to loading your XML file.  It should be the same code as in your XML file.

Loading a merchandising hierarchy with no programming-pic24

 

4.2 Import the Hierarchy

After you have created the Hierarchy and entered the root node (“ALL” in this example), you are ready to load the XML file.  Select the “Categories from File” button on the ribbon.

Loading a merchandising hierarchy with no programming-pic25

After selecting “Categories from File”, select the Inbound Port we created earlier and select the file we created from within Excel.

Loading a merchandising hierarchy with no programming-pic26

After setting up the Import Categories form, select the Import button.  The application will prompt you with a request indicating that it may take a while to process.  If you have a while, then select yes.

The system will work for a while and then you will be presented with the following infolog:

Loading a merchandising hierarchy with no programming-pic27-1

Note the “Total no longer valid records: 1”.  This is the root category “ALL”.  It recognized that the root category already was in place.  Unfortunately, I was unable to get the hierarchy to load without entering the root category manually.

Your hierarchy should look something like this:

Loading a merchandising hierarchy with no programming-pic28

 

Congratulations, you now have a fully imported retail merchandising hierarchy!

Receive Posts by Email

Subscribe and receive notifications of new posts by email.