30 Junho 2017

Orchestrator Report

Este artigo irá ajudá-lo, através de um report, a analisar os processos de Business Intelligence que foram executados: quanto tempo demoraram, quais deram erro, quais é que ainda não foram executados, o aumento ou diminuição de tempos face às últimas execuções e as suas dependências.

O software utilizado foi o Power BI que permite desenvolver visualizações interativas através da criação de dashboards e reports.

Para o correto funcionamento do report é necessário efetuar as seguintes ações no servidor que se pretende analisar:

> Criação de 2 tabelas de orquestração: dbo.SSIS_PACKAGE_CONFIGURATIONS e dbo. SSIS_PACKAGE_GROUP_DEPENDENCIES com a listagem de packages e respetivas dependências;

> Criação de 2 tabelas de auditoria: audit. PROJECT e audit. PROJECT_JOBS com a listagem dos jobs;

> Criação de 2 tabelas de logging: dbo.AGENT_BIJobHistory e dbo.LOG_ERRORS com a listagem de execuções de jobs e de erros;

> Utilização da tabela dbo.sysssislog;

> Criação de 6 views que são a fonte de dados do report (estas views têm como base as tabelas enumeradas anteriormente);

> Criação do Job UPDATE_DW_LOGGING_BI_BIJOBS que atualiza a tabela dbo.AGENT_BIJobHistory de 10 em 10 minutos com a informação de execução de Jobs obtida através das tabelas de sistema: msdb.dbo.sysjobs, msdb.dbo.sysjobhistory, msdb.dbo.sysjobsteps e msdb.dbo.sysjobactivity

Na figura 1 podemos analisar a primeira página do report contendo os Jobs que foram executados, os que deram erro ou foram cancelados, os que ainda estão a correr e os que ainda não foram executados. Podemos também ver a duração de cada job.

De realçar que, para o DialGauge apresentar corretamente o número total de Jobs, foi necessário criar uma métrica em DAX que calcula o número de jobs existentes independentemente dos filtros que estão a ser aplicados com exceção de um possível filtro ao JobName:

AllDistinctCountJobName =

calculate (DISTINCTCOUNT (AGENT_BIJobHistory_AGG [JobName]);

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

)

Do lado direito da figura conseguimos saber a duração de cada Job que terminou com sucesso através do Treemap e saber quanto tempo demorou cada step de cada Job através do ScrollingTextVisual.


Figura 1 – Today’s Job Report

Na figura 2 podemos analisar os Packages que tiveram uma duração de execução fora do normal em relação às execuções passadas. Estas diferenças poderão estar relacionadas com alterações nos Packages, problemas no servidor, situações de deadlocks, etc.

Os packages que surgem nesta análise têm de ter uma média de duração dos últimos 3 dias superior a 1 minuto (excluindo Packages com erro) e terão de ter uma variação superior a 50% em relação à média de tempo dos dois últimos dias. Como tal, é necessário criar a seguinte métrica em DAX:

Variation_between_Today_and_Last_2_Days =

IF (SYSSSISLOG_DURATION[Duration] > 0;

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

0)

O amarelo indica a duração de hoje, o azul indica a duração média dos últimos dois dias, o traço indica a duração de ontem e a cruz indica a duração de dois dias antes.

Esta página está a ser filtrada pela última execução de cada Package. A sua fonte é a tabela dbo.sysssislog que permite ver todas as execuções de packages.


Figura 2 – Today’s Package Report

A página presente na figura 3 permite-nos analisar o número de erros que foram escritos na tabela LOG_ERRORS. Por norma esta tabela é carregada quando estamos a carregar factuais com Ids inexistentes nas dimensões. Esta tabela permite-nos ver os respetivos códigos e perceber a razão pela qual os Ids não existem. O visual Sunburt permite-nos analisar o número de erros por modelo e o KPI em baixo à esquerda permite-nos verificar que hoje ocorreram 91K erros ao passo que ontem ocorreram menos 7% de erros em relação a hoje. Para este KPI foram criadas as seguintes métricas em DAX:

TodayNrOfErrors =

Calculate (sum (LOG_ERRORS_AGG[NrOfErrors]);

FILTER (LOG_ERRORS_AGG; LOG_ERRORS_AGG [Flag_Today_Errors] = "YES")

)

YesterdayNrOfErrors =

Calculate (sum (LOG_ERRORS_AGG[NrOfErrors]);

FILTER (LOG_ERRORS_AGG; LOG_ERRORS_AGG [Flag_Today_Errors] = "NO")

)


Figura 3 – Today’s LOG_ERRORS Report

Na figura 4 temos a categorização dos Packages dos grupos permitindo-nos analisar quais são os grupos que têm uma duração mais elevada e como tal, mais impacto no tempo que um Job demora a ser executado.

Esta página está a filtrar os Packages pela sua última execução do dia e tem como fonte as tabelas dbo.sysssislog e dbo.SSIS_PACKAGE_CONFIGURATIONS. Esta última é uma tabela que deverão adicionar à vossa base de dados com a listagem de todos os Packages que são executados, categorizados por grupo.


Figura 4 – Today’s Package Group Report

A figura 5 permite-nos uma análise diferente das páginas anteriores. Aqui podemos analisar, através de um SankeyDiagram, as dependências existentes entre os grupos de Packages. Ao selecionar um grupo pode ver quais os grupos que têm de ser executados antes e quais os grupos que vão ser executados depois.

Esta página tem como fonte a tabela dbo.SSIS_PACKAGE_CONFIGURATIONS e a tabela dbo.SSIS_PACKAGE_GROUP_DEPENDENCIES que deverão adicionar à vossa base de dados com todas as dependências que têm entre grupos de packages.


Figura 5 – Package Group Hierarchies

Na figura 6, com a visualização Sparkline, podemos analisar a duração dos Jobs nos 2 últimos meses e mês atual.


Figura 6 – Today’s Package Group Report

Foi também efetuada uma versão mobile bastante simples. O intuito desta versão é conseguirmos saber em que estado se encontram os nossos jobs (como se pode verificar na figura 7):

Figura 7 – Mobile Version

 

 

 

Nuno

      Nuno Sousa
       Consultant
Blog