5 Dimensions and Attributes
Retail Insights dimensions and attributes represent the structure and activities of a retail organization and make measurement possible. Data is stored at low levels to allow maximum flexibility in reporting. Dimensions and their attributes allow you to summarize this information at higher levels where it is needed to support business decision-making. For example, the Sales fact table holds data at the location, item, and day level. The time, product, and organization dimensions allow you to summarize this data at any level at which it is needed.
Note:
This chapter contains selective lists of dimensions and attributes. See Reporting on Oracle Analytics Repository Objects for information about producing comprehensive listings of Oracle Analytics repository objects.
Business Calendar
The business calendar (fiscal calendar) is a dimension based on a retailer's calendar and is not aligned with the Gregorian/solar calendar. It is used in place of the Gregorian calendar to eliminate discrepancies in the number of days per month, as well as number of weekend days per month. The business calendar is sometimes just called the time calendar.
The business calendar can be based on a variation of the 4-5-4 calendar or the 13-period calendar. Both of these types of calendars allocate exactly seven days to every week, unlike the Gregorian calendar. Most facts are qualified by a calendar attribute.
The following is the hierarchy of the Business Calendar dimension.
![Business calendar dimension hierarchy Business calendar dimension hierarchy](img/business_calendar_hier.png)
Table 5-1 lists the attributes of the Business Calendar dimension.
Table 5-1 Business Calendar Dimension Attributes
Attribute | Definition |
---|---|
Fiscal Period |
The period of time, generally a month, reflected in financial statements. |
Fiscal Year Number |
Represents the period of year which company uses for calculating its annual fiscal statement. |
Fiscal Year |
Represents the period of year which company uses for calculating its annual fiscal statement. |
Fiscal Year Start Date |
Represents the start date of fiscal year for the company. |
Fiscal Year End Date |
Represents the end date of fiscal year for the company |
Fiscal Half Year |
Fiscal half-year period name. |
Fiscal Quarter |
Represents the fiscal quarter for the company |
Fiscal Period Start Date |
Represents the start date of fiscal period for the company. |
Fiscal Period End Date |
Represents the end date of fiscal period for the company. |
Fiscal Week |
Represents the fiscal week for the company. |
Fiscal Week Start Date |
Represents the start date of fiscal week for the company. |
Fiscal Week End Date |
Represents the end date of fiscal week for the company. |
Fiscal Day Name |
Fiscal Day Name |
Fiscal Date |
Represents the fiscal date. |
4-5-4 Calendar
The 4-5-4 calendar is the default calendar. The calendar can be implemented as 4-5-4, 4-4-5, or 5-4-4, depending upon your needs. In addition, you determine the day of the week on which each week begins and ends. Every quarter contains 13 full weeks. Quarters have two 4-week months and one 5-week month.
This calendar also has special handling of 53-week years, which can occur due to each fiscal year being shorter than a Gregorian year. The 53rd week of such a year can be placed into a 4-week or 5-week period depending on the business processes being followed, resulting in a 5 or 6-week period, respectively. Choosing where to place the extra week is an implementation decision.
Because fiscal periods normally have the same number of days/weeks from year to year, Retail Insights provides additional calendar interfaces for defining a mapping between the current year and last year, known as "shifted" and "unshifted" calendars. In an unshifted calendar, the first 52 weeks of each year are aligned with each other, and the 53rd week is left out of LY comparisons. In a shifted calendar, the 53-week year is restated for LY comparisons, such that weeks 1 through 52 of the following year align with weeks 2 to 53 of the 53-week year. Week 1 of the 53-week year can be used as the LY for Week 53, or it can be left out, depending on the configuration specified.
13-Period Calendar
A 13-period calendar year is divided into 13 periods of four weeks (28 days). Every fifth or sixth year, there are 53 weeks. The calendar has a 28-year cycle of 6 years, 5 years, 6 years, 6 years, and 5 years. The 13-period calendar hierarchy is as follows.
![Thirteen period calendar hierarchy Thirteen period calendar hierarchy](img/thirteen_period_hier.png)
Gregorian Calendar
The Gregorian calendar is a solar calendar that is based on the length of the earth's revolution around the sun. The Gregorian calendar is divided into 11 months of 30 or 31 days, plus February. February has 28 or 29 days, depending on whether the year is a leap year (occurring every four years). Thus, the Gregorian year is either 365 or 366 days. It is important to note that February 29th is treated like the 53rd week of a fiscal calendar, in that it will not be used in LY comparisons. Similarly, in the current year, February 29th has no value for LY. RI is built around the concept of like-for-like comparisons where your timeframes this year and last year have the same number of days or weeks.
The following is the hierarchy of the Gregorian calendar.
![gregorian calendar hierarchy gregorian calendar hierarchy](img/gregorian.png)
The Gregorian calendar level for Week behaves differently from the Fiscal Calendar. Weeks are not guaranteed to have 7 days, due to the uneven length of a Gregorian year relative to week start/end days. RI uses a pattern where the first and last week of a year may have less than 7 days, so that all other weeks within the year will have 7 days. This also means that the Week level should only roll up directly to the Year level if you want to avoid splitting the same week across multiple months or quarters.
Note:
Only one type of business calendar (4-5-4 or 13-period) can be installed for Retail Insights, in addition to the Gregorian calendar.
Table 5-2 Gregorian Calendar Dimension Attributes
Attribute | Definition |
---|---|
Gregorian Year |
This is the Gregorian Year |
Gregorian Half Year |
This is the Gregorian Half Year |
Gregorian Quarter |
This is the Gregorian Quarter |
Gregorian Month |
Indicate the month. |
Gregorian Month Start Date |
This is the start date of the gregorian month. |
Gregorian Month End Date |
This is the end date of the gregorian month. |
Gregorian Week |
This is the Gregorian Week |
Gregorian Week Start Date |
This is the Gregorian Week Start Date |
Gregorian Week End Date |
This is the Gregorian Week End Date |
The Gregorian calendar attributes within the Business Calendar allow for reporting against this-year and last-year historical data for Gregorian periods, making use of the LY mapping for Gregorian calendar to determine how each period aligns with it's last-year equivalent. For more information on loading the TY-to-LY mappings, refer to the Oracle Retail Insights Operations and Interface Guide.
If you plan to shift your LY calendar to align to a different timeframe year-over-year (for example, to align the week-ending days rather than align the individual days in the year) then there is also a set of Gregorian Unshifted Sales metrics which will not use the shifted LY mappings. These metrics use the abbreviation "GLY" to denote Gregorian LY behaviors.
For example, you may change the Gregorian LY mappings to align Sunday this year to the equivalent Sunday in last year. When using the standard set of LY metrics in RI, they will roll up for those shifted dates (LY YTD may start from January 3rd instead of January 1st in this case). The associated metric for GLY YTD will still roll up from January 1st, no matter what LY mapping you have specified. This allows you to perform life-for-like analyses (where the same weeks are compared YoY) as well as calendar-based analyses (where you compare the same month or year timeframe even if the days are different) without having to switch your calendar back and forth.
Note that at this time, only sales metrics have GLY equivalents. If you want to shift your LY calendar and there is no GLY equivalent metric to get the unshifted view of your data, you can easily create the formula yourself using the built-in AGO() function in OAS.
Gregorian Flexible Attributes
The Gregorian calendar also supports a number of flexible attributes that may be defined by the retailer at the time of implementation. These flex attributes work on the same principle as RMFCS's Custom Flex Attribute system (CFAS) in that the table structure for the data has an identical set of common datatypes and columns. These calendar at-tributes can be used for any number of business reasons, such as reporting on holidays, high-selling periods, alternate definitions of seasons or planning periods, or any other custom timeframe.
Table 5-3 Gregorian Flexible Dimension Attributes
Attribute | Definition |
---|---|
Date Flex Attr 1-10 Char |
Date level character-based flex attribute. |
Date Flex Attr 11-20 Number |
Date level numerical flex attribute. |
Date Flex Attr 21-25 Date |
Date level date-based flex attribute. |
Time of Day
The Time of Day dimension permits analysis in the areas of loss prevention and store productivity, where identifying problems and trends requires the use of hourly or smaller time increments. In addition, the Time of Day dimension allows analysis of sales and return transactions on an hourly basis.
The following is the hierarchy for the Time of Day dimension.
![Time of day dimension hierarchy Time of day dimension hierarchy](img/time_day_hier.png)
Table 5-4 lists the attributes of the Time of Day dimension.
Table 5-4 Time of Day Dimension Attributes
Attribute | Definition |
---|---|
Hour Number 24 Hour Format |
Hour portion in 24-hour format. |
Qtr Hour Interval |
A quarter-hour time slice within the 24-hour period, starting at 0:00 - 0:15to 23:45 - 23:59, numbered from 1 to 4 to indicate the quarter of that hour. |
Minute Number |
Minute portion in 24-hour or 12-hour format, numbered from 1 to 60 to indicate the minute of that hour. |
Employee
The Employee dimension stores data about the employees who work for a retailer. The Employee dimension is attached to sales transactions and is used for productivity and loss prevention reporting.
The employee data that is supplied by Oracle Retail Sales Audit relates only to headquarters employees. In case retailer wants to have all the store and warehouse employees the retailer has to load them into Retail Insights. Other types of employee data that do not exist in Sales Audit, but are desirable for reporting, pertain to employee hours worked and compensation.
RI can be configured to create employee records during the nightly batch process, such that employee IDs included on a transaction (such as the cashier and salesperson) will be usable in reports even when the employee master data file is not provided separately. In this case, reporting on sales by Cashier or Salesperson ID is possible, but the other information like names will not be available.
The primary ways to use the employee attributes for sales reports are:
-
Employee Number along with Sales metrics, to report on employee discounts for employee-purchased items
-
Cashier Number along with Sales metrics, to report on sales by cashier that was logged into the POS
-
Salesperson Number along with Sales metrics, to report on sales by salesperson who was given credit for the sale (or line-item on the sale if multiple salespersons are credited).
Table 5-5 lists the attributes of the Employee dimension.
Table 5-5 Employee Dimension Attributes
Attribute | Definition |
---|---|
Cashier Flag |
Indicator of whether if the employee is a cashier, with values of “Y" for yes and “N" for no. An employee can be both a cashier and a salesperson at the same time. |
Sales Rep Flag |
Indicator of whether the employee is a salesperson, with values of “Y" for yes and “N" for no. An employee can be both a cashier and a sales person at the same time. |
Employee Name |
Name of the employee. |
Employee Number |
Number assigned to the employee. |
Cashier |
Name of the cashier. |
Cashier Number |
Number assigned to the cashier. |
Salesperson |
Name of the salesperson. |
Salesperson Number |
Number assigned to the salesperson. |
Employee Alternate Number |
Old employee ID from a legacy system or other systems still in use such as Payroll. |
Employee Supervisor Number |
Source system ID generated by organization/system for the employee's supervisor or manager. |
Employee Supervisor Name |
Name from the source system for the employee's supervisor or manager. |
Employee Job Title |
The job title associated with the primary position held by the employee. |
Employee Active Flag |
Indicates if the employee is still active in records. |
Employee Auth Amount |
Identifies the amount this employee is authorized to approve for any purposes related to the business of the organization. |
Employee Auth Curr Code |
The currency code for the authorization amount assigned to the employee. |
Employee Auth Cat Code |
The category code for the authorization amount assigned to the employee. |
Cashier Alternate Number |
Old cashier ID from a legacy system or other systems still in use such as Payroll. |
Cashier Supervisor Number |
Source system ID generated by organization/system for the cashier's supervisor or manager. |
Cashier Supervisor Name |
Name from the source system for the cashier's supervisor or manager. |
Cashier Job Title |
The job title associated with the primary position held by the cashier. |
Cashier Active Flag |
Indicates if the cashier is still active in records. |
Cashier Auth Amount |
Identifies the amount this cashier is authorized to approve for any purposes related to the business of the organization. |
Cashier Auth Curr Code |
The currency code for the authorization amount assigned to the cashier. |
Cashier Auth Cat Code |
The category code for the authorization amount assigned to the cashier. |
Salesperson Alternate Number |
Old salesperson ID from a legacy system or other systems still in use such as Payroll. |
Salesperson Supervisor Number |
Source system ID generated by organization/system for the salesperson's supervisor or manager. |
Salesperson Supervisor Name |
Name from the source system for the salesperson's supervisor or manager. |
Salesperson Job Title |
The job title associated with the primary position held by the salesperson. |
Salesperson Active Flag |
Indicates if the salesperson is still active in records. |
Salesperson Auth Amount |
Identifies the amount this salesperson is authorized to approve for any purposes related to the business of the organization. |
Salesperson Auth Curr Code |
The currency code for the authorization amount assigned to the salesperson. |
Salesperson Auth Cat Code |
The category code for the authorization amount assigned to the salesperson. |
Cluster
Understanding consumer shopping behavior is important to help retailers when planning assortment, pricing, promotions and other key merchandising decisions.
This includes understanding:
-
Who shops (or is expected to shop) the merchandise area (Department or Class)
-
How they would shop the merchandise area as well as other merchandise areas when in the store
This information helps retailers develop strategies and tactical execution plans that are tailored to meet specific customers' needs, thus maximizing customer satisfaction while meeting retailers overall business objectives around increased profitability and growth.
Understanding the makeup of the local consumers shopping each individual store is important in developing assortment, pricing and promotion strategies that are tailored to the local consumer needs. However, given the number of stores at a typical retailer, it is not possible to manually plan these at the individual store level. Hence the need for the intelligent grouping of similar stores into clusters.
Clustering stores enables retailers to manage large chains (that is, greater than 500 locations) in an efficient manner. Effective clustering should involve a small number of clusters providing maximum differentiation among one another, while minimizing the difference between the locations within each cluster. In other words, stores in a single cluster should be fairly homogeneous, while the clusters themselves should be heterogeneous.
Stores can be clustered based on their similarity in attributes such as performance, size or format of store, weather, or based on similarity in customer make up based on their demographic attributes.
Clusters are organized into the following hierarchy: Cluster Group - Cluster - Location: below is an example hierarchy.
![Cluster hierarchy example Cluster hierarchy example](img/cluster.jpg)
Cluster Attributes
Table 5-6 Cluster Attribute Dimensions
Attribute | Definition |
---|---|
Cluster Group Code |
The cluster group code is a business code that is also a unique identifier for a cluster group |
Cluster Group Label |
The cluster group label is a short description of why the cluster group was built. |
Cluster Group Type |
Cluster groups are built for multiple reasons, the cluster group type states what the cluster group was built for. Valid cluster group types could be promo, price, markdown, assortment, inventory, replenishment, performance, etc. The cluster group type should be considered required, as it is the only attribute to make sure cluster groups are unique. |
Cluster Code |
The cluster code is a business code that is also a unique identifier for a cluster within a cluster group. |
Cluster Name |
The cluster name is a short description of the cluster. |
Cluster Description |
The cluster description is a long description of the cluster. |
Primary Life-stage |
Primary life-stage is the most prominent life-stage within a cluster - since clusters can be made up of multiple customer segments - there can be more than one life-stage present. Hence this attribute being the primary or most prominent life-stage attribute value. |
Primary Ethnicity |
Primary ethnicity is the most prominent ethnicity within a cluster - since clusters can be made up of multiple customer segments - there can be more than one ethnicity present. Hence this attribute being the primary or most prominent ethnicity attribute value. |
Primary Education Level |
Primary education level is the most prominent education level within a cluster - since clusters can be made up of multiple customer segments - there can be more than one education level present. Hence this attribute being the primary or most prominent education level attribute value. |
Primary Typical Lifestyle |
Primary typical lifestyle is the most prominent typical lifestyle within a cluster - since clusters can be made up of multiple customer segments - there can be more than one typical lifestyle present. Hence this attribute being the primary or most prominent typical lifestyle attribute value. |
Primary Income Level |
Primary income level is the most prominent income level within a cluster - since clusters can be made up of multiple customer segments - there can be more than one income level present. Hence this attribute being the primary or most prominent income level attribute value. |
Primary Dwelling Type |
Primary dwelling type is the most prominent dwelling type within a cluster - since clusters can be made up of multiple customer segments - there can be more than one dwelling type present. Hence this attribute being the primary or most prominent dwelling type attribute value. |
Primary Age Class |
Primary age class is the most prominent age class within a cluster - since clusters can be made up of multiple customer segments - there can be more than one age class present. Hence this attribute being the primary or most prominent age class attribute value. |
Price Zones
Price zones are a way of grouping stores together for use in pricing decisions. They appear functionally similar to Clusters. Price zones are typically created in a retailer's pricing management solution (such as Pricing Cloud Service or RMFCS). Oracle Retail Insights supports loading price zones into the Cluster interfaces using RDE, or through externally interfaced data files. This data will make use of the same attributes outlined above, as well as following the same data structures and formats.
Consumer Attributes
Growing retailers need to attract new customers, and the key to attracting customers is understanding them. Oracle Retail Insights offers a means for retailers to understand and attract new customers and in so doing grow their businesses, through the use of consumer data from Oracle Data Cloud.
Retailers can use Oracle Retail Insights' Consumer analysis to develop a deep understanding of consumers (that is, those shoppers who are their potential customers). It helps retailers understand the types of purchases each consumer segment makes, where the most desirable consumers live and shop, and in which product categories they should be competing for consumers. Building on that knowledge, retailers can build effective strategies to induce consumers to buy their products, and convert them from out-of-reach, obscure consumers to familiar, loyal, and revenue-producing customers.
Getting Consumer Data
The process starts by identifying and segmenting your best customers in order to make requests to ODC for consumer data. The Customer Segmentation module of the AI Foundation Cloud Services can be used to create these customer groups. Once a request is made to ODC for a given list of customers (represented with Oracle Person IDs), they will return a group of consumers who best align to the characteristics of those individuals and represent ideal targets for consumer conversion. This consumer data is loaded into RI (using the W_RTL_CONSUMER_DS interface) for analysis, using a set of flexible attributes which can be relabeled to match the data you get back on the ODC responses.
RI also provides an optional Consumer Segment interface (W_RTL_CONSUMERSEG_DS) for directly loading details you may want to add to an ODC consumer group after creating it, such as a name or description for future reference. Lastly, the interfaces W_RTL_CONS_METADATA_GS and W_RTL_CONS_DOMAIN_LKP_DS are used during implementation to configure which ODC attributes you have requested, so that RI can appropriately display the translatable text strings for them in reporting.
The following table summarizes the available Consumer attributes in RI:
Table 5-7 Consumer Dimension Attributes
Attribute | Definition |
---|---|
Consumer Segment Attributes |
|
Consumer Segment ID |
Unique identifier of a consumer segment, as provided from the source system for consumer data. |
Consumer Segment Name |
Short name or description of a consumer segment, to be provided manually for use in reporting. |
Consumer Segment Type |
The type of consumer segment, such as one sourced from ODC or another external system. |
Consumer Segment Desc |
Detailed description or supplemental details about a consumer segment, such as the purpose of the segment or the actions taken on it. |
Consumer Segment Created Date |
System date when a consumer segment record was first established. |
Consumer Segment Updated Date |
System date when a consumer segment record was last updated. |
Consumer Segment Rank |
The ranked position of a consumer within a given segment. A value of 1 means that the consumer record was identified as the best fit for a segment. |
Consumer Segment Size |
The number of consumers belonging to a consumer segment at the time the segment was processed. |
Consumer Attributes |
|
Consumer ID |
Unique identifier of a consumer. |
Consumer UDA 1 to 100 |
Consumer attribute value as provided by the source system. The specific attributes in these fields will vary by implementation. |
Consumer UDA 1 to 100 Desc |
Consumer attribute description which can be translated or updated over time for the same attribute value. |
Consumer Create Date |
The date a consumer record was first established in this system. |
Consumer Update Date |
The date a consumer record was last updated in this system. |
Organization
The Organization dimension mirrors the structure of the retail company, allowing analysis at every level of the organization. Assessing the contribution of a child attribute to its parent attributes (for example, location to region or chain) allows an analyst to identify the segments of the larger organization that are performing as planned, and those where performance is below expectations. In addition, the Organization hierarchy makes it possible to analyze sales by channel and perform comparable stores analysis.
The majority of business measurements in Retail Insights reference data by attributes of the Organization dimension. Sales and profit, markdowns, stock position, and most other data is held by location, the lowest-level attribute in the Organization dimension hierarchy.
The following diagram illustrates an example organization hierarchy.
![Organization hierarchy example Organization hierarchy example](img/org_hier.png)
Organization starts at company level, with chain, area, region, district, and store at the lower levels of the hierarchy. A warehouse is a physical storage and distribution facility where inventory may be received, held, and transferred to other locations such as stores. A warehouse can be attached to any level of the organizational hierarchy for reporting purposes, but this is not a requirement.
Sets of Books
Multinational retailers need to maintain multiple sets of books in their financial systems. This need can be driven by a number of different factors such as the following:
-
A company divided into different legal entities (such as brands)
-
A company having operations in different countries (with different currencies and calendars)
When a company operates with multiple sets of books, they may have different physical instances of their business and accounting systems to support this segregation, or they may use a single physical instance of their systems to support the different sets of books. When operating with multiple sets of books in a single installation, a company partitions its general ledger according to the sets of books. Each set of books has its own chart of accounts and other identifying characteristics, such as the primary currency and accounting calendar. The company may also partition other data along these lines to help segregate data more efficiently. Sets of books can segregate structural data as well as the chart of accounts.
![Sets of books example Sets of books example](img/sets_of_books.png)
Wholesale
Wholesale functionality, that is, the selling and distribution of products to independently owned customers in a business-to-business transaction, is a market need for many retailers. In contrast to franchise locations, wholesale customers are not owned or licensed by the retailer. Oracle Retail Insights allows retailers who do a significant amount of wholesale business to perform analysis separately from the rest of their regular retail business, with attributes and metrics that are specific to the wholesale business. This enables focused analysis by a retailer who has more than one distinct line of business (retail and wholesale) that have different performance indicators and metrics.
Wholesale Customer Attributes
Table 5-8 lists the attributes of the Wholesale Customer dimension.
Table 5-8 Wholesale Customer Dimension Attributes
Attribute | Definition |
---|---|
DUNS Number |
The DUNS number is a nine-digit number assigned to each business location that has a unique, separate, and distinct operation. |
Organization Size Code |
This attribute is the organization size code. |
Competitor Flag |
This attribute indicates the organization is a competitor. |
Partner Flag |
This attribute indicates the organization is a partner. |
Prospect Flag |
This attribute indicates the organization is a prospect. |
Supplier Flag |
This attribute indicates the organization is a supplier. |
Sales Account Flag |
This attribute indicates the organization has a sales account with the retailer. |
Sales Ref Flag |
This attribute indicates that sales exist for this organization. |
Existing Sales Account Flag |
This attribute indicates the organization has an existing sales account. |
Sales Account Type Code |
This field indicates the type of sales account. |
Internet home page |
This is the URL for the organization's home page. |
Customer Since Date |
This is the date the organization became a customer. |
Customer End Date |
This is the date the organization's customer relationship ended. It could be something like the end of the latest sales contract that was not renewed. |
Customer Category Code |
This field indicates to which category the customer belongs. |
Line of Business |
Line of business. |
SIC Code |
This is the Standard Industry Classification code, a four-digit code used by the US government for classifying industries. |
SIC Name |
Standard Industry Classification name. |
Govt ID Type |
Government ID Type |
Govt ID Value |
Government ID Value |
Service Provider Flag |
This attribute indicates the organization is a service provider. |
Potential Sales Volume |
This is the potential sales volume of the organization. This should be a range of volume amounts. For example [0-500,000], [500,000-1,000,000] and [1,000,000+]. |
Annual Revenue |
This is the organization's annual revenue amount. |
Supplier ID |
This is the supplier ID if the organization is a supplier. |
Customer Number |
This is the internal customer number assigned to the organization. |
Primary Contact Name |
This is the primary contact name for the organization. |
Primary Contact Phone Number |
This is the primary phone number for the organization. |
Base Currency Code |
This is the base currency code of the organization. |
Stockholding Franchise Locations
Franchising is the sales and distribution of products to customers who license a retailer's trade name or services, or both, for a fee. Example services provided could include assortment planning, ordering, and store inventory management. A franchise leases the name of the operating retailer but is not owned by them; however in many situations a retailer manages its franchise stores very similarly to how it manages its own corporate stores, including managing its inventory. In such a situation retailers should create stockholding franchise locations as a way to manage their inventory. Because stockholding franchise locations and corporate locations function similarly, Oracle Retail Insights enables retailers to analyze them similarly while retaining the ability to segregate sales at franchise locations from sales at corporate locations.
Non-Stockholding Franchise Locations
If a retailer does not wish to manage the inventory of its franchise locations, those locations can be set up as non-stockholding franchise locations and analyzed accordingly. The retailer will retain the ability to analyze franchise sales separately from sales at corporate locations.
New/Remodeled Stores
New or recently remodeled stores tend to be more volatile and can have a skewing effect on business performance indicators. Sales and profits from new or recently modeled stores are not really comparable in business analysis and a retailer may decide to exclude them for analysis.
A store may be flagged as a New or Remodeled store in the Organization dimension. The flag can either be received from the merchandising source system or in case the retailer does not have the capability in the merchandising system to calculate the flag, RI can flag the stores as New or Remodeled through an RI driven logic. The flags can be set to 'Y' or 'N' by utilizing the variable RA_NEW_STORE_DT and RA_REMODEL_ STORE_DT in C_ODI_PARAM and the RI existing attributes - remodeled store date (W_INT_ORG_ATTR_D. ORG_ATTR1_DATE) and new store date (W_INT_ORG_ATTR_D. ORG_ ATTR2_DATE).
When supplying new/remodeled store dates directly (as well as store close dates on ORG_ATTR3_DATE), these first three date columns on W_INT_ORG_ATTR_D must be used, as all RI attributes used in reporting on these dates will only source data from these columns.
Organization Attributes
Table 5-9 lists the attributes of the Organization dimension.
Table 5-9 Organization Dimension Attributes
Attribute | Definition |
---|---|
Company |
Name of a company. Company is the highest attribute within the Organization hierarchy. A company consists of one or more chains. |
Company Number |
Unique ID from the source system that identifies a company. |
Chain |
Name of a chain. A chain consists of one or more areas. |
Chain Number |
Unique ID from the source system that identifies a chain. |
Chain Mgr |
Name of a chain manager. |
Area |
Name of an area. An area consists of one or more regions. |
Area Number |
Unique ID from the source system that identifies an area. |
Area Mgr |
Name of an area manager. |
Region |
Name of a region. |
Region Number |
Unique ID from the source system that identifies a region. |
Region Mgr |
Name of a region manager. |
District |
Name of a district. A district consists of one or more locations. |
District Number |
Unique ID from the source system that identifies a district. |
District Mgr |
Name of a district manager. |
Loc |
Lowest attribute within the organization hierarchy. It identifies a warehouse, store, or partner within the company. |
Loc Number |
Unique ID from the source system that identifies a location. |
Loc List |
Name of a location list. A location list is an intentional grouping of locations for reporting purposes. |
Loc List ID |
Unique ID from the source system that identifies a location list. A location list is an intentional grouping of locations for reporting purposes. |
Loc Trait |
Name of a location trait. A location trait is an attribute of a location that is used to group locations with similar characteristics. |
Loc Trait ID |
Unique ID from the source system that identifies a location trait. A location trait is an attribute of a location that is used to group locations with similar characteristics. |
Tsf Entity ID |
Unique ID from the source system that identifies a transfer entity. A transfer entity is a group of locations that share legal requirements around product management. A location can belong to only one transfer entity, and a transfer entity can belong to multiple organization units. |
Org Unit ID |
Unique ID from the source system that identifies a financial organization unit. An organization unit can belong to only one set of books. |
SOB ID |
Unique ID from the source system that identifies a financial set of books. A set of books represents an organizational structure that groups locations based on how they are reported from an accounting perspective. |
Tsf Entity Desc |
Detailed description of a transfer entity. A transfer entity is a group of locations that share legal requirements around product management. A location can be associated with only one transfer entity, and a transfer entity can be associated with multiple organization units. |
Comp Flag |
Indicator of whether a location has been opened for configurable time, with values of “Y" for yes and “N" for no. Generally, comparable stores are locations that are in operation for at least 53 weeks. |
Comp Anchor Year |
When using the "same stores" method of comp store analysis, the anchor year specifies which year of comp flag statuses should be applied across previous years. This attribute is required in analyses which use that method of comp reporting, and is generally set to the current fiscal year. |
New Store Flag |
Indicator of whether a location has been opened newly, with values of "Y" for yes and "N" for no. |
Remodeled Store Flag |
Indicator of whether a location has been remodeled recently, with values of "Y" for yes and "N" for no. |
Store Type |
Indicator of the type of store, with values of “Company," “Wholesale," and “Franchise." |
Address Type |
Type of address. Values are as follows:
|
Loc Name3 |
Three-character abbreviation of a location name. |
Loc Name10 |
Ten-character abbreviation of a location name. |
Loc Name Secondary |
Secondary name of a location. |
Address Line 1 |
First line of street address. |
Address Line 2 |
Second line of street address. |
Address Line 3 |
Third line of street address. |
City |
City of a location. |
Postal Code |
Postal code of a location. |
Phone Number |
Primary phone number of a location. |
Loc Type |
Type of location, with values of “Store," “Warehouse," and “External Finisher." |
Linear Distance |
Total merchandisable space of a location. Feet is the unit of measure. |
VAT Region ID |
Unique ID from the source system that identifies the Value Added Tax (VAT) region in which a store is located. |
VAT Included Flag |
Indicator of whether Value Added Tax (VAT) is included in the retail price, with values of “Y" for yes and “N" for no. |
Currency Code |
Base currency code of the organization. |
Break Pack Flag |
Indicator of whether a warehouse is capable of distributing less than the supplier's case quantity, with values of “Y" for yes and “N" for no. |
Stockholding Flag |
Indicator of whether a location can hold stock, with values of “Y" for yes and “N" for no. In a non-multichannel environment, the value is always "Y". |
Loc Mgr |
Name of the manager of the organization. |
Mall |
Name of the mall in which a store is located. |
Loc Open Date |
Open date of a location. |
Loc Close Date |
Close date of a location. |
Selling Area |
Total square footage of a store's selling area. |
Remodel Date |
Date that a location was last remodeled. |
Tsf Zone ID |
Unique ID from the source system that identifies a transfer zone. A transfer zone is an intentional grouping of locations for transferring owned inventory from one location to another. A location can belong to only one transfer zone. |
Promo Zone ID |
Unique ID from the source system that identifies a promotion zone. A promotion zone is an intentional grouping of locations for promotion activity. A location can belong to only one promotion zone. |
Total Area |
Total square footage of a location. |
Default WH ID |
Warehouse that can be used as the default for creating cross-dock masks. This determines which stores may be sourced by a warehouse, and it only contains virtual warehouses in a multichannel environment. |
Store Format Desc |
Description of a store format. Examples are Conventional Store, Supermarket, Virtual Store, Catalog Store, and Hard Discount. |
Store Format ID |
Unique ID from the source system that identifies a store format. |
State |
State name of a location. |
Country |
Country name of a location. |
Banner ID |
Unique ID from the source system that identifies a banner. A banner is the name of a retailer's subsidiary. |
Banner |
Name of a banner. A banner is the name of a retailer's subsidiary. |
Channel |
Name of a channel. A channel is a method for a retailer to interact with a customer, and it is an outlet for sale and delivery of goods and services to the customer. A retailer can have multiple outlets, such as brick-and-mortar stores, Web sites, and catalogs. |
Channel ID |
Unique identifier associated with a channel. |
Channel Type |
Type of channel to interact with a customer. The values are “Brick and Mortar," “Webstore," and “Catalog." |
Virtual WH Flag |
Indicator of whether a location is a virtual warehouse, with values of “Y" for yes and “N" for no. |
Physical WH ID |
Unique ID from the source system that identifies a physical warehouse that is assigned to a virtual warehouse. |
State Code |
Code that identifies the state of the location. |
Sister Store ID |
Location that will be used to relate a current store to the historical data of an existing store. |
Store Class |
Type of store class, which retailers can use to group their stores. The best stores are typically considered “A" stores, the next-best “B" stores, and so on. Values can be “A," “B," “C," “D,""E," and “X". |
WH Delivery Policy |
Contains the delivery policy of the warehouse. |
WH Redistribution Indicator |
Indicates that the warehouse is a re-distribution warehouse. Used as a location on Purchase Orders in place of actual locations that are unknown at the time of Purchase Order creation and approval. Valid values are Y or N. |
WH Replenishment Indicator |
This indicator determines if a warehouse is replenishable. |
WH Finisher Indicator |
Indicates if this virtual warehouse is an internal finisher. |
Virtual WH Type |
Indicates the type of virtual warehouse. Codes vary by retailer and are specified in the source system. |
WH Inbound Handling Days |
Warehouse inbound handling days are defined as the number of days that the warehouse requires to receive any item and get it to the shelf so that it is ready to pick. |
Duns Location |
Holds the location associated with the DUNS number. |
DUNS Number |
Holds the Dun and Bradstreet number to identify the company. |
Loc Customer Order Flag |
Indicates whether the location is customer order location or not. |
Loc Customer Order Ship Flag |
Indicates whether the location is able to ship customer orders or not. |
Loc Email Address |
Contains the email address for the store or warehouse. |
Loc Fax Number |
Contains the fax number for the location. |
Loc Gift Wrapping Flag |
Indicates whether the location supports gift wrapping or not. |
Store Acquired Date |
Contains the date on which the store was acquired. |
Store Language ISO Code |
Holds the ISO code associated with the given store language. |
Total Selling Area |
Total store selling area as a summable metric for reporting at higher levels of the hierarchy. |
Comparable Store
Comp stores are really established stores as opposed to new or closed stores. Comp store measurements are important to an analyst because profits and sales from the more established stores provide stable indicators of business performance. New or closed stores tend to be more volatile and can have a skewing effect on business performance indicators. Sales and profits from new or closed stores are not really comparable in business analysis, and as a result, they are not included in the comp store measurements.
The Comparable Store Flag can be sent from the retailer's non-Oracle merchandising source system or manually derived and loaded into the RI interface for W_RTL_LOC_COMP_MTX_DS. Note that RI does not load comp flag information from RMFCS in either case; it is either sourced externally and interfaced to RI, or derived by hand and uploaded as-needed. In both cases, the data should consist of a pipeline delimited flat file containing Store ID, Comp Store Flag, Effective From Date, which will form the interface file that must be loaded to W_ RTL_LOC_COMP_MTX_DS table. This file can contain flag values in (N, Y, C), representing non-comparable, comparable, and closed stores respectively.
RI provides multiple methods for reporting on comparable stores, depending on the retailer's business needs. In the "Same Store" method of comparison, the stores designated as comp/non-comp/closed in a reporting period have their history grouped under the same statuses for previous years as well, allowing the retailer to always be comparing the same stores this year versus last year in comp reporting. This option is enabled using the SAME_STORES variable in C_ODI_PARAM. If set to 'Y' then this method is enabled for both As-Is and As-Was reporting. The number of years of history to "duplicate" the comp statuses across is configured with the ANCHOR_TO_YEARS variable, which defaults to 2 years (this year and last year).
If Same Stores comp reporting is disabled, then both subject areas instead use As-Was comp reporting (also known as Group Comp). This method will consider the historical values of the Comp Flag and directly report a store's history based on its comp status at that point in time. This type of reporting may split a store's history across different comp statuses within the same analysis.
Product
The Product dimension represents the product lines that the company sells. The Product dimension is essential to the department manager who needs to know which items turn the highest profit, or how an item performs within the market as a whole. Because of its importance for analysis in the retail environment, attributes from the Product dimension are present in nearly every data mart in Retail Insights. In most cases, data is kept at the lowest level in the hierarchy (item), to allow maximum flexibility and detail in reporting.
The following diagram illustrates an example product hierarchy.
![Product hierarchy example Product hierarchy example](img/product_hier.png)
Product Differentiators
Differentiators are used to define the characteristics of an item. Characteristics such as size, color, flavor, scent, and pattern are attached to items as differentiators within Oracle Retail Merchandising Foundation Cloud Service (RMFCS). Differentiators hold all item differentiator identifiers, along with their associated National Retail Federation (NRF) industry codes.
Oracle Retail Insights comes with the following differentiators as an example for reference:
-
Style (Color only)
-
Color for Style
They are arranged in the following hierarchy: Style (Color only) > Color for Style > Item.
These are just an example and can be added to or modified as needed to make them relevant to a specific retail business.
![Hierarchy example Hierarchy example](img/product-diff.png)
For retailers that do not use Style and Color differentiators, a more generic set of attributes is available which captures any diff (or combination of diffs) that are selected in RMFCS when creating SKUs from a Level 1 item. This combination of diffs is referred to as the "item diff aggregate". RI captures the diff values used to define all of the SKUs under a Level 1 item, and creates an intermediate reporting level in between Level 1 and Level 2 (SKU). In the typical Style/Color configuration of a fashion retailer, this is the same as the Color level of reporting, but it has the flexibility to support whatever combination of diffs are used in RMFCS.
The following attributes allow for building reports at the Item Diff Aggregate level:
-
Item Diff Agg ID
-
Item Diff Agg Desc
They are arranged in the following hierarchy: Item Level 1 > Item Diff Agg > Item.
Product Attributes
Table 5-10 lists the attributes of the Product dimension:
Table 5-10 Product Dimension Attributes
Attribute | Definition |
---|---|
Company Number |
Unique ID from the source system that identifies a company. |
Company |
Name of a company. A company consists of one or more divisions. |
Division Number |
Unique ID from the source system that identifies a division. |
Division |
Name of a division. A division is the highest category of merchandise within an organization. Typically a division is used to signify the overall category of merchandise, such as hardlines or apparel. |
Division Buyer Number |
Unique ID from the source system that identifies a division buyer. |
Division Buyer |
Name of a division buyer, an executive responsible for purchasing merchandise to be sold in a store or retail channel for a particular division. |
Division Merchant Number |
Unique ID from the source system that identifies a division merchant. |
Division Merchant |
Name of a division merchant. |
Group Number |
Unique ID from the source system that identifies a group. |
Group |
Name of a group. A group is the next level of merchandise in a hierarchy below division. A group consists of one or more departments. A group can belong to only one division. |
Group Buyer Number |
Unique ID from the source system that identifies a group buyer. |
Group Buyer |
Name of a group buyer. A group buyer is an executive responsible for purchasing merchandise to be sold in a store or retail channel for a particular group. |
Group Merchant Number |
Unique ID from the source system that identifies a group merchant. |
Group Merchant |
Name of a group merchant. |
Department Number |
Unique ID from the source system that identifies a department. |
Department |
Name of a department. A department is the next level below group in the merchandise hierarchy. A group can have multiple departments. Key information about how inventory is tracked and reported is stored at the department level. |
Department Buyer Number |
Unique ID from the source system that identifies a department buyer. |
Department Buyer |
Name of a department buyer, an executive responsible for purchasing merchandise to be sold in a store or retail channel for a particular department. |
Department Merchant Number |
Unique ID from the source system that identifies a department merchant. |
Department Merchant |
Name of a department merchant. |
Profit Calc Type |
Indicator of the profit calculation type, with values of “Direct Cost" and “Retail Inventory". |
Purchase Type |
Indicator of the purchase type of merchandise, with values of “Owned", “Consignment", and “Concession." |
OTB Calc Type |
Indicator of the open–to-buy calculation type, with values of “Cost" and “Retail." |
Class Number |
ID within a department that uniquely identifies a class. |
Class |
Name of a class. A class is the next level below department in the merchandise hierarchy. A department can have multiple classes. A class provides the means to group products within a department. A class consists of one or more subclasses. |
Class Buyer Number |
Unique ID from the source system that identifies a class buyer. |
Class Buyer |
Name of a class buyer, an executive responsible for purchasing merchandise to be sold in a store or retail channel for a particular class. |
Class Merchant Number |
Unique ID from the source system that identifies a class merchant. |
Class Merchant |
Name of a class merchant. |
Subclass Number |
ID within a department number and class number that uniquely identifies a subclass. A class can have multiple subclasses. |
Subclass |
Name of a subclass. A subclass defines the type of merchandise sold in a department and class. |
Subclass Buyer Number |
Unique ID from the source system that identifies a subclass buyer. |
Subclass Buyer |
Name of a subclass buyer, an executive responsible for purchasing merchandise to be sold in a store or retail channel for a particular subclass. |
Subclass Merchant Number |
Unique ID from the source system that identifies a subclass merchant. |
Subclass Merchant |
Name of a subclass merchant. |
Item Number |
Unique ID from the source system that identifies an item. |
Item |
Detailed description of an item. Item is the lowest-level attribute within a product hierarchy. Sales and inventory facts are tracked at one of the predetermined levels within the Item attribute. |
Pack Flag |
Indicator of whether an item is a pack. A pack item is a collection of items that can be ordered or sold as a single unit. |
Package Size |
Size of the product printed on packaging. |
Package UOM |
Unit of measurement in which a package size is measured. |
Item Level |
Indicator of the level within an item family, with values of 1, 2, and 3. |
Transaction Level |
Indicator of the level within an item family that inventory is tracked, with values of 1, 2, and 3. |
Item Level 1 Number |
Item number of the highest level in an item family. |
Item Level 1 Desc |
Item description of the highest level in an item family. |
Item Level 2 Number |
Item number of the second level in an item family. |
Item Level 2 Desc |
Item description of the second level in an item family. |
Item Level 3 Number |
Item number of the lowest level in an item family. |
Item Level 3 Desc |
Item description of the lowest level in an item family. |
Item Diff Agg ID |
Combination of item differentiators used to define the aggregate reporting level between Level 1 and Level 2. |
Item Diff Agg Desc |
Description of item differentiators used to define the aggregate reporting level between Level 1 and Level 2. |
Color Item Diff Agg |
The color of an item, when used as an item diff aggregate. |
Size Item Diff Agg |
The size of an item, when used as an item diff aggregate. |
Flavor Item Diff Agg |
The flavor of an item, when used as an item diff aggregate. |
Brand Item Diff Agg |
The brand of an item, when used as an item diff aggregate. |
Style Item Diff Agg |
The style of an item, when used as an item diff aggregate. |
Fabric Item Diff Agg |
The fabric of an item, when used as an item diff aggregate. |
Scent Item Diff Agg |
The scent of an item, when used as an item diff aggregate. |
Original Retail |
Original retail price of an item per unit and is stored in the primary currency. |
Mfg Recommended Retail |
Recommended manufacturer's retail price of an item per unit, stored in the primary currency. |
Pack Number |
Item number where PACK_FLG = Y. A pack item is a collection of items that can be ordered or sold as a single unit. |
Pack Item Quantity |
Quantity of a pack component item units that make up a pack. |
Pack Desc |
Item description where PACK_FLG = Y. A pack item is a collection of items that can be ordered or sold as a single unit. |
Pack UOM |
Standard unit of measurement for a pack item. |
Item List ID |
Unique ID from the source system that identifies an item list. An item list is an intentional grouping of items for operational purposes. |
Item List Desc |
Detailed description of an item list. An item list is an intentional grouping of items for operational purposes. |
UDA Head ID |
Unique ID from the source system that identifies a user-defined attribute of an item. A UDA head is a parent of a UDA detail. |
UDA Head Desc |
Detailed description of a user-defined attribute of an item. A UDA head is a parent of a UDA detail. |
UDA Detail ID |
Unique ID from the source system that identifies a user-defined attribute detail of an item. A UDA detail can be a child of only one UDA parent. |
UDA Detail Desc |
Detailed description of a user-defined attribute detail of an item. A UDA detail can be a child of only one UDA parent. |
Diff Type |
Indicator of the differentiator type, with example values of “Size," “Color," “Flavor," “Scent," and “Pattern." A differentiator type is a parent of a differentiator. |
Diff ID |
Unique ID from the source system that identifies an item differentiator. Differentiators define the characteristics of an item. A differentiator can be a child of only one differentiator type. |
Diff Desc |
Description of an item differentiator. A differentiator can be a child of only one differentiator type. |
UOM |
Standard unit of measurement for an item. |
Item Number Type Code |
Indicator of the type of numbering system used to identify an item. Values are as follows:
|
Item Input Flag |
Indicator of whether an item holds inventory for an item transformation, with values of “Y" for yes and “N" for no. |
Merchandise Flag |
Indicator of whether an item is merchandise, with values of “Y" for yes and “N" for no. |
Pack Retail Flag |
Indicator of whether a pack has its own unique retail price, or if a pack retail price is the sum of its components' retail prices, with values of “Y" for yes and “N" for no. |
Class Alternate |
Alternate version of the Class attribute which operates only on the description of the class, allowing for grouping of same-named classes onto the same result row in an analysis. |
Subclass Alternate |
Alternate version of the Subclass attribute which operates only on the description of the subclass, allowing for grouping of same-named subclasses onto the same result row in an analysis. |
Item Desc |
Descriptive text for the transaction-level item from the merchandising system, without any appended values such as item numbers. |
Item UDA ID 1 - 50 Item UDA Desc 1 - 50 |
User-defined attributes which have been "pivoted" into a column-based structure, allowing for side-by-side usage in reporting. Content of the attributes is determined by populating the W_RTL_UDA_METADATA_G interface. |
Item Diff ID 1 - 8 Item Diff Desc 1 - 8 |
User-defined differentiators which have been "pivoted" into a column-based structure, allowing for side-by-side usage in reporting. Content of the attributes is determined by populating the W_RTL_UDA_METADATA_G interface. |
Item Supplier Label |
The descriptive label for an item as provided by the supplier to the merchandising system. |
Item Supplier VPN |
The vendor product number as provided by the supplier to the merchandising system. |
Item Supplier Origin Country |
The origin country as provided by the supplier to the merchandising system. |
Item Supplier Pickup Lead Time |
The pickup leadtime as provided by the supplier to the merchandising system. |
Item Supplier Inner Pack Size |
The inner pack size of an item as provided by the supplier to the merchandising system. |
Item Secondary Desc |
The secondary item description optionally provided in the merchandising system. |
Item Primary Part Number |
The primary part number associated with a transaction item, such as a UPC or EAN number. |
Item Primary Part Desc |
The primary part description associated with a transaction item, such as a UPC or EAN description. |
Pack Comp Qty |
Pack component item units contained within a pack item. |
Color Group |
Description of the differentiator group used to setup items having a Color attribute in the merchandising system. |
Color Group ID |
Identifies the differentiator group used to setup items having a Color attribute in the merchandising system. |
Container Item |
This field holds the container item number for a contents item. |
Diff Group |
Description of the differentiator group used to setup items in the merchandising system. |
Diff Group ID |
Differentiator group used to setup items in the merchandising system. |
Item Case Type |
This field determines which case sizes to extract against an item for inventory planning applications |
Item Catch Weight Flag |
Indicates whether the item should be weighed when it arrives at a location. |
Item Catch Weight Order Type |
This field determines how catch weight items are ordered. |
Item Catch Weight Sale Type |
This field indicates the method of how catch weight items are sold in store locations. |
Item Catch Weight UOM |
Unit of measure for catch weight items. |
Item Cost Zone Group |
Cost zone group associated with the item. |
Item Default Waste Percent |
Default daily wastage percent for spoilage type wastage items. |
Item Deposit Price Per UOM |
This field indicates if the deposit amount is included in the price per UOM calculation for a contents item ticket. |
Item Deposit Type |
This field is the deposit item component type. A NULL value in this field indicates that this item is not part of a deposit item relationship. |
Item Forecastable Flag |
Indicates if this item will be interfaced to an external forecasting system (Y, N). |
Item Gift Wrap Flag |
This field will contain a value of Y if the item is eligible to be gift wrapped. |
Item Handling Sensitivity |
Holds the sensitivity information associated with the item. |
Item Handling Temp |
Holds the temperature information associated with the item. |
Item Retail Label Type |
This field indicates any special label type associated with an item (i.e. pre-priced or cents off). |
Item Retail Label Value |
This field represents the value associated with the retail label type. |
Item Service Level |
Holds a value that restricts the type of shipment methods that RCOM can select for an item. |
Item Ship Alone Flag |
This field will contain a value of Y if the item should be shipped to the customer is a separate package versus being grouped together in a box. |
Item Short Desc |
Shortened description of the item. |
Item Store Order Multiple |
Merchandise shipped from the warehouses to the stores must be specified in this unit type or multiple. |
Item UOM Conversion Factor |
Conversion factor between an Each and the STANDARD_UOM when the STANDARD_UOM is not in the quantity class. |
Item Waste Percent |
Average percent of wastage for the item over its shelf life. |
Item Waste Type |
Identifies the wastage type as either Sales Wastage or Spoilage Wastage. |
Pack Orderable Code |
Code identifying the type of orderable pack. An orderable pack is a collection of items that is ordered as a single unit. Values include V (vendor pack), B (buyer pack) and N (not orderable). |
Pack Sellable Code |
Code identifying the type of sellable pack. A sellable pack is a collection of items that is sold as a single unit. Values include S (sellable) and N (not sellable). |
Pack Type Code |
Code identifying the type of pack. A pack is a collection of one or more items with varying quantities. Values include S (simple pack) and C (complex pack). |
Perishable Item Flag |
A grocery item attribute used to indicate whether an item is perishable or not. |
Simple Pack Comp Number |
The component item number contained in a simple pack. Use this attribute to display the component of a sellable simple pack in-line with other data. If the item is not a simple pack, it will just repeat the selling item number. |
Simple Pack Comp Qty |
The component item quantity contained in a simple pack. Use this attribute to display the number of units in a sellable simple pack in-line with other data. If the item is not a simple pack, it will display a quantity of 1. |
Size Group |
Description of the differentiator group used to setup items having a Size attribute in the merchandising system. |
Size Group ID |
Identifies the differentiator group used to setup items having a Size attribute in the merchandising system. |
Item Orderable Flag |
Indicates if the item is orderable in the merchandising system. |
Table 5-11 Product Split Dimension Attributes
Attribute | Definition |
---|---|
Style |
This attribute displays the style of an item. |
Color |
This attribute displays the color of an item. |
Size |
This attribute displays the size of an item. |
Fabric |
This attribute displays the fabric of an item. |
Flavor |
This attribute displays the flavor of an item. |
Scent |
This attribute displays the scent of an item. |
Color Alternate |
This attribute displays the description of a color in primary language, for use in grouping report results by the color label. |
Size Alternate |
This attribute displays the description of a size in primary language, for use in grouping report results by the size label. |
Season Alternate |
This attribute displays the description of a season in primary language, for use in grouping report results by the season label. This has been copied over from the Season Phase dimension for use in item/season based reports. |
Phase Alternate |
This attribute displays the description of a season phase in primary language, for use in grouping report results by the phase label. This has been copied over from the Season Phase dimension for use in item/season based reports. |
Item Part Desc |
Description of the code associated with a product that is typically printed on the physical item, such as a UPC, EAN, or PLU. A single SKU may have multiple part numbers associated with it. |
Item Part Number |
A code associated with a product that is typically printed on the physical item, such as a UPC, EAN, or PLU. A single SKU may have multiple part numbers associated with it. |
Product Images
RI is capable of displaying item images that have been configured for use in RMFCS. RI directly captures the URLs which have been assigned to items and exposes them to OAS as string attributes. The URLs can be displayed as images by changing the column's Data Format option to either Image URL or HTML. The Image URL format will directly perform a GET browser request on the URL and return the image exactly as it is formatted on the host system. The HTML format allows you to enter custom HTML tags to change the format of the image, such as the width or height. All URLs must use the HTTPS protocol, image URLs using HTTP will not be rendered in RI. The image URLs are a concatenation of the file path and file name from RMFCS, without any manipulation. Ensure that this concatenation results in a valid URL before using it in RI.
An example column formula that can be used in conjunction with the HTML data format is provided below:
'<img src='||"Item As Is"."Item Image"||' width=100 height=100 />'
This formula will display the image at a forced 100x100 pixel size, which is a typical viewing size in reports where the image is just a reference (e.g. to see the color or silhouette) rather than the focus of the analysis.
Table 5-12 lists the attributes for item images.
Table 5-12 Product Image Attributes
Attribute | Definition |
---|---|
Item Image |
Image representing a transaction item. |
Style Image |
Image representing a style or parent-item. |
Subclass Image |
Image representing a subclass. |
Class Image |
Image representing a class. |
Department Image |
Image representing a department. |
Group Image |
Image representing a group. |
Division Image |
Image representing a division. |
Company Image |
Image representing a company. |
Item Attribute Image |
Image representing an item attribute. |
Default Item Attribute Image |
This is the default Item Attribute image. |
Second Half Item Image |
This attribute displays the description of the Item image used in the item similarity comparison. |
Related Items
Table 5-13 lists the attributes of the Related Items dimension. These values are sourced from the related item data in RMFCS. Related item attributes are currently supported with the Sales and Inventory Position facts. Related items should be viewed along with the Item dimension to see the full relationship.
Table 5-13 Product Org Dimension Attributes
Attribute | Definition |
---|---|
Item Relationship ID |
Unique identifier for the relationship with a related item. |
Item Relationship Name |
Name given to the relationship with a related item. |
Item Relationship Type |
Describes the type of relationship for a related item. Values are configured in code_detail table under code_type IREL. Valid values: CRSL, SUBS. |
Related Item Desc |
Description of the related item. |
Related Item End Date |
Indicates the date till the related items can be used for transactions. A null value means it's effective indefinitely. |
Related Item Number |
Unique identifier of the related item. |
Related Item Priority |
Relative priority of a related item when multiple items are assigned. Applicable only in case of relationship type SUBS. |
Related Item Start Date |
Indicates the date when the related items can be used for transactions. |
Substitute Items
Table 5-14 lists the attributes of the Substitute Items dimension. These values are sourced from the substitute item data in RMFCS. Substitute item attributes are currently supported with the Sales and Inventory Position facts. Substitute items should be viewed along with the Item and Organization dimensions to see the full relationship.
Table 5-14 Substitute Items Dimension Attributes
Attribute | Definition |
---|---|
Substitute End Date |
Indicates the date when the substitution will end for the main item |
Substitute Item Desc |
Description for the substitute item. |
Substitute Item Fill Priority |
Contains the fill priority for the main item relative to a substitute item and is NULL if LOC_TYPE is not W (Warehouse). Valid values for this field are: M (main), S (substitute) |
Substitute Item Number |
Unique identifier for the substitute item. |
Substitute Item Pick Priority |
Contains the pick priority for the substitute item. If there are multiple substitute items for a main item, then the pick priority will determine the order the item is picked. |
Substitute Reason |
Reason for substituting the item. |
Substitute Replenishment Pack |
Contains the replenishment pack, if any, that will be used to fulfill the demand of the associated item. |
Substitute Start Date |
Indicates the date when the substitution will start for the main item. |
Product Org Attributes
Table 5-15 lists the attributes of the Product Org Attributes dimension. These values are sourced from the item/loc traits and replenishment item loc table data in RMFCS
Table 5-15 Product Org Dimension Attributes
Attribute | Definition |
---|---|
Backorder Indicator |
Contains a value of Y to indicate the item is backorderable. |
Electronic Marketing Club |
Code representing the electronic marketing club the item belongs to at the location. |
Food Stamp Indicator |
Contains a value of Y when the item is eligible for food stamps. |
In Store Market Basket |
Contains the in store market basket code for the item/location. |
Manual Price Entry |
Contains a value of Y when the item is expected to have manual price entries at the POS for a location. |
National Brand Comparison Item |
Nationally branded item to which you would like to compare the current item. |
Refundable Indicator |
Contains a value of Y to indicate the item is refundable at that location. |
Returnable Indicator |
Contains a value of Y to indicate the item is returnable to that location. |
Reward Club Eligible Indicator |
Whether the item is valid for various types of bonus point or award programs at the location. |
Store Reorderable Indicator |
Contains a value of Y to indicate the item is reorderable to that location. |
WIC Indicator |
Contains a value of Y to indicate the item is eligible for the Women, Infants, and Children (WIC) program. |
Item Loc Status |
Current status of item at the store |
Item Loc Previous Status |
Previous status of item at the store |
Item Loc Status Update Date |
Date on which the status for item at the store was most recently changed. |
Item Loc Ranged Flag |
Contains a value of Y to indicate the item location ranging. |
Item Loc Clearance Flag |
Contains a value of Y to indicate the item is on clearance at the store. |
Item Loc Taxable Flag |
Contains a value of Y to indicate the item is taxable at the store. |
Item Loc Local Desc |
Contains the local description of the item at a specific location. |
Item Loc Local Short Desc |
Contains the local short description of the item at a specific location. |
Item Loc Pallet Tier Units |
Contains the number of shipping units (cases) that make up one tier of a pallet. Multiply TIER x HEIGHT to get total number of cases for a pallet. |
Item Loc Pallet Height |
Contains the number of tiers that make up a complete pallet (height). Multiply TIER x HEIGHT to get total number of cases for a pallet. |
Item Loc Store Order Multiples |
Contains the multiple in which the item needs to be shipped from a warehouse to the location. |
Item Loc Daily Waste Percent |
Contains the average percentage lost from inventory on a daily basis due to natural wastage. |
Item Loc Size of Each |
Contains the size of an each in terms of the uom_of_price. |
Item Loc Ticket Size |
Contains the size to be used on the ticket in terms of the uom_of_price. |
Item Loc Ticket UOM |
Contains the unit of measure that will be used on the ticket for this item. |
Item Loc Primary Variant |
Address sales of PLUs (i.e. above transaction level items) when inventory is tracked at a lower level (i.e. UPC). |
Item Loc Primary Cost Pack |
Contains an item number that is a simple pack containing the |
Item Loc Primary Supplier |
item in the item column for this record. |
Item Loc Primary Country |
Contains the numeric identifier of the supplier who will be considered the primary supplier for the specified item/loc. |
Item Loc Inbound Handling Days |
Contains the identifier of the origin country which will be considered the primary country for the specified item/location. Contains the number of inbound handling days for an item at a warehouse type location. |
Item Loc Source Method |
Specifies how the ad-hoc PO/TSF creation process should source the item/stores request. |
Item Loc Source Warehouse |
Used by the ad-hoc PO/Transfer creation process to determine which warehouse to fill the stores request from. |
Item Loc UIN Type |
Contains the unique identification number (UIN) used to identify the instances of the item at the location. |
Item Loc UIN Label |
Contains the label for the UIN when displayed in SIM. |
Item Loc UIN Capture Time |
Indicates when the UIN should be captured for an item during transaction processing. |
Item Loc UIN Generation Flag |
Contains a value of Y to indicate the UIN is being generated in the external system. |
Item Loc Franchise Costing Loc ID |
Indicates if the costing location of the franchise store is a store or a warehouse. |
Item Loc Franchise Costing Loc Type |
Contains the type of costing location in the costing location field. |
Replenishment Order Method |
Determines if the replenishment process will create an actual order/transfer line item for the item location if there is a need for it or if only a record is written to the Replenishment Results table. Valid values are Manual, Semi-Automatic, Automatic, or Buyer Worksheet. |
Replenishment Method |
Contains the character code for the algorithm that will be used to calculate the recommended order quantity for the item location. Valid values include Constant, Min/Max, Floating point, Time Supply, Dynamic, SO Store Orders. Replenishment Increment Percent |
Replenishment Increment Percent |
Contains the percentage by which the min and max stock levels will be multiplied when calculating the recommended order quantity. |
Replenishment Supply Min Days |
Contains the minimum number of days of supply of stock to maintain. |
Replenishment Supply Max Days |
Contains the maximum number of days of supply of stock to maintain. |
Replenishment Time Supply Horizon |
Contains the number of days over which an average sales rate is calculated to be used in the Time Supply replenishment method algorithm. |
Replenishment Inventory Selling Days |
Contains the number of required days of on hand inventory to satisfy demand. |
Replenishment Reject Orders Flag |
Contains a value of Y to indicate if uploaded store orders with needs date on or after the NEXT_DELIVERY_DATE are valid. |
Replenishment Lost Sales Factor |
Contains the percentage of sales that could have occurred if inventory had been available through the order lead time. |
Replenishment Scaling Exempt Flag |
Contains a value of Y to indicate if the item/location should be exempt from scaling during the order scaling process during the replenishment process |
Replenishment Order Scale Max Value |
Contains the limit up to which order scaling can increase the order quantity for the item/location during the replenishment process. |
Replenishment Terminal Stock Qty |
Contains the desired stock on hand for the item location when the end of season is reached. |
Replenishment Season ID |
Contains the numeric identifier of the season for which this item location is being replenished. |
Replenishment Phase ID |
Contains the numeric identifier of the phase within the season for which this item location is being replenished. |
Replenishment Last Review Date |
Contains the date on which the item location was last reviewed. |
Replenishment Next Review Date |
Contains the date on which the item location will be reviewed next. |
Replenishment Order Unit Tolerance |
Contains the allowable unit change to order quantities generated from replenishment. |
Replenishment Order Percent Tolerance |
Contains the allowable percent change to order quantities generated from replenishment. |
Replenishment Tolerance Flag |
Contains a value of Y to indicate unit and percent tolerances will be used. |
Replenishment Last Delivery Date |
Contains the last delivery date that replenishment was run for. |
Replenishment Next Delivery Date |
Contains the next delivery date calculated for the next review cycle. |
Replenishment MBR Order Qty |
Populated if the item on replenishment is using the Warehouse Stocked/Cross-Docked stock category. |
Replenishment MBR Pickup Lead Time |
Contains the pickup lead time for MBR cross-link line items after reqext processes them. |
Replenishment MBR Supplier Lead Time |
Contains the supplier lead time for MBR cross-link line items after reqext processes them. |
Replenishment Transfer PO Link |
Contains a reference number to link the item on the transfer to any purchase orders that have been created to allow the from location (i.e. warehouse) on the transfer to fulfill the transfer quantity to the to location (i.e store) on the transfer. |
Replenishment Last ROQ |
Contains the last recommended order quantity created by Vendor Replenishment Extraction (rplext.pc). |
Replenishment Pack Size Level |
Contains the pack size level (Case, Inner, Each) at which the item is shipped between warehouses and stores. |
Replenishment Unit Cost |
Contains the unit cost for the item for the replenishment supplier/country. |
Replenishment Supplier Lead Time |
Contains the number of days that will elapse between the date an order is written and the delivery to the store or warehouse from the supplier. |
Replenishment Inner Pack Size |
Contains the break pack size for the item for the supplier. |
Replenishment Pack Size |
Contains the quantity that orders must be placed in multiples of for the supplier of the item. |
Replenishment Pallet Tier Units |
Contains the number of shipping units (cases) that make up one tier of a pallet. Multiply TIER x HEIGHT to get total number of units (cases) for a pallet. |
Replenishment Pallet Height |
Contains the number of tiers that make up a complete pallet (height). |
Replenishment Rounding Level |
This field determines how order quantities will be rounded to Case, Layer and Pallet. |
Replenishment Inner Rounding Threshold |
Contains the Inner Rounding Threshold value. |
Replenishment Case Rounding Threshold |
Contains the Case Rounding Threshold value. |
Replenishment Layer Rounding Threshold |
Contains the Layer Rounding Threshold value. |
Replenishment Pallet Rounding Threshold |
Contains the Pallet Rounding Threshold value. |
Replenishment Service Level Type |
Contains the Service Level Type (Simple Sales or Standard) that will drive the safety stock calculation algorithm. |
Replenishment Zero SOH Transfer Flag |
Contains a value of N to indicate a transfer should be created even though the warehouse does not have enough stock on hand. |
Replenishment Multiple Per Day Flag |
Contains a value of N to indicate if an item can be replenished multiple times per day at the location. |
Replenishment Supplier Lead Time Flag |
Indicates if the supplier lead time will be considered in the calculation of time supply order points and order up to point. |
Replenishment Supplier Num |
Contains the numeric identifier of the supplier from which the specified location will source the replenishment demand for the specified item location. |
Replenishment Country Code |
Contains the country code of the supplier country that will be used to supply the replenishment demand for the specified item location. |
Replenishment Review Cycle |
Contains the number representing when the specified item location will be reviewed for replenishment. Valid values are 0-14. A 0 represents a weekly review cycle, a 1 represents a daily review cycle, a 2 represents a review cycle of every 2 weeks, a 3 represents a review cycle of every 3 weeks, etc. |
Replenishment Stock Category |
Contains the sourcing strategy for the item/location relationship. |
Replenishment Source WH |
Contains the numeric identifier of the warehouse through which the specified item will be sourced, or will crossdock to the specified store. |
Replenishment Activate Date |
Contains the date on which the item location will start to be reviewed for replenishment. |
Replenishment Deactivate Date |
Contains the date at which time the item location will no longer be reviewed for replenishment. |
Replenishment Pres Stock |
Contains the minimum amount of stock that needs to be on store shelves. |
Replenishment Demo Stock |
Contains the amount of stock that cannot be sold as new and is not counted as part of inventory in the replenishment calculations. |
Replenishment Min Stock |
Contains the required minimum number of units available for sale. |
Replenishment Max Stock |
Contains the required maximum number of units available for sale. |
Replenishment Service Level |
Contains the required measure of the probability that demand is satisfied from on hand inventory. |
Replenishment Pickup Leadtime |
Contains the expected number of days required to ship the item from the supplier to the initial receiving location. |
Replenishment WH Leadtime |
Contains the expected number of days required to move the item from the warehouse to the store defined on this record. |
Item Loc Deposit Code Flag |
Indicates whether a deposit is associated with this item at the location. |
Item Loc Proportional Tare Pct |
Contains the value associated of the packaging in items sold by weight at the location. |
Item Loc Fixed Tare Value |
Contains the value associated of the packaging in items sold by weight at the location. |
Item Loc Fixed Tare UOM |
Contains the unit of measure value associated with the tare value. |
Item Loc Return Policy |
Contains the return policy for the item at the location. |
Item Loc Stop Sale Flag |
Indicates that sale of the item should be stopped immediately at the location |
Item Loc Report Code |
Contains the code to determine the reports the location should run. |
Item Loc Flex Attr 1 - 25 |
Item Location level custom flex attributes matching standard CFA datatypes and formatting |
Promotion
A promotion is an attempt to stimulate the sale of particular merchandise. This can be accomplished by temporarily reducing its price, advertising it, or linking its sale to offers of other merchandise at reduced prices or free. A promotion can take place for many different reasons, such as the desire to attract a certain type of customer, increase sales of a particular class of merchandise, introduce new items, or gain competitive advantage. Tracking of sales and demand by promotion allows retailers to assess the success in attracting customers to purchase items that are placed on promotion.
A single promotion can be part of a larger effort or event. Several promotions can be associated with an event. For example, a summer sale event might consist of multiple promotions.
There are a number of formats in which a promotion can be offered. Some common examples of these formats are as follows:
-
Get a specific percent off the price of an item
-
Buy a certain quantity of an item and get a certain amount off the total purchase value
-
Buy a certain item and get a discount on another item
-
Get free shipping and handling
Typically, a promotion on an item is not applied universally. It might be triggered only for certain stores, for certain media, for certain customer types, or for certain offer coupons. The type of circumstance that triggers a promotion is called the promotion trigger type. In a brick-and-mortal market, a promotion is always triggered by the store. In a direct-to-consumer market, there can be different trigger types such as Source Code, Media Code, Selling Item Code, or Customer Type. One promotion can be triggered by only one promotion trigger type.
It is also possible that the retailer has multiple sources of promotions both internal and external to their Oracle applications. RI has the ability to source promotions directly from the RPM and Customer Engagement applications. It can also accept external promotions through a separate interface. Regardless of the source of the promotion data, it is assumed that the retailer will ensure uniqueness of the promotions across all source systems, such that the sales transactions occurring under a specific promotion can be correctly identified and reported on.
Table 5-16 lists the attributes of the Promotion dimension.
Table 5-16 Promotion Dimension Attributes
Attribute | Definition |
---|---|
Promo Source |
Identifier for the source system of the promotion data, in cases where multiple source systems are generating promotions (such as RPM, CE, and OMS). |
Promo ID |
Unique ID from the source system that identifies a promotion. A promotion is an intentional grouping of promotion offers. A promotion can only be a child of a single promotion event. Multiple promotions within a promotion event can have overlapping timeframes within the event. |
Promo Name |
Name of a promotion. A promotion is an intentional grouping of promotion offers. A promotion can only be a child of a single promotion event. Multiple promotions within a promotion event can have overlapping timeframes within the event. |
Promo Description |
Description of a promotion. A promotion is an intentional grouping of promotion offers. A promotion can only be a child of a single promotion event. Multiple promotions within a promotion event can have overlapping timeframes within the event. |
Promo Start Date |
This represents the start date of a promotion. This value is determined by the timeframes of the promotion offers within a promotion. |
Promo End Date |
This represents the end date of a promotion. This value is determined by the timeframes of the promotion offers within a promotion. |
Promo Offer ID |
Unique ID from the source system that identifies a promotion offer. A promotion offer is an intentional grouping of promotion details within a promotion. A promotion offer is always a child of a single promotion, which is a child of a single promotion event. Multiple offers within a promotion can have overlapping timeframes within the promotion. |
Promo Offer Name |
Name of a promotion offer. A promotion offer is an intentional grouping of promotion details within a promotion. A promotion offer is always a child of a single promotion, which is a child of a single promotion event. Multiple offers within a promotion can have overlapping timeframes within the promotion. |
Promo Offer Start Date |
This represents the start date of a promotion offer. Individual offers may have overlapping timeframes within a promotion. |
Promo Offer End Date |
This represents the end date of a promotion offer. Individual offers may have overlapping timeframes within a promotion. |
Promo Offer Type |
Promotion offer type that is applied to a promotion offer, with values such as 0 (simple item offer), 1 (simple transaction offer), and 2 (buy/get transaction offer). A promotion offer type is the method to implement a price discount, reward, or credit/financing. |
Coupon Code |
A static number or code used to identify a set of coupons associated with an offer. May be used to generate serialized coupon numbers that will be issued to customers or redeemed at the point of sale. |
Target Name |
Describes the customer segment that is targeted for a particular promotion. The promotion offers may only be delivered to the customers in the specified segments. |
Promo Detail ID |
Identifier of a detail of a promotion offer, usually either a condition or reward attached to the offer, but may include other details such as rules, constraints, or limits on the offer. |
Promo Detail Type |
Identifies the type of detail record associated with an offer, with values such as "C" for condition or "R" for reward. The available types will vary depending on the source of promotion data. |
Promo Condition/Reward Type |
Identifies the type of condition or reward rule, such as Buy/Spend X or Give Percent Off. |
Promo Condition/Reward Amount |
Identifies the amount associated with a condition or reward, such as the price change amount or percent off. |
Promo Condition/Reward Quantity |
Identifies the number of units associated with the condition or reward, such as the quantity to buy before getting the reward, or the quantity eligible for discount. |
Promo Condition/Reward UOM |
The unit of measure for the condition or reward quantity. |
Promo Flex Attrib 1 through Promo Flex Attrib 60 |
60 flexible promotion fields are available for externally sourced promotion data, which may contain a variety of promotion-specific attributes and values. |
Promo Campaign Desc |
Description of a promotion campaign, which is an intentional grouping of promotions. A single campaign may contain many promotions. |
Promo Campaign End Date |
Date until which the campaign record is effective. |
Promo Campaign ID |
Unique display ID from the source system that identifies a promotion campaign. |
Promo Campaign Start Date |
Date from which the campaign record is effective. This represents the start date of a promotion campaign. |
Promo Campaign Strategy |
The business or marketing strategy for a promotion campaign. This is a generic description field that allows for additional details to be stored as part of the campaign or promotion. |
Promo Campaign Theme |
The theme or categorization of a promotion campaign. This is a generic description field that allows for additional details to be stored as part of the campaign or promotion. |
Promo Campaign Type |
The type of promotion campaign. This is a generic description field that allows for additional details to be stored as part of the campaign or promotion. |
Promo Offer Cancel Date |
The date that an offer was cancelled. |
Promo Offer Cancel Reason Code |
The reason code used to cancel an offer. |
Promo Offer Exclusive Discount Flag |
Indicates if the discount must be exclusive of any other offer or not. Valid values in (Y, N). |
Customer
Knowledge of the customers' preferences and buying behavior allows the retailer to increase sales through up-selling efforts, target customers for promotions, and prevent defection to competitors. In Retail Insights, customer information and transaction history can be used to segment the customer base by one of several methods. This analysis yields important information about who the best customers are, and the affinity of customer segments to particular products.
![Customer segments Customer segments](img/customer_hier.png)
Customer addresses and personal information will be sourced from an external customer management system or from Oracle Retail Customer Engagement (ORCE). Oracle Retail Insights will provide Source Independent Load interfaces (W_PARTY_PER_DS and W_RTL_PARTY_PER_ATTR_DS) to feed customer master data, addresses, and other customer attributes from the Oracle Retail Insights staging tables to the customer dimension. Some or all of this data could be loaded into the CRM system by Oracle Data Cloud, and then passed down from there to RI.
If detailed customer data is not available, it is also possible to seed customer numbers directly from sales transactions (if the POS system is capable of providing such identifiers). This allows for a simple form of customer analysis that uses just the unique identifiers from the point of sale to analyze your data.
Customer addresses and personal information will be sourced from an external customer management system or from Oracle Retail Customer Engagement (ORCE). Oracle Retail Insights will provide Source Independent Load interfaces (W_PARTY_PER_DS and W_RTL_PARTY_PER_ATTR_DS) to feed customer master data, addresses, and other customer attributes from the Oracle Retail Insights staging tables to the customer dimension. Some or all of this data could be loaded into the CRM system by Oracle Data Cloud, and then passed down from there to RI.
Table 5-17 lists the attributes of the Customer dimension.
Table 5-17 Customer Dimension Attributes
Attribute | Definition |
---|---|
Customer Individual Gender Code |
Code for an individual's gender. |
Customer Individual Gender |
An individual's gender, for example: male, female, not declared. |
Customer Individual Marital State Code |
Code for an individual's marital state (marital status). |
Customer Individual Marital State |
An individual's marital state (marital status), for example: single, married, divorced, widowed. |
Annual Income |
Customer's annual income. |
Education Background Code |
Code for the education background code of the customer. |
Recency Category |
Recency category of the customer. |
Customer Primary City |
Customer primary city of residence. |
Customer Primary State Code |
Code for customer primary state. |
Customer Primary State |
Customer primary state. |
Customer Primary Postal Code |
Customer primary postal code. |
Customer Primary Country |
Customer primary country. |
Address ID |
Customer address ID. |
Churn Score |
Score indicating the likelihood of customer retention. |
Customer Status Code |
Status code for a customer. |
Customer Status Code Description |
Status of a customer, for example: potential, first-time, regular. |
Education Background |
Education background of a customer, for example: bachelor's degree, master's degree). |
Ethnicity Code |
Code for the ethnicity of the customer, for example: H = Hispanic, G = German, U = Unknown. |
Nationality Code |
Code for the nationality of the customer. |
Customer Type |
Type of customer |
Nationality |
Nationality of the customer. |
Occupation Code |
Code for the occupation of the customer. |
Occupation |
Occupation of the customer. |
Prospect Flag |
Flag to indicate someone who has visited or shopped online, but has not purchased. The retailer may have some information about such prospect customers. |
Recency Category Code |
Code indicating how recently the customer purchased. |
Recency Category |
Score indicating how recently the customer purchased. |
Frequency Category Code |
Code indicating how often a customer purchases. |
Frequency Category |
Score indicating how often a customer purchases. |
Monetary Category Code |
Code indicating the monetary value of a customer's purchase. |
Monetary Category |
Score indicating the monetary value of customer's purchase. |
RFM Categories Code |
Code indicating the customer's total RFM Score. |
RFM Categories |
Score indicating the combined recency, frequency, and monetary value of a customer. |
Churn Score Range Sort |
Sort range for churn score. |
Churn Score Range |
Range of churn score. |
Customer Address Type Code |
Code for the type of customer address. |
Customer Address Type |
Type of address, for example: billing address, delivery address. |
Years at Address |
Number of years for which the specific address has been in use. |
Customer Address Class Code |
Code indicating the class of the address. |
Customer Address Class |
Class of address, for example: residential address, commercial address. |
Primary Address Flag |
Flag that indicates if the address can be used for all customer communication and reporting purposes. |
City |
Indicates the City. |
State Code |
State code. |
State |
State. |
Postal Code |
Postal code. |
Country |
Indicates the Country. |
Opt Out Flag |
Flag indicating if the address or e-mail address may or may not be marketable. |
Customer Birth Month |
Customer month of birth. |
Customer Birth Year |
Customer year of birth. |
Age |
Indicates the age of customer based on year and month of birth. |
Age Range |
This demographic attribute for customer represent the range in which his age lies. This attribute will be typically configured by user based on their business needs. |
Customer Income Band |
Range in which customer's income falls. |
Ethnicity Name |
Ethnicity of the customer, for example: H = Hispanic, G = German, U = Unknown. |
Dwelling Status |
The dwelling status classifies all dwellings according to whether they are occupied, unoccupied, or under construction during the time period of the data collection. |
Dwelling Size |
This attribute lists the floor area for a dwelling unit expressed in the standard unit of measure. |
Dwelling Type |
This attribute lists the dwelling unit occupied by, or intended for occupancy by, one household. Examples include: detached house, flat, apartment, tenement, trailer park, etc. |
Dwelling Tenure |
The dwelling tenure attribute refers to the period of the occupancy of a private household in a dwelling. It is expressed in number of years. |
Religion |
This attribute identifies a customer's religion. |
Religion Code |
This attribute is the code for a customer's religion. |
Social Class |
Status hierarchy by which customers are classified on the basis of esteem and prestige. Values - Upper Class, Upper Middle class, Lower middle class, Upper lower class, lower class. |
Social Class Code |
Code indicating the status hierarchy by which customer are classified on the basis of esteem and prestige. |
Family Lifecycle |
Indicates the family lifecycle of the customer, Examples include: bachelor, married with no children (DINKS: Double Income, No Kids), full-nest, empty-nest, or solitary survivor. |
Family Lifecycle Code |
Code indicating the family lifecycle of the customer. |
Metro Area Size |
Size of population in the metro area where the customer lives. |
Activity |
Activity based on AIO survey. |
Activity Code |
Activity code based on AIO survey. |
Attitude |
This attribute indicates the customer's attitude. |
Attitude Code |
Code indicating customer's attitude. |
Benefit Sought |
The main benefits the customer looks for in a product. For example, health, taste, and so on. |
Benefit Sought Code |
Code based on benefits sought. |
Climate |
This indicates the weather patterns for the customer's area. |
Climate Code |
The code indicates the weather patterns. |
Customer Lifetime Value |
This attribute is a forecast of customer profitability. |
Customer Lifetime Value Code |
This is the code for customer lifetime value. |
Customer Lifetime Value Range |
This is the range in which the customer's value falls, for example, Very High/High/Medium/Low |
Customer Profitability Code |
This is the code for customer profitability. |
Customer Profitability |
This attribute is a historical analysis of customer profitability, for example, High/Medium/Low. |
Interest |
This attribute indicates interest based on AIO survey. |
Interest Code |
Code indicating customer's interests. |
Occasion |
This attribute indicates when a customer tends to purchase or consume the product. It can be holidays and events that stimulate purchases |
Occasion Code |
Code indicating when customer tends to purchase or consume the product. |
Opinion |
This attribute indicates (but is not limited to) customer's political opinions, environmental awareness, sports, arts and cultural issues. |
Opinion Code |
Code indicating customer opinions. |
Readiness to Buy |
This attribute indicates customer buying mindset. |
Readiness to Buy Code |
Code indicating the customer buying mindset. |
Hours Worked |
The number of hours the customer works. |
Age of Kids |
This attribute will contain predefined ranges for a customer. The generic range of values will be Range - 0-3, 3-6, 6-10, 11-18, 0-16. |
Population Density |
Population density of the customer's area. Possible values can be urban, suburban, or rural. |
No of Teens |
This attribute is the number of teens in the customer's household. |
Usage Rate |
This indicates light, medium and heavy product usage by the customer. |
Years Primary Store |
This attribute is the number of years the customer has shopped at their primary grocery store. |
Customer Active Flag |
Flag indicating if the customer is active. |
Citizenship |
This indicates the citizenship status of the customer. |
Citizenship Code |
Code indicating the citizenship status of the customer. |
Customer Address Effective Date |
The date a customer's primary address is effective from. |
Annual Revenue |
A customer's annual revenue or net worth. |
Call Flag |
Flag indicating if this customer can be called. |
Contact Active Flag |
Flag indicating if this contact is active. |
Contact Business Name |
Name of the business or organization associated with this customer. |
Contact Formed Date |
The date that this customer's information was first recorded. |
Customer End Date |
The effective end date for the customer. |
Customer Since Date |
The effective start date for the customer. |
Customer Birth Date |
The birth date of the customer. |
Customer Email Address |
The primary email address of the customer. |
Customer Phone Number |
The primary phone number of the customer. |
Customer End Date |
The effective end date for the customer. |
Customer First Name |
The first name or given name of a customer. |
Customer Middle Name |
The middle name of a customer. |
Customer Last Name |
The last name or surname of a customer. |
Customer Name Prefix |
The prefix on a customer name. |
Customer Name Suffix |
The suffix on a customer name. |
Customer Nickname |
The nickname of a customer. |
Customer Full Name |
The full name of the customer. |
Customer Home Location |
The name or number of the customer's chosen home or preferred retail location. |
Customer Signup Location |
The name or number of the location where the customer signed up or had their data entered into the system. |
Last Transaction Date |
The last recorded transaction date for the customer, as registered in source system for the customer data. |
First Transaction Date |
The first recorded transaction date for the customer, as registered in source system for the customer data. |
Enterprise Flag |
Flag indicating if this customer is an individual or an organization. |
Suppress Call Flag |
Flag indicating if this customer should not be contacted by phone. |
Suppress Email Flag |
Flag indicating if this customer should not be contacted by email. |
Suppress Fax Flag |
Flag indicating if this customer should not be contacted by fax. |
Suppress Mail Flag |
Flag indicating if this customer should not be contacted by mail. |
Customer Oracle ID |
The identifier assigned by Oracle Data Cloud to track the data for a known individual across systems. |
Customer Oracle Address ID |
The identifier assigned by Oracle Data Cloud to track the data for a known household across systems. |
Customer Segmentation
Customer segmentation is the process of identifying and classifying customers according to their current and future value to your business. Segmentation identifies your most and least valuable customers based on how frequently and recently customers have purchased, and the monetary value and profitability of their business. You can use this information to establish programs and policies that protect your most valued customers against defecting to a competitor. In addition, segmentation assists the marketing analyst in identifying customers whose purchasing history indicates the potential to become more profitable, as well as those who contribute little value to your business.
Your best customers are those who:
-
Have purchased goods or services from you recently
-
Purchase from you frequently
-
Spend a large amount of money
Table 5-18 lists the attributes of the Customer Segment dimension.
Table 5-18 Customer Segment Dimension Attributes
Attribute | Definition |
---|---|
Customer Segment Name |
Name of the customer segment. |
Customer Segment Type |
Indicates the type of customer segment. |
Customer Segment Age Range |
This attributes indicates the age group for customer segment. This attribute can be used by marketers devise, and endorse items specifically for the needs and perceptions of age groups. |
Customer Segment Gender Code |
The code indicating gender of customer segment. |
Customer Segment Gender |
This attributes defines the gender of customer segment. Gender drives marketing decisions for categories like clothing, hairdressing, magazines and toiletries and cosmetics, and so on. |
Customer Segment Family Size |
Indicates the Family Size for a demographics based segment. |
Customer Segment Generation Code |
Generation code for creating demographic segments. |
Customer Segment Generation |
Generation for creating demographic segments. Possible value can be Baby-boomers, Generation X ans so on. |
Customer Segment Annual Income Range |
The attribute defines target customer segment income range. Retailers will use this attribute to potentially target affluent customers with luxury goods and convenience services. Low Income range customers may be targeted with every day value or discounted items and services. |
Customer Segment Occupation Code |
Occupation code to classify customer into occupational categories. |
Customer Segment Occupation |
Occupation for purposes of segmenting into occupational categories. |
Customer Segment Education Background Code |
Educational background code to classify customer into different education categories. |
Customer Segment Education Background |
Educational background to classify customer into different education categories. |
Customer Segment Ethnicity Code |
The code to identify ethnic groups to find customers with special interests. |
Customer Segment Ethnicity |
This attribute identifies ethnic groups to find customers with special interests. |
Customer Segment Nationality Code |
Nationality code for the purpose of demographics based segmentation. |
Customer Segment Nationality |
This attribute identifies nationality to find customers with special interests. |
Customer Segment Religion Code |
Religious code for the purpose of demographics based segmentation. |
Customer Segment Religion |
This attribute identifies religious groups to find customers with special interests. |
Customer Segment Social Class Code |
Code indicating the status hierarchy by which customer are classified on the basis of esteem and prestige. |
Customer Segment Social Class |
Status hierarchy by which customer are classified on the basis of esteem and prestige. Values - Upper Class, Upper Middle class, Lower middle class, Upper lower class, lower class. |
Customer Segment Family Lifecycle Code |
Code indicating the family lifecycle of the segment. |
Customer Segment Family Lifecycle |
Indicates the family lifecycle of the segment, Examples include: bachelor, married with no children (DINKS: Double Income, No Kids), full-nest, empty-nest, or solitary survivor. |
Customer Segment Region Code |
Region code for the purpose of geographic based segmentation. Possible value can be continent, country, state, or even neighborhood. |
Customer Segment Region |
Region value for the purpose of geographic based segmentation. Possible value can be continent, country, state, or even neighborhood. |
Customer Segment Metro Area Size |
Size of population for creating geographic based customer segments. |
Customer Segment Population Density |
Population density for creating geographic customer segments, Possible values can be urban, suburban, or rural. |
Customer Segment Climate Code |
The code indicates the weather patterns. |
Customer Segment Climate |
This indicates the weather patterns for the purpose of geographic based segmentation. |
Customer Segment Benefit Sought Code |
Benefits sought code for purposes of segmentation based on benefits sought. |
Customer Segment Benefit Sought |
The main benefits consumers look for in a product. For example, health, taste, and so on. |
Customer Segment Usage Rate |
This indicates light, medium and heavy product usage segments. |
Customer Segment Readiness To Buy Code |
Code indicating the customer segment's buying mindset. |
Customer Segment Readiness To Buy |
This attribute indicates customer segment's buying mindset. |
Customer Segment Occasion Code |
Code indicating when segment tends to purchase or consume the product. |
Customer Segment Occasion |
This attribute indicates when segment tends to purchase or consume the product. It can be holidays and events that stimulate purchases |
Customer Segment Activity Code |
Activity code based on AIO survey. |
Customer Segment Activity |
Activity based on AIO survey. This attribute can be used to create Psychographic segments. |
Customer Segment Interest Code |
Code indicating customer segment's interests. |
Customer Segment Interest |
Indicates interest based on AIO survey. This attribute can be used to create Psychographic segments. |
Customer Segment Opinion Code |
Code indicating customer segment's opinions. |
Customer Segment Opinion |
This attribute indicates (but is not limited to) customer segments political opinions, environmental awareness, sports, arts and cultural issues. |
Customer Segment Attitude Code |
Code indicating customer segment's attitude. |
Customer Segment Attitude |
This attribute indicates the customer segment's attitude. This can be used to create Psychographic segments. |
Customer Segment Value Code |
Code indicating customer segment's value. |
Customer Segment Value |
This attribute indicates the customer segment's value. This can be used to create Psychographic segments. |
Customer Segment Source Type |
This attribute indicates whether the customer segment was based on customers or households. |
Customer Segment Allocation
The customer segment allocation folder under Customer Insights in Oracle Retail Insights enables analysis of the association of a retailer's customer segments to its merchandise and organization hierarchies. That association enables the targeting of specific customer segments with promotions by indicating in what locations and what products a customer segment is most likely to purchase. Note that this is purely for dimensional reporting.
For example, if a merchant sees a strong association between customer segment: farmer; subclass: plows; locations: Midwest Region, she will want to ensure that she has an extended assortment of the plows subclass for that Region. That way she is driving sales as well as meeting or exceeding customer expectations.
The Customer Segment Allocation association itself is done by external systems and interfaced to Oracle Retail Insights. The association level needs to be predefined in the configuration file to determine at what level of the merchandise and organization hierarchy customer segment allocation should be tracked. For example, a retailer could configure association at subclass and store level, or department and region level, or whatever levels are appropriate for their organization. Regardless of what level is chosen during configuration, it is not recommended to drill up or down on those merchandise or organization hierarchy levels during reporting, as that will provide incorrect results.
Customer Behavior
Retail Insights exposes a set of metrics describing customer behavior, which are calculated using the Retail AI Foundation Cloud Services. These metrics are calculated using customer-linked transaction data. In addition to helping understand how customers have behaved in past, these metrics can also help predict future behavior.
Table 5-19 Customer Behavior Metrics
Attribute | Definition |
---|---|
Customer Latency |
The number of days between each of a customer's transactions sales or return. |
Customer Lifespan |
The time between a customer's first and last purchase. |
Customer RFM |
The RFM (recency, frequency, monetary) score determines quantitatively which customers are the best ones by examining how recently a customer has purchased (recency), how often the customer purchases (frequency), and how much the customer spends (monetary). |
Customer Projected Next Purchase Date |
Prediction of the next likely customer purchase date. |
Customer Location Loyalty |
How loyal are customers to a specific location? A value of 100% indicates that they always shop at a particular location. |
Customer Style Loyalty |
How loyal are customers to a particular style? A value of 100% indicates that they always prefer one specific style. |
Customer Color Loyalty |
How loyal are customers to a particular color? A value of 100% indicates that they always prefer one specific color. |
Customer Brand Loyalty |
How loyal are customers to a particular brand? A value of 100% indicates that they always prefer one specific brand. |
Customer Price Efficiency Loyalty |
How efficient are customers in getting a promotion price? A value of 100% indicates that the customer always buys items on promotions or is very efficient in obtaining a good price. |
Customer Projected Lifetime Value |
The projected total lifetime value of a customer, which is modeled by predicting the number/value of future purchases a customer will make and combining that with their purchase history. |
Customer Loyalty Scores
Loyal customers are among the retailer's most precious assets. A loyal customer contributes to your business on a regular basis over an extended period of time and almost always ranks as one of your best customers.
When used in conjunction with RFM analysis, these metrics allow you to assess the importance of various items to your best customers.
In Retail Insights, customer's loyalty scores are tracked at individual customer as well as customer segment level for various grains of promotion, calendar, style, brand and merchandising hierarchy.
Loyalty score attributes indicate the likelihood of purchase of merchandise by a given customer or customer segment for the supported attributes.
Table 5-20 lists the attributes of the Customer Loyalty dimension.
Table 5-20 Customer Loyalty Score Dimension Attributes
Attribute | Definition |
---|---|
Seg Dept Loyalty Score |
Customer Segment's loyalty scores for Department, Location and Day. This score is an indication of customer segment's experience of purchase of products or services. |
Seg Dept Loyalty Score by Promo |
Customer segment‘s loyalty score for Department, Location and Day by Promotion Component Type. This score is an indication of customer segment's experience of purchase of products or services. |
Seg Class Loyalty Score |
Customer segment‘s loyalty score for Class, Location and Day. This score is an indication of customer segment's experience of purchase of products or services. |
Seg Class Loyalty Score by Promo |
Customer segment‘s loyalty score for Class, Location and Day by Promotion Component Type. This score is an indication of customer segment's experience of purchase of products or services. |
Seg Subclass Loyalty Score |
Customer segment ‘s loyalty score for Subclass, Location and Day. This score is an indication of customer segment's experience of purchase of products or services. |
Seg Subclass Loyalty Score by Promo |
Customer segment‘s loyalty score for Subclass, Location and Day by Promotion Component Type. This score is an indication of customer segment's experience of purchase of products or services. |
Seg Style Brand Loyalty Score |
Customer segment‘s loyalty score for Style, Brand, Location and Day. This score is an indication of customer segment's experience of purchase of products or services. |
Seg Style Brand Loyalty Score by Promo |
Customer segment‘s loyalty score for Style, Brand, Location and Day by Promotion Component Type. This score is an indication of customer segment's experience of purchase of products or services. |
Cust Dept Business Month Loyalty Score |
Customer's loyalty score for Department, Location and Business Month. This score is an indication of customer's experience of purchase of products or services. |
Cust Dept Business Month Loyalty Score by Promo |
Customer's loyalty score for Department, Location and Business Month by Promotion Component Type. This score is an indication of customer's experience of purchase of products or services. |
Cust Class Business Month Loyalty Score |
Customer's loyalty score for Class, Location and Business Month. This score is an indication of customer's experience of purchase of products or services. |
Cust Class Business Month Loyalty Score by Promo |
Customer's loyalty score for Class, Location and Business Month by Promotion Component Type. This score is an indication of customer's experience of purchase of products or services. |
Cust Style Business Month Brand Loyalty Score |
Customer's loyalty score for Style, Brand, Location and Business Month. This score is an indication of customer's experience of purchase of products or services. |
Cust Style Business Month Brand Loyalty Score by Promo |
Customer's loyalty score for Style, Brand, Location and Business Month by Promotion Component Type. This score is an indication of customer's experience of purchase of products or services. |
Cust Dept Greg Month Loyalty Score |
Customer's loyalty score for Department, Location and Gregorian Month. This score is an indication of customer's experience of purchase of products or services. |
Cust Dept Greg Month Loyalty Score by Promo |
Customer's loyalty score for Department, Location and Gregorian Month by Promotion Component Type. This score is an indication of customer's experience of purchase of products or services. |
Cust Class Greg Month Loyalty Score |
Customer's loyalty score for Class, Location and Gregorian Month. This score is an indication of customer's experience of purchase of products or services. |
Cust Class Greg Month Loyalty Score by Promo |
Customer's loyalty score for Class, Location and Gregorian Month by Promotion Component Type. This score is an indication of customer's experience of purchase of products or services. |
Cust Style Greg Month Brand Loyalty Score |
Customer's loyalty score for Style, Brand, Location and Gregorian Month. This score is an indication of customer's experience of purchase of products or services. |
Cust Style Greg Month Brand Loyalty Score by Promo |
Customer's loyalty score for Style, Brand, Location and Gregorian Month by Promotion Component Type. This score is an indication of customer's experience of purchase of products or services. |
Household
Table 5-21 lists the Customer Household attributes supported by Retail Insights.
Table 5-21 Customer Household Attributes
Attribute | Definition |
---|---|
Household Income |
Indicates the household income. |
Household Class Id |
Code for household class. |
Household Class |
Household class for a customer. Possible values are Nuclear/Joint/Single Parent, Double Income/Single Income. |
Supplier
A supplier is a company that supplies goods or a service to another company. In the retail industry, the supplier supplies the retailer with goods, and the retailer sells those goods to customers. The same item can be supplied by multiple suppliers. As a result, a primary supplier is assigned to an item. When reporting by supplier, all items that are sold are attributed to the primary supplier.
Retail Insights supports multiple supplier sites for each supplier. A supplier site is the location from which the supplier ships merchandise. Terms and conditions can be defined at the supplier site level.
The attributes in the Supplier dimension allow the business analyst to rate supplier performance based on delivery history and the quality of products. This information can be used to identify suppliers whose performance is below standard, as well as those who are in compliance with expectations.
The following is the hierarchy of the Supplier dimension.
![Supplier dimension hierarchy example Supplier dimension hierarchy example](img/supplier_hier.png)
Table 5-22 lists the attributes of the Supplier dimension.
Table 5-22 Supplier Dimension Attributes
Attribute | Definition |
---|---|
Supplier Number |
Unique ID from the source system that identifies a supplier. |
Supplier |
Trading name of a supplier. |
Supplier Parent |
Supplier level. For a supplier site, this value contains the parent supplier number. Sites represent physical locations from which suppliers ship. A null value indicates that this is a supplier. |
QC Flag |
Indicator of whether orders from a supplier require quality control, with values of “Y" for yes (unless overridden by the user when the order is created) and “N" for no, indicating that no quality control is required for this supplier unless indicated by the user during order creation. Quality control for suppliers involves checking the quality of the merchandise received (for example, damaged or over-ripened) and whether received shipments contain the quantity on the receiving label. |
VMI Status |
Status with which vendor-managed inventory (VMI) purchase orders are created, with values of “A" for approved and “W" for worksheet. A null value indicates that the supplier is not a VMI supplier. A VMI supplier does inventory planning for the retailer. A VMI supplier is also responsible for replenishing and reordering the retailer's supply. |
Pre Mark Flag |
Indicator of whether a supplier's premarked inventory is in separate containers for cross-dock shipping to stores, with values of “Y" for yes and “N" for no. |
EDI Flag |
Indicator of whether a supplier electronically sends advance shipping notices (ASN), with values of “Y" for yes and “N" for no. |
Intl Currency Flag |
Indicator of whether a supplier operates in the same currency as the retailer's primary currency, with values of “Y" for yes and “N" for no. |
Currency Code |
Code of the currency that a supplier uses for business transactions. |
Supplier Status |
Indicator of whether supplier is currently active, with values of “A" for active and “I" for inactive. |
Supplier Start Date |
Date the supplier record was first inserted into the data warehouse. |
Supplier End Date |
Date the supplier was deleted from the source system. |
Currency Description |
Description of the currency that a supplier uses for business transactions. |
Supplier Name 2 |
Secondary name of a supplier. |
Primary Flag |
Indicator of whether the supplier is the primary supplier for the item, with values of “Y" for yes and “N" for no. Each item has only one primary supplier. This field does not apply to sub-transaction-level items. |
Pack Size |
Number of items in a pack. Orders for the item must be placed in multiples of this quantity. |
In Order Qty |
Minimum quantity of the item that can be ordered at one time. |
Max Order Qty |
Maximum quantity of the item that can be ordered at one time. |
Lead Time |
Number of days needed between the date an order for an item is written and the delivery from the supplier to the store or warehouse. |
Pickup Lead Time |
Number of days needed between the date an item leaves a supplier and the delivery to an initial receiving location. |
Inner Pack Size |
Break pack size for an item. A break pack is a pack within a larger container. |
Supplier Trait ID |
Unique ID from the source system that identifies a supplier trait. A supplier trait is an attribute of a supplier, used to group suppliers with similar characteristics. |
Supplier Trait Desc |
Description of a supplier trait. A supplier trait is an attribute of a supplier, used to group suppliers with similar characteristics. |
Supplier Backorder Ind |
Indicates if backorders or partial shipments will be accepted. |
Supplier Default Lead Time |
Holds the default lead time for the supplier. The lead time is the time the supplier needs between receiving an order and having the order ready to ship. |
Supplier Delivery Policy |
Contains the delivery policy of the supplier. |
Supplier Final Destination Ind |
Indicates if the supplier can ship to final destinations as per allocation or not. |
Supplier Return Allowed Ind |
Indicate if the supplier or supplier site accepts returns for the items associated with them. |
Retail Type
The Retail Type attribute represents the price type at which items were sold or held as inventory. There are seven values for Retail Type:
-
Regular
-
Promotional
-
Clearance
-
Employee
-
Intercompany
-
Book Transfer
-
Normal Transfer
This attribute segments a number of business measurements by price type, including sales and profit, stock position and value, markdowns, markups, transfers and competitor pricing. This information is valuable when determining a pricing strategy, analyzing inventory value, or evaluating a competitor.
It is important to note that inventory data is not held for all values of Retail Type. In RMFCS, stock on hand is considered to be in clearance or non-clearance status. In Retail Insights, non-clearance inventory is associated with the Regular value of Retail Type, while clearance inventory is associated with the Clearance value. Similarly, transfers can only be classified using one of the (I, B, N) values.
Table 5-23 describes the Retail Type attribute.
Table 5-23 Retail Type Attribute
Attribute | Definition |
---|---|
Retail Type |
Price type of an item. Values are as follows:
If an item is on promotion and clearance at the same time, the retail type is “C". |
Product Season
Product season functionality allows you to categorize each item according to different seasons, and phases within seasons. For example, you can assign a season of "Spring" to a group of items, according to the supplier's deliveries of fashion items. Those relationships can be further broken down into the phases, such as "Spring I" and "Spring II." These item-phase-season relationships are then loaded into Retail Insights. You can query sales and inventory data, for example, based on all items in the spring season, or just items in the Spring II phase.
Note:
On a given day, an item can belong to more than one season and more than one phase within a season. Seasonality is designed to group by item/location/day to avoid double-counting.
Retail Insights provides two versions of Season Phase attributes to support different business practices. The first version is called Season Phase Operational attributes. These attributes should be used when your merchandising system is managed to align buying and selling activities to fixed periods of time, such as a set of items being sold only during the Spring 2017 season. When using these attributes in reports, the start and end dates of the seasons and phases will be used to limit the data returned, similar to using calendar attributes. For example, if you want to see the net sales and profit for the Spring 2017 season, you could use the operational Season ID attribute to limit results to the effective dates of that season (without worrying about what those dates are).
The second set of attributes is called Season Phase Planning. These attributes should be used when a season or phase is used informationally, such as to describe when the item will first be received into stores, but not necessarily the window of time the item is selling for. Using these attributes will not limit reports to the start and end dates, it is more similar to using item or location attributes.
The following is the hierarchy of the Product Season dimension.
![Product season dimension hierarchy Product season dimension hierarchy](img/seasons_hier.png)
Table 5-24 lists the attributes of the Product Season dimension.
Table 5-24 Product Season Dimension Attributes
Attribute | Definition |
---|---|
Season ID |
Unique ID from the source system that identifies a season. A season is a designated timeframe that may or may not correspond with the Gregorian or business/fiscal calendars. |
Season Desc |
Description of a season. A season is a designated timeframe that may or may not correspond with the Gregorian or business/fiscal calendars. |
Season Start Date |
Date from which the source record (in the source system) is effective. This represents the start date of a season. |
Season End Date |
Date until which the source record (in the source system) is effective. This represents the end date of a season. |
Phase ID |
Unique ID from the source system that identifies a phase. A phase is a designated timeframe that may or may not correspond with the Gregorian or business/fiscal calendars; however, it falls within a season and is always a child of a single season. Multiple phases within a season may have overlapping timeframes within the season. |
Phase Desc |
Description of a season phase. A phase is a designated timeframe that may or may not correspond with the Gregorian or business/fiscal calendars; however, it falls within a season and is always a child of a single season. Multiple phases within a season may have overlapping timeframes within the season. |
Phase Start Date |
Date from which the source record (in the source system) is effective. This represents the start date of a phase. |
Phase End Date |
Date until which the source record (in the source system) is effective. This represents the end date of a phase. |
Phase Desc Alternate |
Alternate grouping of phase descriptions based only on the names, which allows for grouping multiple same-named phases onto the same report line. |
Trade Area
A trade area is the geographic area serviced by a retail store or proposed retail store. A trade area is defined by whether a consumer shops at the store, and a retailer may have multiple trade areas for the site (primary, secondary, tertiary). Trade areas should be defined in such a way that retailers can determine the demographic, psychographic, or population data for the geography served by the store. This data is pulled from market area data, which is usually based on census blocks in the U.S. The trade area provides a mechanism to map market area data to a specific store because the census blocks (or other method used to store market area data) do not correlate directly to the geographic area served by a store. Examples of ways to define a trade area include using traffic flow studies, a retail gravity model, a zip code method, or commuting data.
Table 5-25 Trade Area Dimension Attributes
Attribute | Definition |
---|---|
Trade Area Name |
Indicates the name of the trade area |
Trade Area Description |
This attribute provides a description of the trade area. |
Trade Area Type |
This attribute describes the type of trade area. Valid values could include Urban, Suburban, Rural, and others. |
Pull factor |
Pull factors are ratios that estimate the proportion of local sales that occurs in a town. |
Commuter population |
Number of people who commute in this trade area. |
Peak Season Population |
The number of people in the Trade Area during peak 'population' season. This is common in Trade Areas with high tourist population ebb and flow. |
Tourist Population |
The number of people that are tourists in a Trade Area. |
State Population |
The number of people in the state that the Trade Area resides. |
Number of Households |
The number of households within a trade area. |
Average Family Size |
The average number of people within a household that reside in a trade area. |
Per Capita Income |
The income divided by the total population of a Trade Area. |
Avg Num of Vehicles |
Average number of vehicles per household in this trade area. |
Average Drive Time |
This attribute indicates the average time in minutes consumers must drive from their homes to shop. |
Reclassification
Reclassification occurs when any entity in a dimension changes its place in the dimension hierarchy, or when one or more attributes of an entity are changed. Reclassification affects Retail Insights reporting, whether you are using as-is, as-was, or point in time analysis. See "Analysis Methods" in Creating and Modifying Reports for more information.
Major Reclassification and Lower-Level Dimensions
A major change occurs whenever an entity changes its place in the product hierarchy (group, department, and item can be reclassified) or in the organization hierarchy (area, region, district, and location can be reclassified). This type of reclassification alters the relationship among entities in a hierarchy.
For example, a single item (white shirt) might be reclassified from the Dress to the Casual subclass.
![Item reclassification example Item reclassification example](img/reclass_01.png)
Only the Product and Organization dimensions can undergo major changes, and they are referred to as lower-level dimensions. They are dimensions with major changeable lower levels. Because Product and Organization are aggregating dimensions, a major change results in an altered data aggregation within their hierarchies.
The history of an entity before and after the major change can be tracked and compared. For example, an item can be moved from one subclass to another within its product hierarchy of department and class. While there are many good reasons for a retailer to move, or reclassify, an item in this way, Retail Insights still needs to track sales for that item from its new location in the product hierarchy, both before and after the change.
Retail Insights handles major changes by assigning the reclassified item (to use the same example) a new surrogate key. The surrogate key, along with the dimension's identifier, allow a means to track the dimension, and all transactions related to it, at any point in time.
Minor Changes and Top-Level Dimensions
A minor change means that an attribute of an entity is changed, but its position in the hierarchy remains the same. The dimensions that can only undergo minor changes are known as top level dimensions and consist of every dimension except organization and product. The levels of the top level dimensions cannot be reclassified; they are static.
Product and organization dimensions can undergo minor changes, but minor changes are not significant enough to alter their hierarchies.
For example, a description of a subclass might be changed from “Humorous Cards" to “Funny Cards."
![Minor change example Minor change example](img/reclass_02.png)
This type of change does not alter the relationship of a subclass to any other level of the hierarchy above or below it. The record is simply updated to reflect the description change; a new surrogate key does not need to be inserted. Minor change dimension processing in Retail Insights is less complex than major change processing.
Customer Order
Oracle Retail Insights' customer order functionality allows retailers to analyze transactions that cross multiple channels, and enables analysis of Oracle's Commerce Anywhere capabilities. It has two dimensions: customer order demand and customer order fulfillment.
For most retailers, effective customer order management has become critical as customers no longer shop only in brick and mortar stores, but expect the ability to interact with retailers across a variety of channels. A customer order is an agreement between the retailer and the customer in which the customer pays for an item and the retailer agrees to make the item available for pickup or delivery at a later date. It consists of two parts, demand and fulfillment. Demand involves facilitating the capturing of customer orders via an e-commerce site, a mobile device, an in-store kiosk or any other similar method. The order fulfillment process, in which the customer takes possession of the product, must be properly managed across those channels to avoid jeopardizing relationships with valued customers who want a seamless experience. An order management system, such as DOO (Distributed Order Orchestration) and GOP (Global Order Promising), is used to manage the order throughout its lifecycle. When an order is initially taken, this application will determine where the order should be sourced based on customer preferences and rules related to fulfillment options set by a retailer (e.g. cost, lead times). Oracle Retail Insights provides a comprehensive set of metrics to help retailers achieve customer satisfaction. Included are key performance measurements for customer order demand and customer order fulfillment.
Oracle Retail Insights' customer order dimension supports a number of different attributes of a customer order to allow performance analysis of retailer's business across all channels. A complete list of these attributes and their descriptions is in the following sections. These attributes allow a user to slice and dice customer order data for analyses by order delivery information, order status, and other customer order details.
For example, if an item in an order line is sold as a substitute for another item (perhaps the original item is unavailable), then both the original item and the substitute item will be identified as such. These attributes can be used to analyze the demand for the original item the customer wanted and the alternative items that were actually ordered and delivered.
Order status is also captured so that retailers can track the order lifecycle and analyze orders based on whether they are backordered, complete, canceled, etc. to discover potential issues involved with customer satisfaction that excessive backorders or cancellations might indicate. A large amount of canceled orders, for instance, could mean there is a group of upset customers who are returning items with which they are unsatisfied or for which delivery time was too late to be acceptable.
Finally, a retailer can identify how an order was shipped, through the requested shipment type and requested shipment method attributes, which identify the carrier and the service type being used to fulfill the order. This could be used in conjunction with the order status analysis to determine if customer dissatisfaction correlates to a specific shipment type or method.
Note:
When using Customer Order Promotion Transaction, Customer Order Transaction, Customer Order Status, and Customer Order Fulfillment dimensions, Salesperson and/or Cashier attributes should be used to represent an employee. Employee Name should not be used with these facts.
Table 5-26 Customer Order Demand Attributes
Attribute | Definition |
---|---|
CO Header Demand Status |
This attribute provides the status of the customer order header, which could be unique to the retailer's order management system. Using this attribute a user can identify the status of customer order. Some of the statuses could be "Order Initiate", "Back-ordered", "Partial Picked", "Picked", "Partial Shipped", "Shipped", "Completed" and "Cancelled". |
CO Line Demand Status |
This attribute provides the status of the customer order line, which could be unique to the retailer's order management system. |
Sales Person |
This attribute lists the retailer's sales person who was responsible for the transaction and was credited with originating the sale. |
Cashier |
This attribute lists the employee who processed the sales transaction by receiving the tender from customer. |
Customer Service Representative |
This attribute lists the employee who helped the customer with any questions or sold them value-added services (re-packaging, gift packing, gift cards, etc). |
Origin Demand Channel |
This attribute lists the location deemed the point of origin for the customer order. There are several channels, such as call center, website, SMS advertisement, store cashier, and sales person that could be considered the Origin Demand Channel. |
Submit Demand Channel |
The location deemed the generation of demand or point of submission for the customer order. There are several channels, such as customer service center, website, kiosk at store, and store POS system that could be considered the submit demand channel. The origin demand channel and submit demand channel may or may not be the same for a customer order. |
CO Header Number |
Each customer order has header information that is primarily customer-related, pertains to the entire order, and is uniquely identified by a Customer Order header number. Header information also contains information about the conditions that affect how the system processes an order, such as fulfillment type, fulfillment method and delivery dates. Most of the remaining header information consists of default values from the Address Book, Customer Billing Instructions, and Customer Master, such as tax code and area, and shipping address information. |
CO Line Number |
The customer order line number is used to uniquely identify the customer order line information, which includes detailed information about the items on the order, such as quantities, prices, status, and shipped quantities. It also contains the customer order header number to identify the order to which the line belongs. |
Requested Shipment Type |
This attribute provides the type of requested shipment for the customer order line. Some shipment types could be "Direct Ship to Customer", "Store Pickup", etc. |
Requested Shipment Method |
Requested Shipment Method is more granular information about the Requested Shipment Type attribute. It defines the method of shipping to the customer. If the shipment type is "direct ship to cust" the method might be "overnight" or "ground". If the shipment type is "Store Pickup" the method would refer to how the goods were made available at the store, such as "WH-to-Store transfer", or "Stock from Store", etc. |
CO Line Original Item |
If an item is not available it may be replaced with a substitute item. In that case Oracle Retail Insights stores the original item as the CO Line Original Item attribute. |
CO Line Substitute Item |
If a customer orders an item that is not available, a retailer may decide to substitute a similar item that is available to be shipped immediately. This attribute displays the substitute item. |
CO Retail Type |
This attribute displays the price type that was recorded for the line item. The possible values could be R-Regular, P-Promotion, and C-Clearance. |
CO Cancel Reason |
This attribute is the reason given by the customer for canceling an order. Examples could be "Backorder Abandon," "Late Delivery," etc. |
Table 5-27 Customer Order Fulfillment Organization Dimension Attributes
Attribute | Definition |
---|---|
Fulfillment Company Number |
This attribute displays the unique ID from the source system that identifies a fulfillment company. |
Fulfillment Company |
Name of a fulfillment company. Fulfillment Company is the highest attribute within the fulfillment Organization hierarchy. A fulfillment company consists of one or more fulfillment chains. |
Fulfillment Chain Number |
This attribute displays the unique ID from the source system that identifies a fulfillment chain. |
Fulfillment Chain |
This attribute displays the name of a fulfillment chain. A fulfillment chain consists of one or more areas. |
Fulfillment Area Number |
This attribute displays the unique ID from the source system that identifies a fulfillment area. |
Fulfillment Area |
This attribute displays the name of a fulfillment area. A fulfillment area consists of one or more regions. |
Fulfillment Region Number |
This attribute displays the unique ID from the source system that identifies a fulfillment region. |
Fulfillment Region |
This attribute displays the name of a fulfillment region. A fulfillment region consists of one or more districts. |
Fulfillment District Number |
This attribute displays the name of the unique ID from the source system that identifies a fulfillment district. |
Fulfillment District |
This attribute displays the name of a fulfillment district. A fulfillment district consists of one or more locations. |
Fulfillment Location Number |
This attribute displays the unique ID from the source system that identifies a fulfillment location. |
Fulfillment Location |
This attribute displays the lowest level within the fulfillment organization hierarchy. It identifies a fulfillment warehouse, fulfillment store, or partner within the fulfillment company. |
Fulfillment Channel ID |
The ID of channel in which a customer order is fulfilled. |
Fulfillment Channel |
The channel in which a customer order is fulfilled. |
Table 5-28 Customer Order Tender Attributes
Attribute | Definition |
---|---|
Sales Transaction Number |
This attribute displays a unique number through which the sales transaction can be identified. The transaction number is used to add detailed information about the item sales on the transaction, such as quantities, prices, discounts and tender amounts. |
Tender Type |
The form of payment made for a customer order sales transaction. Examples of tender types include cash, credit card, or gift card. |
Transaction Type |
This attribute differentiates cross channel liability transactions from normal sales, return transactions, and wholesale sales and return transactions. This is an internally generated attribute used by Oracle Retail Insights. |
Reason
The Reason dimension makes it possible to track why a particular action was taken in the areas of inventory adjustment and sales. Return reasons such as "wrong item shipped" or "defective" are tracked by Return Reason. Inventory adjustments are tracked by Inv Adjustment Reason. The Reason attributes do not form a drillable hierarchy.
Both sets of reason codes exist within the same attributes, but only the codes associated with a specific metric will display in a given analysis. For example, Reason Code and Return Amt will show the return reason codes. Reason Code and Adjustment Units will show the inventory adjustment reason codes. Status Codes will behave similarly for Unavailable Inventory and Customer Order facts.
Table 5-29 Reason Attributes
Attribute | Definition |
---|---|
Reason Code |
To identify the reason why a particular action had performed depending on the subject area used (For example: Inv Adjustments, Return to Vendor, cost change, price change etc.) |
Reason Description |
A detailed description of the reason why a particular action had performed depending on the subject area used (For example: Inv Adjustments, Return to Vendor, cost change, price change etc.) |
Status Code |
To identify the status of the element depending on the subject area used. (For example: Inv Status, Customer order status etc.) |
Status Description |
A detailed description of the status depending on the subject area used. (For example: Inv Status, Customer order status etc.) |
Status Class |
This Attribute can be used to identify the different functional areas that status is used for. (For example: Inv Status, Customer order status etc.) |
Reason Category |
This attribute gives the category of reason for different functionalities (For example: Inventory Adjustment, RTV etc.) |
Inventory Transfer
Inventory Transfers are stock movements between a retailer's locations. Inventory Transfers analysis will enable retailers to improve sales and avoid out of stocks by moving stock to locations where it is most needed. Depending on the transaction codes used in creating Inventory Transfers the transfer type is captured in Retail Insights as Normal, Book and Inter Company transfer types. Retail Insights will not support Transfers functionality for Transformable items. Retail Insights holds the inventory Transfers at item, to location, from location, transfer type and day level.
Table 5-30 Inventory Transfer Attributes
Attribute | Definition |
---|---|
Transfer Type Code |
Indicates the code for Transfer Type. This is based on the origin of the transfer request and determines how transfer behaves. |
Transfer Type Description |
Indicates the description for Transfer Type. This is based on the origin of the transfer request and determines how transfer behaves. Different Transfer Types that are supported are - Normal Transfer, Book Transfer, Inter Company. |
Tsf Zone ID |
Unique ID from the source system that identifies a transfer zone. A transfer zone is an intentional grouping of locations for transferring owned inventory from one location to another. A location can belong to only one transfer zone. |
Tsf Zone Desc |
Detailed description of a transfer zone. A transfer zone is an intentional grouping of locations for transferring owned inventory from one location to another. A location can belong to only one transfer zone. |
Tsf Entity ID |
Unique ID from the source system that identifies a transfer entity. A transfer entity is a group of locations that share legal requirements around product management. A location can belong to only one transfer entity, and a transfer entity can belong to multiple organization units. |
Tsf Entity Desc |
Detailed description of a transfer entity. A transfer entity is a group of locations that share legal requirements around product management. A location can belong to only one transfer entity, and a transfer entity can belong to multiple organization units. |
Transfer from Organization
The Transfer from Organization dimension allows tracking of inventory transfers from a location or other organizational attribute. This permits analysis of the number of units transferred and the retail and cost value of the transfer in the organization.
Table 5-31 Transfer From Organization Attributes
Attribute | Definition |
---|---|
From Chain Number |
Chain in the company from which a transfer originates |
From Chain |
Name of the chain from where the transfer originated. |
From Area Number |
Area in the chain from which a transfer originates. |
From Area |
Name of the Area under the chain from which a transfer originates. |
From Region Number |
Region in the area from which a transfer originates. |
From Region |
Name of the Region under the area from which a transfer originates. |
From District Number |
District Number from which a transfer originates. |
From District |
Name of the District under the region from which a transfer originates. |
From Loc Number |
Warehouse, store, or partner location number from which a transfer originates. |
From Loc |
Warehouse, store, or partner location name from which a transfer originates. |
From Tsf Entity ID |
Transfer entity ID from which a transfer originates. |
From Tsf Entity Desc |
Transfer entity description from which a transfer originates. |
From Tsf Zone ID |
Transfer Zone ID from which a transfer originates. |
From Tsf Zone Desc |
Transfer Zone description from which a transfer originates. |
Transfer Status
Separately from the dimensions listed above, RI also maintains the current status of each individual transfer created in the merchandising system. This is equivalent to the RMFCS table for TSFHEAD, and captures all of the up-to-date attributes and status codes for every transfer action. Having this data in RI allows allocators and buyers to analyze transfer activity across the business and quickly identify problem areas using a variety of criteria, such as getting daily reports for cancelled or rejected transfers, transfers which have been open past a certain number of days, or transfers which have specific context types.
Table 5-32 Transfer Status Dimension Attributes
Attribute | Definition |
---|---|
Transfer Number |
Unique number to identify the transfer within the system. |
Parent Transfer Number |
Identifies the transfer at the level above the transfer and only used for the transfer with finishing activity. |
Transfer From Loc Type |
Contains the location type of the from location of the transfer. Valid values: S-Store; W - Warehouse; E - External Finisher |
Transfer From Loc |
Contains the location number of the transfer from location. This field will contain a store, warehouse or external finisher number based upon the FROM_LOC_TYPE field. |
Transfer Expected DC Date |
It is the date that the transfer is expected to be shipped from a warehouse and communicated to WMS. |
Transfer Inventory Type |
Indicates whether the transfer is for Available or Unavailable inventory (not combination of both). Valid values: A - Available, U - Unavailable |
Transfer Type |
Identifies the type or reason for the transfer. |
Transfer Status |
Contains the status of the transfer. Valid value: I - Input, B - Submitted, A - Approved, S - Shipped, C - Closed, D - Deleted (will be deleted during batch), X - Transfer is being externally closed, P - Picked, L - Selected. |
Transfer Freight Code |
Determines the priority for this transfer. Valid values: N - Normal, E - Expedite, H - Hold |
Transfer Routing Code |
Indicates the type of freight to use on the transfer. |
Transfer Create ID |
Contains the user ID of the user that created the transfer. |
Transfer Approval Date |
Contains the date the transfer was approved. |
Transfer Approval ID |
Contains the user ID of the user that approved the transfer. |
Transfer Delivery Date |
Indicates the earliest date that the transfer can be delivered to the store. |
Transfer Close Date |
Contains the date the transfer was closed. |
Transfer External Ref Number |
Contains audit trail reference to external system when an external transaction initiates master record creation in the Oracle Retail system. |
Transfer Repl Approve Flag |
Contains the indicator used to determine if the transfer should be approved during the replenishment process. Valid values: Y, N. |
Transfer Comments |
Contains any miscellaneous comments associated with the transfer entered by the user. |
Transfer EOW Date |
Contains the end of week date for the exp_dc_date column. It is used for OTB extracts for Intercompany transfers. |
Transfer Mass Return Number |
Contains the Mass Return Transfer Number with with this transfer is associated. |
Transfer Not After Date |
Contains the last day upon which a store can ship the requested merchandise to the warehouse. |
Transfer Context Type Code |
This field holds the reason code related to which a transfer is made. |
Transfer Context Type Desc |
The descriptive value for the transfer context type code. |
Transfer Context Value |
Contains the value relating to the context type, for example Promotion Number. |
Transfer Restock Cost Percent |
Contains the percentage of cost charged by the receiving location for re-stocking. |
Transfer Franchise Order Need Date |
Contains the need date of franchise Order. This column is populated only for Franchise Order transfers. |
Transfer Delivery Slot |
Indicates the delivery slot that will be used for the transfer. |
Transfer Franchise Order Number |
Contains the franchise order number this transfer is linked to. |
Transfer Franchise Return Number |
Contains the franchise return number this transfer is linked to. |
Market Item
One of the critical components available with Oracle Retail Insights reporting is the ability for a retailer to compare its own performance to that of the market. Market Item attributes allow the retailer to make assortment, promotional and space allocation decisions within a wider context. By comparing its own trends to that of the market it is possible to identify and respond to opportunities and problems quickly and effectively.
Table 5-33 Market Item Dimension Attributes
Attribute | Definition |
---|---|
All Store |
Represents the highest level of Market Item hierarchy. |
Market Dept |
Indicates the second level of Market Item hierarchy. |
Market Category |
The range of products purchased by a business organization or sold by a retailer is broken down into discrete groups of similar or related products; these groups are known as product categories (examples of grocery categories might be: tinned fish, washing detergent, toothpastes). |
Market Subcategory |
Each market category divides into sub-categories. A pre requisite to defining the sub-categories is that trends behind the categories are known. Subcategory is defined as grouping of common differentiating characteristics within a larger category. |
Market Segment |
The next level below Market subcategory. Key information about how inventory is tracked and reported is stored at the Market Segment level. |
Market Sub-segment |
The next level below Market Segment. This is equivalent to Subclass level of Retailer's merchandising hierarchy. |
Market Item Description |
Description of the item including characteristics of the market item. |
Market Item Brand |
Displays the brand associated with the market item. This is level 10 of Market Item hierarchy. |
Market Sub Brand |
A subcomponent of a brand. For example, if a brand were "Super Cola", the subbrand might be "Super Cola Light". |
Market Brand Owner |
Brand owner for the Item. |
Market Brand Owner Number |
Brand owner for the Item. |
Market Item Flavor |
Indicates the flavor of Market Item. |
Market Item Pattern |
Indicates the pattern of Market Item. |
Market Item Scent |
Indicates the scent of Market Item. |
Market Item Size |
Indicates the size of market item. |
Market Package Type |
The package type defines as the packaging method chosen by the market item. After choosing the packaging type, retailer should specify the dimensions of the item. The following types of packaging types are available Case Pallet Each. |
Market Parent Company |
The next level below Market Sub-segment. It Indicate the parent company for the given market item hierarchy. |
Vendor Name |
The name of the vendor who supplies the market item. |
Multi Pack |
The multi-pack is defined as package of several individual pack items sold as a unit. This can be broken into multiple pack items. |
Universal Product Code |
Twelve-digit barcode printed or affixed on virtually everything sold in supermarkets or retail stores, including books, magazines, candy, etc., for automatic checking-out at the cashier counter. UPC not only identifies an item, it also provides real time information on quantity sold, and inventory and ordering information. |
Competitor Pricing
A competitor is a retailer with a product range and customer base similar to those for the organization business unit [Store location in RI] 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 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 pricing strategies can be implemented by retailers to prevent potentially costly customer defections.
Sample questions that Competitor Pricing Analysis can help answer:
-
How do my prices compare, for specific items, against nearby competitor locations? Against average competitor prices across all competitor locations?
-
How do my prices vary for an Item at the competition, when that Item has regular price, or when it's on promotion, or on clearance at the competitor?
One of the critical components available with Oracle Retail Insights reporting is the ability for a retailer to compare its own performance to that of the market. Market Item attributes allow the retailer to make assortment, promotional and space allocation decisions within a wider context. By comparing its own trends to that of the market it is possible to identify and respond to opportunities and problems quickly and effectively.
Buyer
The Buyer dimension stores data about buyers who are responsible for raising purchase orders. The buyer dimension is attached to the Purchase order transactions and is used to report on order quantity, received quantity, cancelled qty against purchase orders created by the given buyer.
Table 5-34 lists the attributes of the Buyer dimension.
Table 5-34 Buyer Attributes
Attribute | Definition |
---|---|
Buyer Name |
The name of the person authorized to create purchase order. |
Buyer Phone |
The current telephone number of the buyer. |
Buyer Fax |
The current Fax number of the buyer. |
Purchase Order
A Purchase order (PO) is a request issued by a Retailer to a supplier, indicating types, quantities, and agreed prices for products. Sending a purchase order to a supplier constitutes a legal offer to buy products or services.
The purchase order dimension stores key details of the purchase orders such as Supplier, Buyer, Order Type, import order indicator etc for orders that have been approved at least once are stored in the dimension.
The purchase order dimension is used with Buyer, Supplier, Item, Organization, Calendar dimensions to report on cost and quantity of ordered, cancelled, received purchase orders against a supplier/Buyer/Item/Location/Time period. The dimension can also be used with the Sales fact, if there are matching customer order numbers on both the PO header record and a sales transaction record.
Table 5-35 lists the attributes of the Purchase Order dimension.
Table 5-35 Purchase Order Attributes
Attribute | Definition |
---|---|
Appointment Date Time |
This column will hold the date and time of the receiving appointment at the warehouse. |
Backhaul Allowance |
Contains the type of backhaul allowance that will be applied to the order. Some examples are Calculated or Flat rate |
Backhaul Type |
This field contains the type of backhaul allowance that will be applied to the order. Some examples are Calculated or Flat rate |
Close Date |
This contains the date when the order is closed. |
Contract Number |
This contains the contract number associated with this order. |
Currency Code |
This contains the currency code for the order. |
Customer Order Number |
The customer order identifier associated with a purchase order, typically used for drop shipments where the PO is placed to fulfill the customer order. |
Delivery Supplier |
This field holds the supplier/supplier site from where the goods are delivered. |
Earliest Ship Date |
The date before which the items on the purchase order cannot be shipped by the supplier. Represents the earliest ship date of all the items on the order |
EDI PO Indicator |
This indicates whether or not the order will be transmitted to the supplier via an Electronic Data Exchange transaction. |
Import Country ID |
The identifier of the country into which the items on the order are being imported. |
Import Order Number |
This indicates if the purchase order is an import order. Valid values are Y (Yes) and N (No). |
Latest Ship Date |
The date after which the items on the purchase order cannot be shipped by the supplier. Represents the greatest latest ship date of all the items on the order |
Not After Date |
This contains the last date on which the delivery of goods in purchase order will be accepted. |
Not Before Date |
This contains the first date on which the delivery of goods in purchase order will be accepted. |
Order Number |
This is the purchase order number that uniquely identifies an order within source system. |
Order Type |
Indicates the type of order and which Open To Buy bucket will be updated. Valid values include: N/B - Non Basic ARB - Automatic Reorder of Basic BRB - Buyer Reorder of Basic. |
Original Approval Date |
This contains the date that the order was originally approved. |
Originated Indicator |
Indicates where the order originated. Valid values include: 0 - Current system generated (used by automatic replenishment) 2 - Manual 3- Buyer Worksheet 4 - Consignment 5 - Vendor Generated |
Payment Method |
Indicates how the purchase order will be paid. Valid options are LC (Letter of Credit), WT (Wire Transfer), OA (Open Account). |
Pickup Date |
Contains the date when the order can be picked up from the Supplier. This field is only required if the Purchase Type of the order is Pickup. |
Pickup Location |
Contains the location at which the order will be picked up, if the order is a Pickup order. |
Pickup Number |
This contains the reference number of the Pickup order. |
PO Type |
This contains the value associated with the PO_TYPE for the order. |
Purchase Type |
Indicates what's included in the suppliers cost of the item. Valid values include C (Cost), CI (Cost and Insurance), CIF (Cost, Insurance and Freight), FOB (Free on Board). |
QC Indicator |
This indicator determines whether or not quality control checking is required when items for this order are received. Valid values are Y and N. |
Reject Code |
This contains a code for the reason why the order was rejected during the automatic replenishment approval process. Valid values include: VM (Vendor minimum not met), NC (Negative cost calculated on an item), UOM (UOM convert error due to incomplete data). |
Ship Method |
The method used to ship the items on the purchase order from the country of origin to the country of import. Valid values include 10 (Vessel, Non container), 11 (Vessel, Container), 12 (Border Water-borne (Only Mexico and Canada)), 20 (Rail, Non-container), 21 (Rail, Container), 30 (Truck, Non container), 31 (Truck, Container), 32 (Auto), 33 (Pedestrian), 34 (Road, Other, includes foot and animal borne), 40 (Air, Non-container), 41 (Air, Container), 50 (Mail), 60 (Passenger, Hand carried), 70 (Fixed Transportation Installation), 80 (Not used at this time). |
Ship Pay Method |
Code indicating the payment terms for freight charges associated with the order. Valid values include: CC - Collect, CF - Collect Freight Credited Back to Customer, DF - Defined by Buyer and Seller, MX - Mixed, PC - Prepaid but Charged to Customer, PO - Prepaid Only, PP - Prepaid by Seller |
Split Reference Order Number |
This column will store the original order number from which the split orders were generated from. It will be for references purposes only. The purpose is to allow users a means of grouping orders that were split from an original super order. The original order, once split, will however be removed from the system. |
Status |
Indicates the status of the purchase order. Dimension only holds POs with the following status: A - Approved C - Cancelled |
Vendor Order Number |
This contains the vendor's unique identifying number for an order. These orders may have originated by the vendor through the EDI process or this number can be associated to an Oracle Retail order when the order is created on-line. |
Revision Date |
The date that an existing purchase order was revised. A revision could include major changes such as a cancellation of ordered units, or a minor change such as a modification to the unit cost amount. |
Allocation
An allocation helps allocate merchandise against each store or warehouse after determining the inventory requirements for the given item, location, and week using real time inventory information. An allocation can either be done in advance of the order's arrival or at the last minute to leverage real-time sales and inventory information. Pre-distribution of product quantities on a purchase order can be done to support faster delivery of goods from a warehouse location to stores. This is tracked via allocations against a given purchase order. Multiple allocations can be raised against a given PO that help distribute the ordered quantity among the stores sourcing from the warehouse.
The Allocation dimension holds details of a given allocation such as the order number against which the allocation was done, the status etc. The Allocation dimension is linked to the Purchase order dimension to report allocations and the allocated quantities against a purchase order.
Table 5-36 lists the attributes of the Allocation dimension.
Table 5-36 Allocation Attributes
Attribute | Definition |
---|---|
Alloc Number |
Contains the unique identifier for the allocation |
Order Number |
The purchase order number against which the allocation has been raised. This is a common attribute between the Purchase Order Dim and the Allocation Dim. |
Status |
Status of the allocation. Valid Values: 'R' = Reserved 'A' = Approved 'C' = Closed |
PO Type |
The PO_Type of the order associated with the allocation |
Alloc Method |
Contains the preferred allocation method, which is used to distribute goods when the stock received at a warehouse cannot immediately fill all requested allocations to stores. Valid values for this field are: A - Allocation quantity based P - Prorate method C - Custom |
Release Date |
Contains the date on which the allocation should be released from the warehouse for delivery to the store locations. |
DOC |
The ASN or BOL number for an ASN or BOL sourced allocation. |
DOC Type |
The source of the Allocation. Valid Values: PO, TSF, ALLOC, ASN, BOL |
Tender Type
The tender type dimension holds the various tender types that may be utilized during sales transactions.
Table 5-37 Tender Type Dimension Attributes
Attribute | Definition |
---|---|
Tender Type |
Represents the tender type code. |
Tender Type Group |
Represents the tender type group to which the tender type ID belongs to. |
Tender Card Number |
Represents the identifier of a gift card or voucher that was purchased (such as for gift card sales) or used as tender (such as gift card redemption). Does not include other tender types such as credit cards. |
Coupon
A coupon is a voucher entitling the holder to a discount for a particular product.
Coupons are important vehicles for targeted offers and for driving sales of a desired category. An analysis of coupon use can help retailers understand if the cost of producing and distributing coupons is worthwhile.
Table 5-38 Coupon Dimension Attributes
Attribute | Definition |
---|---|
Coupon Description |
Contains the description of the coupon associated with the coupon number. |
Coupon Reference Number |
Holds the coupon barcode - only an EAN13 or free text can be entered. |
Coupon Maximum Discount Amt |
Contains the Maximum Discount value that can be gained from the coupon. |
Coupon Amt |
Contains the percent or dollar value of the coupon. |
Percent Ind |
Specifies whether the coupon amount is a percent or a dollar value. |
Promotion |
Holds the promotion ID. Any open promotion can be selected to be associated with coupons |
Promotion Component ID |
Promotion Component ID field required for RPM. Will be required if a promotion has been selected. |
Transaction Level Ind |
Indicates if this is a transaction level coupon. |
Coupon Effective Date |
The effective from-date of the coupon. |
Coupon Expiration Date |
The date the coupon expires. |
Transaction Code
The Transaction Code attribute represents the codes used in merchandising systems to differentiate different types of transactions which occur during daily operations. In Retail Insights, these codes are also used to separate Inventory Receipts based on their associated transaction type.
There are three values for Transaction Code which are used in conjunction with Inventory Receipts:
-
Purchases (20)
-
Allocation Transfer Receipts (44~A)
-
Transfer Receipts (44~T)
Table 5-39 Transaction Code Dimension Attributes
Attribute | Definition |
---|---|
Transaction Code |
Code which describes the transaction type that generated the inventory receipts. |
Transaction Description |
Description of the transaction type that generated the inventory receipts. |
Customer Loyalty Program
Loyalty Programs define the rules used for tracking the purchases of Customers belonging to location loyalty programs, usually through a system of "points". These points can then be redeemed for discounts of a fixed amount (though the points alone have no intrinsic value). The discounts can be distributed through the mail as paper coupons, or made available to customers as an E-Award coupon or Entitlement coupon associated with an Award Program. Loyalty program data can be extracted from Oracle Retail Customer Engagement.
Table 5-40 Loyalty Program Dimension Attributes
Attribute | Definition |
---|---|
Loyalty Program Number |
The number associated with the customer loyalty program. |
Loyalty Program Description |
The name of the customer loyalty program. |
Loyalty Points Description |
The name of the points used in a point-based loyalty program. |
Loyalty Points Currency Value |
The currency amount required to earn a point in a point-based loyalty program. |
Loyalty Program Active Flag |
A flag indicating if a loyalty program is currently active. |
Loyalty Program Start Date |
The effective start date for the loyalty program. |
Loyalty Program End Date |
The effective end date for the loyalty program. |
Loyalty Program Level Number |
The number associated with a level in a customer loyalty program. |
Loyalty Program Level Description |
The name of a level in a customer loyalty program. |
Loyalty Program Level Active Flag |
A flag indicating if a loyalty program level is currently active. |
Loyalty Program Level Default Flag |
A flag indicating if a loyalty program level is the default level a member of the program will start at. |
Loyalty Program Currency |
The primary currency used for the loyalty program. |
Customer Loyalty Account
Loyalty Accounts are used to assign customers to one or more Loyalty Programs. A loyalty account may contain details about the customer's use of the program, such as their points balance, program level, and account open and expiration dates. A customer must have a loyalty account in order to take advantage of the benefits of a loyalty program. Loyalty account data can be extracted from Oracle Retail Customer Engagement.
Table 5-41 Loyalty Account Dimension Attributes
Attribute | Definition |
---|---|
Loyalty Account Number |
The number associated with the customer loyalty account. |
Loyalty Account Card Serial Number |
The sixteen-digit number embossed on a loyalty account card. |
Loyalty Account From Date |
The date that a loyalty account became active at a loyalty program level. |
Loyalty Account To Date |
The date that a loyalty account was no longer active at a loyalty program level. |
Loyalty Account Active Flag |
A flag indicating if the loyalty account is active in the source system. |
Loyalty Account Expiry Flag |
A flag indicating if the loyalty account has expired, such as due to inactivity or account closure. |
Loyalty Account Points Balance |
The number of points available on a loyalty account. |
Loyalty Account Escrow Balance |
The number of points in escrow to a loyalty account. |
Last Award Processed Date |
The last time an award was processed against a loyalty account. |
Last Accrual Date |
The last time points were accrued against a loyalty account. |
Last Program Level Change Date |
The last time the loyalty account moved to a different program level. |
Last Transaction Date |
The last time a transaction was recorded against the primary customer on a loyalty account |
Stock Counts
A stock count (or cycle count) is an inventory auditing procedure, which falls under inventory management, where a subset of inventory, in a specific location, is counted on a specified day. Stock counts may be performed once or multiple times per fiscal year, and different locations may undergo a count at different times. The primary purpose of stock counting is to capture an accurate count of all stock on hand and compare it to the inventory management system's records for inaccuracies and losses. Major differences between the stock count and inventory records would be a cause for concern and require further investigation by the retailer, as these differences could be due to theft or poor inventory management practices by the store.
Reporting on stock counts involves collecting sales and inventory data for the range of dates between the prior count and the current one. This information can be compared to the manual counts and metrics like shrinkage can be calculated at various levels of the merchandise or organization hierarchies. Due to the dynamic nature of stock counts (in terms of when they occur and which locations have undergone them at any point in time), aggregating the sales and inventory data is not as simple as rolling up to a specific fiscal period. The reporting system will need to understand the stock count dates that occur at each retail location, and return aggregate data which is rolled up from each store's individual date ranges.
RI provides a Stock Count dimension for capturing and reporting on stock count activities as well as two Stock Count facts covered in the next chapter. The dimension consists of a list of locations associated with a stock count, as well as the window of time to analyze historical data for the count. The data for this dimension needs to be provided to RI by the retailer from an inventory system such as SIM that manages the stock count activities.
Only when the dimension is provided daily can the stock count facts be populated with data.
Note:
When reporting on factual data, such as sales and inventory for a stock count activity, the following OAS filter can be used to limit the results to the stock count period:
"Business Calendar"."Fiscal Date" between "Stock Count"."Stock Count From Date" and "Stock Count"."Stock Count To Date"
This filter can be added by creating a new filter, selecting the box for "Convert this filter to SQL" in the popup, and clicking OK. Copy the text above into the text box that appears. Then click OK again.
Table 5-42 Stock Count Dimension Attributes
Attribute | Definition |
---|---|
Stock Count ID |
Number which uniquely identifies the stock or cycle count in the source system. |
Stock Count Desc |
Description of the cycle or stock count. This value can be used to group together stock count activities across multiple locations for reporting purposes. |
Stock Count Type |
Indicates the type of stock count, such as B (both unit and amount) or U (unit only). |
Stock Count Status |
Indicates the status of a stock count, such as whether it is scheduled but not yet executed, or already finalized and approved. |
Stock Count Start Date |
Contains the starting date from which data should be included for the current stock or cycle count, such as the day after the prior count occurred. |
Stock Count End Date |
Contains the date on which the stock or cycle count event will take place. |
Discount Type
Discount types are codes defined in the point of sale system to classify a price change applied to a sale, such as manufacturer coupons, employee discounts, or manager price overrides. Discount types would be populated in the sales audit system in order to capture which discounts have been applied to a transaction, which is then loaded to RI along with the Sales Discount fact.
Table 5-43 Discount Type Dimension Attributes
Attribute | Definition |
---|---|
Discount Type Code |
Number which uniquely identifies the discount code in the point of sale and auditing systems. |
Discount Description |
Description of the discount code, such as Employee Discount. |
Selling Organization
It is possible for return transactions to be tagged with the original selling location and original transaction ID associated with the sale of the returned item. This information can be passed from the point of sale system, and then RI will link the selling location to the organization hierarchy for reporting. This functionality enables detailed returns analysis following the sale and return of an item, such as identifying items which are bought online and returned in store.
Table 5-44 Selling Organization Dimension Attributes
Attribute | Definition |
---|---|
Selling Company Number |
This attribute displays the unique ID from the source system that identifies original selling company. |
Selling Company |
Name of the original selling company. |
Selling Chain Number |
This attribute displays the unique ID from the source system that identifies original selling chain. |
Selling Chain |
This attribute displays the name of original selling chain. |
Selling Area Number |
This attribute displays the unique ID from the source system that identifies original selling area. |
Selling Area |
This attribute displays the name of original selling area. |
Selling Region Number |
This attribute displays the unique ID from the source system that identifies original selling region. |
Selling Region |
This attribute displays the name of original selling region. |
Selling District Number |
This attribute displays the name of the unique ID from the source system that identifies original selling district. |
Selling District |
This attribute displays the name of original selling district. |
Selling LocNumber |
This attribute displays the unique ID from the source system that identifies original selling location. |
Selling Loc |
This attribute displays the name of the original selling location. |
Selling Channel Id |
This attribute displays the unique ID from the source system that identifies original selling channel. |
Selling Channel |
This attribute displays the name of the original selling channel. |
Clearances
Clearance events which are managed through a pricing solution such as RPM will be loaded into RI for detailed clearance reporting. The clearance events will be captured for the items and locations included in the event, for the range of effective dates the clearance is active for. An item-location may only be under the effects of a single clearance event at a time, but may undergo multiple clearances across the entire item lifecycle. RI will automatically manage the effective dates and eligibility of items on clearance as new clearance events are generated in the source system.
Retail Insights will maintain the full history of clearance events applied to items, allowing for in-depth analysis of sales, inventory, and similar facts grouped by individual clearances, or aggregated by the clearance groups or markdown numbers associated with multiple events. Some facts are not supported with the Clearances dimension as the fact data would not typically be used at this level, such as Base/Net Supplier Costs, Customer Orders, Purchase Orders, and Sales Promotions. Certain combinations of dimensions would also prevent Clearance analysis, such as looking at Transfers based on the From/To Locations where you could be transferring between clearance and non-clearance locations.
Table 5-45 Clearances Dimension Attributes
Attribute | Definition |
---|---|
Clearance ID |
The display ID of a clearance event |
Clearance Group ID |
The group ID of a clearance event. |
Clearance Group Desc |
The description of a clearance group assigned to a clearance. |
Clearance Markdown ID |
The unique identifier of the markdown number assigned to a clearance event. |
Clearance Markdown Number |
The markdown number assigned to a clearance event, generally designating the sequence of the event across multiple clearances (such as first, second, final). |
Clearance OOS Date |
Contains the date when the item/location on clearance is expected to be out of stock. Does not mean the item will actually become out of stock on this date. |
Clearance Reason |
The user-provided reason for initiating the clearance event. |
Clearance Change Type |
The type of price change being applied, such as Amount Off, Percent Off, Fixed Price, or Exclude. |
Clearance Change Amt |
The amount of a price change or fixed price override on a clearance event. This value shows the item price after the change has been applied. |
Clearance Change UOM |
The unit of measurement for the price change on a clearance event. |
Clearance Change Currency |
The currency code for the price change on a clearance event. |
Custom Flex Attributes
The custom flex attribute solution (CFAS) for RMFCS is a metadata driven framework that enables you to set up additional attributes on the pre-enabled RMFCS entities without having to change the existing screens or make any changes in the application code. Retail Insights has the ability to consume certain sets of commonly used CFAS attributes created using the out-of-box RMFCS framework, including Item, Location, and Item-Location attributes. RI currently supports the standard configuration of these tables using a single group-set per dimension.
Each CFAS interface is loaded from RMFCS as an extension of the associated dimension in RI. RI currently supports the following interfaces.
-
Item attributes are loaded from the RMFCS table ITEM_MASTER_CFA_EXT and are exposed in OAS as a new set of Item dimension attributes.
-
Location attributes are loaded from a combination of STORE/WH/PARTNER CFA tables and are exposed in OAS as a new set of Organization dimension attributes.
-
Item-Location attributes are loaded from the ITEM_LOC_CFA_EXT table and are exposed in OAS as a new set of Product Org attributes.
-
Supplier attributes are loaded from RMFCS table SUPS_CFA_EXT table and are exposed in OAS as a new set of Supplier attributes.
-
Merchandise hierarchy attributes are loaded from a combination of DEPS/CLASS/SUBCLASS CFA tables in RMFCS and are exposed in OAS as a set of Item dimension attributes.
The RI attribute names for CFAS attributes are intentionally generic, and it is expected that the retailer will relabel them during implementation of RI. The naming scheme follows a standard pattern of <Dimension> Flex Attr <Number> <Datatype>. For example, Item attributes will have names such as Item Flex Attr 22 Date or Item Flex Attr 11 Number. These should be relabeled to show names that will be meaningful to RI users when building reports. Once they have been loaded and labeled appropriately, the attributes should function in the same manner as any other RMFCS-sourced attribute.
Deals
A deal is a set of one or more agreements that take place between the retailer and a vendor. A vendor can be a supplier, wholesaler, distributor or manufacturer, and from the vendor, the retailer is entitled to receive discounts or rebates for goods that are either purchased or sold. A deal consists of a set of discounts and/or rebates that are negotiated with the vendor and share a common start date. Retail Insights includes a standalone Deals dimension with attributes that define the deal details. The deal ID is the primary key for the dimension and a deal will have only one row in the data. When new data comes to the system for an existing deal, the attribute values are updated to reflect the latest data. History is not retained for old versions of deal data. Deal attributes may be used with the Deal Income fact for reporting.
Table 5-46 Deals Dimension Attributes
Attribute | Definition |
---|---|
Deal Active Date |
Date the deal will become active. This date will determine when deal components begin to be factored into item costs. |
Deal Actual Earned TD |
The total monies earned for the deal to date. |
Deal Add Reporting Days |
This column will give the number of extra reporting days that should be added to the Deal_actuals_forecast table to cater to the late postings of the transactions after the deal close date. |
Deal Apply Timing |
Indicates when the deal component should be applied - at PO approval or time of receiving. Valid values are O for PO approval, R for receiving. |
Deal Approval Date |
Date the deal was approved. |
Deal Bill Back Method |
This will determine the bill back method. It will be required for bill back deals only. Valid values are Credit note or Debit note. |
Deal Bill Back Period |
Code that identifies the bill-back period for the deal component. This field will only be populated for billing types of BB. Valid values are W for week, M for month or Q for Quarter and A for Annual. |
Deal Billing Partner ID |
This indicates the partner that will included on the invoice information. |
Deal Billing Partner Type |
Type of the partner the deal applies to. Valid values are S1 for supplier hierarchy level 1 (manufacturer), S2 for supplier hierarchy level 2 (distributor) and S3 for supplier hierarchy level 3 (wholesaler). |
Deal Billing Supplier ID |
This indicates the supplier that will included on the invoice information. |
Deal Billing Type |
Billing type of the deal component. Valid values are OI for off-invoice and BB for bill-back. |
Deal Close Date |
Date the deal will/did end. This date determines when deal components are no longer factored into item costs. It is optional for annual deals, required for promotional deals. It will be left null for PO-specific deals. |
Deal Comments |
Free-form comments entered with the deal. |
Deal Comp Type |
Type of the deal component, user-defined and configurable. In the case of multiple components, will only get one of the values for this record. |
Deal Comp Type Desc |
Description of the deal component. In the case of multiple components, will only get one of the values for this record. |
Deal Create Date |
Date of when the record was created. This value should only be populated once on insert, it should never be updated in the source. |
Deal Currency |
Currency code of the deals currency. All costs on the deal will be held in this currency. |
Deal External Ref No |
Any given external reference number associated with the deal. |
Deal Growth Rate TD |
The budget growth rate percentage for the deal to date. |
Deal Hist Comp End Date |
The last date of the historical period against which growth will be measured in this growth rebate. |
Deal Hist Comp Start Date |
The first date of the historical period against which growth will be measured in this growth rebate. |
Deal Income Method |
This will determine how the income will be calculated. Valid values are Actuals earned to date or Pro-rated using forecast. |
Deal Invoice Logic |
This will determine if the credit notes or debit notes created should be created manually or require manual intervention and also if negative amounts should be included. Valid values are AA for Automatic All values, MA for Manual All Values, AP Automatic Positive values only, MA Manual Positive values only, NO - no invoice processing. |
Deal Last Invoice Date |
This is the last time an invoice was raised for the deal. |
Deal Next Invoice Date |
This is the estimated next invoice date for the deal. |
Deal Number |
Unique deal number, generated from a sequence. |
Deal Order Number |
Order the deal applies to, if the deal is PO-specific. |
Deal Pack Level Flag |
Used to indicate whether the packs are to be tracked at pack level or not. |
Deal Partner Desc |
Name of the partner assigned to this deal. |
Deal Partner ID |
Level of supplier hierarchy (such as manufacturer, distributor or wholesaler) set up as a partner, used for assigning rebates by a level other than supplier. |
Deal Partner Type |
Type of the partner the deal applies to. Valid values are S1 for supplier hierarchy level 1 (manufacturer), S2 for supplier hierarchy level 2 (distributor) and S3 for supplier hierarchy level 3 (wholesaler). |
Deal Rebate Calc Type |
Indicates if the rebate should be calculated using linear or scalar calculation methods. Valid values are L for linear or S for scalar. |
Deal Rebate Flag |
Indicates if the deal component is a rebate. Deal components can only be rebates for bill-back billing types. Valid values are Y for yes or N for no. |
Deal Rebate Growth Flag |
Indicates if the rebate is a growth rebate, meaning it is calculated and applied based on an increase in purchases or sales over a specified period of time. Valid values are Y for yes or N for no. |
Deal Rebate Income Type |
Indicates if the rebate should be applied to purchases or sales. Valid values are P for purchases or S for sales. It will be required if the rebate indicator is Y. |
Deal Recalc Orders Flag |
Indicates if approved orders should be recalculated based on this deal once the deal is approved. Valid values are Y for yes or N for no. |
Deal Reject Date |
Date the deal was rejected. |
Deal Reporting Level |
This will determine periods shown in the deal income screen and the frequency of the deal income accrual reporting. Valid values are W for week, M for month or Q for Quarter. |
Deal Status |
Code for the status of the deal. Valid values are A for approved, R for rejected and C for closed. Unapproved deals will not be extracted from the source. |
Deal Stock Ledger Flag |
Indicates if the deal income accrual will also be written to the RMS stock ledger. Valid values are Y for yes or N for no. |
Deal Supplier Desc |
Name of the supplier assigned to this deal. |
Deal Supplier Num |
Deal supplier's number. This supplier can be at any level of supplier hierarchy. |
Deal Threshold Type |
Identifies whether thresholds will be set up as qty values, currency amount values or percentages (growth rebates only). Valid values are Q for qty, A for currency amount or P for percentage. |
Lifecycle Pricing Optimization
If you own the Oracle Retail Lifecycle Pricing Optimization (LPO) solution then you will have the ability to report on some of the results of that application directly in Retail Insights. Retail Insights exposes the optimization run attributes and included product hierarchy nodes as dimensions and attributes. It also includes the run input and output metrics and calculations as fact measures. You may report on these fact measures by Optimization Run and Run Products, but you can also join the data with the Business Calendar and Clusters dimensions to see the metrics by price zone and fiscal week.
Table 5-47 Price Optimization Run Dimension Attributes
Attribute | Definition |
---|---|
LPO Processing Week ID |
Internal calendar identifier that corresponds to the last actual week data was loaded for price optimization purposes. |
LPO Run ID |
Unique identifier for the Price Optimization run. |
LPO Run Name |
Name assigned to identify the Price Optimization run. |
LPO Run Desc |
Description assigned to identify the Price Optimization run. |
LPO Run Status ID |
Status ID of the Price Optimization run. |
LPO Run Status |
Status of the Price Optimization run, such as Ready for Review or Approved. |
LPO Last Execution Date |
The last time the Price Optimization run was executed. |
LPO Batch Run Flag |
Indicates if the Price Optimization run was triggered from a batch. |
LPO Finalized Flag |
Indicates if the Price Optimization run has been finalized. |
Table 5-48 Price Optimization Product Dimension Attributes
Attribute | Definition |
---|---|
LPO Product Rec Lvl ID |
Identifier for the recommendation level of the LPO product hierarchy |
LPO Product Rec Lvl Name |
Name for the recommendation level of the LPO product hierarchy |
LPO Product Lvl 1 ID |
Identifier for the top level of the LPO product hierarchy |
LPO Product Lvl 1 Name |
Name for the top level of the LPO product hierarchy |
LPO Product Lvl 2 ID |
Identifier for level 2 of the LPO product hierarchy |
LPO Product Lvl 2 Name |
Name for level 2 of the LPO product hierarchy |
LPO Product Lvl 3 ID |
Identifier for level 3 of the LPO product hierarchy |
LPO Product Lvl 3 Name |
Name for level 3 of the LPO product hierarchy |
LPO Product Lvl 4 ID |
Identifier for level 4 of the LPO product hierarchy |
LPO Product Lvl 4 Name |
Name for level 4 of the LPO product hierarchy |
LPO Product Lvl 5 ID |
Identifier for level 5 of the LPO product hierarchy |
LPO Product Lvl 5 Name |
Name for level 5 of the LPO product hierarchy |
LPO Product Lvl 6 ID |
Identifier for level 6 of the LPO product hierarchy |
LPO Product Lvl 6 Name |
Name for level 6 of the LPO product hierarchy |
LPO Product Lvl 7 ID |
Identifier for level 7 of the LPO product hierarchy |
LPO Product Lvl 7 Name |
Name for level 7 of the LPO product hierarchy |
LPO Product Lvl 8 ID |
Identifier for level 8 of the LPO product hierarchy |
LPO Product Lvl 8 Name |
Name for level 8 of the LPO product hierarchy |
LPO Product Lvl 9 ID |
Identifier for level 9 of the LPO product hierarchy |
LPO Product Lvl 9 Name |
Name for level 9 of the LPO product hierarchy |
Retail Insights Attribute Metadata
The following chart provides information about Retail Insights attribute metadata. Users please be aware that you cannot mix facts across as-is and as-was subject areas.
Table 5-49 Retail Insights Attribute Metadata
Attributes | As-Is | As-Was |
---|---|---|
Business Calendar |
X |
X |
Employee |
X |
X |
Cluster |
X |
X |
Consumer Group |
X |
X |
Consumer Household Group |
X |
X |
Organization |
X |
X |
Stockholding Franchise |
X |
X |
Non-Stockholding Franchise |
X |
X |
Product |
X |
X |
Promotion |
X |
X |
Customer |
X |
X |
Customer Behavior |
X |
|
Customer Segment |
X |
X |
Customer Segment Allocation |
X |
X |
Household |
X |
X |
Customer Segment Loyalty |
X |
|
Supplier |
X |
X |
Retail Type |
X |
X |
Season Phase |
X |
X |
Season Phase Planning |
X |
X |
Trade Area |
X |
|
Market Item |
X |
|
Buyer |
X |
X |
Purchase Order |
X |
X |
Allocation |
X |
X |
Tender Type |
X |
X |
Coupon |
X |
X |
Competitor Pricing |
X |
X |
Customer Order |
X |
X |
Customer Order Origin Channel |
X |
X |
Customer Order Submit Channel |
X |
X |
Customer Order Tender Type |
X |
X |
Fulfillment Organization |
X |
X |
Gregorian Calendar |
X |
X |
Customer Order Fulfillment |
X |
X |
Customer Order Status |
X |
X |
Reason |
X |
X |
Shipment Method |
X |
X |
Shipment Type |
X |
X |
Tender Type |
X |
X |
Time of the day |
X |
X |
Return to Vendor |
X |
X |
Inventory Adjustments |
X |
X |
Inventory Transfers |
X |
X |
Transaction Code |
X |
X |
Customer Loyalty Program |
X |
X |
Customer Loyalty Account |
X |
X |
Stock Count |
X |
X |
Discount Type |
X |
X |
Selling Organization |
X |
X |
Clearances |
X |
X |
Purchase Type |
X |
X |
Deals |
X |
X |
Price Optimization Run |
X |
|
Price Optimization Product |
X |