The logical table size calculation in Microstrategy

The logical table size calculation 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.

 

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:

1. Calculate the number of levels per hierarchy:

   Hierarchy 1: 3
   Hierarchy 2: 4

2. 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	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

 

1. Note that the lowest level attribute weight for every hierarchy is always 10.

    

2. 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

 

In this way, every table in the project gets its own logical size depending on the attribute IDs that they contain and the level of the attributes.

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:

1. 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.
2. From this set of tables, the engine chooses the table with the smallest LTS.
3. 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.

 

NOTE:
 
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."
 

source: MSTR