13 Março 2018

Como implementar uma plataforma de subscrição de relatórios usando SSIS? – Business Intelligence

A ferramenta SSIS… SQL Server Integration Services… é uma excelente ferramenta para (1) extrair dados das variadas fontes de dados do cliente, (2) transformar esses mesmos dados lidos em dados mais compreensíveis pelo negócio e depois (3) carregar esses mesmos dados em produtivo, área já acessível pelos utilizadores finais de negócio que consomem Relatórios, Dashboards, ScoreCards, SelfService, etc.

Mas o trabalho do SSIS termina aqui?

 

Não. Os dados, agora, estão disponíveis para serem consumidos via Relatórios (por exemplo) e podemos ter vários utilizadores que podem executá-los ao mesmo tempo. Mas esta situação pode levar a problemas de desempenho do Sistema, por esta mesma concorrência.

E mais, nós queremos registar os pedidos e a sua execução de uma maneira mais estruturada e controlada.

Então, como a ferramenta SSIS pode ajudar-nos novamente?

“Simples”: com a sua ajuda podemos implementar uma Solução “leve” de Plataforma de Subscrição de Relatórios (PSR)!

 

 

Mas podemos ser mais ambiciosos…

Já que esta plataforma gere a concorrência de execução de pedidos de geração de Relatórios, porque não estender esta execução a outros domínios, sem ser a execução de Relatórios? Extensibilidade!
Então vamos implementar via SSIS uma plataforma que permita assincronamente executar qualquer tipo de pedido, quer seja Relatórios ou não!

 

Então, esta plataforma envolve a execução de pedidos – criados pelos utilizadores (por exemplo: “Quero executar um Relatório de Vendas, então crio um pedido, preencho-o com os dados necessários de entrada e envio-o para uma “fila de espera” para ser executado depois por um “serviço”) de uma maneira assíncrona: o cliente não tem que esperar pela sua execução, ele pode consultar o estado atual do seu pedido e ele próprio poder executar outras tarefas.

Então, resumidamente, é uma plataforma onde:

1- os pedidos são criados pelos utilizadores
2- os pedidos são registados numa fila especial (FIFO mas com prioridades)
3- os pedidos são lidos e executados assincronamente
4- o utilizador sabe o estado do seu pedido e acede ao output gerado no final, de alguma maneira

 

Para o Sistema de Pedidos – vamos usar os componentes SQL BI, onde se inclui a ferramenta SSIS.

 

Primeiro, vamos definir alguns conceitos para esta plataforma:

Pedido – o pedido criado pelos utilizadores: tem dados de entrada/contexto que permita a sua execução, sem indefinições;
Fila – onde são registados os pedidos;

Pedido agendado – pedido para ser executado numa dada data/hora;

“Listener” – lê pedidos da fila e só sabe fazer isto; cada pedido lido é passado a um “Dispatcher”;

“Dispatcher” – executa o pedido propriamente dito;

“Dispatcher” de Pedidos Agendados – executa pedidos agendados;

Plugin/Provider – um componente – pacote SSIS – customizado para executar um determinado tipo de pedido (por exemplo, esse tipo de pedido pode ser a execução de um relatório, pode ser um componente que envia emails, o que quisermos!) e está configurado nesta mesma plataforma.

Isto leva a que seja extensível!

 

Então, agora, vamos definir os requisitos Funcionais e Não-Funcionais:

Do lado “Cliente”:

FR01 – o Sistema deve implementar um “web service” que permita às aplicações cliente desta plataforma:

i) Consultar o estado de pedidos
ii) A criação de pedidos
iii) A criação de pedidos agendados
iv) O cancelamento, se ainda possível, do pedido registado na fila

Do lado “Servidor”:

FR02 – os pedidos devem ter como informação:

- Uma Prioridade
- Data de Criação
- Estado de Execução atual
- Flag de Execução (executado ou não?)
- Os dados de entrada que definem o contexto de execução do pedido
- Quem criou o pedido (utilizador)

FR03 – o Sistema deve permitir a execução de qualquer tipo de pedido; mas por cada tipo de pedido deve ser criado um “plugin” específico que depois é “encaixado” via configuração na plataforma
FR04 – no caso de integração com relatórios formato SSRS (SQL Server Reporting Services), deve ser permitida a integração desta plataforma com o serviço SSRS
FR05 – o Sistema deve permitir o agendamento de pedidos, em termos de execução (para futura implementação)
FR06 – o Sistema deve permitir que o pedido possa ser executado em caso de erro (configurável)
FR07 – o Sistema deve gerir o número de execuções em simultâneo, para gestão de desempenho do serviço

Como requisitos Não-Funcionais:

NFR01 – o Sistema deve permitir Balanceamento de Carga (gestão de desempenho)
NFR02 – o Sistema deve ser robusto
NFR03 – o Sistema deve permitir adequar as configurações de execução ao estado atual do sistema (por exemplo, se for detetado que o sistema está com carga então devem-se ler menos pedidos da fila de espera, por exemplo)
NFR04 – o Sistema deve permitir a execução mais rápida possível dos pedidos (os não agendados)

 

Como desenhar/preparar a arquitetura?

Os cinco componentes representados na imagem anterior são:

1 – Bases de Dados de “Request Manager”
2 – Microsoft SQL Server Integration Services
3 – Microsoft SQL Server Reporting Services
4 – Sistema Ficheiros
5 – Sistema Impressão

Os dois primeiros são os mais importantes deste sistema:

1 – Bases de Dados de “Request Manager”
2 – Microsoft SQL Server Integration Services

Juntos definem o coração e o motor deste Sistema!

Os outros (de três a cinco) representam um exemplo prático de uso deste sistema.

1 – Bases de Dados De Request Manager

 

 

Podemos modelar o Pedido desta maneira:

 

(de cima para baixo)

- Chave (identifica univocamente o pedido no sistema)
- CreateDate (data de criação do pedido)
- ModifiedDate (data de modificação do pedido)
- CreatedBy (criador do pedido)
- RequestManagerStateID (estado corrente de execução do pedido)
- StateObs (observações complementares)
- SucessFlag (indica se pedido foi executado com sucesso ou não)
- RetriesCount (nº de tentativas decorridas de re-execução em caso de erro)
- Priority (prioridade)
- Retriable (indica se pode ser re-executado em caso de erro)
- OutputChannelType (identifica o tipo de componente – plugin – que irá executar este pedido) => extensibilidade!
- OutputChannelDetail (contexto de execução do pedido definido como entrada; parâmetros de entrada basicamente…)
- ServiceExecutionID, ServiceInfo (reservado)
- AppExecutionContext (cache de execução se necessário)
- ControlHeartBeat (reservado)
- ForScheduling (indica se o pedido tem agendamento)
- ForSchedulingCommand (informação de agendamento)
- CancelFlag (flag de cancelamento do pedido)

 

Prioridades:

Tabela – RequestManagerPriority

RequestManagerPriorityID – chave
Description – descrição

Valores:

1-Low
2-Normal
3-Hight
4-VIP

Estados possíveis em baixo; mais poderão ser acrescentados dependendo das regras funcionais do “plugin”:

Tabela – RequestManagerState

RequestManagerStateID – chave
NameState – nome do estado
Description – descrição do estado
Flag_Listener_Can_Catch – indica se o pedido pode ser lido pelo componente “Listener” neste estado
Flag_Dispatcher_Is_Busy – identifica este estado como sendo um “estado de execução”
Flag_Scheduler_Dispatcher_Is_Busy – para agendamento
NameStateForClient – nome visível pelos utilizadores
DescriptionForClient – descritivo visível pelos utilizadores

Os estados por defeito são:

1 – In the Queue
2 – Ready for Dispacthing
3 – Dispatched: Initiating workflow WK
99 – Executed
100 - Scheduling Request
101 - Scheduled. Waiting SQL Agent call
102 - Schedule waked and is running
999 - Request was canceled

 

Para garantir o requisito de extensibilidade desta plataforma, o seguinte deve ser definido:

Tabela – RequestManagerOutputChannelType

RequestManagerOutputChannelTypeID – chave
SSIS_PackageProvider – nome do pacote SSIS que irá tratar o tipo de pedido em causa
Description – descrição

No exemplo, em cima, o “plugin” com Id 1 está associado ao componente com nome “RequestManager_PlugInProvider_SSRS”: está responsável pela geração de relatórios SSRS.

Mais objetos relevantes:

Perfis de Pedidos

Cada pedido tem sempre um Perfil!

Tabela – RequestManagerProfileName

RequestManagerProfileNameID – chave
RequestManagerProfileName – o nome do perfil
SetPriorityTo – prioridade definida
SetRetriableTo – indica se o pedido pode ser reexecutado em caso de erro
SetOutputChannelTypeTo – define o “plugin” associado que irá tratar este tipo de pedido

E a Segurança?!

 

Tabela – RequestManagerProfileNamePermissions

RequestManagerProfileNameID – chave
User – utilizador que pode criar pedidos
Description – descrição

E quanto a log aplicacional?

Tabela – RequestManagerLog

Valores de configuração

Tabela – SSIS Configurations

2 – Microsoft SQL Server Integration Services

 

Os seguintes componentes

a) Listener
b) Dispatcher
c) Dispatcher Plugin/Provider
d) Schedule Dispatcher

 

Estão implementados via ferramenta SSIS.

a) Listener (RequestManager_Listener.dtsx)

Este componente lê pedidos da fila e passa-os ao componente Dispatcher. É a principal função.

Em paralelo podem ser executadas tarefas de manutenção e monitorização como sejam:

- Escalar a prioridade de pedidos que estejam na fila há muito tempo para serem executados, isto porque outros mais prioritários são executados primeiro
- Recuperar pedidos que não recuperaram devido a erro inesperado
- Arquivo de pedidos para histórico
- Controlo da carga atual do sistema

E, como implementá-los?

Figura – O componente Listener “RequestManager_Listener.dtsx”

Subcomponente 1

Fluxo:
1- lê os pedidos recorrendo-se do procedimento SQL “RequestManager_GetRequestsFromQueueForDispatch”; as regras de leitura ficam assim centralizadas
2- lança o componente Dispatcher assincronamente para cada pedido
3- “dorme” n segundos antes da próxima execução (gerir tempo CPU)

Subcomponent 2 (Queue Manager)

Gere a fila.

Fluxo:
1-Executa o procedimento de nome“RequestManager_ManageQueue”
2-Calcula o valor corrente de carga no sistema (algoritmo simplista: criar uma rotina de “sleep” de 1 minuto, por exemplo, e no final calcular a duração decorrida: se duração= 1minuto então a carga é nula caso contrário sendo superior significa que já existe alguma carga sobre o CPU; dependendo do tempo decorrido podem-se definir níveis de carga
3-“dorme” n segundos antes da próxima execução (gerir tempo CPU)

 

b) Dispatcher

Este componente recebe do “Listener” um pedido para ser executado.

Figura – O componente Dispatcher “RequestManager_Dispatcher.dtsx”

Subcomponente 1

Para o pedido atual a ser executado, obtém o nome do pacote SSIS associado (“PlugIn”) usando para o efeito o atributo já discutido “Channel Output Type” e executa-o passando o respetivo pedido (Id, chave).

Esta chamada é síncrona!

Nota importante: está preparado para erros inesperados: caso haja um o estado do pedido é marcado como erro.

 

 

Subcomponente 2

É executado em paralelo com o subcomponente 1.

Permite assinalar que este Dispatcher está “vivo” ainda e a executar o pedido.

Porque é importante? Porque um pedido pode demorar muito tempo a ser executado e a única maneira de saber se o pedido não “crashou” (deu erro inesperado) ou se ainda está a ser executado é ter em paralelo este ciclo de registo de atividade!

 

c) Dispatcher Plugin/Provider

Este corresponde ao “plugin” que trata o tipo de pedido em causa.

É invocado pelo componente do sistema “Dispatcher” no ponto assinalado em baixo:

 

O pedido deve ser executado o mais rápido possível para que outros pedidos possam ser executados!

 

O lado do servidor está definido.

E o lado do cliente?

Como é que os aplicativos externos podem interagir com este serviço para criar pedidos ou consultar o estado, por exemplo?

Resposta: deve ser definido um “Web Service” com a seguinte interface:

Figura – Web Service

As principais funcionalidades:

- CancelRequest (cancelar pedidos)
- GetRequestsByUser (consultar os pedidos criados por dado utilizador)
- InsertRequest (criar pedidos)
- InsertScheduledRequest (criar pedidos com agendamento)

E a segurança?

Autenticação:

Três modos são possíveis:

 

De cima para baixo:

- Obtém o utilizador corrente chamador num contexto Windows (Sistema operativo)
- O utilizador é passado diretamente como parâmetro (menos seguro)
- O utilizador é obtido via “token de segurança” – implementar recorrendo a mecanismo de SSO (“Single Sign On”)

Autorização:

O perfil de um pedido define os utilizadores que podem executar pedidos.

Integridade:

SSL configurado para o Web Service.

É isto!