RSMUS.comIf 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.
- Create the AIF Inbound Port
- Create the Hierarchy Data in Excel
- Export the Hierarchy Data in Excel to an XML file
- 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”.
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
Next, select the “Service Operations” button and select the services as per the image below:
1.2 Activate the Port
Next, activate the Inbound Port by selecting “Activate” from the form ribbon.
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.
1.4 Extract the XSD File
Next, Select the “Data Policies” button.
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.
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.
Once you have enabled all of the required fields, select the “View Schema” button. Note – it may not be visible without selecting the carrot.
The following schema will be displayed:
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:
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.
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
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
After the XML source is selected, the XML Maps dialog is updated as follows: Next, select “OK”.
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.
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.
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”.
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).
Once the file is exported, it will look something like this:
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.
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.
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.
After selecting “Categories from File”, select the Inbound Port we created earlier and select the file we created from within Excel.
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:
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:
Congratulations, you now have a fully imported retail merchandising hierarchy!
