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:

Calculate the number of levels per hierarchy:
Hierarchy 1: 3
Hierarchy 2: 4
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

Note that the lowest level attribute weight for every hierarchy is always 10.
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:

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

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select Custom as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the Project Design Guide . ...

Error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog

Error. Engine Attribute Role Limit Exceeded! Some times Microstrategy will give the below error when updating the schema after an attribute is created. MicroStrategy Developer --------------------------- Error(s) occurred while loading schema: [DFCSCHEMA] Population Exception: The object shown in the following hierarchy no longer exists in schema: -Table LKUP_TEST_FEE_SHIPMENTS error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog; OR (2) Use Table Alias featureDSSSQLEngine: Schema loading error: Message from GetErrorInfo : Report cache is not found.. Error(s) occurred while loading schema: 63. CAUSE: This error message means that the table shown in the error text needs to be split into a very large number tables. MicroStrategy has a limitation that only allows one table to be split into no more than 100 tables. The error above is shown when this limitation is surpassed....

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

mstrio – Python and R wrappers for the MicroStrategy

mstrio – Python and R wrappers for the MicroStrategy REST APIs Connecting to MicroStrategy Create a connection to the Intelligence Server using Connection() and  connect() in Python and R, respectively. Required arguments for the   Connection()  function are the URL for the MicroStrategy REST API server, MicroStrategy Intelligence Server username and password, as well as the MicroStrategy project name. By default, the  connect()  function anticipates your MicroStrategy Intelligence Server username and password. LDAP authentication is also supported. Use the optional argument  login_mode=16  in the  connect()  function for LDAP authentication. Extract data from cubes and reports To extract data from MicroStrategy cubes and reports, use the get_cube()  and  get_report() functions. Use...

Create an alert-based subscription in MicroStrategy Distribution Services

Create an alert-based subscription in MicroStrategy Distribution Services on Web Subscription to a report or Report Services document which will be executed when a certain conditional threshold is met based on another executing report. For example, a scheduled report executes which shows the Revenue by day for the past week. If the Revenue on any one day falls below a certain value, a subscription to another report or Report Services document can be triggered and delivered to a recipient. An alert based subscription can only be created directly on a report; however, another report or Report Services document can be delivered when the alert based subscription is triggered. Note: you need a grid report to create an alert and you cannot create if you want to create on a document with text boxes. The following example will walk through the basic steps on how to setup a subscription based on an alert like this: Follow the brief steps bel...

Relative path for images in Microstartegy

Best practices on how to reference an image specified using a relative path for exported documents in MicroStrategy If possible, users should use HTTP paths to reference an image other than relative path or absolute path. If it is necessary to use a relative path to reference image, multiple locations need to store the copy of image as shown in the following table: Important notes: MicroStrategy Web Server is not involved in storing image when exporting. Image should be stored in Intelligence Server or MicroStrategy Developer Client For MicroStrategy Developer export, local MicroStrategy Developer handles the export, image should be placed on every single Developer machine If you don't know your Common Path or MSTR Home Path you can obtain them from the following registry keys if using windows or msireg.reg if using Unix HKEY_LOCAL_MACHINE --> SOFTWARE -->Microstrategy --> DSS Server -> Casetor -->Home Path HKEY_LOCAL_MACHINE --> SO...

Ways to create derived elements

Ways to create derived elements Using the Derived Elements Editor, you can create derived elements using the techniques described in the topics listed below: Grouping attribute elements to create derived elements Filtering attribute elements to create derived elements Using calculations to create derived elements

Derived metric based on attribute values

Derived metric based on attribute values Here is how could create and display data correctly on using below simple steps. Create a report with Category, Subcategory and Revenue. Create New Metric in a report or VI. Case((Category@ID = 1), Revenue, 0) Booksand Name it as Revenue for where 2 is Category ID for "Books" Report will display result as below. Result for new metric is blank. Now to fix this create a new Derived metric on Category attribute first with formula as Max(Category) {~ } and calling Books Now Edit the "Revenue for Books metric and Replace Category@ID with this new Books metric formula would looks like this Case((Books = 1), Revenue, 0). Report result would now display as expected as shown below

Microstrategy Filters & Prompts explained

Microstrategy Filters & Prompts explained Filters Specify conditions required for data to be included in a report. A filter specifies the conditions that data must meet to be included in a report or a metric. Filters limit the data that is extracted from the data source and focus the data presented to the end user.When building a report, users can create filter expressions that include sophisticated conditional clauses in order to pull specific slices of information. Within reports and documents, end users can further narrow down results by adding view filters that dynamically change the subset of data being displayed. Prompts A prompt allows a user to provide an answer to restrict or filter data during report execution. P rompts allow report designers to create reports that let users change the report content at run time. Prompts can modify the definition of custom groups, filters, metrics, and templates, as well as reports. P...

Microstrategy Dossier training videos

Microstrategy Dossier training videos Adding data to a Dossier: Creating a visualization filter in Dossier: Sending Dossier to User libraries: How to format Dossiers: Adding a designer filter to a Dossier: Use Bookmarks in Dossiers: Dynamic Links in Dossier: Recreating documents from Dossiers: Exploring and searching in Dossiers: Exploring sample dossiers in MSTR library: Using Page to Page Targets in Dossiers:

Microstrategy Analytics

Search This Blog