How are the fact tables determined using the logical table size for SQL generation in MicroStrategy
The logical table size is an integer number that represents the granularity or level of aggregation of a particular table. It is called 'logical' because it is not related to the physical size of the tables (number of rows). It is calculated according to the attribute IDs that are present in the table and their level in the system hierarchy.
Even though, the number does not reveal the actual number of rows in the table, it is an accurate way of measuring a table size without having to access its contents.
IMPORTANT:
The system hierarchy is defined by the parent-child relationships between attributes of the same family (formerly known as a dimension), not by user-defined hierarchies (i.e., drilling hierarchies).
MicroStrategy Engine utilizes an algorithm based on attribute keys to calculate the Logical Table Size (LTS):
Given the following tables:
The algorithm that calculates the table sizes performs the following steps:
- Calculate the number of levels per hierarchy:
Hierarchy 1: 3
Hierarchy 2: 4
- Calculate each attribute individual weight according to the level in the hierarchy (level in hierarchy/number of levels in hierarchy * 10). The attribute level follows a consecutive order from the top to the bottom. For attributes with more than one parent, the next consecutive number of the highest weight parent is used (i.e., attribute D has a level of 3 (from attribute B) and not 2 (from attribute C).
Hierarchy 1: 3
Hierarchy 2: 4
Hierarchy 2: 4
| Hierarchy | Attribute | Level in Hierarchy | Weight |
| 1 | A | 1 | 1/3 * 10 = 3.33 |
| B | 2 | 2/3 * 10 = 6.66 | |
| C | 1 | 1/3 * 10 = 3.33 | |
| D | 3 | 3/3 * 10 = 10 | |
| 2 | E | 1 | 1/4 * 10 = 2.5 |
| F | 2 | 2/4 * 10 = 5 | |
| G | 3 | 3/4 * 10 = 7.5 | |
| H | 4 | 4/4 * 10 = 10 |
- Note that the lowest level attribute weight for every hierarchy is always 10.
- Calculate each table logical size: LTS = ROUND(sum(weights of attributes in the table))
| Table | Attribute IDs | Logical Table Size |
| LU_A | A | ROUND(3.33) = 3 |
| LU_B | A, B | ROUND(3.33 + 6.66) = 10 |
| LU_C | C | ROUND(3.33) = 3 |
| LU_D | A, B, C, D | ROUND(3.33 + 6.66 + 3.33 + 10) = 23 |
| LU_E | E | ROUND(2.5) = 2 |
| LU_F | E, F | ROUND(2.5 + 5) = 7 |
| LU_G | F, G | ROUND(5 + 7.5) = 12 |
| LU_H | G, H | ROUND(7.5 + 10) = 17 |
| F1 | A, B, C, D, E, F, G, H | ROUND(3.33 + 6.66 + 3.33 + 10 + 2.5 + 5 + 7 + 7.5 + 10) = 55 |
| F2 | D, H | ROUND(10 + 10) = 20 |
| F3 | A, F | ROUND(5 + 3.33) = 8 |
The previous process is executed every time that the warehouse catalog is saved or the project's schema is updated with the following option checked:
Whenever a report is executed and SQL is generated, the MicroStrategy SQL Engine performs the following steps to determine what tables is to be used:
- Get the set of tables that can resolve the report, according to (mainly, but not exclusively) the attributes on the template, attributes on the filter and the metric dimensionality.
- From this set of tables, the engine chooses the table with the smallest LTS.
- If more than one table have the same LTS, the engine chooses the first table in memory. This scenario is possible when many aggregate tables exist in the project but should never affect the result set in a consistent warehouse. If certain table is preferable, then the LTS for that table may be modified to force the Engine to choose it.
If the schema is updated with the 'Recalculate table logical size' option, then the sizes are recalculated. If any change was made to the logical table size of a table, it is overwritten. Users may prevent a custom logical table size from being overwritten by checking the option to "Preserve this logical size when updating Schema information."
Comments
Post a Comment