The logical data model of the Oracle Retail Data Model defines the business entities and their relationships in order provide a clear understanding of the business and data requirements for the data warehouse.
The logical data model includes the following entities:
See also:
For a discussion of the data model of the Oracle Retail Data Model OLAP component, see "OLAP Data Model in Oracle Retail Data Model".Reference Entities define the entities within, and associated with, the retail organization for which data would be recorded and analyzed. Reference entities record the structure of the retail organization and all people, products and organizations associated with it.
Descriptions of the Reference entities are provided in the following topics:
A certificate is issued by the organization business unit (such as a store) and has a monetary value toward purchase for goods or services. Therefore a certificate represents a current liability that is carried until the certificate is redeemed, expires or is otherwise no longer valid. Certificate could be a gift certificate, voucher, or store credit and it records how and by whom the certificate is issued and how the unspent balance should be handled. Typically a certificate is used as a tender type. For loss prevention purposes, it helps in identifying cashiers who have an abnormal number of certificate issued or redeemed, or a ratio of sales to returns for a particular certificate type. The number of outstanding certificates is also tracked to allow trending and certificate age reporting, escheat reporting, and other regulatory requirements
Table 2-1 describes the Certificate entities.
Table 2-1 Certificate Entity Descriptions
Entity Name | Description |
---|---|
CERTIFICATE |
A certificate with a face monetary value issued by a store for subsequent exchange for merchandise. |
CERTIFICATE TYPE |
Type of certificate; for example, gift. |
DISPOSITION TYPE |
Defines how the unspent balance for a gift certificate is to be handled. Options include paying the balance back to the customer as cash, issuing another gift certificate for the remaining balance, and others. |
ISSUE TYPE |
A code to denote how the RETAIL STORE issues Certificates, for example, embossed or printed at the point of sale. |
ORGANIZATION BUSINESS UNIT |
A business unit of the organization that sells, stores, or distributes merchandises and services through either a physical location (store), catalog, web page or other channel, distribution center, or warehouse. |
Competitor is a retailer with a product range and customer base similar to those for the organization business unit and its channels. The competitor entity holds information about each competitor store and associates it with a location in the organization. Competitor pricing details can then be associated with a specific competitor location and mapped to an item in the product hierarchy. This structure provides the means to compare competitor prices for similar or identical items, at a direct competitor location. With this type of timely information, promotion, and price change strategy can be implemented in time to prevent costly customer defections.
This entity associates Competitor Retail Item to a specific competitor location and maps it to an item in the product hierarchy. It provides the means to compare competitor prices for identical or similar items, at a direct competitor location and ranks the relative importance of a competitor location to a business unit. A retailer may identify a competitor location as the target for a given business unit and captures the competitor offer type (whether the item was on regular or promotional pricing at the time of the competitive shop) and multi-unit incentive (as a type of price; for example, 2 for 1.00, or 3 for 1.45). It also captures which competitor is driving the competitive price. A retailer may want to filter on the distance to pull only competitors within a certain radius of their own store. For example, a retailer can see the past month's competitor pricing history, compared to their own prices, only for competitor locations with a distance of 10 (distance) miles (distance UOM) or less.
Table 2-2 describes the Competitor Retail Item entities.
Table 2-2 Competitor Retail Item Entity Descriptions
Entity Name | Description |
---|---|
COMPETITOR |
A retailer with a product range and customer base similar to those for the store. |
COMPETITOR LOCATION |
Physical location of the competitor. |
COMPETITOR LOCATION ASSIGNMENT |
The associative relationship between competitor locations and business unit locations. For example, a competitor grocery store may contain a bank, a florist, and a pharmacy. Competitors can be either primary or secondary. |
COMPETITOR RETAIL ITEM |
A Retail Item that a competitor stocks that has, to the consumer, no apparent difference in form, fit, or function, but may have a different price. |
COMPETITOR RETAIL TYPE |
Lookup Table for retail types of retail items. Types include regular, promotion, and clearance. |
ORGANIZATION BUSINESS UNIT |
A business unit of the organization that sells, stores, or distributes merchandises and services through either a physical location (store), catalog, web page or other channel, distribution center, or warehouse. |
An individual or organization that purchases, may purchase, or did purchase goods or services from a retail organization, including both business-to-business (B2B), business-to-consumer (B2C) customers and prospects. Customer affiliations can include financial, non-financial groupings and their relationships to one another.
Syndicated data, for both B2B and B2C, from Dunn & Bradstreet, AcXiom, and Harte Hanks provides rich demographic, psychographics, and behavioral data attributes, such as customer occasion and preferences, for analysis and data mining. Flexible and generic demographic groups and related entities enable the retailer to capture credit history, education, employment, equipment, and hobbies among others. The model includes full support for customer group affinities for items, (which items appeal to which customer groups).
All types of customer accounts are supported including charge payments, rentals, and layaways. Various customer status records, including complete status history and reasons for status change, are maintained. Privacy protection, related to customer information, uses Oracle Encryption technology to safeguard customer data.
Figure 2–1 represents the Customer Entity Relationships.
Table 2-3 describes Customer entities.
Table 2-3 Customer Entity Descriptions
Entity Name | Description |
---|---|
ACCOUNT TYPE |
Lookup for account type. The account type could include Installment Payment Account, Charge Account, Trade Account, Layaway Account, and Rental Account. |
ADDRESS LOCATION |
Address of an individual location. |
CUSTOMER |
An individual or organization that purchases, may purchase, or did purchase goods and or services from a retail store. |
CUSTOMER ACCOUNT |
A charge account or other accounting relationship a customer has with the store or organization. An account exists to allow the store to record a series of transactions with the same customer and keep an ongoing record of monies owed by the customer and monies due to the customer. |
CUSTOMER ADDRESS |
Assigns the address location to a Profile or Customer. |
CUSTOMER AFFILIATION |
Associates a customer with a customer group. |
CUSTOMER GROUP |
A group of customers based on specific demographic and marketing attributes and properties. Examples include over 65 year old customers, students, unions, and other associations. |
CUSTOMER GROUP ITEM |
An association of Item and Customer Group, the data for this may come from external source. |
CUSTOMER CLUSTER |
Customer clusters and their descriptions. The data may come from an external source. |
CUSTOMER occasion |
Events celebrated or observed by a customer. For example, Mother's Day, Thanksgiving, and others. |
CUSTOMER occasion TYPE |
Lookup for Customer Occasion types. |
CUSTOMER RELATIONSHIP |
Association between customers. Example associating the Husband-Wife relationship. |
CUSTOMER RELATIONSHIP INFO |
Information regarding the customer or prospect that is restricted to comply with privacy and other laws. This table is encrypted. |
CUSTOMER PREFERENCE |
Merchandise preferences of a Key Customer, for classes of items or other general categories. |
CUSTOMER STATUS |
Lookup for customer or prospect status. |
CUSTOMER QUICKFACTS |
Collection of Customer related measures. |
DEMOGRAPHY ATTRIBUTE |
A sub-level group or category further qualifying a set of data (Profile Group) collected about a customer to assist in marketing efforts. Examples: NC - Number of Children, EDL - Education Level, and others. |
DEMOGRAPHY GROUP |
The domain of classifications used to group profile information about a Party. Examples include the following:
|
DERIVED VALUE |
Derived value of the customer as defined by the user. |
HOUSEHOLD |
Household statistics and demographic information. |
INDIVIDUAL DEMOGRAPHY VALUE |
Detailed demographic information describing customers. For example age has Demography group as AGE, Attribute contains various bands and value as 15 years, which would be stored in this entity. |
MEMBERSHIP ACCOUNT |
Membership Account details such as frequent shopper membership points. |
MEMBERSHIP TYPE |
Lookup value for membership type. |
ORGANIZATION DEMOGRAPHY VALUE |
User defined statistical or demographic information about an Organization. |
ORGANIZATION MARKET DATA |
Publicly available and statistical information regarding the customer organizations, such as DUNS number and number of employees. |
PREFERENCE TYPE |
Type of preference relevant to consumer or customers. |
PROFILE INDIVIDUAL |
Attributes of an individual customer; that is, a customer who is an individual and not and organization. |
PROFILE ORGANIZATION |
Attributes for a customer organization; that is, a customer who is not an individual. |
PROSPECT |
An individual, collection of individuals, company, or public institution that does not currently purchase merchandise or services from the retailer, but who may in the future. A prospect has no recorded relationship with the retailer. |
PROSPECT INDIVIDUAL |
Attributes of an individual prospect, one who is not an organization. |
PROSPECT ORGANIZATION |
Attributes of a prospect organization. |
PROSPECT QUICK FACTS |
Collection of Prospect related information. |
PROSPECT RESTRICTED INFO |
Confidential information regarding the prospect, in other words, date of birth or national identifier of a customer. This table is encrypted. |
STATUS |
Lookup for status reason. |
STATUS REASON |
A reason why a particular Party Status Type may be assigned to a customer |
STATUS TYPE |
Lookup Table for status type: A - Active I - Inactive P - Prospective U - Unmarketable (for example, deceased) |
VALUE MEASURE |
User defined measures that help define the derived value of customer or prospect. |
VALUE TYPE |
User defined value types that help define the derived value of a customer or prospect. |
Identifies the Cluster that includes that Customer, based on the Customer's buying behavior.
Table 2-4 describes Customer Cluster Item Entities.
Table 2-4 Customer Cluster Item Entity Descriptions
Entity Name | Description |
---|---|
ACCOUNT TYPE |
Lookup for types of account, in other words, Installment Payment Account, Charge Account, Trade Account, Layaway Account, and Rental Account. |
CUSTOMER |
An individual or organization that purchases, may purchase, or did purchase goods and or services from a retail store. |
CUSTOMER CLUSTER |
Customer clusters and their descriptions. The data may come from an external source. |
CUSTOMER CLUSTER ITEM ASSIGNMENT |
Cross references customer cluster with item. |
ITEM |
A level in a product hierarchy frequently used for business analysis. An item can be a group of Stock Keeping Units (SKU)s where each SKU is the same item but varies in size, weight, color, or other attributes. Item is sometimes referred to as Article. |
An employee is an individual who works for a retail organization. The model supports an employee performing multiple roles (such as cashier, stocker, or service desk agent) in multiple locations (for example, a mall with different retail banners) on the same day with varying pay structures. The separation of role from employee enables the retailer to manage their workforce more effectively and is possible with flexible site calendars with job role assignments.
The model supports employee splits by allowing multiple employees to be responsible for a single sale. Therefore, multiple employees can share commissions and SPIFFs (Sales Performance Incentive Factor Formulas).
Labor information, such as employee training details, compensation details, and time sheet breakdown, are captured. Employees can be assigned to one or more discount groups enabling retailers to offer flexible discount policies.
Privacy protection, related to employee information, uses Oracle Encryption technology to safeguard customer data.
Figure 2–2 represents the Employee entities.
Table 2-5 describes the Employee entities.
Table 2-5 Employee Entity Descriptions
Entity Name | Description |
---|---|
ADDRESS LOCATION |
Address for an individual location. |
BUSINESS UNIT JOB ROLE |
Job role within an organization. |
BUSINESS UNIT SHIFT |
Work shift associated with the Business Unit, cross referenced to the Employee job roles for the allocation of these shifts. |
EMPLOYEE |
An individual who works for the retail organization, accepts direction from the retail store management and satisfies the statutory criteria requiring that payroll taxes and benefit contributions be paid by the retailer. |
EMPLOYEE ACTUAL LABOUR HOURLY |
Actual shifts in which the employee worked, cross referenced with Business Unit Shift. |
EMPLOYEE ACTUAL LABOUR SALARIED |
Actual labor for salaried employees. |
EMPLOYEE ADDRESS |
Maps Employee table with Address Location Table. May include multiple addresses of an Employee and type of address, for example: permanent address, temporary address, rented, and others. |
EMPLOYEE DESIGNATION |
Designation (job title) of an employee. |
EMPLOYEE DISCOUNT GROUP ASSIGNMENT |
Association between an employee and an employee discount group, which makes the employee eligible for the price reductions available to the discount group. |
EMPLOYEE JOB ROLE ASSIGNMENT |
Cross References of the job roles present in the organization with the employees assigned to the job roles. Employees may have multiple roles. |
EMPLOYEE RESTRICTED INFORMATION |
Confidential information regarding the employees, in other words, the date of birth or national identifier of an employee. This table is encrypted. |
EMPLOYEE SCHEDULE |
Planned schedule for an employee, including the store, job role, and shift for which the employee is scheduled to work. |
EMPLOYEE TRAINING RECORD |
Record that a particular employee has been trained in performing a particular task or skill. |
JOB ROLES |
Job roles within the retail organization. |
ORGANIZATION BUSINESS UNIT |
A business unit of the organization that sells, stores, or distributes merchandises and services through either a physical location (store), catalog, web page or other channel, distribution center, or warehouse. |
ORGANIZATION STORE |
Business Unit with the retail organization from where goods and merchandise are sold for personal or household consumption. |
ORGANIZATION JOB ROLE AT SITE |
Resource planning table identifying various shifts, job roles, and number of employees required across stores. Associates Job Roles, Organization Business Physical Site, and Organization Site Shifts. |
ORGANIZATION SITE SHIFTS |
Work shifts for a physical site. Mapped to Job Roles, through Organization Job Role at Site, for allocation for these shifts. |
PAY CATEGORY |
Pay categories in the retail organization. |
PAY DETAIL |
Payouts from payroll, in other words, compensation amount to an employee under a payroll category and type, contribution from the company toward the employee under the payroll category and type. |
PAY TYPE |
Lookup for pay type. |
PRICE DERIVATION RULE |
Specification of a method to be used to transform the current sale unit retail amount into the retail price actually paid by a member of the employee discount group at the point of sale. |
ROLES HEIRARCHY |
Hierarchy among the job roles within the retail organization. |
USER |
Associative entity for Employee and Job Role. Assigns a unique ID for each job role that an employee performs at a particular store. An employee appears only once in the Employee table, but in this table, the employee appears once for each job role at each business unit. |
A level in a product hierarchy frequently used for business analysis.
An item, or article, can consist of a group of Stockkeeping Units (SKUs) where each SKU Item is the same article, but each size, weight, color of the item would have a different SKU.
Figure 2–3 represents the Item entity relationships.
Table 2-6 describes the Item Entities.
Table 2-6 Item Entity Descriptions
Entity | Description |
---|---|
AGE RESTRICTION RULE |
Defines a rule that restricts the sale of an Item to customers who must be of a minimum age and also the minimum age of an employee to perform a task , such as selling alcohol. |
ALTERNATIVE ITEM |
A cross-reference of items that might be substituted or offered in place of another item. |
BRAND |
Selling and promotional name to identify a product for advertising and name recognition purposes. |
BUSINESS ENTITY SELLING RULE |
Identifies the selling rules associated with at item at a particular business entity or business unit. |
BUSINESS ENTITY TENDER RESTRICTION RULE |
Identifies the tender classes and tenders accepted by the business entity. |
COMPANY |
Retail organization. Top level of the product and organization hierarchy. |
COMPETITOR RETAIL ITEM |
A RETAIL ITEM, which is stocked by a COMPETITOR and is perceived by the customer to have no discernible difference in terms of form, fit or function -- but may be sold at a different retail price. |
DAY |
Day Level in time entity, lowest level of each type of calendar. |
DEPOSIT RULE |
An association of a Stock Item and a Return Agent that defines the rules governing the deposit payment that must be paid by the customer at the time the item is purchased and the refund that must be made to the customer upon return of the item package or container. The rule is most often related to bottles, aluminum cans, crates and other containers, which must be returned for reuse or recycling. |
DISPLAY UNIT ITEM |
A sub-type of ITEM for shelf, rack or other display unit that is used by the store to display merchandise. Particularly used for racks and shelves custom designed for a particular item. Not normally for sale. |
ITEM |
Product, article or bundle of SKUs. For example, Item could be Acme shirt, with associated SKUs for each color and size of the shirt. |
ITEM CLASS |
5th level down in item hierarchy below item department. Item class consists of one or more item subclasses. |
ITEM CLUSTER |
Grouping of items based on common characteristics. |
ITEM CLUSTER CUSTOMER ASSIGNMENT |
Association entity of Customer to Item Cluster based on Customer buying patterns. |
ITEM DEPARTMENT |
4th level down in item hierarchy below item group. Item department consists of one or more item classes. |
ITEM DIVISION |
2nd level down in item hierarchy below Company. Item Division consists of one or more Item Groups. |
ITEM GROUP |
3rd level down in item hierarchy, below Item Division. Item Group consists of one or more Item Departments. |
ITEM HIERARCHY |
Names and descriptions for the user defined item hierarchy. |
ITEM HIERARCHY LEVEL |
User defined. Hierarchy level name and description. |
ITEM HIERARCHY LEVEL ASSIGNMENT |
Associative entity for Product Entity and Item Hierarchy Level; maps parents to children in a hierarchy. |
ITEM HIERARCHY VERSION |
Version of the hierarchy. |
ITEM LEVEL |
Name and description for User Defined Item Levels. |
ITEM LEVEL ATTRIBUTE |
User Defined Attributes associated with an item hierarchy level. |
ITEM LEVEL ATTRIBUTE VALUE |
Values for User Defined Attributes of an Item Level in the user defined hierarchy. |
ITEM MARKET DATA |
List of Market Items. Market items refer to the flow of goods through distribution channels authorized by the manufacturer or producer. |
ITEM RECLASS |
Items closed or reclassified today. The table is deleted or inserted daily by batch modules for reclassification processing. |
ITEM SALES PROHIBITION PERIOD RULE |
Rules restricting the sale of an item, in other words, day, time, age of customer, age of operator for alcohol sales. |
ITEM SEASON |
Associative entity for Item, Season, and Phase. Maps items to seasons and phases. |
ITEM SELLING RULE |
A set of commonly used selling rules for Items. The entity is typically in a one-to-one relationship with Item, unless each combination of size, color, and style of a particular piece of merchandise is individually assigned to a SKU for inventory recording purposes, but all sizes, colors, and styles of that item have the same selling rules. |
ITEM SHELF LEVEL |
A type of ITEM LABEL that provides a means of conveying information about a RETAIL ITEM to the CUSTOMER, EMPLOYEE, or both. The label is sited adjacent to the item, usually in front of the merchandise where the customer can easily see it. |
ITEM SPIFF RULE |
Rule or condition associated with an Item applied when a Sales Performance Incentive Factor Formula (SPIFF) is awarded to a salesperson. |
ITEM STATE |
Lookup for the state of the Item. |
ITEM SUBCLASS |
6th level down in item hierarchy, below Item Class. Item Subclass consists of one or more Items. |
ITEM SUBDEPARTMENT |
Item SubDepartment within a Department in the Product hierarchy at a given of time. |
ITEM TENDER RESTRICTION GROUP |
A collection of Items which share a common restriction on the which tenders may be used to pay for them at a store. |
ITEM TENDER RESTRICTION RULE |
An association between Item Tender Restriction Group and Tender which constrains the use of a specific type of tender in the settlement of a sale for a specific Item. |
LICENCE SALES RESTRICTION |
A restriction or limitation on the sales of a class of items based on the purchaser's profession, license, or other certification. |
LOYALTY AWARD |
Identifies a reward that a customer received for meeting the requirements of a promotion, in other words, a premium gift when a customer has purchased a certain amount during a promotion. |
MANUFACTURER |
The external party that manufactures the ITEM. |
MANUFACTURER COUPON FAMILY |
A unique code assigned by the manufacturer to classify product for promotion purposes. In the ARTS model it is used to validate manufacturers' coupons. |
ORGANIZATION BUSINESS ENTITY |
Any logical entity that is recognized as a part of the enterprise for Business Analysis and Transactions. Classification for a Business Entity can include company, operation unit, store, or warehouse. |
PHASE |
Period of time within a Season. |
POS DEPARTMENT |
A grouping of items with similar point of sale control and processing attributes. The entity type may also be used to control sales that are not properly identified at the item level. |
PRODUCT ENTITY |
Any logical entity that is recognized as a product or item for Business Analysis and Transactions. |
RESTRICTION VALIDATION QUESTION |
Standard question asked to a Customer as part of the process of negotiating a Sales Restriction that has been placed upon a class of items. |
SALES RESTRICTION |
A type of limitation that restricts the sale of a particular class of item. |
SEASON |
Seasons and their attributes. Seasons are arbitrary periods of time around which some retailers organize their buying and selling patterns. Each day should fall within no more than one season. |
SKU ITEM CHOICE |
Mapping from a parent group select item to item denoting a choice that may be made by the customer at the time sale for a Group Select Sale, package deal, or bill of material, in which several items are bundled under a single price, and the customer can make substitution for some items from a list of choices for the bundle. Example: Ski Package where the customer can choose one of several SKUs, often one of several skis, poles, bindingds and boots. |
TENDER |
Tender includes all the forms of payment that are accepted by the RETAIL STORE in settling sales and other transactions. |
TENDER CLASS |
A type of tender with common characteristics. |
VALIDATION QUESTION ASSIGNMENT |
Associates Restriction Validation Question to Sales Restriction. |
VENDOR |
External source for merchandise and goods that the retail store offers or for supplies and goods that the retail organization uses. |
VENDOR MANUFACTURER BRAND |
Associative entity for Vendor, Manufacturer, and Brand. |
VENDOR QUICKFACTS |
Collection of Vendor related measures. |
VENDOR SITE |
Subentity of Vendor indicating the vendor location which supplies the item. |
Association of clusters with customers.
Table 2-7 describes the Item Cluster Customer entities.
Table 2-7 Item Cluster Customer Entities
Entity Name | Description |
---|---|
CUSTOMER |
An individual or organization that purchases, may purchase, or did purchase goods and or services from a retail store. |
ITEM |
A level in a product hierarchy frequently used for business analysis. An item can be a group of Stock Keeping Units (SKU)s where each SKU is the same item but varies in size, weight, color, or other attributes. Sometimes referred to as Article. |
ITEM CLUSTER |
All Item clusters and their descriptions. |
ITEM CLUSTER CUSTOMER ASSIGNMENT |
Maps Item Cluster with Customer. |
Reflects the structure of the market as a whole at the product level. It allows the analyst to examine the performance of the retailer's products in the general marketplace. Understanding of the market situation allows the analyst to identify items in which the marketplace is outperforming the retailer and take corrective action.
Table 2-8 describes the Item Cluster Customer entities.
Table 2-8 Item Market Data Descriptions
Entity | Description |
---|---|
ITEM DEPARTMENT |
4th level in the item hierarchy, below item group. Item department consists of one or more item classes. |
ITEM MARKET DATA |
Item in Market with descriptive information that may be purchased through external entities. |
MARKET ITEM DEPARTMENT |
A department or category grouping of items in the market. |
MARKET ITEM DEPARTMENT ASSIGNMENT |
Associative entity mapping item department with market item department. |
POS IDENTITY |
Lists the various means of identifying items at the point of sale including the Point of Sale (POS) and the internal stock keeping Item ID for the item. The POS Item ID is generally filled with the Global Trade Item Number (GTIN) (Universal Product Code [UPC], European Article Number [EAN], and others) for an item, but it is not mandatory. |
SKU ITEM |
Stock Keeping Unit or unit identification (typically the UPC) used to track store inventory and sales. Each SKU is associated with an item, variant, product line, bundle, service, fee or attachment. |
Geographical descriptions and rollups based on location address.
Figure 2-4 Location Geography Entity Relationships
Table 2-9 describes the Location Geography entities.
Table 2-9 Location Geography Entity Descriptions
Entity | Description |
---|---|
ADDRESS LOCATION |
Physical address for an individual location. |
ADDRESS LOCATION HISTORY |
History of the names and addresses associated with a business unit, vendor, prospect, or customer. |
ADDRESS RELATED |
Associates one addresses with other addresses, for example, alternate address, locations with multiple addresses. |
ADDRESS TELEPHONE |
Phone numbers associated with a specific address. |
ADDRESS TYPE |
Lookup for address types such as home, office, mobile, and warehouse. |
EMAIL ADDRESS |
E-mail address associated with a Location. |
GEOGRAPHY DEMOGRAPHIC GROUP |
User defined classifications for Demographic attributes, such as:
|
GEOGRAPHY DEMOGRAPHY ATTRIBUTE |
User defined classifications for a demographic profile group, for example:
|
GEOGRAPHY DEMOGRAPHY VALUE |
Values associated with a geographic location as defined by the Geography Demography Attribute. |
GEOGRAPHY ENTITY |
Geographic entities that can be used to define the location of an address. Examples include, Region, North, State, Country, City, Geography, EMEA, Americas, and others. |
GEOGRAPHY HIERARCHY |
Type of geographic hierarchy, in other words, Sales Hierarchy, Organization Location Hierarchy, and others. |
GEOGRAPHY HIERARCHY LEVEL |
Associative entity for Geography Hierarchy and Geography Levels, mapping levels to hierarchies. |
GEOGRAPHY HIERARCHY LEVELS ASSIGNMENT |
Associative entity for Geography Hierarchy Level and Geography Entities; assigns geography values to hierarchy levels. |
GEOGRAPHY HIERARCHY VERSION |
Version table for the hierarchies. |
GEOGRAPHY LEVEL |
User defined Hierarchical levels for the geographic hierarchies. |
GEOGRAPHY LEVEL ATTRIBUTES |
User defined attributes associated with a specific geographical level. |
GEOGRAPHY LEVEL ATTRIBUTE VALUE |
Values as defined by geography level attributes for a geography hierarchy level. |
LOCAL AUTHORITY TYPE |
Lookup for type of Local Authority. Examples include city, state, and county. |
LOCAL TAX AUTHORITY |
Government authority that levies sales taxes or imposes rules or statutory compliances. |
POST CODE |
Postal codes and associative demographic information of interest to the Retail Organization. |
PROFILE SOURCE |
Source from which a Profile is acquired or populated, in other words, a mailing list provider. |
STATUS |
Lookup for status. |
TIME ZONE |
Time zone relative to Greenwich Mean Time (GMT). |
Media for communicating promotion and marketing efforts of the organization.
Table 2-10 describes the Media entities.
Table 2-10 Media Entity Descriptions
Entity | Description |
---|---|
COMMUNICATION TYPE |
Lookup for Type of communication; examples include, vocal, pictorial, broadcast, written. |
MEDIA |
Mass communication medium, such as New York Times, Boston Globe, CNN, BBC and others). Promotions are communicated through Media. |
MEDIA TYPE |
Lookup for the media type used to communicate with the customer. Examples include catalog, internet, postcard, TV, radio, newspaper, and list. |
An Organization is a company, association, institution, or other enterprise of interest to a retail enterprise. Each organization can use a business unit specific calendar.
In addition to the named organization business unit hierarchy, the model supports defining custom hierarchy(ies), custom level(s), assignment of level to multiple hierarchy(ies), custom defined attributes for each hierarchy and level, thereby providing the retailer the most flexibility in organization assignment.
A business unit can be modeled as a sales channel, a distribution center, or both; a store, warehouse, web-store, catalog, or tele-store (infomercial). Touch points can include workstation (with units and related attributes), Call center (with job role and link to employee), or both.
Market Area (a geographic area with syndicated data) and its Level enables a retailer to locate a new store, DC, or Warehouse. Methods to define the Market Area include the study of traffic flow, use of a retail gravity model, zip code method, or commuting data.
Trade area indicates where a retailer operates and can be primary, secondary or tertiary. Trade area may not be related to geographical area. It provides a mechanism to map market area data to a specific store, because census block (or whatever is used to store market area data) does not map to the geographic area that the store serves.
Support for syndicated data for demography aids the Organization in customer analysis using Dunn & Bradstreet or ABI. Non-syndicated data are captured through flexible and generic demographic group and related entities. Environmental conditions related to business units are also captured.
Retailer's spatial analytic needs GMROS (Gross Margin Return on Space) and SQUINCH (Square Inch analysis - typical for Direct Marketing) are supported using rich attributes for Inventory Location and Selling Locations, including x-y-z coordinates, shapes and sizes, and bills of material.
Direct retailers can analyze web page and catalog layouts with depictions, inventory location, and depiction locations.
Store-based retailers can indicate shifts and job roles appropriate to their locations with varying assignments.
Figure 2-5 represents the Organization entity relationships.
Figure 2-5 Organization Entity Relationships
Table 2-11 describes the Item Organization entities.
Table 2-11 Organization Entity Description
Entity | Description |
---|---|
ADDRESS LOCATION |
Addresses of a physical location. |
BUSINESS UNIT CALENDAR |
Operating Calendar for the Business Unit, allocated for each day of the year. |
BUSINESS UNIT JOB ROLE |
Job roles within the retail organization. |
BUSINESS UNIT TYPE |
Lookup for business unit types such as Store, Warehouse, Catalogue and others. |
BUSINESS UNIT SHIFT |
Work shift associated with the Business Unit, associated with Employee job roles for the allocation for these shifts. |
BUSINESS UNIT USAGE TYPE |
Possible values for the site usage are:
|
CALL CENTER |
A unit within an organization or a third-party organization that handles telephone sales, services, or both. |
CHANNEL TYPE |
Lookup for channel types; for example, selling, distribution, and others. |
COMPANY |
Retail Organization. Top level of the product and organization hierarchy. |
DAY |
Day Level in time entity, lowest level of each type of calendar. |
DAY ACTUAL CONDITION |
User defined condition describing phenomenon that may have affected sales on a particular day at a business unit. Phenomenon could include strike, construction, rain, or snow. |
EMPLOYEE |
An individual who works for the retail organization, accepts direction from the retail store management and satisfies the statutory criteria requiring that payroll taxes and benefit contributions be paid by the retailer. |
INVENTORY LOCATION |
Physical location where the retailer stores merchandise. Inventory Location may be co-located at a Site with Retail Store, Distribution Center, or Administrative Center and does not include containers, ships and trucks that are in transit. |
LOCATION TYPE |
Lookup for location types of a given site, in other words, Free-standing (Isolated retail location not connected to other retailers), Central Business District (CBD) (Corner location, Center location), Secondary Business District (SBD)-Street (Corner location, Center location), Neighborhood Business District (NBD)-Street (Corner location, Center location), Shopping Center (Strip centers, Malls), and Other (Airport, Hotel, Hospital, Resort, Store-within-store, Entertainment or Recreation). |
MARKET AREA |
A geographic area for which resident geographic data is available. Market Area may or may not contain a store. |
MARKET AREA LEVEL |
Level of classification inside the market areas based on, Community, Geography or user defined criteria. |
ORGANIZATION AREA |
Organization Hierarchy Level within an organization chain and is the parent of one or more Organization Regions. |
ORGANIZATION BANNER |
The name of a retail company's subsidiary that is recognizable to the consumer or the name of the store as it appears on the catalog, web channel or brick and mortar store. |
ORGANIZATION BUSINESS ENTITY |
Any logical entity that is recognized as a part of the enterprise for Business Analysis and Transactions. Classification for a Business Entity can include company, operation unit, store, warehouse and others. |
ORGANIZATION BUSINESS UNIT |
Business unit at the lowest level of the retail organization where business is conducted; in other words, store, distribution center, warehouse, web-store or catalogue.
|
ORGANIZATION CATALOG |
Publication, such as a book or pamphlet, containing list or itemized display of titles, or articles for exhibition or sale, usually including descriptive information or illustrations. For example, a catalog of fall fashions; a seed catalog. |
ORGANIZATION CHAIN |
Chain is the 2nd highest level within the organization hierarchy below company. A chain consists of one or more areas. |
ORGANIZATION DEMOGRAPHY VALUE |
Stores the Demographic information associated with the Business unit, as defined by the user defined demography groups and attributes. Examples:
|
ORGANIZATION DEPARTMENT |
A specialized section within a business unit. |
ORGANIZATION DISTRICT |
District is the 5th highest attribute within the organization hierarchy, below Region. A district consists of one or business units. |
ORGANIZATION DISTRIBUTION CENTER |
A distribution center for a set of products or a warehouse or other specialized building with refrigeration or air conditioning, which are supplied by transport, such as aircraft, truck, rail or ship, and then re-distributed to retailers or wholesalers. |
ORGANIZATION HIERARCHY |
User defined. Master list of all of the hierarchies in an organization. |
ORGANIZATION HIERARCHY LEVEL |
Association table for the hierarchies and levels. |
ORGANIZATION HIERARCHY LEVEL ASSIGNMENT |
Assignment table for Hierarchy levels to the Business Entities. |
ORGANIZATION HIERARCHY VERSION |
Version table for hierarchies. |
ORGANIZATION LEVEL |
List of all the business levels within an organization. |
ORGANIZATION LEVEL ATTRIBUTES |
User defined. Attributes applicable only to the corresponding level in the organization, in other words, Regional Language. |
ORGANIZATION LEVEL ATTRIBUTE VALUE |
Values for the user defined attributes associated with an organization hierarchy level. |
ORGANIZATION MARKET DATA |
Publicly available and statistical information regarding the customer organizations, such as DUNS number and number of employees. |
ORGANIZATION REGION |
Region is the 4th highest attribute within the organization hierarchy, below Area. A region consists of one or more districts. |
ORGANIZATION STORE |
Fixed location from where goods and merchandise are sold for personal or household consumption. |
ORGANIZATION WAREHOUSE |
Location in which goods or merchandise are stored but not sold. |
ORGANIZATION WEBSTORE |
A Web site owned or commissioned by the organization from where goods and merchandise are sold for personal or household consumption. |
SELLING LOCATION |
An area of floor space or shelf space within the Retail Store to which sales can be assigned. Selling Location may be assigned to or rented by a Vendor. |
SELLING LOCATION TYPE |
Lookup for selling location types, in other words, Shelf, Floor, Rack and others. |
STORE WORKSTATION |
Device used as an Interface to any retail business function, for example, the capture and storage of TRANSACTIONS and operational performance reporting. Usually a cash register. |
TOUCHPOINT |
Place from where transactions take place. Meeting point for customer and retail organization. Touchpoint can be both logical and physical.
|
TRADE AREA |
Geographic region from which a store draws most of its retail customers. Can be defined by distance, drive time, or other factors. |
TRADE AREA COVERAGE |
Demographic and accessibility data for a given trade area. |
USER |
Associative entity for Employee, Job Role; associates a unique ID for every job role that an employee performs at a particular business unit. An employee appears only once in the Employee table, but in USER table, the employee appears once for each job role at each business unit. |
WORK STATION DISPLAY |
Physical display for Items near the workstation, usually intended for impulse purchases such as magazines, candy, gift cards, and calendars. |
The product hierarchy represents the product line that the company sells. Retailers must understand their products when making crucial decisions about what items to buy, where to stock them, and how to sell them to customers. The product hierarchy makes it possible for analysts to measure performance at any level represented in the product hierarchy.The product hierarchy is essential to the category or department manager who must know what items turn the highest profit, or how an item performs within the market as a whole.Due to its importance for analysis in the retail environment, attributes from the product hierarchy are present in nearly every fact table. In most cases, data is kept at the lowest level in the hierarchy (SKU item) to allow maximum flexibility and detail in reporting.
Promotion reflects the tactics a retailer undertakes to generate increased incremental sales volume for specific item-store combinations within a promotional event. Promotions are frequently communicated as part of a marketing campaign to ensure that awareness is generated with the target audience.
Figure 2–6 represents Promotion entity relationships.
Figure 2-6 Promotion Entity Relationships
Figure 2–12 describes Promotion Entities.
Table 2-12 Promotion Entity Descriptions
Entity | Description |
---|---|
BUSINESS WEEK |
Information relating to a week in a Business Calendar. |
CAMPAIGN |
Entire communication strategy for a specific marketing communications program. The marketing communications program is frequently in support of promotional events and individual promotions but can be standalone. Retailers execute several different types of campaigns, including advertising, direct marketing and in-store marketing. There are several sub-types within each category as well. Advertising includes (1) traditional broadcast, (2) direct response and (3) online. Direct marketing includes (1) individually tracked and (2) summary tracking. In-store includes (1) broadcast and (2) 1:1. The 1:1 is usually performed in call centers or on Web sites. Each campaign consists of 1 to n communications, which is the lowest level of the campaign object. |
CAMPAIGN COST |
Costs associated with a campaign. |
CAMPAIGN CUSTOMER ASSIGNMENT |
Assignment entity among Campaign Execution message, Customer, Campaign Message Rendering. |
CAMPAIGN EXECUTION MESSAGE |
Information regarding the message costs for a media campaign. |
CAMPAIGN MEDIA |
Media associated with the campaign. |
CAMPAIGN MEDIA LAUNCH |
Details about how a campaign is carried out. |
CAMPAIGN MEDIA SELLING ITEM |
Items presented to customer or public as part of the campaign. |
CAMPAIGN MESSAGE DEPICTION |
Information regarding the depiction of a campaign message within the media. |
CAMPAIGN MESSAGE RENDERING |
Details about how the campaign message was rendered, broadcast or distributed in the media and associated costs.
|
CAMPAIGN TARGET |
Sub entity of Campaign Customer Assignment indicating the target audience for a campaign. |
COMMUNICATION TYPE |
Lookup for Type of communication; examples include, vocal, pictorial, broadcast, written. |
COST |
Sub entity of Campaign Message Rendering containing the variable costs of each message communication. |
CREATIVES |
Creative content of the message. Examples include photos, writing, drawings, or recordings. Points to a file location where the creatives are stored. |
CUSTOMER |
An individual or organization that purchases, may purchase, or did purchase goods and or services from a retail organization. |
EVENT |
Something that takes place or has been planned represented by the designation of time, place and purpose. |
GEOGRAPHIC ENTITY |
Describes various physical geography entities that can be created. For example, Geographic Entities could be Sales Region North, State, country city, geography, EMEA, Americas or others. |
MEDIA |
Mass communication medium, such as New York Times, Boston Globe, CNN, BBC and others). Promotions are communicated through Media. |
MEDIA DEPICTION ITEM ASSIGNMENT |
Associative entity linking Campaign Message Depiction with Promotion Selling Item. |
MEDIA TYPE |
Description of Media Type. Examples include TV, radio, newspaper, and list. |
ORGANIZATION BUSINESS ENTITY |
Any logical entity that is recognized as a part of the enterprise for Business Analysis and Transactions. Classification for a Business Entity can include company, operation unit, store, warehouse and others. |
PROMOTION |
A collection of eligibilities and price derivation rules, during a specific time group. |
PROMOTION ITEM |
Associative entity connecting any level of the item hierarchy and organization hierarchy, and optionally a vendor, with the promotion. |
PROMOTION MEDIA COST |
Plan and forecast costs for a campaign media. |
PROMOTION PRICE DERIVATION |
Rules for which sale prices to use for the promotion selling item. |
PROMOTION SELLING ITEM |
Associative entity linking promotion item and campaign media selling item, and the prices which may be used for the item during the promotional period. |
TARGET |
A defined group at which the promotion is aimed. |
VENDOR |
External source for merchandise and goods that the retail store offers or for supplies and goods that the retail organization uses. |
SKU Item is the lowest level of merchandise for which inventory and sales records are retained within the retail organization business unit.
The model supports the following item types:
Stock Item
Service Item
Prepared Item
Aggregate Item
Collection Item
Substitution Item
Group Select Item
The model includes information specific to the item type including Base, Net and Landed Cost, a models Construction for 'Service Item', Item Choice for 'Group Select' and Bill-of-Materials (BOM) for Collection Item with Member Item's contribution there by supporting a Pack versus Standalone Item Contribution Analysis.
It supports a simple cross-reference between the barcode, point of sale scan code or other keyed identifying number used at POS and the internal stock keeping unit (SKU) for the item. These identifiers are generally filled with the GTIN (UPC or EAN) for an item, but this is not required. A retailer may develop and maintain its own set of POS identifiers.
In addition to supporting item attributes such as Size, Style, Flavor, Color, Fabric, Fiber, and Coating, the model supports a flexible and generic way to define variety group and related entities that can capture other interesting attributes that are implementation specific
The model provides a set of prices that are applied to an Item, which can be maintained at generic level or at business unit level including its hierarchy. It has been designed to collect the "item price" file with the highest performance, while maintaining the price history and confidentiality. The model provides a simple cross-reference between a SKU Item identified and managed internally by the retail store and a Vendor Item with the intent of providing a cross reference between the internal and supplier view of a SKU. It provides rule for Retail Sale Unit Count Conversion and UOM that help in exploding a higher level material receiving unit into a lower level. For example a pallet may be exploded into 24 cases and each case may be exploded into 24 retail sale units.
The model supports inventory rules that allows retailer to specify maximum and minimum inventory on hand.
Figure 2–7 shows the relationships of the SKU Item entities.
Table 2-13 SKU Item Entity Descriptions
Entity | Description |
---|---|
AGGREGATE SKU |
Sub-type of SKU that is an aggregation of one or more constituent SKUs. The constituent items could also be sold individually. |
COATING |
Lookup entity for SKU Item attribute Coating. |
COLOR |
Lookup table for SKU Item attribute Color. |
DYE |
Lookup entity for SKU Item attribute Dye. |
FABRIC |
Lookup entity for SKU Item attribute Fabric. |
FIBER |
Lookup entity for SKU Item attribute Fiber. |
GROUP SELECT |
SKU item that is part of a group of SKU items, only one of which is sold. The choice of which item is made by the customer at the time of purchase. |
ITEM |
A level in a product hierarchy frequently used for business analysis. An item can be a group of Stock Keeping Units (SKU)s where each SKU is the same item but varies in size, weight, color, or other attributes. Sometimes referred to as Article. |
ITEM SEASON |
Associative entity for Item, Season, and Phase. Maps items to seasons and phases. |
ORGANIZATION BUSINESS UNIT |
A business unit of the organization that sells, stores, or distributes merchandises and services through either a physical location (store), catalog, web page or other channel, distribution center, or warehouse. |
POS IDENTITY |
A simple cross-reference between the barcode, point of sale scan code or other keyed identifying number used at the Point of Sale (POS) and the internal stock keeping Item ID for the item. The POS Item ID is typically filled with the Global Trade Item Number (GTIN) (Universal Product Code [UPC], European Article Number [EAN], and others) for an item, but it is not mandatory. A retailer may develop and maintain its own set of POS identifiers. |
PREPARED |
Sub-type of SKU Item for which the final product is manufactured (or prepared) for sale by the retailer according to a pre-defined Recipe. |
SERVICE SKU |
SKU that provides a detailed identifier and description for a service offered for sale to a customer by the retail organization. Service SKU also identifies and describes rental items and other tangible items used by customers for a contracted period, but not purchased. |
SERVICE TERM |
The terms and conditions that apply to the provision of any services either by the retail Organization or by arrangement through a third party. The terms and conditions are normally listed in a separate document, which the customer is requested to sign as acceptance of these terms. |
SIZE |
Lookup entity for the SKU Item attribute Size |
SIZE TYPE |
Lookup for type of size. For example, shoes, clothing, or package dimension. |
SKU ITEM |
Stock Keeping Unit or unit identification (typically the UPC) used to track store inventory and sales. Each SKU is associated with an item, variant, product line, bundle, service, fee or attachment.
|
SKU ITEM BUSINESS UNIT INVENTORY RULES |
Associative entity for Business Unit, SKU Item and Vendor that defines the inventory rules for the vendor item at the business unit. |
SKU ITEM BUSINESS UNIT SELLING PRICE |
SKU Item Selling Price related to a business unit. |
SKU ITEM CHOICE |
A mapping from a parent Group Select Item to Item denoting a choice that may be made by the customer at the time of sale for a Group Select sale, package deal, or bill of material, in which several items are bundled under a single price, and the customer can make substitutions for some items from a list of choices for the bundle. |
SKU ITEM COLLECTION |
An optional relationship between a SKU item and its components and affiliates where the components consist of other SKU ITEMs. |
SKU ITEM CONSTRUCTION |
The terms and conditions that apply to the provision of any services either by the retail organization or by arrangement through a third party. The terms and conditions are normally listed in a separate document which the customer is requested to sign as acceptance of these terms. |
SKU ITEM SELLING PRICE |
The set of prices that re applied to a SKU Item. |
SKU ITEM PRICE HISTORY |
Historical archive of the retail selling unit price at which a given SKU Item was actually sold at POS, net of markdowns, markups and other changes that modify the cumulative mark on for an SKU Item. |
SKU ITEM SHELF ATTRIBUTE |
Shelf requirements for a SKU item. |
SKU ITEM STYLE |
Lookup entity for SKU Item attribute Style. |
SKU ITEM SUBSTITUTION |
A 3-way join of SKU Item, SKU Item Collection and Substitute SKU Item indicating the Substitute SKU could have replaced a member SKU in the SKU Item Collection, and subsequent quantity and price adjustment. |
SKU ITEM TYPE |
Lookup for metadata denoting the type of SKU item being sold (or returned) in the line item Values: Stock, Service, Fee, Deposit, Deposit Refund, Tare, Swatch, Component, Raw, Prepared, Group Select, and Aggregate. |
SKU ITEM VARIETY ASSIGNMENT |
User Defined SKU Item attributes other than size, weight, and style, such as color, associated with the SKU Item. Can have multiple varieties for an SKU Item. |
UNIT OF MEASURE |
Identifies and describes valid units of measure that are used throughout. |
SKU ITEM WEIGHT |
Look up entity for the SKU Item Attribute Weight. |
STOCK |
Unit of merchandise sold to a customer or used by the Retail Store. Examples include:
|
STOCK ITEM TYPE |
Lookup for types of Stock Item. |
TAX EXEMPT CODE |
A code to denote the tax exemption status from sales and use tax. |
VARIETY |
User Defined Item attribute other than size, weight, and style, such as color. |
VARIETY TYPE |
Lookup for variety types; for example, shape. |
VENDOR |
External source for merchandise and goods that the retail store offers or for supplies and goods that the retail organization uses. |
VENDOR ITEM |
Items supplied by the vendor with vendor-specific item and provides the vendor-specific attributes of the item. Provides the vendor's view of the item and uses the vendor's descriptions of item attributes. |
VENDOR ITEM BUSINESS UNIT ASSIGNMENT |
Defines the Vendor Items supplied to a Business Unit. |
VENDOR ITEM SKU ASSGNMENT |
Associative entity defining the relationship between vendor item and SKU item. |
VENDOR SKU BUSINESS UNIT ASSIGNMENT |
Associative entity defining the relationship between vendor, SKU Item, and Business Unit. |
WEAVE |
Lookup entity for SKU Item Attribute Weave. |
Provides for variations in SKU selling price for business unit and item state. The following table describes the SKU Item Business Unit Selling Price Assignment.
Table 2-14 describes the SKU Item Business Unit Selling Price Assignment Entities.
Table 2-14 SKU Business Unit Selling Price Assignment
Entity | Description |
---|---|
ITEM SELLING RULE |
A set of commonly used selling rules for Items. The entity is typically in a one-to-one relationship with Item, unless each combination of size, color, and style of a particular piece of merchandise is individually assigned to a SKU for inventory recording purposes, but all sizes, colors, and styles of that item have the same selling rules. |
ITEM STATE |
This code defines the current state of an item within the retail store. An items state limits what actions may be taken on an item in terms of ordering, receiving, selling, returns, transfers, and counting. Example statuses include active, inactive, discontinued, and pending. |
ORGANIZATION BUSINESS ENTITY |
Any logical entity that is recognized as a part of the enterprise for Business Analysis and Transactions. Classification for a Business Entity can include company, operation unit, store, warehouse and others. |
ORGANIZATION BUSINESS UNIT |
A business unit of the organization that sells, stores, or distributes merchandises and services through either a physical location (store), catalog, web page or other channel, distribution center, or warehouse. |
SELLING STATUS |
Lookup for the selling status of the item. For example, active, discontinued, seasonal, to be discontinued, held for future release and others. |
SKU ITEM |
Stock Keeping Unit or unit identification (typically the UPC) used to track store inventory and sales. Each SKU is associated with an item, variant, product line, bundle, service, fee or attachment. |
SKU ITEM BUSINESS UNIT SELLING PRICE |
SKU Item Selling Price related to a business unit. |
SKU ITEM SELLING PRICE |
The set of prices that are applied to an SKU Item. |
TAXABLE GROUP |
A group of Items for which a Tax Authority defines Tax Group Rules. |
The Tendor Repository entities are types of physical tender containers used in the retail enterprise. Examples include: assets like, store safe(s) or tills.
Figure 2-8 represents Tender Repository relationships.
Figure 2-8 Tender Repository Relationships
The time hierarchies play a central role in the data warehouse because the Business questions in a retail environment, as in any other, are almost invariably time based.
Oracle Retail Data Model provides multiple calendars, all based on the Day entity. These include Calendar (Gregorian), Business, Fiscal, Advertising, and Planning. Time-based performance comparisons are an important part of decision support in retailing. For example, a user might want to assess sales performance for a current month or season by comparing the sales performance to the same month or season for the previous year. The time hierarchies allow the transformations required to support time-based comparisons to take place.
Time intervals are based on the 4-5-4 calendar or a thirteen-period calendar. The calendar can be implemented as 4-5-4, 4-4-5, or 5-4-4, depending upon the needs of the retail organization analyst. In addition, the retail organization determines the weekday on which a week begins and ends. Every quarter contains thirteen full weeks. Quarters have a four week month, followed by a five week month, and ended by a four week month.
A thirteen period calendar may be used as an alternative. The retailer must determine the structure of the calendar and implement the same consistently. For example, a thirteen period calendar may begin on the Sunday after the last Saturday in February. The calendar year may end on a Saturday 52 or 53 weeks after it begins. Every five or six years there are 53 weeks in the year.
Note:
The business calendar is configurable using calendar scripts.The year is divided into four quarters. In the 4-4-5 quarter the first quarter contains two periods of four weeks and one period of five weeks. The following figure represents the Time entity relationship.
Figure 2–9 represents Time entity relationships.
Table 2–15 describes the Time Entities.
Table 2-15 Time Entity Description
Entity | Descriptions |
---|---|
ADVERTISING PERIOD |
Period level in the advertising calendar. |
ADVERTISING QUARTER |
Quarter level in the advertising calendar. |
ADVERTING WEEK |
Week level in the advertising calendar. |
ADVERTISING YEAR |
Year level in the advertising calendar. |
BUSINESS HALF MONTH |
Half-month level in the Business calendar. |
BUSINESS HALF YEAR |
Half-year level in the Business calendar. |
BUSINESS MONTH |
One month in the Business calendar. |
BUSINESS QUARTER |
Quarter level in Business calendar. |
BUSINESS WEEK |
One week in the Business calendar. |
BUSINESS YEAR |
One year in the Business calendar. |
CALENDAR HALF MONTH |
Half-month level in the normal calendar. |
CALENDAR HALF YEAR |
Half-year level in the normal calendar. |
CALENDAR MONTH |
Month level in the normal calendar. |
CALENDAR QUARTER |
Quarter level in the normal calendar. |
CALENDAR WEEK |
Week level in the normal calendar. |
CALENDAR YEAR |
Year level in the normal Calendar. |
DAY |
Day level in the normal calendar. This day is common to all calendars. |
FISCAL HALF MONTH |
Half-month level in the fiscal calendar. |
FISCAL HALF YEAR |
Half-year level in the fiscal calendar. |
FISCAL MONTH |
Month level in the fiscal calendar. |
FISCAL QUARTER |
Quarter level in the fiscal calendar. |
FISCAL WEEK |
Week level in the fiscal calendar. |
FISCAL YEAR |
Year level in the fiscal calendar. |
PLANNING PERIOD |
Period level in the planning calendar. |
PLANNING QUARTER |
Quarter level in the planning calendar. |
PLANNING SEASON |
Plan season information. |
PLANNING SEASON WEEK ASSIGNMENT |
Plan season and respective week relationships. |
PLANNING WEEK |
Week level in the planning calendar. |
PLANNING YEAR |
Year level in the planning calendar. |
WEEK |
Week level in normal calendar. |
WEEK DAY |
Calendar weekdays. |
The Time of Day hierarchy permits analysis of traffic flows and employee productivity, as well as for analysis of loss prevention where identifying problems and trends requires the use of hourly or smaller time increments. In addition, the time of day hierarchy allows analysis of sales and return transactions on a quarter-hourly basis.The Time of Day hierarchy is not related or linked to the time calendar hierarchy. It captures time of day at hour, half hour and quarter hour level.
Figure 2–10 represents the Time of Day Entity Relationships.
Figure 2-10 Time of Day Entity Relationships
Table 2–16 describes the Time of Day Entities.
Time-based comparisons are an essential part of analysis at almost every level in a retail environment. Time transformations are used to compare values from different time periods. The current year versus last year and month-to-date comparisons are examples of common time transformations. Typical examples are the comparison of sales value for the current season-to-date to the same period last year, or the retail value of inventory compared to the previous week.
Time Transformation entities enable following types of analysis:
Store sales by day, week (calendar, fiscal, business, Ad), and month
Comparable store sales using figures such as:
this-year-this week (TY-TW) versus last-year-this week (LY-TW)
monthly comparisons like this-year-this month (TY-TM) versus this-year-last month (TY-LM)
year-to-date comparisons (TY-YTD) versus (LY-YTD)
same day comparison across week this-day-this-week (TD-TW) versus this-day-last week (TD-LW)
Time transformations relate the elements of time-based attributes to other elements of the same attribute. For any given year, month, week, or day, there is a corresponding time frame for the previous year.
Transformation entities simply specify the relationship between elements for some time-based frame of reference. That is, comparing sales value for the current week to the same week last year employs a table that specifies every calendar week and the corresponding week for the previous year. These entities make it possible to identify the corresponding week for last year for every week on the calendar which is an example of a one-to-one transformation. For every element in the table, there is one corresponding element for the time frame in question. It also provides the support for many-to-many transformations for calculating year-to-date, season-to-date and similar totals. These entities specify all of the elements that are to be included in calculating a total from a given reference point. Fore example, year-to-date transformation specifies all of the days or weeks that are included in the transformation from a given day or week since the beginning of the year.
Figure 2–11 represents the Time Transformation Entity Relationships
Figure 2-11 Time Transformation Entity Relationships
Figure 2–17 describes the Time Transformation Entities.
Table 2-17 Time Transformation Entity Description
Entity | Description |
---|---|
ADVERTISING PERIOD |
Information relating to a Period in Advertising Calendar. |
ADVERTISING QUARTER |
Quarter level in the advertising calendar. |
ADVERTISING WEEK |
Week level in the advertising calendar. |
ADVERTISING YEAR |
Year level in the advertising calendar. |
CALENDAR HALF MONTH |
Half-month level in the normal calendar. |
CALENDAR HALF YEAR |
Half-year level in the normal calendar. |
CALENDAR MONTH |
Month level in the normal calendar. |
CALENDAR QUARTER |
Quarter level in the normal calendar. |
CALENDAR WEEK |
Week level in the normal calendar. |
CALENDAR YEAR |
Year level in the normal calendar |
DAY |
Day level in the normal calendar. |
DAY TO DATE TRANSFORMATION |
Cumulative time transformations at the day level. |
DAY TRANSFORMATION |
Transformation for a day, for example: this day last year, this day last month. |
FISCAL HALF MONTH |
Half-month level in the fiscal calendar. |
FISCAL HALF YEAR |
Half-year level in the fiscal calendar. |
FISCAL MONTH |
Month level in the fiscal calendar. |
FISCAL QUARTER |
Quarter level in the fiscal calendar. |
FISCAL WEEK |
Week level in the fiscal calendar. |
FISCAL YEAR |
Year level in the fiscal calendar. |
HALF MONTH TO DATE TRANSFORMATION |
Cumulative time transformations at the half-month level. |
HALF MONTH TRANSFORMATION |
Transformation for a half month such as this half month last year or this year last half month. |
HALF YEAR TO DATE TRANSFORMATION |
Cumulative time transformations at the half-year level. |
HALF YEAR TRANSFORMATION |
Time transformations at the half-year level. |
MONTH TO DATE TRANSFORMATION |
Cumulative time transformations at the month level. |
MONTH TRANSFORMATION |
Time transformations at the month level. |
PERIOD TO DATE TRANSFORMATION |
Cumulative time transformations at the period level. |
PERIOD TRANSFORMATION |
Time transformations at the period level. |
PLANNING PERIOD |
Period level in the planning calendar. |
PLANNING QUARTER |
Quarter level in the planning calendar. |
PLANNING WEEK |
Week level in the planning calendar. |
PLANNING YEAR |
Year level in the planning calendar. |
QUARTER TO DATE TRANSFORMATION |
Cumulative time transformations at the quarter level. |
QUARTER TRANSFORMATION |
Time transformations at the quarter level. |
SEASON |
Seasons and their attributes. Seasons are arbitrary periods of time around which the retailer may organize their buying and selling patterns. Each day should fall within no more than one season. |
TIME STANDARD BY DAY |
Information relating to a day. |
TIME STANDARD BY WEEK |
Information relating to a week. |
WEEK TO DATE TRANSFORMATION |
Cumulative time transformations at the week level. |
WEEK TRANSFORMATION |
Time transformations at the week level. |
YEAR TRANSFORMATION |
Transformations at the year level. |
Touchpoint is the interface for interaction between the retail business unit and customer. Touchpoint has two sub-entities: store workstation and call center. Each touchpoint is associated with an organization business unit and each call center touchpoint is associated with an employee. The following table describes the Touchpoint entities.
Table 2–18 describes the Touchpoint Entities.
Table 2-18 Touchpoint Entity Descriptions
Entity | Description |
---|---|
CALL CENTER |
A unit within an organization or a third-party organization that handles telephone sales, services, or both. |
STORE WORKSTATION |
Device used as an Interface to any retail business function, for example, the capture and storage of transactions and operational performance reporting. Usually a cash register. |
TOUCHPOINT |
Place from where transactions take place. Meeting point for customer and retail organization. Touchpoint can be both logical and physical.
|
USER |
Associative entity for Employee, Job associates a unique ID for each job role that an employee performs at a particular store. An employee appears only once in the Employee table, but in a user table, the employee appears once for each job role at each store. |
WORKSTATION DISPLAY |
Display unit for items placed for sale at the workstation |
WORKSTATION LOCATION TYPE |
Lookup for Workstation Location types. |
A party from whom the retail enterprise may purchase goods or services.
Table 2–19 describes the Vendor Entities.
Table 2-19 Vendor Entity Descriptions
Entity | Description |
---|---|
ADDRESS LOCATION |
Address for a physical; location. |
APPOINTMENT CALENDAR |
Appointment calendar for a vendor with the retail business unit. |
APPOINTMENT TYPE |
Lookup for Appointments types such as recurring, urgent, or planned. |
CARRIER |
An external party that transports merchandise or supply items from their source to the retail store and from the retail store back to their source. |
DAY |
Day Level in time entity, lowest level of each type of calendar. |
DEAL |
Special offer from a vendor to the retail organization. The deal generally provides allowances, discounts, special favorable terms of payment or other incentives to motivate the retail organization to purchase more products or services from a supplier. |
DEAL VENDOR ITEM ASSIGNMENT |
Identifies a specific Vendor Item offered as part of a deal to the retail organization and defines how the deal cost is to be handled. |
DISCREPANCY TOLERENCE RULE |
Defines permissible variance between the total inventory control document cost (based on the suppliers cost) and the stores receiving total (based on the stores record of supplier item cost). Any variance that exceeds the discrepancy threshold triggers an invoice or item-level reconciliation. |
FACTOR COMPANY |
Information about the factor company. |
ORGANIZATION BUSINESS UNIT |
A business unit of the organization that sells, stores, or distributes merchandises and services through either a physical location (store), catalog, web page or other channel, distribution center, or warehouse. |
ORGANIZATION MARKET DATA |
Publicly available and statistical information regarding the customer organizations, such as DUNS number and number of employees. |
STATUS |
Lookup for status, such as Buy, Not Buy, Suspended, or Active. |
STATUS REASON |
Reason for the vendor status. For example, if the status is "Not Buy", a reason could be government restrictions, vendor quality issues, and others. |
STATUS TYPE |
Lookup for status. |
TERM MASTER |
Master data of terms of business with the vendor. |
VENDOR |
External source for merchandise and goods that the retail store offers or for supplies and goods that the retail organization uses.
|
VENDOR ADDRESS |
Associative entity between Vendor and Address Location; maps vendors to their addresses. |
VENDOR APPOINTMENT |
Details of vendor appointments. Vendor appointments are regular visits by the vendor's representative to the retail store. |
VENDOR CARRIER ASSIGNMENT |
Associative entity for Vendor and Carrier; maps vendors and their carriers. |
VENDOR CLASS |
Classification of Vendors such as Primary, Associate, or Direct Supply. |
VENDOR CONTRACT |
Details of contract with Vendor. |
VENDOR FACTOR COMPANY ASSIGNMENT |
Associative entity linking the factor company with the vendor. |
VENDOR ITEM |
Items supplied by the vendor, along with vendor-specific item and provides the vendor-specific attributes of the item. Provides the vendor's view of the item and uses the vendor's descriptions of item attributes. |
VENDOR ITEM BUSINESS UNIT ASSIGNMENT |
Defines the Vendor Items supplied to a Business Unit. |
VENDOR QUICK FACTS |
Collection of vendor related measures. |
VENDOR RATING |
Rating for a vendor |
VENDOR RATING TYPE |
Lookup for vendor rating type such as timeliness or quality of goods. |
VENDOR SITE |
Subentity of Vendor indicating the vendor location which supplies the item. |
VENDOR SITE ADDRESS |
Association entity between the Vendor Site or Vendor and the address location. |
VENDOR SITE CARRIER ASSIGNMENT |
Relationship between VENDOR SITE and CARRIER. |
VENDOR STATUS |
Status of Vendor. Indicates is the vendor is presently used, on suspension, or another status. |
Vendor SKU Business Unit Assignment shows the relationship between SKU Item, Item, Vendor and Organization Business Unit.
Table 2–20 describes the Vendor SKU Business Unit Assignment.
Table 2-20 Vendor SKU Business Unit Assignment Entity Description
Entity | Description |
---|---|
ORGANIZATION BUSINESS UNIT |
A business unit of the organization that sells, stores, or distributes merchandises and services through either a physical location (store), catalog, web page or other channel, distribution center, or warehouse. |
ORGANIZATION BUSINESS UNIT ASSIGNMENT |
Place from where an organization conducts its business, for example, store, distribution center, warehouse, web-store or catalogue. |
SKU ITEM |
Stock Keeping Unit or unit identification (typically the UPC) used to track store inventory and sales. Each SKU is associated with an item, variant, product line, bundle, service, fee or attachment. |
VENDOR |
External source for merchandise and goods that the retail store offers or for supplies and goods that the retail organization uses. |
VENDOR ITEM BUSINESS UNIT ASSIGNMENT |
Defines the Vendor Items supplied to a Business Unit. |
VENDOR ITEM SKU ASSIGNMENT |
Associates vendor with Item SKU |
VENDOR SKU BUSINESS UNIT ASSIGNMENT |
Relationship between SKU Item vendor and organization business unit. |
Lookup entities hold the descriptions for frequently used attributes which saves space, as the referring fact table holds only a small key or code and foreign key, and Oracle Retail Data Model stores the space consuming description in a lookup entity table and does not repeat it in each transaction row in which it is referenced.
Table 2-21, "Lookup Entity Descriptions" lists and briefly describes the lookup entities and links to individual topics for each entity. These individual topics provide a more complete description, list possible values, and list entities that use the lookup.
Table 2-21 Lookup Entity Descriptions
Entity Name | Brief Description |
---|---|
Types of accounts. |
|
Distinct occurrences of activity request types. |
|
Address types; for example, Home, office, or warehouse. |
|
A period of time that can extend over 2 or more days. |
|
Appointment types; for example, recurring, urgent, or planned. |
|
Identifies the method used to authorize tender. |
|
Unique retailer assigned identifier for a Retail Store, Distribution Center or Administration Center that performed the transaction. |
|
The type of business unit (for example, store, kiosk, or call center) |
|
A code denoting which kind of card was accepted. |
|
Static Certificate age bands used to categorize based on age. |
|
Contains information on certificate types like Gift Certificate or Work Certificate. |
|
Types of channel (for example, selling or distribution). |
|
Lookup for different types of check in. |
|
The Coating attribute of SKU Item. |
|
The Color attribute of SKU Item. |
|
Defines the unit type the owned attribute costs are assigned to for an item such as sale, pack, or ship unit. |
|
The barcode on a store or manufacturer coupon. |
|
National designation and quantitative value of monetary media used as tender in the processing of the Tender Line Item. |
|
Categorizations of Customer Occasion. |
|
Distinct values of customer order hold events. |
|
Defines where and how a customer may pickup an item. |
|
Specifies the quantitative value of the referenced Currency media. |
|
Captures the various types of discount. |
|
Denotes what disposition a returned item was in (for example, return to vendor). |
|
The Dye attribute of SKU Item. |
|
Describes types of Employee (for example, Part Time). |
|
Method used of entering transaction data |
|
Credit card or member card information |
|
Defines the temperature, relative humidity, lighting and other physical or climatic environmental requirements for storing and displaying the item. |
|
The Fabric attribute of SKU Item. |
|
The Fiber attribute of SKU Item. |
|
Defines the relevant hazardous material handling properties of the item. |
|
Defines the inventory accounting method to be used for the item. |
|
Type of inventory document |
|
Type of inventory location. |
|
Type of Inventory (for example, Damaged or Customer Order). |
|
Defines a state that Stock Items are kept in the retail enterprise item inventory records. |
|
Status of the inventory. |
|
Lookup for the state of the item (for example, Damaged Item). |
|
How the certificate or voucher was issued by the organization business unit. |
|
Language for which the system keeps some string translations. |
|
The Local Authority Type is for types of Local Authority |
|
A code that describes what business activities and functions are performed in a specific location. |
|
Code assigned by the manufacturer to classify product for promotion purposes. |
|
A description of Media Type (for example, TV, radio, newspaper, or list).. |
|
Types of frequent shopper programs (for example, miles or money). |
|
Type of miscellaneous line item |
|
Multiple tender combinations. |
|
Lookup for types of Order Category (for example, backorder). |
|
A unique retailer assigned code denoting a potential state for a Customer Order Line Item (for example, Partial Delivery). |
|
A unique retailer assigned code denoting a type of Customer Order (for example, Layaway). |
|
Description of an order's source (for example, store or call center). |
|
A unique retailer assigned code denoting a potential state for a Customer Order. |
|
Lookup for the different types of order status type. |
|
Various pay categories present in an organization. |
|
Various pay types under the different categories. |
|
Type of personal identification required to authorize a tender. |
|
The type of preference relevant to consumers or customers (for example, color preference). |
|
Basic published or advertised price, often subject to discount. |
|
The source from which a Profile is acquired or populated (for example, a mailing list provider). |
|
Reason codes and descriptions (for example, Cancel For Late Shipment or Return Due to Cancellation of Order). |
|
Reason type codes and their descriptions (for example, Urgent). |
|
A kind of Retail Transaction lookup that is mapped to a Resource to control access to that kind of Retail Transaction. |
|
The retail types (for example, Regular or Promotion). |
|
The present Status of Return Activity (for example, Delivered or Pending). |
|
Different methods of calculating the Recency, Frequency, Monetary, and Profitability (RFMP) scores. |
|
Distinct occurrences of request origins. |
|
A code denoting how the item is being treated in the line item. |
|
Indicates whether the Item is sold by weight or as a unit. |
|
Defines the security environment and procedures required for receiving, displaying and selling high priced merchandise like jewelry, certain prescription drugs, ordinance, fireworks. |
|
The different types of selling location. |
|
Selling status of the item. |
|
Different types of shipment methods. |
|
Different types of shipment priority. |
|
The Size attribute of SKU Item. |
|
The size details of the SKU. |
|
Identifies and describes the general appearance of retail items. |
|
Metadata denoting the kind of SKU item being sold (or returned) in the line item. |
|
The reason why a particular Party Status Type may be assigned to a customer |
|
The domain of classifications tracked to the roles that a Customer is fulfilling. |
|
Types of Stock Item. |
|
Journal Accounts for the accumulation of certain transactions and charges |
|
A government authority that levies sales taxes and on whose behalf the store collects these sales taxes. |
|
Different types of tax exemptions. |
|
A group of Items for which a TaxAuthority defines TaxGroupRules. |
|
Type of tenders with common characteristics (for example, check or coupon). |
|
All the tender type IDs and their parent tender type groups. |
|
Type of Tender Repository. |
|
Information about different terms like sales. |
|
Different types of theft; for example, Bank Fraud, Credit Card Fraud, Government Documents or Benefit Fraud, Employment-Related Fraud, Load Fraud, Other, Phone or Utilities Fraud. |
|
Specific designator that indicates what type of transaction that has been captured through a workstation. |
|
Inventory transfer type. |
|
Identifies and describes valid units of measure that are used throughout the model. |
|
Formulas for converting from one Unit of Measure to another. |
|
The type of value (such as time or money). |
|
Captures all the variety type (for example, Color). |
|
Classification of Vendors. |
|
Vendor rating type values. |
|
Different types of work hour. |
The Account Type entity captures types of account. The account type could be Installment Payment Account, Charge Account, Trade Account, Layaway Account, and Rental Account.
The Activity Request Type entity is distinct occurrences of activity request types (for example, Where Is My Order, general, and gift certificate lookup).
This lookup entity is used for the following entities:
Catalog Request Type Day Derived
Customer Service Request
The Address Type entity is types of addresses (or example, home, office, or warehouse).
This lookup entity is used for the following entities:
Organization Business Unit
Address Location
Customer Address
Customer Account Tender
The Analysis Duration entity is a period of time that can extend over two or more days. Valid values are: Analysis On Week Basis, Analysis On Month Basis, and Analysis On Year Basis.
The Appointment Type entity is for types of appointment (for example, recurring, urgent, or planned).
This lookup entity is used for Vendor Appointment entity.
The Authorization Method entity identifies the method used to authorize tender. Examples include: by electronic query, by sales employee visual inspection of customer card and id.
This lookup entity is used for the following entities:
The Business Unit Types are unique retailer assigned identifier for a Retail Store, Distribution Center or Administration Center that performed the transaction.
This lookup entity is used for the following entities:
The Business Unit Usage Type entity is the site usage. Possible values for the site usage are:
Store
Store within a Store (Quick Serve, Dry Cleaning, Bank, ATM, Hair Salon, Parcel Service, Wireless Provider)
Department (Pharmacy, Film, Optician, Nursery, Cosmetics, Gift Registry, Customer Service, Returns counter, Pickup Counter, Drive-through)
Kiosk (Cart, Video, Stationary)
Vending machine Warehouse
Distribution Center
Call Center
Administrative
The Card Type entity is a code denoting which kind of card was accepted. Examples include: Amex, Diners, Disc, JCB, MC, or Visa.
This lookup entity is used for Credit Debit Card Tender.
The Certificate Age Band entity is for Static Certificate age bands which are used to categorize based on age. Each age band is a client-defined range of age in days. The age of a certificate is used to determine the age band into which it falls.
This lookup entity is used for the following entities:
The Certificate Type entity contains information on certificate types like Gift Certificate or Work Certificate.
This lookup entity is used for the following entities:
The Channel Type entity is types of channel. Examples include: selling, distribution, selling and distributing.
This lookup entity is used for the following entities:
The Check In Type entity is used to lookup for different types of check in.
The Coating entity is for values of the Coating attribute of SKU Item.
The Color entity is for the Color attribute of SKU Item.
The Cost Per Unit Type entity is the unit type the owned attribute costs are assigned to for an item. Valid unit types include:
Sale unit
Pack unit
Ship unit
This lookup entity is used for Inventory Control Document Line Item entity.
The Coupon Scan entity is the barcode on a store or manufacturer coupon. The coupon scan code comprises two parts: the first is a fixed 12 character code that contains the manufacturer identification, family code, and coupon value, the second is based on Code 128 and comprises up to 20 characters which specify the manufacturers number system character, the offer code, and end of offer code. The supplementary Code 128 was introduced as a guideline in 1997.
These codes included are:
Primary Label
Secondary Label
Coupon ID
The Currency entity is the national designation and quantitative value of monetary media used as tender in the processing of the Tender Line Item entity. Examples include: US Dollar, Indian Rupee, or Japanese Yen.
This lookup entity is used for the following entities:
The Customer Occasion Type entity is for categorizations of Customer Occasion.
The Customer Order And Hold Event entity is distinct values of customer order hold events.
The Customer Pickup Type entity defines where and how a customer may pickup an item. For example, a refrigerator may have to be picked up at the shipping dock or at the retailer's warehouse.
This lookup entity is used for the following entities:
The Determination entity specifies the quantitative value of the referenced Currency media. Examples include: ten dollars (as in 10 dollar bill), fifty pounds (fifty pound note), 25 cents.
The Discount Type entity captures the various types of discount. Examples include: quantity discount or cash discount.
This lookup entity is used for Discount Line Item.
The Disposition Type entity denotes what disposition a returned item was in. Examples include: return to vendor, return to stock, or write off.
This lookup entity is used for the following entities:
The Dye entity is for values of the Dye attribute of SKU Item.
The Employee Type entity describes types of Employee. Examples include: Part Time, Contractual, or Full Time.
This lookup entity is used for the following entities:
The Entry Method entity is the method used of entering transaction data. Examples include: Entry Through Key, Entry Through Magnetic Ink Character Recognitions, Entry Through MSR, Entry Through Scanning, Entry Through Smart Card
This lookup entity is used for the Retail Transaction Line Item.
The Entry Source entity is the credit card or member card information.
The Environment Type entity defines the temperature, relative humidity, lighting and other physical or climatic environmental requirements for storing and displaying the item.
This lookup entity is used for the following entities:
The Fabric entity is for values of the Fabric attribute of SKU Item.
The Fiber entity is for values of the Fiber attribute of SKU Item.
The Hazardous Material Type entity defines the relevant hazardous material handling properties of the item. The code is provided for oil products, pesticides, swimming pool suppliers, or fertilizers (especially bomb grade).
This lookup entity is used for the following entities:
The Inventory Accounting Method defines the inventory accounting method to be used for the item. Examples include: the retail method or cost method.
This lookup entity is used for the following entities:
The type of inventory document. Examples include: Transfer In, Transfer Out, and Return to Vendor.
The type of inventory location. Examples include: Customer Service, Display, Store, and Shelf.
The Inventory Type entity is the type of Inventory. Examples include: Damaged or Customer Order.
This lookup entity is used for the following entities:
The Inventory State entity defines a state that Stock Items are kept in the retail enterprise item inventory records. Values include: On Hand, On Order, On Layaway, Damaged, and To Be Returned.
This lookup entity is used for the following entities:
The Inventory Status entity is the status of the inventory. Examples include work-in-progress, manufactured, and finished.
The Item State entity is the state of the item. Examples include: Damaged Item, Item Passed Quality Checked, Item Return from Quality Checked, or Item is Sent to Quality Checked.
This lookup entity is used for the following entities:
The Issue Type entity is how the certificate or voucher was issued by the organization business unit. Examples include: Embossed and Printed.
This lookup entity is used for Certificate entity.
The Language Type entity is the language for which the system keeps some string translations. Examples include: Dutch, English, Francais.
This lookup entity is used for Customer Order entity.
The Local Authority Type is for types of Local Authority
The Location Type entity is a code that describes what business activities and functions are performed in a specific location. For an inventory location it would be:
DISPLY for display
CUSTSVC for customer service
RETADJ for returns and adjustments
STOCKPT for stock point
RECV for receiving
This lookup entity is used for the following entities:
The Manufacture Coupon Family entity is code assigned by the manufacturer to classify product for promotion purposes. Examples include:
Raincheck Coupon
Manufacturer Coupon
Electronic Coupon.
This lookup entity is used for the Coupon Tender Line Item Tender.
The Media Type entity is a description of Media Type (for example, TV, radio, newspaper, or list)..
The Membership Type entity is for types of frequent shopper programs (for example, miles or money).
The type of miscellaneous line item. Examples include: Customer Line item and Miscellaneous fees.
The Multiple Tender Class entity is for multiple tender combinations. Examples include cash and credit card, or cash and coupon.
This lookup entity is used for the Retail Tender History.
The Order Category Type is to lookup types of Order Category. Examples include: backorder, seasonal order and Temporary Order.
This lookup entity is used for the following entities:
The Order Line Item State entity is a unique retailer assigned code denoting a potential state for a Customer Order Line Item. Examples include:
This lookup entity is used for the following entities:
The Order Type entity is a unique retailer assigned code denoting a type of Customer Order. Examples include Layaway, Order for Delivery, and Order for Pickup.
This lookup entity is used for the following entities:
The Order Source Type entity is the description of an order's source. Examples include calls center, workstation, or store.
This lookup entity is used for the following entities:
The Order State entity is a unique retailer assigned code denoting a potential state for a Customer Order. Examples include:
This lookup entity is used for the following entities:
The Order Status Type entity is the different types of order status type. Examples include
The Pay Category entity is for various pay categories present in an organization.
The Pay Type entity is for various pay types under the different categories.
The Personal ID Required Type entity is a type of personal identification required to authorize a tender; for example
Drivers license
Second credit card
Social security card
The Preference Type entity is the type of preference relevant to consumers or customers (for example, color preference).
The Price List Lookup is the basic published or advertised price, often subject to discount.
This lookup entity is used for the following entities:
The Profile Source entity is the source from which a Profile is acquired or populated (for example, a mailing list provider).
The Reason entity is codes and descriptions for explanations. Examples include:
This lookup entity is used for the following entities:
The Reason Category entity is for reason type codes and their descriptions. Examples include:
This lookup entity is used for the following entities:
The Retail Transaction Type Lookup is a kind of Retail Transaction lookup that is mapped to a Resource to control access to that kind of Retail Transaction. The Sample values include:
Sale
Return
Sale Reversal
This lookup entity is used for the following entities:
The Retail Type entity is for types of retail processing. Examples include: regular, promotion, clearance.
This lookup entity is used for the following entities:
The Return Status entity is the present Status of Return Activity. Examples include: Delivered, Pending, Item Partially.
The RFMP Method entity is for different methods of calculating the Recency, Frequency, Monetary, and Profitability (RFMP) scores.
This lookup entity is used for Customer RFMP Score.
The Request Origin Type Lookup is for distinct occurrences of request origins. Examples include: telephone, fax, and Internet.
This lookup entity is used for the following entities:
The Sale or Return Action Lookup entity is a code denoting how the item is being treated in the line item.
Possible values include:
Layaway
OrderForDelivery
PreviousLayaway
ReturnItem
SaleItem
Return
Sale
The Sale Weight Or Unit Count entity indicates whether the ITEM is sold by weight or as a unit. Examples include Item is sold by Unit and Item is sold by Weight.
This lookup entity is used for the following entities:
The Security Required Type entity defines the security environment and procedures required for receiving, displaying and selling high priced merchandise like jewelry, certain prescription drugs, ordinance, fireworks.
This lookup entity is used for the following entities:
The Selling Location Type entity is the different types of selling locations within a store. Examples include shelf, floor, and rack.
This lookup entity is used for Selling Location entity.
The Selling Status entity is the selling status of the item. Examples include active, discontinued, seasonal, to be discontinued, and held for future release.
The Shipment Method entity is different types of shipment methods. Examples include: Shipment By Air, Shipment By Sea, and Shipment By Train.
This lookup entity is used for the following entities:
The Shipment Priority entity is different types of shipment priority. Examples include: Primary, Secondary, and Tertiary.
This lookup entity is used for the following entities:
The Size entity is for values of the Size attribute of SKU Item.
The Size Type entity is the size details of the SKU. Examples include small, medium, or large.
The SKU Item Style entity identifies and describes the general appearance of retail items.
The SKU Item Type entity is metadata denoting the kind of SKU item being sold (or returned) in the line item. Possible values are: Stock, Service, Fee, Deposit, Deposit Refund, Tare, Swatch, Component, Raw, Prepared, Group Select, and Aggregate.
This lookup entity is used for the following entities:
The Status Reason entity is the reason why a particular Party Status Type may be assigned to a customer
The Status Type entity is the domain of classifications tracked to the roles that a Customer is fulfilling. Examples: A - Active, I - Inactive, P - Prospective, U- Unmarketable Customer (such as, a deceased customer).
The Stock Item Type entity is types of Stock Items.
The Store Financial Ledger Account entity is journal accounts for the accumulation of certain transactions and charges. Examples include Actual Receipts By Stores, Actual Receipts Sorted By Day(History), Actual Receipts Sorted By Received Type.
The Tax Authority entity is a government authority that levies sales taxes and on whose behalf the store collects these sales taxes. The tax authorities are:
National
State
Province
City
County
Other
This lookup entity is used for Till Tax History.
The Tax Exemptions entity is different types of tax exemptions. This lookup entity is used for the following entities:
The Taxable Group entity is a group of Items for which a TaxAuthority defines TaxGroupRules. Examples include food items and hard goods.
This lookup entity is used for the following entities:
The Tender Class entity is types of tenders with common characteristics. Examples include Check, Coupon, Credit Card, Cash, Debit Card, or Food Stamp. This lookup entity is used for the following entities:
The Tender Type entity is all of the tender type IDs and their parent tender type groups. The tender type dimension is composed of one table (TNDR_TYPE_DM) and one view (TNDR_TYPE_GRP_DM).
An example of a tender type group is credit card. Examples of tender type IDs that belong to the group are American Express, Master Card or Discover Card.
This lookup entity is used for the following entities:
The Tender Repository Class is the types of Tender Repository entities. Examples include: Safes or Tills.
This lookup entity is used for Tender Repository.
The Term Code entity is information about different terms like sales. Examples include: Supplier must be notified of any shortages within three days or Product must be Properly Checked.
This lookup entity is used for the following entities:
The Theft Type entity is different types of theft. Examples include: Bank Fraud, Credit Card Fraud, Government Documents or Benefit Fraud, Employment-Related Fraud, Load Fraud, Other, Phone or Utilities Fraud.
The Transaction Type entity is a specific designator that indicates what type of transaction that has been captured through a workstation. Sample Values are: Sale, Return, Sale Reversal, or Return Reversal. This lookup entity is used for the following entities:
The Transfer Type entity is for the inventory transfer types. Examples include: normal, book, and inter-company.
This lookup entity is used for the following entities:
The Unit Of Measure entity identifies and describes valid units of measure that are used throughout the model. Examples include: Pound, Ounce, Gallon, Gram, Kilogram, or Liter.
This lookup entity is used for the following entities:
Formulas for converting from one Unit of Measure to another.
The Value Type entity describes the type of value. Value type could be time or money.
The Variety Type entity captures all the variety type (for example, Color).
The Vendor Class entity is for classification of Vendors. Examples include: Primary, Associate, or Direct Supply.
This lookup entity is used for Vendor entity.
The Vendor Rating Type entity is for Vendor rating type values. Examples include: timeliness or based on quality.
This lookup entity is used for Rating entity.
The Work Hour Type entity is for different types of work hour. Examples include standard or overtime.
Base entities contain atomic level transaction data. Base entities could be leveraged as an Operational Data Store (ODS) and as a system of record. Data in the base entities support the derived and aggregate layers to facilitate Star and population, and act as a source for Data Mining for advanced analysis.
Table 2-22, "Base Entity Descriptions" lists and briefly describes the base entities and links to individual topics for each entity that provides information about how each entity relates to other entities.
Table 2-22 Base Entity Descriptions
Entity | Description |
---|---|
The date and count of escheated vouchers. |
|
Order placed by a Customer for merchandise or services to be provided at some future date and time. |
|
Line item component of a Customer placed order. |
|
State of a Customer Order line item being for a given period. Examples of states include pending, back order, billed, and 'booked. |
|
Retailer defined state for an Order. Possible values include Pending, Partially Delivered, Complete, and Canceled. |
|
Activity request transactions for a customer service center. |
|
Information regarding employee labor activity. |
|
Daily exchange rates for specific currencies. |
|
Record of the movement of merchandise or supply Stock Items. |
|
Detail line on an Inventory Control Document that identifies the Stock item, and unit of measure exchanged, or the freight, charges, taxes, and allowances applicable to a particular inventory control event and action. |
|
Location of SKU Items in inventory by business unit, selling location, inventory location by date. |
|
Sales information for market items obtained from external source. |
|
Order from a business unit to purchase inventory, supplies, or services from a vendor. |
|
Items, quantities and amounts included in a purchase order. |
|
State of a Purchase Order line item for a given period. |
|
State of a Purchase Order for a given period. |
|
A line item component of a RETAIL TRANSACTION that records the exchange in ownership of a merchandise item (for example, a sale or return) or the sale or refund related to a service. |
|
A detail line item of TRANSACTION that records the crediting or debiting of a CUSTOMER PROMOTIONAL ACCOUNT with points, dollars, or miles. |
|
Point of Sale Tender information by employee, tender type, business unit, day. Records of tender in and tender out. |
|
A line item component of a retail transaction that records the settlement of that transaction with an offsetting, valid tender type. |
|
A type of transaction that records the business conducted between the retail enterprise and another party involving the exchange in ownership or accountability, or both, for merchandise or tender, or both, or involving the exchange of tender for services. |
|
Employee Associate involved in serving the customer who purchased the merchandise or services identified in the Retail Transaction. |
|
A detail line item of a miscellaneous transaction. |
|
A detail line item of a Retail Transaction that records the business conducted between the retail store and another party involving the exchange in ownership or accountability, or both, for merchandise or tender, or both, or involving the exchange of tender for services. |
|
Weekly sales forecast Information at given levels of Item, and organization hierarchies. |
|
Weekly sales plan including Returns, Cost of Sales, Promotion, Clearance, at given levels of Item and Organization hierarchies. |
|
Holds details of tender change in a transaction. |
|
A collection of monetary and operational totals used to track the activity volume of a till between Till Settlement Transactions. |
|
A collection of tax totals for a tax authority by till for a tender reconciliation period. |
|
A collection of tender type accumulators by till tender accumulation period used to support till tender accountability. |
|
Cost change information for a SKU item, vendor, and business unit combination on a given day. |
The Certificate Escheated Day entity is the date and count of escheated vouchers.
The Customer Order entity is an order placed by a Customer for merchandise or services to be provided at some future date and time.
Figure 2-12, "Customer Order Entity Relationships" shows how this entity relates to other entities.
Figure 2-12 Customer Order Entity Relationships
The Customer Order Line Item entity is the line item components of a Customer placed order.
Figure 2-13, "Customer Order Line Item Entity Relationships" shows how this entity relates to other entities.
Figure 2-13 Customer Order Line Item Entity Relationships
The Customer Order Line Item State Assignment entity is the state of a Customer Order line item being for a given period. Examples of states include pending, back order, billed, and 'booked.
Figure 2-14, "Customer Order Line Item State Assignment Entity Relationships" shows how this entity relates to other entities.
Figure 2-14 Customer Order Line Item State Assignment Entity Relationships
The Customer Order State entity is the retailer defined state for an Order. Possible values include Pending, Partially Delivered, Complete, and Canceled.
Figure 2-15, "Customer Order State Entity Relationships" shows how this entity relates to other entities.
Figure 2-15 Customer Order State Entity Relationships
The Customer Service Request entity is activity request transactions for a customer service center.
Figure 2-16, "Customer Service Request Entity Relationships" shows how this entity relates to other entities.
Figure 2-16 Customer Service Request Entity Relationships
The Employee Labor entity is information regarding employee labor activity.
Figure 2-17, "Employee Labor Entity Relationships" shows how this entity relates to other entities.
Figure 2-17 Employee Labor Entity Relationships
The Exchange Rate Currency Day entity is the daily exchange rates for specific currencies.
Figure 2-17, "Employee Labor Entity Relationships" shows how this entity relates to other entities.
The Inventory Control Document entity is a record of the movement of merchandise or supply Stock Items.
Figure 2-19, "Inventory Control Document Entity Relationships" shows how this entity relates to other entities.
Figure 2-19 Inventory Control Document Entity Relationships
The Inventory Control Document Line Item entity is a detail line on an Inventory Control Document that identifies the Stock item, and unit of measure exchanged, or the freight, charges, taxes, and allowances applicable to a particular inventory control event and action.
Figure 2-20, "Inventory Control Document Line Item Entity Relationships" shows how this entity relates to other entities.
Figure 2-20 Inventory Control Document Line Item Entity Relationships
The Inventory Item State entity is the location of SKU Items in inventory by business unit, selling location, inventory location by date.
Figure 2-21, "Inventory Item State Entity Relationships" shows how this entity relates to other entities.
Figure 2-21 Inventory Item State Entity Relationships
The Market Sales Item Week entity is sales information for market items obtained from external source.
Figure 2-22, "Market Sales Item Week Entity Relationships" shows how this entity relates to other entities.
Figure 2-22 Market Sales Item Week Entity Relationships
The Purchase Order entity is an order from a business unit to purchase inventory, supplies, or services from a vendor.
Figure 2-23, "Purchase Order Entity Relationships" shows how this entity relates to other entities.
Figure 2-23 Purchase Order Entity Relationships
The Purchase Order Line Item entity is items, quantities and amounts included in a purchase order.
Figure 2-24, "Purchase Order Line Item Entity Relationships" shows how this entity relates to other entities.
Figure 2-24 Purchase Order Line Item Entity Relationships
The Purchase Order Line Item entity is the state of a Purchase Order line item for a given period.
Figure 2-25, "Purchase Order Line Item State Entity Relationships" shows how this entity relates to other entities.
Figure 2-25 Purchase Order Line Item State Entity Relationships
The Purchase Order State entity is the state of a Purchase Order for a given period.
Figure 2-26, "Purchase Order State Entity Relationships" shows how this entity relates to other entities.
Figure 2-26 Purchase Order State Entity Relationships
The Retail Sale Return Line Item entity is a line item component of a Retail Transaction that records the exchange in ownership of a merchandise item (for example, a sale or return) or the sale or refund related to a service.
Figure 2-27, "Retail Sale Return Line Item Entity Relationships" shows how this entity relates to other entities.
Figure 2-27 Retail Sale Return Line Item Entity Relationships
A Retail Sale Return Promotion Line Item entity is a detail line item of TRANSACTION that records the crediting or debiting of a CUSTOMER PROMOTIONAL ACCOUNT with points, dollars, or miles.
Figure 2-28, "Retail Sale Return Promotion Line Item Entity Relationships" shows how this entity relates to other entities.
Figure 2-28 Retail Sale Return Promotion Line Item Entity Relationships
A Retail Tender History entity is a Point of Sale Tender information by employee, tender type, business unit, day. This entity keeps records of tender in and tender out.
Figure 2-29, "Retail Tender History Entity Relationships" shows how this entity relates to other entities.
Figure 2-29 Retail Tender History Entity Relationships
A Retail Tender Line Item entity is a line item component of a retail transaction that records the settlement of that transaction with an offsetting, valid tender type.
Figure 2-30, "Retail Tender Line Item Entity Relationships" shows how this entity relates to other entities.
Figure 2-30 Retail Tender Line Item Entity Relationships
A Retail Transaction entity is type of transaction that records the business conducted between the retail enterprise and another party involving the exchange in ownership or accountability, or both, for merchandise or tender, or both, or involving the exchange of tender for services.
Figure 2-31, "Retail Transaction Entity Relationships" shows how this entity relates to other entities.
Figure 2-31 Retail Transaction Entity Relationships
A Retail Transaction Associate Assignment is an Employee Associate involved in serving the customer who purchased the merchandise or services identified in the Retail Transaction.
Figure 2-32, "Retail Transaction Associate Assignment Entity Relationships" shows how this entity relates to other entities.
Figure 2-32 Retail Transaction Associate Assignment Entity Relationships
A Retail Transaction Miscellaneous Line Item is a detail line item of a Retail Transaction. This entity records the business conducted between the retail store and another party involving the exchange in ownershi8p or accountability for merchandise or tender or involving the exchange of tender for services.
A Retail Transaction Line Item entity is a detail line item of a Retail Transaction that records the business conducted between the retail store and another party involving the exchange in ownership or accountability, or both, for merchandise or tender, or both, or involving the exchange of tender for services.
Figure 2-33, "Retail Transaction Line Item Entity Relationships" shows how this entity relates to other entities.
Figure 2-33 Retail Transaction Line Item Entity Relationships
A Sales Forecast Item Organization Hierarchy entity is weekly sales forecast Information at given levels of Item, and organization hierarchies.
Figure 2-34, "Sales Forecast Item Organization Hierarchy Week Entity Relationships" shows how this entity relates to other entities.
Figure 2-34 Sales Forecast Item Organization Hierarchy Week Entity Relationships
A Sales Plan Organization Hierarchy Week entity is weekly sales plan including Returns, Cost of Sales, Promotion, Clearance, at given levels of Item and Organization hierarchies.
Figure 2-35, "Sales Plan Item Organization Hierarchy Week Entity Relationships" shows how this entity relates to other entities.
Figure 2-35 Sales Plan Item Organization Hierarchy Week Entity Relationships
A Tender Change Line Item holds details of tender change in a transaction.
A Till History entity is a collection of monetary and operational totals used to track the activity volume of a till between Till Settlement Transactions.
Figure 2-36, "Till History Entity Relationships" shows how this entity relates to other entities.
Figure 2-36 Till History Entity Relationships
A Till Tax History entity is a collection of tax totals for a tax authority by till for a tender reconciliation period.
Figure 2-37, "Till Tax History Entity Relationships" shows how this entity relates to other entities.
Figure 2-37 Till Tax History Entity Relationships
A Till Tender History entity is a collection of tender type accumulators by till tender accumulation period used to support till tender accountability.
Figure 2-38, "Till Tender History Entity Relationships" shows how this entity relates to other entities.
Figure 2-38 Till Tender History Entity Relationships
A Vendor SKU Cost Profit Day entity is cost change information for a SKU item, vendor, and business unit combination on a given day.
Figure 2-39, "Vendor SKU Cost Profit Day Entity Relationships" shows how this entity relates to other entities.
Figure 2-39 Vendor SKU Cost Profit Day Entity Relationships
Derived entities contain information drawn from one or more base entities, and contains denormalized or transposed data. Granularity is partially aggregated, typically at day level, but in some cases at quarter hour level or partially aggregated transactional data from the base entities.
Table 2-23, "Derived Entity Descriptions" lists and describes the derived entities and links to individual topics for each entity that provides information about how each entity relates to other entities.
Table 2-23 Derived Entity Descriptions
Entity | Description |
---|---|
This table is populated from retail transaction line item sub type certificate for issue and retail tender line item sub type certificate tender for redemption. |
|
Cross reference of employee transactions by customer. |
|
Daily record of customer orders by SKU item for a business unit. |
|
State of a Customer Order Line Item State; for example, pick, backorder, altered, or return for a given time period. |
|
Recency, Frequency, Monetary, and Profitability Value Score of a customer, by business unit. |
|
SKU item purchases and returns by customer for a business unit. |
|
Summary of Customer Service Requests in which a catalogue was requested by a customer or prospect by day and business unit. |
|
Inventory adjustment information at the item-business unit-day-reason level. |
|
Details of the items marked as nonsellable at day level. |
|
Status and value of Inventory; for example: stock on hand, on order for a business unit, SKU item and day. |
|
Point of Sale Tender transactions by minute and tender type for a workstation in a Business Unit. |
|
Point of Sale Retail Transactions by minute and tender type for a workstation in a Business Unit. |
|
Summary of SKU Item sales and returns by day, business unit and optionally by promotional campaign. |
|
Summary of allocated space by item. The table is updated from inventory item state. 'Max' and 'Min' are populated from the recursive selling location. |
|
Describes the actions that happen during a drawer insert which is operationally associated with a Workstation and, optionally, an Employee. A Till Derived entity is used to keep cash and other Tender collected through Retail Transactions and used to make change. |
The Certification Activity Transaction Derived entity is a table that is populated from retail transaction line item sub type certificate for issue and retail tender line item sub type certificate tender for redemption.
Figure 2-40, "Certificate Activity Transaction Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-40 Certificate Activity Transaction Derived Entity Relationships
The Customer Employee Relationship Day entity is a cross reference of employee transactions by customer.
Figure 2-41, "Customer Employee Relationship Day Entity Relationships" shows how this entity relates to other entities.
Figure 2-41 Customer Employee Relationship Day Entity Relationships
The Customer Order Item Day Derived entity is a daily record of customer orders by SKU item for a business unit.
Figure 2-42, "Customer Order Item Day Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-42 Customer Order Item Day Derived Entity Relationships
The Customer Order Line Item State Derived entity is the state of a Customer Order Line Item State; for example, pick, backorder, altered, or return for a given time period.
Figure 2-43, "Customer Order Line Item State Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-43 Customer Order Line Item State Derived Entity Relationships
The Customer RFMP Score entity is the Recency, Frequency, Monetary, and Profitability Value Score of a customer, by business unit.
Figure 2-44, "Customer RFMP Score Entity Relationships" shows how this entity relates to other entities.
Figure 2-44 Customer RFMP Score Entity Relationships
The Customer SKU Sale Return Day Derived entity is the SKU item purchases and returns by customer for a business unit.
Figure 2-45, "Customer SKU Sale Return Day Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-45 Customer SKU Sale Return Day Derived Entity Relationships
The Catalog Request by Day Derived entity is a summary of Customer Service Requests in which a catalogue was requested by a customer or prospect by day and business unit.
Figure 2-46, "Catalog Request by Day Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-46 Catalog Request by Day Derived Entity Relationships
The Inventory Adjustment by Item Day Derived entity is inventory adjustment information at the item-business unit-day-reason level.
Figure 2-47, "Inventory Adjustment by Item Day Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-47 Inventory Adjustment by Item Day Derived Entity Relationships
The Inventory Unavailable Item Day entity is the details of the items marked as nonsellable at day level.
Figure 2-48, "Inventory Unavailable Item Day Entity Relationships" shows how this entity relates to other entities.
Figure 2-48 Inventory Unavailable Item Day Entity Relationships
The Inventory Position by Item Day Derived entity is the status and value of Inventory; for example: stock on hand, on order for a business unit, SKU item and day.
Figure 2-49, "Inventory Position by Item Day Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-49 Inventory Position by Item Day Derived Entity Relationships
The POS Tender Flow entity is the Point of Sale Tender transactions by minute and tender type for a workstation in a Business Unit.
Figure 2-50, "POS Tender Flow Entity Relationships" shows how this entity relates to other entities.
Figure 2-50 POS Tender Flow Entity Relationships
The POS Transaction Flow entity is Point of Sale Retail Transactions by minute and tender type for a workstation in a Business Unit.
Figure 2-51, "POS Transaction Flow Entity Relationships" shows how this entity relates to other entities.
Figure 2-51 POS Transaction Flow Entity Relationships
The Retail Sale Return Item Day Derived entity is a summary of SKU Item sales and returns by day, business unit and optionally by promotional campaign.
Figure 2-52, "Retail Sale Return Item Day Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-52 Retail Sale Return Item Day Derived Entity Relationships
The Space Utilization Item Day Derived entity is a summary of allocated space by item. The table is updated from inventory item state. 'Max' and 'Min' are populated from the recursive selling location.
Figure 2-53, "Space Utilization Item Day Derived Entity Relationships" shows how this entity relates to other entities.
Figure 2-53 Space Utilization Item Day Derived Entity Relationships
The Till Revived entity describes the actions that happen during a drawer insert which is operationally associated with a Workstation and, optionally, an Employee. A Till Derived entity is used to keep cash and other Tender collected through Retail Transactions and used to make change.
Figure 2-54 shows how this entity relates to other entities.
Figure 2-54 Till Derived Entity Relationships
Aggregate entities hold data rolled up from the Base or Derived entities at different levels across different dimensional hierarchies.
Table 2-24, "Aggregate Entity Descriptions" lists and briefly describes the aggregate entities and links to individual topics for each entity that provides a more complete description and information about how that entity relates to other entities.
Table 2-24 Aggregate Entity Descriptions
Entity Name | Brief Description |
---|---|
Record of a carrier's delivery performance during a given week. |
|
Daily summary of issued and redeemed certificates or vouchers. |
|
A weekly summary of issued and redeemed certificates or vouchers. |
|
Monthly cross reference of employee transactions by customer. |
|
Monthly record of SKU item purchases and returns by a customer and handled by an employee for a business unit. |
|
Daily record of customer orders by department. |
|
Monthly record of customer orders by department. |
|
Monthly record of customer orders by SKU item. |
|
Weekly record of customer orders by SKU item. |
|
Daily record of customer orders by item subclass. |
|
Monthly record of customer orders by item subclass. |
|
Weekly record of customer orders by item subclass. |
|
Weekly record of the budgeted quantity and cost of the inventory. |
|
Weekly records of SKU item Location in inventory by business unit, selling location, and inventory location. |
|
Daily status and value of Inventory; for example, stock on hand, on order for a business unit and SKU Item. |
|
Weekly status and value of Inventory; for example, stock on hand, on order for a business unit and SKU Item. |
|
Weekly status of Inventory; for example, stock on hand, on order for a business unit and SKU Item. |
|
Daily status and value of Inventory; for example, stock on hand, on order for a business unit and item subclass. |
|
Weekly status and value of Inventory; for example, stock on hand, on order for a business unit and Item Subclass. |
|
Daily record of inventory receipts by Item and business unit. |
|
Weekly record of inventory receipts by Item and business unit. |
|
Daily record of inventory receipts by subclass and business unit. |
|
Weekly record of inventory receipts by subclass and business unit. |
|
Daily record of inventory transfers at the Item, to business unit, from business unit, and transfer type. |
|
Weekly record of inventory transfers at the Item, to business unit, from business unit, and transfer type. |
|
Daily record of Inventory transfer details by item subclass. |
|
Weekly record of Inventory transfer details by item subclass. |
|
Timeliness, quantity, quality control vendor compliance information at the SKU item-business unit-vendor-season level. |
|
Details of weekly sales total of market items by department. |
|
Weekly record of cost contribution of items in a promotion. |
|
Weekly record of sales and margin of items in promotion. |
|
Daily summary markdown details by department. |
|
Weekly summary markdown details by department. |
|
Daily summary of markdown details by Item. |
|
Weekly summary of markdown details by Item. |
|
Daily summary of SKU Item sales and returns across organization hierarchy optionally by promotional campaign. |
|
Daily summary of sales and returns by department, optionally by promotional campaign. |
|
Weekly summary of sales and returns by department, optionally by promotional campaign. |
|
Monthly summary of SKU Item sales and returns for a business unit optionally by promotional campaign. |
|
Weekly summary of SKU Item sales and returns for a business unit, optionally by promotional campaign. |
|
Daily summary sales and returns for a business unit by item subclass, optionally by promotional campaign. |
|
Monthly summary of sales and returns for a business unit by item subclass, optionally by promotional campaign. |
|
Weekly summary of Item subclass sales and returns for a business unit by item subclass, optionally by promotional campaign. |
|
Summary of POS transaction details; for example, gross positive, tender loans, tender pickups, returns, by week for a given employee or workstation. |
|
Daily Summary of allocated space by item. Aggregate of Space Utilization Item Day Derived. |
|
Monthly inventory values; for example, Begin and End Stock on Hand, Cost amounts, and Markdown Values at Subclass and business unit. |
|
Weekly inventory values; for example, Begin and End Stock on Hand, Cost amounts, and Markdown Values at item Subclass and business unit. |
|
A collection of monetary and operational totals used to track the activity volume of a till between Till Settlement Transactions by workstation and week. |
|
A collection of tender type accumulators by till tender accumulation period and employee used to support till tender accountability. |
|
Daily Summary of quantities of SKU item available by vendor and item. |
|
Weekly record of timeliness, quantity, quality control vendor compliance information by item, business unit, shipment, and Purchase Order. |
|
Weekly record of vendor compliance like, timeliness, quantity, quality control vendor compliance information by business unit shipment and Purchase Order. |
|
Daily cross-reference of vendor contract details by SKU Item. |
The Carrier Compliance Week Aggr entity is a record of a carrier's delivery performance during a given week. Delivery performance is measured by how many times they were late, early or on-time, and how late or early they were in hours or days.
Figure 2-55 shows how this entity relates to other entities.
Figure 2-55 Carrier Compliance Week Aggr Entity Relationships
The Certificate Activity Day Aggr entity is a daily summary of issued and redeemed certificates or vouchers. The table contains counts, amounts, and age bands for issued and redeemed vouchers for change of voucher status. Age bands are derived from the Certificate Age Band table where the age of a voucher fails within the limits of the age band. Aggregation is at day level.
Figure 2-56 shows how this entity relates to other entities.
Figure 2-56 Certificate Activity Day Aggr Entity Relationships
The Certificate Activity Week Aggr entity is a weekly summary of issued and redeemed certificates or vouchers. The table contains counts, amounts, and age bands for issued and redeemed vouchers for change of voucher status. Age bands are derived from the Certificate Age Band table where the age of a voucher fails within the limits of the age band. Aggregation is at week level.
Figure 2-57 shows how this entity relates to other entities.
Figure 2-57 Certificate Activity Week Aggr Entity Relationships
The Customer Employee Relationship Month Aggr entity is a monthly cross reference of employee transactions by customer. This entity is an aggregate of Customer Employee Relationship Day.
Figure 2-58 shows how this entity relates to other entities.
Figure 2-58 Customer Employee Relationship Month Aggr Entity Relationships
The Customer Employee Sale Return Week Aggr entity is a monthly record of SKU item purchases and returns by a customer and handled by an employee for a business unit. This entity is an aggregate of Customer SKU Sale Return Day Derived.
Figure 2-59 shows how this entity relates to other entities.
Figure 2-59 Customer Employee Sale Return Week Aggr Entity Relationships
The Customer Order Department Day Aggr entity is a daily record of customer orders by department.
This entity is an aggregate of Customer order Item Day Derived.
Figure 2-60 shows how this entity relates to other entities.
Figure 2-60 Customer Order Department Day Aggr Entity Relationships
The Customer Order Department Month Aggr entity is a monthly record of customer orders by department.
This entity is an aggregate of Customer order Item Day.
Figure 2-61 shows how this entity relates to other entities.
Figure 2-61 Customer Order Department Month Aggr Entity Relationships
The Customer Order Item Month Aggr entity is a monthly record of customer orders by SKU item.
This entity is an aggregate of Customer order Item Day
Figure 2-62 shows how this entity relates to other entities.
Figure 2-62 Customer Order Item Month Aggr Entity Relationships
The Customer Order Item Week Aggr entity is a weekly record of customer orders by SKU item.
This entity is an aggregate of Customer order Item Day.
Figure 2-63 shows how this entity relates to other entities.
Figure 2-63 Customer Order Item Week Aggr Entity Relationship
The Customer Order Subclass Day Aggr entity is a daily record of customer orders by item subclass.
This entity is an aggregate of Customer order Item Day.
Figure 2-64 shows how this entity relates to other entities.
Figure 2-64 Customer Order Subclass Day Aggr Entity Relationship
The Customer Order Subclass Month Aggr entity is a monthly record of customer orders by item subclass.
This entity is an aggregate of Customer order Item Day.
Figure 2-65 shows how this entity relates to other entities.
Figure 2-65 Customer Order Subclass Month Aggr Entity Relationship
The Customer Order Subclass Week Aggr entity is a weekly record of customer orders by item subclass.
This entity is an aggregate of Customer order Item Day.
Figure 2-66 shows how this entity relates to other entities.
Figure 2-66 Customer Order Subclass Week Aggr Entity Relationships
The Inventory Budget By Week Aggr entity is a weekly record of the budgeted quantity and cost of the inventory
Figure 2-67 shows how this entity relates to other entities.
Figure 2-67 Inventory Budget By Week Aggr Entity Relationships
The Inventory Item State History Week entity is a weekly records of SKU item Location in inventory by business unit, selling location, and inventory location.
This entity is an aggregate of Inventory Item State.
Figure 2-68 shows how this entity relates to other entities.
Figure 2-68 Inventory Item State History Week Entity Relationships
The Inventory Position By Department Day Aggr entity is the daily status and value of Inventory; for example, stock on hand, on order for a business unit and SKU Item.
This entity is an aggregate of Inventory Position by Item Day Derived.
Figure 2-69 shows how this entity relates to other entities.
Figure 2-69 Inventory Position By Department Day Aggr Entity Relationships
The Inventory Position By Department Week Aggr is the weekly status and value of Inventory; for example, stock on hand, on order for a business unit and SKU Item.
This entity is an aggregate of Inventory Position by Item Day Derived.
Figure 2-70 shows how this entity relates to other entities.
Figure 2-70 Inventory Position By Department Week Aggr Entity Relationships
The Inventory Position By Item Week Aggr entity is the weekly status of Inventory; for example, stock on hand, on order for a business unit and SKU Item.
This entity is an aggregate of Inventory Position by Item Day Derived.
Figure 2-71 shows how this entity relates to other entities.
Figure 2-71 Inventory Position By Item Week Aggr Entity Relationships
The Inventory Position Subclass Day Aggr entity is the daily status and value of Inventory; for example, stock on hand, on order for a business unit and item subclass.
This entity is an aggregate of Inventory Position by Item Day Derived.
Figure 2-72 shows how this entity relates to other entities.
Figure 2-72 Inventory Position Subclass Day Aggr Entity Relationships
The Inventory Position Subclass Week Aggr entity is the weekly status and value of Inventory; for example, stock on hand, on order for a business unit and Item Subclass.
This entity is an aggregate of Inventory Position by Item Day Derived
Figure 2-73 shows how this entity relates to other entities.
Figure 2-73 Inventory Position Subclass Week Aggr Entity Relationships
The Inventory Receipt By Item Day Aggr entity is the daily record of inventory receipts by Item and business unit.
Figure 2-74 shows how this entity relates to other entities.
Figure 2-74 Inventory Receipt By Item Day Aggr Entity Relationships
The Inventory Receipt By Item Week Aggr entity is the weekly record of inventory receipts by Item and business unit.
This entity is an aggregate of Inventory Receipt by Item Day Aggr.
Figure 2-75 shows how this entity relates to other entities.
Figure 2-75 Inventory Receipt By Item Week Aggr Entity Relationships
The Inventory Receipt By Subclass Day Aggr entity is the daily record of inventory receipts by subclass and business unit.
This entity is an aggregate of Inventory Receipt by Item Day Aggr.
Figure 2-76 shows how this entity relates to other entities.
Figure 2-76 Inventory Receipt By Subclass Day Aggr Entity Relationships
The Inventory Receipt By Subclass Week Aggr is the weekly record of inventory receipts by subclass and business unit.
This entity is an aggregate of Inventory Receipt by Item Day Aggr.
Figure 2-77 shows how this entity relates to other entities.
Figure 2-77 Inventory Receipt By Subclass Week Aggr Entity Relationships
The Inventory Transfer By Item Day Aggr entity is the daily record of inventory transfers at the Item, to business unit, from business unit, and transfer type.
Figure 2-78 shows how this entity relates to other entities.
Figure 2-78 Inventory Transfer By Item Day Aggr Entity Relationships
The Inventory Transfer By Item Week Aggr entity is the weekly record of inventory transfers at the Item, to business unit, from business unit, and transfer type.
This entity is an aggregate of Inventory Transfer by Item Day Aggr.
Figure 2-79 shows how this entity relates to other entities.
Figure 2-79 Inventory Transfer By Item Week Aggr
The Inventory Transfer By Subclass Day Aggr entity is the daily record of Inventory transfer details by item subclass.
This entity is an aggregate of Inventory Transfer by Item Day Aggr.
Figure 2-80 shows how this entity relates to other entities.
Figure 2-80 Inventory Transfer By Subclass Day Aggr
The Inventory Transfer By Subclass Week Aggr is the weekly record of Inventory transfer details by item subclass.
This entity is an aggregate of Inventory Transfer by Item Day Aggr.
Figure 2-81 shows how this entity relates to other entities.
Figure 2-81 Inventory Transfer By Subclass Week Aggr Entity Relationships
The Inventory Vendor Compliance Aggr entity is the timeliness, quantity, quality control vendor compliance information at the SKU item-business unit-vendor-season level.
Figure 2-82 shows how this entity relates to other entities.
Figure 2-82 Inventory Vendor Compliance Aggr Entity Relationships
The Market Sales Department Week Aggr entity is the details of weekly sales total of market items by department.
This entity is an aggregate of Market Sale s Item Week.
Figure 2-83 shows how this entity relates to other entities.
Figure 2-83 Market Sales Department Week Aggr Entity Relationships
The Promotion Cost Contribution Week Aggr entity is the weekly record of cost contribution of items in a promotion.
Figure 2-84 shows how this entity relates to other entities.
Figure 2-84 Promotion Cost Contribution Week Aggr Entity Relationships
The Promotion Sales Margin Week Aggr entity is the weekly record of sales and margin of items in promotion.
Figure 2-85 shows how this entity relates to other entities.
Figure 2-85 Promotion Sales Margin Week Aggr Entity Relationships
The Retail Markdown Department Day Aggr entity is the daily summary markdown details by department.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-86 shows how this entity relates to other entities.
Figure 2-86 Retail Markdown Department Day Aggr Entity Relationships
The Retail Markdown Department Week Aggr entity is the weekly summary markdown details by department.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-87 shows how this entity relates to other entities.
Figure 2-87 Retail Markdown Department Week Aggr Entity Relationships
The Retail Markdown Item Day Aggr entity is the daily summary of markdown details by Item.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-88 shows how this entity relates to other entities.
Figure 2-88 Retail Markdown Item Day Aggr Entity Relationships
The Retail Markdown Item Week Aggr entity is the weekly summary of markdown details by Item.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-89 shows how this entity relates to other entities.
Figure 2-89 Retail Markdown Item Week Aggr Entity Relationships
The Retail Sale Return Organization Hierarchy Day Aggr entity is the daily summary of SKU Item sales and returns across organization hierarchy optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-90 shows how this entity relates to other entities.
Figure 2-90 Retail Sale Return Organization Hierarchy Day Aggr Entity Relationships
The Retail Sale Return Department Day Aggr entity is the daily summary of sales and returns by department, optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-91 shows how this entity relates to other entities.
Figure 2-91 Retail Sale Return Department Day Aggr Entity Relationships
The Retail Sale Return Department Week Aggr entity is the weekly summary of sales and returns by department, optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day.
Figure 2-92 shows how this entity relates to other entities.
Figure 2-92 Retail Sale Return Department Week Aggr Entity Relationships
The Retail Sale Return Department Month Aggr entity is the monthly summary of SKU Item sales and returns for a business unit optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day.
Figure 2-93 shows how this entity relates to other entities.
Figure 2-93 Retail Sale Return Department Month Aggr Entity Relationships
The Retail Sale Return Item Week Aggr is the weekly summary of SKU Item sales and returns for a business unit, optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-94 shows how this entity relates to other entities.
Figure 2-94 Retail Sale Return Item Week Aggr Entity Relationships
The Retail Sale Return Subclass Day Aggr entity is the daily summary sales and returns for a business unit by item subclass, optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day derived.
Figure 2-95 shows how this entity relates to other entities.
Figure 2-95 Retail Sale Return Subclass Day Aggr Entity Relationships
The Retail Sale Return Subclass Month Aggr entity is the monthly summary of sales and returns for a business unit by item subclass, optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-96 shows how this entity relates to other entities.
Figure 2-96 Retail Sale Return Subclass Month Aggr Entity Relationships
The Retail Sale Return Subclass Week Aggr entity is the weekly summary of Item subclass sales and returns for a business unit by item subclass, optionally by promotional campaign.
This entity is an aggregate of Retail Sale Return Item Day Derived.
Figure 2-97 shows how this entity relates to other entities.
Figure 2-97 Retail Sale Return Subclass Week Aggr Entity Relationships
The Retail Transaction Emp Workstation Aggr entity is a summary of POS transaction details; for example, gross positive, tender loans, tender pickups, returns, by week for a given employee or workstation.
Figure 2-98 shows how this entity relates to other entities.
Figure 2-98 Retail Transaction Emp Workstation Aggr
The Space Utilization Department Day Aggr entity is a daily Summary of allocated space by item.
This entity is an aggregate of Space Utilization Item Day Derived.
Figure 2-99 shows how this entity relates to other entities.
Figure 2-99 Space Utilization Department Day Aggr Entity Relationships
The Stock Ledger By Subclass Month Aggr entity is the monthly inventory values (for example, Begin and End Stock on Hand, Cost amounts, and Markdown Values at Subclass and business unit).
Figure 2-100 shows how this entity relates to other entities.
Figure 2-100 Stock Ledger By Subclass Month Aggr Entity Relationships
The Stock Ledger By Subclass Week Aggr entity is the weekly inventory values (for example, Begin and End Stock on Hand, Cost amounts, and Markdown Values at item Subclass and business unit).
Figure 2-101 shows how this entity relates to other entities.
Figure 2-101 Stock Ledger By Subclass Week Aggr Entity Relationships
The Till History Workstation Aggr entity is a collection of monetary and operational totals used to track the activity volume of a till between Till Settlement Transactions by workstation and week.
This entity is an aggregate of Till History.
Figure 2-102 shows how this entity relates to other entities.
Figure 2-102 Till History Workstation Aggr Entity Relationships
The Till Tender History Employee Aggr entity is a
Figure 2-103 shows how this entity relates to other entities.
Figure 2-103 Till Tender History Employee Aggr Entity Relationships
The Vendor Availability Item Day Aggr entity is a daily Summary of quantities of SKU item available by vendor and item.
Figure 2-104 shows how this entity relates to other entities.
Figure 2-104 Vendor Availability Item Day Aggr Entity Relationships
The Vendor Compliance Item Week entity is a weekly record of timeliness, quantity, quality control vendor compliance information by item, business unit, shipment, and Purchase Order.
Figure 2-105 shows how this entity relates to other entities.
Figure 2-105 Vendor Compliance Item Week Entity Relationships
The Vendor Compliance Week Aggr entity is a weekly record of vendor compliance like, timeliness, quantity, quality control vendor compliance information by business unit shipment and Purchase Order.
Figure 2-106 shows how this entity relates to other entities.
Figure 2-106 Vendor Compliance Week Aggr Entity Relationships
The Vendor Contract Item Day Aggr entity is the daily cross-reference of vendor contract details by SKU Item.
Figure 2-107 Bshows how this entity relates to other entities.
Figure 2-107 Vendor Contract Item Day Aggr Entity Relationships