KB6540: Metadata partitioning in MicroStrategy SQL Generation Engine explained

Metadata partitioning is the term that Strategy uses to distinguish a partitioning setup that is stored in the Strategy Metadata repository. In contrast with warehouse partitioning, a metadata partitioning solution does not require a Partition Mapping Table (PMT) stored in the warehouse. The only tables that are needed are the Partitioned Base Tables (PBTs) and the mapping has to be defined in the project's metadata using the Metadata Partition Mapping Editor.
 
Single Dimension Partitioning:
 
In the following example, metadata partitioning is used for the [Single Dim Price] fact on the [Year] attribute:
 

 
Logic:
When a report with:
[Year]
[M1]: Sum([Single Dim Price]) {~+}
 
Without Filter:
 
When no filter is used on the report, the SQL that the Engine generates:
select   count(*) WJXBFS1
from   LU_YEAR a21
where   a21.YEAR_ID in (2000)
select   count(*) WJXBFS1
from   LU_YEAR a21
where   a21.YEAR_ID in (1999)
 
The Engine generates a prequery against EACH ONE of the tables defined in the logical partition. The Engine only checks if a nonzero value is returned for each one of the prequeries. If the count value is at least 1, then the PBT corresponding to the pass contains data relevant to the calculation and it will be included in future passes.
select   a11.YEAR_ID YEAR_ID,
    sum(a11.UNIT_PRICE) M1
from   PRICE_00 a11
group by     a11.YEAR_ID
union all
select   a11.YEAR_ID YEAR_ID,
    sum(a11.UNIT_PRICE) M1
from   PRICE_99 a11
group by     a11.YEAR_ID
 
Since both prequeries return at least 1, then the Engine generates SQL against the two existing PBTs.
 
With a Valid Filter
 
When a filter is used in the report ([year] = 1999), the following SQL is generated:
select   count(*) WJXBFS1
from   LU_YEAR a21
where   (a21.YEAR_ID in (1999)
  and   a21.YEAR_ID in (2000))
select   count(*) WJXBFS1
from   LU_YEAR a21
where   (a21.YEAR_ID in (1999)
  and   a21.YEAR_ID in (1999))
 
The prequery is the same as in the previous example, the first pass will return a 0 count.
select   a11.YEAR_ID YEAR_ID,
    sum(a11.UNIT_PRICE) M1
from   PRICE_99 a11
where   a11.YEAR_ID in (1999)
group by     a11.YEAR_ID
 
Since the prequery detects data only for the second pass, the one corresponding to the PRICE_99 table, the Engine will only query the table [PRICE_99].
 
 
With an Invalid Filter
 
When a filter is used in the report ([year] = 2003), and there is no valid data for that year, the following SQL is generated:
select   count(*) WJXBFS1
from   LU_YEAR a21
where   (a21.YEAR_ID in (2003)
  and   a21.YEAR_ID in (2000))
select   count(*) WJXBFS1
from   LU_YEAR a21
where   (a21.YEAR_ID in (2003)
  and   a21.YEAR_ID in (1999))
 
No SQL is generated to retrieve the fact values from the PBTs since neither prequery returns a nonzero value for the specified condition.
 
Cross Dimensional Partitioning
 
In the following example, metadata partitioning is used for the [Cross Dim Price] fact on the [Year] and [Item] attributes:
 

 
Logic:
When a report with:
[Year], [Item] 
[M2]: Sum([Cross Dim Price]) {~+} 
select   count(*) WJXBFS1 
from   LU_ITEM a21 
    cross join LU_YEAR a22 
where   (a21.ITEM_ID in (1) 
  and   a22.YEAR_ID in (1997)) 
select   count(*) WJXBFS1 
from   LU_ITEM a21 
    cross join LU_YEAR a22 
where   (a21.ITEM_ID in (2) 
  and   a22.YEAR_ID in (1998))
Since there are no other tables to query for the legitimacy of the filtering criterion. The first 2 queries perform a cross-join between lookup tables. This cross join will always be performed when the partition is performed across dimensions (unrelated attributes). If the attributes on the template belong to the same hierarchy, then no cross-join will be generated.
select   a11.ITEM_ID ITEM_ID,
    a12.Item_Name Item_Name,
    a11.YEAR_ID YEAR_ID,
    a11.UNIT_PRICE M2
from   PRICE_97 a11
    join LU_ITEM a12
      on (a11.ITEM_ID = a12.ITEM_ID)
union all
select   a11.ITEM_ID ITEM_ID,
    a12.Item_Name Item_Name,
    a11.YEAR_ID YEAR_ID,
    a11.UNIT_PRICE M2
from   PRICE_98 a11
    join LU_ITEM a12
      on (a11.ITEM_ID = a12.ITEM_ID)
 
The last pass will go to the fact tables needed for the analysis.
 
 
Note: If a database exhibits poor performance on the partitioning pre-queries because of the count(*) expression, the VLDB property Query Optimizations > MD Partition Prequery Option can be changed to the non-default option, "Use constant in prequery." If the "use constant" option is chosen, pre-query SQL will read as follows, selecting the constant 1 instead of the count.
 
select   1 WJXBFS1
from   LU_ITEM a21
    cross join LU_YEAR a22
where   (a21.ITEM_ID in (1)
  and   a22.YEAR_ID in (1997))
 
Strategy's general recommendation is to use the default, count(*) setting. With count(*), one row will be returned; if the count value is > 0, the partition table should be used in subsequent passes; if 0, that partition will be ignored. "Use constant" means that one row will be returned for every element matching the filter qualification. There is no way to predict in advance how many rows that will be, and consequently no way to be certain of avoiding an adverse impact on Intelligence Server memory.
 
Note: Strategy recommends the use of warehouse partitioning when implementing cross-dimensional partitioning.