Logical Tables » Creating logical tables » Defining logical table sizes

Defining logical table sizes

MicroStrategy assigns a size to every table in the project when you initially add it to the project. These size assignments are based on the columns in the tables and the attributes to which those columns correspond. Because MicroStrategy uses the conceptual or logical attribute definitions to assign a size to each table in the project, this measurement is referred to as logical table size.

The logical table size determines which logical table to use to answer a query when multiple logical tables would provide the same data. The table with the lowest logical table size is used, as a lower logical table size means the table has a higher level of aggregation. The performance of accessing a table with a higher level of aggregation is usually better than accessing a table with a lower level of aggregation.

MicroStrategy initially assigns logical table sizes based on an algorithm that takes into account the number of attribute columns and the various levels at which they exist in their respective hierarchies. If this does not meet your requirements, you can define the logical table size for a table as required.

For example, a base fact table contains millions of rows of transaction-level data. The other tables have only higher-level or summary information. Since the levels of the attributes are lower in the base fact table, the base fact table is assigned a higher value for its logical table size than are the summary tables with higher-level attributes. This means the table with summary information is used to return data rather than the table with transaction-level data when the tables can be used to answer the same query.

However, in the example scenario described above, there may be a reason that you want to access the table with transaction-level data instead of the table with summary information. In these types of scenarios, you can manually define the logical table size for a table, for which you have the following options:

Defining the logical table size of a single table
Defining logical table size while comparing all project tables

Defining the logical table size of a single table

The steps to define the logical table size of a single table using the Table Editor are described below.

To define the logical table size of a single table

1 From MicroStrategy Developer, log in to a project.

Browse to the location where you store your tables, and double-click the desired table. If a message is displayed asking if you want to use read only mode or edit mode, select Edit and click OK to open the Table Editor in edit mode so that you can make changes to the table. The Table Editor opens.

If you are only given the option of using read only mode, this means another user is modifying the project’s schema. You cannot use edit mode until the other user is finished with their changes and the schema is unlocked.
For information on how you can use read only mode and edit mode for various schema editors, see Using read only or edit mode for schema editors.
2 On the Logical View tab, in the Logical size area, type the value for the table’s logical size.

If multiple tables can be used to answer the same query, the table with the lowest logical table size is used.

3 To lock the logical table size of a table, select the Preserve this logical size when updating schema information check box. When a table’s logical size is locked, the table is excluded from the logical table size calculation when a schema update is performed.
4 Click Save and Close to save your modifications to the table and close the Table Editor.
5 You must update the schema for the new logical table size to take effect. Close all editors, then from the Schema menu, select Update Schema.

Defining logical table size while comparing all project tables

The steps to define the logical table size of a table while comparing all project tables are described below.

To define the logical table size of a table while comparing all project tables

1 From MicroStrategy Developer, log in to a project.

From the Schema menu, select Edit Logical Size. If a message is displayed asking if you want to use read only mode or edit mode, select Edit and click OK to open the Logical Size Editor in edit mode so that you can make changes to the table’s logical size. The Logical Size Editor opens.

If you are only given the option of using read only mode, this means another user is modifying the project’s schema. You cannot use edit mode until the other user is finished with their changes and the schema is unlocked.
For information on how you can use read only mode and edit mode for various schema editors, see Using read only or edit mode for schema editors.
2 In the Size value column for a table, type the desired value for the table's logical size.
If multiple tables can be used to answer the same query, the table with the lowest logical table size is used.
You can sort any column in the Logical Size Editor by clicking on the column heading.
You can display the number of rows in each table by selecting Show the table row count from the Edit menu.
3 To lock the size of a table, select that table’s Size locked check box. When a table’s logical size is locked the table is excluded from the logical table size calculation when a schema update is performed. This helps to retain any table sizes that are manually defined.

To unlock the table's size, clear the Size locked check box.

To lock or unlock all table values displayed, click the Lock all or Unlock all toolbar options.

4 To allow MicroStrategy to calculate the table logical size automatically for all tables, click the Calculate all icon.
5 From the File menu, select Save to save your changes.
6 From the File menu, select Close to close the Logical Size Editor.
7 You must update the schema for any new logical table sizes to take effect. Close all editors, then from the Schema menu, select Update Schema.