Skip to main content

MSTR VLDB SQL optimization tips

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

Types of filters in Microstrategy

Types of filters in Microstrategy Below are the types of filters: 1. Attribute qualification filter These types of qualifications restrict data related to attributes on the report. a) Attribute form qualification Filters data related to a business attribute’s form(s), such as ID or description. •  For example, the attribute Customer has the forms ID, First Name, Last Name, Address, and Birth Date. An attribute form qualification might filter on the form Last Name, the operator Begins With, and the letter H. The results show a list of customers whose last names start with the letter H. b) Attribute element list qualification Filters data related to a business attribute’s elements, such as New York, Washington, and San Francisco, which are elements of the attribute City. • For example, the attribute Customer has the elements John Smith, Jane Doe, William Hill, and so on. An attribute element list qualification can filter data to display only those customer...

Microstrategy Dossiers explained

Microstrategy  Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has  evolved dashboards to the point that they are more than dashboards - they are  interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so  the've  renamed VI dashboards to  ‘ dossiers ’.  Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers   With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...

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

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

Update the data on an Intelligent Cube without having to republish the entire cube in MicroStrategy

Update the data on an Intelligent Cube without having to republish the entire cube in MicroStrategy MicroStrategy has introduced a feature known as, Incremental Refresh Options, which allow Intelligent Cubes to be updated based on one or more attributes, by setting up incremental refresh settings to update the Intelligent Cube with only new data. This can reduce the time and system resources necessary to update the Intelligent Cube periodically versus a full republish. For example, if a user has an Intelligent Cube that contains weekly sales data, the user may want this Intelligent Cube to be updated at the end of every week with the sales data for that week. By setting up incremental refresh settings, he can make it so that only data for one week is added to the Intelligent Cube, without affecting the existing data and without having to reload all existing data. Users can select t...

Apply or Pass-through functions in Microstrategy

Ap ply (Pass-Through) functions MSTR Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various RDBMS systems.. Syntax common to Apply functions Apply Function Name   ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN) where: Apply Function Name  – is a generic name used for the predefined pass-through functions described above expression with placeholders  – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy. Arg  – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on. There are   five  pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, cli...

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

exact string when searching for elements in an element prompt in MicroStrategy

When a user types in keywords to tries to find element names in an element prompt, the search returns all objects containing the keywords in MicroStrategy Developer 9.4.x-10.x. However, the user would like to search for the exact phrase. It is suggested to use quotes to get exact phrase when there is a space between. Like "Black Panther" Using the MicroStrategy Tutorial Project as an example, a user wishes to search for an item named Minolta Maxxum Camera. The search results for Minolta Maxxum Camera return all items containing any or all of those words, as shown below: CAUSE: This occurs due to the search defaulting to 'ORing' the search terms. This means that any or all keywords that match the strings will be returned. The SQL for this search is shown below: SELECT ITEM_NAME FROM LU_ITEM WHERE (ITEM_NAME LIKE '%Minolta%' OR ITEM_NAME LIKE '%Maxxum%' OR ITEM_NAME LIKE '%Camera%') ACTION: To match an exact string, use...

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