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

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

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

Microstrategy "Error type: Odbc error. Odbc operation attempted

 "Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’.  Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...
4 comments
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...
2 comments
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

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...
Post a Comment
Read more

Super Cubes in MicroStrategy 2019

Super Cubes in MicroStrategy 2019 Beginning in MicroStrategy 11.0 and 2019, users will notice objects referred to as " Super Cubes ".  Super Cubes are simply a renaming of what was previously referred to as MTDI (Multi Table Data Import) Cubes. Note that only the naming convention has changed, as the functionality remains the same . This name change is visible in MicroStrategy Workstation and MicroStrategy Web. In MicroStrategy Workstation, right click on any cube created via Data Import and select Properties . The object Type is listed as Super Cube: This can also be seen when editing or authoring a dossier. In your Datasets panel, hover over the name of your dataset. The tooltip will show the dataset type listed as a Super Cube.
Post a Comment
Read more

Microstrategy Custom group and consolidation comparison

Custom group and consolidation comparison Both consolidations and custom groups provide flexibility in reports, but the objects differ in their structure and use. The essential distinction is that consolidations work with attributes and custom groups use filters. Consolidations are groupings of attribute elements while custom groups are based on filter criteria. Custom groups are used to apply different filters to different rows of a report. Consolidations are used to create virtual attributes to allow reporting on attributes that do not exist in the data model. Finally, row level math can be performed with consolidations but not with custom groups. Custom groups are more flexible than consolidations because you do not have to know much about your data to create filters for a custom group. In contrast, consolidations require that you know exactly which attribute elements to select when creating the consolidation. To continue with the examples from the previous sect...
Post a Comment
Read more

Execute Integrity manager test from Command line

Execute Integrity manager test from Command line  MSTR Integrity Manager allows the user to execute a test without having to load the GUI, or to schedule a test to run later at specific times or dates. Go over using Windows AT command at: https://support.microsoft.com/en-us/help/313565/how-to-use-the-at-command-to-schedule-tasks https://www.lifewire.com/at-command-2618090 Here are the prerequisites to execute a test from the command line: Create a test and saved using the Integrity Manager graphical interface. Make sure that the users has the ' Use Integrity Manager ' privilege for that project(provided by the administrator) and the ' Execute ' permission for the reports to be tested. Keep in mind that MicroStrategy Integrity Manager can only test three-tier projects, i.e., projects which are connected to a MicroStrategy i- Server. Projects in Direct Connection (two-tier) mode cannot be tested with this tool...
1 comment
Read more

Multi-Select Drop Down Selector with Apply Button

Multi-Select Drop Down Selector with  OK Button  When creating a Report Services Document, you may want to create a drop down selector to save space, but also would like the drop down to be a multi-selector with OK/Apply button. Below are instructions to achieve this in a Report Services Document.  Steps to Create: 1. Right click on your drop down selector and choose Properties and Formatting 2. Navigate to the Layout Tab 3. In the layout tab, click "Allow multiple selections". It will  not  indicate that the check box is enabled, but this step is mandatory.  3. Next, navigate to the Theme tab. Choose the  L ight Theme  from the drop down menu and click the Apply button. 4) Next, change the Theme back to  None  and click Apply. 4. When you return to the Layout tab, you will see the checkbox for  Allow multiple selections  is now checked.  
Post a Comment
Read more