| Oracle® Retail Insights User Guide Release 17.0 E95062-02 |
|
 Previous |
 Next |
A report is primarily constructed of logical columns. Logical columns include:
Facts
Attributes
Metrics
You can constrain (limit) the data to be included in a report with filters and prompts:
A filter constrains the data in the report so that the report shows only the information that the user of the report wants to see. For example, you can use filters to limit reports to show information only about certain locations, items, and time periods. See "Filters" later in this chapter.
A prompt allows the user of a report to select how to filter data in the report. For example, a prompt can ask the user to select a time period or location. See "Prompts" later in this chapter.
A fact is a column that contains numeric data in one or more database tables. For example, the Sales fact SLS_AMT_LCL (sale amount) allows access to the corresponding column in the sales tables in the Retail Insights database.
Facts are the basis for the formulas used to construct business metrics. For example, the formula SUM(SLS_AMT_LCL) is the basis for the calculation of gross sales amount.
By themselves, facts have no meaning. The statement ”inventory on hand was 10” only has meaning when given the context of time and place. Attributes place facts in context and make them meaningful. An attribute is the general description of some aspect of the business, such as location, day, or item. Examples are Minneapolis (location), April 16, 2011 (day), and scarves (item). Facts become useful only when qualified by one or more attributes. Facts are most often qualified by multiple attributes (see "Attributes and Dimensions" later in this chapter).
The majority of facts are additive, meaning that two facts of the same type can be added to create a meaningful number. For example, the sum of total sales for each of the days in a week gives the total sales for that week, and the sum of total sales for each month can give the total sales for a quarter or year.
Some facts are semi-additive, meaning that facts of the same type cannot be added in all circumstances. For example, adding receipts of an item to existing inventory produces a meaningful result, a new count of inventory on hand. On the other hand, adding the number of units on hand for every day during a week does not result in a meaningful weekly total; rather, the amount of inventory is expressed as a position for some time period such as day or week.
The data in positional fact tables reports the state of an entity at a certain point in time, rather than the total activity of an entity, these facts cannot be simply summed over time.
For example, you could ask the question: ”What was my total unit retail for this week?” This is not the correct question. Aggregations of positional facts along the axis of time take end-of-period snapshots that answer the question: ”What was my unit retail at the end of this week?”
For all aggregations along the time axis, aggregation programs run daily. For aggregations of positional facts within a period, this results in a period-to-date position, rather than an end-of-period position. After the period is complete, the last run for that period results in the desired end-of-period position.
An attribute describes some characteristic of an entity such as a product, a time period, or a store location. Attributes are used to aggregate data and constrain data in a report.
Attributes that are not part of the same dimension are related when they exist in the same fact table. The attributes item, location, and week are not formally related in a hierarchy; however, all of these attributes exist in the sales fact table. This means that questions can be answered by one or more of these attributes.
For example, you might ask first to see sales data by location and week. Because the fact table contains the attribute Item as well, the data can be reorganized using the Item attribute. As a general rule, information can be referenced by any attribute, or combination of attributes, present in the fact table.
Dimensions are collections of related attributes. These are some examples of Retail Insights dimensions:
Organization
Product
Promotion
Business Calendar
Attributes can be related to each other through parent-child relationships. In a relationship of this type, the child attribute belongs to only one parent attribute. For example, the Location attribute in the Organization hierarchy is defined as the child of the Region attribute. All elements of the Location attribute exist in only one region. Because the Region attribute is also defined as the child of another attribute, the relationship of the Location attribute to all other attributes in the hierarchy can be predicted.
Through these relationships, you can drill into data. Investigation of a business problem often begins at a summary level and moves to a detailed level as analysis progresses. Drilling allows you to focus on parts of the data set where problems are identified.
Oracle Retail Insights contains an extensive set of metrics (measures) and key performance indicators (KPIs) designed for business intelligence in a retail environment.
Metrics are performance measurements, typically numeric, that allow you to analyze business performance. Metrics range in complexity, from a simple metric that sums the values in a single fact column, to highly complex calculations that contain mathematical operators.
A metric can be viewed as a statement that specifies how a performance measure is calculated. The basic component of a metric is a formula that specifies the calculation to be made. A metric can contain other components that specify additional criteria for calculating the metric.
Each metric has a formula that specifies how the metric is calculated. The formula for a simple metric specifies a fact and a function for the fact. For example, the following formula calculates a sum of values in the sales fact column:
SUM(SLS_AMT_LCL)
where SLS_AMT_LCL is the fact and SUM is the function to be performed.
In a compound metric, the formula contains two or more metrics and a formula for calculation. For example, a formula for a compound metric might calculate the average sales value by dividing the net sales metric by another metric that calculates the number of units sold.
As another example, the following compound metric formula calculates average sales value per unit using two simple metrics:
Sales Value / Sales Units
Compound metrics can also be used to create other compound metrics. For example, the formula for the stock turn metric employs a simple metric (Sales Value) and a compound metric (Avg Stock Retail Value):
Sales Value / Avg Stock Retail Value
Avg Stock Retail Value in the preceding formula is itself a compound metric, constructed from three simple metrics that access base formulas for the facts used in the calculation:
(SUM(BOH Retail Value + EOH Retail Value ) / (No of Weeks with Stock + 1))
Variance metrics are common compound metrics in Retail Insights. Variance metrics compare the change or difference in two different data points.
”Percent change” and ”percent variance” metrics in Retail Insights are defined as:
(A-B)/B
The following are some examples of percent change and percent variance metrics.
Table 2-1 Percent Change and Percent Variance Metrics
| Metric | Formula |
|---|---|
|
Gross Sales Amt Var LY |
(Gross Sales Value - Gross Sales Value (Last Year))/Gross Sales Value (Last Year) |
|
Gross Sales Qty Var LY |
(Gross Sales Qty / Gross Sales Qty LY) - 1 |
|
Gross Profit Var LY |
(Gross Profit / Gross Profit LY) - 1 |
|
Net Sales Amt WTD Var LY |
(Net Sales Amt WTD / Net Sales Amt LY WTD) - 1 |
|
Net Reg Sales Qty MTD Var LY |
(Net Reg Sales Qty MTD / Net Reg Sales Qty LY MTD) - 1 |
The level component of a metric specifies the attribute level to which a metric aggregates. By default, a metric aggregates to the level of the attributes on the report.
Some complex metrics require more than one level of aggregation in formulas. For example, you might want a report that shows the percent contribution sales value of each location to its region. You must know the sales value for each location and the total sales value for region to which it belongs to create the formula for this metric:
Sales Value (Location) / Sales Value (Region)
A metric that specifies a level of aggregation other than the default level for the report is called a level metric. Retail Insights includes many level metrics for sales and profit for attributes in the Organization and Product dimensions. In Retail Insights, when a metric has a predefined dimension level, the name of the attribute level appears in parentheses after the metric name. The following are some example level metrics for sales value in the Product hierarchy.
Sales Value (Company)
Sales Value (Group)
Sales Value (Department)
Sales Value (Class)
You can use level metrics to build compound metrics that measure the contribution of lower-level elements to higher or parent levels. The following are some examples of these contribution metrics.
Table 2-2 Contribution Metrics
| Metric | Formula |
|---|---|
|
Sales Amt Item Contribution to Department |
Sales Value /Sales Value (Department) |
|
Sales Amt Contribution to Location |
Sales Value/Sales Value (Location) |
|
Sales Amt Division Contribution to Tot |
Sales Value/Sales Value (Division) |
|
Profit Item Contribution to Department |
Profit Value/Profit Value (Department) |
|
Profit Division Contribution to Tot |
Profit Value (Division)/Profit Value (Company) |
Time-based comparisons are an essential part of analysis at almost every level in a retail environment. Typical examples are the comparison of sales value for the current season-to-date to the same period last year, or the retail value of inventory compared to the previous week.
Retail Insights time conversion functions use the following Oracle BI time series aggregation functions:
Ago()
This function calculates the aggregated value from the current time back to a specified time period.
ToDate()
This function aggregates a measure attribute from the beginning of a specified time period to the currently displayed time.
The Ago() and ToDate() functions are described in the following documents:
Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition
Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition
See Appendix A, "Time Series Conversion Functions," for information about the time series conversion functions.
A filter constrains the data that is retrieved from the database. The filter attached to a report limits the data that is retrieved for the metrics in the report. For example, a filter can limit the information in a report to a particular month, department, and location.
Filters generally constrain all of the metrics in a report. In some cases, however, it is necessary to place additional constraints on individual metrics in a report. When a condition is applied to a single metric, it does not affect the other metrics in the report. A metric condition plays the same role in a metric that a filter plays in a report, limiting the data that is retrieved based on one or more conditions.
In Retail Insights, sales and return amounts are segmented by price type according to the retail price type: regular, promotion, or clearance. Sales fact tables hold sales and return amounts in two fact columns, SLS_AMT_LCL and RET_AMT_LCL. The retail price type is indicated by a code for each row in the table. A sales metric retrieves all values, regardless of type, unless a price type is specified. To specify the price type, a filter is attached to the metric. For example, regular price type is indicated in the fact table by a value of 1. A filter stating that price type must equal 1 is attached to a metric. Queries for this metric limit the data to rows in the fact table that have a retail type of 1.
You can build your own filters with Oracle BI. Retail Insights does not include any packaged filters.
Prompts allow any end user of a report to select the data used in the content of a report. Using prompts, you can customize filter criteria and other parts of a report, allowing multiple users to use the same report to answer different business questions.
In Oracle BI, there are two kinds of prompts:
Dashboard prompts
A dashboard prompt filters all reports on a dashboard page. A dashboard prompt can prompt the end user for multiple filter criteria.
Inline prompts
An inline prompt applies to only one report. You can use an inline prompt to prompt the user about the content of an individual report column.
An inline prompt can prompt only for the dimensions that exist in the report. A dashboard prompt can prompt about any dimension, even if it does not exist in a particular report.
