In Strategy, a warehouse can be partitioned along any number of dimensions. Data partitioning is a technique used to optimize query performance and is defined as the splitting of one base fact table into several partitioned base tables built on a definable data grouping.
Partitioning of large tables into multiple smaller tables provides the following advantages:
- The query response time is improved
- The time required to load into tables is decreased
The disadvantage of partitioning is:
- The increased maintenance
In choosing a partitioning strategy, the overall goal is to gain the most benefits in terms of query performance, database management and batch processing. To improve query performance, the project architect must minimize the number of tables and records (within a table) that must be read to satisfy the majority of queries issued against the warehouse.
Partitioning strategies differ significantly based on the data model, the nature of the business, the database server, the hardware platforms and usage patterns. Developing a partitioning strategy involves knowledge of the following four major factors:
- Client usage patterns
- Data factors
- DBMS functionality
- Data model
To enable partitioning, a Partition Mapping Table (PMT) is needed. This table contains the attribute ID(s) used to define the partitioning (partition keys). In addition, the PMT must contain a column named 'PBTNAME' containing the names of each of the partitioned base tables.
The following example shows a schema before and after enabling partitioning:
Pre-Partitioning:
Fact Table:
http://127.0.0.1
CORPORATE_SALES
| http://127.0.0.1
CORPORATE_SALES
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
REGION_YEAR_SALES
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
The CORPORATE_SALES is a base table that contains the sales from two hierarchies only: Time and Region.YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200199919990220008150200020001110003902000200012200098020002000122001560
|
Post Partitioning:
In an environment where most of the business questions are enclosed within one year and one region (only a few reports contain data across years/ regions) a schema partitioned on year and region would be the best choice.
Partition Mapping Table (PMT):
REGION_YEAR_MAP
A PMT is needed for each fact table to be partitioned. In this case the REGION_YEAR_MAP has been created (by the DBA) and included into the Strategy warehouse catalog.
| When a PMT is added to the warehouse catalog, it will be added as a partition mapping table, the icon will change and the number of partitions will be shown in parenthesis:
Also, all the corresponding partitions are removed from the list of available tables. The partitions function as a unit; they cannot be deselected individually. To see the contents of the PMT, right-click on it and choose 'View Partitions'.
After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
|
In this case, the partition is defined by YEAR_ID and REGION_ID:
REGION_YEAR_MAP
After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
|
After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
|
After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
|
After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
|
After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
| After adding the table, a Strategy partition-mapping object has to be defined (File > New > Partition menu).
YEAR_IDREGION_IDPBTNAME19991R01_Y1999_SALES19992R02_Y1999_SALES20001R01_Y2000_SALES20002R02_ Y2000_SALES
|
Important: There is a 1 to 1 relationship between the partition key and the table name. For example, Year 1999 and Region 1 must NOT exist in two tables and the R01_Y1999_SALES table must only contain data for the year 1999 and region 1.
Important: If a prefix is needed to access the PMTs, it has to be included into the mapping table when populating it.
Partitioned Base Tables (PBT):
The partitioned tables must be created and populated by the DBA. Views are not recommended since they defeat the purpose of partitioning and the performance will be hampered instead of improved. In this case, each table contains information for one year and one region only.
|
|
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
Usually the PBTs hold the same structure as the original base fact table. Nevertheless, they can be implemented in two different ways:
1. Denormalized PBTs:
R01_Y1999_SALES
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
A denormalized partitioned base table is bigger since it is more redundant. Nevertheless, this is recommended for better performance.
1. Normalized PBTs:
R01_Y1999_SALES
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
R01_Y1999_SALES R01_Y2000_SALES R02_Y1999_SALES R02_Y2000_SALES YEAR_IDMONTH_IDREGION_IDSTORE_IDSALES_AMT19991999011000150199919990110002200MONTH_IDSTORE_IDSALES_AMT1999010001501999010002200
|
A normalized base table saves database space but it is not recommended if performance is a key issue. The Strategy Engine always applies filters on the partitioned base table queries even if it the filter is a partitioning key.
For example, if a filter on year = 1999 and region = 1 is used. The engine will apply the filter year = 1999 and region = 1 to the R01_Y1999_SALES table. To resolve that filter, joins to the LU_STORE and LU_MONTH tables will be needed since those IDs are not found in it.
Important: Parts of a compound attribute cannot be omitted. For example, in a schema where Store is a compound attribute with Region-Market-Store, and the warehouse is partitioned by Store, the partitioned base tables cannot omit Region and Market.
