Skip to main content

MSTR VLDB SQL optimization tips

VLDB SQL optimization tips

 Edit 0 5…
https://www2.microstrategy.com/producthelp/10.6/SupplementalAdmin/WebHelp/Lang_1033/Content/AdminSupplemental/Optimizing_queries.htm

https://www2.microstrategy.com/producthelp/10.6/SupplementalAdmin/WebHelp/Lang_1033/Content/AdminSupplemental

https://www2.microstrategy.com/producthelp/10.6/SupplementalAdmin/WebHelp/Lang_1033/Content
/attr_selection_form_selection_option_interm_passes.htm

SQL Generation and Data Processing: VLDB Properties » Details for all VLDB properties » Selecting and inserting data with SQL: Select/Insert » Attribute Selection and Form Selection Option for Intermediate Passes
Attribute Selection and Form Selection Option for Intermediate Passes

Normally, the MicroStrategy SQL Engine selects the minimum number of columns that are needed in each pass. For an intermediate pass, the SQL Engine usually only selects attribute ID forms. The SQL Engine also selects the attributes necessary to make the join, usually key attributes. Then in the final pass, additional attributes or attribute forms that are necessary for report display can be joined.

This algorithm is optimal in most cases, as it minimizes the size of intermediate tables. However, in certain schemas, especially denormalized ones, and schemas that use fact tables as both lookup tables and relationship tables, such an algorithm may cause additional joins in the final pass.

Example

A report template contains the attributes Region and Store, and metrics M1 and M2. M1 uses the fact table FT1, which contains Store_ID, Store_Desc, Region_ID, Region_Desc, and f1. M2 uses the fact table FT2, which contains Store_ID, Store_Desc, Region_ID, Region_Desc, and F2. With the normal SQL Engine algorithm, the intermediate pass that calculates M1 selects Store_ID and F1, the intermediate pass that calculates M2 selects Store_ID and F2. Then the final pass joins these two intermediate tables together. But that is not enough. Since Region is on the template, it should join upward to the region level and find the Region_Desc form. This can be done by joining either FT1 or FT2 in the final pass. So with the original algorithm, either FT1 or FT2 is being accessed twice. If these tables are big, and they usually are, the performance can be very slow. On the other hand, if Store_ID, Store_Desc, Region_ID, and Region_Desc are picked up in the intermediate passes, there is no need to join FT1 or FT2 does not need to be joined in the final pass, thus boosting performance.

For this reason, the following two properties are available in MicroStrategy:

• Attribute Form Selection Option for Intermediate Pass
• Attribute Selection Option for Intermediate Pass
• These properties intend to use bigger (wider) intermediate tables to save additional joins in the final pass and exchange space for time.
• These two properties work independently. One does not influence the other.
• Each property has two values. The default behavior is the original algorithm.
• When the property is enabled:
— The SQL Engine selects additional attributes or attribute forms in the intermediate pass, when they are directly available.
— The SQL Engine does not join additional tables to select more attributes or forms. So for intermediate passes, the number of tables to be joined is the same as when the property is disabled.

Comments

Post a Comment

Popular posts from this blog

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 . ...
4 comments
Read more

Reduce Intelligent Cube Size By Finding Intelligent Cube Objects Which Are Not In Use

Reduce Intelligent Cube Size By Finding Intelligent Cube Objects Which Are Not In Use If the i-cubes can potentially be reduced in size an audit can be performed on the cube objects to see which cube objects are not being used by any of the view reports, documents, or dossiers.   The below are examples for a few of the common metadata database platforms . NOTE: To perform this audit, queries are run against the MicroStrategy metadata database. Ensure a metadata backup is taken prior to performing the below actions. Steps: 1) Identify the object ID of the Intelligent cube to be audited by checking the objects Property window 2) Identify the object ID of the project this cube exists within by opening the Project Configuration Sample Cube ID =   CFAF1E9B4D53990698C42E87C7AF2EB5 Sample Project ID =  B7CA92F04B9FAE8D941C3E9B7E0CD754   3) Run the below SQL against the metadata database by replacing the Cube ID and Project ID within the respective ...
Post a Comment
Read more

Compound key attribute

Compound key attribute A compound key attribute is an attribute whose primary key is made up by the combination of two or more columns. The multiple columns are joined with other attributes, typically in a many-to-many relationship . To create a compound key, users must create multiple attribute forms, group them together and set the form group as the key for the attribute. Use the same steps as specified in the help menu: Open attribute editor (right-click on attribute and select 'Edit') Select the forms that will make up the compound key From the 'Edit' menu, choose 'Group' NOTE: Modifying the key form will trigger required updates when saving. This may make related application objects (reports, filters and metrics) unusable. Click on 'Yes' to continue, when prompted, in a dialog box, to confirm this action Save the changes. Choose 'Update Schema' from the Schema menu
Post a Comment
Read more

Algorithm to calculate Logical Table Size in Microstrategy

How are the fact tables determined using the logical table size for SQL generation 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.   IMPORTANT:   The system hierarchy is defined by the parent-child relationships between attributes of the same family (formerly known as a dimension), not by user-defined hierarchies (i.e., drilling hierarchies).   MicroStrategy Engine utilizes an algorithm based on attribute keys to calculate the Logical Table Size (LTS): Given the following tables: ...
Post a Comment
Read more

Microstrategy Removing sections that do not have metric data

Removing sections that do not have metric data This is an interesting feature which might not be explored by many of us and it comes us handy. A  cross join between datasets can result in rows or Group Header/Footer sections that do not have metric data. For example, a document contains two datasets. Dataset 1 contains Year and Revenue, with data for three years (2007-2009). Dataset 2 contains Year and Profit, filtered to return data for only two years (2008 and 2009). If you place Year and Profit in the Details and execute the document, it displays three rows, although no profit data exists for 2007. This is a product of the cross join between the two datasets. You do not want to see the blank line for 2007 since it does not give you any data for profit. You can select the  Trim sections for which no metric value data is available  check box. This removes the row for 2007, since no metric data for Profit is available for 2007. The results are shown below: ...
Post a Comment
Read more

Predictive modelling in Data Science using Microstrategy

Creating a predictive modelling in MicroStrategy MicroStrategy Data Mining Services has been evolving to include more data mining algorithms and functionality. One key feature is MicroStrategy Developer’s Training Metric Wizard. The Training Metric Wizard can be used to create several different types of predictive models including linear and exponential regression, logistic regression, decision tree, cluster, time series, and association rules. Linear and exponential regression The linear regression data mining technique should be familiar to you if you have ever tried to extrapolate or interpolate data, tried to find the line that best fits a series of data points, or used Microsoft Excel’s LINEST or LOGEST functions. Regression analyzes the relationship between several predictive inputs, or independent variables, and a dependent variable that is to be predicted. Regression finds the line that best fits the data, with a minimum of error. For example, you have a dataset ...
1 comment
Read more

Scheduling a report or document to be sent to an FTP in MSTR

Scheduling a report or document to be sent to an FTP server You can have a report or document automatically delivered to a location on your FTP server on a specific schedule. To do so, you must subscribe to the report or document, as described in the steps below. You can customize your subscription by typing macros in the  File Name ,  Sub-folder , or  Zip File Name  fields. These macros are automatically replaced with the appropriate text when the report or document is delivered. For example, you create a subscription to a document. If you type  {&Project}  in the  File Name field, the name of the project in which the document is saved is displayed in the name of the document when it is delivered. • This procedure assumes that an administrator has already added your FTP server as a new device in Developer. Steps to do so are included in the  System Administrator Help . To send a report or document to an FTP server on a schedule ...
Post a Comment
Read more

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...
1 comment
Read more

Enable Incremental fetch in MSTR documents

Enable Incremental fetch in MSTR documents Incremental fetch divides large documents or layouts into pages, thereby loading the data in batches (or blocks) rather than all at the same time. This improves the usability and performance of a large document or layout, by reducing the load and overall memory usage on the web server. To apply incremental fetch to a document In MicroStrategy Web, open the document in the Document Editor. If the document contains multiple layouts, select the layout to apply incremental fetch to. From the  Tools  menu, choose  Document Properties . The Document Properties dialog box opens. On the left, under Layout Properties, select  Advanced . Select the  Enable Incremental Fetch  check box. From the  Fetch Level  drop-down list, select the object to be counted for the incremental fetch level. If the document or layout is grouped, the groups are displayed in the drop-down list. Groups that are displayed...
1 comment
Read more