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

URL API connection mode parameters

URL API connection mode parameters When using the URL API, to denote which type of Authentication to use, developers can use the connMode parameter.  The following values are used for this parameter: Standard = 1 LDAP Authentication = 16 Database Authentication = 32 Guest = 8 Windows Authentication = 2 Trusted Authentication = 64 Integrated Authentication = 128
Post a Comment
Read more

Level metrics in Microstartegy/MSTR

Level Metrics Level metrics allows the users to choose the right combination of level target, filtering, and grouping (referred to as elements) to achieve your desired results.  The elements of a metric level are described below: • Target : The target is the attribute level at which the metric calculation groups. For a more detailed description, see  Target: The context of a calculation . • Grouping : Grouping determines how the metric aggregates. For a more detailed description, see  Grouping: How aggregation is performed . • Filtering : Filtering governs how the report filter interacts with the metric calculation. For a more detailed description, see  Filtering: Interaction with report filters . The level is indicated between the curly braces ({ }) in the metric definition shown below: Sum(Revenue) {~, Product} The tilde (~) represents the report level with standard filtering, denoted by the plus sign (+). If you add item as a level, th...
Post a Comment
Read more

Create a transaction services photo uploader

Create a transaction services photo uploader   1.  Create a new table "photo_upload" in Tutorial warehouse database (the default location: C:\Program Files\MicroStrategy\Tutorial Reporting\TUTORIAL_DATA_7200.mdb), as shown below:    2. The 'photo_upload' table has to be pre-populated with *exactly* 10 rows of data, the values for the 'ID' column should be 1-10 and the values for the 'uploaded' column should all be 0 3.  In MicroStrategy Desktop, create a freeform report "R1" based on the new table "photo_upload" in Tutorial data created at step 1, as shown below:   SELECT Location, Description, ID, uploaded, numbers FROM PHOTO_UPLOAD 4.  Create another table for transaction insert SQL. Make sure to create an 'autonumber' type ID as primary key for this table, or auto_increment ID for different DBs.                     5. Create...
Post a Comment
Read more

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

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

Configure Connection Mapping in Microstrategy

Configure Connection Mapping in Microstrategy The following steps demonstrate the second scenario where two different data warehouses are used within the same project: Create two different database connections -                                                                                        One that points to the data warehouse for the European users                                                                 and the other that points to the data warehouse for USA users as shown below: Select Europe as the default database connection for the database Instance as seen below: Go to P...
Post a Comment
Read more

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

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

Prompt-in-prompt (Nested prompt) feature in a FreeForm SQL Report

Prompt-in-prompt (Nested prompt) feature in a FreeForm SQL Report In some business scenarios, it is required to implement prompt-in-prompt (nested prompt) feature to use the answer of one prompt to qualify on the elements of another prompt in Freefrom SQL reports in MicroStrategy Developer 9.4.x-10.x. The following procedure describes how to achieve prompt-in-prompt in a Freeform SQL report in MicroStrategy Tutorial project: Create a new filter, select Add an Attribute qualification and choose the highest level attribute Country. Make sure to Qualify On: Elements. Click Prompt as the image shown below. Accept all the default values without any changes in the popped up window after clicking on "Prompt" as shown below. Save the filter as "Country Filter". Click "Prompt", choose "Use a filter to reduce the number of elements" option and select Country Filter created in last step, as shown below. Save the filter as "Region Filter...
1 comment
Read more