Raw Layer Mapping

The following tables need to be mapped into the RAW layer for Financial Planning to be set up. With the release a standard data mapping into the raw layer is provided from the Navision erp tables. For other data sources either the customer should provide data directly into the raw layer tables er you use the raw views for data mapping.

Try not to change anything in the other layers as the mapping into PREP and FP are part of the upgrade paths.

raw.SALE_VALUE_HISTORY

History of Sales, Cost of Goods Sold and Discounts in values and units. Primary key: PRODUCT_ITEM_NO,LOCATION_NO,DATE

  • PRODUCT_ITEM_NO (nvarchar) - required: The product number
  • LOCATION_NO (nvarchar) - required: the location number
  • DATE (date) - required: The date of sale
  • SALE_UNITS (decimal(18,4)) - not required: Sale in units
  • TRANSFER_UNITS(decimal(18,4))- not required:
  • SALE_VALUE (decimal(18,4)) - required: Sale in value
  • COST_VALUE (decimal(18,4)) - required: Cost of sales in value
  • PLANNED_DISCOUNT_VALUE (decimal(18,4)) - not required: Planned discount in value
  • OTHER_DISCOUNT_VALUE (decimal(18,4)) - not required: Other discount in value

raw.ACCOUNT

A dimension table for all account key hierarchies that the business uses. Used for Expense Planning in Financial Planning Primary key: LEVEL,NO

  • LEVEL (int) - required: The level in the account key hierarchy. Level 1 account key is parent to level 2 account keys. Level 1 is top-level
  • NO (nvarchar) - required: the account key number
  • NAME (nvarchar) - required: the account key name
  • PARENT_NO (nvarchar) - not required: The NO of the parent account key. Should be NULL if account key is on top level

raw.DEPARTMENT_INFO

A dimension table for all departments that the business uses for Financial Planning. Primary key: CODE

  • CODE (nvarchar) - required: The level in the account key hierarchy. Level 1 account key is parent to level 2 account keys. Level 1 is top-level
  • NAME (nvarchar) - required: the department name
  • DEPARTMENT_GROUP_NO_LVL_1 (nvarchar) - not required: The CODE of the highest level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_2 (nvarchar) - not required: The CODE of the 2nd level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_3 (nvarchar) - not required: The CODE of the 3rd level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_4 (nvarchar) - not required: The CODE of the 4th level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_5 (nvarchar) - not required: The CODE of the 5th level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_6 (nvarchar) - not required: The CODE of the 6th level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_7 (nvarchar) - not required: The CODE of the 7th level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_8 (nvarchar) - not required: The CODE of the 8th level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_9 (nvarchar) - not required: The CODE of the 9th level in the department group hierarchy (see raw.DEPARTMENT_GROUP)
  • DEPARTMENT_GROUP_NO_LVL_10 (nvarchar) - not required: The CODE of the 10th level in the department group hierarchy (see raw.DEPARTMENT_GROUP)

raw.DEPARTMENT_GROUP

A dimension table for all department group hierarchies that the business uses for Financial Planning. Used for Expense Planning in Financial Planning Primary key: NO,LEVEL

  • NO (int) - required: The NO of the department group
  • NAME (nvarchar) - required: the department name
  • LEVEL (int) - required: The level in the department group hierarchy. Level 1 department is parent to level 2 departments. Level 1 is top-level
  • PARENT_NO (nvarchar) - not required: The NO of the parent department. Should be NULL if department is on top level

raw.ACCOUNT_HISTORY

Historical financial values down to account keys and financial departments. Used as baseline plan and for comparison in Expense Planning in Financial Planning Primary key: ACCOUNT_NO,DEPARTMENT_NO,DATE

  • ACCOUNT_NO (nvarchar) - required: The account key number
  • DEPARTMENT_NO (nvarchar) - required: the financial department number
  • DATE (date) - required: The date of sale
  • VALUE (decimal(18,4)) - not required: Value amount in base currency.

raw.DEPARTMENT_MAPPING

A table to map various sources of data into a department, similar to core.location_mapping_setup F.x. for customers that have more than one location in a department, they would put those locations as SOURCE for the department. Primary key: NO,LEVEL

  • SOURCE (nvarchar) - required: The NO of the source element (e.g. LOCATION_NO)
  • department_code (nvarchar) - required: the department number