30 June 2017

Orchestrator Report

This article will help you, through a report, to analyse the Business Intelligence processes that were executed: how long they took, which ones finished with error, which ones have not still been executed, the duration’s increase or decrease comparing to the last executions and its dependencies.

The software that was used was Power BI, which allows us to develop interactive visualizations through the creation of dashboards and reports.

In order for the report to work correctly, it is necessary to take the following actions on the server you intend to analyse:

> Creation of 2 Orchestration tables: dbo.SSIS_PACKAGE_CONFIGURATIONS and dbo. SSIS_PACKAGE_GROUP_DEPENDENCIES with the packages list and its dependencies;

> Creation of 2 audit tables: audit. PROJECT and audit. PROJECT_JOBS with the jobs list;

> Creation of 2 logging tables: dbo.AGENT_BIJobHistory and dbo.LOG_ERRORS with the executions list and errors;

> Use of the dbo.sysssislog table;

> Creation of 6 views that are the source of the report (these views have the enumerated tables as source);

> Creation of the UPDATE_DW_LOGGING_BI_BIJOBS job which refreshes the dbo.AGENT_BIJobHistory table every 10 minutes with executions data obtained through the following system tables: msdb.dbo.sysjobs, msdb.dbo.sysjobhistory, msdb.dbo.sysjobsteps and msdb.dbo.sysjobactivity

In figure 1, we can analyse the report’s first page containing the jobs that were executed, which ones finished with error or were cancelled, which ones are still running and which ones haven’t still been executed. We can also see each job’s duration.

In order for the DialGauge to present correctly the job’s total number, it was necessary to create a DAX measure that calculates the number of existing jobs independently of the applied filters except JobName filter:

AllDistinctCountJobName =

calculate (DISTINCTCOUNT (AGENT_BIJobHistory_AGG [JobName]);

ALLEXCEPT (AGENT_BIJobHistory_AGG; AGENT_BIJobHistory_AGG [JobName]; AGENT_BIJobHistory_AGG [Flag_Today])


On the figure’s right side, through the Treemap, we see each job’s duration (successful jobs) and know how much time each step took (by watching the ScrollingTextVisual).

Figure 1 – Today’s Job Report

In figure 2, we can see the packages that had an unusual execution duration comparing to previous executions. These differences can be related to package changes, server problems, deadlocks, etc.

The packages that appear on this page need to have an average duration of the last 3 days bigger that 1 minute (excluding packages with errors) and at least a 50% variation comparing to the average of the last two days. As so, it is necessary to create the following DAX measure:

Variation_between_Today_and_Last_2_Days =


ABS (1 - (SYSSSISLOG_DURATION [Duration_Avg_2_Last_Days] / SYSSSISLOG_DURATION[Duration]));


Yellow indicates today’s duration, blue indicates the average duration of the last two days, the line indicates yesterday’s duration and the cross indicates before yesterday’s duration.

This page is being filtered by the last execution of each package. Its source is dbo.Sysssislog table, which allows us to see all package executions.

Figure 2 – Today’s Package Report

Figure 3 allows us to analyse the number of errors that were written on LOG_ERRORS table. Usually this table is loaded when we are executing fact tables with unknown dimension Ids. This table allows us to see the codes and understand the reason why these Ids do not exist. Sunburst visual gives us the number of errors by model and the KPI located at the left bottom allows us to see that today we had 91K unknown Ids while yesterday we had 7% less comparing to today. For the KPI, the following DAX measures were created:

TodayNrOfErrors =

Calculate (sum (LOG_ERRORS_AGG[NrOfErrors]);



YesterdayNrOfErrors =

Calculate (sum (LOG_ERRORS_AGG[NrOfErrors]);



Figure 3 – Today’s LOG_ERRORS Report

In figure 4, we have group package categorization allowing us to see which groups have the most impact on each job’s duration.

This page is filtering packages by its last day’s execution and has as source dbo.sysssislog and dbo.SSIS_PACKAGE_CONFIGURATIONS table. This last one is a table that you should add to your database with all the packages that are going to be executed, categorized by group.

Figure 4 – Today’s Package Group Report

Figure 5 shows a different perspective from the previous pages. Here we can analyse, through SankeyDiagram’s, the existing dependencies between package groups. By selecting a group, we can see the groups that must be executed before and the groups that will be executed next.

This page has 2 tables as source dbo.SSIS_PACKAGE_CONFIGURATIONS and dbo.SSIS_PACKAGE_GROUP_DEPENDENCIES. You should add them to your database with all the packages and group dependencies they have.

Figure 5 – Package Group Hierarchies

In figure 6, thanks to the Sparkline visualization, we can analyse the Jobs Duration for the last 2 months plus the current month.

Figure 6 – Today’s Package Group Report

We also did a simple mobile version. The purpose of this version is for us to be able to know in which state our jobs are (as you can see in figure 7):

Figure 7 – Mobile Version





      Nuno Sousa