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

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

"Table structure cannot read or update" in the warehouse catalog error while updating a table structure in Microstrategy

"Table structure cannot read or update" in the warehouse catalog error while updating a table structure while using Warehouse Catalog in MicroStrategy Developer This issue could be due to the incorrect prefix. To fix the issue. 1. Open the Warehouse Catalog. 2. Select the table and assign the correct Prefix update the structure. 3. Save and close the warehouse catalog.

Administration standards to optimize network data transfer speeds

Optimization standards to help developers work with the System Administrator to optimize network data transfer speeds: Place all server components in the same segment to minimize latency between the Intelligence Server, Web server, data warehouse server, and metadata server Expand bandwidth and minimize latency between servers and clients to ensure that bottlenecks do not occur when data is requested over your network Use HTTP compression between the Web servers and Web clients to achieve maximum throughput between these components Configure a Web proxy server to handle caching and reduce the load on the Web server

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report Apart from using the VLDB properties to create the left outer join, article describes how to use the Logical View to specify an outer join between two attribute lookup tables when only attributes are on a report. This method exists as attribute only outer joins will not be generated on their own by the MicroStrategy SQL engine. This is because they are only necessary with r agged/unbalanced hierarchies which are not supported as null attribute IDs are not supported (parent elements with no child elements or child elements with no parents). Brief instructions are provided using the example below. Consider, two attributes: Parent01 and Child01 have a parent-child relationship. Their Lookup tables are defined, as follows Parent01 Child01 Note that although there are 4 ID values for the attribute Parent01, there is no defined relationship ...

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

Customers Who Live in the Same City as Call Centers

Customers Who Live in the Same City as Call Centers Your new utility company has call centers located throughout the country, and your recent surveys indicate that customers who live in the same city as a call center are particularly satisfied with service due to extremely rapid repairs during power outages. To begin your new advertising campaign, you want to generate a list of Call Centers that coincide with Customer Cities. The following steps create an attribute-to-attribute qualification filter that generates the list of desired cities. To Create an Attribute-To-Attribute Qualification that Compares the Call Center and Customer City Attributes In MicroStrategy Web, log in to a project. Open any folder page (for example, click Shared Folders on the home page). Click the Create Filter icon . From the Object browser on the left, select the Customer City attribute from the Customers hierarchy and drag it to the filter pane on the right. Change Qualify...

Order of tables appear in the SQL generated by the MicroStrategy SQL Generation Engine

Order of tables appear in the SQL generated by the MicroStrategy SQL Generation Engine By default, the MicroStrategy SQL Generation Engine orders the tables in the following way( FMRL ): Fact Tables Metric Qualification (MQ) Tables Relationship Tables Lookup Tables

HyperIntelligence Training Videos

HyperIntelligence Training Videos Design and build hyper cards Optimizing Datasets for HyperIntelligence Using the HyperIntelligence for Office Outlook Add-In Building HyperIntelligence Cards Using HyperIntelligence for Mobile on Android Deploying HyperIntelligence for Outlook Insights On-The-Go: HyperIntelligence for Mobile Building HyperIntelligence Profile Cards Designing Custom HyperIntelligence Cards Using the Calendar with HyperIntelligence for Mobile

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

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

Microstrategy Analytics

Search This Blog