Mirror of Oracle documentation
Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.
1 Introduction to Oracle Retail Insights
This chapter introduces the role of business intelligence and data warehousing in a retail environment. It briefly describes the implementation of Oracle Retail Insights and its data sources, and the Retail Insights user interface through Oracle Analytics.
Business Intelligence in the Retail Environment
Business intelligence includes the processes, methods, and technologies adopted by organizations to answer complex business questions and build comprehensive decision support systems. Business intelligence enables all users in a retail organization to answer questions about the business, for example:
-
How do actual sales this period compare to the current plan?
-
What is the retail value of inventory on hand, and how does it compare to the same period last year?
-
What are the best-selling items in a division or department?
-
How effective was the last promotion?
The answers to these questions and others are embedded in the enormous volume of sales and returns, price changes, receipts, and other transactions generated by your retail organization. These transactions are the raw material for business intelligence. Transactionlevel data must be converted to information to support decisions in a retail enterprise.
These systems help organizations in maintaining secure, conformed, and highly available data for all levels of users, from top-level executives who make decisions based on corporate-level information to managers and analysts who analyze their areas and take actions based on their findings. Business intelligence is built using several processes, and applications that maintain these processes, using the latest tools and technologies. One of the main components of business intelligence is a data warehouse. A data warehouse is the repository that stores data extracted from multiple source systems, modeled to perform for both data loading, reporting, and ad hoc analysis needs.
Data Warehousing and Business Intelligence
The data warehouse is the central repository for the data that is required for business intelligence in a retail environment. The applications and components that make up the data warehouse perform these functions:
-
They organize and standardize data so that it can be stored in a consistent format in the data warehouse.
-
They load data to a relational database management system that is specially constructed for business intelligence.
-
They provide analytical tools and interfaces necessary to deliver information throughout the retail organization.
Online transaction processing (OLTP) applications, such as Oracle Retail Merchandising Foundation Cloud Service (RMFCS), are designed for efficient record-keeping. They generally
hold only a small amount of historical information. The data warehouse, on the other hand, consists entirely of historical data organized by business area. (Collections of data organized to support particular business areas are sometimes called data marts.) These business areas consist of a relatively small number of very large tables.
This type of organization is optimal in the business intelligence environment, where large quantities of historical data must be stored and made available to users in summary form. The tables that make up the data warehouse contain the information that is needed to create a picture of the organization at any point during the period for which data is kept.
Oracle Retail Insights
Oracle Retail Insights offers a rich business intelligence solution to retail industry users. Retail Insights is built using latest Oracle technologies and uses Oracle Data Integrator (ODI) for extracting, transforming, and loading (ETL) the data to Oracle Analytics for end user reporting and analysis. This solution provides complete, enterprise-wide insight for retail users, enabling fact-based actions and intelligent interactions.
Retail Insights starts with customer and merchandising data. It embraces existing corporate data sources, and it integrates with Oracle Retail solutions to increase effectiveness across the entire merchandising life cycle.
Retail Insights can integrate with Oracle applications, as well as applications from other vendors. It can be implemented alone, or integrated with other applications, to accommodate each retailer’s unique information needs and applications environment. The prebuilt nature of the solution allows you to achieve fast time to value, by reducing deployment time and helping to lower total cost of ownership.
Oracle Retail Insights is a software product that includes the following modules:
-
Merchandise Insights
-
Customer Insights
-
Consumer Insights
Merchandise Insights Module
The Merchandise Insights module is a merchandising-specific business intelligence module of the Retail Insights application. It provides insight to critical performance indicators such as item sales, store performance, markdowns, inventory turns, sales and profit trends, and current and potential out-of-stocks. Merchandise Insights dashboards provide the ability to act on those insights. They enable you to order more stock, reallocate merchandise, or begin a promotion, triggered by metric thresholds.
In an end-to-end Oracle Cloud implementation, the primary source of information for the Merchandise Insights subject areas is the Merchandising Foundation Cloud Service. This accounts for much of the core areas in RI, such as your item and store information, stock on hand balances, and transactional OLTP activity spanning the entire item lifecycle. Other data sources include the Sales Audit Cloud Service for sales transactions and the Pricing Cloud Service for promotion and clearance event details. Merchandise Insights data can also be supplemented with external data using flexible fact interfaces and financial plan interfaces.
Customer Insights Module
The Customer Insights module enables you to perform detailed analysis of customers and customer segments. This module is often implemented together with Merchandise Insights as
they share the same core foundation data elements. For each Customer Insights subject area, there are numerous metrics that can be used to answer business questions such as the following:
-
Who are my most profitable customers? Who are my most frequent shoppers?
-
How do my customers respond to promotions? Which customers prefer specific promotion types?
-
How are my products selling across various customer demographics?
-
How are my products selling across various customer behaviors?
-
How do my loyalty program sales compare to overall sales patterns?
In an end-to-end Oracle Cloud implementation, the primary source of information for Customer Insights subject areas is the Customer Engagement Cloud Service. CE accounts for the customer, segment, and loyalty information that drives much of the dimensional data used in this module. CE does not provide sales information and it is assumed that Sales Audit or another system is still used to provide that data. Customer Insights data can also be supplemented with external data using flexible fact interfaces and financial plan interfaces.
Consumer Insights Module
The Consumer Insights module enables you to perform analysis of consumers and consumer segments, using data provided by Oracle Data Cloud (ODC). RI provides ways to load enriched customer attributes which ODC sends to your CRM, allowing for a greater understanding of your customer base. RI can also receive information about prospective customers from the larger pool of consumers in ODC. These consumers are selected using your own customer base as a way to identify others who are likely to shop at your business.
Customer Enrichment
ODC offers the ability to enrich first-party customer data with a wealth of attributes and profiles accumulated from hundreds of third-party consumer datasets to help you better understand your customer base. This could be anything from identifying frequent online shoppers to finding people who are “Fitness Fanatics”. This data is typically provided to your CRM system (such as Oracle Retail Customer Engagement) and can then be interfaced into RI for enhanced customer analytics and segmentation.
You can take advantage of this consumer data in RI to answer questions about your customers that may not have been possible before:
-
Do my customers have any common hobbies, activities, or spending behaviors outside my stores which may influence their purchasing decisions with me?
-
Do my customers tend towards specific consumer profiles that can influence how I interact with them through targeted offers and marketing campaigns?
-
How do my customers break down by age, gender, family composition, and other demographics which can impact their response to my advertisements and promotions?
-
Are my customers highly seasonal shoppers with preferences for specific holidays or events, which could indicate opportunities for new promotions targeted to those times of the year?
Consumer Prospecting
ODC also provides a service for analyzing your current customers to locate potential consumers that could be converted through outreach and promotional activity. The service takes a subset of your own customers and matches them to other consumers who share
common attributes or behaviors, making them ideal candidates for consumer conversion activities. Most importantly, the consumers returned by this process will have Oracle Person IDs (which are also applied during customer enrichment), allowing you to link new prospects with ODC-enriched customer data over time. Converted consumers may begin to appear as known customers with sales transactions, which can be rolled up to the Consumer Segment level to analyze conversion rates of a segment that you have been actively targeting with marketing or outreach.
Retail Insights will automatically link consumer prospects with ODC-enriched customers using the Oracle Person ID, providing new insights into the effectiveness of targeted offers and the characteristics of first-time buyers. ODC will also provide the same set of enrichment attributes and profiles on all new prospects that you already get on your first-party data, giving you deeper insights into the individuals that we’ve identified as prime potential customers for your business.
Characteristics
These are some characteristics of Oracle Retail Insights:
- Rich reporting capabilities
Retail Insights offers report creation capabilities using two different analysis methods in the same environment:
-
Historical (as-was)
-
Current (as-is)
See Creating and Modifying Reports for more information about these analysis methods.
Packaged reports are provided as a reference for creating customized reports and serve as the baseline reports for Retail Insights. Retailers may use the packaged reports as-is but it is expected that new reports will be created as part of the implementation process to fit the business’s specific reporting needs.
- Comprehensive Solution
Oracle Retail Insights is an end-to-end solution for reporting and retail business intelligence needs through the following:
-
Data integration with source applications
-
Loading and transforming the fact and dimension data
-
Rolling up the data for improved query performance
-
The Web-based Oracle Analytics user interface for report creation
-
Shell scripts for setting up the batch schedule
-
An automated installer
-
High-performance extract, transform, and load (ETL) code
Using Oracle Data Integrator, Retail Insights offers high performance for the Oracle Database batch processes.
- Extensibility
Retail Insights ETL code can be customized and extended for customer-specific needs.
- Flexibility
Oracle Data Integrator and Oracle Analytics code promote flexibility during implementation based on customer-specific needs and help in improving batch and report performance.
- High-performance reporting
Retail Insights metadata is built using Oracle Analytics and designed to perform in complex reporting scenarios.
- Robust data model
The Retail Insights data model is designed to support a retailer’s data needs in a business intelligence environment. Data model elements are designed to perform with Oracle Analytics architecture.
Retail Insights Data Sources
Retail Insights uses several data sources including Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Oracle Retail Price Management System (RPM). Data is extracted, loaded, and transformed into the Retail Insights data model to support reporting requirements. The first step after installing Retail Insights is to load the data into data warehouse tables using packaged Oracle Data Integrator ETL programs.
This image illustrates the data sources for Oracle Retail Merchandise Insights. The data sources can be Oracle Retail applications or other data sources specific to each retailer’s systems environment.
Oracle Retail Solutions
Oracle Retail Insights integrates with any combination of the following Oracle Retail applications:
-
Oracle Retail Merchandising Foundation Cloud Service (RMFCS)
-
Oracle Retail Sales Audit
-
Oracle Retail Invoice Matching (ReIM)
-
Oracle Retail Pricing (PCS)
-
Oracle Retail Merchandise Financial Planning (MFP)
-
Oracle Retail Inventory Planning Optimization (IPO) Demand Forecasting
-
Oracle Retail Assortment Planning (AP)
-
Oracle Retail AI Foundation (AIF)
-
Oracle Retail Customer Engagement (ORCE)
An online transaction processing (OLTP) application such as Oracle Retail Merchandising Foundation Cloud Service (RMFCS) is the principal source of data for Retail Insights. The OLTP application provides the majority of attribute data for most dimensions, including organization, product, and time calendar dimensions. The OLTP application supplies facts for many data marts including inventory, pricing, cost, and supplier compliance.
Oracle Retail Sales Audit provides the tools to evaluate point-of-sale data, to ensure the accuracy and completeness of information exported to downstream systems used in optimization processes, financial reporting, and analysis.
Oracle Retail Invoice Matching (ReIM) is a solution that provides the data necessary to support invoice verification, minimizing interface development and maintenance costs. ReIM can serve as the source of invoice cost data. This information must be extracted from another application if you do not use ReIM.
Oracle Retail Pricing (PCS) is a solution that assists with pricing decisions. PCS can serve as the source of promotion data. This information must be extracted from another application if you do not use PCS.
Oracle Retail Merchandise Financial Planning (MFP) provides strategic and financial product planning functions. These functions support industry planning standards for preseason and inseason processes. MFP facilitates the creation of financial plans in a structured method. Similarly, Assortment Planning (AP) provides these functions at a lower level of detail.
Inventory Planning Optimization (IPO) Demand Forecasting provides accurate forecasts that enable retailers to coordinate demand-driven outcomes that deliver connected customer interactions. With a single view of demand, IPO Cloud Service-Demand Forecasting provides pervasive value across retail processes, including driving optimal strategies in planning, increasing inventory productivity in supply chains, decreasing operational costs, and driving customer satisfaction from engagement to sale to fulfillment.
Oracle Retail AI Foundation (AIF) provides strategic clustering functions. Based on various rules and algorithms customer segments are derived and customers are grouped. AIF facilitate creation of customer segments based on various customer attributes.
Oracle Retail Customer Engagement (ORCE) enables storage of customer data to track their shopping preferences, habits, and tendencies. This information can be analysed in a structured
way in RI that allows retailers to have a better understanding of their customer base and to target promotions to customers who are most likely to react.
Data Granularity
Data granularity is decided for fact tables based on reporting requirements. Currently, data granularities are set for generic report requirements. Data may be available at lower levels in source systems and may not be available in Retail Insights because of requirements. Dimension data exists at the lowest hierarchy levels.
The data from transaction systems is transformed to accommodate the Retail Insights database structure. This data serves as the foundation for business measurements, but by itself it is not sufficient to answer many business questions.
Typically, data is held at a low granular level in Retail Insights. For example, sales data is held by location, item, and day attributes. There is one row in the sales fact table for every combination of these attributes. In most cases, however, the analyst wants to view data at higher levels in the product and organization hierarchies, and for a longer span of time than a single day.
Effective business intelligence requires facts to be held at a low granular level, while allowing measurements at any level in the organization where they are needed. For example, a location manager making an assessment of monthly sales at the department level wants a report showing total sales for each department. When the location manager spots a potential problem at the department level, the manager may want to focus analysis on the subclass, or even the specific items, for which problems exist. Retail Insights permits analysis at any level by storing information at a low granular level, while allowing reporting at higher summary levels.
In some cases, Retail Insights holds data at multiple levels, to facilitate analysis and improve performance. For example, sales facts are held by subclass and week, as well as by item and day (the location attribute is present in both tables). The result is that the same data exists in more than one fact table in the database. While redundant data improves performance by reducing the number of queries that must be serviced, it also requires more maintenance. Retail Insights uses redundant data in a few cases in which all customers benefit in terms of performance; in most cases, however, retailers must determine where redundancy is needed, based on their own requirements.
Metadata Organization
The Oracle Retail Insights presentation model is implemented in the form of two subject areas, as-is and as-was. (a subject area is also called a presentation catalog in the repository.) The following are the categories of data in the Retail Insights subject areas:
- Merchandise Insights
Merchandise Insights refers to data like sales and sales promotion, supplier, product, employee, and organization for as-is and as-was time analysis. Much of the data for merchandise insights is sourced from an OLTP system such as RMFCS.
- Customer Insights
The Customer dimension is supported for sales, sales promotion, customer loyalty, and promotions for as-is and as-was analysis. Data for customer analysis is usually sourced from a CRM system such as ORCE.
- Consumer Insights
The Consumer Segment dimension is supported on the same facts which are used for Customer (assuming a common link using Oracle Person IDs). The Consumer dimension
is used with the Consumer Segment dimension. Data comes from a combination of ORCE and Oracle Data Cloud.
The subject areas are as follows:
-
Retail Insights As-Was
-
Retail Insights As-Is
See Reporting on Oracle Analytics Repository Objects, for information about how to produce documentation about repository objects.
As-Was
The supporting attributes and metrics for as-was reporting are available in this subject area. On the reports on this subject area, the historical data is associated with the hierarchy of an attribute before a reclassification. For example, if an item is reclassified from the Snacks Department to a new Grocery Department, the previous history of the item stays with the old department (Snacks), which shows how the SKU performed in that department. Future transactions for the item will belong to its new department (Grocery).
As-Is
The supporting attributes and metrics for as-is reporting are available in this subject area. On the reports on this subject area, historical data is associated with the new hierarchy of an attribute after a reclassification. For example, if an item is reclassified from the Snacks Department to a new Grocery Department, the previous history of the item moves to the new department (Grocery).
Accessing the Applications
The Retail Insights Cloud Service includes a number of Oracle applications and tools bundled as a single cloud offering. The table below provides a list of common URLs for accessing these applications. The “xxx” prefix in the URL would be replaced by your customer identifier (such as mycompany1) and the “yyy” prefix would be replaced with your environment type (for production environments there is no prefix, for pre-production environments it may be “stage” or “test”).
Table 1-1 Application URLs
| Solution/Tool | Sample URL |
|---|---|
| Identity and Access Management (OCI IAM) | https://idcs-1234.identity.oraclecloud.com/ui/v1/myconsole?root=my-info |
| Analytics Classic and BI Publisher | https://{analytics tenant and region}/ui/analytics |
| Data Visualization (DV) | https://{analytics tenant and region}/ui/dv |
| AI Foundation (AIF) | https://{service}.retail.{region}.ocs.oraclecloud.com/{solution-customer-env}/ oraseui/ |
| Innovation Workbench | https://{service}.retail.{region}.ocs.oraclecloud.com/{solution-customer-env}/ ords |
| Process Orchestration and Monitoring (POM) | https://{service}.retail.{region}.ocs.oraclecloud.com/{solution-customer-env}/ POMJetUI/ |
| Retail Home | https://{service}.retail.{region}.ocs.oraclecloud.com/{solution-customer-env}/ retailhome/ |
Using Identity and Access Management
Oracle Cloud Infrastructure Console and the Identity and Access Management (OCI IAM) is Oracle’s cloud native security and identity platform. It provides a powerful set of hybrid identity features to maintain a single identity for each user across cloud, mobile, and on-premises applications. IAM enables single sign on (SSO) across all applications in a customer’s Oracle Cloud tenancy. Customers can also integrate IAM with other on premise applications to extend the scope of this SSO.
All applications in the Retail Insights Cloud Service leverage a single IAM login, which must be created for you by your IT or system administrators group. The link to access IAM will be specific to your company and should be provided to you. Roles will be assigned to your user at the time of account creation, which will determine the applications and tools you are allowed to access. For information on common activities you may need to perform in IAM, refer to the link below:
Finding Help
Each application within the Retail Insights and AI Foundation Cloud Services provides its own help content. Follow the steps below to locate and use this content from within each application.
Retail Insights
Retail Insights uses the Oracle Analytics platform as its user interface. The steps to locate the Help content for Oracle Analytics are summarized below. Note that Oracle Analytics is a platform shared by many Oracle applications, so the help content focuses on the user interface itself, rather than specific application data or metrics.
1. Login to the Retail Insights application using the /analytics URL as defined in the Application URLs section.
2. From the Home page, click on the question mark (?) icon and select Oracle Analytics from the menu.
3. Use the Task menu on the left side of the page to select a guide. The most common Retail Insights activities can be found in the Build Reports and Dashboards task.
4. You can also access the content directly from this URL: https://docs.oracle.com/en/ middleware/bi/analytics-server/build-reports-and-dashboards.html
Retail AI Foundation
The AI Foundation Cloud Services provides a user guide accessible from any page in the application. The steps to locate this help content are summarized below.
1. Login to AI Foundation application using the /oraseui/ URL as defined in the Application URLs section.
2. From the Home page, click on the question mark (?) icon and select Application Help from the menu.
Data Visualization
The DV application is bundled as a component of Oracle Analytics, and the help content for it is likewise embedded in the OAC documentation.
1. Login to the Data Visualization application using the /dv URL as defined in the Application URLs section.
2. From the Home page, expand the Navigator menu using the icon in the upper left corner of the screen.
3. Select the Academy menu option.
4. Select a task that relates to the area you need help on, or click Learn More… to view additional options.
5. You can also access the content directly from this URL: https://docs.oracle.com/en/ middleware/bi/analytics-server/visualize-data.html
Innovation Workbench
The IW application is bundled as a component of AI Foundation Cloud Services and is built using Oracle Application Express (APEX). The steps to locate this help content are summarized below.
1. Access the Innovation Workbench application using the /ords URL or using the link in the AI Foundation task menu.
2. On the Login page (before logging in) click the down arrow at the bottom of the screen.
3. Select one of the guides from the list, such as “Learn More”.
4. You can also access the content directly from this URL: https://www.oracle.com/database/ technologies/appdev/apex.html
Oracle Analytics User Interface
Oracle Analytics is the interface that provides the OLAP tools for Oracle Retail Insights. Oracle Analytics is a comprehensive solution that you can use to create, modify, schedule, and distribute reports to end users throughout your retail enterprise. You access Oracle Analytics through your Web browser. Oracle Analytics is the metadata built on top of the Retail Insights data model, and it can be used for executing and scheduling existing reports or creating ad hoc reports.
For information about creating reports with Oracle Analytics, the primary reference is the Oracle Fusion Middleware User’s Guide for Oracle Analytics Server .
Oracle Analytics content can be developed in many ways for your enterprise. The illustrations in this guide show the default installation of Retail Insights dashboards and reports. You can create your own dashboards to organize your reports and other objects you create. You can also develop report schedules and automated distribution mechanisms, to direct reports to the people who need them.
Dataset generated from RI subject areas, and then use the Explain feature on the resulting project’s attributes.
-
RI attributes that require HTML formatting are not supported in DV (such as product and attribute images)
-
DV projects cannot be delivered using Agents or exported directly to Email
Supported Languages
Oracle Analytics provides numerous language options for users; however, not all languages supported by Oracle Analytics are supported by Retail Insights. The following languages are fully supported for Retail Insights users (many others supported by Oracle Analytics have partial translations but are not updated regularly at this time):
-
Arabic
-
English
-
French
-
German
-
Italian
-
Polish
-
Portuguese (Brazil)
-
Spanish
Concurrent and Maximum Users
The Retail Insights primary subject area is set to a maximum of 200 connections on the connection pool, which limits the number of active queries at any one time. This is not the same as the number of concurrent users, since one user is able to run multiple reports and dashboards at the same time and each execution may use a separate connection from the pool. There is no separate limit on the number of users logged into the system, but you may notice performance degradation if the number of users in the system approaches the maximum number of connections. Users that attempt to run reports after the maximum connection count is reached may not be allowed to do so and would need to try again later. This limitation is a global setting and cannot be changed per-environment.
User-Based Data Filtering
Retail Insights supports automatic filtering of report results based on users and groups mapped to levels of the merchandise or organization hierarchy. For example, users may be assigned specific stores or districts for which they are responsible. When executing reports at this level of detail in RI, they will automatically be limited to results matching their assigned locations. The levels of the organization hierarchy that are supported with this functionality include Chain, Area, Region, District, or Store. Merchandise hierarchy includes division, group, department, class, and subclass (but not below that).The retailer is responsible for populating the user, group, and organizational mapping tables as part of the nightly file uploads to RI. Refer to the Retail Insights Interfaces Guide in My Oracle Support for more details on these interfaces.
Note that the data filtering is not applied to reports which are not at the specified level of the user/hierarchy mappings, as the identifiers would not be present on the tables used by such
queries. Additionally, users that are not present on the data filtering tables will not have any restrictions applied to query results.
More Information
For more information on creating custom reports, see the following:
-
Oracle Retail Insights Implementation Guide
-
Visualizing Data and Building Reports in Oracle Analytics Cloud