Dashboard performance troubleshooting in MicroStrategy 10

Dashboard performance troubleshooting in MicroStrategy 10.x

Details of Document Execution Request, introduce the Performance Troubleshooting Cycle, and present links to other resources with detailed steps for troubleshooting specific components that may affect performance.

To begin, the architecture for dashboard execution is diagrammed here:

MicroStrategy Intelligence Server
When an end user makes a Document Execution Request through any client (a web browser via MicroStrategy Web, the MicroStrategy Desktop/Developer client, the MicroStrategy Mobile app, or the MicroStrategy Office client), the request is sent to the MicroStrategy Intelligence Server, which processes the request and prepares the response.

The MicroStrategy Intelligence Server will execute all children datasets on the dashboard by either generating SQL and running this against the data warehouse, or by fetching data from a cache. The Intelligence Server will first analyze all the templates, then determine which datasets the metrics are from, extend these datasets with runtime relations if necessary, aggregate the metric to desired level, join these datasets on the common attributes, and generate the final view.

After final result for the grid has been assembled, the Intelligence Server will retrieve the design structure for the dashboard either by querying the metadata for the dashboard definition. Subsequently, the Intelligence Server will then generate the appropriate response type for rendering the dashboard. For Office documents, dashboards executed in DHTML view modes, and Flash dashboards, the Intelligence Server will generate XML, and for PDF documents, the Intelligence Server will generate PDF. For Mobile dashboards, the Intelligence Server will generate a binary in addition to XML.

When the response generation is complete, the Intelligence Server will send the response to different components depending on which client the original Document Execution Request was initiated from. For requests made from a web browser via MicroStrategy Web, the MicroStrategy Mobile app, or the MicroStrategy Office client, the Intelligence Server will send the response to the MicroStrategy Web Server, the MicroStrategy Mobile Server, or the MicroStrategy Web Services Server respectively for further processing. For requests made from the MicroStrategy Desktop/Developer client, the Intelligence Server will send the response to the Desktop/Developer client directly without going through an additional server.

MicroStrategy Web Server
End users may render dashboards executed through MicroStrategy Web in DHTML view modes, Flash mode, or PDF. When the MicroStrategy Web Server receives the response from the Intelligence Server for one of these requests, it will transform the Intelligence Server response to the appropriate HTTP response suitable for displaying in the end user browser.

For dashboards executed in DHTML view modes, the Web Server will transform the Intelligence Server XML response to HTML.
For Flash dashboards, the Web Server will first upload a Flash dashboard viewer component to the end user browser (called DashboardViewer.swf, by default located here on the Web Server machine: C:\Program Files (x86)\MicroStrategy\Web ASPx\swf\DashboardViewer.swf), then forward the Intelligence Server XML response to the DashboardViewer in order for the end user browser to render.
For PDF documents, the Web Server will forward the Intelligence Server PDF response to the end user browser directly.

MicroStrategy Mobile and Web Services Servers
Dashboards executed through the MicroStrategy Mobile app must go through the MicroStrategy Mobile Server, and dashboards executed through the MicroStrategy Office Client must go through the MicroStrategy Web Services server.
For dashboards executed through Mobile, when the MicroStrategy Mobile Server receives the XML and binary response from the Intelligence Server, it will forward this response to the end user mobile client for further processing.

The Performance Troubleshooting Cycle

The above chart illustrates the Performance Troubleshooting Cycle. The goal of the cycle is to improve performance by identifying which components are acting as bottlenecks, then making the appropriate modifications to these components specifically, the environment as a whole, or the dashboard itself.

Monitoring

The first step of the cycle is to quantify the performance by measuring the time spent in each of the components that are part of a Document Execution Request, as described in the preceding sections. The table below summarizes a few key modules that commonly consume the most amount of time during a dashboard execution:

MicroStrategy Component	Key Module
Intelligence Server	Query Execution
	Data Preparation
	XML generation
Web Server	Web processes
	Network
Client	Client rendering

To measure the time spent in these components, refer to the following resources:

Intelligence Server and Client

Query Execution - Reference MicroStrategy Product Documentation > System Administration Guide > Chapter 5 for a complete list on how to monitor Job Execution and system usage

Data Preparation, XML Generation and Client rendering - KB30914: Overview of Profiling MicroStrategy Documents

Web Server

Web processes - KB10211: What are MicroStrategy Web statistics and how can they be activated?

Optimizing
After bottlenecks have been identified, certain component-level settings can be adjusted to optimize performance. However, since MicroStrategy deployments will typically rely on third-party components, several component-level settings may be outside the scope of MicroStrategy Technical Support but will nonetheless effect MicroStrategy performance. An example of this is an Intelligence Server may perform slowler on a machine with relatively fewer hardware resources (RAM, CPU speed, disk read/write speed, etc.) than a machine with more resources. As another example, an Intelligence Server may also perform slower on a machine with plentiful resources but many other processes running simultaneously in the background compared to a machine dedicated for just the Intelligence Server process. As a final example, an Intelligence Server and a Web Server may generate a document body quickly but a client browser may not be powerful enough to render this document in a short time period.
To adjust component-level settings specific to MicroStrategy, refer to the following resources:

Comments

Optimizing queries in Microstrategy using VLDB properties

Optimizing queries in Microstrategy using VLDB properties #vldb #vldbproperties The table b elow 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...

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

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

Internationalization Design Technics

Microstrategy Internationalization Design Technics MicroStrategy supports data internationalization through two different techniques. You can either provide translated data through the use of extra tables and columns, or you can provide separate databases to store your translated data. These techniques are described below: You can support data internationalization in your database by using separate tables and columns to store your translated data. You can use various combinations of tables and columns to support and identify the translated data in your database. To support displaying the name of each month in multiple languages, you can include the translated names in a separate column, one for each required language, within the same table. Each column can use a suffix to identify that the column contains translated data for a certain language. The same LU_MONTH_OF_YEAR table with translated data for the Spanish and German langua...

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

Custom Tooltips in Microstrategy developer and Web

Custom Tooltips in Microstrategy developer and Web The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: Macro Information Displayed {&TOOLTIP} All relevant labels and values associated with a graph item. {&GROUPLABEL} Name of the graph item's category. This value is often the graph item's attribute element information, as attributes are commonly used as the categories of graph reports. {&SERIESLABEL} Name of the graph item’s series. This value is often the graph item's metric name information, as metrics are commonly used as the series of graph reports. {&VALUE} The value of a given data point. {&XVALUE} The X-value of a data point. Only applicable to Bubble charts and Scatter plots. {&YVALUE} The Y-value of a data point. Only applicable to Bubble charts and Scatter plots. {&ZVALUE} The Z-value of a data point. Only applicable to Bubble charts and Scatter plots. {...

Control the display of null and zero metric values

Show Control the display of null and zero metric values in a grid report You can determine how to display or hide rows and columns in a grid report that consist only of null or zero metric values. You can have MicroStrategy hide the rows and columns in the following ways: Hide rows and columns that consist only of null metric values Hide rows and columns that consist only of zero metric values Hide rows and columns that consist only of null or zero metric values (default) Once you have defined how MicroStrategy hides null and zero metric values in the grid, you can quickly show or hide the grid using the Hide Nulls/Zeros option in the Data menu, as described below, or by clicking the Hide Nulls/Zeros icon in the Data toolbar. To determine how null and zero metric values are displayed or hidden in a grid report Open the report in Edit mode. From the Tools menu, select Report Options . The Report Options...

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...

MicroStrategy Developer Preferences options are expanded so big that some options are being cutoff. Show the hidden objects in the Microstrategy developer MicroStrategy Developer Preferences options are expanded so big that some options are being cut off. The steps below given in the MSTR article may not work. This can be simple handled by using the steps below: In the Microstrategy Developer go to Tools -> Preferences (Not my prefernces :) ) Under Developer category -> select Browsing on the browsing tab you see all the options like below: 3. Now using the mouse place the cursor on text box of 10000 which is next to 'Maximum number of monitoring objects displayed per page. 4. Then Hit Tab on Keyboard and hit another Tab on keyboard 5. Then press the space or down arrow on keyboard and click on OK or Enter. That will show the hidden objects in the Microstrategy developer Normal Version ...

Fact tables levels tables in Microstrategy explained

Fact tables levels in Microstrategy: Fact tables are used to store fact data. Fact tables should contain attribute Id's and fact values which are measurable. All the descriptive information about the fact tables should stored in Dimension tables either in Star Schema fashion or Snow Flake Schema fashion which is best suited to your reporting solution. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes using these attribute ID columns. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored. So the level of a fact table in the Fact_Item_Day_Customer can be the attribute Id's which is at Day, Item & Customer Id level. For example, fact tables containing sales and inventory data look like the tables shown in the following diagram: Base fact columns ver...

Microstrategy Analytics

Search This Blog