1. Home
  2. DotActiv - Database Integration
  3. What Are The Staging Table Requirements For Database Integration?

What Are The Staging Table Requirements For Database Integration?

Database integration is the process whereby your retail data is extracted from different sources – POS database, ERP or SAP – and manipulated to be in a specific structure to fit the format of our staging tables. It can then be processed through a specialized backend software and used by Category Planners for production purposes in the DotActiv application. 

A staging table is a database table containing your business data. In the context of the DotActiv software, it consists of your markets, products, sales and stock information.

The Generic Staging Table explains the fields you need to populate and the format and set up of these fields in detail. 

The following fields on the staging tables are recommended for DotActiv to implement our process successfully.

Market Feed

Store Code

The store code serves as the unique identifier within the market feed. It is crucial for this column to be populated so that stores can be identified and grouped together accurately if stores are clustered. 

Store Name

Although there is a unique identifier within the store code, the store name makes it easier and quicker for the user to identify which store they are currently working with and its location.

Retail Format

This field oversees the various store formats and will help with clustering stores. It differentiates store formats contracted to work on and provides critical information about core and non-core categories within these store formats. For example, a supermarket might hold a complete range, but convenience stores will have a much smaller product range with a different category strategy.

Region

The importance of this field might vary between customers and is used when clustering stores. Product ranges might differ within the different regions as the customer base varies.

Cluster

This field will only be available if the client has clusters. If there are no clusters, we will feed the information back once we cluster the categories and stores. If clusters exist from the customer, it is a mandatory field to base further groupings and ranges on. 

Product Feed

Product ID

The Product ID is the unique product identifier in your environment. This field is mandatory for production so that each and every product SKU can be identified and fall within a category of the Retailer and DotActiv hierarchy. 

EAN Barcode

This field is the product’s barcode as per the packaging and can also be a unique identifier, especially with image mapping.

Merchandising Group

Level 1 of your product hierarchy. 

Department

Level 2 of your product hierarchy. 

Category

Level 3 of your product hierarchy. 

Subcategory

Level 4 of your product hierarchy. 

Segment

 Level 5 of your product hierarchy.

Subsegment

Level 6 of your product hierarchy. 

Please note not all hierarchy levels are mandatory to populate. However, the detailed classification of products results in an in-depth analysis for your categories and reporting on every single level of the hierarchy structure. 

Product Description

This column helps the Category Planner identify the product they are working with quickly. If this column is always populated and updated, the planner will also spend less time classifying newly listed products.

Brand

If brand blocking is a strategy for the category, it makes it easier for the planner when this column is populated. The brand can also help with brand profiling – identifying whether a product is premium or economic.

Supplier

This column is helpful for planners and buyers when it comes to identifying supplier and retailer collaboration opportunities and whether there are any pre-existing arrangements when it comes to product placement or forward share.

Size & UOM

These columns provide an overview of the volume of the product in line with its unit of measure. These are crucial fields when the software allocates brand profiles.

Pack Size

The number of items in a shrink pack. I.e., the smallest quantity orderable from the distribution centre.

MDD

The minimum number of facings (deep) a product should have on shelf. This amount is generally set to two but can increase for smaller products to increase visual appeal for minimum stock orders.

KVI

Also known as the ‘known value item’ used to flag an item as part of core ranges such as products like Coca-Cola.

Status

This field is for the product status and used if there are multiple statuses for a product. This field would speak to the national product status, with site specific statuses added to the product feed. 

Product Height, Width, And Depth

Product dimensions in cm or inches based on the measurement system used in your region. This column is mandatory to calculate capacities and %cubic space on the planograms. 

Sales Feed

Store Code

This field is the unique identifier to the market fields and is mandatory to understand the range within each store as this will impact the clusters and assortments. This field offers a view per product, per site.

Product ID

This field is the unique product identifier in your environment. This field is mandatory for the Category Planner to identify every product SKU and to see how much the product contributes towards the category from a sales, unit movement and profit perspective. 

Sales At Sell

This field is the total sales value for the period, store and product combination, i.e., units x selling price for a period.

Sales Quantity

This field is the number of units sold for the period, store and product combination. 

Sales At Cost

This field is the total sales value at cost for the period, store and product combination, i.e., units x cost price for a period.

Ranging Indicator

This field indicates that a product is ranged for the store and period combination. 

Period (From Date And To Date)

The Category Planner will always need to know which data period they are working with to take seasonality into account or to conduct a performance analysis.

Store Stock Feed

Period (From Date And To Date)

The Category Planner will always need to know which data period they are working with to oversee stock level fluctuations. 

Store Code

This field will ensure a view of the stock per store, per product.

Product Code

This field will ensure a view of the stock per store, per product.

Store Bal At Cost

This field will be the last known value of the stock at cost price, i.e., units x cost price.

Store Bal Quantity

This field will provide an overview of stock per product, per store.

DC Stock Feed

Period (From Date And To Date)

The Category Planner will always need to know which data period they are working with to oversee stock level fluctuations. 

Store Code

This field will ensure a view of the stock per DC, per product. The Store Code for the DC should also be listed under the Servicing Warehouse field (which is available under Market Feed) in order to link which storefront receives stock from which DC.

Product Code

This will ensure a view of the stock per DC, per product.

DC Bal At Cost

This will be the last known value of the stock at cost price, i.e. units x cost price.

DC Bal Quantity

This will provide an overview of stock per product, per DC.

Updated on May 31, 2024

Was this article helpful?

Related Articles

Leave a Comment