Controle seu Extrator por planilhas

nicolett_yuri — Sun, 22 Nov 2015 16:34:00 GMT

Fala pessoal, beleza?

Essa semana vou passar um dica sobre como controlar sua aplicação de extração através de uma planilha em excel.

Lembra do ETL? Extração, Transformação e Leitura? Extração é a primeira etapa do processo e é responsável por consumir as informações e grava-las em QVDs.

Quero explicar certinho o que iremos propor aqui, pois esta é uma etapa que pode gerar confusão ao seu usuário!

Objetivo

O objetivo principal é criar uma planilha (ou tabela) em que você possa indicar quais serão os QVDs gerados na etapa de Extração. Na planilha poderemos indicar as tabelas, os campos, as regras de restrição, se é uma carga temporal, etc.

O que precisamos deixar MUITO claro é que adicionar uma nova tabela nesta planilha não fará com que uma nova informação apareça na aplicação final, ou seja, a nova tabela apenas vai gerar um novo QVD.

Quero deixar isso bem claro, pois já desenvolvi esse tipo de planilha em alguns clientes que queriam ter mais "autonomia" com determinadas regras de extração, porém essa autonomia é bastante limitada, pois novas tabelas não significam que teremos novas informações nos painéis - Não é porque adicionei a tabela de funcionários na planilha que, automaticamente, teremos um indicador de turnOver na aplicação final.

Parece bobeira, mas sempre que deixamos o usuário com algumas autonomias, precisamos EXPLICAR MUITO BEM até onde essa autonomia chega.

A planilha

Existem muitas formas de como essa planilha pode ficar, o que quero deixar aqui é a minha forma de se fazer, mas pode ser que você tenha criado uma diferente que seja muito melhor (não deixe de compartilhar ou comentar comigo sobre as formas que vocês fazem, isso é muito importante!!).

A planilha vai fazer o controle das tabelas extraídas, porém não vai controlar qual DATABASE pertence e nem o diretório em que o QVD será gerado. Vamos imaginar que essa é uma dica inicial de como você poderá melhora-la ao adaptar ao seu ambiente.

As colunas da planilha

A planilha que trabalharemos será composta pelas seguintes colunas:

Carregar: Responsável por indicar se essa tabela será ou não recarregada na próxima recarga. A ideia é dar o controle ao cliente se essa tabela deve ou não ser gerada na próxima recarga; é útil quando o esta refazendo alguma informação em que não haja necessidade de todas as tabelas serem atualizadas;
Nome QVD: Nome final que o QVD receberá;
Campos: Responsável por indicador todos os campos que serão lidos desta tabela;
Tabelas: Nome da tabela envolvida na query ou das tabelas (caso seja uma query com várias tabelas);
Condições: São as condições do WHERE que a query receberá.
Temporal: Se essa tabela (ou query) será incremental com condicional de data. É importante ressaltar que neste modelo a carga temporal deve ser mensal.

Como preencher cada coluna?

Carregar: Preencher com Sim ou Não;
Nome QVD: Deve-se preencher com o nome do QVD que será gerado. Caso essa query seja temporal, será acrescentado o sufixo "_YYYYMM" ao nome do QVD, indicando de qual ano e mês os dados pertenceml
Campos: A sintaxe é a mesma utilizada no SQL, caso sejam todos os campos, deve-se preencher com * (asterisco), caso sejam alguns campos deve-se utilizar o delimitador vírgula, caso a query seja composta por várias tabelas, o campo deve ser preenchido com o alias da tabela.
Tabelas: A sintaxe é a mesma utilizada no SQL para o FROM, ou seja, caso tenha um tabela deve-se utilizar apenas o nome da tabela, caso a query seja composta por várias tabelas, o nome das tabelas deve possuir o alias e o delimitador será a vírgula;
Condições: A sintaxe é a mesma utilizada no SQL para o WHERE (não é necessário colocar o WHERE na planilha), caso não tenha nenhuma condicional, deixar em branco.Caso a condição seja temporal, é necessário preencher com as variáveis vDataInicio e vDataFim (possuem o formato DD/MM/YYYY). Exemplo: DataCompra >= '$(vDataInicio)' and DataCompra <= '$(vDataFim)'.
Temporal: Preencher com Sim ou Não.

Abaixo exemplos de preenchimento da tabela (para não restar dúvidas):‌

Na primeira linha temos a leitura de todos os campos da tabela cliente que será exportada para o qvd chamado Cliente.qvd;

Na segunda linha temos a leitura de quatro campos da tabela produto que sofre a redução de algumas categorias de produto e será exportado para o qvd chamado Produto.qvd;

Na terceira linha temos a leitura de todos os campos da tabela categoria_produto que será exportada para o qvd chamado Categoria_Produto.qvd, porém não será gerada devido ao preenchimento da coluna "Carregar" com o Não.

Na quarta linha temos uma query de faturamento que é composta por duas tabelas, item_nota_fiscal e nota_fiscal, da tabela nota_fiscal serão carregados todos os campos, porém da tabela item_nota_fiscal serão carregados apenas os campos valor e cod_produto. Essa query possui diversas regras condicionais e também sofrerá carga incremental através do campo dt_emissao. No fim, o QVD gerado será Faturamento_YYYYMM.qvd.

A carga temporal

Em nosso exemplo poderemos escolher se a carga será ou não temporal, e na definição da aplicação a carga temporal será mensal ou seja, sempre trabalhará com informações pertencentes ao mesmo mês.

Em nossa aplicação a carga temporal sempre irá recarregar os dados do mês atual e do mês anterior.

Posso alterar isso? Pode, mas terá que alterar a programação.

O que nosso extrator vai carregar mesmo?

Reforçando: Nosso extrator vai se conectar apenas a uma base de dados (banco de dados) para extrair as tabelas e querys descritas na planilha.Posso incluir uma planilha? Não, para isso você terá que alterar a programação.

A aplicação

Bom, entendemos como funciona cada coluna de nossa planilha e agora vamos entender como a nossa aplicação vai interpreta-la.Basicamente o preenchimento da planilha será dividido em quatro regras, são elas:

Não possui condição e não é temporal;
Possui condição e não é temporal;
Possui condição e é temporal;
Não vai carregar.

Como interpretamos cada uma das regras de acordo com o preenchimento das colunas?

Carregar = Sim, Condições = vazio e Temporal? = Não;
Carregar = Sim, Condições diferente de vazio e Temporal? = Não;
Carregar = Sim, Condições diferente de vazio e Temporal? = Sim;
Carregar = Não.

Entendendo o código

Em primeiro lugar iremos carregar toda a planilha de configuração:

Feito isso, iremos criar uma rotina para percorrer todas as linhas dessa planilha e identificar cada campo da planilha através de uma variável:‌

As variáveis criadas serão:

vCarrega: Recebe o conteúdo da coluna Carregar;
vTabela: Recebe o conteúdo da coluna Tabelas;
vCampos: Recebe o conteúdo da coluna Campos;
vCondicoes: Recebe o conteúdo da coluna Condições;
vTemporal: Recebe o conteúdo da coluna Temporal;
vNomeArquivo: Recebe o conteúdo da coluna Nome QVD;

Script da Regra 1

Essa regra contempla as tabelas que: Não possuem condição e não são temporais.

A identificação da regra é realizada no IF, ou seja, a tabela precisa estar preenchida da seguinte forma:

Carregar = Sim
Condições = Vazio
Temporal? = Não.

O trecho de Select vai receber as variáveis que captaram os valores das planilhas, se olharmos a execução, ficaria mais ou menos assim:

‌

A geração do QVD:

Script da Regra 2

Essa regra contempla as tabelas que: Possuem condição e não são temporais.‌

A identificação da regra é realizada no IF, ou seja, a tabela precisa estar preenchida da seguinte forma:

Carregar = Sim
Condições = Diferente de vazio
Temporal? = Não.

O trecho de Select vai receber as variáveis que captaram os valores das planilhas, se olharmos a execução, ficaria mais ou menos assim:

Script da Regra 3

Essa regra contempla as tabelas que: Possuem condição e são temporais.‌

A identificação da regra é realizada no IF, ou seja, a tabela precisa estar preenchida da seguinte forma:

Carregar = Sim
Condições = Diferente de vazio
Temporal? = Sim.

O trecho de Select vai receber as variáveis que captaram os valores das planilhas, se olharmos a execução, ficaria mais ou menos assim:

Lembre-se que neste caso existe um FOR interno que vai gerar os dados do mês atual e do mês anterior em QVDs separados, conforme imagem abaixo:

Script da Regra 4

Essa regra contempla as tabelas que: Possuem Carregar preenchido com Não.

Essa regra só existe parar gerar uma linha de informação no LOG através do Trace. Essa linha de LOG é importante na verificação de quais tabelas geraram ou não em determinada carga.

No nosso exemplo

No exemplo ficou da seguinte forma:

Faça o download da planilha e da aplicação.

Conclusão

Essa é uma forma muito prática de passar o controle das extrações das tabelas para o nosso cliente. Caso ele precise regerar alguma tabela e outras não ele mesmo conseguirá configurar isso na planilha.

Eu gosto de utilizar essa planilha devido a praticidade que temos para incluir novas tabelas em nossa carga, mas sempre deixe bem claro que incluir uma tabela na planilha não quer dizer que essa informação aparecerá no painel final (como uma mágica rsrs)!

É possível complementar essa planilha indicando qual o banco de dados cada tabela pertence e até incluir leitura de planilhas em excel, basta alterar um pouco o código! Depois posso fazer um post para complementar isso!

Um grande abraço pessoal e até a próxima semana (é sério!! rsrs! Vai chegando o final do ano e parece que o tempo fica mais curto, mas estou me policiando para não deixar de postar!!)

Re: Controle seu Extrator por planilhas

daniel_vale — Mon, 23 Nov 2015 10:38:51 GMT

Muito legal a explicação, parabéns!!

Re: Controle seu Extrator por planilhas

lucianosv — Mon, 23 Nov 2015 15:57:55 GMT

Bom demais.

Grande mestre Yuri, qual a a base original?

Não teria aí um access com as estruturas?

Re: Controle seu Extrator por planilhas

nicolett_yuri — Mon, 23 Nov 2015 16:08:02 GMT

Você fala a base de dados que utilizei?

No meu caso eu tenho um SQL local instalado em que fiz os testes

Re: Controle seu Extrator por planilhas

lucianosv — Mon, 23 Nov 2015 16:12:08 GMT

Isso. Se estivesse num mdb daria pra rodar.

Vou criar um aqui similar.

Vlw

Re: Controle seu Extrator por planilhas

nicolett_yuri — Mon, 23 Nov 2015 16:14:47 GMT

No MDB vai funcionar da mesma forma, talvez o que mude é a forma de chamar a variável do Qlik na condicional WHERE.

Depois que criar comente aqui para vermos seu exemplo

Re: Controle seu Extrator por planilhas

lucianosv — Mon, 23 Nov 2015 17:34:39 GMT

Criei aqui mas acontece algo interessante:

Grava os qvds, mas mesmo eu tendo retirados os drops do script só gera a base de clientes. O peek não está funcionando mas ainda não entendi a razão.

Já quebrei a cabeça mas ainda não resolvi.

Você que publicar com a base?

Re: Controle seu Extrator por planilhas

marcio150972 — Mon, 23 Nov 2015 17:37:57 GMT

Sensacional o conteúdo, parabéns e muito obrigado!

Re: Controle seu Extrator por planilhas

nicolett_yuri — Mon, 23 Nov 2015 17:42:49 GMT

Já achei o erro!

A tabela que estou chamando no Peek é a TmpTabelas, porém essa tabela não existe, o nome correto é Tabelas.

Depois vou corrigir o post!

Obrigado Luciano!!

Re: Controle seu Extrator por planilhas

lucianosv — Mon, 23 Nov 2015 17:45:15 GMT

Show.

Arrumei aqui tb. Funcionando com perfeição.

topic Re: Controle seu Extrator por planilhas in Brasil

Controle seu Extrator por planilhas

Objetivo

A planilha

As colunas da planilha

Como preencher cada coluna?

A carga temporal

O que nosso extrator vai carregar mesmo?

A aplicação

Entendendo o código

Script da Regra 1

Script da Regra 2

Script da Regra 3

Script da Regra 4

No nosso exemplo

Conclusão

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas

Re: Controle seu Extrator por planilhas