Inventory module
This unified layer is applied to the pipeline in the data model containing data from inventory movement tables. Map inventory quantities and valuation here.
Inventory transactions
Column table
Column Name | Data Type | Description | Key |
Company | Text (50) | The unique identifier code associated with the company. | Additional key |
Direction | Text (50) | The direction of the inventory transaction. Is it incoming or outgoing? | |
Inventory qty | Numeric | The quantity of inventory that is being moved in or out of warehouses. | |
Item | Text (50) | Unique identifier code of the item or product that is moving in or out of inventory. | |
Item group | Text (50) | Unique identifier code of the group or category that the item belongs to. | |
Item key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Posting date | Date | The date that this transaction is posted to. | |
Price | Numeric | Unit price of the inventory transaction. | |
Salesperson | Text (50) | Unique identifier code of the person responsible for this transaction. | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the first segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the first segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the first segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Storage location | Text (50) | The unique identifier code of the warehouse, site or other functions that the inventory is going to or coming from. | |
Storage location key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Transaction number | Text (50) | Unique identifier code for the inventory transaction. | Primary key |
Transaction type | Text (50) | Unique identifier code for the transaction type, for example the code representing 'Goods issued', or 'Goods receipt' etc. | |
Unit | Text (50) | The unique unit of measure code pertaining to the inventory transaction. | |
Unit group | Text (50) | Unique code for the unit of measure group that the unit of measure belongs to. | |
Unit key | Text (50) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Valuation amount | Numeric | The inventory valuation amount of the transaction. |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Item Groups | Item Groups | Item group -> Item group Company -> Company |
Items | Items | Item -> Item Company -> Company |
Units | Units | Unit group -> Units Group Unit -> Units Company -> Company |
Storage locations | Storage locations | Storage location -> Storage location Company -> Company |
Note
It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.
Stock in warehouse
This unified layer is applied to the pipeline which has information regarding on-hand quantities for inventory warehouses. Columns to be mapped here include the items or products, their quantities, units and groups.
Columns table
Column Name | Data Type | Description | Key |
Company | Text (50) | The unique identifier code associated with the company. | Additional key |
Item | Text (50) | Unique identifier code of the item or product that is in the warehouse. | Primary key |
Item cost | Numeric | Unit cost of the item that is in the warehouse. | |
Item group | Text (50) | Unique identifier code of the group or category that the item belongs to. | |
Item key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Quantity committed | Numeric | The quantity of stock committed in specified units. | |
Quantity in stock | Numeric | The quantity of stock on hand in the warehouse in specified units. | |
Quantity ordered | Numeric | The quantity of stock ordered in specified units. | |
Storage location | Text (50) | The unique identifier code for the warehouse, site or storage location in which the on-hand stock quantities reside. | |
Storage location key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Unit | Text (50) ) | The unique code for the unit specified for quantities in the warehouse. | |
Unit group | Text (50) | The unique code given to the group that the unit belongs to. | |
Unit key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Item Groups | Item Groups | Item group -> Item group Company -> Company |
Items | Items | Item -> Item Company -> Company |
Units | Units | Unit group -> Units Group Unit -> Units Company -> Company |
Storage locations | Storage locations | Storage location -> Storage location Company -> Company |
Units
The Units unified layer is applied to the pipeline containing information for units used in the ERP or accounting software transactions, eg Sales unit, purchase unit, etc. The Units layer will map the unit and its grouping.
Columns table
Column Name | Data Type | Description | Key |
Company | Text (50) | The unique identifier code associated with the company. | Additional key |
Unit group name | Text (100) | The name of the unit group. | |
Unit name | Text (100) | The name of the unit of measure. | |
Units | Text (50) | The unique identifier code for the unit. | Primary key |
Units Group | Text (50) | The unique code given to the group that the unit belongs to. | Additional key |
Units key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |