Limit data flow to Retail Store database in MS Dynamics AX/365

I have had multiple clients and peers ask me recommendations on how to control the size of the retail store database or minimize the bandwidth usage for retail store master synching? Most of the time this questions is raised because, either the customer is using MS SQL Expression edition which comes with the maximum database size of 10 GB or full data sync takes hours/days to complete because a lot of irrelevant data is pumped into retail store database.

The out of box way: Assortments

One way to do this is to use the retail Assortment. I know that almost all of you who are reading this article is aware of what an assortment is in MS Dynamics AX or Dynamics 365. Simply configuring an assortment per concept or area will not help. One common mistake that most of the people make is that multiple assortments will be created based on different concepts or type of store. However, all the stores (channel databases) will be linked to a single data group which basically defeats the functionality of assortment. This is usually done to reduce the difficulty of maintaining multiple data groups. When two stores with different assortments are attached to the same datagroup, a single data packet with the masters for both the stores will be created when a retail job is running. My recommendation is to maintain, a one is to one relation between assortment and channel data group. The only thing you have to note is that the higher number of channel data groups you maintain, it will take longer to process retail distribution jobs as change tracking for incremental push is maintained at the channel data group level.

Let’s think out of box

Even though assortment helps you to reduce the amount of data that is pushed to the store, it is not enough. Assortment basically filters data (price, translations, etc) that is specific to items sold in the store. However, you cannot filter further like sending only English description of a product to the store or sending only price definition in one particular currency. In this scenario, the channel schema configuration in Retail Back office module can save you.

Retail channel schema in Dynamics AX and Dynamics 365 is defined in XML format. Adding few nodes to this XML can do magics in filtering the data. If you go through the XML format carefully you can understand how the system is filtering the data only for products those are part of an assortment. Similarly, you can apply the same logic for filtering only ‘en-us’ description when the product translations are sent to the store. For now, please see a sample code below which we used to filter and send only lines with currency USD in pricedisctable. I will write a detailed post later on how to do this.

Quick Steps:

1. Export existing channel schema.

2. Make the changes.

3. Create a new schema and import the file edited in the last step.

4. Click on Generate Queries.

5. Assign it to the data group.