Skip to main content

Optimizing queries in Microstrategy using VLDB properties

Optimizing queries in  Microstrategy using VLDB properties

#vldb #vldbproperties

The table below summarizes the Query Optimizations VLDB properties. Additional details about each property, including examples where necessary, are provided in the sections following the table.

Property Description Possible Values Default Value
Additional Final Pass Option
Determines whether the Engine calculates an aggregation function and a join in a single pass or in separate passes in the SQL.
• (default) Final pass CAN do aggregation and join lookup tables in one pass
• One additional final pass only to join lookup tables
Final pass CAN do aggregation and join lookup tables in one pass
Apply Filter Options
Indicates during which pass the report filter is applied.
• Apply filter only to passes touching warehouse tables
• Apply filter to passes touching warehouse tables and last join pass, if it does a downward join from the temp table level to the template level
• Apply filter to passes touching warehouse tables and last join pass
Apply filter only to passes touching warehouse tables
Attribute Element Number Count Method
Controls how the total number of rows are calculated for incremental fetch.
• Use Count(Attribute@ID) to calculate total element number (uses count distinct if necessary)
• Use ODBC cursor to calculate total element number
Use Count(Attribute@ID) to calculate total element number (uses count distinct if necessary)
For Tandem databases, the default is Use ODBC Cursor.
Count Distinct with Partitions
Determines how distinct counts of values are retrieved from partitioned tables.
• Do not select distinct elements for each partition
• Select distinct elements for each partition
Do not select distinct elements for each partition
Custom Group Banding Count Method
Helps optimize custom group banding when using the Count Banding method. You can choose to use the standard method that uses the Analytical Engine or database-specific syntax, or you can choose to use case statements or temp tables.
• Treat banding as normal calculation
• Use standard case statement syntax
• Insert band range to database and join with metric value
Treat banding as normal calculation
Custom Group Banding Points Method
Helps optimize custom group banding when using the Points Banding method. You can choose to use the standard method that uses the Analytical Engine or database-specific syntax, or you can choose to use case statements or temp tables.
• Treat banding as normal calculation
• Use standard case statement syntax
• Insert band range to database and join with metric value
Treat banding as normal calculation
Custom Group Banding Size Method
Helps optimize custom group banding when using the Size Banding method. You can choose to use the standard method that uses the Analytical Engine or database-specific syntax, or you can choose to use case statements or temp tables.
• Treat banding as normal calculation
• Use standard case statement syntax
• Insert band range to database and join with metric value
Treat banding as normal calculation
Data population for Intelligent Cubes
Defines if and how Intelligent Cube data is normalized to save memory resources.
• Do not normalize Intelligent Cube data
• Normalize Intelligent Cube data in Intelligence Server
• Normalize Intelligent Cube data in database using Intermediate Table Type
• Normalize Intelligent Cube data in database using Fallback Type
• Normalize Intelligent Cube data basing on dimensions with attribute lookup filtering
• Normalize Intelligent Cube data basing on dimensions with no attribute lookup filtering
Normalize Intelligent Cube data in Intelligence Server
Data population for reports
Defines if and how report data is normalized to save memory resources.
• Do not normalize report data
• Normalize report data in Intelligence Server
• Normalize report data in database using Intermediate Table Type
• Normalize report data in database using Fallback Table Type
• Normalize report data basing on dimensions with attribute lookup filtering
Do not normalize report data
Default Sort Behavior for Attribute Elements in Reports
Determines whether the sort order of attribute elements on reports considers special sort order formatting defined for attributes.
• Sort attribute elements based on the attribute ID form for each attribute
• Sort attribute elements based on the defined 'Report Sort' setting of all attribute forms for each attribute
Sort attribute elements based on the attribute ID form for each attribute
Dimensionality Model
Determines level (dimension) replacement for non parent-child related attributes in the same hierarchy.
• Use relational model
• Use dimensional model
Use relational model
Engine Attribute Role Options
Enable or disable the Analytical Engine's ability to treat attributes defined on the same column with the same expression as attribute roles.
• Enable Engine Attribute Role feature
• Disable Engine Attribute Role feature
Disable Engine Attribute Role feature
Filter tree optimization for metric qualifications
Determines if metric qualifications that are included in separate passes of SQL are included in a single pass of SQL when possible.
• Enable Filter tree optimization for metric qualifications
• Disable Filter tree optimization for metric qualifications
Enable Filter tree optimization for metric qualifications
Incremental Data Transfer
Determines whether data that is transferred between Intelligence Server and a database is performed using a single transfer of data or multiple, incremental transfers of data
• Enable Incremental Data Transfer
• Disable Incremental Data Transfer
Disable Incremental Data Transfer
Maximum Parallel Queries Per Report
Determines how many queries can be executed in parallel as part of parallel query execution support
User-defined
2
MD Partition Prequery Option
Allows you to choose how to handle prequerying the metadata partition.
• Use count(*) in prequery
• Use constant in prequery
Use count(*) in prequery
Multiple data source support
Defines which technique to use to support multiple data sources in a project.
• Use MultiSource Option to access multiple data sources
• Use database gateway support to access multiple data sources
Use MultiSource Option to access multiple data source
OLAP function support
Defines whether OLAP functions support backwards compatibility or reflect enhancements to OLAP function logic.
• Preserve backwards compatibility with 8.1.x and earlier
• Recommended with 9.0 and later
Preserve backwards compatibility with 8.1.x and earlier
Parallel Query Execution
Determines whether MicroStrategy attempts to execute multiple queries in parallel to return report results faster and publish Intelligent Cubes.
• Disable parallel query execution
• Enable parallel query execution for multiple data source reports only
• Enable parallel query execution for all reports that support it
Disable parallel query execution
Parallel Query Execution Improvement Estimate in SQL View
Determines whether reports and Intelligent Cubes include an estimate in the percent of processing time that would be saved if parallel Query execution was used to run multiple queries in parallel.
• Disable parallel query execution improvement estimate in SQL view
• Enable parallel query execution improvement estimate in SQL view
Disable parallel query execution improvement estimate in SQL view
Rank Method if DB Ranking Not Used
Determines how calculation ranking is performed.
• Use ODBC ranking (MSTR 6 method)
• Analytical engine performs rank
Use ODBC ranking (MSTR 6 method).
Remove Aggregation Method
Determines whether to keep or remove aggregations in SQL queries executed from MicroStrategy.
• Remove aggregation according to key of FROM clause
• Remove aggregation according to key of fact tables (old behavior)
Remove aggregation according to key of FROM clause
Remove Group By Option
Determines whether Group By and aggregations are used for attributes with the same primary key.
• Remove aggregation and Group By when Select level is identical to From level
• Remove aggregation and Group By when Select level contains all attribute(s) in From level
Remove aggregation and Group By when Select level is identical to From level
Remove Repeated Tables For Outer Joins
Determines whether an optimization for outer join processing is enabled or disabled.
• Disable optimization to remove repeated tables in full outer join and left outer join passes
• Enable optimization to remove repeated tables in full outer join and left outer join passes
Enable optimization to remove repeated tables in full outer join and left outer join passes
Set Operator Optimization
Allows you to use set operators in sub queries to combine multiple filter qualifications. Set operators are only supported by certain database platforms and with certain sub query types.
• Disable Set Operator Optimization
• Enable Set Operator Optimization (if supported by database and [Sub Query Type])
Disable Set Operator Optimization
SQL Global Optimization
Determines the level by which SQL queries in reports are optimized.
• Level 0: No optimization
• Level 1: Remove Unused and Duplicate Passes
• Level 2: Level 1 + Merge Passes with Different SELECT
• Level 3: Level 2 + Merge Passes, which only hit DB Tables, with different WHERE
• Level 4: Level 2 + Merge All Passes with Different WHERE
Level 4: Level 2 + Merge All Passes with Different WHERE
Sub Query Type
Allows you to determine the type of subquery used in engine-generated SQL.
• WHERE EXISTS (SELECT * ...)
• WHERE EXISTS (SELECT col1, col2...)
• WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT * ...) for multiple columns IN
• WHERE (COL1, COL2...) IN (SELECT s1.COL1, s1.COL2...)
• Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery
• WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT col1, col2 ...) for multiple columns IN
• Use Temporary Table, falling back to IN (SELECT COL) for correlated subquery
Use Temporary Table, falling back to EXISTS (SELECT *...) for correlated subquery
Transformation Formula Optimization
Defines whether to attempt to improve performance of reports that use expression-based transformations.
• Always join with transformation table to perform transformation
• Use transformation formula instead of join with transformation table when possible
Use transformation formula instead of join with transformation table when possible
Unrelated Filter Options
Determines whether the Analytical Engine should keep or remove the unrelated filter.
• Remove unrelated filter
• Keep unrelated filter
• Keep unrelated filter and put condition from unrelated attributes in one subquery group
Remove unrelated filter
Unrelated Filter Options for Nested Metrics
Determines whether the Analytical Engine should keep or remove the unrelated filters when using nested metrics.
• Use the 8.1.x behavior:
• Use the 9.0.x behavior:
Use the 8.1.x behavior
WHERE Clause Driving Table
Determines the table used for qualifications in the WHERE clause.
• Use lookup table
• Use fact table
Use fact table







Labels:

Comments

Post a Comment

Popular posts from this blog

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

Metric values are repeated across rows when a report is executed in MicroStrategy

Metric values are repeated across rows when a report is executed in MicroStrategy When comparing report results between DB Query Tool and MicroStrategy, some reports show repeated metric values in MicroStrategy where there were none in DB Query Tool. To illustrate the issue, a fact table CAT_ITEM_SLS has been added into the MicroStrategy Tutorial project and populated with a small set of three rows. CAT_ID ITEM_ID REVENUE  1 1  10   1 2  20  2  2  30  Report results in DB Query Tool: Report results in MicroStrategy: In MicroStrategy, the row for "Art As Experience" in the Spring 2007 catalog repeats the $20 value from the Winter 2007 catalog, where DB Query Tool shows the $30 value from the fact table. CAUSE The discrepancy occurs because the attribute elements for Catalog and Item are in a many-to-many relationship, but the attribute relationship in the MicroStrategy schema is defined incorrectly w...
1 comment
Read more

Documents with derived attributes cannot be edited within MicroStrategy Developer 10.x

Documents with derived attributes cannot be edited within MicroStrategy Developer 10.x When trying to edit a document created in MicroStrategy Web using MicroStrategy Developer 10.x, the 'Edit' button is found greyed out and inaccessible as per the screenshot below. When navigating to the same document in MicroStrategy Web 10.x, it is possible to edit the document. STEPS TO REPRODUCE: Create a document in MicroStrategy Developer or Web 10.x with any number of attributes. See below: Notice in Developer, you can currently right click on this document and select the 'Edit' option. Open up the same document in MicroStrategy Web 10.x and add a derived attribute, right clicking an attribute and selecting Insert new attribute. Save the report in MicroStrategy Web 10.x with the same name. Open MicroStrategy Developer 10.x, and right click the document, you will notice that the Edit option is greyed out as seen below: Navigate to the same document wi...
1 comment
Read more

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

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

Retrieve a list of user groups and the associated users in MicroStrategy Developer 9.x / 10.x

Retrieve a list of user groups and the associated users in MicroStrategy Developer Follow the steps below to create a list of all groups and the users in each group: In MicroStrategy Developer 9.x, select 'Project Documentation' from the Tools menu to start the wizard. Select any project that is in the project source that contains the users and groups and click Next. Select only Configuration Objects for documentation. Uncheck the 'Basic Properties' object category from the next screen, as shown below: Then select only 'User Group' under the Configuration Objects section and only 'Groups' and 'Members' under the Definition section, as shown below: Go through the rest of the wizard, and open the resulting documentation. After navigating down to the User Groups, the documentation should look similar to the following image: This page shows every group, any child groups, and all members of each group.
Post a Comment
Read more

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

Personalizing file locations, email and file subscriptions using macros in Microstrategy

Personalizing file locations MSTr allows to dynamically specify the  File Location  and  Backup File Location  in a file device using macros.  For example, if you specify the  File Location  as  C:\Reports\{&RecipientName}\ ,  all subscriptions using that file device are delivered to subfolders of  C:\Reports\ . Subscribed reports or documents for each recipient are delivered to a subfolder with that recipient’s name, such as  C:\Reports\Jane Smith\  or  C:\Reports\Hiro Protagonist\ . The table below lists the macros that can be used in the  File Location  and  Backup File Location  fields in a file device: Description Macro Date on which the subscription is sent {&Date} Time at which the subscription is sent {&Time} Name of the recipient {&RecipientName} User ID (32-character GUID) of the recipient {&RecipientID} Distribution Services add...
Post a Comment
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