KB30443: Considerations for the handling of attribute data with respect to partition mapping in MicroStrategy SQL Generation Engine

In Strategy SQL Generation Engine 8.1.x and 9.x, fact tables may be partitioned using warehouse partition mapping tables or metadata partition mapping tables. In warehouse partition mapping, a table in the warehouse associates attribute elements with the table names of the individual partitions. Metadata partition mapping defines data slices by filters, where each slice maps onto a physical partition in the warehouse.
 
Both partitioning schemes depend on proper treatment of attribute data.
 
1. Functional uses of tables
Following are the primary uses of warehouse tables in Strategy SQL Generation Engine:

• Base table: Provides source data for metrics. In a given SQL pass, the base table(s) may be fact tables or previously calculated intermediate tables.
• Lookup table: Provides attribute IDs and descriptions. A table functions as a lookup table if its columns appear in the SELECT, WHERE or GROUP BY clauses.
• Relationship table: Includes at least two attribute ID columns to join attributes. A lookup table can serve as a relationship table, or it could serve as both a lookup and a relationship in the same query.
  
• A table functions solely as a relationship table if it appears only in the FROM clause.
• A transformation table is a special case of relationship table.
• Partition mapping tables: Strategy warehouse and metadata partition mapping use pre-queries to determine the physical tables to use for a particular metric. Warehouse partition mapping depends on physical tables in the warehouse, which will appear at the top of the FROM clause in the pre-query.

In this document, base (or fact), lookup and relationship tables refer to the function of the table in the SQL pass. Strategy SQL Generation Engine does not know the purpose for which a table was created in the warehouse. It knows only where the table is used in the schema and how it is called into service in a given query. A table may be intended as a fact table, but used in a query to support an attribute relationship. For purposes of this discussion, that table will be considered a relationship table.
 
2. Attribute lookups and relationships should generally not use fact tables.
Strategy does not prohibit the use of fact tables as attribute lookup or relationship tables. However, it is not recommended to press a fact tables into double duty in this way, for the following reasons:

• Element browsing performance is likely to suffer. Fact tables are generally much larger than dedicated lookup or dimension tables. When Strategy queries the database for attribute elements, the database will have to sift through many times more rows to return the distinct set of elements.
• Fact tables may appear in unexpected places, impacting performance and possibly producing incorrect results.
• Attribute relationships are assumed to be stable, while facts can be volatile.
• Insufficient keys for joining may be present in some situations leading to double counting and invalid metric values.

3. Only fact tables should be partitioned.
Warehouse and metadata partition mapping in Strategy SQL Generation Engine support partitioning for fact tables only. It is not valid to partition attribute lookup and relationship tables. Attribute lookup and relationship tables are assumed to be complete. If they are partitioned, completeness cannot be assured.
 
If a fact table had been used to support attribute data or relationships, and that fact table is later partitioned, it is no longer valid to use the partitions as attribute lookup or relationship tables. It will be necessary to provide the attribute lookup or relationship data in another way, by creating new warehouse tables or database views.
 
4. Partition mapping tables for warehouse partitioning may never be used as attribute lookup or relationship tables.
Using a partition mapping table as an attribute lookup generates the following error:
 

Error: SQLEngine got an Exception from DFC: Logic Exception: Partition mapping table can not be used as lookup table.
Error in Process method of Component: SQLEngineServer, Project Strategy Tutorial, Job 1152, Error Code= -2147212800.

 
Strategy cannot resolve the relationship table to the partition at runtime, so the following SQL is submitted to the warehouse, producing a database error.
 

select a11.ITEM_ID  ITEM_ID,

   a11.ITEM_NAME  ITEM_NAME0

from LU_ITEM a11

where ((a11.ITEM_ID)

   in (select s22.ITEM_ID

      from {|Partition_Base_Table|} s22

      where s22.MONTH_ID in (200605)))