Fact Data Key Columns

Fact data files, such as sales and inventory, share many common characteristics and standards that can be followed regardless of the specific file. The table below summarizes those key elements and their minimum requirements. You will generally always want to populate these fields where they exist on an interface file.

Table 8-9 Common Fact Data Fields

Column Header	Usage
ITEM	Unique identifier of a transaction item. Must align with the product records in the PRODUCT.csv file where ITEM_LEVEL = TRAN_LEVEL.
ORG_NUM	Unique identifier of an organizational entity. Must align with the location records in the ORGANIZATION.csv file.
DAY_DT	Transaction date or business date for the fact data entry, formatted as YYYYMMDD. Must be a valid date within the range of periods in CALENDER.csv.
RTL_TYPE_CODE	Retail types define a general category for the record that varies by interface. For Sales and Markdowns, it must be one of R/P/C, representing the regular/promo/clearance status of the transaction.
*_QTY	Fields ending with QTY represent the quantity or units of an item on the record, such as the units sold on a transaction line or the units of on-hand inventory.
*_AMT_LCL	Fields containing AMT_LCL represent the currency amount of a record in the local currency of the source system, such as sales retail amount in Canadian dollars from a Canada location, or the cost value of on-hand inventory at your U.S. warehouse.
DOC_CURR_CODE	The original document currency of the record in the source system. For example, a sale made in Canada may have a value of CAD in this field.
LOC_CURR_CODE	The local operating currency of your main office or headquarters. A company based in the United States would use USD in this field.
ETL_THREAD_VAL	If you are providing any data files ending in a .dat extension, then it might contain an ETL_THREAD_VAL column. This column must be hard-coded to be 1 on all rows without exception; it should not be null and should not be any values greater than 1. This is a legacy field that allowed multi-threading in older generations of RAP architecture.

Nearly all fact files share a common intersection of an item, location, and date as specified above. Such files are expected to come into the platform on a nightly basis and contain that day’s transactions or business activity.

Most fact data also supports having currency amounts in their source currency, which is then automatically converted to your primary operating currency during the load process. There are several currency code and exchange rate columns on such interfaces, which should be populated if you need this functionality. The most important ones are shown in the list above, and other optional column for global currencies can be found in the Interfaces Guide. When you provide these fields, they must all be provided on every row of data, you cannot leave out any of the values or it will not load properly.

Here are sample records for commonly used historical load files having a small set of fields populated. These fields are sufficient to see results in RI reporting and move the data to AI Foundation or MFP but may not satisfy all the functional requirements of those applications. Review the Interfaces Guide for complete details on required/optional columns on these interfaces.

SALES.csv:

ITEM,ORG_NUM,DAY_DT,MIN_NUM,RTL_TYPE_CODE,SLS_TRX_ID,PROMO_ID,PROMO_COMP_ID,CASHIER_ID,REGISTER_ID,SALES_PERSON_ID,CUSTOMER_NUM,SLS_QTY,SLS_AMT_LCL,SLS_PROFIT_AMT_LCL,RET_QTY,RET_AMT_LCL,RET_PROFIT_AMT_LCL,TRAN_TYPE,LOC_CURR_CODE,DOC_CURR_CODE
1235842,1029,20210228,0,R,202102281029,-1,-1,96,19,65,-1,173,1730,605.5,0,0,0,SALE,USD,USD
1235842,1029,20210307,0,R,202103071029,-1,-1,12,19,55,-1,167,1670,584.5,0,0,0,SALE,USD,USD
1235842,1029,20210314,0,R,202103141029,-1,-1,30,18,20,-1,181,1810,633.5,0,0,0,SALE,USD,USD

INVENTORY.csv:

ITEM,ORG_NUM,DAY_DT,CLEARANCE_FLG,INV_SOH_QTY,INV_SOH_COST_AMT_LCL,INV_SOH_RTL_AMT_LCL,INV_UNIT_RTL_AMT_LCL,INV_AVG_COST_AMT_LCL,INV_UNIT_COST_AMT_LCL,PURCH_TYPE_CODE,DOC_CURR_CODE,LOC_CURR_CODE
72939751,1001,20200208,N,0,0,0,104.63,0,48.52,0,USD,USD
73137693,1001,20200208,N,0,0,0,104.63,0,48.52,0,USD,USD
75539075,1001,20200208,N,0,0,0,101.73,0,47.44,0,USD,USD

PRICE.csv:

ITEM,ORG_NUM,DAY_DT,PRICE_CHANGE_TRAN_TYPE,SELLING_UOM,STANDARD_UNIT_RTL_AMT_LCL,SELLING_UNIT_RTL_AMT_LCL,BASE_COST_AMT_LCL,LOC_CURR_CODE,DOC_CURR_CODE
89833651,1004,20200208,0,EA,93.11,93.11,53.56,USD,USD
90710567,1004,20200208,0,EA,90.41,90.41,50.74,USD,USD
90846443,1004,20200208,0,EA,79.87,79.87,44.57,USD,USD