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.
4
Creating and Modifying Reports
Getting Started with Oracle Analytics
Retail Insights uses the Oracle Analytics platform as its user interface. Learning about the core features and functionality of this interface is a necessary first step in accessing your data and building analyses and dashboards. Oracle Analytics is split into two modules, Analytics Classic and DV. Analytics Classic uses the concepts of analyses and dashboards to display your data, while DV uses workbooks which are a combination of both types of objects in one modern interface.
An analysis is a query against your organization’s data that provides you with answers to business questions. Analyses enable you to explore and interact with information visually in tables, graphs, pivot tables, and other data views. You can also save, organize, and share the results of analyses with others. Users of Retail Insights can be granted the ability to view existing analyses or create new ones, depending on your permission levels.
Once an analysis is created and saved, it can be added to dashboards. Dashboards can include multiple analyses to give you a complete and consistent view of your company’s information across all departments and operational data sources. Dashboards provide you with personalized views of information in the form of one or more pages, with each page identified with a tab at the top. Dashboard pages display anything that you have access to or that you can open with a web browser including analyses results, images, text, links to websites and documents, and embedded content such as web pages or documents.
To learn more about creating and viewing analyses in Oracle Analytics, start here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/build-reports-and-
dashboards.html
Within DV, use workbooks to access your data, build reports, design multi-page dashboards, and visualize the results using graphs. Workbooks combine the concepts of analyses (which are singular tables or graphs on one dataset) and dashboards (which include many different views and filters).
To learn more about creating workbooks, start here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/visualize-data.html
Analysis Methods
The Retail Insights presentation model is designed in two different subject areas based on the reporting scenarios and analysis methods that Retail Insights supports:
-
Retail As-Is
-
Retail As-Was
A single instance of Retail Insights offers as-is and as-was analysis for the slowly changing dimensions Product and Organization. Slowly changing dimensions are dimensions with data that changes infrequently and unpredictably, rather than changing on a time-based, regular schedule. For example, a record for an item that you sell might be moved from one subclass to another in the product hierarchy, and such changes may happen only a few times a year. RI tracks such movements as part of the as-is and as-was subject areas.
Although facts and dimensions appear similar across these subject areas, they are modeled differently in the Oracle Analytics repository. For example, Item or Subclass or Class might appear similar in all subject areas, but their sources and join conditions are different to support the appropriate method of reporting.
As-Was reporting is only available to Retail Insights subscribers. As-Is reporting should be used for all other Retail Analytics & Planning (RAP) applications.
As-Is Reporting
This type of reporting reflects the current nature of facts and dimensions as they are known to be true today. The performance of a dimension is tracked according to the current state of the dimension in a hierarchy without regard to time period.
If hierarchies have changed or items have been reclassified, as-is reporting shows history as if it had occurred under the current hierarchy or parent. Performance of the previous hierarchy or parent cannot be seen in as-is reporting.
See “Reclassification” in Dimensions and Attributes for more information.
As-Was Reporting
As-was reporting reflects the current values of transactions tied to a dimension value that was applicable at a former point in time. The performance of a dimension is tracked along the changes it has undergone in a hierarchy over a period of time. One of the effects of reclassification is that the presence of two hierarchies or parents makes it possible to compare an entity’s performance before and after it undergoes this change.
In fact tables, all history is kept under the former hierarchy or parent, while all data after a reclassification is under the current hierarchy or parent.
Drilling allows you to see a particular report at a given level, and then view the same report at a lower level, to examine data at a finer level of granularity. This type of analysis makes welldefined hierarchies extremely important. Drill paths must be clear, and facts must add up between levels of aggregation. This requirement explains why changes to the position of an entity in the hierarchy are considered major.
Support for Multiple Currencies
Oracle Retail Insights supports five currencies:
-
Local Currency
-
Document Currency
-
Global Currency 1
-
Global Currency 2
-
Global Currency 3
Edit Prompt
Prompt for Request Variable vy LY_SHIFT
Label Shift Type Description Ly Shift Setting
Choice List Values Values Custom Values Values
4 Options
General More
Variable Data Type Defauilt (Test) ¥
Using Variables
Retail Insights makes use of variables to provide certain values such as the current business date. It is important to understand how to use variables in your analyses, as this is the primary way you can get results that change dynamically based on your business activities. You can reference several types of variable in your analyses, dash-boards, and actions: session, repository, presentation, request, and global. Content authors can define presentation, request, and global variables themselves but other types (session and repository) are defined for you and update automatically.
For more information about the types of variables available and how they are used in Oracle Analytics, start here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/advanced-
techniques-reference-stored-values-
variables.html#GUID-53F62733-303D-4524-93FC-5BD5A10FF4AC
In addition to the system variables defined by Oracle Analytics, Retail Insights provides the list of repository variables below.
Table 4-3 Repository Variables
| Variable Name | Description |
|---|---|
| Today | Returns today’s calendar date |
| Yesterday | Fetches the previous date based on the sysdate |
| LastYearDate | Returns the last year date based on the current sysdate |
| CurrentDate | Returns the current Business Date in the system |
| CurrentWeek | Returns the current Fiscal Week value |
| CurrentQuarter | Returns the current Fiscal Quarter value |
| YTD | Returns the current Fiscal Year value |
| LastWeek | Returns the previous Fiscal Week value |
| LastWeek2 | Returns the Fiscal Week value of two weeks ago |
| LastWeek3 | Returns the Fiscal Week value of three weeks ago |
| LastWeek4 | Returns the Fiscal Week value of four weeks ago |
| NextWeek | Returns the next Fiscal Week value |
| FromPeriod | Fiscal Period value which is one year back from ToPeriod |
| ToPeriod | Fiscal Period value for the current period, can be used with FromPeriod to get a 13-month rolling window |
| Last 30 days | Returns all Fiscal Week names for the last 30 calendar days |
| CurrentGregWeek | Returns the current Gregorian Week value |
| CurrentGregMonth | Returns the current Gregorian Month value |
| CurrentGregQuarter | Returns the current Gregorian Quarter value |
| CurrentGregHalfYear | Returns the current Gregorian Half Year value |
| CurrentGregYear | Returns the current Gregorian Year value |
| LastGregMonth | Returns the previous Gregorian Month value |
ee
When using calendar variables for filtering, they will supercede any calendar filters at the top of the workbook, so it is best to choose which method you are using for the entire workbook (either all tables will by dynamically filtered by calendar variables, or they will be prompted at the top of the workbook and users must select values). Other filters which are unrelated to the variables, like Product filters, can still be used at the top of the workbook and applied to all views uniformly.
Creating Reports for Sales Transactions
One of the most common uses of Retail Insights is reporting on your business’s sales transactions. RI maintains a historical record of all sales transactions which occur both in your retail stores and in non-retail channels such as your web store and warehouses. It is therefore critical to understand the many ways in which RI presents sales data to the user, so that you can quickly and accurately report on the information that’s relevant to you.
Types of Sales Metrics
RI broadly splits sales metrics into three basic types: Gross Sales, Net Sales, and Returns. Net Sales metrics are always calculated as (Gross - Returns) for a given quantity. Within each type, RI provides four basic measures of sales: Quantity, Retail Amount, Cost, and Profit. Examples of these basic metrics are provided in the table below.
Table 4-4 Sales Metrics
| Sample Metric | Explanation |
|---|---|
| Gross Sales Qty | Total sales units, not accounting for returns. |
| Gross Profit | Difference between the retail selling value and cost of goods sold, equivalent to Gross Margin $. |
| Net Sales Amt | Net retail sales amount, calculated as gross sales amount minus returns. |
| Return Profit | Difference between the retail amount of returns and the cost value of those returns. Represents the profit lost due to returned units. |
Adding these metrics to an analysis allows us to easily see how they relate to each other. For example, we can create an analysis with gross, net, and returned sales units and validate that net = gross - returns.
1. Start a new analysis in the Retail Insights As-Is subject area.
2. Locate the Sales folder in the left side panel of the Criteria tab and expand it.
3. Scroll down until you locate the Gross Sales Qty metric, and double-click it to add it to the analysis.
|
B DepartmenEF of Region EF of Fiscarvear Gross Sales Oty Z Gross Sales AmM it) Gross Prom Zit
| Retail Type | Gross Sales Oty | Gross Sales Amt | Gross Profit |
|---|---|---|---|
| c | 2.322 | 347,377 | 81,226 |
| P | BS | 12,121 | 2 808 |
| R | 19,345 | 4325791 | 2557654 |
SCC‘
Moré Cobumns …
| Business Calendar
-
| Fiscal Date
-
| Fiscal Day Name a= Fiscal Half Year a= Fiscal Period
-
=: Fiscal Period End Date
-
a= Fiscal Period Start Date a= Fiscal Quarter
“Y Fiscal Weekis Weekis equal to/is in 2017WEEK10
| Fiscal Year | Retajl. Type | Gross SalesQty | Gross SalesAmt | Gross Profit | GrossSales QtyWTD | Gross Sales Qty MTD | GrossSales Qty YTD |
|---|---|---|---|---|---|---|---|
| 2017 | Cc | 85 | 14,352 | 5.207 | B85 | 85 | 1,797 |
| R | 1,336 | 264627 | 144,914 | 1,336 | 1,336 | 10,862 | |
| 1 | ] } |
Business Calendar Sales | GEE Fiscal Year =f FiscalWeekiFiF 3% Gros
| Loc | Fiscal Date | Gift CardAmount Sold |
|---|---|---|
| 041-FIFTHAVENUE440004 | 64/2017 | 450 |
a
Table 4-8 (Cont.) Calculated Sales Metrics
| Calculated Value | Related RI Metric Name |
|---|---|
| Transaction Count | Trx Count |
Creating Reports for Inventory Positions
Retail Insights holds stock position at a very low level, which is the ending position for every day for every item at every stockholding location. RI maintains a large variety of inventory metrics which span the entire lifecycle of your merchandise (from the initial order to in-transit, on-hand, RTV, and several others).
Types of Inventory Metrics
The stock position measures include quantity, retail value, and cost amount (usually interfaced from source systems based on weighted average cost calculation). There are three distinct groupings of stock position in Retail Insights:
-
On-hand stock (goods owned by the retailer and received in a location)
-
In-transit stock (goods owned by the retailer, received into one location such as a distribution center, but currently in transit to another store or warehouse)
-
On-order stock (goods on an approved Purchase Order which have not yet been received)
Two examples of on-hand measures are ending on-hand (EOH) for a time period, as well as beginning on-hand (BOH) for a time period. The EOH position for week 1 is the BOH position for week 2. On Order positions are tracked only at the end-of-period level, as the primary reporting method for those values is to show the current position at a given point in time.
Metrics pertaining to owned inventory (such as on-hand and in-transit) are further broken down by their clearance status. RI use the nomenclature “Clr” and “Non-Clr” to represent inventory that is either on clearance or at regular price.
Combining these metrics in an analysis will allow us to comprehensively track the position of our inventory over time.
1. Start a new analysis in the Retail Insights As-Is subject area.
2. Locate the Inventory Position folder in the left side panel of the Criteria tab and expand it.
3. Using the Search box, find and add the following metrics and attributes to the analysis: Fiscal Week, BOH Qty, EOH Qty, In Transit Qty, On Order Qty
4. Note how BOH and EOH Qty match their positions over time, but the others are just a single positional value.
| Fiscal We@k® | BOH Qty | EOHQty” | InTransitQty | OnOrder Oty | |
|---|---|---|---|---|---|
| 2016WEEKO1 | 140,389 | 0 | o | * | |
| 2016WEEKO2 | 140,389 | 152,054 | 0 | O | fe |
| Z2016WEEKO3 | 152,054 | 161,669 | 0 | iH | |
| Z2016WEEKOS | 161,669 | 162,790 | 9.492 | 0 |
| Fiscal Week | BOHOty | EOHOty | InTransitOQty | OnOrderQty | EOHCIrQty | In Transit Cir Qty |
|---|---|---|---|---|---|---|
| 2016WEEKO1 | 140,389 | 0 | 0 | 27,163 | ) | |
| 2016WEEKO2 | 140,389 | 152,054 | 0 | 0 | 29,805 | 0 |
| 20 16WEERKOS | 152,054 | 167,669 | 0 | 0 | 26,123 | 0 |
| Z20716WEEKO4 | 161,669 | 162,190 | 9492 | 0 | 31,141 | 857 |
| Edit Filter | @ x | |||
|---|---|---|---|---|
| Column = | Fiscal Period fy | |||
| Operator | isLIKE (pattern match) | ¥ | ||
| Value | =2017% | ro | ||
| Add MoreOptions ¥ Cl | ear All | |||
| Loc | Fiscal Period | EOQHCost | InTransitRetail | OnOrder Oty |
| 01-FIFTHAVENU | E 440001 2017 Period01 | 624,023 | 93,316 | 0 |
| 2017 Penodd? | 698.537 | 68,729 | 0 | |
| 2017 Periodd3 | 593,860 | 37,436 | 0 | |
| 2017 Perioddd | 599,930 | 6,870 | 0 | |
| 2017PerioddS | 621,685 | 143,246 | 719 |
| Reason Code | Reason Description | RTV Units | RIV Retail | RTVCost |
|---|---|---|---|---|
| Oo | Overstock | 17 | 3,065 | 3,296 |
| W | ExternallyInitiatedRTV | 4 | 136 | 326 |
< k& New Workbook
folders. Every user automatically has a personal folder created for them when they first log into the system.
- Shared objects should be saved in the Custom folder in the Shared Folders root directory. These objects are visible to all users, except where administrators have restricted the permissions. Do not create shared content outside of the Custom folder.
All objects created in the shared Custom folder are meant to be centrally managed by a customer administrator or BI team lead. Objects should be organized by functional group or business process, and have permissions assigned at the folder level to restrict access to specific roles or groups. Both folders and individual objects can have permissions assigned to limit the ability to view, modify, or execute them.
To learn more about managing content in the Oracle Analytics classic user interface, start here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/typical-workflow-managecontent.html
If you are using Data Visualizer to manage content, then start here:
Sharing Content in Retail Insights
Retail Insights uses the native capabilities of Oracle Analytics Server to publish and share content. Use one of the following methods to share content with other users:
-
Create agents to deliver content to users by email or to their Home page in RI (only for analyses and dashboards)
-
Create content in the shared Custom folder and set the permissions to share it with other users
-
Create BI Publisher reports with a bursting query to send files to Object Storage or email
-
Export the analysis or workbook to a file, such as an Excel spreadsheet or PDF document
To learn more about sharing content in the Oracle Analytics classic user interface, start here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/automate-business-processesagents.html
If you are using Data Visualizer to share content, then start here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/import-export-and-share.html
Known Issues and Common Questions
The following are additional considerations and suggestions for designing Oracle Retail Insights reports.
-
Stock ledger reports cannot be created below subclass and week, because data for these fact areas have the lowest levels of subclass and week from the source systems.
-
Comp and BOH (beginning on-hand) inventory metrics are only supported at week level. You must also use a prompt or filter on week or a higher level of the time dimension.
-
When reporting on any time transformation metrics like YTD, you must have a prompt or filter on the fiscal calendar, typically for one specific day or week.
-
To compare as-is and as-was results for the same report, create a single dashboard with these reports on different pages. The same report cannot include both as-is and as-was results.
-
Wherever there are many-to-many relationships, you must have prompts or filters on one value to avoid double-counting. For example, there can be overlapping seasons, and the same items can belong to both seasons. If there is no filter or prompt on season, the items common to both seasons can be double-counted. Another example of this is an item list, where the same item can be in multiple item lists. A filter or prompt on item list will ensure that correct data is displayed.
-
Retail Insights does not store attribute groups that do not have associated values. For example, Retail Insights will not consume location lists that do not have any associated locations. It will not show item UDAs that are not linked to any items.
-
Customer Order Demand cannot be analyzed by the Fulfillment Channel.
-
• Order Fulfillment cannot be analyzed by the Demand Channel. • Demand and Fulfillment analysis is not supported by Season Dimension. • Market Item and Retail Item side-by-side analysis is not supported. • Season Based reporting is not supported for Market Item and Consumer Reports. • Market Item reporting is only supported for the As-Is Subject area.
-
When combining data from multiple facts which make use of different dimensions (for example, Inventory Position and Purchase Orders), go into the Advanced tab of the analysis and select the checkbox for Show Total value for all measures on unrelated dimensions. This is required to see results when a dimension is not present on some facts, such as viewing EOH Qty with Purchase Order Number and PO Ordered Qty.