OLAP Performance Advisor’s server monitor displays the current activity on your server. Also CPU Time, Reads/Writes, Cache hits and memory usage graphs, visual representation of your active connections are available.

Also OLAP Performance Advisor efficiently extracts meaningful information from a Microsoft Analysis Services trace files or tables. It analyzes traces and collects data for all Analysis Services objects (cubes, dimensions, measure groups, etc.) and many other performance statistics.

Tracing is the process of capturing data based on selected events, data columns, and filters. OLAP Performance Advisor captures these events to determine the efficiency of the database performance. The results can be saved as a trace file or trace table for further analysis. Also you can analyze trace files or table recorded by standard Microsoft SQL Profiler.

Many OLAP Performance Advisor features are not available when a trace file is used, including the ability to generate an analysis report. It is recommended to save the trace file as a trace table to access these features.

Monitoring server activity

Add your server using Add Server button in the Server Explorer. Start server monitoring by selecting Monitoring node:

New tab with server’s real-time statistics is opened:

Depending on your Analysis Server version (2005 or 2008), you can see [CPU Time + connections activity] or [CPU Time + Cache hits/misses + Reads/Writes + Memory usage + connections activity] diagrams:

You can pause and continue monitoring any time. Also you can display graphs using local and GMT time (see Options dialog).

Activity diagram allows you to monitor connections and session’s activity. Move the mouse over a SPID to get more information about the selected process:

Managing servers and traces

Server Explorer allows you to manage all your SSAS servers and server traces:

Add your server in the Server Explorer using Add Server button. Under the server node, you can find Monitoring command (opens server monitor described above) and the list of server traces grouped by record time.

When you start new trace, it’s added under server node in the Server Explorer so you can open and analyze it any time later.

You can specify SQL Server database where all new traces will be stored (Trace Repository). You can manage which trace tables should be presented in the Server Explorer using Browse trace repository dialog.

Recording new traces

Press New trace button on the tool bar or server’s Start new trace context menu to start new trace:

Start new trace dialog is opened:

Define how you want to store your trace: as database table or TRC file, select trace events if necessary (press Trace options).

Press Start trace to start trace recording. New trace is opened in the OLAP Performance Advisor. It displays recording statistics:

Trace scheduling

By default, traces are recorded while OLAP Performance Advisor is running. But using special Windows service, you can record traces even when the application is closed. Press Trace options button and select Scheduling tab:

Press Start now button to start the Windows service. After that uncheck “Stop trace recording on application exit“option (available when the service is running). Now the trace will be recorded even if the application is closed. BITraceRecorderService is part of OLAP Performance Advisor installation and it’s managed on Recording view in Options | Settings dialog box:

Also it’s possible to control trace start/end time or desired trace size (trace events) using Scheduling tab:

Opening trace files

Use the following instructions to open an external trace file:

In the File menu, select File | Open | Trace File menu or “Open Trace File” toolbar button:

In the Open dialog box, locate the trace file. Select the trace file and then click Open.

To analyze the trace, convert the trace file into a trace table using “Save trace as table” toolbar button:

You can also use File | Save as | Trace table menu item. In the Connect to SQL Server dialog window, enter the server name or choose the server from the dropdown list. Choose the authentication mode then click OK. If you choose SQL Server authentication, enter the Login Name and Password. In the Source Table dialog window, select the database and enter the table name:

Opening trace tables

In the File menu, select Open | Trace Table or click Open Trace Table toolbar button:

In the Connect to SQL Server dialog window, enter the server name or choose the server from the dropdown list:

Choose the authentication mode then click OK.

If you choose SQL Server authentication, enter the Login Name and Password specified during SQL Server installation. In the Source Table dialog window, select the database and the trace table you want to analyze:

Trace requirements

OLAP Performance Advisor uses trace columns not included in the default SQL Profiler template. For best results, use OLAPPerformanceAdvisor template included in the installation package. If you cannot import this template in your environment, make sure you include EventClass, EventSubClass, TextData, SPID, ObjectPath, ObjectType columns in your trace. You are able to analyze a trace without these columns, but OLAP Performance Advisor notifies you about limited results when you open such trace:

Trace Analysis

Connected and disconnected trace analysis modes

OLAP Performance Advisor analyzes information containing in traces. But to correctly parse information stored in traces, it’s necessary to know the structure of SSAS objects in question. Specifically for Analysis Services trace, it’s necessary to know the structure of cubes and dimensions.

OLAP Performance Advisor allows you to analyze traces in two modes:

ü Connected mode: OLAP Performance Advisor is connected to Analysis Services server (usually it’s the same server where the trace was recorded) and loads SSAS objects definitions for complete analysis. Specifically, cubes and dimensions are loaded to provide complete statistics for cubes, measure groups, partitions, dimensions, attributes. Traces recorded in OLAP Performance Advisor are analyzed in connected mode automatically.

ü Disconnected mode: OLAP Performance Advisor works without Analysis Services connection. In this case you are still able to load SSAS objects definitions from Analysis Services files (XMLA files). For example, you can generate XMLA files for cubes and dimensions, load these files in OLAP Performance Advisor and perform trace analysis without connecting to server. Even if you do not load XMLA files in this mode, OLAP Performance Advisor performs trace analysis but with limitations.

We always recommend using connected mode whenever it’s possible. In this case all objects will be analyzed correctly and you do not need to load XMLA files manually.

Trace analysis in connected mode

Open trace table. When you open it, the following dialog allows you to connect to Analysis Services immediately after opening your trace^{^[1]}:

Click Connect to Analysis Services button and specify the Analysis Services server name, username and password if necessary:

Click OK.

Currently connected server is displayed on the Analysis Services connection toolbar:

Using this toolbar, you can change connected server any time later (see Connect to Analysis Services button).

Note that if the trace was recorded on different server or SSAS objects have been changed since trace recording time, the statistics may be incorrect or limited.

Start trace analysis after connecting to the server (see section Performing trace analysis).

Trace analysis in disconnected mode

Open your trace table. On the first run, the following dialog allows you to load XMLA files[2]:

Click Load Analysis Services (XMLA) files button and select load XMLA files for cubes and dimensions you want to get complete statistics for. Loaded cubes and dimensions are shown in Cubes/Dimensions list on the Analysis Services Connection toolbar:

XMLA files are usually generated programmatically or from SQL Server Management Studio by choosing Script as | CREATE To | File context menu item for the appropriate cube or dimension:

Start trace analysis after loading XMLA files (see section Performing trace analysis).

Opening our demo trace in disconnected mode

Our sample demotrace (included in the installation package) is based on the standard Microsoft Adventure Works DW Standard Edition SSAS database. After saving it as a table, you could either connect to Analysis Services to load Adventure Works objects or use XMLA files generated using Adventure Works. For the convenience, we provide XMLA files in the installation package as well so you do not need to connect to Analysis Services to try OLAP Performance Advisor.

To load Adventure Works XMLA files:

- Save demotrace as trace table (see section Opening trace files);

- After opening trace table, load XMLA from the following location:
<Your installation directory>\Samples\XMLA for demotrace.
Usually it’s c:\Program Files\Lakeside SQL\OLAP Performance Advisor\Samples\XMLA for demotrace.
You can load many XMLA files simultaneously:

After loading XMLA files, make sure Analysis Services connection toolbar shows that you have 2 loaded cubes and 15 dimensions:

Then perform trace analysis (see below).

Performing trace analysis

You can start trace analysis using Start Trace Analysis tool button:

or Analysis | Start Trace Analysis menu item.

After completing analysis, Analysis Report and Activity Graph views contain analysis data presented in tables and diagrams.

Working with analysis results

Analysis Report View

After completing analysis, the Analysis Report view is activated:

where:

1 – Tree of SSAS objects, aggregations, measurements and other data which OLAP Performance Advisor collects. Click on the appropriate node to display statistics on the right pane.

2 – For nodes representing lists of objects (say, Cubes, Dimensions etc), this grid view shows comparative data for objects.

3 – Distribution chart for selected object executions (both process and queries).

4 – Analysis warnings.

Objects tree

For SSAS objects (Cubes and Dimensions), if all Analysis Services objects are loaded either from server or XMLA files, the complete statistics for Cubes, Measure Groups, Partitions, Dimensions and Attributes are displayed. Choose the appropriate node to display statistics for the appropriate object.

Also available:

Query Patterns – the list of query patterns presented in the trace. OLAP Performance Advisor shows separate statistics for MDX, DMX and SQL queries:

You can easily detect which queries was executed and when.

Commands – statistics for executed commands (XMLA Batches):

Aggregations – subtotals for specified trace columns:

Grids and diagrams

The grids contain calculated measurements (subtotals) for all Analysis Services objects, commands, etc.

Distribution diagram (see Distribution tab) below shows how these calls are distributed in time.

Executions list (see Executions tab) shows exact executions(ocurrencies) of the specified call/command in the trace. Using Goto raw data grid command, you can navigate to Data And Filters tab to given trace event:

Analysis Warnings

Analysis Warnings window is displayed if analysis is completed with warnings. Specifically, OLAP Performance Advisor shows such messages if required cube or other Analysis Services object definition not found in connected Analysis Services server or loaded XMLA files. You can navigate to original row where this error occurs or copy message to the clipboard if you want to contact our support.

Trace Analysis Reports

OLAP Performance Advisor allows you to store generated analysis report in separate XML file for further analysis and comparison without access to analyzed trace table and Analysis Services server.

To save Analysis Report XML: Analyze your trace and select File | Export | Export Trace Analysis Report:

Report files are saved as XML files with extension olaptrcreport. To open these files later in OLAP Performance Advisor, use File | Open | Trace File.. menu item and select Analysis Report files filter to browse report files:

Opened trace report contains Anlaysis Report information without diagrams:

Advisory pane

OLAP Performance Advisor also contains Advisory pane which presents set of recommendations based on current analysis results and server information. These suggestions are based on our own and Microsoft® TechNet articles and Best Practices.

How to find my bottlenecks?

Sort data grid by measurement column

Find worst object(s) by sorting Analysis Report grids by CPU Time or Duration:

Find worst executions by sorting data grid on Data And Filters tab:

Compare Analysis Objects performance

Use Analysis Report grids to find most CPU/Time consuming cubes, measure groups, etc. Sort by CPUTime, Duration, subtotal, average or percentage:

Analyze Analysis Services query and/or process performance

Set Analysis | Trace Analysis Properties | Analyze Query Events and Analysis | Trace Analysis Properties | Analyze Process Events options and analyze Analysis Services querying and/or processing accordingly:

Compare statistics before and after optimizations

For example, change your partitions for a measure group and compare measurements (process/query totals and averages) for partitions before and after optimization.

Storage Engine/Formula Engine bottlenecks

If a significant percentage (30% by default, can be changed – see below) of the total time spent by Analysis Services resolving the poorly performing MDX query is spent in the storage engine, there is a storage engine bottleneck. Similar, if a significant percentage (30% by default, can be changed – see below) of the total time spent by Analysis Services resolving the MDX query is spent by the formula engine, there is a formula engine bottleneck.

Analyze the trace, open Query Patterns | MDX view on analysis report:

OLAP Performance Advisor calculates Storage Engine duration for each MDX query when it’s possible. Based on these values, statistics for Storage Engine and Formula engine is collected for each MDX query pattern (see (1) - Storage Engine Duration, Formula Engine Duration columns).

For each pattern, number of critical Storage Engine calls and Formula Engine calls (potential bottlenecks) are displayed (2). Individual query executions with Storage Engine statistics (time spent in Storage/Formula Engines, % from total duration for given MDX execution) is displayed on Executions tab. Potential bottlenecks are highlighted with color and tooltip (3).

You can change the percentage values used to determine critical Storage Engine/Formula Engine calls using Analysis | Trace Analysis Properties dialog:

Default values are 30% according to the Microsoft’s[3] guideline.

You can disable this analysis for short queries (“Do not analyze if Duration is less than” option).

Detected Storage Engine and Formula Engine bottlenecks are shown under MDX node:

[1] This dialog is not displayed if your trace is recorded for a server presented in the Server Explorer. You can disable this dialog by setting “Never show this dialog” flag on. You can also choose “Continue without loading objects“. In this case you can connect to Analysis Services or load XMLA files later.

[2] You can disable this dialog by setting “Never show this dialog” flag on.

You can also choose “Continue without loading objects“. In this case you can connect to Analysis Services or load XMLA files later.

[3] See the SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services. Link: http://www.microsoft.com/downloads/details.aspx?FamilyId=975C5BB2-8207-4B4E-BE7C-06AC86E24C13&displaylang=en