24 Abril 2018

SSIS Excel Parser – Business Intelligence

O que é o SSIS Excel Parser e qual a sua utilização em Business Intelligence?

SSIS Excel Parser Business Intelligence

 

O SSIS Excel Parser é um componente implementado via ferramenta SSIS (SQL Server Integration Services) e o seu principal objetivo é extrair dados de ficheiros Excel, de qualquer célula, para a base de dados, de uma maneira mais normalizada, onde mais tarde podem ser consultados via Vistas SQL (Views).

A ideia é implementar um “Template de Leitura” (Modelo) que permita ao componente saber que Folhas do ficheiro Excel devem ser tratadas e, em cada uma, que Células a tratar por sua vez (localizadas por uma posição absoluta: linha e coluna).

Depois, para cada um destes Templates/Modelos, podem-se gerar Instâncias desses mesmos ficheiros Excel, com os dados extraídos para Tabelas normalizadas.

SSIS Excel Parser Business Intelligence

 

Por exemplo, um “ficheiro template” pode ser modelado assim:

SSIS Excel Parser Business Intelligence

 

Por ordem:

Template – Cabeçalho

SSIS Excel Parser Business Intelligence

 

TemplateId – chave primária
Category – categoria
Name – título
ExcelFormatFile – “wild-card” de leitura do ficheiro
Description – descrição

Sheets4Template – Detalhe acerca das folhas a modelar

SSIS Excel Parser Business Intelligence

 

TemplateSheetId – chave primária
FK_Template – chave estrangeira para o template Excel
ExcelSheetName – nome da folha Excel
SetOrder – ordem da folha no ficheiro Excel (posição)
Description – descrição

Cont4Sheets – Detalhe acerca do conteúdo; células localizadas por uma linha e coluna

SSIS Excel Parser Business Intelligence

 

FK_TemplateSheet – chave estrangeira para a folha Excel
DataRegionName – permite agrupar células por nome (opcional)
FieldName – campo
Location_RowNr – linha
Location_ColumnNr – coluna
ExpectedType – tipo esperado
Description – descrição

Instâncias de Template

Modelo SQL:

SSIS Excel Parser Business Intelligence

 

Por ordem:

- Chave da Instância
- Chave estrangeira para o Template
- Nome da Instância
- Data de Movimento
- Nome Ficheiro Excel
- Data Inserção
- Permite histórico de extrações

SSIS Excel Parser Business Intelligence

 

Por ordem:

- Chave primária
- Chave estrangeira para Instância
- Campo
- Valor

Modelar o “Template”:

SSIS Excel Parser Business Intelligence

 

A seguinte Vista SQL foi criada para facilitar na leitura dos “Templates” modelados:

SSIS Excel Parser Business Intelligence

 

Instâncias:

SSIS Excel Parser Business Intelligence

 

A seguinte Vista SQL foi criada para facilitar a leitura dos dados extraídos associados às variadas Instâncias:

SSIS Excel Parser Business Intelligence

 

Vamos testar com um exemplo!

SSIS Excel Parser Business Intelligence

 

 

Primeiro, o Modelo

Exemplo possível de ficheiro Excel em baixo:

Nota: o nome do ficheiro tem o ano (2014) e o nome da instância (TEGEE01)

SSIS Excel Parser Business Intelligence

 

Usa-se um ficheiro Excel para auxiliar na configuração dos Templates, em SQL:

Comandos TSQL são gerados para facilitar a inserção dos registos nas tabelas apontadas acima:

SSIS Excel Parser Business Intelligence

 

Depois, é preciso criar o pacote SSIS (SQL Integration Services)

Cada Modelo/Template deve ser implementado por um pacote SSIS (pode ser otimizado no futuro).

No nosso exemplo, podemos criar o seguinte pacote SSIS:

SSIS Excel Parser Business Intelligence

 

SSIS Excel Parser Business Intelligence

 

Uma “script task” codificada em C# é usada para ler os dados do ficheiro Excel.

O pacote possui uma variável interna que identifica qual o Template a usar:

SSIS Excel Parser Business Intelligence

 

Se um novo ficheiro precisar de ser modelado, o que devemos fazer?

1- Identificar nesse ficheiro Excel-fonte:

a. As folhas Excel a modelar
b. E em cada folha, quais as células a ler

2- Usar o ficheiro Excel auxiliar para gerar comandos TSQL de inserção do Modelo

3- Criar um novo pacote SSI que irá tratar este novo modelo (copy-paste do já existente e adaptá-lo mudando a variável interna. Este comportamento poderá ser melhorado)

É isto.

SSIS Excel Parser Business Intelligence