Skip to main content

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:
 

KB5200-7X0-0054A.gif

 

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

 

HierarchyAttributeLevel in HierarchyWeight
1A11/3 * 10 = 3.33
B22/3 * 10 = 6.66
C11/3 * 10 = 3.33
D33/3 * 10 = 10
2E11/4 * 10 = 2.5
F22/4 * 10 = 5
G33/4 * 10 = 7.5
H44/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))

 

TableAttribute IDsLogical Table Size
LU_AAROUND(3.33) = 3
LU_BA, BROUND(3.33 + 6.66) = 10
LU_CCROUND(3.33) = 3
LU_DA, B, C, DROUND(3.33 + 6.66 + 3.33 + 10) = 23
LU_EEROUND(2.5) = 2
LU_FE, FROUND(2.5 + 5) = 7
LU_GF, GROUND(5 + 7.5) = 12
LU_HG, HROUND(7.5 + 10) = 17
F1A, B, C, D, E, F, G, HROUND(3.33 + 6.66 + 3.33 + 10 + 2.5 + 5 + 7 + 7.5 + 10) = 55
F2D, HROUND(10 + 10) = 20
F3A, FROUND(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:

KB5200-7X0-0054B.gif

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

Comments

Popular posts from this blog

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned.  Case  can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 ,  ReturnValue1 ,  Condition2 , ReturnValue2 ,...,  DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV  evaluates a single metric and returns different values according to the results. It can be used to perfo...

Optimizing queries in Microstrategy using VLDB properties

Optimizing queries in  Microstrategy using VLDB properties #vldb #vldbproperties The table b elow summarizes the Query Optimizations VLDB properties. Additional details about each property, including examples where necessary, are provided in the sections following the table. Property Description Possible Values Default Value Additional Final Pass Option Determines whether the Engine calculates...

Microstrategy "Error type: Odbc error. Odbc operation attempted

 "Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’.  Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...

Control the display of null and zero metric values

Show   Control the display of null and zero metric values in a grid report You can determine how to display or hide rows and columns in a grid report that consist only of null or zero metric values. You can have MicroStrategy hide the rows and columns in the following ways: Hide rows and columns that consist only of null metric values Hide rows and columns that consist only of zero metric values Hide rows and columns that consist only of null or zero metric values (default) Once you have defined how MicroStrategy hides null and zero metric values in the grid, you can quickly show or hide the grid using the Hide Nulls/Zeros option in the Data menu, as described below, or by clicking the  Hide Nulls/Zeros  icon  in the Data toolbar. To determine how null and zero metric values are displayed or hidden in a grid report Open the report in Edit mode. From the  Tools  menu, select  Report Options . The Report Options...

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level   Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...

System Manager workflow to execute on a schedule

Creating a System Manager workflow to execute on a schedule System Manager workflow can execute on a schedule or after an event has been triggered. This can be accomplished by creating a simple batch file, and scheduling that batch file to execute with a third-party tool like Microsoft Task Scheduler.   Note : To avoid user permission conflicts, the following steps must be performed with highest privileges.   In the below example, the workflow makes the i-server restarts every day.   1. The user must first have a valid workflow. This particular workflow is a template that is delivered out-of-the-box with System Manager.   2. Save the workflow in  .smw  format.   3. In a text editor (such as Notepad), enter the command line statement that the task scheduler should execute.     MASysMgr.exe -w C:\filename.smw” “UserName=User1 “Password=1234”   4. Save the file in  .bat  ...
MicroStrategy Developer Preferences options are expanded so big that some options are being cutoff. Show the hidden objects in the  Microstrategy  developer MicroStrategy Developer Preferences options are expanded so big that some options are being cut off. The steps below given in the MSTR article may not work. This can be simple handled by using the steps below:  In the Microstrategy Developer go to Tools -> Preferences (Not my prefernces :) ) Under Developer category -> select Browsing on the browsing tab you see all the options like below: 3. Now using the mouse place the cursor on text box of 10000 which is next to 'Maximum number of monitoring objects displayed per page. 4. Then Hit Tab on Keyboard and hit another Tab on keyboard 5. Then press the space or down arrow on keyboard and click on OK or Enter. That will show the hidden objects in the Microstrategy developer   Normal Version ...

Fact tables levels tables in Microstrategy explained

Fact tables levels in Microstrategy: Fact tables are used to store fact data. Fact tables should contain attribute Id's and fact values which are measurable. All the descriptive information about the fact tables should stored in Dimension tables either in Star Schema fashion or Snow Flake Schema fashion which is best suited to your reporting solution. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes using these attribute ID columns. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored. So the level of a fact table in the Fact_Item_Day_Customer can be the attribute Id's which is at Day, Item & Customer Id level. For example, fact tables containing sales and inventory data look like the tables shown in the following diagram: Base fact columns ver...

Internationalization Design Technics

Microstrategy Internationalization Design Technics MicroStrategy supports data internationalization through two different techniques. You can either provide translated data through the use of extra tables and columns, or you can provide separate databases to store your translated data. These techniques are described below: You can support data internationalization in your database by using separate tables and columns to store your translated data. You can use various combinations of tables and columns to support and identify the translated data in your database. To support displaying the name of each month in multiple languages, you can include the translated names in a separate column, one for each required language, within the same table. Each column can use a suffix to identify that the column contains translated data for a certain language. The same LU_MONTH_OF_YEAR table with translated data for the Spanish and German langua...

Retrieve a list of user groups and the associated users in MicroStrategy

    Retrieve a list of user groups and the associated users in MicroStrategy Developer 9.x Although MicroStrategy Command Manager 9.x can be used to generate lists of all user groups and all users within a particular group, there is currently no way for it to retrieve a list of all groups and all of the users in each group. If such a list is desired, it can be created using the Project Documentation Wizard in MicroStrategy Desktop Developer 9.x. Follow the steps below to create a list of all groups and the users in each group: In MicroStrategy Developer 9.x, select 'Project Documentation' from the Tools menu to start the wizard. Select any project that is in the project source that contains the users and groups and click Next. Select only Configuration Objects for documentation. Uncheck the 'Basic Properties' object category from the next screen, as shown below: Then select only 'User Group' under the Configuratio...