Microstrategy Performance Tuning

-
We have different options to make MSTR to write a better query and again it is depends on your business logic.

Here I am giving few useful and handy options which improves MSTR query and its performance.

Few VLDB settings which are helpful to write better query with respect to different scenarios:

  1. Select/Insert --> Attribute Form Selection --> change to select ID and other forms -this might prevent some of the lookup table joins if all information are already in the temp tables. Might not work if you have outer join settings in final pass 
  2. Select/Insert --> Attribute Selection Option --> change to select other attributes in join tree - this might again diminish the amount of joins in the final pass
  3. Query Optimization --> Additional Final Pass --> change to have one additional pass for lkp tables only - this might split your final join and make it easier for SQL server
  4. Lookup Table Join Order --> change to "Fully ..." - this might change the order to something more suitable
Another factor which is really important in terms of performance is query execution plan. Some cases, MSTR query would be good but still query performance is very bad due to query execution plan.

In this case, you have analyze the execution plan carefully and have to tweak using below options

1) Adjust any of the above VLDB settings as per the analysis
2) If necessary, use some SQL hint and we have VLDB options to implement that.

Example:
------------ 
In one scenario, MSTR SQL is perfectly alright and still your last pass query is taking huge time.

We have done a different analysis like, 

  1. Change SQL join type to SQL 89 - This gives very good performance
  2. Changed Join order in the final pass, again it works

Then we took our SQL execution plan and analyzed then we came to know, with some cost or stats, execution plan is taking wrong join order and it leads to number of cross joins in the background.

Then we used a SQL hint to force the join order - Option (force order) and query ran in seconds.

Comments

Post a Comment

Popular posts from this blog

Intelligent cubes in Microstrategy

-
Intelligent Cubes: Topics: Intelligent cubes Intelligent cubes Vs Cache Manage and Monitoring cubes Cube Size and Memory usage Accessing Intelligent cubes Dynamic sourcing Intelligent cube & Security filters Intelligent cubes & Multi-tenant Architecture Intelligent cubes & Level Metrics Intelligent cube is an OLAP feature introduced in Microstrategy 9 version.  Intelligent cube is a pre aggregated set of data stored in intelligent server memory and it will give very quick access to the data without hitting the data warehouse. It is very simple to create an intelligent cube, it is same like a report creation. When we create and publish intelligent cube, it stores all the data in intelligent server memory as well as a copy in file system (secondary storage area -Hard disk). Whenever it reboots, it loads the data to memory from the file ·           Stores data in intelligent server mem...
Read more

Useful Metadata Queries - Microstrategy

-
Object ID in MSTR 9.2: Object id in MSTR 9.2 metadata tables has been stored as Unique identifier. So we cannot use Metadata object id in MSTR Desktop and vice versa. But we have a built in functions to convert this. MSTR Metadata Object ID to Desktop Object ID: dbo.fn_UniqueidentifierToCharMsTR () MSTR Desktop Object ID to MSTR Metadata Object ID dbo.fn_CharToUniqueidentifier() Metadata query to get user and group details Excluding Everyone group: select GROUPNAME . object_name [Customer_group] , USERNAME . object_name [User_name] from dbo . DSSMDOBJINFO GROUPNAME       join             ( select object_name , OBJDEPN . depn_objid from dbo . DSSMDOBJINFO b                     join             ...
Read more

Microstrategy OLAP - Dynamic Aggregation

-
Dynamic Aggregation: When you create a report, MSTR SQL engine writes a SQL in a report level and calculates all the metric values added in the report. After report creation and execution, you have two different options in all objects added in the report. a) Remove from report b) Remove from grid  - Dynamic aggregation Remove From Report: Right Click on any attribute and give "Remove from report" then MSTR SQL engine will rewrite the SQL in the new level and calculate all the values Remove from Grid: Right Click on any attribute and give "Remove from grid" (Still that object present in the report object and only removed from the grid) - In this case, No changes in the SQL. MSTR Analytical engine will calculate all metric aggregation based on the new level in the grid  - This is called DYNAMIC AGGREGATION   In simple terms, MSTR analytical engine dynamically aggregates and calculates all metric values when you remove any attribute from the grid How...
Read more