Warehouse Partition Mapping Table in MicroStrategy

To add and set up a Partition Mapping Table in MicroStrategy:

Step 1: Create the Partition Mapping Table (PMT). Views are not recommended since they defeat the purpose of partitioning and the performance will be hampered instead of improved. Usually the PBTs hold the same structure as the original base fact table.

This table must be created with the following structure:

The ATTRIBUTE_ID column name must match the column name on the partitioned base tables. This column contains the values of the attributes at which the tables are partitioned. 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.

For Example, if the partition level is at Year, this column will be named 'Year_id' and contain values such as: 1998, 1999 and 2000.

The PBTNAME column name cannot be changed. This column contains the names of the partitioned base tables. PBTNAME = Partitioned Base Table Name.

Step 2: Add this table to MicroStrategy using 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. Update Schema.

Note: If a prefix is needed to access the PMTs, it has to be included into the mapping table when populating it in WH Catalog.

Step 3: Go to the Partition Mappings folder under Schema Objects. The PMT appears in the right window. Right-click on the table and select edit. Click on the 'Add' button and select the attribute that marks the level of partitioning. Update Schema.

NOTE: (1) The PBTNAME in the Partition Mapping Table (PMT) should be unique. Otherwise, double counting may occur.(2) A PMT is needed for each fact table to be partitioned. (3) A normalized partition base table (PBT) saves database space but it is not recommended if performance is a key issue. The MicroStrategy Engine always applies filters on the partitioned base table queries even if it the filter is a partitioning key.