| Oracle® Retail Insights Cloud Service Suite Operations Guide Release 18.0.001 F17010-01 |
|
 Previous |
 Next |
| Oracle® Retail Insights Cloud Service Suite Operations Guide Release 18.0.001 F17010-01 |
|
Previous |
Next |
This chapter describes how Retail Insights processes dimension data from the source system or systems. This chapter presents the following dimension data concepts:
An overview of dimension data processing
The dimensions in Retail Insights
Detailed dimension processing flows
|
Note: Dimension data extraction programs are available for retailers with Oracle Retail source applications (that is, RMS and RPM). These programs are packaged with the applications. |
The Retail Insights data model consists of the following dimensions:
Company
Employee
Item-Location Trait
Item-Supplier
Item-List
Item-UDA
Item-Differentiators
Item-Pack
Item-Season
Location List
Location Trait
Organization
Channel
Product
Season
Promotion
Retail Type
Supplier
Supplier Trait
Time
Business Calendar
Gregorian Calendar
Status
Reason
Customer Loyalty Account
Customer Loyalty Program
Stock Count
Discount Type
Clearance
Dimensions in Retail Insights data model behave as SCD Type 1 or SCD Type 2. The behavior of the dimensions to act as SCD Type 1 or SCD type 2 is embedded in the ODI code packaged with the Retail Insights product. Any changes to this behavior require customization to the code.
The Product and Organization dimension's hierarchy changes behave as SCD Type 2 changes and are required to capture changes 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). Attributes other than hierarchy specific attributes are tracked as type 1 changes within these dimensions.
The history of an entity before and after the change can be tracked and compared if the dimension is set to behave as Type 2. That is, suppose an item is 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 (perhaps there is a need to track that item in relation to different items in the system), Retail Insights still needs to track sales for that item from its new location in the product hierarchy, both before and after the change.
Because product and organization are aggregating dimensions, a hierarchy change impacts the following areas:
Dimension programs are executed to generate new surrogate keys for the changed data, while keeping the old data (including surrogate keys) unchanged.
New transactions/facts are processed with new dimension information as part of the batch.
As Is aggregate programs are executed to re-calculate the impact on previou.sly calculated data and also aggregate the new data using the latest hierarchy.
Positional facts (namely: Inventory Position, Net Cost, Base Cost and Pricing), need to close the records using the old hierarchy and open the new records. For more information, see Chapter 4, "Fact Data Concepts".
A type 1 change means that an attribute of an entity is changed, but its position in the hierarchy remains the same. For the dimensions that are not hierarchical, an attribute value is changed and it is not required to capture both the new and historical values for this attribute.
Dimensions other than Product and Organization are tracked as type 1. One example of a type 1 change is the modification of a description field in a dimension. That is, a description of a subclass is changed from "Humorous Cards" to "Funny Cards". This type of change does not alter the relationship of 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.
By updating the records on dimension tables that have type 1 attributes does not impact the data processed on the fact tables as the associated surrogate keys remain unchanged.
During the actual processing of data, there are four kinds of actions that can happen to a dimensional entity in the Retail Insights:
Insert: When an entity is created, it is inserted into the system. For example, introduction of a new item in the company.
Type 2 Change: With type 2 change, an entity is effectively closed and re-inserted, so that its history before and after the change can be tracked and compared.
Type 1 Change: With type 1 change, the attribute of the entity is updated with a new value but the surrogate key of the impacted row in the table remains unchanged.
Close: When an entity is no longer active, it is considered to be closed. Although closing an entity in a transactional system often involves deleting it from the system entirely, in an analytical system like Retail Insights, the entity's record is retained so that its history can continue to be reported. These records are marked with CURRENT_FLG as 'N'.
One exception in Retail Insights are following dimensional matrices: Item-List, Item-UDA, Item-Diff, Location List and Location-Traits, where only the current relationship between two source system identifiers (and their surrogate keys) is maintained. Note the following exception to this rule:
For Item-Pack relationship on the W_RTL_ITEM_GRP2_D table, where deleted Item-Pack relationships, closed items, and reclassed items are all kept on the table.
The following system columns exist on the dimension tables for tracking and debugging purposes. Note that not all system columns will be populated for all the target tables as the usage is decided based on the requirements.
Dimension Table's system fields:
CREATED_BY_WID: Currently not used by Retail Insights.CHANGED_BY_WID: Currently not used by Retail Insights.CREATED_ON_DT: Currently not used by Retail Insights.CHANGED_ON_DT: Currently not used by Retail Insights.AUX1_CHANGED_ON_DT: Currently not used by Retail Insights.AUX2_CHANGED_ON_DT: Currently not used by Retail Insights.AUX3_CHANGED_ON_DT: Currently not used by Retail Insights.AUX4_CHANGED_ON_DT: Currently not used by Retail Insights.SRC_EFF_FROM_DT: Effective start date of the dimension record received from the source system.SRC_EFF_TO_DT: Effective end date of the dimension record receieved from the source systemEFFECTIVE_FROM_DT: Effective start date of dimension record within the data warehouse system. This date is same as SRC_EFF_FROM_DT, if available.EFFECTIVE_TO_DT: Effective end date of dimension record within the data warehouse system. This date is same as SRC_EFF_FROM_DT, if available.DELETE_FLG: Currently not used by Retail Insights.CURRENT_FLG: This flag indicates if the record is current within the data warehouse or not. Valid values include 'Y' or 'N'W_INSERT_DT: This is the date on which the dimension record was first inserted into the data warehouse system.W_UPDATE_DT: This is last date when the dimension record was updated within the data warehouse system.DATASOURCE_NUM_ID: This column is the unique identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, Oracle recommends that you define separate unique source IDs for each of your different source instances.ETL_PROC_WID: System Field. This column is the unique identifier for the specific ETL process used to create or update this data.INTEGRATION_ID: This column is the unique identifier of a dimension or fact entity in its source system. In case of composite keys, the value in this column can consist of concatenated parts.TENANT_ID: Currently not used by Retail Insights.X_CUSTOM: This column is used as a generic field for customer extensions.
Most dimensional entities in the Retail Insights have both keys (typically referred to as 'surrogate keys' or 'pseudokeys') and business keys.
Business keys can be an individual entity or a combination of entities given to the entities when it was created in the source system. However, in Retail Insights, this identifier cannot always be used to uniquely identify an entity. An entity may undergo a major change like a subclass moves to a new class, where it is closed and reloaded in order to mark the change in hierarchy, so that history can be tracked before and after the change. It may also be deleted or deactivated in the source system. All these situations result in multiple records in the Retail Insights tables for the same entity.
In order to distinguish between different states of the same entity, or different entities with the same business key, the Retail Insights must use some other value to uniquely mark it. A surrogate key is a unique value used to identify an entity in the Retail Insights. A new key is attached to an entity whenever it is inserted into a data mart dimension table. Surrogate keys are maintained within database sequences. Every dimension, fact or aggregate table has one corresponding sequence in the database and is incremented every time a new record is inserted. Surrogate keys in the Retail Insights data model are named as ROW_WID for each table and while they are referenced in the fact or aggregate tables then they are renamed to dimension specific names. For example, ROW_WID of W_MCAL_DAY_D table is referenced as DT_WID in W_RTL_SLS_IT_LC_DY_A table.
Note that the business key is used as basis for maintaining surrogate key as the business key is the entity that let's the system identify same or different entities coming from the source systems.
Retail Insights supports As Was, As Is and PIT reporting types, refer to the Oracle Retail Insights User Guide for more details on what these reporting types mean. For implementing any of these reporting types or combination of reporting types, refer to the Oracle Retail Insights Implementation Guide.
This section explains how these reporting types are supported by the Oracle Retail Insights Data Model.
As Was reporting is supported by set of base fact tables and aggregate tables (which are described in detail in the next chapter on fact data concepts, along with corresponding dimensions.
As Was reporting is enabled by the dimension surrogate keys and their join to the corresponding fact table's dimension key. For example, ROW_WID of W_PROD_CAT_DH is referenced as PROD_DH_WID in the sales aggregate table W_RTL_SLS_SC_LC_DY_A. ROW_WID on W_PROD_CAT_DH table is the surrogate key that maintains any changes to the Subclass level and above within Retail Insights data model. By joining these dimension and aggregate column, an As Was report can be created on product hierarchy. Since hierarchy changes are tracked as type 2 changes in the Retail Insights data model for product dimension, every surrogate key will point to a specific hierarchy at that point of time and will produce the desired results for the As Was report.
As Is reporting is enabled by a type 1 attribute that exists on both Product and Organization hierarchies and also by supporting aggregate tables. For example, with every change to the hierarchy a new row is inserted into the product dimension table generating a new ROW_WID for the same business key and at the same time a type 1 attribute called SCD1_WID is also maintained on W_PRODUCT_D table and the value of this column is persistent for the same item.
This attribute (SCD1_WID) along with corresponding SCD1_WID attribute on the fact table provides the As Is or current hierarchy view of the data. Also note that several As Is specific aggregate tables are also available in the Oracle Retail Insights Data Model and can be used for this reporting for better performance. For more information on choosing aggregate tables during an implementation, refer to the Oracle Retail Insights Implementation Guide. If aggregate tables are not chosen during implementation, As Is reporting can still be carried out but may impact the report performance.
These aggregate tables are updated or recalculated every time reclassification occurs in either product or organization hierarchy.
PIT is another reporting type available in Retail Insights. There are no set of tables or extra processing that is required to support this functionality.
The Oracle Retail Insights Data Model exposes all the hierarchies against the required fact tables to be queried for a particular point-in-time (a specific date) and utilized dimension and fact joins on SCD1_WID columns along with user specified date. The join conditions are defined in Oracle BI EE code.
As part of the type 2 attribute maintenance it is very important to understand that changes to the hierarchy are made all the way to the lowest level in the dimension hierarchy. Consider the following examples:
Lowest level entity reclassifies to new parent (for example, Item reclassified to new Subclass)
Parent entity belongs to a new grand parent (Subclass reclassified to new class)
Retail Insights programs accounts for both scenarios stated above and the second scenario specifically (Parent entity belongs to a new grand parent), requires a pushdown. When an entity at a higher level undergoes a major change, all of its descendents (held within the lower levels of the hierarchy) must undergo the change with it. For example, if the subclass is reclassified to a new class then the subclass will get a new row with a new surrogate key and all the items belonging to this specific subclass will also get a new row and new surrogate keys.
The same rule applies if the dimension attributes are tracked as type 2 and are closed/deactivated in the source system.
The remainder of this chapter illustrates the flow of dimension data from source tables to Retail Insights dimension tables.
Retail Insights dimensions are designed based on specific requirements and can belong to either of these three categories:
Type 1 Dimensions
Type 2 Dimensions
Dimensions not requiring SCD behavior
Each of these dimension types are described in detailed below along with the flow diagram.
Dimensions that require history to be maintained on specific attributes or set of attributes are called SCD type 2 dimensions. In Retail Insights data model the hierarchy changes for Product and Organization dimension are tracked as type 2 changes. For example, if the hierarchy changes for subclass to a new class, a new row is inserted for the subclass and the old row is updated with new effective end date. Similarly if a record is deactivated/deleted in the source system then the record is updated with the current flag of 'N'. For all the active rows, current flag is maintained as 'Y'.
The following steps describe the flow diagram for type 2 dimensions in detail:
Check whether To Run Package: Checks whether the package is in runnable state. If the package has already executed and is in 'Error', 'Success', or 'In Process' status, the interfaces in the package do not execute and the packages fails.
The status of the package is stored in C_LOAD_DATES table and it is required that if a program failed in the previous execution, the error status related records for that packaged need to be removed from the table before executing the interface again. In normal nightly batch, a program is scheduled for cleaning up the entries in this table before starting next batch. For additional details on this program, see Chapter 7, "Program Reference Lists".
Error details can be checked in the error and log files. For additional details on how to execute ODI programs, error files, logging, and restartability, see Chapter 7, "Program Reference Lists".
Write/Update Log file: Log files capture warnings, errors or completion status of the ODI programs at each step and can be referenced anytime during or after the program execution.
Insert Into Control Table: Creates a record for the package being executed along with the target table in C_LOAD_DATES table. This table maintains the program execution status and needs to be updated every time the program is executed.
Flag Deleted Records: If the option for flagging deleted records is selected, this step will be executed. This step executes if the interface is executed in full mode only.
All the records that do not exist in the source system and do exist in the data warehouse table are considered to be deleted from the source system when the program is executed in full mode. This step will update the existing record's CURRENT_FLG to 'N'.
Update Existing Records: This step identifies the records that are marked as 'Overwrite on change' in the ODI model and updates these records with new values.
The attributes that are not required to be tracked as type 2 as generally marked as 'Overwrite on Change' and are updated when the value of these attributes changes.
Historize Old Rows: This step identifies the records that are marked as 'Add row on change' in the ODI model. If there is a change in the values between existing and newly extracted data, the existing (old) record is closed by updating the effective end date to current business date and also setting the current flag to 'N'.
Insert Changing Dimensions: This step identifies the records that are marked as 'Add row on change' in the ODI model. If there is a change in the values between existing and newly extracted data, it inserts the newly extracted rows into the data warehouse table with start of effective date as the next day from the current business date along with current flag as 'Y'.
Insert New Dimensions: This step identifies the records that are not available in the target table using the columns marked as 'Natural Key' in ODI model. These records are considered new records and are inserted into the data warehouse table (target table) with current flag as 'Y' and effective start date as current business date.
The following steps describe the flow diagram for type 1 dimensions in detail:
Check whether To Run Package: Checks whether the package is in runnable state. If the package has already executed and is in 'Error' or 'Success' or 'In Process' status then the interface(s) in the package does not execute and packages fails.
The status of the package is stored in C_LOAD_DATES table and it is required that if program failed in the previous execution then the error status related records for that packaged need to be removed from the table before re-executing the interface. In normal nightly batch, a program is scheduled for cleaning up the entries in this table before starting next batch. For additional details on this program, see Chapter 7, "Program Reference Lists".
Error details can be checked in the error and log files. For additional details on how to execute ODI programs, error files, logging, and restartability, see Chapter 7, "Program Reference Lists".
Write/Update Log file: Log files capture warnings, errors, or completion status of the ODI programs at each step and can be referenced anytime during or after the program execution.
Insert Into Control Table: Creates a record for the package being executed along with the target table in C_LOAD_DATES table. This table maintains the program execution status and needs to be updated every time the program is executed.
Flag Deleted Records: If the option for flagging deleted records is selected then this step will be executed. This step executes when the interface is executed in full mode only.
All the records that do not exist in the source system and do exist in the data warehouse table are considered to be deleted from the source system if the program is executed in full mode. This step will update the existing record's CURRENT_FLG to 'N'.
Update Existing Records: This step identifies the records that are marked as 'Overwrite on change' in the ODI model and updates these records with new values.
The attributes that are not required to be tracked as type 2 as generally marked as 'Overwrite on Change' and are updated when the value of these attributes changes.
Insert New Dimensions: This step identifies the records that are not available in the target table using the columns marked as 'Natural Key' in ODI model. These records are considered new records and are inserted into the data warehouse table (target table) with current flag as 'Y' and effective start date as current business date.
Dimensions that are not considered as slowly changing do not involve complex calculations for maintenance, unlike dimensions with SCD behavior. These dimensions in the Retail Insights data model are updated, inserted with new data in each execution, or are re-built (by truncating old data) in each execution.
