Logical Table Size

 Logical Table Size

What is a logical table size in MicroStrategy ?

This is a number that represents the level of aggregation for a given table.  Logical Table Size (LTS) is used my MSTR to identify the best table to choose when generating SQL. This is a number that will be used to identify which table is best fetching the data. For example, I have a report, with Category, Subcategory and profit metric.   I have the following two fact tables in the data warehouse

1. FACT1 - category,subcategory,profit
2. FACT2 - day,category,subcategory,profit.

Which of the tables is MSTR likely to choose for the join ?  MSTR uses an algoritham to find the best fact table for data retrival. It chooses the fact table with the lowest LTS.  Lets look at how mstr calculates the LTS, but before that we need to see the hierarchy. You need to use the system hierarchy, since this hierarchy build on true parent child relationship defined for the project.

Category

Sub Category

Item

Brand

How many levels are there in this hierarchy ? 4

MSTR will assign a weight based on the level of the attribute in the hierarchy.

Category  = 1/4 *10  = 2.5

Sub Category =  2/4 * 10 = 5

Item = 3/4 * 10 = 7.5

Brand = 4/4 * 10 = 10 (note that the lowest level attribute always have a weight of 10 )

Similary for a the date hierarchy, the values are calculated as below.

Year

Month

Week

Day

Year = 1/4* 10 = 2.5

Month = 2/4 *10 = 5

Week = 3/4 * 10 = 7.5

Day = 4/4 * 10 = 10

How to use the attribute weight and calculate logical table size ?

1. FACT1 = weight for category + weight for sub category = 2.5 + 5 = 7.5 (rounded to 8)
2. FACT2 = weight of day +  weight for category + weight for sub category = 10 + 2.5 + 5 = 17.5 (rounded to 18)

The fact table with smallest of these values is selected for querying the metric.  So in this case mstr will use FACT1 in the SQL join.

 You might come across hierarchies where a child has multiple parent. for example .

Category

Sub Category

Item

Product

 

Brand

 Here Brand is a child of both Item and Product. In this case the level of Brand is 4 (by using the level path from Item, here Item is at level 3) or 2 (by using the level path from Product, here product is at level 1)

MSTR will consider the highest of these numbers when consideing the level for Brand. ie 4.