Every time you import or create a table in your project, a logical table size is assigned to it. This size is calculated based on the table columns, more precisely on the attributes directly mapped to it and their relationships. MicroStrategy gives priority to tables with smaller sizes each time a report can be satisfied by multiple tables. 

Most of the times it’s not necessary to change this setting (MicroStrategy algorithm works pretty well), however there might be one or two times you’ll need to manually assign different table sizes to each table in order to get the best performance.

Lets say that if we have two or more tables in the project that can satisfy the same report (attribute, metrics and filter conditions) then these tables are aggregate tables, this means they have similar data but aggregated at different dimension levels. They may also have additional facts but let’s leave it to other discussion.

Attribute weight is defined by the position of an attribute in its hierarchy, divided by the number of attributes present in that hierarchy multiplied by a factor of 10. LTS is the sum of weights of the attributes mapped in a table.

In this example there are two aggregate fact tables that have the same lts of 15, however they have different information, one of these tables contains sku and country information, and the other has supplier and city. Based on the attributes they contain it’s obvious that the table with the sku information is much larger since there are many more skus than suppliers. In this example, where the logical table size is the same but the physical size is much different, it’s very useful to change the lts manually. 

Generally, smaller logical size does equate to smaller physical size. Tables with higher-level attributes usually have a smaller logical table size than tables with lower-level attributes. However, there are times when this is not the case due to the particular combination of attributes in a table. In such cases, you have to change the logical table size to force the SQL Engine to use the table that you know has a smaller physical size. In this example, ‘FACT_SUPPLIER_CITY’ table could have a lts smaller than 15 in order to be the preferred table.

Leave a Reply