| Oracle® Retail Analytics Implementation Guide Release 14.1 E58117-01 |
|
 Previous |
 Next |
Retail Analytics is a high performance data warehouse, capable of moving and storing massive amounts of data, and providing efficient access to that data via the delivered and custom built reports. For any BI solution, including Retail Analytics, smart decisions on how to implement and run your data warehouse will ensure that you are getting the most out of it. This chapter contains information that will help you get the best performance out of Retail Analytics and identifies common contributors that can weaken performance, as well as best practices that will ensure Retail Analytics is running in the most optimal manner.All implementations are unique and the factors that are beneficial for one implementation may not have the same effect for all the implementations. It is a good practice to test several settings/approaches for the factors and recommendations listed below and use the ones that work best for your environment. The factors listed in this chapter are the key factors that impact performance but no absolute values or settings can be provided for implementation purposes due to the uniqueness of each environment.Oracle Retail Analytics includes ODI for extract, transform and load and Oracle Business Intelligence (BI EE) for analytic reporting purposes. The recommendations in this chapter will focus on both back end (ETL) and front end (Oracle BI EE) components of Retail Analytics.
Based on the complexity of the report, Oracle BI EE sometimes generates complex SQL, causing the Oracle Database to pick a less than optimized execution plan. In order to avoid this scenario, it is recommended that the "SQL Plan Baseline" functionality of the Oracle 11gR2 be enabled (it is disabled by default). For more details refer to the Oracle 11gR2 Performance Tuning Guide.
With an increased use of the Retail Analytics application, the data volumes will grow and may result in slower performance. The performance impact can be on Retail Analytics batch that loads data to data warehouse tables, Retail Analytics reports, and storage.Adoption of purging and archiving strategy help in reducing data volumes, resulting in better performance. Consider the following recommendations while implementing these strategies in a data warehouse:
Design your archiving and purging strategy as early as possible in the Retail Analytics implementation. This helps in designing the most optimal table partitioning for large tables.
Ensure that the data is deleted in the most optimal manner. SQL delete statements may not be the most efficient way of removing unnecessary data from Retail Analytics tables. Consult with your database administrator to discuss purging and archiving techniques.
Purging and archiving of tables must be carefully designed as it requires a good understanding of analytic reports required by business users or regulatory requirements that require companies to retain certain data for a required duration. For example, in certain cases, aggregated data may be kept longer as compared to the base level fact data because the users are interested in summary level reports as compared to detailed (base level) reports for data older than two years.
Automation of the archiving and purging processes ensures that a consistent approach is being followed in maintaining tables with large data volumes and provides consistent report performance to the users.
While designing purging programs, make sure that dimensional data is not deleted for which fact data is available or will be available.
An important consideration during purging is to make sure that Retail Analytics seed data (where applicable) is not deleted accidentally.
Retail Analytics, by default, provides several aggregate tables. For the complete list, see "Aggregates List". These pre-built aggregate tables are selected based on the following:
General usage patterns of the data
Reporting needs (As-Is or As-Was or both)
General aggregation ratio
The ratio between data in the base fact table versus data in the potential aggregate table should be considered while deciding whether the fact table should be aggregated or not. A ratio of 1:5 through 1:10 is a good starting point, a ratio of 1:10 through 1:20 is good to aggregate, and a ratio of 1 to more than 20 must be aggregated.
During implementation or before, it is expected for the retailer to identify these scenarios and select the appropriate aggregate tables for best performance and usability. All the aggregate tables which are pre-packaged will have the ODI and Oracle BI EE mappings. It is highly recommended not to use Retail Analytics with all the available aggregates.
Using all these aggregations improves report performance but the improved report performance should be weighed against reduced ETL batch performance and increased storage requirement.
The reason for providing these aggregates is to give flexibility for the customer to pick appropriate levels and doesn't have to invest in customizing the product.
Below are the different groupings of aggregations. See "Aggregates List" for additional details.
As-Was aggregates
As-Is aggregates
As-Was Corporate aggregates
As-Is Corporate aggregates
Season aggregates
Even though there are different flavors of aggregations based on As-Is and As-Was, there will be few aggregate tables which will be commonly used for both As-Is and As-Was. That is because, As-Is and As-Was differentiation is only across Product and Organization Hierarchy. If the aggregation for a fact table is based on Time dimension or any dimension other than Product and Organization, then that aggregate table can be used for both As-Is and As-Was. For example, since the
W_RTL_SLS_IT_LC_WK_A table is at Item and Location, it can used for either As-Was, As-Is, or both.
When the aggregations happen on any level of the Product or Organization hierarchy, there will be separate aggregates for As-Is and As-Was. For example, the W_RTL_SLS_SC_LC_DY_A and W_RTL_SLS_SC_LC_DY_CUR_A aggregates are on the subclass level of product dimension. The W_RTL_SLS_SC_LC_DY_A aggregate is for As-Was and the W_RTL_SLS_SC_LC_DY_CUR_A aggregate is for As-Is. All the As-Is aggregates are suffixed by 'CUR_A', which means 'Current.'
|
Note: With the exception of a Corporate aggregate in Sales, Retail Analytics out of the box does not have any aggregates across the Organization Dimension. |
When the aggregation is only on a level from Product or Organization dimension then those are referred as Corporate Aggregates. These kinds of aggregates are very useful when reporting is done on any level of Product and Calendar hierarchy or Organization and Calendar hierarchy. For the list of this type of aggregates for every fact area see "Aggregates List". Corporate aggregates are also classified into As-Is and As-Was because they need to be processed separately to capture the current as opposed to historical parent information.
Season aggregates are useful to do reporting specific to Season dimension. All the aggregates on Season can be used for both As-Is and As-Was.
For each group of aggregations, as mentioned above, there is a mandatory aggregate table that needs to be used for other selections of the aggregates and that can be identified in the FlexAggregates document with the highlighted text.
For example, if the business only needs As-Is, the following points need to be considered:
Get the general usage patterns of the data and aggregation ratio. Based on that, select the list of aggregate tables. This may not be accurate for the first time but can always be changed over a period of time based on the usage and the changing data.
Ensure that you disable/freeze all the As-Was aggregate jobs and some of the As-Is aggregates which were not selected, in ODI and disable the same in Oracle BI EE as well. See the Oracle Retail Analytics Operations Guide for more information.
This section only covers As-Is and As-Was aggregates, but Retail Analytics also offers PIT (Point in Time) reporting, which does not require any special processing of data. There are no special tables or ODI jobs for PIT. In Oracle BI EE there is a separate subject area for PIT reporting. For additional information on PIT see the Oracle Retail Analytics User Guide. PIT reporting is always done from the base fact tables or the corporate aggregate tables. If PIT is required along with As-Is, As-Was, or both then choose the corporate aggregates so that all the three reporting scenarios will be benefited.
Retail Analytics base fact extract and load programs can be configured to run using multiple threads. The default number of threads for these programs is set to one and can be configured based on requirements. For additional information on how multi-threading works, see the Program Overview chapter of the Oracle Retail Analytics Operations Guide.
Finalize the multi-threading strategy for the base fact extract and load programs.
Number of threads for each program may vary based on the data volume that program handles and resource availability. Different thread numbers should be tested by clients during implementation to achieve optimal results from multi-threading.
In the C_ODI_PARAM table, update the value of the PARAM_VALUE column to the desired number of threads. This applies to all records with the value 'LOC_NUM_OF_THREAD' in the PARAM_NAME column and the name of the program that requires multi-threading set in the SCENARIO_NAME column. See an example below for scenario named SDE_Test, where the desired number of threads needs to be set to 2 from 1 (default).
UPDATE C_ODI_PARAMSET PARAM_VALUE = 2WHERE PARAM_NAME = 'LOC_NUM_OF_THREAD'AND SCENARIO_NAME = 'SDE_Test'
If the number of thread required is more than 10, you need to modify the DDL for intermediate temp tables used by the ODI scenario. DDL changes require adding extra partitions to hold the data. The number of partitions on the intermediate temp table must be the same or higher than the required number of threads (which is the value for LOC_NUM_OF_THREADS set in the previous step).
ODI must be configured prior to implementing Retail Analytics. See the Oracle Retail Analytics Installation Guide for details on configuring ODI.
Set up the proper dependencies between the applications to ensure resources are fully utilized, which helps the nightly batch finish earlier.
Retail Analytics load programs (SIL programs) must not wait for all the extraction programs (sde) to finish before starting. Some of them can start executing as soon as the corresponding staging table is populated. For more information on setting up dependencies, refer to the Oracle Retail Analytics Operations Guide.
Allocate resources to the most important batch jobs (ones that populate the tables) that support your critical reports (the reports you need first thing in the morning). You can assign job priority in most batch scheduling tools.
Ensure that your source applications batch is optimized. Retail Analytics runs towards the end of the nightly batch. Retail Analytics jobs are often the last jobs to start due to the dependencies on the source system jobs, so Retail Analytics is often the last to finish. Optimizing the source applications batch helps Retail Analytics jobs start earlier.
Sort the W_RTL_INV_IT_LC_G table data after the data is seeded for the first time to improve ETL performance.
In a production environment, fact tables with large data volume can be created with the No Logging option. This improves the ETL performance and can be implemented on a case by case basis.
Report design can affect the performance of a report. While creating custom reports, refer to the following guidelines:
Report developers should be trained in Oracle BI to learn how to design reports in the most optimal manner.
Design reports at the highest level possible and allow drill down to more detailed levels when required.
Design reports in a manner that multiple users can utilize a single report output rather than multiple users running the same report. A best practice is to run one report and distribute that report to multiple users. For more information on how to distribute reports, refer to the Delivering Content chapter of the Oracle Business Intelligence Enterprise Edition User Guide and the Configuring Oracle BI Scheduler chapter of the Oracle Business Intelligence System Administrator's Guide.
Do not design reports to request data at a level lower than the minimum level that a metric can be reported. In addition, drilling must not be performed at these levels. This ensures that reports do not produce misleading or invalid results. For example, reports must not be designed to request planning data at the item level because planning data is only available at the subclass level and above.
As-Is reporting for all the positional facts such as inventory, cost and so on is only possible at the corporate level aggregates.
Evaluate and purge reports periodically to eliminate any outdated or duplicate reports.
Design reports to use the least amount of fact areas necessary. This reduces the number of fact table joins and in turn reduces the risk of poor report performance. For example, a best practice is not to design a single report with all sales, inventory, pricing and cost metrics, as this report will perform poorly due to joins on big fact tables. In this type of scenario, try creating separate reports with one or two fact areas on the report at a time and combining the results after these reports have run successfully.
Design reports with the least number of metrics necessary.
Schedule reports according to priority. This ensures that critical reports are available when needed. For more information on how to schedule reports, refer to the Configuring Oracle BI Scheduler chapter of the Oracle Business Intelligence System Administrator's Guide.
Decision support queries sometimes require retrieval of large amounts of data. The Oracle BI server can save the results of a query in cache files and then reuse those results later when a similar query is requested. Using the middle-tier cache permits a query to be run one time for multiple runnings of a query and not necessarily every time the query is run. The query cache allows the Oracle BI Server to satisfy many subsequent query requests without having to access back-end data sources (such as Oracle database). This reduction in communication costs can dramatically decrease query response time.
To summarize, query caching has the following advantages only when the same report is run repeatedly:
Improvement of query performance
Less network traffic
Reduction in database processing and charge back
Reduction in Oracle BI server processing overhead
For more details on Caching refer to the Managing Performance Tuning and Query Caching chapter in the Oracle BI EE System Administrator's Guide.
Database level table partitioning is very important for ETL batch and report performance. For more information, see Chapter 4, "Compression and Partitioning".
Retail Analytics is built on Oracle Database 11g Release 2 and must be optimized and configured for a retailers' needs. Refer to the Setting up your Data Warehouse System chapter of the Oracle 11g Data Warehouse Guide.
ETL program and report performance are highly dependent on the hardware resources. For more information, see Chapter 2, "Setup and Configuration".
Changes and modifications to the Retail Analytics delivered code or development of new code is considered customization. Retail Analytics does not support custom code developed by clients unless the issue related to customization can be recreated using Retail Analytics delivered objects. Listed below are recommendations that will help you in maintaining Retail Analytics code:
Naming convention: it is recommended that you use a good and consistent naming convention when customizing Retail Analytics delivered code or building new code in the Retail Analytics environment.
This strategy is helpful in identifying custom code and also helps when merging a retailer's Retail Analytics repository with future releases of the Retail Analytics repository. There is a possibility of losing customizations to Retail Analytics provided ODI scripts or Oracle BI EE repository, if the customized code uses the same object/script names that are used by Retail Analytics.
As a best practice, keep all the documentation up-to-date for capturing any changes or new code that has been developed at a site. For example, if table structure has been customized, create or update the custom Data Model Guide with these changes.
While customizing the rpd, do not make any changes directly on the main shipped/original rpd. Make a copy of the original rpd and start the changes on the copied rpd which will be the modified version. This is useful while applying any patches in future releases of Retail Analytics through Oracle BI EE's merge utility. For more details refer to the Managing Oracle BI Repository Files chapter of the Oracle BI EE Metadata Repository Builder's Guide.
For customizations to existing ODI code or while creating new ODI code, refer to the ODI Best Practices Guide included with your product code.
Create aliases for the objects created in the physical layer for usability purposes.
Do not design the business layer model as a snow-flake model.
Any level key on ident's must be set to non-drillable.
In the presentation layer, fact folders (presentation tables) must contain only metrics and dimension folders (presentation tables) must contain only attributes.
For a development environment, it is recommended to use a multi-user environment. For more information on setting up a multi-user environment, refer to the Completing Setup and Managing Oracle BI Repository Files chapter of the Oracle Business Intelligence Server Administration Guide.
The following best practices are recommended for Retail Analytics:
Loading batch (sil) execution catch-up can be achieved by backing up the staging table data. The following scenarios explain when users can benefit from this. This approach is considered a customization on Retail Analytics programs and is not supported.
Catch-up: When Retail Analytics is not ready for implementation, users can use history data stored in the staging backup tables (explained later in this section) to catch-up on the data loading once the system is implemented or becomes available.
Retail Analytics database systems are down: When Retail Analytics database systems are down, users can use history data stored in the staging backup tables (explained later in this section) to load the data once the system becomes available.
The following steps illustrate/show how the Loading Batch Execution catch-up solution works:
Create Retail Analytics staging tables for each corresponding staging table using the DDL for the staging tables provided. For more information, see the Oracle Retail Analytics Installation Guide.
Set up the Retail Analytics ODI Extract (SDE) programs, so they are ready to be executed against source applications and load into the staging tables created in previous setup.
Create a one-to-one backup table for each staging table. This backup table uses the same DDL as the staging table along with an additional field (load_date) which can be mapped to source system business date. This date is used as a filter when the backup data is ready to be moved to the staging table.
Execute the SDE program to populate the staging tables created in the first step.
Move staging table data into backup staging table with the correct business date. By default, Retail Analytics does not provide the backup table DDL or backup data population scripts.
Repeat the process of executing the SDE program and taking the backup to the staging backup table periodically (daily, once in two days, weekly, and so on), until the Retail Analytics systems are available. Note that the staging table only contains the current business day's data, while the backup staging table contains data for all the business dates when the program was executed.
Once the Retail Analytics systems become available, move the backup staging table data to the staging tables, one day at a time. This can be done by using 'load_date' as a filter on the source backup staging table data.
Once one business date data is moved to the staging table, SIL programs and corresponding PLP programs need to be executed for loading data into final data warehouse tables.
Repeat the process of moving data from backup staging to staging and executing SIL and PLP programs until all the data for all business dates from backup staging tables is loaded into the fact and dimension tables.
Depending on your specific requirements and for facilitating performance improvement, a reporting mirror (exact copy of existing data warehouse) can be created. With this approach, one database can be used for ETL processes and the second database instance can be used by users for running their reports. There are several ways (database level solutions, operating system level solutions and hardware level solutions) of creating a database mirror. Consult with your IT resources or database administrator for evaluating available options.If this approach is adopted, you must run your queries from the reporting mirror area, not from core data warehouse area. Take the following into consideration:
Consider this approach for large data warehouse implementations.
Creating data marts can be a good option when implementing mirroring.
Build a user notification mechanism should be built to notify users after the data has been refreshed on the mirror.
The table below lists Retail Analytics aggregates grouped by subject area and aggregation type.
Table 5-1 Retail Analytics Aggregates
| Fact | Base Fact Table | Aggregate Tables As-Was | Aggregate Tables As-Is | Corporate Aggregate Tables (As-Was) | Corporate Aggregate Tables (As-Is) | Season Aggregates (As-Is/As-Was) |
|---|---|---|---|---|---|---|
|
Inventory Position |
W_RTL_INV_IT_LC_DY_F |
W_RTL_INV_IT_LC_WK_A, |
W_RTL_INV_IT_DY_A, |
W_RTL_INV_IT_DY_A, |
||
|
Inventory Receipts |
W_RTL_INVRC_IT_LC_DY_F |
W_RTL_INVRC_IT_LC_WK_A, |
W_RTL_INVRC_SC_LC_DY_CUR_A, W_RTL_INVRC_SC_LC_WK_CUR_A |
W_RTL_INVRC_IT_DY_A, |
W_RTL_INVRC_IT_DY_A, |
|
|
Markdown |
W_RTL_MKDN_IT_LC_DY_F |
W_RTL_MKDN_IT_LC_WK_A, |
W_RTL_MKDN_SC_LC_DY_CUR_A, W_RTL_MKDN_SC_LC_WK_CUR_A, W_RTL_MKDN_CL_LC_DY_CURR_A, W_RTL_MKDN_CL_LC_WK_CURR_A, W_RTL_MKDN_DP_LC_DY_CURR_A, W_RTL_MKDN_DP_LC_WK_CURR_A |
W_RTL_MKDN_IT_DY_A, |
W_RTL_MKDN_IT_DY_A, |
W_RTL_MKDN_IT_LC_DY_SN_A, W_RTL_MKDN_IT_LC_WK_SN_A, W_RTL_MKDN_IT_DY_SN_A, W_RTL_MKDN_IT_WK_SN_A |
|
Net Cost |
W_RTL_NCOST_IT_LC_DY_F |
W_RTL_NCOST_IT_DY_A |
W_RTL_NCOST_IT_DY_A |
|||
|
Net Profit |
W_RTL_NPROF_IT_LC_DY_F |
W_RTL_NPROF_IT_LC_WK_A, |
W_RTL_NPROF_IT_DY_A, |
W_RTL_NPROF_IT_DY_A, |
||
|
Planning |
W_RTL_MFPCPC_SC_CH_WK_F, W_RTL_MFPOPC_SC_CH_WK_F, W_RTL_MFPCPR_SC_CH_WK_F, W_RTL_MFPOPR_SC_CH_WK_F |
|||||
|
Pricing |
W_RTL_PRICE_IT_LC_DY_F |
W_RTL_PRICE_IT_DY_A |
W_RTL_PRICE_IT_DY_A |
|||
|
Sales Transaction |
W_RTL_SLS_TRX_IT_LC_DY_F |
W_RTL_SLS_IT_LC_DY_A, |
W_RTL_SLS_IT_LC_DY_A, W_RTL_SLS_IT_LC_WK_A, W_RTL_SLS_SC_LC_DY_CUR_A, W_RTL_SLS_SC_LC_WK_CUR_A, W_RTL_SLS_CL_LC_DY_CUR_A, W_RTL_SLS_CL_LC_WK_CUR_A, W_RTL_SLS_DP_LC_DY_CUR_A, W_RTL_SLS_DP_LC_WK_CUR_A |
W_RTL_SLS_IT_DY_A, |
W_RTL_SLS_IT_DY_A, |
W_RTL_SLS_IT_LC_DY_SN_A, W_RTL_SLS_IT_LC_WK_SN_A, W_RTL_SLS_IT_DY_SN_A, W_RTL_SLS_IT_WK_SN_A |
|
Sales Forecast |
W_RTL_SLSFC_IT_LC_DY_F, W_RTL_SLSFC_IT_LC_WK_F |
W_RTL_SLSFC_SC_LC_DY_A, |
W_RTL_SLSFC_SC_LC_DY_CUR_A, W_RTL_SLSFC_SC_LC_WK_CUR_A |
W_RTL_SLSFC_IT_DY_A, |
W_RTL_SLSFC_IT_DY_A, |
W_RTL_SLSFC_IT_LC_DY_SN_A, W_RTL_SLSFC_IT_LC_WK_SN_A, W_RTL_SLSFC_IT_DY_SN_A, W_RTL_SLSFC_IT_WK_SN_A |
|
Sales Pack |
W_RTL_SLSPK_IT_LC_DY_F |
W_RTL_SLSPK_IT_LC_WK_A |
W_RTL_SLSPK_IT_DY_A, |
W_RTL_SLSPK_IT_DY_A, |
W_RTL_SLSPK_IT_LC_DY_SN_A, W_RTL_SLSPK_IT_LC_WK_SN_A, W_RTL_SLSPK_IT_DY_SN_A, W_RTL_SLSPK_IT_WK_SN_A |
|
|
Sales Promotion |
W_RTL_SLSPR_TRX_IT_LC_DY_F |
W_RTL_SLSPR_PC_CS_IT_LC_DY_AW_RTL_SLSPR_PC_CUST_LC_DY_AW_RTL_SLSPR_PC_HH_WK_AW_RTL_SLSPR_PC_IT_LC_DY_AW_RTL_SLSPR_PE_CS_IT_LC_DY_AW_RTL_SLSPR_PE_CUST_LC_DY_AW_RTL_SLSPR_PE_IT_LC_DY_AW_RTL_SLSPR_PP_CS_IT_LC_DY_AW_RTL_SLSPR_PP_CUST_LC_DY_AW_RTL_SLSPR_PP_IT_LC_DY_A |
||||
|
Stock Ledger |
W_RTL_STCK_LDGR_SC_LC_WK_F, W_RTL_STCK_LDGR_SC_LC_MH_F |
|||||
|
Supplier Compliance |
W_RTL_SUPPCM_IT_LC_DY_F, W_RTL_SUPPCMUF_LC_DY_F |
W_RTL_SUPPCM_IT_LC_WK_A, W_RTL_SUPPCMUF_LC_WK_A |
W_RTL_SUPPCM_IT_LC_WK_A, W_RTL_SUPPCMUF_LC_WK_A |
W_RTL_SUPPCM_LC_WK_A |
W_RTL_SUPPCM_LC_WK_A |
|
|
Supplier Invoice |
W_RTL_SUPP_IVC_PO_IT_F |
|||||
|
Unit Cost |
W_RTL_BCOST_IT_LC_DY_F |
W_RTL_BCOST_IT_DY_A |
W_RTL_BCOST_IT_DY_A |
|||
|
Wholesale Franchise |
W_RTL_SLSWF_IT_LC_DY_F |
W_RTL_SLSWF_IT_LC_WK_A, |
W_RTL_SLSWF_IT_LC_WK_A, W_RTL_SLSWF_SC_LC_DY_CUR_A, W_RTL_SLSWF_SC_LC_WK_CUR_A |
W_RTL_SLSWF_IT_DY_A, |
W_RTL_SLSWF_IT_DY_A, |
