2 Physical Model Customization

Questions to Answer Before You Customize the Physical Model

When designing the physical model, remember that the logical data model is not one-to-one with the physical data model. Consider the load, query, and maintenance requirements when you are designing the physical model customizations. For example, answer the following questions before you design the physical data model:

• Identify the scope of the changes. See "Common Change Scenarios When Customizing the Foundation Layer of Oracle Retail Data Model" for an overview discussion of making physical data model changes when your business needs do not result in a logical model that is the same as the Oracle Retail Data Model logical model.

• What is the result of the source data profile?

• What is the data load frequency for each table?

• How many large tables are there and which tables are these?

• How will the tables and columns be accessed? What are the common joins?

• What is your data backup strategy?

Conventions When Customizing the Physical Model

When developing the physical model for Oracle Retail Data Model, the following conventions were used. Continue to follow these conventions as you customize the physical model.

General Naming Conventions for Physical Objects

Follow these guidelines for naming physical objects that you define:

• When naming the physical objects follow the naming guidelines for naming objects within an Oracle Database schema. For example:

  • Table and column names must start with a letter, can use only 30 alphanumeric characters or less, cannot contain spaces or some special characters such as "!" and cannot use reserved words.

  • Table names must be unique within a schema that is shared with views and synonyms.

  • Column names must be unique within a table.

• Although it is common to use abbreviations in the physical modeling stage, as much as possible, use names for the physical objects that correspond to the names of the entities in the logical model. Use consistent abbreviations to avoid programmer and user confusion.

• When naming columns, use short names if possible. Short column names reduce the time required for SQL command parsing.

• The ordm_sys schema delivered with Oracle Retail Data Model uses the prefixes and suffixes shown in the following table to identify object types.

  Prefix or Suffix	Used for Name of These Objects
  CB$	Materialized view used to support/deliver required functionality for an Oracle OLAP cube. This is an internal object built and maintained automatically by the Oracle OLAP server in the database. Note: Do not report or query against this object. Instead access the corresponding _VIEW object.
  DMV_	Materialized view created for performance reasons (that is, not an aggregate table or an OLAP cube).
  DWA_	Aggregate tables or relational materialized views (aggregate objects)
  DWB_	Base transaction data (3NF) tables.
  DWC_	Control tables.
  DWD_	Derived tables -- including data mining result tables.
  DWL_	Lookup tables.
  DWV_	Relational view of time dimension
  DWR_	Reference data tables.
  _VIEW	A relational view of an OLAP cube, dimension, or hierarchy.

  
  

  Note:

  You should use a similar prefix and suffix, combined with an indicator for your company or project name for any new tables, views, and cubes that you define during customization. For example, if your customization project chooses a standard prefix of 'AZ', then new base tables would be created with the prefix 'AZB_', new reference tables would use the prefix 'AZR_'.

  See:

  Oracle Retail Data Model Reference for detailed information about the objects in the default Oracle Retail Data Model.

Common Change Scenarios When Customizing the Foundation Layer of Oracle Retail Data Model

There are several common change scenarios when customizing the foundation layer of the physical data model:

• Additions to Existing Structures

  If you identify business areas or processes that are not supported in the default foundation layer of the physical data model of Oracle Retail Data Model, add new tables and columns.

  Carefully study the default foundation layer of the physical data model of Oracle Retail Data Model (and the underlying logical data model) to avoid building redundant structures when making additions. If these additions add high value to your business value, communicate the additions back to the Oracle Retail Data Model Development Team for possible inclusion in future releases of Oracle Retail Data Model.

• Deletions of Existing Structures

  If there are areas of the model that cannot be matched to any of the business requirements of your legacy systems, it is safer to keep these structures and not populate that part of the warehouse.

  Deleting a table in the foundation layer of the physical data model can destroy relationships needed in other parts of the model or by applications based on the it. Some tables may not be needed during the initial implementation, but you may want to use these structures at a later time. If this is a possibility, keeping the structures now saves re-work later. If tables are deleted, perform a thorough analysis to identify all relationships originating from that entity.

• Changes to Existing Structures

  In some situations some structures in the foundation layer of the physical data model of Oracle Retail Data Model may not exactly match the corresponding structures that you use. Before implementing changes, identify the impact that the changes would have on the database design of Oracle Retail Data Model. Also identify the impact on any applications based on the new design.

Example of Changing the Foundation Layer of the Oracle Retail Data Model

As an example, let's examine how Oracle Retail Data Model supports the various retail services, what you might discover during fit-gap analysis, and how you might extend Oracle Retail Data Model to fit the discovered gaps.

Entities supporting Retail services

The entities provided with the logical model of Oracle Retail Data Model that support the retail services are:

• Customer: An individual or organization that purchases, may purchase, or did purchase goods and or services from a store.

• Customer Order: The entity captures information about an order placed by a customer for merchandise or services to be provided at some future date and time.

• SKU Item: Stock Keeping Unit or unit identification, typically the UPC, used to track store inventory and sales. Each SKU is associated with an item, variant, product line, bundle, service, fee, or attachment. This is the lowest level of merchandise for which inventory and sales records are retained within the retail store.

• Item Category: Category within a subclass in the product hierarchy, as it was at a given point in time.

The differences discovered during fit-gap analysis

Assume that during the fit-gap analysis, you discover the following need that is not supported by the logical model delivered with Oracle Retail Data Model:

Your company wants to add a new section in the retail domain. For example, if you want to add a book section in your store to other existing departments.

Extending the physical model to support the differences

For example, to extend the physical data model, do the following:

1. Create a table named DWR_AUTHOR to hold the Author's information by executing the following statements:

   CREATE TABLE DWR_AUTHOR
   (
   AUTHOR _ID INTEGER NOT NULL,
   AUTHOR _FIRST_NAME VARCHAR2 (50) NOT NULL,
   AUTHOR _LAST_NAME VARCHAR2 (50) 
   );
   ALTER TABLE DWR_AUTHOR ADD CONSTRAINT AUTHOR_PK PRIMARY KEY (AUTHOR _ID);
   

2. Add columns in the DWR_SKU_ITEM table using the following statement:

   ALTER TABLE DWR_SKU_ITEM ADD COLUMN ISBN INTEGER NULL
   

3. Create another new table named DWR_AUTHOR_ITEM_ASGN:

   CREATE TABLE DWR_AUTHOR_ITEM_ASGN
   (
   AUTHOR _ID INTEGER NOT NULL,
   SKU_ITEM_KEY NUMBER (30) NOT NULL
   );
   

Tablespaces in Oracle Retail Data Model

A tablespace consists of one or more data files, which are physical structures within the operating system you are using.

Recommendations: Defining Tablespaces

If possible, define tablespaces so that they represent logical business units.

Use ultra large data files for a significant improvement in very large Oracle Retail Data Model warehouse.

Changing the Tablespace and Partitions Used by Tables

You can change the tablespace and partitions used by Oracle Retail Data Model tables. What you do depends on whether the Oracle Retail Data Model table has partitions:

• For tables that do not have partitions (that is, lookup tables and reference tables), you can change the existing tablespace for a table.

  By default, Oracle Retail Data Model defines the partitioned tables as interval partitioning, which means the partitions are created only when new data arrives.

  Consequently, for Oracle Retail Data Model tables that have partitions (that is, Base, Derived, and Aggregate tables), for the new interval partitions to be generated in new tablespaces rather than current ones, issue the following statements.

  ALTER TABLE table_name MODIFY DEFAULT ATTRIBUTES 
  TABLESPACE new_tablespace_name;
   
  

  When new data is inserted in the table specified by table_name, a new partition is automatically created in the tablespace specified as new_tablespace_name.

• For tables that have partitions (that is, base, derived, and aggregate tables), you can specify that new interval partitions be generated into new tablespaces.

  For Oracle Retail Data Model tables that do not have partitions, that is lookup tables and reference tables, change the existing tablespace for a table with the following statement.

  ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;
  

Data Compression in Oracle Retail Data Model

A key decision that you must make is whether to compress your data. Using table compression reduces disk and memory usage, often resulting in better scale-up performance for read-only operations. Table compression can also speed up query execution by minimizing the number of round trips required to retrieve data from the disks. Compressing data however imposes a performance penalty on the load speed of the data.

Recommendations: Data Compression

In general, choose to compress the data. The overall performance gain typically outweighs the cost of compression.

If you decide to use compression, consider sorting your data before loading it to achieve the best possible compression rate. The easiest way to sort incoming data is to load it using an ORDER BY clause on either your CTAS or IAS statement. Specify an ORDER BY a NOT NULL column (ideally non numeric) that has a large number of distinct values (1,000 to 10,000).

alter table <tablename> move compress;

Surrogate Keys in the Physical Model

The surrogate key method for primary key construction involves taking the natural key components from the source systems and mapping them through a process of assigning a unique key value to each unique combination of natural key components (including source system identifier). The resulting primary key value is completely non-intelligent and is typically a numeric data type for maximum performance and storage efficiency.

Advantages of Surrogate keys include:

• Ensure uniqueness: data distribution

• Independent of source systems

• Re-numbering

• Overlapping ranges

• Uses the numeric data type which is the most performant data type for primary keys and joins

Disadvantages of Surrogate keys:

• Requires allocation during ETL

• Complex and expensive re-processing and data quality correction

• Not used in queries – performance impact

• The operational business intelligence requires natural keys to join or trace back to source operational systems

Integrity Constraints in Oracle Retail Data Model

Integrity constraints are used to enforce business rules associated with your database and to prevent having invalid information in the tables. The most common types of constraints include:

• PRIMARY KEY constraints, this is usually defined on the surrogate key column to ensure uniqueness of the record identifiers. In general, it is recommended that you specify the ENFORCED ENABLED RELY mode.

• UNIQUE constraints, to ensure that a given column (or set of columns) is unique. For slowly changing dimensions, it is recommended that you add a unique constraint on the Business Key and the Effective From Date columns to allow tracking multiple versions (based on surrogate key) of the same Business Key record.

• NOT NULL constraints, to ensure that no null values are allowed. For query rewrite scenarios, it is recommended that you have an inline explicit NOT NULL constraint on the primary key column in addition to the primary key constraint.

• FOREIGN KEY constraints, to ensure that relation between tables are being honored by the data. Usually in data warehousing environments, the foreign key constraint is present in RELY DISABLE NOVALIDATE mode.

The Oracle Database uses constraints when optimizing SQL queries. Although constraints can be useful in many aspects of query optimization, constraints are particularly important for query rewrite of materialized views. Under some specific circumstances, constraints need space in the database. These constraints are in the form of the underlying unique index.

Unlike data in many relational database environments, data in a data warehouse is typically added or modified under controlled circumstances during the extraction, transformation, and loading (ETL) process.

Indexes and Partitioned Indexes in Oracle Retail Data Model

Indexes are optional structures associated with tables or clusters. In addition to the classical B-tree indexes, bitmap indexes are very common in data warehousing environments

• Bitmap indexes are optimized index structures for set-oriented operations. Additionally, they are necessary for some optimized data access methods such as star transformations. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.

• B-tree indexes are most effective for high-cardinality data: that is, for data with many possible values, such as customer_name or phone_number. However, fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of disk space because the indexes can be several times larger than the data in the table. B-tree indexes can be stored specifically in a compressed manner to enable huge space savings, storing more keys in each index block, which also leads to less I/O and better performance.

Recommendations: Indexes and Partitioned Indexes

Make the majority of the indexes in your customized Oracle Retail Data Model bitmap indexes.

Use B-tree indexes only for unique columns or other columns with very high cardinalities (that is, columns that are almost unique). Store the B-tree indexes in a compressed manner.

Partition the indexes. Indexes are just like tables in that you can partition them, although the partitioning strategy is not dependent upon the table structure. Partitioning indexes makes it easier to manage the data warehouse during refresh and improves query performance.

Typically, specify the index on a partitioned table as local. Bitmap indexes on partitioned tables must always be local. B-tree indexes on partitioned tables can be global or local. However, in a data warehouse environment, local indexes are more common than global indexes. Use global indexes only when there is a specific requirement which cannot be met by local indexes (for example, a unique index on a nonpartitioning key, or a performance requirement).

Partitioned Tables in Oracle Retail Data Model

Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces. Each piece of the database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics. From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives the administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a nonpartitioned table. No modifications are necessary when accessing a partitioned table using SQL DML commands.

As discussed in the following topics, partitioning can provide tremendous benefits to a wide variety of applications by improving manageability, availability, and performance:

• Partitioning the Oracle Retail Data Model for Manageability

• Partitioning the Oracle Retail Data Model for Easier Data Access

• Partitioning the Oracle Retail Data Model for Join Performance

Partitioning the Oracle Retail Data Model for Join Performance

Sub-partitioning by hash is used predominately for performance reasons. Oracle Database uses a linear hashing algorithm to create sub-partitions.

A major performance benefit of hash partitioning is partition-wise joins. Partition-wise joins reduce query response time by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel. This significantly reduces response time and improves both CPU and memory resource usage. In a clustered data warehouse, this significantly reduces response times by limiting the data traffic over the interconnect (IPC), which is the key to achieving good scalability for massive join operations. Partition-wise joins can be full or partial, depending on the partitioning scheme of the tables to be joined.

As illustrated in Figure 2-2, a full partition-wise join divides a join between two large tables into multiple smaller joins. Each smaller join, performs a joins on a pair of partitions, one for each of the tables being joined. For the optimizer to choose the full partition-wise join method, both tables must be equi-partitioned on their join keys. That is, they have to be partitioned on the same column with the same partitioning method. Parallel execution of a full partition-wise join is similar to its serial execution, except that instead of joining one partition pair at a time, multiple partition pairs are joined in parallel by multiple parallel query servers. The number of partitions joined in parallel is determined by the Degree of Parallelism (DOP).

Figure 2-2 Partitioning for Join Performance

Description of "Figure 2-2 Partitioning for Join Performance"

Recommendations: Number of Hash Partitions

To ensure that the data gets evenly distributed among the hash partitions it is highly recommended that the number of hash partitions is a power of 2 (for example, 2, 4, 8, and so on). A good rule of thumb to follow when deciding the number of hash partitions a table should have is 2 X # of CPUs rounded to up to the nearest power of 2.

If your system has 12 CPUs, then 32 would be a good number of hash partitions. On a clustered system the same rules apply. If you have 3 nodes each with 4 CPUs, then 32 would still be a good number of hash partitions. However, ensure that each hash partition is at least 16MB. Many small partitions do not have efficient scan rates with parallel query. Consequently, if using the number of CPUs makes the size of the hash partitions too small, use the number of Oracle RAC nodes in the environment (rounded to the nearest power of 2) instead.

Parallel Execution in Oracle Retail Data Model

Parallel Execution enables a database task to be parallelized or divided into smaller units of work, thus allowing multiple processes to work concurrently. By using parallelism, a terabyte of data can be scanned and processed in minutes or less, not hours or days.

Figure 2-3 illustrates the parallel execution of a full partition-wise join between two tables, Sales and Customers. Both tables have the same degree of parallelism and the same number of partitions. They are range partitioned on a date field and sub partitioned by hash on the cust_id field. As illustrated in the picture, each partition pair is read from the database and joined directly.

There is no data redistribution necessary, thus minimizing IPC communication, especially across nodes. Figure 2-3 shows the execution plan you would see for this join.

Figure 2-3 Parallel Execution of a Full Partition-Wise Join Between Two Tables

Description of "Figure 2-3 Parallel Execution of a Full Partition-Wise Join Between Two Tables"

To ensure that you get optimal performance when executing a partition-wise join in parallel, use the degree of parallelism as a multiple of the number of partitions. The number of partitions should be multiple of the number of cores. To get best performance use degree of parallelism that is the same as the number of partitions to be processed in a query (this should be equal to number of CPU cores).

What happens if only one table that you are joining is partitioned? In this case the optimizer could pick a partial partition-wise join. Unlike full partition-wise joins, partial partition-wise joins can be applied if only one table is partitioned on the join key. Hence, partial partition-wise joins are more common than full partition-wise joins. To execute a partial partition-wise join, Oracle Database dynamically repartitions the other table based on the partitioning strategy of the partitioned table.

After the other table is repartitioned, the execution is similar to a full partition-wise join. The redistribution operation involves exchanging rows between parallel execution servers. This operation leads to interconnect traffic in Oracle RAC environments, since data must be repartitioned across node boundaries.

Figure 2-4 illustrates a partial partition-wise join. It uses the same example as in Figure 2-3, except that the customer table is not partitioned. Before the join operation is executed, the rows from the customers table are dynamically redistributed on the join key.

Figure 2-4 Partial Partition-Wise Join

Description of "Figure 2-4 Partial Partition-Wise Join"

alter session enable parallel query; 

alter session enable parallel dml;

alter table <table_name> parallel 32;