BI xPress Monitoring

BI xPress Report Monitoring Console

IconDescription
BI xPress Report Monitoring Console IconUse the Report Monitoring Console to monitor and troubleshoot your reports. The report console displays critical performance statistics and allows you to identify performance issues and the overall health of their report server. Statistics such as memory usage, data, and number of rows transferred are displayed and have programmable thresholds.

Feature Highlights

  • Displays a high level overview of a report server
  • Displays stats such as average runtime, average processing time, number of executions, amount of data transferred, and memory usage
  • Displays the percentage of runtime that is consumed by processing a report

Note:  Reporting Monitor Console is compatible with SSRS 2008 or later.

Using the Report Monitoring Console

1. Open the SentryOne Workbench, and select Report Monitoring Console. Select the Database Connection icon to open the Reporting Database Connector window.

2. Enter your server connection, instance, and database. Enter your authentication credentials, then select Connect to connect to the server.

BI xPress Report Monitoring Console Reporting Database Connector
BI xPress Report Monitoring Console example

Report Averages

BI xPress Report Monitoring Console Report Averages

The Report Averages panel displays the status of all executing reports present within your Reporting Services instance. Adjust the number of reports that display by using the Report Averages drop-down menu and then selecting the maximize report icon.

Column NameDescription
Report NameThe name of the report as defined in by the filename found on the reporting instance.
Last Report StatusIndicates the status of the most recent execution.
No. of ExecutionsThe number of times this report has executed, either successfully or unsuccessfully.
Avg. RuntimeThe average runtime of the report for all available executions.
Avg. Data Retrieval TimeThe average amount of time it took for the reporting server to retrieve data from the report specified data sources.
Avg. Processing TimeThe average amount of time it took for the reporting server to calculated and process the data retrieved from the data sources. Also, the amount of time it took to execute any formulas or expressions will be included in this value.
Avg. Rendering TimeThe average amount of time it took for the reporting server to draw the report including graphics, images, text, and charts for all executions available.
Avg. DataThe average size of all data that was transferred to the reporting server for all available executions.
Avg. RowsThe average number of rows that were retrieved by the reporting server for all available executions.
Avg. Max MemoryThe average value of the maximum memory consumption required by each report during execution.

Longest Running Reports on Average

BI xPress Report Monitoring Console Longest Running Reports on Average

Use the Longest Running Reports on Average panel to easily determine which reports have been executing the longest, and pinpoint bottlenecks within the Reporting Services instances.  Adjust the number of reports that display by using the Longest Running Reports on Average drop-down menu and then selecting the maximize report icon.

Frequent Reports

BI xPress Report Monitoring Console Frequent Reports

Use the Frequent Reports panel to easily determine which reports are executing the most often. Reports that execute more than expected can cause the entire Reporting Services instance to run slow, leading to performance and scalability issues. Adjust the number of reports that display by using the Frequent Reports drop-down menu and then selecting the maximize report icon.

Active Users

BI xPress Report Monitoring Console Active Users

The Active Users panel displays a list of all users that have connected to the report server and the number of times each user has executed reports. Adjust the number of users that display by using the Active Users drop-down menu and then selecting the maximize report icon.

BI xPress Monitoring Console

IconDescription
BI xPress Monitoring Console Icon

The BI xPress Monitoring Console allows you to monitor all of your SSIS packages that have the Auditing Framework in real time. Normally this information could only be obtained by debugging packages within the development environment.

Use the Monitoring Console to view predefined reports that display extraction and load trends, recent executions, any packages that may be currently running, package execution trends, execution dashboards, and execution alerts. The data in the reports can be filtered in a variety of ways such as: by date, package, machine, execution account, and execution mode.

Note:  The Monitoring Console can only monitor SSIS package executions that have the Auditing Framework applied to them or were imported using the SSIS Catalog Import feature.

Feature Highlights

  • Watch packages as they execute in real time
  • Access multiple reports to view numerous details about packages execution

The BI xPress Monitoring Console gives you many of the same details and real time monitoring that would be provided to you during debugging a package within BIDS, Visual Studio, or SQL Server Data Tools. Open the Monitoring Console by doing one of the following:

  • Open Visual Studio and then Select SentryOne > BI xPress > Monitoring Console from the toolbar
  • Open the SentryOne Workbench, and select Monitoring Console

After opening the Monitoring Console, you can start real-time monitoring or view historical reports from the main screen. The Monitoring Console is divided into six panels:

  • Option Toolbar
  • Package Execution
  • Execution Diagram
  • Errors and Warnings
  • Variables
  • Connection Panels

Option Toolbar

BI xPress Monitoring Console Options toolbar

The option toolbar allows you to manage your BI xPress database, and the monitoring console's behavior. The option toolbar manages both the database and display configurations within the execution panel. The following options are available:

OptionDescription
BI xPress DatabaseSpecifies the BI xPress database where the monitoring console connects.
Purge DatabaseOpens the Purge Database window, and gives you configuration options for purging the BI xPress Database. You can specify the start/end dates and the batch size in days. You can include optional purge filters for purging only specific executions by package name, machine name, or execution ID.  You can also specify a timeout in seconds before the purge operation errors out, with a value of zero indicating no timeout. Select Purge Records to execute a one time purge. Select Create Schedule to schedule the database purge and configure a SQL Agent job. Select Create SQL Agent Job to immediately create the job. Select View SQL Agent Job SQL Script to execute a DDL script that creates the job, and review the SQL Script used to create the SQL Agent job.
BI xPress Purge BI xPress Database window
BI xPress Purge BI xPress Database window populated
Package GroupingToggle between organizing the package execution panel by groups or by package.BI xPress Monitoring Console Package Grouping example
OptionsOpen the options window and adjust the behavior of the Monitoring Console. The following options are configurable:
OptionDescription
SSIS Monitoring Console OptionsAdjust the refresh rate, timeout, and max variable length options. The max variable length option can increase performance by reducing the amount of data returned from the BI xPress database. Note:  This setting only affects how much data is retrieved from the database. It does not affect the length of a variable value stored within the database. This setting
Execution RangeAdjust the parameters used by the Monitoring Console to retrieve executions.
Package and Group FilteringFilter executions based on a specific set of rules.
BI xPress SSIS Monitoring Console Options

Package Execution Panel

BI xPress Monitoring Console Execution Panel
BI xPress Monitoring Console Execution Panel Context Menu

The monitoring console's package execution panel displays a listed summary of the most recent executions within the BI xPress Database, and allows you to control the execution that displays. There are four main functions within the Package Execution panel: execution name, execution summary, execution selection, and execution management.

OptionsDescription
Execution NameLocated in the upper left hand corner of each execution, this displays the name of the package that was executed.
Execution SummaryEach execution displays a visual and textual summary for the specific execution:
Visual or textual summaryDescription
StatusThe background color of each execution visually describes the current status for the package. Red - Package completed with at least one error Green - Package completed successfully without errors Yellow - Package is still running Blue - Package has been stopped during execution
Date and RuntimeThe date located on the left of the execution describes when this execution occurred. The time located on the right represents how long the package executed before it completed.
Execution SelectionThe package execution panel controls the execution that displays within the other Execution Diagram. Up to 16 packages can be selected to be displayed within the Execution Diagrams at a time.
Execution ManagementRight click a package to open the context menu. You can use these options to manually stop or delete executions within the BI xPress database.

Note:  Manually stop a package that's still running (packages highlighted in yellow) by right -clicking the package and then selecting Mark Selected execution(s) as stopped from the context menu. 

Delete packages that are no longer running (whether because they have been stopped, completed successfully, or completed with errors) by selecting them, right-clicking, and then selecting Delete selected execution(s).

Important:  Marking an execution as stopped does not physically stop the execution, it only marks the entry within the BI xPress Database as being stopped. If the package is still running, it will continue to run.

Execution Diagram

BI xPress Monitoring Console Execution Diagram

The monitoring console's Execution Diagram is the main display that's used to monitor package execution. The Execution Diagram displays a visual representation of the control flow, and data flow of a selected package. The execution diagram panel is divided up into three sections: Header, toolbar, and diagram.

Header

BI xPress Monitoring Console Execution Diagram Header

The Header displays a summary of the currently selected execution. The following details are displayed:

DetailDescription
 Execution NameThe name of the execution. Select the X to close the execution and free up space to view your other selected executions.
 User and Machine InformationDisplays the user account and machine that executed the package.
Date and RuntimeDetails the start time and how long the execution ran before it completed.
StatusThe background color of the header describes the current status for the package: Red - Package completed with at least one error Green - Package completed without errors Yellow - Package is still running Blue - Package was manually stopped before execution completed

Toolbar

BI xPress Monitoring Console Execution Diagram toolbar

The toolbar controls what's currently displayed within the diagram. The following options are available:

OptionDescription
Control FlowSwitches the diagram view to display the current execution's control flow execution. The diagram displays the control flow execution by default.
Data FlowSwitches the diagram to display the current execution's data flow executions. The data flow button is only enabled if the execution contains at least one data flow execution. The diagram displays the first data flow execution by default. Use the data flow selector to select a different data flow execution.
Data Flow SelectorSelect the data flow execution you want to display within the diagram. The selector is only enabled when you are viewing the data flow executions.
Auto Layout ButtonToggles between using the layout present within the package. If enabled, BI xPress detects out of date or missing layouts and attempts to generate a more accurate diagram. If disabled, BI xPress displays the provided package layout, regardless of whether it is out of date or missing.
Auto Generated Diagram labelThis label notifies you that the layout information is out of date or missing. This label only displays if you have Auto Layouts enabled.

Diagram

Displays the execution details for the control or data flow. By default, the execution diagram displays the control flow.

Note:  When you select a package execution that's been imported using the SSIS Catalog Import feature for the first time, the monitoring console attempts to download the package layout. The monitoring console then displays the package in a familar way for SSIS developers.

The Execution Diagram has the following navigation buttons in the upper right of the diagram:

ButtonDescription
BI xPress Monitoring Console Zoom out diagram buttonZooms out to display more of the execution diagram.
BI xPress Monitoring Console Reset Zoom diagram buttonResets the zoom to the default zoom level.
BI xPress Monitoring Console Zoom In diagram buttonZooms in to show less of the execution diagram.
BI xPress Monitoring Console Best Fit diagram buttonFits the current control or data flow diagram to the window.

Errors and Warnings

BI xPress Monitoring Console Errors and Warnings panel

The Errors and Warnings panel provides details of all the errors and warnings thrown by the currently selected package, which makes it easier to troubleshoot problem packages.

Note:  You can filter out the messages displayed within the Errors and Warnings panel to show either all messages, only errors, or only warnings.

Variables

BI xPress Monitoring Console Variables panel

The Variables panel displays a time line of all changes to user variables within the currently selected package. Enable Variable Change Tracking to switch between displaying the latest value for each variable, or displaying a historical list of all variable changes that took place during the execution of the currently selected package.

Connections

BI xPress Monitoring Console Connections panel

The Connections panel provides details about all of present connection managers within the currently selected package.

BI xPress Monitoring Console Dashboard Reports

The following Dashboard Reports are available within the BI xPress Monitoring Console:

Best Practices Analysis Results

BI xPress Monitoring Console Best Practices Analysis Results

This dashboard shows the total violations, errors, performance violations, warnings, and informational messages for your best practices rules. The following information is provided:

  • Total Violations
  • Errors
  • Performance
  • Warnings
  • Information
  • Latest N analysis (where N is user specified number)

Execution Dashboard

BI xPress Monitoring Console Execution Dashboard Report Parameters
BI xPress Monitoring Console Execution Dashboard Last 60 Days

This dashboard gives you one place to view the most common counters of SSIS package execution including: top 20 slow packages, fast packages, minimum used packages, maximum used packages, recent data extracts, recent data loads. The following information is provided:

  • Running packages (Last N days/weeks/months)
  • Completed packages (Last N days/weeks/months)
  • Top 20 slowest packages (Last 90 days)
  • Top 20 fastest packages (Last 90 days)
  • Top 20 minimum used packages (Last 90 days)
  • Top 20 maximum used packages (Last 90 days)
  • Data extract summary (Last N days/weeks/months)
  • Data load summary (Last N days/weeks/months) 

Note:  Where Last "N" days/weeks/months can be changed by specifying interval parameter.

When you open the execution dashboard a report for the last 60 days displays by default. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference. 

Extract / Load Trend

BI xPress Monitoring Console Extract/Load Trend

The Extract/Load Trend displays a graphical view of extract and load trends over several years, weeks, months and days. The following information is provided:

  • Extracted and Loaded record count trend for selected time frame
  • Multiple level of row count trend analysis for Package, Data Flow, and source/target
  • Component Type, Object Name, Package Name and many other Data Flow related information

Note:  The default view is 60 days. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference.

Extract / Load Detail

BI xPress Monitoring Console Extract/Load Detail

Extract/Load Detail displays the extract / load detail data for every Data Flow within each package execution. When you first open the Extract/Load Detail page you will be prompted to specify a date range. After specifying your parameters, select View Report to display the report. The following information is provided:

  • All Data Flow summary for packages executed in selected time frame
  • Start time and end time of package and Data Flow tasks
  • Source and target detail of each Data Flow which includes SQL query, server or file name, database name and user name

Package Performance

BI xPress Monitoring Console Package Performance

Package Performance displays a package level performance graph within a specified interval. The following information is provided:

  • Average runtime of package for selected time frame
  • Total package executions for the period of the specified time frame
  • Performance indicators detailing the amount of executions that fall within an acceptable range of the average runtime
  • Performance history graph of packages which are executed for selected time frame
  • Preview performance trend graph

Note:  The default view displays the last 60 days. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference. You can also select to Export or print the report.

Package & Task Performance

BI xPress Monitoring Console Package and Task Performance Version
BI xPress Monitoring Console Package and Task Performance hidden parameters

Package & Task Performance displays the package and task level performance graphs within a specified interval. The following information is provided:

  • Average runtime of package for selected time frame
  • Performance indicators detailing the amount of executions that fall within an acceptable range of the average runtime
  • Preview performance trend graph
  • Performance history graph of packages and task which are executed for the selected time frame
  • Performance history graph of Data Flow components
  • Total package executions for the period of the specified time frame

Note:  The default view displays the last 60 days. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference. You can also select to Export or print the report.

Package Alerts

BI xPress Monitoring Console Package Alerts

Package Alerts displays a detailed error / warning report including package level errors. The following information is provided:

  • All packages and task with errors or warnings
  • Start time and end time of package and task
  • Errors/warnings details for package execution between the selected time frame

Note:  Select a date range to view. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference.

Package Execution Trend

BI xPress Monitoring Console Package Execution Trend

Package Execution Trend displays useful information about the trend of package runtime, task runtime and errors / warnings. The following information is provided:

  • Package performance trend in graphical and tabular form
  • Package ad task performance break down for N numbers of days/weeks for the selected time frame
  • Error/warning trend analysis in graphical form
  • Execution detail in tabular format which includes package name, run time, error/warning count and row count

Note:  The default view displays the last 60 days. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference. The left of the report displays a list of all selected packages in the report.

Recent Excecutions Summary 

BI xPress Monitoring Console Recent Execution Summary

Recent Executions Summary displays information regarding the execution status of packages. The following information is provided:

  • Package runtime
  • Error/warning
  • Extracted/loaded rows
  • User account under which package was executed
  • Machine name
  • Interactive or non-interactive mode
  • Variable values on execution completion

Note:  Specify a date range for the report. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference.

Recent Executions Details

BI xPress Monitoring Console Recent Execution Details

Recent Execution Details displays information about the execution status of packages and tasks. The following information is provided:

  • Package and task runtime
  • Error/warning displayed under related task
  • Extracted/loaded rows (applicable to Data Flow)
  • User account under which package was executed
  • Machine name
  • Interactive or Non-interactive mode
  • Variable values on execution completion

Note:  Specify a date range for the report. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference.

Runtime Standard Deviation

BI xPress Monitoring Console Runtime Standard Deviation

Runtime Standard Deviation displays packages that are running slower or faster than their usual trend. The following information is provided:

  • Packages running slower or faster than configured standard deviation limit
  • Performance history graph for last N executions
  • Average execution time
  • Standard deviation of execution time
  • Extracted and loaded row count

Note:  Specify a date range for the report. Select the arrow to the right of the View Report button to open the Report Parameters section. Use the Report Parameters section to change the report parameters to your preference.