Baixe o app para aproveitar ainda mais
Prévia do material em texto
Do Banco de Dados Relacional à Tomada de Decisão MVTech · 2 DO BANCO DE DADOS RELACIONAL À TOMADA DE DECISÃO Aprenda como sair do banco de dados convencional e criar um ambiente completo de suporte à tomada de decisão! Diego Nogare Do Banco de Dados Relacional à Tomada de Decisão MVTech · 3 Do Banco de Dados Relacional à Tomada de Decisão MVTech · 4 À minha família. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 5 Primeiramente gostaria de agradecer a todos que, durante estes anos de estudo e aprendizagem de SQL Server, acompanharam meus trabalhos - seja como leitor do blog, como conferencista em congressos ou seminários, como participante em palestras, como aluno em treinamentos oficiais Microsoft ou da NGR Solutions, ou mesmo aluno em cursos de Graduação ou Pós-Graduação, como colegas de profissão e amigos. Tive o imenso prazer de trocar experiências e aprender com cada um de vocês. Não posso deixar de dizer que tive MUITA SORTE em conhecer profissionais de SQL Server de vários os lugares do mundo. Brasil, Espanha, Índia, Estados Unidos, Trinidad e Tobago, México, Costa Rica, Portugal, Chile e Argentina são só alguns desses países. Aprendi com cada um deles um pouco de cultura e costumes locais. Mesmo não conhecendo todos os países em que eles vivem, consegui ter uma visão de como é o mercado de trabalho e como vivem as pessoas destes lugares. Entendendo esse cenário globalizado e olhando principalmente para o Brasil, meu maior objetivo com este livro é permitir que você possa criar um ambiente de Business Intelligence dentro da sua empresa, para melhorar a tomada de decisão dos líderes que trabalham contigo. O objetivo não é aprofundar em assuntos teóricos, que são importantes para um ótimo trabalho. Mas em um momento de objetividade e entrega de resultados, os textos aqui apresentados são focados em exemplos práticos. Para este estudo contínuo que será feito no decorrer do livro, os projetos e bancos de dados que são apresentados tem por objetivo o estudo e o aprendizado de técnicas de mercado para o SQL Server e Business Intelligence. Ambos Databases podem ser baixados no portal Do Banco de Dados Relacional à Tomada de Decisão MVTech · 6 Codeplex (http://www.codeplex.com) procurando-se pelos nomes AdventureWorks2012 e/ou AdventureWorksDW2012, ou por meio do acesso direto da página do projeto (http://www.msftdbprodsamples.codeplex.com). Estes Databases são mantidos pela comunidade e também time de desenvolvimento da Microsoft. Os exemplos criados durante o livro podem ser baixados diretamente do meu site, acessando o link: http://www.diegonogare.net/Livro_BI_2014.zip Quero agradecer aos meus nobres amigos Thiago Zavaschi que escreveu o prefácio desta publicação, Luan Maciel Moreno que revisou tecnicamente os capítulos garantindo uma conclusão práticas dos exemplos e Renato Cairo por fazer a validação gramatical dos textos. Também quero deixar registrada a colaboração da minha esposa Laysa Nogare. Foi ela quem me incentivou e, mesmo esperando nossa primeira filha, permitiu que este livro saísse do zero e chegasse até você com uma sequência lógica de ideias e com uma forma de escrita mais adequada. Sem ela, este livro seria um grande post de blog! Obrigado, Laysa! Do Banco de Dados Relacional à Tomada de Decisão MVTech · 7 Para uma corporação ter sucesso nos negócios, um dos fatores mais importantes é a tomada de decisões de maneira assertiva. Para tal, é necessário bom embasamento sobre o ritmo do negócio sobre diferentes visualizações. Este conhecimento é originado por meio de ferramentas para análise dos dados gerados pela própria empresa. Ferramentas para este fim são necessárias para análises poderem ocorrer sobre grandes volumes de dados, pois a análise manual neste caso pode levar a erros e decisões incorretas. Existem algumas “formas diferentes” de se fazer BI (Business Intelligence). Estas podem ser agrupadas em dois grandes grupos, um chamado de self-service BI e outro de BI corporativo. O presente livro é focado no que chamamos de BI corporativo, que serve principalmente para as decisões de negócio da corporação como um todo. Neste livro é apresentado de forma prática como lidar com as principais dificuldades iniciais que deverão ser enfrentadas (muitas das quais enfrentadas por mim quando comecei nesta área) por quem está se aventurando nesta jornada chamada de Business Intelligence (Inteligência de Negócio). O Diego começa fazendo uma introdução ao conceito principal de BI, bem como outros conceitos que irão lhe acompanhar no início, meio e final de seus projetos - ou seja, pelo resto da vida. Serão ferramentas, conceitos, siglas e estruturas novas... não desista! É comum acreditar que o volume de informações a serem aprendidas é grande. E de fato é, mas este livro deve te ajudar a dar os primeiros passos neste mundo de consultas multidimensionais, bases de dados desnormalizadas, relatórios, indicadores, etc. Conceitos são importantes - tão importantes quanto aprender a sair da inércia e ganhar tração quando se está estudando ou Do Banco de Dados Relacional à Tomada de Decisão MVTech · 8 desenvolvendo um projeto desta natureza. Para isso, é apresentado de maneira bastante detalhada como ir “do banco relacional à tomada de decisão”. Um projeto de BI envolve diversas etapas. Começamos pela extração dos dados dos sistemas de produção, realizada por ferramentas que realizam o que chamamos de processo de ETL (Extract, Transformation and Load) a ser realizada pelo Integration Services (SSIS) no nosso caso, para constituirmos uma nova base conhecida como Data Warehouse (DW). Esta base é o que será usado de fato para alimentar nossa ferramenta analítica (SSAS) e posteriormente ser entregue de diferentes formas. Entre elas, podemos citar os relatórios através do Reporting Services (SSRS). Todas estas etapas estão retratadas na páginas seguintes. Tive a oportunidade de ler os originais do livro antes do público em geral e sei que ele pode ajudar nas tarefas simples, porém comuns nesse mundo. Obrigado pelo convite, meu amigo! Agora convido a todos a desfrutarem deste livro para aprender a como criar um BI na sua empresa. E que os desafios enfrentados se tornem mais amigáveis depois de lê-lo. :-) Thiago Zavaschi – Premier Field Engineer em Business Intelligence – Microsoft Do Banco de Dados Relacional à Tomada de Decisão MVTech · 9 O Minha Vida (http://www.minhavida.com.br) é uma empresa com um grande propósito: melhorar a qualidade de vida da população. Queremos ser capazes de despertar nas pessoas o cuidado com a sua própria saúde. E quando falamos em saúde, nos referimos não só à prevenção ou ao tratamento de doenças, mas também a pequenas mudanças de hábitos capazes de transformar positivamente o dia a dia das pessoas. Queremos fazer diferença na vida das pessoas! Pensando nisso, nosso time de tecnologia criou o MVTech, uma iniciativa para disseminar conhecimento através de iniciativas de seus colaboradores, buscando o aprimoramento do mercado nacional. Com diversas ações como: artigos técnicos em blogs; matérias para portais; respostas em foruns de discussão, palestras em eventos, eventos presenciais e online, etc… afinal, somos apaixonados pelo que fazemos! por que não compartilhar nossa paixão?A criação do MVTech dá um passo adiante nesta linha de compartilhamento de conhecimento, passando a existir um canal oficial onde poderão ser encontrados grandes conteúdos. Seja bem vindo ao MVTech Alexandre Tarifa – Diretor de Tecnologia – Minha Vida Do Banco de Dados Relacional à Tomada de Decisão MVTech · 10 APRESENTAÇÃO __________________________________________ 5 PREFÁCIO _______________________________________________ 7 MVTECH ________________________________________________ 9 SUMÁRIO ______________________________________________ 10 INTRODUÇÃO AO DATA WAREHOUSE _________________________ 14 TABELAS DIMENSÕES _____________________________________ 21 DIMENSÃO DE TEMPO ____________________________________ 25 TABELAS FATOS __________________________________________ 27 MEASURES _____________________________________________ 30 INTRODUÇÃO BUSINESS INTELLIGENCE _______________________ 32 IMPORTANDO DADOS COM SQL SERVER 2014 IMPORT AND EXPORT DATA __________________________________________________ 44 IMPORTANDO DADOS COM SQL SERVER INTEGRATION SERVICES 2014 ______________________________________________________ 59 Do Banco de Dados Relacional à Tomada de Decisão MVTech · 11 TIPOS DE CONEXÕES NO SSIS _______________________________ 72 PROCESSANDO O CUBO ATRAVÉS DO SSIS _____________________ 85 PUBLICANDO SEU PACOTE DO SSIS ___________________________ 94 CRIANDO UM JOB PARA AUTOMATIZAR A EXECUÇÃO DO PACOTE __ 101 CRIANDO SEU PRIMEIRO CUBO COM O SSAS __________________ 110 COMO CRIAR UMA MEASURE CONVENCIONAL NO SSAS _________ 118 COMO CRIAR UMA MEASURE CALCULADA NO SSAS _____________ 122 MANIPULANDO MEMBROS E ATRIBUTOS DE DIMENSÕES ________ 128 CRIANDO UMA HIERARQUIA DE MEMBROS DA DIMENSÃO _______ 139 COMO CRIAR E USAR KPI NO SSAS __________________________ 148 INTERNACIONALIZANDO SEU PROJETO COM O TRANSLATE _______ 156 PROCESSANDO E PUBLICANDO O CUBO ______________________ 163 PROCESSAMENTO DO SSAS, DIRETO AO PONTO! _______________ 173 ACESSANDO O CUBO ATRAVÉS DO EXCEL 2013 ________________ 177 REPORTING SERVICES CONFIGURATION MANAGER _____________ 186 Do Banco de Dados Relacional à Tomada de Decisão MVTech · 12 SHARED DATA SOURCE E SHAREDDATASET ____________________ 195 CONTRAINDO E EXPANDINDO RESULTADOS, COMO HIERARQUIAS _ 202 RELATÓRIO COM LINHAS ZEBRADAS _________________________ 220 PUBLICANDO E ACESSANDO O RELATÓRIO ____________________ 228 COMO INSERIR GAUGE NO RELATÓRIO _______________________ 234 PARAMETRIZANDO VALOR MÁXIMO NO GAUGE _______________ 247 COMO INSERIR 2 PONTEIROS NO GAUGE _____________________ 258 DRILLDOWN NO REPORTING SERVICES _______________________ 266 PERSONALIZANDO PALETA DE CORES DO GRÁFICO _____________ 280 CONCLUSÃO ___________________________________________ 286 Do Banco de Dados Relacional à Tomada de Decisão MVTech · 13 CONCEITOS DE BUSINESS INTELLIGENCE E DATA WAREHOUSE Do Banco de Dados Relacional à Tomada de Decisão MVTech · 14 Resumindo, o Data Warehouse é a reorganização dos dados estruturados de forma desnormalizada. Ele visa a melhoria contínua na entrega de informação ágil e precisa para os tomadores de decisões. O mais importante em um projeto de Data Warehouse é responder questões das áreas de negócios, sempre buscando o alinhamento com a proposta de valor da empresa. Diversas publicações acadêmicas denominam o Data Warehouse como um Sistema de Suporte a Decisão (e eu particularmente concordo com esta denominação), já que a modelagem do DW é usada com foco em agilizar a análise e permitir uma melhor tomada de decisão. É um desafio para profissionais de banco de dados relacional mudar a forma de pensar e possibilitar a criação do Data Warehouse. Visto que todos os estudos e casos de sucesso da utilização de dados normalizados, evitando redundância e criando os relacionamentos entre as tabelas, precisam ser modificados para modelar os dados de forma desnormalizada e redundante. Ao se trabalhar com desnormalização dos dados, não é a diminuição de espaço em disco utilizado que se busca, mas sim a velocidade na resposta. Quando os dados estão todos redundantes e calculados na modelagem do Data Warehouse, o espaço utilizado pelo armazenamento destes dados aumenta. Isso acontece porque os dados se repetem diversas vezes, caracterizando a desnormalização e melhorando a performance dos resultados das consultas. O custo com processamento, memória, I/O no disco, entre outros, tende a aumentar, mas o benefício de ter a resposta para os tomadores de decisão deve ser levado em conta para equilibrar a comparação do custo versus benefício. Na maioria dos casos o orçamento que a área de TI possui é um rateio do orçamento das áreas de negócios que consomem serviços da área de TI. Uma estratégia usada em diversas empresas é Do Banco de Dados Relacional à Tomada de Decisão MVTech · 15 dividir os custos de hardware – provenientes da aquisição de novos servidores – entre a área de negócio solicitante e a área de TI. Ao se criar o DW, é preciso ter em mente quais são as questões que precisam ser respondidas para a área de negócios que solicitou o projeto. Possuir um relacionamento saudável com a área de negócios é vital para que se consiga modelar as Dimensões e Fatos, que serão explicados mais à frente, do projeto condizente com suas necessidades. É inútil desenvolver um projeto de BI, que na maioria das vezes é longo e custoso para a empresa, se ele não responder nenhuma das perguntas que precisam ser respondidas para a área de negócios e não auxilia as tomadas de decisões. A montagem do Data Warehouse geralmente consome mais tempo do que as outras tarefas que serão desenvolvidas. Pesquisas apontam que o ETL (Extraction, Transformation and Loading, ou Extração, Transformação e Carga) consome em média 70% do tempo do projeto. Este processo é responsável por extrair dados de uma ou diversas fontes (Extraction); transformar, limpar e adequar os dados destas fontes em algo útil para o projeto (Transformation); e por fim carregar esses dados transformados em uma outra base de dados (Loading). Por meio do processo de ETL, é possível conectar-se a diversas fontes de dados, seja SQL Server, Oracle, DB2, arquivos XML, TXT, CSV, etc. É possível também escrever em diversas fontes de dados, inclusive os citados acima. Como explicado na apresentação, vamos criar o ambiente de DW consumindo a base de dados Adventure Works, que é um SQL Server relacional. A modelagem do Data Warehouse, apesar de seguir um conceito totalmente diferente, utiliza o mesmo motor de dados relacional. O motor analítico só é utilizado quando se cria o projeto do Analysis Services, e este sim é um motor diferente do ambiente relacional. No desenho da arquitetura de uma Dimensão do Data Warehouse, existem duas modelagens que são amplamente estudadas e utilizadas: Snowflake Schema e Star Schema. Elas se diferem pela modelagem da estrutura do ambiente e desempenho no processamento. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 16 As duas modelagens serão explicadas com mais detalhes nos tópicos seguintes, mas explicando em linhas gerais o Star Schema é a modelagem na qual as dimensões se conectam diretamente à fato sem nenhuma sub-dimensão ou dimensão intermediária com os dados redundantes e as tabelas desnormalizadas. Já na modelagem Snowflake, quando observa-se a Fato e seus relacionamentos, é possível encontrar sub-dimensão ou dimensão intermediária entreas tabelas, e uma breve lembrança de modelagem normalizada. Quando se faz a modelagem do Data Warehouse, nem tudo que existe na tabela de origem da base de dados relacional deve ser carregada nas Dimensões. Uma informação muito importante para o ambiente relacional pode ser completamente desnecessária para o DW. Imagine um cenário no qual se analisa o poder de compra de Homens e Mulheres do ponto de vista de um determinado produto. Neste cenário pode-se levar em consideração informações como Bairro, Cidade, Estado, País e Sexo, mas são desnecessários os dados como E-Mail ou Telefone. As tabelas Dimensões armazenam os dados detalhados de um grupo específico, consumindo dados de uma ou mais tabelas. Sendo assim, a Dimensão de Cliente possui os dados relativos a clientes, enquanto a Dimensão de Pagamento possui os dados de pagamentos e assim por diante. Estas dimensões não precisam se comunicar entre si, podendo se comunicar exclusivamente com a tabela Fato. Nesta tabela Fato é onde se faz o relacionamento para encontrar um Cliente que fez um determinado Pagamento. Também é com base nas Dimensões que as Medidas (Measures) são derivadas ou desagrupadas (especializadas). Estas Medidas são agrupadores calculados com base em informações existentes na tabela Fato, mas não são criadas dentro do Data Warehouse. As medidas são criadas dentro da ferramenta que faz, de fato, a criação do ambiente OLAP (On Line Analytical Processing). Quando a modelagem da tabela Fato possui uma coluna em que será calculada e gerada uma Medida, são utilizadas outras colunas da mesma tupla (conjunto de colunas de uma Do Banco de Dados Relacional à Tomada de Decisão MVTech · 17 mesma linha) para realizar a derivação e/ou desagrupamento desta Measure (medida), permitindo chegar no detalhe daquele agrupador. Para facilitar o entendimento, imagine um cenário onde uma tabela Fato possui os campos CodCliente, CodProduto e Valor. A coluna CodCliente faz relacionamento à tabela Dimensão de Clientes, a coluna CodProduto faz relacionamento à tabela Dimensão de Produtos e a coluna Valor é um dado redundante da tabela Dimensão de Produtos, justamente para permitir a criação da medida na tabela Fato. Quando se cria o cubo, por exemplo, no Analysis Services, esta coluna Valor da tabela Fato será calculada e somará todos os valores, chegando a um somatório de todas as vendas realizadas, independente do produto ou cliente. Esta medida poderá ser derivada e/ou desagrupada por uma das outras colunas da tupla, neste caso somente o Produto ou o Cliente; No primeiro momento, quando se analisa somente os valores de vendas, é apresentado todo o somatório da coluna Valor, sem nenhum filtro ou derivação. Para esta derivação, quando a visão analítica precisa consultar os dados detalhados por produtos, será apresentado quanto cada produto rentabilizou, dividindo o somatório inicial em quanto cada produto individual contribuiu para o todo. Se a visão analítica consulta os dados pela visão de clientes, será apresentado o valor que cada cliente pagou, dividindo o total em cada um dos clientes. Este é o processo de derivar e/ou desagrupar os dados de uma Measure. As medidas normalmente são baseadas nas informações contidas na tabela fato - sendo os consolidadores (agregadores) baseados em um campo que recebe uma operação de cálculo. Estas agregações padrões podem ser de SOMA, MÉDIA, VALOR MAXIMO, VALOR MÍNIMO, CONTAGEM DE LINHAS, PRIMEIRO VALOR, ULTIMO VALOR, entre outros. E é bom lembrar que as medidas são importantes para o projeto de BI, mas não são calculadas no momento da criação do Data Warehouse. São criadas na ferramenta de análise, como o SQL Server Analysis Services. Na tabela Fato o próprio nome já diz o que possui: fato! É nesta tabela que se faz o cruzamento de todas as chaves das dimensões que Do Banco de Dados Relacional à Tomada de Decisão MVTech · 18 representam um Fato, único e verdadeiro, sobre a análise que precisa ser feita. A tabela Fato é construída, na maioria das vezes, somente com os campos-chave das tabelas Dimensão, para criar os relacionamentos, e com os campos que consistirão nas Measures que serão calculadas. Não é aconselhável adicionar colunas que não se enquadram nestas duas características: Chaves Estrangeiras de tabelas Dimensões e Colunas que serão Medidas. Colunas de texto como nome ou endereço não fazem sentido para uma tabela Fato, mas podem ser extremamente importantes para as dimensões. Para exemplificar, lembre de algum cenário com ambiente relacional; cada tabela Dimensão há uma Chave Primária (Primary Key – PK) e existem várias Dimensões no banco de dados. Na tabela Fato as colunas são Chaves Estrangeiras (Foreign Key – FK) referenciando as colunas chave das Dimensões, criando uma modelagem “relacional”. A Fato consiste nos dados de relacionamento entre todas as Dimensões, fazendo com que seu tamanho aumente se comparado ao mesmo ambiente em uma modelagem relacional, com os dados normalizados. Como visto anteriormente, a desnormalização de tabela aumenta o tamanho armazenado em disco, mas por outro lado nos permite uma resposta muito mais rápida e apropriada para os ambientes de Data Warehouse. Para melhor exemplificar a diferença entre a modelagem Relacional e o Data Warehouse, acompanhe as figuras abaixo. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 19 Ambiente relacional, com a tabela SalesPerson e todos seus relacionamentos, do banco de dados AdventureWorks2012 Veja que na imagem acima, no ambiente relacional, as tabelas estão normalizadas evitando redundância de dados e garantindo integridade referencial por meio de chaves primárias (PK) e estrangeiras (FK). Também é possível observar que a modelagem permite que tabelas se relacionem com quantas tabelas forem necessárias por causa da normalização. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 20 Ambiente de Data Warehouse, com a tabela fato FactFinance e seus relacionamentos com as dimensões, do banco de dados AdventureWorks2012DW Do Banco de Dados Relacional à Tomada de Decisão MVTech · 21 Já nesta modelagem de Data Warehouse, existe uma tabela central conhecida como Tabela Fato, que possui relacionamento com outras tabelas conhecidas como Dimensões. É importante reparar que nesta modelagem a tabela Fato possui relacionamento com todas as Dimensões, mas nenhuma dimensão se comunica diretamente com outra, a não ser por meio da tabela Fato. Os próximos tópicos explicam em detalhes as Dimensões e suas as modelagens. Olhando e comparando a modelagem Relacional e Data Warehouse, pode-se observar que ambas estão no mesmo motor de banco de dados, estruturalmente como um OLTP (Online Transaction Processing). Se as duas modelagens estão nesta estrutura, por que separar? É neste momento que a modelagem e o propósito faz toda a diferença. As dimensões do DW são, por natureza, desnormalizadas e largas. Toda hierarquia de relacionamentos e integridade de dados que se apresenta no modelo relacional é repensado. Nas dimensões, os dados de uma mesma categoria devem ser estruturados para representar a entidade como um todo, por categoria entenda o grupo contextual no qual aquele tipo de informação se encaixa por exemplo: Clientes; Produtos; Pagamentos; etc. Em uma modelagem relacional, atendendo à 3ª forma normal, pode-se ter uma tabela para Logradouro (que possui um relacionamento para Cidade e que por sua vez se relaciona com uma outra tabela para localizar o Estado). Aprofundando um poucomais, a tabela de Fornecedor se relaciona à Logradouro, assim como a tabela Cliente. Quando se modela uma Dimensão, é pensado no problema da área de negócios solicitante e então os dados são remodelados para atender essa demanda. Olhando este mesmo exemplo apresentado a pouco, somente duas tabelas dimensões seriam modeladas, uma para Fornecedor e outra para Cliente Do Banco de Dados Relacional à Tomada de Decisão MVTech · 22 e estas tabelas teriam os campos Logradouro, Cidade e Estado, mesmo que estes dados se repetissem várias vezes para um mesmo Fornecedor ou Cliente. Este processo de desnormalização é enraizado nas tabelas dimensões. Modelagem normalizada entre as tabelas Cliente, Fornecedor, Logradouro, Cidade e Estado. Ambiente de OLTP Do Banco de Dados Relacional à Tomada de Decisão MVTech · 23 Modelagem desnormalizada para criação das dimensões de Fornecedor e Cliente. Ambiente de DW As tabelas Dimensões fornecem os campos–chave, que serão utilizados para popular a tabela Fato, e os campos que permitirão derivar as medidas. Cada tupla da dimensão deve possuir uma chave única, mas existem algumas situações onde a tabela original não possui esta chave única. Para solucionar este problema, uma técnica chamada Chave Substituta (Surrogate Key) deve ser usada, garantindo a maior granularidade possível. Por padrão, como a tabela está desnormalizada, a granularidade encontrada é entre média e baixa, dificultando a localização de dados únicos. Se a tabela Dimensão é desnormalizada, quer dizer que não se pode criar uma modelagem para Data Warehouse utilizando tabelas Dimensões normalizadas? Sim, apesar de não ser tão comum encontrar no mercado, é possível trabalhar com dados normalizados no DW. Essa técnica existe e é utilizada em alguns cenários. Conceitualmente é chamada de Snowflake Schema e será apresentada com detalhes nos próximos tópicos. Uma característica forte desta modelagem é construir Dimensões que possuem Sub-Dimensões ou relacionamento com outras Do Banco de Dados Relacional à Tomada de Decisão MVTech · 24 Dimensões antes de se comunicar com a tabela Fato, se parecendo com uma modelagem normalizada. Diferentemente desta modelagem normalizada é a amplamente utilizada e estudada Star Schema, que é composta por Dimensões específicas que agregam uma ou mais tabelas para um segmento de dados (Clientes; Produtos; etc). Desnormalizando os dados e contemplando mais colunas dentro da tabela Dimensão, criando um único relacionamento entre a tabela Dimensão e a tabela Fato, sem existir Sub-Dimensões ou relacionamento direto entre as Dimensões. É importante ressaltar que, independente do modelo de criação do seu Data Warehouse, seja Star ou Snowflake Schema, os resultados obtidos devem obrigatoriamente ser os mesmos. O que diferencia estas modelagens são os benefícios existentes na normalização/desnormalização, sendo que os dados desnormalizados são mais simples para entendimento da área de negócios solicitante - uma vez que não é necessário criar relacionamentos entre outras dimensões para conseguir ter os retornos necessários. Lembre do exemplo do logradouro de Cliente e Fornecedor, apresentado alguns parágrafos acima. Outra grande diferença destas técnicas é que em uma modelagem Star Schema não é possível criar um relacionamento 1- para-muitos, já em um cenário normalizado (Snowflake Schema) é possível conseguir este tipo de relacionamento. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 25 Toda tabela Fato possui ao menos uma análise feita com base em intervalos de tempo, seja data de cadastro de cliente, data de venda do produto, data de pedido ao fornecedor, entre outros. Estas datas armazenadas podem ocupar um espaço significativo dentro das Dimensões e da Fato, visto que um campo do tipo Datetime, que armazena os dados de data e hora com 3 casas de milissegundos, ocupa 8 bytes de tamanho em cada registro. Fazendo um cálculo rápido, em 1 milhão de registros com uma coluna do tipo Datetime, são mais de 7 MB armazenados no banco de dados. Para saber o tamanho de alguma expressão em bytes, utilize o comando DATALENGTH, passando a expressão com parâmetro, como exemplificado abaixo. Tamanho do DataTime: select DATALENGTH(getdate()); Uma técnica amplamente usada é criar uma tabela Dimensão que armazena diversas colunas calculadas com base em uma coluna de Data, com um intervalo que engloba desde os primórdios dos dados que serão analisados até a data presente ou uma data futura, criando uma ocorrência única para cada dia. Colunas calculadas como: Dia da Semana, Semana do Mês, Semana do Ano, Trimestre, Nome do Mês, Mês do Ano, se são informações de calendário convencional (iniciando em 01/Janeiro e terminando em 31/Dezembro) ou se é calendário fiscal (geralmente utilizado por empresas Americanas, começando no dia 01/Julho e terminando em Do Banco de Dados Relacional à Tomada de Decisão MVTech · 26 30/Junho) podem ser armazenadas evitando o cálculo na hora do processamento ou consulta da informação. Uma PK sequencial é criada nesta tabela e permite que a tabela Dimensão de Tempo faça um relacionamento com a Fato. Ao invés de armazenar um campo do tipo Datetime na tabela Fato, é armazenado um campo do tipo inteiro que faz o relacionamento com a Dimensão, mesmo conceito de PK e FK já explicado anteriormente. Uma vez que a tabela Dimensão de Tempo foi criada especificamente para trabalhar com as Datas, o processo de carga das outras Dimensões também pode ser alterado para usufruir deste benefício, substituindo os campos de Datetime por essa coluna-chave da Dimensão de Tempo, sem criar o relacionamento físico entre as tabelas. O único relacionamento entre esta dimensão e a Dimensão de Tempo continua sendo a tabela Fato. Imagine que sua empresa iniciou as atividades em 01/Janeiro/2010. Não é necessário que você crie a Dimensão de Tempo com dados anteriores à esta data. Isso armazenaria informação desnecessária (e inútil, neste caso), deixando seu Data Warehouse com mais informação do que precisa. Com essa informação, já sabemos qual é a data mínima que será armazenada na Dimensão de Tempo e agora nos resta definir qual é a data final que estará nesta tabela. Na teoria, nenhum cadastro, venda ou qualquer outra interação com seus dados será realizada em uma data futura. Isso nos dá a condição de inferir que a data máxima desta Dimensão de Tempo será hoje. Limitando os dados a hoje, obrigatoriamente toda vez que um novo dia surge, deve-se incluir os dados deste dia nesta dimensão, então os cadastros ou vendas deste dia terão um correspondente na Dimensão de Tempo. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 27 Toda informação sumarizada na tabela Fato deve responder uma necessidade de negócios. Da mesma forma que os dados estão redundantes nas Dimensões, eles também o estarão na Fato. A tabela fica grande por si só, então deve-se evitar o armazenamento de dados desnecessários que só ocuparão espaço em disco e não ajudarão na tomada de decisão. Os dados armazenados dentro da Fato são as combinações das chaves estrangeiras da Dimensões e os valores a serem calculados nas Measures. Por prática de mercado, a chave primária da Fato é o conjunto de colunas dos relacionamentos das Dimensões com a Fato, gerando uma Chave Composta por todas estas colunas, constituindo um conjunto único de dados. O próprio nome desta tabela já diz o que ela representa, um Fato. Em miúdos, o que vem a ser um fato? Quandose junta os dados de todas as dimensões com os valores das measures, pode-se dizer que tem um fato. Por exemplo, um fato é a junção dos códigos de Cliente, Produto, Fornecedor, Região, Loja, etc. A junção de cada um deste códigos pode responder a questões como, por exemplo, se “o cliente Diego Nogare comprou o produto Treinamento de BI do fornecedor NGR Solutions na região Sudeste na loja OnLine”. Este cruzamento de dados é único, consistindo num fato. Qualquer alteração neste conjunto de códigos, que depois podem coletar os dados de detalhes nas dimensões, representa uma informação única, real e verdadeira, sobre o que está se analisando para a área de negócios. Volto à bater na tecla: o projeto de Business Intelligence deve ser totalmente focado na área de negócios e deve responder a questões que ajudarão as áreas a tomar melhores decisões. Veja alguns exemplos de perguntas que as áreas de negócios pedem, e vamos identificar como separar itens que são Dimensões e outros que serão Measures, para por fim, criar a Fato contendo todos estes dados. 1. Total de Faturamento por equipe de Vendas Do Banco de Dados Relacional à Tomada de Decisão MVTech · 28 2. Lucro de uma linha de produto 3. Quantidade de Pedidos por Produto 4. Custo do Produto 5. Total de Faturamento por Cliente 6. Lucro por Região 7. Total de Faturamento por Cidade Olhando estas perguntas, uma a uma, é possível identificar as necessidades da área solicitante e, com isso, montar a estrutura que será a base para o sistema de suporte à tomada de decisão. Na primeira pergunta, Total de Faturamento é uma measure, e Equipe de Vendas é uma Dimensão. Reparem que é possível dividir o Faturamento total de todas as vendas por equipes de vendas, mas não o contrário. Na segunda pergunta, a measure é o Lucro e a dimensão é a Linha de Produto. Na terceira, Quantidade de Pedidos é a measure e Produto é a dimensão. Reparem que esta dimensão é a mesma entidade da segunda pergunta, então neste caso você deve garantir que ambas respostas sejam atendidas pela estrutura da dimensão de produtos. Na questão do Custo do Produto é o mesmo cenário, compartilhando da mesma entidade da dimensão, e uma nova measure é acrescida. Já em Total de Faturamento por Cliente, a measure de Total de Faturamento (anteriormente respondida por Equipe de Vendas) já existe e agora precisa ser especializada por Cliente. Para isso, basta adicionar a nova dimensão. Lucro por Região é o mesmo caso da pergunta anterior, em que se compartilha a measure mas não a dimensão. Já no caso da última pergunta (Total de Faturamento por Cidade), ambas respostas já estão na modelagem de acordo com as perguntas anteriores, basta garantir que a entidade Região contenha a informação de Cidade que a área de negócios precisa. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 29 O resultado seria assim: Dimensões: Equipe | Produto | Cliente e Região Measures: Total de Faturamento | Lucro | Quantidade de Vendas e Custo Do Banco de Dados Relacional à Tomada de Decisão MVTech · 30 Measures são os valores calculados e armazenados dentro da Fato, que serão desagrupados de acordo com as Dimensões, para especializar uma informação. Existem diversos cálculos-padrão que são passíveis de serem utilizados nas measures. Os mais comuns, como já citados, são SOMA, MÉDIA, VALOR MAXIMO, VALOR MÍNIMO, CONTAGEM DE LINHAS, PRIMEIRO VALOR, ULTIMO VALOR. Isso não quer dizer que não se pode calcular nenhuma measure específica para sua tomada de decisão. Os cálculos específicos como Lucro, por exemplo, podem ser calculado com base em uma fórmula que soma os custos de produção do item, mais os impostos, e subtrai do valor de venda, gerando o resultado esperado. Vale lembrar, mais uma vez, que este processo é realizado dentro do SQL Server Analysis Services e não dentro do Data Warehouse. Neste momento, o importante na modelagem é garantir que os dados necessários para realizar os cálculos das medidas, seja padrão ou específico, estejam contemplados nas colunas existentes na tabela Fato e que tenham as correspondências nas Dimensões. As measures podem ser divididas em grupos, de acordo com o grau de abrangência que podem ter nas especializações das informações. Estes grupos são chamados de Additive, Semi-Additive e Non-Additive. O grupo Additive consiste em uma Measure que pode ser segmentada por todas as dimensões existentes no projeto. Isso significa que todas as dimensões podem derivar o valor da Measure, deixando o resultado bem especializado e melhorando ao máximo a tomada de decisão. Um exemplo é o Total de Vendas, que pode ser dividido pelas dimensões Cliente, Fornecedor, Produto, Loja, Região, etc. Já o grupo Semi-Additive permite a derivação do dado por algumas dimensões, mas não por todas. Se uma dimensão não tem Do Banco de Dados Relacional à Tomada de Decisão MVTech · 31 “habilidade” para derivar aquele dado, este já caracteriza uma measure Semi-Additive. Para exemplificar, imagine uma measure que representa o Valor em Estoque. Esta informação pode ser dividida por Produto, Loja, Fornecedor, Região, mas não pode ser especializada por Cliente. Não é possível saber qual cliente irá comprar algum produto, para saber o quanto tem em estoque daquele item. Por fim o grupo Non-Additive é praticamente um tipo de measure que não pode ser especializado por nenhuma dimensão. Este grupo peculiar, com base nas definições da literatura, nem deve ser considerado uma measure, visto que não se pode derivar ou especializar uma dimensão com base nos seus valores. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 32 Quem nunca viu nada de BI com o SQL Server pode se sentir um pouco perdido com os exemplos práticos de mais à frente, mas esta parte introdutória ajudará a dar uma visão interessante e mostrará todo o caminho para entender como implantar BI com essa poderosa ferramenta da Microsoft. Para nivelamento do conhecimento, é importante saber separar os “produtos” dentro do SQL Server. Estes produtos são tecnologias dentro da plataforma de Business Intelligence que fazem trabalhos diferentes umas das outras, mas estão todas interligadas. Então vamos lá: Database Engine: o mecanismo dentro do SQL Server responsável pelo armazenamento relacional dos dados, sendo o motor OLTP (On-Line Transaction Processing). Nesta parte do produto é que se criam as tabelas, índices, procedures, functions, enfim, todos os dados transacionais e do Data Warehouse, seja normalizado ou desnormalizado. É o que todos chamam de banco de dados. Analysis Services (SSAS – SQL Server Analysis Services): a tecnologia dentro do SQL Server que permite criar os cubos, dimensões e tabelas fatos dos dados a serem analisados. Aqui ficam os dados redundantes, com um monte de cruzamento já calculado para você não precisar fazer JOINS em tempo de execução e, com isso, devolver os dados rapidamente para quem utiliza a plataforma de BI. A grosso modo, quando fala-se em BI, geralmente está se referindo diretamente a esta ferramenta. Integration Services (SSIS – SQL Server Integration Services): Esse componente nos permite conectar um lugar qualquer de origem e extrair seus dados, podendo trabalhar em cima deles para “arrumar” alguma estrutura que não esteja de acordo com o que precisamos e depois podemos carregar (salvar) essas informações em algum outro destino. Geralmente é usado para ler informações de origens como arquivos TXT, Do Banco de Dados Relacional à Tomada de DecisãoMVTech · 33 XML ou outros bancos de dados e armazenar estas informações no SQL Server que servirá como base para o Data Warehouse. Em um segundo momento, servirá também para rodar o cubo e ter todas as informações centralizadas no BI. Por último temos o Reporting Services (SSRS – SQL Server Reporting Services): o serviço de gerenciamento de relatórios do SQL Server que permite criar e apresentar para os usuários relatórios ricos e completos de informações reais que estão armazenadas no SQL Server ou Analysis Services. A criação de relatórios com o Reporting Services é bem tranquila, garantindo uma produtividade muito grande para quem utiliza esse gerenciador. Agora vamos relembrar algumas terminologias e formas de trabalho. Modelagem Multidimensional: Quando estudamos SQL Server, aprendemos a criar tabelas com alguns níveis de normalização com o intuito de deixar nosso banco de dados sem informações redundantes desnecessária e com menor quantidade de armazenamento possível, diminuindo quantidade de espaço em disco utilizado. Já com BI temos que pensar exatamente no contrário, em dados desnormalizados. Isso porque temos que colocar os dados em tabelas Fatos e em Dimensões! Tabelas Fato: São os dados que extraímos do banco de dados e agrupamos por assuntos específicos das Dimensões e Measures. Para ilustrar vamos imaginar um processo de venda de produto. Vamos usar Livros, mas poderia ser qualquer outra estrutura. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 34 Modelagem Normalizada Essa representação mostra um diagrama de dados relacionais com as tabelas normalizadas e evitando redundância de dados. Quando vamos atuar com BI e precisamos criar as Tabelas Fato, separamos esses dados pertinentes à informações agrupadas por entidades, como Vendas e Clientes. Pode haver outras tabelas Fato em um mesmo cubo, vai depender do que você precisar. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 35 Ítens passíveis de cálculo da Tabela Fato Na tabela Fato_Venda, deve-se consultar todas as Dimensões cujas informações precisam ser coletadas e escrever nessa Fato. Os dados que alimentam a Dimensão e depois, por sua vez, a Fato podem estar em outro banco e até outro servidor. Não importa, porque o importante são os dados que estarão agrupados pela entidade Vendas, neste exemplo acima. Estas informações serão processadas e armazenadas na nova estrutura de Data Warehouse. Veja que algumas informações vêm de origens diferentes e outras são simplesmente calculadas para atender a necessidade da aplicação da área de negócios. Tabelas Dimensões: São tabelas com informações mais detalhadas que complementam as tabelas Fatos e geram uma (ou várias) tabelas para armazenar esses novos dados redundantes. As dimensões nos possibilitam trabalhar com dados estatísticos de uma determinada informação que precisamos analisar. Estes dados serão calculados na Fato Do Banco de Dados Relacional à Tomada de Decisão MVTech · 36 como sendo Measures. Seguindo o exemplo anterior (Vendas de Livros) conseguimos atrelar dimensão (ou dimensões) a uma tabela fato de Livros para saber onde um determinado livro é mais vendido ou qual é o gênero que mais agrada a uma certa faixa etária. Veja a imagem abaixo para exemplificar esse cenário: Cubo: É um repositório onde todas as informações poderão ser acessadas de forma centralizada e direta. Nos cubos, o cliente já terá todas os dados consolidados e preparados para lhe entregar a informação quase que imediatamente. Entendendo o fato de que os usuários de um sistema de BI são os gerentes e diretores de empresas, a informação para uma tomada de decisão deve ser apresentada bem rápida, auxiliando os usuários a fazerem o trabalho deles rapidamente! É uma explicação elucidativa somente para facilitar o entendimento, já que dentro do Analysis Services os dados não ficam armazenados desta forma. Para facilitar o entendimento, imaginem um projeto de BI para cruzar informações sobre vendas de roupas com cubo de apenas 3 dimensões, sendo uma relacionada à produtos, outra a cores e outra a tamanhos. Com as três dimensões se cruzando, é possível saber quanto cada peça de roupa vende, em qual cor e qual tamanho. Levando em consideração cada dimensão, pense hipoteticamente na dimensão de Do Banco de Dados Relacional à Tomada de Decisão MVTech · 37 Tamanho como sendo as informações armazenadas em Colunas. A dimensão de Cor como sendo as armazenadas em Linhas e a dimensão de Tamanho armazenadas nas profundidades. Cada uma destas células representa um valor que irá compor a Measure para o cálculo a ser feito. Neste caso, Quantidade de Vendas. Armazenamento dos dados na dimensão de Tamanho Do Banco de Dados Relacional à Tomada de Decisão MVTech · 38 Armazenamento dos dados na dimensão de Cor Armazenamento dos dados na dimensão de Produto O cruzamento desta matriz representa a informação mais especializada possível, visto que é a junção de todas as dimensões existentes. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 39 Matriz do cruzamento de todas as dimensões Agora, veja esta representação com a Quantidade de Vendas, em cada uma das células preenchidas. O somatório de todas as células dá o retorno total de vendas, representando a informação mais ampla e menos específica possível. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 40 Cubo com a matriz das measures preenchidas Seguindo a mesma lógica, se for preciso retornar valor de um membro qualquer, sem cruzamento com outras dimensões, a representação faz uma agregação para aquele membro, neste exemplo somando todas as células envolvidas. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 41 Agregação de soma do membro “Tamanho G” – Total 49 Agregação de soma do membro “Cor Azul” – Total 55 Do Banco de Dados Relacional à Tomada de Decisão MVTech · 42 Agregação de soma do membro “Produto Bermuda” – Total 37 Um aplicativo muito utilizado para ler os dados destes cubos OLAP (On-line Analytical Processing) é o Microsoft Office Excel utilizando o recurso de PivotTable e agora também podendo utilizar o PowerPivot. Basicamente os dois recursos no Excel fazem essa interface entre o cubo OLAP, mas o PowerPivot com o Excel 2013 tornou muito rápido criar relatórios e entregar a informação aos gerentes e diretores. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 43 SQL SERVER INTEGRATION SERVICES Do Banco de Dados Relacional à Tomada de Decisão MVTech · 44 Que tal começar a fazer uma movimentação de dados com a ferramenta de Import and Export Data, para começar a se familiarizar com as possibilidades existentes para seus projetos de Business Intelligence? Esta ferramenta é uma versão inicial e com menos recursos que o SQL Server Integration Services. Vou mostrar uma das formas de utilizar o SSIS (SQL Server Integration Services) para importar dados para o SQL Server 2014. A ideia é popular uma tabela já existente com base em um arquivo de texto simples. Utilizarei o Import and Export Data que é uma ferramenta que já acompanha o SQL Server e não nos obriga a criar um projeto no SSDT (SQL Server Data Tools)que é o Visual Studio com os template para se trabalhar com BI. O Import and Export Data é muito mais limitado que o SSDT, mas para esse propósito é suficiente. Para gerar a massa de dados que vou utilizar para explicar, acessei o site da Wikipedia e coletei os nomes e períodos de mandato de todos os Presidentes da República do Brasil (https://pt.wikipedia.org/wiki/Anexo:Lista_de_presidentes_do_Brasil). Para armazenar estes dados será necessário uma tabela de nomes e, nesta tabela, serão carregados nomes e datas. Estes nomes foram salvos em um arquivo TXT, cujo nome é Presidentes.txt e que você encontra no arquivo .Zip com os fontes do livro. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 45 Para exemplificar o processo de importar esses dados para o SQL Server, deve-se criar uma tabela para armazenar os registros. CREATE DATABASE dbNomes GO USE dbNomes GO CREATE TABLE tbPresidentes (id SMALLINT IDENTITY(1,1) , nome VARCHAR(30) not null , inicio DATE , termino DATE) GO Depois de criada a tabela, é hora de abrir o aplicativo SQL Server 2014 Import and Export Data (64-bit), que fica dentro da pasta do Microsoft SQL Server 2014. Veja no caminho abaixo: Do Banco de Dados Relacional à Tomada de Decisão MVTech · 46 A primeira tela que se abre é uma tela de boas-vindas. Ela não realiza nenhuma atividade especial no processo. É só avançar sem nenhum problema. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 47 A segunda tela é importante. É aqui que selecionamos a origem dos nossos dados. Como vamos importar informações de um arquivo TXT, no primeiro combo (no topo da tela) a origem selecionada deve ser Flat File Source. Neste combo existem outras diversas origem de dados, mas neste caso específico será o Flat File. Após escolher essa opção, os campos da tela se adaptam a ela e solicitam informações sobre o arquivo. O botão Browse nos permite escolher o arquivo utilizado para importar os dados. Selecionamos o arquivo Presidentes.txt e algumas informações relativas a ele são preenchidas. No menu lateral à esquerda existem quatro opções. Se quiser visualizar os dados que existem no arquivo, vá até a opção Do Banco de Dados Relacional à Tomada de Decisão MVTech · 48 “Preview”. Reparem que o nome da coluna ficou com o nome do primeiro Presidente da República e isso precisa ser corrigido. Volte à opção General no menu da esquerda e procure uma caixa de seleção escrito Column names in the first data row e desmarque esta caixa. Volte à opção preview e então o nome de Deodoro da Fonseca não está mais na coluna, mas agora a coluna está com a nomenclatura padrão, com os nomes de Column 0, Column 1, e assim por diante. Para alterar esta nomenclatura, vá até a opção “Advanced”, selecione a Column 0 e altere a propriedade Name de Column 0 para Codigo. Aproveite e altere a propriedade DataType de string (DT_STR) para four-byte signed integer (DT_I4). Para aplicar outras conversões entre os DataTypes do SQL Server para o Integration Services, pode conferir nesta tabela a lista de correspondências. http://technet.microsoft.com/pt-br/library/ms141036.aspx Do Banco de Dados Relacional à Tomada de Decisão MVTech · 49 Aproveite e altere também as outras colunas, mudando a propriedade Name e DataType, deixando desta forma: Column 1 Name: Nome | DataType: string (DT_STR) Column 2 Name: Inicio | DataType: database date (DT_DATE) Column 3 Name: Termino | DataType: database date (DT_DATE) Do Banco de Dados Relacional à Tomada de Decisão MVTech · 50 Depois de alterar estas propriedades, vá até a opção Preview e veja as colunas renomeadas. Avance para o próximo passo, clicando em Next. A terceira tela utilizada é a tela referente ao destino dos dados. Neste caso, como vamos colocar os dados do arquivo no SQL Server 2014, deve-se selecionar o destino como SQL Server Native Client 11.0, informar a instância e realizar a autenticação no servidor. Após a autenticação deve- se escolher o Database que vai armazenar os dados, neste caso, dbNomes. Após preencher esses campos, deve avançar para a próxima tela clicando em Next. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 51 A tela seguinte permite escolher em qual tabela do banco de dados os dados do arquivo serão armazenados. Por padrão, ele sugere criar uma tabela nova com o nome do arquivo, mas isso não é obrigatório. Veja que neste caso vamos preencher a tabela que criamos lá em cima, a tbPresidentes. Para informar a essa ferramenta qual coluna do arquivo irá popular qual coluna da tabela, deve-se clicar no botão Edit Mappings na parte inferior da tela. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 52 Na edição de colunas, reparem que as colunas Source e Destination foram ligadas automaticamente. Isso acontece por causa dos nomes serem os mesmos na origem e no destino. Porém, o campo Codigo da tabela foi definido com a propriedade IDENTITY, que faz um auto-incremento numérico, logo não poderá ser preenchido pelos dados da origem. Neste caso, será necessário alterar a informação no Destination da coluna Codigo para <ignore>. Assim, os dados não serão lidos da origem para escrever no destino. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 53 Clica-se em OK na tela de edição de mapeamento das colunas, e Next na tela onde liga-se a origem ao destino. Após a escolha da tabela de destino e a coluna que receberá os dados do arquivo, a ferramenta analisa as ligações informadas. Se estiverem todas com a validação verde, avançamos para a tela seguinte, clicando em Next. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 54 Neste passo, a ferramenta pergunta se quer executar o processo imediatamente ou se quer armazenar para realizações futuras. No nosso caso, vamos apenas popular esta tabela agora, então não teremos problema em mandar executar agora e não salvar essa configuração. Para isso, marque a opção Run immediately e desmarque a opção Save SSIS Package. Novamente, avance clicando em Next. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 55 Este é o penúltimo passo. Ele mostra um resumo do que será realizado e solicita que clique em Finish para efetivamente realizar o processo. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 56 Ao clicar em finalizar, o processo começa a ser executado. Se estiver tudo configurado certo, não haverá problema e uma tela parecida com essa abaixo será apresentada. Veja que todos os passos foram executados com sucesso. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 57 Agora, para provar a movimentação dos dados da origem para o destino, pode-se fazer um SELECT simples e verificar se todos os 36 registros foram incluídos. Este é o código para a validação: SELECT id, nome, inicio, termino from tbPresidentes Do Banco de Dados Relacional à Tomada de Decisão MVTech · 58 Parabéns, com este exemplo você realizou sua primeira movimentação de dados com o SQL Server 2014! Nos próximos passos você irá aprender outras atividades interessantes para realizar nos seus projetos de Business Intelligence. Do Banco de Dados Relacional àTomada de Decisão MVTech · 59 Que tal agora fazer o mesmo trabalho de importação dos dados, mas utilizando o SQL Server Integration Services ao invés do SQL Server 2014 Import and Export Data? Desta vez, o aplicativo utilizado será o SSDT (SQL Server Data Tools), que nada mais é que o Shell do Visual Studio 2012 com os templates de projetos para se trabalhar com BI. Na versão 2014 do SQL Server, é necessário baixar e instalar o Visual Studio separadamente. Para fazer o download, acesse o link http://www.microsoft.com/en- us/download/details.aspx?id=36843. Após o download e a instalação, é hora de abrir o Visual Studio 2012, que está na a pasta Microsoft Visual Studio 2012. Veja no caminho abaixo: Após o SSDT carregar, acesse o menu File > New > Project, para criar um projeto novo. A tela com os templates e tipos de projetos é apresentada. As possibilidades de se trabalhar com Business Intelligence cobre os três tipos de projetos, Analysis Services, Integration Services e Reporting Services. Selecione o Integration Services Project, informe um nome e o local onde irá salvar. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 60 Após preencher os dados, clique em OK e aguarde alguns instantes. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 61 Ao iniciar o projeto, cinco abas superiores são apresentadas na área de desenvolvimento do processo: Control Flow, Data Flow, Parameters, Event Handlers e Package Explorer. Não vou falar sobre as abas que não utilizaremos neste exemplo, vou falar apenas da Control Flow e Data Flow. A aba Control Flow é responsável por criar as execuções do pacote que não são necessariamente relacionadas à ETL (Extração, Transformação e Carga). É dentro desta área que são colocadas as tarefas administrativas, como por exemplo, a Data Flow Task, que é uma tarefa especial e possui sua própria aba superior. O Control Flow pode possuir uma ou mais destas tarefas (Data Flows Task), que são os responsáveis reais por realizar o ETL ou outras tarefas, como acessar uma pasta FTP, criar um loop, enviar um e-mail, entre outros. Já na tarefa Data Flow informamos qual é a origem e o destino dos dados que serão processados. Veja as abas superiores e o item do Data Flow na Toolbox (à esquerda). Ele será arrastado para dentro do Control Flow e irá controlar o processo. Após arrastar o item Data Flow Task para a área do Control Flow, dê dois cliques nesta tarefa e vamos analisar as mudanças da toolbox desta nova aba. Repare que os itens do toolbox foram modificados para se adequar à aba Data Flow. As sessões da toolbox são Favorites (suas tarefas Do Banco de Dados Relacional à Tomada de Decisão MVTech · 62 favoritas), Common (tarefas comuns usadas nos projetos), Other Transforms (são as outras tarefas de transformações, que não estão na sessão Common), Other Sources (são as outras tarefas de origens que não estão na sessão Favorites) e Other Destinations (são as outras tarefas de destino que não estão na sessão Favorites). Os dois grupos que obrigatoriamente sempre serão usados em um projeto de ETL com SSIS serão os de Origem (Sources) e de Destino (Destinations). O grupo de transformação (Data Flow Transformations) pode ser usado ou não, vai depender da sua necessidade. Bom, sabendo que os itens mínimos para se criar um fluxo no SSIS são Origem e Destino, vamos adicionar esses itens e fazer as conexões. A origem será um arquivo TXT. Para isso, vou adicionar o Flat File Source. Encontre esse objeto na toolbox à esquerda e arraste para a área de design. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 63 Depois, como o destino será um banco de dados SQL Server, deve- se adicionar um ADO NET Destination por meio do mesmo processo. Encontre o objeto na toolbox e arraste para a área de design. Repare que os itens estão um na sessão de Origem e outro no Destino, apesar dos nomes serem parecidos. É importante renomear as tarefas, para que seja possível consultar as bases de gerenciamento e saber o que aconteceu e quando estes Do Banco de Dados Relacional à Tomada de Decisão MVTech · 64 processos interagiram com as execuções. Vou renomear a origem para “Arquivo com Presidentes” e o destino para “Tabela com Presidentes” Para se criar o fluxo do processo, visto que podem existir diversas atividades de origens, transformações e destinos apresentados em um mesmo fluxo, é necessário criar uma ordem lógica dos acontecimentos. Para isso, deve-se selecionar o item que será processado primeiro e arrastar a setinha azul para o item seguinte, continuando até completar todo o processo lógico do pacote. Para este exemplo, selecionamos o item “Arquivo com Presidentes”, arrastamos sua setinha azul e ligamos no “Tabela com Presidentes”. Reparem que existem duas setas na origem. Estas setas são chamadas constraints, a seta azul representa que o fluxo deve seguir este caminho quando não ocorrer problema no processamento. Já a seta vermelha é utilizada como caminho quando o processamento daquela tarefa der algum problema. Geralmente a seta vermelha segue para um item que permite enviar um e-mail ou salvar a mensagem do erro em um arquivo de log para análise posterior. Neste exemplo, vou apenas mostrar a constraint de sucesso, a azul. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 65 Agora que já foram criados os fluxos do trabalho, vamos criar as conexões que servirão para orientar a origem e o destino dos dados. Para isso, clique com o botão direito na Origem e depois selecione Edit. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 66 Uma tela com as opções para se carregar um arquivo é apresentada. Esta tela é semelhante à tela criada no exemplo utilizando o SQL Server 2014 Import and Export Data. Como não é exatamente a mesma tela, é preciso criar uma conexão para o arquivo Presidentes.txt. Para isso, clique no botão New e configure as opções do arquivo que será lido, seguindo os mesmos passos que foram realizados no exemplo anterior. Após fazer a configuração dos dados de origem, repare que o objeto não está mais marcado com um alerta x. Isso representa que ele está configurado com uma fonte de dados. Comparando com o destino, que ainda não foi configurado, fica fácil observar o x. Agora é a hora de configurar o destino dos dados, vamos então fazer o mesmo processo com o destino, clique com o botão direito e selecione Edit. Uma tela para selecionar a conexão de destino é apresentada. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 67 Deve-se clicar em New para criar o item do Connection Manager e ir para uma segunda tela. Nesta segunda tela, uma lista com as conexões existentes é apresentada. Novamente clique em New e agora sim configure a conexão. Neste caso, como foi feito na origem, deve-se configurar o destino de acordo com a configuração realizada no exemplo anterior. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 68 Após configurar, volte até a primeira tela da configuração e informe qual será a tabela que vai escrever a informação. Após selecionar a tabela tbPresidentes na combo de Use a Table or View, clique no item de menu Mappings à esquerda e repare que os itens já estão mapeados entre a origem e o destino. Essa configuração irá representar qual informação se liga na outra. Apósligar as colunas, é só clicar em OK. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 69 A tabela com os dados dos nomes dos Presidentes está preenchida. Para fins de exemplo, vamos apagar todos os dados que existem lá dentro e, em seguida, popular a tabela com base neste pacote que foi criado. Para apagar todos os dados, execute o código abaixo no SQL Server Management Studio: truncate table tbPresidentes O processo da movimentação dos dados está quase concluído, só precisa executar o pacote (apertando F5) e aguardar o final do processamento. Se tudo estiver configurado corretamente, seguindo os exemplos acima, as caixinhas ficarão com alertas verdes. Isso significa que foram executadas com sucesso. Caso alguma fique vermelha, significa que nesta tarefa ocorreu uma falha. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 70 Para confirmar a inclusão de todos os dados do arquivo TXT para o SQL Server, consulta-se os dados como feito anteriormente. Este é o código para a validação: SELECT id, nome, inicio, termino from tbPresidentes Com esse processo do SQL Server Integration Services funcionando, foi possível migrar dados de um arquivo TXT para o SQL Server. O arquivo Do Banco de Dados Relacional à Tomada de Decisão MVTech · 71 TXT foi só uma das possíveis origem de dados, podendo ser qualquer outra que estivesse dentro do grupo Data Flow Sources na toolbox da aba Data Flow. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 72 No SQL Server Integration Services, existem dois tipos de escopo para abranger o impacto das conexões. Estes dois escopos são nos níveis de Pacotes e de Projetos. Quando se inicia um novo projeto no SQL Server Integration Services, algumas pastas na Solution Explorer são apresentadas. A que diz respeito à conexões é a pasta Connection Managers. Esta pasta é responsável por garantir as conexões com escopos de Projetos, sendo possível compartilhar esta conexão entre todos os pacotes existentes no seu projeto. Para criar a conexão com este escopo, clique com o botão direito na pasta Connection Managers e vá para New Connection Manager. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 73 Neste momento todos os provedores de conexões instalados na máquina de desenvolvimento são apresentados, permitindo que você escolha em qual irá se conectar. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 74 No caso de selecionar o tipo ADO.NET, você terá acesso a conexões nativas ao SQL Server 2014. As outras conexões permitem acessos a outros servidores ou arquivos, como XML, Flat File, OLEDB, ODBC, entre outros. Após selecionar o ADO.NET e clicar em Add, você será redirecionado àquela tela já conhecida das configurações de autenticação do servidor. Caso não lembre, veja o capítulo sobre SQL Server 2014 Import and Export Data. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 75 Após preencher os dados da conexão, confirme a tela no botão OK até voltar à tela principal do ambiente. Isso lhe permitirá enxergar a conexão que acabou de criar na Solution Explorer, pasta Connection Managers. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 76 O próximo passo é abrir o pacote chamado Package.dtsx que já vem criado na pasta SSIS Packages, para então criar os próximos tipos de conexões, com escopo exclusivo para o pacote. Repare que ao abrir o pacote, também existe uma área no final da página chamada Connection Managers e já existe uma conexão criada nesta área. Esta conexão é exatamente a mesma conexão com escopo de projeto, criada instantes atrás. Um detalhe importante: a conexão com escopo de projeto fica com o nome (project) e também um ícone diferente para se diferenciar das outras conexões locais. Todas as conexões de projeto são replicadas para os pacotes. Por este motivo sempre que for necessário se conectar a uma mesma origem de dados de diversos pacotes, é recomendado criar esta conexão no escopo do projeto. A manutenção também é beneficiada, visto que uma alteração na configuração do Connection Managers do projeto irá refletir em todos os pacotes que estão utilizando aquela conexão. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 77 Para criar a conexão com escopo do pacote, deve-se clicar com o botão direito na área de Connection Manager do pacote e apontar para o provedor que deseja criar a conexão. Da mesma forma utilizada nos exemplos anteriores, foram criadas duas conexões, uma para o SQL Server 2014 que serviu de destino dos dados, e outra para um Flat File que foi caracterizada como a origem dos dados. Para completar o exemplo, deve-se criar uma conexão para o Flat File e apontar para aquele arquivo Presidentes.txt. O controle DataFlow possui uma área de Connection Manager na região inferior da área de design. Nesta área ficam todas as conexões locais do pacote. Vamos adicionar uma nova conexão clicando com o botão direito e indo até o item específico da conexão que deseja realizar. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 78 A tela que irá guiar a configuração do arquivo já é conhecida. É a mesma utilizada nos exemplos de Flat File anteriores. Preencha a tela com todas as configurações necessárias e veja que foi criada uma nova entrada nesta área, agora para o Flat File Connection Manager. É possível renomear as conexões para refletir melhor a realidade do que se está trabalhando. Renomeie a conexão do escopo de projeto para “Destino” e a conexão do pacote para “Origem”. Sua tela irá deverá ser parecida com essa: Do Banco de Dados Relacional à Tomada de Decisão MVTech · 79 É possível migrar uma conexão de escopo de pacote para escopo de projeto, mas o caminho inverso não é permitido. Para realizar esta migração, selecione a conexão no Connection Managers do pacote, clique com o botão direito, e então aponte para Convert to Project Connection. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 80 Ao realizar esta tarefa, a conexão apontada para o escopo de projeto passará a existir em todos os pacotes do projeto, sendo compartilhada entre eles. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 81 Para utilizar uma conexão existente em alguma tarefa (independente do escopo), basta informar à tarefa qual é a conexão. Para exemplificar, adicione uma tarefa de Execute SQL Task no Control Flow do pacote. Clique com o botão direito na tarefa adicionada ao pacote e aponte para Edit. Nesta tela que se abrirá, é preciso configurar a conexão e a instrução SQL a ser executada. Esta tarefa permite executar instruções Do Banco de Dados Relacional à Tomada de Decisão MVTech · 82 SQL, indepentende de ser contra um SQL Server, um MySQL ou um Oracle. Se o SGBD (Sistema Gerenciador de Banco de Dados) permite a conexão, esta tarefa poderá se conectar a ele. Altere a propriedade ConnectionType de OLE DB para ADO.NET, isso fará com que o ambiente receba instruções para disparar contra servidores que tenham autenticações nativas com ADO.NET, que é o caso do SQL Server 2014. Com a alteração desta propriedade, é possível clicar na caixa de seleção de Connection e visualizar a conexão com o nome de Destino.Selecione este item. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 83 Logo abaixo, na propriedade SQLStatement, é o local onde deve entrar a instrução SQL que será executada no servidor que foi configurado. Para o exemplo, coloque o código abaixo, que será responsável por apagar todos os registros da tabela tbPresidentes. truncate table tbPresidentes Confirme as modificações até voltar à tela inicial e veja que o X vermelho já não existe mais na tarefa de Execute SQL Task. Como Do Banco de Dados Relacional à Tomada de Decisão MVTech · 84 informado anteriormente, é importante renomear as tarefas e objetos. Esta tarefa será renomeada como Limpar Tabela tbPresidentes. Se tudo ocorreu com sucesso, os dados da tabela tbPresidentes não existirão mais ao executar o pacote. Agora, para preencher novamente a tabela com os dados dos Presidentes da República, execute o primeiro exemplo utilizando o SQL Server 2014 Import and Export Data ou o segundo exemplo, com o SQL Server Integration Services. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 85 O processamento do cubo é um processo contínuo, que deve ser executado de tempos em tempos de acordo com a atualização do Data Warehouse. Digamos que seus dados são migrados da origem (ambiente de produção) para o Stage (ambiente temporário) uma vez ao dia, após a meia noite, recuperando os dados do dia anterior. Supondo que esta movimentação de dados precise de 30 minutos, então seu cubo deverá iniciar o processamento dos dados após a meia noite e meia. Existe um capítulo mais à frente, na sessão de SQL Server Analysis Services, que explica sobre o processamento do cubo. Este capítulo é para ensinar como criar um pacote de processamento do cubo dentro do SSIS. Para isso, porém, seu cubo já deve estar criado e publicado. A tarefa existente no Integration Services é encarregada de processar o cubo e não de alterar ou publicar. Esta tarefa se conecta ao cubo publicado e processa os dados de acordo com a instrução de processamento. Para realizar esta tarefa, será necessário criar um novo projeto do SSIS. Neste caso, terá o nome de SSIS_ProcessarCubo. Após o projeto criado, também é interessante renomear o nome do pacote. Neste caso renomeei para ProcessarCubo.dtsx. Neste momento pode-se arrastar a tarefa Analysis Services Processing Task da caixa de ferramentas para a área de Control Flow do pacote. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 86 Em seguida, clique com o botão direito na tarefa que está no Control Flow e aponte o mouse para Edit. Na tela que se abre, acesse o item Processing Settings que se encontra no menu lateral esquerdo. Ao lado da caixa de seleção Analysys Services connection manager existe um botão New. Clique neste botão. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 87 Uma nova tela perguntando o caminho do servidor do SQL Server Analysis Services é aberta e solicita que seja feita a conexão e autenticação ao ambiente. Selecione a opção Create a connection to a computer running Analysis Services e clique no botão Edit. Neste momento, uma tela familiar é apresentada. É a mesma tela utilizada nos capítulos anteriores para se conectar à fontes de dados. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 88 Preencha os dados de acesso e autenticação do seu ambiente do SQL Server Analysis Services e clique em OK. Após concluir a conexão, volte até a tela de edição da tarefa de processamento do cubo e repare que alguns itens intermediários à tela foram habilitados. Estes itens permitem que seja informado quais objetos serão processados e com qual opção de processamento. Para fins de conhecimento, vou separar o processamento do cubo em duas fases, mas você pode deixar em uma única se quiser. Na primeira fase a tarefa irá Do Banco de Dados Relacional à Tomada de Decisão MVTech · 89 processar somente as dimensões e, na segunda fase, processar as measures e o cubo. Para selecionar quais objetos serão processados, clique no botão Add que está no centro da tela. Uma nova tela se abre e nesta pode-se expandir os agrupadores de objetos. Como na primeira fase serão processadas somente as dimensões, marca-se todas elas e em seguida finaliza clicando em OK. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 90 Reparem que a área central da tela agora é preenchida com os objetos selecionados e é informada qual opção de processamento será realizada. Veja no capítulo “Processamento do SSAS, direto ao ponto” na sessão do Analysis Services, o que é cada uma destas opções. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 91 Finalize a tela até voltar ao pacote, visualizando o Control Flow e sua primeira tarefa. Lembre-se de renomear a tarefa, para saber o que ela representa. No meu caso, vou renomear para “Processar Dimensões”. Repita os mesmos passos para criar uma segunda tarefa do tipo Analysis Services Processing Task e nesta configurar o processamento do cubo. A diferença desta segunda terafa para a primeira são os objetos que serão processados. Na segunda tarefa, selecione os objetos relativos ao cubo. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 92 Confirme até voltar à área de Control Flow do pacote. Chegando no pacote, crie uma sequencia lógica de execução fazendo com que a primeira tarefa seja executada somente após a segunda. Faça isso ligando a constraint de sucesso da primeira na segunda tarefa. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 93 Para validar se o processamento do cubo está correto, execute o pacote. Se após a execução as tarefas finalizarem com o sinal verde, executaram com sucesso! Do Banco de Dados Relacional à Tomada de Decisão MVTech · 94 A publicação de pacotes no SQL Server 2014 é do mesmo formato que no SQL Server 2012, através do SSIS Catalog. Este catálogo é criado dentro do engine relacional e armazena a estrutura dos pacotes permitindo que seja melhor organizado e gerenciado pelo SQL Server. O banco de dados de sistema MSDB ainda pode ser utilizado para consultar informações sobre o execuções do pacote, retornando diversos resultados gerenciais para os desenvolvedores. Neste capítulo será criado o SSIS Catalog e também será feita a publicação do pacote criado no capítulo anterior. No capítulo seguinte será criado um JOB que executará o pacote automaticamente por meio dos seus agendamentos. A primeira atividade é criar o SSIS Catalog. Para isso, acesse o ambiente relacional do servidor que tem a instalação do Integration Services. No meu caso, é o mesmo ambiente de desenvolvimento, então acessarei o MVS-SQL2014\SQL2014. Do Banco de Dados Relacional à Tomada de Decisão MVTech · 95 Reparem que existe um diretório do Integration Services Catalogs, mas quando este diretório é expandido, não existe nenhum catalogo. É neste lugar que o SSIS Catalog deve ser criado. Para isso, clique com o botão direito do mouse no diretório e aponte para Create Catalog. Caso não esteja habilitado, é necessário habilitar a integração com CLR. Para isso, marque a caixa de seleção Enable CLR Integration. Após esta marcação, a tela estará habilitada para criar o catálogo.
Compartilhar