Mass loading items into AX2012

When you need to import large numbers of item records into AX2012 you will find the time it takes to load them fairly slow. In my example I have 200,000 records to load. First I need to create the Product masters, then I need to release the product to the different companies. I was finding the creation of the product masters was taking about 3 hours to complete with the release to each of the companies around 3 hours each, and I had two companies to release.

Since in a testing phase of data migration I expect to need to run the migration numerous times to fine tune and test the robustness of the solution, but with a nine hour plus time frame this becomes problematic.

Since I always look to optimize performance I decided to not use the classes to import the data, but rather insert them directly into the table themselves, from within AX. The results were substantial. Turing the nine hour plus process, to create the products and release to two companies, down to a less than 10 minutes to complete the entire process.

For my project there was three steps that I had to complete:

Step 1: Load the data into a dummy AX table

Step 2: Create the product

Step 3: Release the product to the desired company

Each of the above steps will be detailed more below:

Step 1 – Load the data

Mass inserting data into AX needs to be driven from AX, and as a consequence the data needs to reside in AX to achieve this. So a new table was created to store the data from the external system. In my case it came from another SQL database, but it could have come from a CSV or another other source, as long as AX can access the data then the data can be loaded into AX.

Create a new table to store the item information. The minimum number of fields you require are:

ItemId, product type, search name, Name, LanguageId, Description, StorageDimensionGroup, TrackingDimensionGroup, ProductRecId, ItemType, ModuleType_Invent, PriceUnit_Invent, UnitId_Invent,ModuleType_Purch, PriceUnit_Purch, UnitId_Purch,ModuleType_Sales, PriceUnit_Sales, UnitId_Sales,InventDimId_Blank, dataarea, item_group, modelgroup,

Load the data is loaded into the table populating the core fields: ItemId, Product type, Search Name, Name, Description, ItemType, UnitId_Invent, UnitId_Purch, UnitId_Sales, PriceUnit_Invent,PriceUnit_Purch,PriceUnit_Sales, item_group and modelgroup.

You can then mass update the table to load the languageId, if only using one language for item descriptions:

Update_recordset <Custom Item Table>
setting LanguageId = ‘en-gb’;

You can also mass update the table to load the storage dimension group for the items:

Refrecid   storageGroup;
storageGroup = EcoResStorageDimensionGroup::findByDimensionGroupName(‘
Update_recordset <Custom Item Table>
setting StorageDimensionGroup = storageGroup
where <filter criteria>;

You can also mass update the table to load the tracking dimension group for the items:

Refrecid    trackingGroup;
TrackingGroup = EcoResTrackingDimensionGroup::findByDimensionGroupName(‘
Update_recordset <Custom Item Table>
setting TrackingDimensionGroup = TrackingGroup
where <filter criteria>;

InventDimId    InventDimId;
InventDimId = Inventdim::findorcreateblank().inventdimid;
Update_recordset <Custom Item Table>
setting InventDimId_Blank= InventDimId, DataArea = “”, ModuleType_Invent = ModuleInventPurchSales::Invent, ModuleType_Sales = ModuleInventPurchSales::Sales, ModuleType_Purch = ModuleInventPurchSales::Purch
where <filter criteria>;

Step 2 – Create the products

You cannot directly insert records into the product master table EcoResProduct as this is an abstract table. You need to insert instead into the table EcoResDistinctProduct.

insert_recordset EcoResDistinctProduct (DisplayProductNumber, ProductType, SearchName)
Select ItemId, ProductType, SearchName from ;

//Load product recids into import table
<Custom Item Table>.skipDatabaseLog(true);
update_recordSet <Custom Item Table>
setting Productrecid = EcoResProduct.RecId
join recid from EcoResProduct
where EcoResProduct.DisplayProductNumber == .ItemId;

insert_recordset EcoResProductTranslation (Product, Name, LanguageId, Description)
Select ProductRecId, Description, LanguageId, Description_Long from ;

Insert_recordset EcoResStorageDimensionGroupProduct (Product, StorageDimensionGroup)
Select ProductRecId, StorageGroup_RecId from ;

Insert_recordset EcoResTrackingDimensionGroupProduct (Product, TrackingDimensionGroup)
Select ProductRecId, TrackingGroup_RecId from ;

You can now view all the records in the Product Master List.

3. Release Products

Next you need to release the required items to the appropriate company.

insert_recordset InventTable (ItemId, Product, NameAlias, ItemType)
Select ItemId, ProductrecId, SearchName, ItemType from
where <filter criteria>;

insert_recordset inventTableModule (ItemId, ModuleType, PriceUnit, UnitId)
Select ItemId, ModuleType_Invent, PriceUnit_Invent, UnitId_Invent from
where <filter criteria>;

insert_recordset inventTableModule (ItemId, ModuleType, PriceUnit, UnitId)
Select ItemId, ModuleType_Sales, PriceUnit_Sales, UnitId_Sales from
where <filter criteria>;

insert_recordset inventTableModule (ItemId, ModuleType, PriceUnit, UnitId)
Select ItemId, ModuleType_Purch, PriceUnit_purch, UnitId_Purch from
where <filter criteria>;

insert_recordset inventItemSetupSupplyType (ItemId, ItemDataAreaId)
Select ItemId, DataArea from
where <filter criteria>;

insert_recordset ecoResStorageDimensionGroupItem (ItemDataAreaId, ItemId, StorageDimensionGroup)
Select DataArea, ItemId, StorageGroup_RecId from
where <filter criteria>;

insert_recordset ecoResTrackingDimensionGroupItem (ItemDataAreaId, ItemId, TrackingDimensionGroup)
Select DataArea, ItemId, TrackingGroup_RecId from
where <filter criteria>;

insert_recordset InventModelGroupItem (ModelGroupDataAreaId, ItemId, ModelGroupId, ItemDataAreaId)
Select DataArea, ItemId, ModelGroup, DataArea from
where <filter criteria>;

insert_recordset InventItemGroupItem (ItemGroupDataAreaId, ItemId, ItemGroupId, ItemDataAreaId)
Select DataArea, ItemId, Item_Group, DataArea from
where <filter criteria>;

insert_recordset inventItemPurchSetup (itemId, InventDimId, InventDimIdDefault)
Select ItemId, InventDimId_Blank,InventDimId_Blank from
where <filter criteria>;

insert_recordset inventItemSalesSetup (itemId, InventDimId, InventDimIdDefault)
Select ItemId, InventDimId_Blank,InventDimId_Blank from
where <filter criteria>;

insert_recordset inventItemInventSetup (itemId, InventDimId, InventDimIdDefault)
Select ItemId, InventDimId_Blank,InventDimId_Bank from
where <filter criteria>;

insert_recordset inventItemLocation (itemId, InventDimId)
Select ItemId, InventDimId_Blank from
where<filter criteria>;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s