Balanced Scorecard & BI

A teoria do Balanced Scorecard por Norton e Kaplan, ou BSC para os íntimos, teve um impacto significativo no mundo do BI. Talvez, aliás, seja essa a raiz da mistura corrente entre OI e BI. Eu estava lá quando isso estava acontecendo, e vou dividir com vocês um pouco das minhas histórias sobre aqueles dias loucos.

O Mundo Até Então

Até meados do ano 2000, o top em BI de usuário era um DSS conhecido pelo acrônimo EIS, de Executive Information System. O EIS era composto por um DW e uma ferramenta OLAP, organizados de tal forma que um executivo poderia enxergar a empresa inteira a partir do topo, da maior agregação, e descer, seguindo o caminho que desejasse, até o nível mais baixo, da linha.

Assim, por definição, todo executivo ganhava o seu centro de controle ou painel de monitoração empresarial. O apelo do sistema é que ele dispensava o usuário de conhecer programação de qualquer tipo, pois um EIS era voltado para o nível gerencial, o nível tático-estrégico da organização.

Ao redor do EIS haviam uma gama de opções, entre produtos e soluções.

Por exemplo, o usuário poderia pedir um gerador de relatórios (um produto), para construir as listagens que bem entendesse, e quando desejasse. Ganhou alguma notoriedade, nesta época, o slogan “entregar o dado certo, no momento certo, para a pessoa certa”.

E haviam as soluções de BI (e ainda hão), que são pacotes fechados com um propósito definido. Soluções como CRM (gerenciamento da interação com cliente), Churn Detection (detecção e prevenção de atrito, como em call centers) ou Credit Scoring (concessão de crédito automatizado), eram desenvolvidas com uso de Data Mining sobre os dados das empresas.

E não esqueçamos dos DWs, projetos sempre complicados e difíceis, e quase sempre operando aos trancos e barrancos. A tecnologia de DWs passou por três momentos de inflexão: a criação de bancos de dados em dispositivos de acesso aleatório, por volta de 1960, foi o primeiro. DASDs habilitaram a existência de Bancos de Dados Relacionais, sem os quais não seria possível construir um serviço viável de exploração de dados – antes usavam-se fitas magnéticas, e qualquer coisa mais complexa que uma listagem agregada era um transtorno. Depois veio o Modelo Dimensional, no início dos anos 90, que resolveu a vida dos usuários ao montar os dados de uma forma inteligível, apta à exploração por analistas de negócios. Ainda faltava resolver o acúmulo de dados, que seguia também aos trancos e barrancos, atendido parcialmente por Modelagem Dimensional. O advento do Data Vault, já na década 2000, resolveu essa parte. Hoje em dia, DWs são problemas só para quem está desatualizado desde 2003.

Enquanto Isso, Na Sala de Monitoramento…

Quando alguém decide que quer gerenciar uma empresa quando crescer, esse alguém faz uma faculdade de Administração de Empresas. Dentro dessa especialidade existe um sem-número de técnicas e teorias voltadas a entender como uma empresa funciona e como tomar decisões para que ela cresca.

Dentro de Administração de Empresas existem subconjuntos de conhecimento que tratam do monitoramento de uma organização, bem como o planejamento estratégico dela. O termo “balanced scorecard” surgido durante a década de 90 refere-se, de maneira geral, a uma técnica de monitoramento de rendimento (performance) que usa indicadores financeiros e não-financeiros. Essa técnica acompanha a execução das atividades por grupos de profissionais e as consequências dessas atividades. Por algumas coincidências, e pelo movimento do mercado, criou-se a percepção que Robert Kaplan e David Norton criaram o conceito, o que não é verdade, pois a técnica já existia antes. Eles apenas desenvolveram um sistema de gerenciamento estratégico que usa a idéia de um balanced scorecard como pino central.

Balanced Scorecard

A premissa de um Balanced Scorecard é que uma empresa pode monitorar a execução de suas estratégias acompanhando certos indicadores-chaves. O Balanced Scorecard do Norton e Kaplan é uma formalização desse conceito em uma metodologia que, automatizada com auxílio da Informática, cristalizou-se em uma solução chamada Strategic Management System (SMS), ou Sistema de Gerenciamento Estratégico.

Essa metodologia está explicada no livro deles, o The Balanced Scorecard: Translating Strategy into Action. Em tese, qualquer um pode implementar o sistema em sua empresa, a partir deste livro. Eu consegui encontrar uma companhia, ESM, que vende tal sistema, aparentemente uma encarnação oficial das teorias da dupla. Eis alguns screenshots dele:

Exemplo de painel ESM.
Exemplo de painel ESM.

 

Exemplo de mapa estratégico ESM.
Exemplo de mapa estratégico ESM.

O grande truque, que deu fama e fortuna aos autores, é saber que parâmetros monitorar e entender como esses parâmetros surgem das métricas geradas pela empresa, como essas métricas se ligam aos objetivos estratégicos.

Exceto por toda essa teoria, um SMS é, nada mais, nada menos que uma coleção de painéis de instrumentos – os famigerados dashboards – com dados coletados dos sistemas da empresa.

So The Story Goes…

O livro deles saiu em 1996, mas eles já vinham fazendo sucesso com essas idéias desde 1992, quando saiu o primeiro paper sobre o tópico.

O mercado por SMS estava aquecido devido ao sucesso da dupla e suas idéias. Implementações desses conceitos começaram a surgir, e a face mais visível desses sitemas era… o dashboard! Mas não acabava aí, não! Os dados que eram apresentados nesses painéis vinham, em geral, dos sistemas informatizados da empresa.

  • BSC é voltado para administração empresarial;;
  • BI também;
  • BSC usa dados integrados;
  • DW, uma subseção de BI, integra dados da organização;
  • BSC apresenta dados em visualizações bacanas;
  • BI também.
  • Etc.

Entenderam o caminho que a coisa tomou? Até parece que BSC e BI estão ligados intimamente, mas o fato é que nem de longe BSC é uma solução de BI!

  • BSC foca em dados correntes e suas relações determinadas a priori, em monitorar os dados da empresa para aquilatar o consistência do planejamento estratégico e sua execução. Os dados são atualizados muito frequentemente (um dia ou menos) e, em boa parte das vezes, apenas a variação dos indicadores é acompanhada, não dos dados mais granulares. O histórico dos dados em si não é capturado, e portanto não é usado;
  • BI é voltado para acumular dados históricos e analisá-los para descobrir suas relações a posteriori, em busca de entender o negócio. Dizemos que os dados são usados para tentar modelar, matematicamente, o funcionamento da companhia. Fala-se em previsão, em estimativas, em correlações previamente ignordas entre as variáveis etc. etc. etc.

Não são coisas estranhas entre si, pois ambos usam dados gerados em sistemas informatizados (BSC menos, BI mais), mas não são a mesma coisa, ou sequer parentes próximos.

Mesmo assim formou-se, no mercado de BI, a percepção de que BSC faz parte de BI.

Ok, vamos relegar essa parte e focar na questão “empresas vendendo BSC, o pão quente do momento”. Imagine o que aconteceu: empresas de BSC, como a tal da ESM mencionada acima, tinham lá o seu produto, que era uma novidade então.

E as empresas que vendiam BI, tinham o quê para mostrar como sendo BSC? Coleta de dados e apresentação de dados – e a teoria do BSC em um livro! Quando vendedores de BSC mostravam seu produto, a aparência, a parte visual era importante. Quando os fornecedores de BI seguiam essa trilha, o que mais aparecia para os clientes eram… Os dashboards!!

Conclusão

Não posso afirmar, categoricamente, que a tecnologia de painéis de instrumentos foi incorporada ao BI por “culpa” do BSC. Mas eu posso contar para vocês que ao visitar clientes interessados em BSC, eu, gerente de soluções SAS, vendedor de BI, era cobrado para mostrar os painéis da solução SAS para BSC. Era contra esse aspecto que a solução oferecida pelo SAS era comparada.

Eu nunca consegui emplacar uma venda de BSC pelo SAS. O produto de BSC da concorrência (não-BI) era bom o bastante para anular as vantagens do SAS (integração de dados díspares e flexibilidade nos painéis) e conseguir o pedido, mesmo sendo mais caro. Na minha opinião, olhando para trás, eu diria que a concorrência tinha uma implementação formal de BSC, completa e pronta, enquanto que o SAS apenas tentava surfar nessa onda, reempacotando seu produto com outro público-alvo diferente do público regular de BI.

Só que, até então, painéis não eram coisas de BI. Caramba, não tinha nem no catálogo do SAS, que tem tudo!

Por isso, na minha humilde opinião, dashboards entraram para o rol de recursos de BI por contaminação do mercado de SMS, do qual o BSC Norton e Kaplan é um ilustre membro. Dashboards não são melhor ferramenta analítica que um cubo OLAP ou um projeto de Data Mining, mas são excelentes meios para levar informação e oferecer visões completas – para apresentação dos dados.

É isso. Até a próxima. ;-)

Anúncios

Analisando os Logs do PDI – Parte 3

No primeiro post da série vimos como configurar a captura de logs nos processos do PDI, e obter informações básicas sobre os resultados de um processo qualquer.

No segundo post eu mostrei como usar os dados de performance de uma transformação para “caçar” gargalos.

Na terceira e última parte vamos entender como usar as tabelas logging channel para montar um relatório que lista todas as transformações e jobs executados a partir de um job-pai.

Cenário

Eis um exemplo básico de ETL que carrega um Data Warehouse dimensional:

Job principal de refresh do DW Beltrano.
Job principal de refresh do DW Beltrano.

O job acima chama uma transformação que, como o nome diz, define as variáveis do processo, e depois segue chamando os subjobs, cada um com uma tarefa específica: atualizar dimensões, carregar fatos e por fim preencher as tabelas pré-agregadas para OLAP. Por último é chamada outra transformação que coleta informações sobre o estado do DW naquele dia.

Dentro de cada um daqueles subjobs temos uma sequência de transformações, como, por exemplo, a carga das dimensões:

Job de sequenciamento de carga das dimensões.
Job de sequenciamento de carga das dimensões.

E cada uma dessas transformações faz a real movimentação de dados:

Carga da dimensão Cliente.
Carga da dimensão Cliente.

Para sabermos se tudo funcionou precisamos consultar o log de cada uma delas. Como os jobs estão amarrados com avanços condicionais (setas verdes), se der algum problema, o processo inteiro é abortado. Isso dá alguma facilidade de monitoração. Rodando no Spoon, o erro fica aparente:

Problema no processamento das dimensões.
Problema no processamento das dimensões.

Só que em produção não temos Spoon, então precisamos examinar o log:

2016/03/19 10:44:20 - Spoon - Starting job...
2016/03/19 10:44:20 - Refresh DW Beltrano - Start of job execution
2016/03/19 10:44:20 - Refresh DW Beltrano - Starting entry [Seta Variáveis]
2016/03/19 10:44:20 - Seta Variáveis - Loading transformation from XML file [file:///home/beltrano/ETL_Beltrano/t_a_seta_variaveis.ktr]
2016/03/19 10:44:20 - BELTRANO - KTR - Seta Variáveis - Dispatching started for transformation [BELTRANO - KTR - Seta Variáveis]
2016/03/19 10:44:20 - Grava timestamp.0 - Connected to database [beltrano_dw] (commit=1000)
2016/03/19 10:44:20 - Recupera Timestamp do Refresh.0 - Finished processing (I=0, O=0, R=1, W=2, U=0, E=0)
2016/03/19 10:44:20 - Seta variável.0 - Setting environment variables...
2016/03/19 10:44:20 - Seta variável.0 - Set variable REFRESH_TIMESTAMP to value [2016/03/19 10:44:20.724]
2016/03/19 10:44:20 - Seta variável.0 - Finished after 1 rows.
2016/03/19 10:44:20 - Seta variável.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2016/03/19 10:44:20 - Grava timestamp.0 - Finished processing (I=0, O=1, R=1, W=1, U=0, E=0)
2016/03/19 10:44:20 - Refresh DW Beltrano - Starting entry [Carga das Dimensões]
2016/03/19 10:44:20 - Refresh DW Beltrano - Carga das Dimensões - Starting entry [Dimensão Clientes]
2016/03/19 10:44:20 - Dimensão Clientes - Loading transformation from XML file [file:///home/beltrano/ETL_Beltrano/t_d_clientes.ktr]
2016/03/19 10:44:20 - Dimensão Clientes - Dispatching started for transformation [Dimensão Clientes]
2016/03/19 10:44:21 - Lê CNPJs.0 - Finished reading query, closing connection.
2016/03/19 10:44:21 - Lê CNPJs.0 - Finished processing (I=5000, O=0, R=0, W=5000, U=0, E=0)
2016/03/19 10:44:21 - Insere Tipo.0 - Finished processing (I=0, O=0, R=5000, W=5000, U=0, E=0)
2016/03/19 10:44:21 - Formata fluxo PJ.0 - Finished processing (I=0, O=0, R=5000, W=5000, U=0, E=0)
2016/03/19 10:44:21 - Lê CPFs.0 - Finished reading query, closing connection.
2016/03/19 10:44:21 - Lê CPFs.0 - Finished processing (I=50000, O=0, R=0, W=50000, U=0, E=0)
2016/03/19 10:44:21 - Insere Tipo e Cargo.0 - Finished processing (I=0, O=0, R=50000, W=50000, U=0, E=0)
2016/03/19 10:44:21 - Formata fluxo PF.0 - Finished processing (I=0, O=0, R=50000, W=50000, U=0, E=0)
2016/03/19 10:44:21 - Une fluxos.0 - Finished processing (I=0, O=0, R=55000, W=55000, U=0, E=0)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - ERROR (version 5.4.0.0-128, build 1 from 2015-06-03_13-41-59 by buildguy) : Unexpected error
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - ERROR (version 5.4.0.0-128, build 1 from 2015-06-03_13-41-59 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - An error occurred executing SQL: 
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - SELECT count(*) FROM d_clientes WHERE cliente_sk = 0
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - ERROR: relation "d_clientes" does not exist
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -   Position: 22
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - 
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.core.database.Database.openQuery(Database.java:1722)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.core.database.Database.openQuery(Database.java:1652)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.core.database.Database.openQuery(Database.java:1648)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.core.database.Database.openQuery(Database.java:1635)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.core.database.Database.getOneRow(Database.java:2963)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.checkDimZero(DimensionLookup.java:1681)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:216)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at java.lang.Thread.run(Thread.java:745)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - Caused by: org.postgresql.util.PSQLException: ERROR: relation "d_clientes" does not exist
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -   Position: 22
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     at org.pentaho.di.core.database.Database.openQuery(Database.java:1711)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 -     ... 8 more
2016/03/19 10:44:21 - Ordena lista.0 - Finished processing (I=0, O=0, R=55000, W=15882, U=0, E=0)
2016/03/19 10:44:21 - Dimensão Clientes - ERROR (version 5.4.0.0-128, build 1 from 2015-06-03_13-41-59 by buildguy) : Errors detected!
2016/03/19 10:44:21 - Estado.0 - Finished processing (I=28, O=0, R=6154, W=6154, U=0, E=0)
2016/03/19 10:44:21 - Carrega Dimensão Clientes.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
2016/03/19 10:44:21 - Dimensão Clientes - Transformation detected one or more steps with errors.
2016/03/19 10:44:21 - Dimensão Clientes - Transformation is killing the other steps!
2016/03/19 10:44:21 - Cidade.0 - Finished processing (I=9715, O=0, R=8553, W=8552, U=0, E=0)
2016/03/19 10:44:21 - Dimensão Clientes - ERROR (version 5.4.0.0-128, build 1 from 2015-06-03_13-41-59 by buildguy) : Errors detected!
2016/03/19 10:44:21 - Refresh DW Beltrano - Carga das Dimensões - Finished job entry [Dimensão Clientes] (result=[false])
2016/03/19 10:44:21 - Refresh DW Beltrano - Finished job entry [Carga das Dimensões] (result=[false])
2016/03/19 10:44:21 - Refresh DW Beltrano - Finished job entry [Seta Variáveis] (result=[false])
2016/03/19 10:44:21 - Refresh DW Beltrano - Job execution finished
2016/03/19 10:44:21 - Spoon - Job has ended.

Lendo o log descobrimos, facilmente, que o problema é a tabela d_clientes, que não foi criada no DW.

“Facilmente”?

Talvez em um processo simples, com poucos passos – e quando o erro aparece logo no começo. Mas achar o erro em um log desses, que pode chegar a vários megabytes, é qualquer coisa, menos “fácil”.

Você pode argumentar que basta fazer uma busca por ERROR. Verdade, mas precisaria fazer a busca no log inteiro, até o fim! E depois outra: onde é que apareceu mesmo esse erro? Examine o log outra vez: depois que achar o erro, você precisa seguir o log para cima, para descobrir em que arquivo (job ou transformação) ele ocorreu, ou para baixo, até encontrar o nome do job/transformação – mas não o nome do arquivo!

Resumindo: dá para fazer, mas é um porre.

Encontrando Nemo. Digo, Erros!

Eu sempre digo que sou um cara prático (preguiçoso é feio, ainda que seja mais franco, hehe) e gosto de usar computadores para fazer qualquer tarefa que possa ser feita por eles.

Uma forma mais fácil de encontrar erros é montar uma consulta como a do primeiro artigo e listar tudo que possuir o campo status igual a stop:

 (SELECT
       id_job,
       'Job' as tipo,
       jobname as nome,
       status,
       to_char(replaydate, 'DD/MM/YY HH24:MI:SS') as replaydate
  FROM job
  WHERE status='stop')
UNION
  (SELECT
       id_batch,
       'Transformação' as tipo,
       transname as nome,
       status,
       to_char(replaydate, 'DD/MM/YY HH24:MI:SS') as replaydate
  FROM transformation
  WHERE status='stop')
ORDER BY replaydate,nome

Resultado:

id tipo nome status replaydate
3 Job Popula Empresa-Case stop 23/02/16 20:48
8 Job Empresa-Case – Popula Tabelas Indepentes stop 23/02/16 20:55
2037 Transformação BELTRANO – KTR – Seta Variáveis stop 19/03/16 10:32
2039 Transformação Dimensão Clientes stop 19/03/16 10:33
2041 Transformação Dimensão Clientes stop 19/03/16 10:35
2043 Transformação Dimensão Clientes stop 19/03/16 10:44

Bem melhor, não? O problema aqui é que ele traz todo mundo. Ainda precisaríamos fazer algum filtro para pegar só o do dia anterior, ou até uma semana para trás, ou algo assim.

Mas podemos fazer melhor.

16.3 Árvore Genea-LOG-ica

Segundo Matt Casters, a tabela de logging channels surgiu como uma forma de amarrar os diversos logs: todas as coisas que são geradas no PDI ganham um ID particular, que é usado sempre que precisam – internamento ou no registro de log – se referir à aquele objeto. (Pelo menos foi o que eu entendi.) Isso é uma evolução em relação ao sistema antigo, que usava um ID sequencial para cada item, o que, convenhamos, é algo um tanto quanto noob.

O sistema de logs do PDI grava os canais de log em um tabela com o seguinte layout:

Coluna Tipo Descrição
id_batch int4 ID do lote
channel_id varchar ID do canal
log_date timestamp Data de registro nesta tabela
logging_object_type varchar Tipo do objeto: job, stransformação, banco de dados etc.
object_name varchar Nome do objeto
object_copy varchar Número da cópia do objeto
repository_directory varchar Caminho do objeto (repositório em banco)
filename varchar Diretório e nome do arquivo que contém este objeto
object_id varchar ID do objeto no repositório (repositório em banco)
object_revision varchar Versão do objeto (só para versão EE)
parent_channel_id varchar ID do canal do objeto-pai, que criou este objeto
root_channel_id varchar ID do canal do objeto-ancestral, que deu origem a todos os outros

O PDI pode salvar jobs e transformações em um sistema de arquivos, como um arquivo XML, ou em um repositório em banco de dados. As colunas da tabela que se referem a atributos válidos apenas para artefatos gravados no repositório em banco de dados recebem nulo quando o job ou transformação é gravado como um arquivo ordinário.


Pelo fato de os IDs serem gerados como um hash, o risco de colisão é próximo de nulo. Tanto é assim que as tabelas para canais de job e transformação tem o mesmo layout, e a Pentaho recomenda usar uma só tabela para registrar os dois.

Eis o conteúdo de algumas destas colunas:

Algumas colunas da tabela logging Channels.
Algumas colunas da tabela logging Channels.

Podemos usar essa tabela para listar todos os jobs e transformações envolvidos em um único processo. Depois, usando as tabelas de log de job e transformação, podemos adicionar o status e ordená-los pela data e hora de inicialização (replaydate.) Podemos montar um relatório com esse resultado. Daí poderemos revisar o processamento do dia e encontrar eventuais falhas muito mais – agora sim! – facilmente.

Vamos fazer isso, então.

Lista de Jobs-Raiz

Um job-raiz, por falta de um termo melhor, é um job que está na raiz de um processamento qualquer, e corresponde à lista de todos os tipos de objeto (coluna logging_object_type) JOB, cujo pai é nulo e que as tem a coluna root_channel_id igual à channel_id. Por exemplo, na figura anterior, o último job do ID_BATCH igual à 1023 é um job-raiz.

A consulta que traz isso é:

SELECT id_batch,
       channel_id,
       log_date,
       object_name
FROM job_logging_channels
WHERE logging_object_type = 'JOB'
      AND parent_channel_id IS NULL
      AND channel_id = root_channel_id

E eis o resultado:

id_batch channel_id log_date object_name
18 f411e0d2-… 2016-02-23 21:45:12.588 Popula Empresa-Case
22 9e142006-… 2016-02-23 22:27:00.323 Popula Tabelas de Pedidos
1023 b604142b-… 2016-03-19 10:27:20.576 Refresh DW Beltrano

Essa lista é o primeiro filtro do relatório: o conteúdo da coluna object_name será apresentado ao usuário como um prompt. E como cada job pode ter sido executado diversas vezes, um segundo prompt vai apresentar a lista de datas em que aquele processo foi executado, para que o usuário escolha que corrida analisar.

A lista de datas de execução é obtida com uma consulta na tabela de log do job, filtrada pelo object_name. Usando o job Popula Empresa-Case como exemplo, a lista de datas vem desta consulta:

SELECT DISTINCT
       channel_id,
       replaydate
FROM job
WHERE jobname = 'Popula Empresa-Case'

Usando essas consultas eu comecei o relatório:

Relatório de linhagem do job, com prompts de job e corrida.
Relatório de linhagem do job, com prompts de job e corrida.

Lista de Descendentes

A forma como a tabela de canais foi bolada permite construir uma consulta recursiva, para recuperar todas as partes de um processo, descendo até o último nível. Essa é a forma correta de consultar essa tabela. Porém, como eu não sei tanto SQL para poder construir uma consulta recursiva, vou apenas ser criativo e fazer de outra forma.

Os descendentes daquele job-raiz tem todos uma coisa em comum: o mesmo job-raiz (duhn.) Logo, podemos selecionar todos os elementos – jobs e transformações – que foram executados no mesmo pacote fazendo outra consutla à tabela de log. Eis como selecionar somente os jobs e transformações disparados pelo job principal:

SELECT channel_id,
       logging_object_type,
       object_name
FROM job_logging_channels
WHERE logging_object_type IN ('JOB','TRANS')
      AND root_channel_id = ${job_raiz}

O resultado, especificamente para o caso do job-raiz 9e142006-fb5d-4b28-8b87-1ff0c706919e, é:

channel_id logging_object_type object_name
d4d5227e-c07b-45d1-a6a9-715310de2e7e TRANS Gera Pedidos
56639da7-cb6d-435f-8d1e-c3a1f99c8687 TRANS Gera Parâmetros para Itens de Pedidos
06af3232-75ea-4b7f-b178-eab915e190df TRANS Seta Variáveis de Subitens
ee44ed8d-dbcc-431b-9b9d-d8220fe78838 TRANS Gera Pedidos Detalhes
aa0a4992-be94-4f8e-b0bf-98d14c0baaef JOB Popula Itens de Pedidos
957dc288-16e1-462c-9f1a-89efb026a2a2 TRANS Seta Variáveis de Subitens
4f2927bf-14c5-4b5a-a0d3-8a4c8eb597d7 TRANS Gera Pedidos Detalhes
a4dbea3e-6731-4fc3-92fb-1230888847d9 TRANS Seta Variáveis de Subitens
095dd255-20ec-4b96-a1d5-cf3a316f248d TRANS Gera Pedidos Detalhes
9e142006-fb5d-4b28-8b87-1ff0c706919e JOB Popula Tabelas de Pedidos

Pronto! Temos a nossa lista de jobs e transformações que compõe o job principal! Agora precisamos apenas dos detalhes de cada um deles, que estão nas respectivas tabelas de log de job e de transformação.

Detalhando a Lista

Como os detalhes de jobs e transformações estão em tabelas separadas, vamos fazer dois JOINs e depois reuni-los. Primeiro, os detalhes dos jobs:

SELECT logging_object_type,
       object_name,
       status,
       replaydate
FROM job_logging_channels, job
WHERE logging_object_type = 'JOB'
      AND job_logging_channels.channel_id = job.channel_id
      AND root_channel_id = ${job_raiz}

E agora os das transformações:

SELECT logging_object_type,
       object_name,
       status,
       replaydate
FROM job_logging_channels, transformation
WHERE logging_object_type = 'TRANS'
      AND job_logging_channels.channel_id = transformation.channel_id
      AND root_channel_id = ${job_raiz}

Unindo as duas consultas, temos o resultado completo:

Relatório completo, mostrando a linhagem de execução, ordenada por data/hora.
Relatório completo, mostrando a linhagem de execução, ordenada por data/hora.

Conclusão & Encerramento

O Pentaho Data Integration é uma das mais modernas e sofisticadas ferramentas de integração de dados disponíveis. Entre seus vários recursos estão a captura de logs muito detalhados, dos quais podemos extrair um gama de informaçôes sobre os processos executados por ele.

Nesta série vimos como configurar e usar o sistema de logs do PDI para obter uma visão simples, ainda que minimamente completa, sobre o que se passou em uma dada corrida (primeiro post).

No segundo post vimos como analisar os logs das transformações para detectar os gargalos, isto é, os pontos que puxam a velocidade da dita transformação para baixo.

Com este terceiro post concluímos a série. Vimos como usar um recurso fundamental do sistema de logs, a tabela de “canais” de log (logging channels), para montar uma listagem que sequencia todos os jobs e transformações executados em um processo (clique aqui para baixar o relatório.)

Esses três artigos formam um exemplo simples e prático para monitorar o processo diário, mas há muito que podemos fazer para melhorar a gestão de um DW. Por exemplo, temos todas as possibilidades de automação de detecção de erros e acionamentos por e-mail.

Até a próxima. ;-)

Diferença Entre SAS e MicroStrategy

Sempre que alguém chega ao blog por meio de uma busca na web, o WordPress.com registra o termo que resultou nesta busca. Ontem, por exemplo, estava lá o seguinte:

Top Searches

dw dimensão data semana exemplo,
livro como criar datawarehouse,
diferença entre sas e microstrategy,
controle de chaves planilha

Ora, pensei eu, diferença entre o SAS e o MicroStrategy? Por quê não? Semana que vem eu trarei a última parte da série Logs do PDI, com a consulta que dá a árvore de jobs e transformações. Hoje eu vou comentar sobre sobre como se comparam essas ferramentas.

Ferramentas & BI

Quem acompanha o blog sabe que eu tenho uma queda, um tombaço, por conceitos. Sou Físico, adoro teoria, não posso fazer nada.

Mas,por mais que eu reforce a importância dos conceitos para resolver um problema da melhor forma possível, no final do dia são as ferramentas que materializam os resultados. Tão importante quanto conhecer a teoria é saber quais são as ferramentas, como elas funcionam e como se relacionam aos conceitos.

O post de hoje vai começar explicando o básico da diferença entre MicroStrategy e SAS, e depois vai classificar as ferramentas mais famosas em categorias mais ou menos óbvias. A intenção não é chover no molhado, mas ser um recurso simples e rápido a quem tem pressa de entender essas diferenças.

A minha definição de BI é “a aplicação do método científico aos dados de uma empresa”. Para que isso aconteça precisamos preparar os dados e depois explorá-los. Ou seja, grosso modo, há duas grandes atividades em BI:

  • Juntar dados;
  • Analisar dados.

Portanto, essas são as duas grandes categorias de divisão de ferramentas em BI.


O MicroStrategy pertence exclusivamente à segunda categoria, análise de dados. O SAS pertence a ambas categorias: ele serve para acumular e analisar os dados.


O MicroStrategy

Se você examinar a página da plataforma MicroStrategy, vai notar que, logo no começo, há os seguintes dizeres:

With out-of-the-box gateways and native drivers, MicroStrategy makes it easy to seamlessly connect to any enterprise resource, including databases, mobile device management (MDM) systems, enterprise directories, cloud applications, physical access control systems, and more.

Traduzindo livremente:

Com conectores prontos e drivers nativos, o MicroStrategy facilmente conecta-se a qualquer recurso corporativo, incluindo bancos de dados, sistemas de gerenciamento de dispositivos móveis (MDM), diretórios corporativos, aplicações em nuvem, sistemas de controle de acesso físico e mais.

Se você seguir o link para saber mais, vai ver a mesma mensagem, apenas com outras palavras.

Apesar de eles até comentarem sobre dados e tal, no fundo o MicroStrategy não mexe com os dados de origem. O que o MicroStrategy faz, e faz muito bem por sinal, é servir um monte de tipos de visualização de dados, em um monte de meios diferentes, a partir de um monte de fontes de dados. Ele faz praticamente tudo que existe para ser feito em termos de visualização, exploração de dados e distribuição de resultados. Mas o MicroStrategy não integra dados e não faz Data Mining.

O SAS

O SAS é uma suite composta pelo Sistema SAS e diversos produtos periféricos. O SAS entrega tudo que o MicroStrategy entrega, com um pouco menos de “ooohhhh”, claro, e ainda faz integração de dados e Data Mining.

SAS significa Statistical Analysis System, e foi desenvolvido para atender uma necessidade do governo dos EUA. Seguindo a fantástica tradição norte-americana de unir Ciências e Negócios, esse software acadêmico cresceu e virou um produto de sucesso. Por mais de quarenta anos ele tem evoluído e, hoje, SAS é “A” empresa de BI, e a suite SAS é “O” software de BI, contra o qual o mercado inteiro é medido. (Não que o mercado saiba disso, claro. ;-) Não adianta – eu sou fã do SAS.)

O SAS possui um produto para ETL, um para limpeza de dados, um para Data Mining, um para OLAP, um cliente OLAP, ferramentas de relatório, de painéis (afff…), um para etc. etc. etc….

A página de produtos, para você ter uma idéia, lista as coisas por ordem alfabética. Para não soterrar o interessado em specs sheets sem fim, eles também oferecem uma página de soluções, organizada por indústrias – 23 atualmente.

O SAS é muito mais que só uma ferramenta disso ou daquilo. Ele é um software capacitado a implementar e executar a estratégia de BI de empresas de qualquer porte ou setor. Essa é sua força e sua fraqueza – já já veremos porque.

Laranjas e Maçãs

Trocando em miúdos, SAS e MicroStrategy não se comparam diretamente. Assim como SAS e Informatica, SAS e SPSS. Porém podemos comparar:

Não é fácil comparar os produtos do SAS com os outros. Eu já não mexo no SAS há uma década, e não posso afirmar nada hoje. Porém, na comparação direta, na cara do cliente, o SAS tinha uma dificuldade grande em lidar com a concorrência porque cada fornecedor estava em um nicho, mas o SAS estava em todos. A idéia do SAS é vender soluções, que levam o cliente a ganhar dinheiro. Quando o cliente não quer saber de solução, mas precisa apenas comprar uma ferramenta de visualização de dados, o SAS perde parte do seu apelo de plataforma integrada e tal. Perdi várias vendas para o MicroStrategy, simplesmente porque ele era mais fácil de usar e mais bonito. Mas também nunca perdemos uma venda de solução, na qual resolvíamos um problema do cliente – coisas “banais” como estancar a perda de um milhão de clientes por mês, ou otimizar mala-direta de milhões de dólares por campanha. ;-) Nada de relatório ou painel.

Tudo Vs. Tudo

De maneira geral, as ferramentas do mercado atual classificam-se nestas categorias:

  • Integração de Dados:
    • SAS Data Management;
    • Informatica PowerCenter;
    • Pentaho Data Integration;
    • Microsoft Data Integration Services
    • Clover ETL
    • Talend
  • Data Stores
    • Oracle, MS SQL Server, Postgres, MySQL
    • Teradata, Vertica, MoneDB
    • MS SQL Server Analysis Services
    • Hadoop
  • Data Mining
    • SAS Enterprise Miner
    • SPSS
    • RapidMiner
    • R, Weka
  • Visualização
    • SAS Visual Studio, SAS/GRAPH, SAS/OR, SAS/etc…
    • MicroStrategy
    • BO (agora da SAP)
    • Pentaho OLAP, Relatório, Relatório AdHoc e painéis
    • QlikView
    • Tableau

Há um mundaréu de produtos e eu, com certeza, deixei vários de fora. A idéia, como eu disse no início, é só dar uma base para consulta rápida, que te mostre ao menos o caminho, a direção na qual seguir para estudar mais.


Repare que o SAS e o Pentaho aparecem em todas as categorias (Weka é parte do Pentaho), exceto Data Store. Isso é uma feliz coincidência: o Pentaho é, no fundo, um SAS open source.


Concluindo…

…uma comparação minuciosas entre quaisquer produtos do mercado de BI tende a ser muito complicada. Vários produtos possuem similaridades que justifiquem colocá-los como concorrentes, mas nem sempre dá para fazer uma comparação 1 para 1. Na verdade, raramente dá. Se você precisa se decidir entre um produto e outro, tente pelo menos levar em consideração a estratégia da empresa e o futuro que vocês planejam.

Até a próxima. ;-)

Autopublicação na Prática – Lançamento Oficial

O Autopublicação na Prática está oficialmente lançado!

Capa do Autopublicação na Prática. Esse aí sou eu fazendo o PnP...
Capa do Autopublicação na Prática. Esse aí sou eu fazendo o PnP…

Se você veio aqui aproveitar a tradicional promoção de lançamento e não quer perder tempo com blá-blá-blá, clique aqui e seja feliz!


Agora, se você vai encarar o blá-blá-blá… Pegou o café? :-)

Meu primeiro livro foi escrito em 2013, justamente o Pentaho na Prática. Eu não sabia nada sobre escrever livros, muito menos sobre escrever livros eletrônicos, e cometi minha cota de erros e mais um pouco. Claro, vários clientes reclamaram de uma série de probleminhas (e alguns problemões), a própria Amazon encontrou várias falhas… Eu fiz tudo que era possível para salvar aquele trabalho, aquele original, mas não foi o suficiente. Independentemente do conteúdo, o produto, o livro eletrônico, não estava à altura da qualidade da Amazon.com, e ele foi tirado do ar.

Depois de um tempo eu percebi que o livro havia sido aprovado pelos leitores. Ele era mesmo útil para várias pessoas(!), e muitos queriam tê-lo, tanto que recebi várias consultas sobre quando ele voltaria ao ar. Comecei a considerar reescrevê-lo inteiramente, mas desta vez fazendo tudo certo.

Foi quando me bateu a sensação de que existe demanda reprimida por conhecimento. Imaginem, um livro sobre Pentaho e estava vendendo! Pentaho é um nicho de nicho de nicho, e tem demanda por material sobre ele – no Brasil! Imagine quanta demanda reprimida existe por aí – e no Brasil?!

Daí o resto é história: entre ufanismo envergonhado, vaidade desenfreada e ideologia desatinada, eu decidi que antes de retornar com o PnP eu publicaria um livro sobre o easybook, o Software Livre que eu precisei aprender a usar para refazer o Pentaho na Prática. Mais: como meu negócio é entregar resultado, eu montei um gabarito e incluí um capítulo de tutorial (Capítulo 2) para que meu leitor possa criar em minutos um novo livro tão bom quanto qualquer obra profissional.

É isso. Se você sabe fazer algo que mais ninguém sabe, se você é um bom contador de histórias, vá buscar seu exemplar gratuito do Autopublicação na Prática e adicione sua contribuição ao mercado literário brasileiro. Se não for caro, quem sabe eu não viro seu leitor? ;-)


Você pode ler o livro em qualquer dispositivo – não é necessário possuir um Kindle!


Compre! ;-)

Analisando os Logs do PDI – Parte 2

No primeiro post da série Logs do PDI vimos como configurar as transformações e jobs do PDI para capturar os logs de processamento e como usá-los para monitorar as execuções. Neste segundo post veremos como usar esses dados para identificar gargalos.

Pias & Transformações

Imagine uma pia, na qual uma torneira despeja água. Se entrar mais água do que a pia consegue escoar, a água vai começar a se acumular. Se nada for feito, o nível de água na pia vai subir, e subir, até transbordar. Há duas formas de se evitar que a água transborde: reduzir a entrada ou aumentar a saída. Em termos mais genéricos, para evitar que a água transborde devemos condicionar a entrada à restrição. A “restrição” é justamente a válvula de escoamento da pia.

A velocidade de uma transformação, medida em linhas por segundo, l/s, está condicionada à restrição existente na transformação: a velocidade de uma transformação será igual à velocidade do seu passo mais lento. Esse passo mais lento é o gargalo.


Se uma transformação possuir passos de ordenação de linhas, agrupamentos etc., que represam linhas, a velocidade média da transformação perde essa relação direta. Ainda vai haver uma relação entre a velocidade da transformação e a velocidade do seu passo mais lento, apenas não será mais 1 para 1. Mas, para manter o didatismo do post, vamos assumir uma transformação perfeitamente esférica e sem atrito, na qual essa condição vale. ;-)


Para aumentar a velocidade de uma transformação, portanto, devemos aumentar a velocidade do fluxo (em l/s) no gargalo. Quando a velocidade no gargalo tiver subido o bastante, algum outro passo vai ser o responsável por restringir a velocidade da transformação, se transformando no novo gargalo. Removemos esse novo gargalo, aumentando a velocidade da transformação, e outro aparecerá, reiniciando o processo. Eventualmente, chegaremos num ponto de rendimento máximo da transformação, no qual uma nova mudança em qualquer passo não vai resultar em aumento de velocidade da transformação.

Simples, não?

Engarrafamento de Linhas

E como descobrimos gargalos em uma transformação?

Usando o Spoon para detectar gargalos: notou um pontilhado em volta de algum passo? É um gargalo!
Usando o Spoon para detectar gargalos: notou um pontilhado em volta de algum passo? É um gargalo!

Se você estiver rodando-a no Spoon, a interface gráfica do PDI, é muito fácil. O PDI detecta o gargalo, definido por ele como “o passo que recebe mais linhas do que é capaz de processar”, e faz com que o Spoon apresente um pontilhado animado em volta de cada passo identificado como gargalo. Esse monitoramento ocorre em tempo real, e causa efeitos curiosos: em certas situações o gargalo parece “pular” entre dois ou mais passos, e em outras vários passos são marcados como gargalos, simultaneamente. A dinâmica que causa isso está fora do escopo deste post, mas não é difícil de entender se você parar para pensar um pouco.

Para que seu Spoon mostre esse passo na sua transformação você precisa habilitar o checkbox Show bottleneck transformation steps, conforme visto na figura abaixo. Essa janela aparece quando selecionamos o item Options, no menu Tools.

Onde fica o checkbox que habilita a exibição do gargalo.
Onde fica o checkbox que habilita a exibição do gargalo.

Essa opção é muito boa durante o processo de desenvolvimento, mas o que fazer para transformações que rodamos em produção? É um ambiente diferente – memória diferente, disco diferente, carga (tarefas simultâneas) diferentes! Se bobear até o sistema operacional é diferente! Não dá para rodar uma por uma no Spoon e ficar olhando, esperando encontrar um passo-gargalo.

Você adivinhou: consultamos o log de performance.

Entendendo o Gargalo

Conforme o próprio PDI coloca, um gargalo (ou restrição) é “um passo que recebe mais linhas do que é capaz de processar”. Para entender isso você precisa saber um pouco sobre a estrutura interna de um passo.

Um passo possui, grosso modo, três partes:

  1. Buffer de entrada: onde são gravadas as linhas que chegam do passo anterior, e onde elas ficam esperando ser processadas;
  2. Motor do passo: retira uma linha do buffer de entrada, processa e grava o resultado no buffer de saída, repetindo esse processo enquanto o passo anterior estiver mandando linhas;
  3. Buffer de entrada: onde são gravadas as linhas processadas pelo passo, e onde elas aguardam até o engine do PDI movê-las para o passo seguinte.
Todo passo possui dois buffers: entrada e saída.
Todo passo possui dois buffers: entrada e saída.

Assim, se o motor do passo, o processamento em si, não for rápido o bastante, ele começa a ficar para trás: as linhas começam a se acumular no buffer de entrada. Se, além disso, se o passo seguinte for no mínimo tão rápido quanto o passo atual, as linhas processadas não “esquentam cadeira” no buffer de saída, e vão sendo levadas adiante. Por exemplo, na transformação simples mostrada abaixo, com três passos, o passo do meio é um gargalo:

O passo do meio é um gargalo: buffer de saída com menos linhas que o de entrada.
O passo do meio é um gargalo: buffer de saída com menos linhas que o de entrada.

Note que os buffers do passo do meio, representados pelas caixas de entrada e saída, aparecem vazio na saída e cheio na entrada. Essa é a condição de gargalo, ou de restrição. Se pudermos aumentar (elevar) a vazão dessa restrição, a velocidade da transformação, como um todo, aumentará.

Como vemos isso nos logs? Bom, quando rodamos uma transformação no Spoon, este monta um quadro de métricas dos passos, e atualiza-o conforme a transformação vai sendo executada, conforme as linhas vão sendo processadas.

Passos e seus parâmetros durante execução.
Passos e seus parâmetros durante execução.

A coluna input/output, última à direita na imagem anterior, exibe a relação entre a quantidade de linhas presentes no buffer de entrada, aguardando processamento, e quantidade de linhas no buffer de saída, aguardando transporte até o passo seguinte. Sempre que um passo possui mais linhas em sua entradas que em sua saída, ele está “segurando” a transformação. Para encontrarmos os gargalos de uma transformação, então, basta analisarmos os passos dela, em busca de buffers de entrada cheios e buffers de saída vazios (ou com bem menos linhas.)

Analisando Vazão em Transformações

O log que captura as métricas de cada passo, segundo a segundo, chama-se Performance. Veja como ativá-lo no primeiro post da série. Essa tabela possui as seguintes colunas:

  • id_batch
  • seq_nr
  • logdate
  • transname
  • stepname
  • step_copy
  • lines_read
  • lines_written
  • lines_updated
  • lines_input
  • lines_output
  • lines_rejected
  • errors
  • input_buffer_rows
  • output_buffer_rows

Reparou nas duas últimas? São as que nos interessam.


Não vou montar uma solução pronta, mas mostrar como usar essa tabela para desenhar algo que atenda à sua necessidade – e depois vou mostrar um exemplo do relatório que eu uso para “debugar” transformações em produção.


Precisamos de uma consulta que traga os buffers, por cada passo, por transformação. Além disso, como cada transformação pode ter sido executada várias vezes, precisamos filtrar também por lote (id_batch.) Essa consulta ficaria assim:

SELECT
  id_batch,
  transname,
  stepname,
  input_buffer_rows,
  output_buffer_rows
FROM transformation_performance
WHERE
  id_batch = X AND
  transname = 'x'

Filtrando para lote 2080 e transformação Carga da Lista de Clientes PF, temos:

id_batch transname stepname input_buffer_rows output_buffer_rows
2080 Carga da Lista de Clientes PF Gera strings aleatórias 224 276
2080 Carga da Lista de Clientes PF Cria CEP, Telefone e Fax 0 0
2080 Carga da Lista de Clientes PF Cria CEP, Telefone e Fax 0 0
2080 Carga da Lista de Clientes PF Lê nomes 0 0
2080 Carga da Lista de Clientes PF Cria as partes de CEP, Telefone e Fax 0 0
2080 Carga da Lista de Clientes PF Muda rnd_telefone/fax para string 0 0
2080 Carga da Lista de Clientes PF Seleciona endereço 2 0
2080 Carga da Lista de Clientes PF Lê sobrenomes 0 61
2080 Carga da Lista de Clientes PF Acerta tamanho das strings 0 0
2080 Carga da Lista de Clientes PF Seleciona sobrenome 61 0
2080 Carga da Lista de Clientes PF Lê lista de enderecos 0 0
2080 Carga da Lista de Clientes PF Seleciona nome 0 0
2080 Carga da Lista de Clientes PF Cria ID do Cliente 1560 225
2080 Carga da Lista de Clientes PF Insere cidade_id 1 3
2080 Carga da Lista de Clientes PF Limpa fluxo 0 0
2080 Carga da Lista de Clientes PF Cria índice do endereço 276 125
2080 Carga da Lista de Clientes PF Gerar linhas 0 2444
2080 Carga da Lista de Clientes PF Cria índices de nome 147 110
2080 Carga da Lista de Clientes PF String operations 3 1
2080 Carga da Lista de Clientes PF Absoluto de rnd_cpf 110 3
2080 Carga da Lista de Clientes PF Grava tabela 0 0
2080 Carga da Lista de Clientes PF Gera strings aleatórias 1778 5755
2080 Carga da Lista de Clientes PF Cria CEP, Telefone e Fax 0 0

E aí, consegue dizer se algum destes passos é um gargalo? Nem eu. Vamos melhorar nossa consulta: vamos agrupar por passo, remover o nome da transformação e lote da execução, já que ambos são previamente conhecidos (estão no filtro.) Fica assim:

SELECT
  stepname,
  sum(input_buffer_rows) as buffer_entrada,
  sum(output_buffer_rows) as buffer_saida
FROM transformation_performance
WHERE
  id_batch = 2080 AND
  transname = 'Carga da Lista de Clientes PF'
GROUP BY
  stepname
ORDER BY
  stepname

Resultado:

stepname buffer_entrada buffer_saida
Absoluto de rnd_cpf 4289 2770
Acerta tamanho das strings 10117 10100
Cria as partes de CEP, Telefone e Fax 100 10098
Cria CEP, Telefone e Fax 10101 20099
Cria ID do Cliente 21021 11892
Cria índice do endereço 15660 3458
Cria índices de nome 3483 4289
Gerar linhas 0 21902
Gera strings aleatórias 11889 15658
Grava tabela 20281 0
Insere cidade_id 202 259
Lê lista de enderecos 0 0
Lê nomes 0 0
Lê sobrenomes 0 61
Limpa fluxo 20099 20281
Muda rnd_telefone/fax para string 125 100
Seleciona endereço 258 714
Seleciona nome 714 100
Seleciona sobrenome 161 125
String operations 2770 202

Já dá para dizer se alguém é gargalo? Não! Veja, buffer de entrada e buffer de saída são métricas não-aditivas: somá-las ao longo do tempo não dá nenhuma informação sobre o processo. Nem somando-as ao longo dos passos, ou das transformações, nada! No máximo, como métricas não-aditivas, talvez uma média ao longo do tempo possa dar alguma idéia do que está acontecendo.

Mesmo assim, a média não ajudaria. Lembra-se que o monitoramento de gargalo do Spoon ocorre em tempo real? E que os gargalos podem pular de um passo para outro? Só conseguimos identificar um gargalo olhando o comportamento dele ao longo do tempo, momento a momento.

Incluíndo o tempo na consulta, temos:

SELECT
  stepname,
  logdate,
  sum(input_buffer_rows) as buffer_entrada,
  sum(output_buffer_rows) as buffer_saida
FROM transformation_performance
WHERE
  id_batch = 2080 AND
  transname = 'Carga da Lista de Clientes PF'
GROUP BY
  stepname,
  logdate
ORDER BY
  stepname

Vai nos dar a seguinte saída:

stepname logdate buffer_entrada buffer_saida
Absoluto de rnd_cpf 2016-02-21 16:34:41 110 3
Absoluto de rnd_cpf 2016-02-21 16:34:41 110 3
Absoluto de rnd_cpf 2016-02-21 16:34:42 87 100
Absoluto de rnd_cpf 2016-02-21 16:34:43 4092 2667
Absoluto de rnd_cpf 2016-02-21 16:34:44 0 0
Absoluto de rnd_cpf 2016-02-21 16:34:45 0 0
Absoluto de rnd_cpf 2016-02-21 16:34:46 0 0
Absoluto de rnd_cpf 2016-02-21 16:34:46 0 0
Acerta tamanho das strings 2016-02-21 16:34:41 0 0
Acerta tamanho das strings 2016-02-21 16:34:42 0 0
Acerta tamanho das strings 2016-02-21 16:34:43 120 100
Acerta tamanho das strings 2016-02-21 16:34:44 9997 10000
Acerta tamanho das strings 2016-02-21 16:34:45 0 0
Acerta tamanho das strings 2016-02-21 16:34:46 0 0
Acerta tamanho das strings 2016-02-21 16:34:46 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:41 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:42 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:43 100 101
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:44 0 9997
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:45 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:46 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:46 0 0
Cria CEP, Telefone e Fax 2016-02-21 16:34:41 0 0
Cria CEP, Telefone e Fax 2016-02-21 16:34:42 0 0
Cria CEP, Telefone e Fax 2016-02-21 16:34:43 101 100
Cria CEP, Telefone e Fax 2016-02-21 16:34:44 10000 10000
Cria CEP, Telefone e Fax 2016-02-21 16:34:45 0 9999
Cria CEP, Telefone e Fax 2016-02-21 16:34:46 0 0

Agora podemos dizer, se em algum instante, um passo se comportou como um gargalo. Claro que examinar os dados assim é uma bobagem – podemos montar um gráfico com isso.

Relatório de Análise de Fluxos

Juntando essa consulta com os relatórios do primeiro post, montamos um relatório de análise de fluxo de transformações:

Relatório de análise de fluxo em transformações.
Relatório de análise de fluxo em transformações.

Esse relatório traça a velocidade daquele passo em linhas por segundo no gráfico de cima, e o volume de linhas presentes nos buffers, no gráfico de baixo, tudo instante a instante. O que vemos é que esse passo possui uma vazão boa, até, de 20.000 l/s, e que ele nunca esteve em condição de gargalo. Muito pelo contrário: no instante 5 ele teve o seu buffer de saída completamente cheio, sugerindo que o passo seguinte poderia ser um gargalo.

Eis o trecho da transformação que estamos analisando:

Trecho final da transformação de carga de clientes PF da Beltrano S/A.
Trecho final da transformação de carga de clientes PF da Beltrano S/A.

Os passos seguintes são um Select Values e um Table Output. Eis os buffers do Select Values:

Buffers do Select Values.
Buffers do Select Values.

E os do Table Output:

Buffers do Table Output.
Buffers do Table Output.

A-há! Veja ali, do momento 3 ao momento 6, como o buffer de entrada (a linha vermelha) sobe até atingir 10.000 linhas, enquanto o buffer de saída permanece em zero! Esse passo é um gargalo! Ele ficou com o buffer de entrada cheio, que fez com que as linhas “transbordassem” para o passo anterior, o Select Values. Tanto esse Select Values não é um gargalo que os dois buffers sobem e descem juntos. Mais ainda: foi justamente entre os momentos 3 e 6 (comuns a todos os passos, aliás, pois o log de performance captura todo mundo junto) que o buffer de saída do passo Cria CEP, Telefone e Fax saturou, isto é, atingiu seu limite de 10.000 linhas, e começou a se refletir no buffer de entrada, mesmo que por apenas um segundo (cada momento equivale a um segundo nesta escala.)

Conclusão

A partir da consulta mostrada podemos criar um relatório que exibe o estado dos buffers de cada passo momento a momento. Usando a transformação como guia, podemos analisar cada um dos passos em busca do sintoma de restrição de fluxo.

Essa não é a melhor análise, já que nos obriga a olhar um por um. Como você faria essa análise automaticamente? Como deixar para o Report Designer detectar os gargalos de uma transformação?

E apenas para fechar o problema encontrado neste exemplo: um gargalo em um passo que grava dados no banco significa que a rede e o banco é que são os gargalos. Dependendo da influência da rede e do banco na força da restrição, podemos levar o processamento para mais perto (reduzindo o custo da rede) ou melhorar o banco com, por exemplo, discos mais rápidos.

Na próxima semana terminaremos a série com um relatório de genealogia de um processo de ETL.

Até lá!


Novo Lançamento!

Sexta-feira, 11 de março de 2016, será o lançamento do meu novo livro, Autopublicação na Prática. Não deixe de passar por aqui, porque – como sempre – ele será lançado por R$ 0,00!

Coletânea Geek BI 2015 – Lançamento Oficial

Em 7/2/2016 a coletânea de artigos postados no blog durante 2015 foi publicado como um e-book na Amazon.com:

Página do Geek BI 2015 na Amazon.com.br.
Página do Geek BI 2015 na Amazon.com.br.

A Coletânea GeekBI 2015 está oficialmente lançada!

Como sempre, em reconhecimento ao apoio dos meus leitores, hoje durante o dia inteiro o livro está disponível gratuitamente na Amazon.com.br e em todas as outras lojas.


Você pode ler o livro em qualquer dispositivo – não é necessário possuir um Kindle!


Capa da coletânea.
Capa da coletânea.

Esta página do blog conta um pouco mais sobre as coletâneas do Geek BI. Se ainda não for o bastante para te deixar com curiosidade, você pode baixar uma amostra gratuita e sem compromisso – como qualquer outro e-book lá.

Compre! ;-)