universidade do vale do itajaí centro de ciências

UNIVERSIDADE DO VALE DO ITAJAÍ
CENTRO DE CIÊNCIAS TECNOLÓGICAS DA TERRA E DO MAR
CURSO DE CIÊNCIA DA COMPUTAÇÃO
IMPLEMENTAÇÃO DE UM DATA MART PARA ÁREA COMERCIAL
DA HAVAN LOJAS DE DEPARTAMENTOS
Área de Sistemas de Informação
por
Marinho da Silva Junior
Luis Carlos Martins, Especialista
Orientador
Itajaí (SC), junho de 2007
UNIVERSIDADE DO VALE DO ITAJAÍ
CENTRO DE CIÊNCIAS TECNOLÓGICAS DA TERRA E DO MAR
CURSO DE CIÊNCIA DA COMPUTAÇÃO
IMPLEMENTAÇÃO DE UM DATA MART PARA ÁREA COMERCIAL
DA HAVAN LOJAS DE DEPARTAMENTOS
Área de Sistemas de Informação
por
Marinho da Silva Junior
Relatório apresentado à Banca Examinadora do
Trabalho de Conclusão do Curso de Ciência da
Computação para análise e aprovação.
Orientador: Luis Carlos Martins, Especialista
Itajaí (SC), junho de 2007
SUMÁRIO
LISTA DE ABREVIATURAS..................................................................iv
LISTA DE FIGURAS................................................................................. v
LISTA DE TABELAS ...............................................................................vi
RESUMO...................................................................................................vii
ABSTRACT..............................................................................................viii
1 INTRODUÇÃO ...................................................................................... 1
1.1 PROBLEMATIZAÇÃO................................................................................... 3
1.1.1 Formulação do Problema .............................................................................. 3
1.1.2 Solução Proposta ............................................................................................ 3
1.2 OBJETIVOS ..................................................................................................... 4
1.2.1 Objetivo Geral................................................................................................ 4
1.2.2 Objetivos Específicos...................................................................................... 4
1.3 METODOLOGIA............................................................................................. 5
1.4 ESTRUTURA DO TRABALHO ..................................................................... 7
2 FUNDAMENTAÇÃO TEÓRICA ........................................................ 8
2.1 NATUREZA DO TRABALHO GERENCIAL ............................................... 8
2.1.1 O Processo de Tomada de Decisões............................................................. 10
2.2 SISTEMAS DE APOIO À DECISÃO ........................................................... 11
2.3 DATA WAREHOUSE.................................................................................... 14
2.3.1 Características de um DW........................................................................... 16
2.3.2 Arquitetura de Data Warehouse ................................................................. 16
2.3.3 Data Mart ..................................................................................................... 18
2.3.4 Granularidade .............................................................................................. 19
2.4 MODELAGEM DIMENSIONAL ................................................................. 21
2.4.1 Fatos.............................................................................................................. 22
2.4.2 Dimensões ..................................................................................................... 23
2.4.3 Medidas......................................................................................................... 25
2.4.4 Modelo Estrela ............................................................................................. 26
2.5 OLAP............................................................................................................... 28
2.6 FERRAMENTAS DE APOIO ....................................................................... 30
2.6.1 SQL Management Studio ............................................................................ 31
2.6.2 SQL Server Integration Services (SSIS) ..................................................... 32
2.6.3 Gerador de relatórios Itlviewer................................................................... 34
3 PROJETO ............................................................................................. 39
3.1 ÁREA COMERCIAL DA HAVAN............................................................... 39
3.2 COMUNICAÇÃO ENTRE FILIAIS............................................................. 40
3.3 LEVANTAMENTO DAS NECESSIDADES GERENCIAIS ...................... 42
ii
3.4 MODELO DIMENSIONAL .......................................................................... 43
4 DESENVOLVIMENTO ...................................................................... 50
4.1 CONSTRUÇÃO DO MODELO FÍSICO DO DATA MART ...................... 50
4.2 IMPLEMENTAÇÃO DOS PROCESSOS DE MAPEAMENTO,
EXTRAÇÃO E TRANSFORMAÇÃO.................................................................. 51
4.3 IMPLEMENTAÇÃO DOS PROCESSOS DE CARGA DOS DADOS NO
DATA MART ......................................................................................................... 54
4.4 CONSTRUÇÃO DAS CONSULTAS ............................................................ 57
4.5 VALIDAÇÃO XXX ........................................................................................ 65
5 CONCLUSÕES .................................................................................... 69
5.1 TRABALHOS FUTUROS ............................................................................. 69
REFERÊNCIAS BIBLIOGRÁFICAS ................................................... 71
A Data Mart para área comercial da Havan ........................................ 74
B Criação da chave tempo ...................................................................... 77
C Geração de arquivos em MSM ........................................................... 79
D Construção de um relatório no Itlviewer........................................... 80
iii
LISTA DE ABREVIATURAS
BD
DM
DTS
DW
ER
ERP
ETL
MPLS
MSM
OLAP
OLPT
PDV
SAD
SPT
SQL
SSD
SSIS
TCC
UNIVALI
Banco de Dados
Data Mart
Data Transformation Service
Data Warehouse
Entidade Relacionamento
Enterprise Resourse Planning
Extract-Transformation-Load
Multi Protocol Label Switch
Micronectics Standard Mumps
On-Line Analytical Processing
On-Line Transaction Processing
Pontos de Venda
Sistemas de Apoio à Decisão
Sistema de processamento de transação
Structured Query Language
Sistema de Suporte a Decisões
SQL Server Integration Services
Trabalho de Conclusão de Curso
Universidade do Vale do Itajaí
iv
LISTA DE FIGURAS
Figura 1. Etapas da solução proposta ...............................................................................................4
Figura 2. Dez papéis de gerentes eficazes ........................................................................................9
Figura 3. Componentes de um SAD...............................................................................................12
Figura 4. Componentes básicos de um data warehouse ..................................................................17
Figura 5. Implementação de data mart’s integrados .......................................................................19
Figura 6. Definição da Granularidade ............................................................................................20
Figura 7. Representação do fato vendas através de um cubo de dados............................................22
Figura 8. Elementos que participam de um fato compra.................................................................23
Figura 9. Fato vendas e suas dimensões aplicadas no varejo ..........................................................24
Figura 10. Modelo Estrela .............................................................................................................27
Figura 11. Operações drill down e roll up .....................................................................................29
Figura 12. Interface do SQL Management Studio ..........................................................................32
Figura 13. Interface do SQL Server Integration Services ...............................................................33
Figura 14. Etapa da construção de um pacote DTS no SQL 2005...................................................34
Figura 15. Interface de edição de relatórios do Itlviewer ................................................................35
Figura 16. Definição de categoria no Itlviewer...............................................................................36
Figura 17. Definição da orientação no cubo no Itlviewer ...............................................................37
Figura 18. Cubo de dados no Itlviewer ..........................................................................................38
Figura 19. Topologia da rede Havan ..............................................................................................41
Figura 20. Modelo para atender a necessidade gerencial 1 .............................................................44
Figura 21. Modelo para atender a necessidade gerencial 2 .............................................................45
Figura 22. Modelo para atender a necessidade gerencial 3 .............................................................46
Figura 23. Modelo para atender a necessidade gerencial 4 .............................................................47
Figura 24. Modelo para atender a necessidade gerencial 5 .............................................................48
Figura 25. Modelo final consolidado..............................................................................................49
Figura 26. Edição da tabela fato_vendas ........................................................................................50
Figura 27. Construção de um pacote DTS SQL 2005 .....................................................................54
Figura 28. Processos dos pacotes DTS...........................................................................................55
Figura 29. Pacote DTS SQL 2000..................................................................................................56
Figura 30. Agendamento da carga incremental ..............................................................................57
Figura 31. Cadastro de tabelas na metadata do Itlviewer ................................................................58
Figura 32. Relatório com Drill Donw, necessidade gerencial 1 ......................................................59
Figura 33. Relatório com Roll Up, necessidade gerencial 1............................................................60
Figura 34. Relatório para necessidade gerencial 2..........................................................................61
Figura 35. Gráfico para necessidade gerencial 2 ............................................................................61
Figura 36. Relatório para necessidade gerencial 3..........................................................................62
Figura 37. Relatório com slice, necessidade gerencial 3.................................................................62
Figura 38. Relatório para necessidade gerencial 4..........................................................................63
Figura 39. Relatório com Dice, necessidade gerencial 4.................................................................64
Figura 40. Relatório para necessidade gerencial 5..........................................................................65
Figura 41. Itlviewer – Passo 1, definições iniciais..........................................................................80
Figura 42. Itlviewer - Passo 2, seleção da tabela base.....................................................................80
Figura 43. Itlviewer - Passo 3, seleção das colunas ........................................................................81
Figura 44. Itlviewer - Passo 4, propriedades da coluna...................................................................82
Figura 45. Itlviewer - Passo 6, definição dos filtros........................................................................83
Figura 46. Itlviewer - Passo 7, definição da categoria ....................................................................83
v
LISTA DE TABELAS
Tabela 1. Modelos de tomada de decisão .......................................................................................11
Tabela 2. Mudança de enfoque ......................................................................................................13
Tabela 3. Medidas do fato devolução de vendas.............................................................................25
Tabela 4. Exemplos de métricas aditivas e não aditivas..................................................................26
Tabela 5. Volume de vendas de produtos por região ......................................................................29
Tabela 6. Operação Slice sobre o volume de vendas ......................................................................30
Tabela 7. Operação Dice sobre o volume de vendas.......................................................................30
vi
RESUMO
Silva Junior, Marinho da. Implementação de um Data Mart para área comercial da Havan
Lojas e Departamentos. Itajaí, 2007. 93 f. Trabalho de Conclusão de Curso (Graduação em
Ciência da Computação)–Centro de Ciências Tecnológicas da Terra e do Mar, Universidade do
Vale do Itajaí, Itajaí, 2007.
Em um mercado altamente competitivo, o processo de tomada de decisão com suporte eficiente e
eficaz de sistemas de informação pode ser considerado um fator crítico de sucesso. Neste contexto,
os gestores desempenham um papel chave nas organizações tendo como principal atividade tomar
decisões, sendo que os Sistemas de Apoio a Decisão (SAD) apresentam-se como uma boa
alternativa para dar suporte às necessidades de informações gerenciais. A partir desta visão, este
trabalho objetivou a construção de um SAD, implementado através de um Data Mart (DM) para
atender as necessidades de informações gerenciais da área comercial da Havan Lojas de
Departamentos. Com a implementação do DM, dados que estavam espalhados nas bases
operacionais da empresa foram reunidos e tratados adequadamente para fornecer aos gestores
informações de apoio à decisão. O DM foi implementado no Sistema Gerenciador de Banco de
Dados SQL Server 2005 e o processo de ETL (Extract Transformation Load) realizado com o
auxílio do SQL Server Integration Services. As consultas ao DM foram construídas com o do
gerador de relatórios ItlViewer, ferramenta já utilizada pela Havan para gerar relatórios das bases
operacionais, a qual implementa as técnicas OLAP (On Line Analitical Processing), permitindo aos
gestores a análise dos dados armazenados no DM de forma dinâmica com visualização das
informações de maneira personalizada para apoiar o processo decisório.
Palavras-chave: Data Mart. Sistemas de Apoio a Decisão. OLAP.
vii
ABSTRACT
In a very competitive market, the process of making decision with efficient and efficacious support
of information system can be considered a critical factor of success. In this context, in the
organizations the managers play a key role having as main activity to make decisions being that the
Decision Support Systems (DSS) are presented as a good alternative to support the necessities of
management information. From this view, this work aimed at the construction of a DSS,
implemented through a Data Mart (DM), to take care of the necessities of management information
of the commercial area of “Havan” Departament Store. With the implementation of the DM data
that was dispersed in the operational basis of the company were assembled and treated adequately
to provide to the managers information of support to the decision. The DM was implemented in
Manager System of SQL Database 2005Server, the ETL process (Extract Transformation Load)
accomplished with the aid of the SQL Server Integration Services. The consultations to the DM
were constructed with the generator of It1Viewer reports, tool already used by Havan to generate
reports of operational basis, which implement OLAP (on line analitical processing) techniques,
allowing to the managers the analysis of data stored in the DM of dynamic form with visualization
of information of personalized way to support the decision process.
Keywords: Data Mart. Decision Support Systems. OLAP.
viii
1 INTRODUÇÃO
Com o objetivo de atender as necessidades dos clientes, segmentar o mercado e atrair novos
clientes, as empresas investem cada vez mais em tecnologia de informação para auxiliar de forma
estratégica o processo de tomada de decisões. Conforme Torres (1995), “vivemos em um mundo em
que um dos mais fortes fatores de competitividade para qualquer empresa, em qualquer ramo de
negócios, é o uso da tecnologia, adequada aos seus objetivos”. Entretanto, o simples uso da
tecnologia não constitui o único fator a ser considerado para que essa venha a produzir os resultados
desejados, devendo-se considerar outros fatores como porte da empresa, ramo de atividade,
capacitação e treinamento de recursos humanos dentre outros.
A busca de informações determinantes sobre o negócio da empresa tornou-se essencial para
o sucesso das organizações que atualmente não sofrem com a falta de dados, mas sim de uma
abundância de dados redundantes e inconsistentes difíceis de administrar com eficiência, acessar e
consultar para suporte às decisões. Os dados estão em toda parte, o difícil é transformá-los em
informação. Uma das tecnologias utilizadas para este fim é o Data Warehouse (DW), que segundo
Singh (2001), é o processo de integração dos dados corporativos de uma empresa em um único
repositório, centralizando dados armazenados em diferentes fontes, organizando esses dados para
facilitar a geração de relatórios e análises, de forma resumida, afirma que “data warehouse é uma
tecnologia de gestão e análise de dados”.
Resumidamente, trata-se de um ambiente onde os dados ficam armazenados, após serem
coletados de várias fontes diferentes, recebendo o tratamento adequado para fornecer aos seus
usuários informações de apoio à decisão. Assim, um DW pode ser explorado para encontrar
respostas a uma série de questões que podem manter um negócio competitivo na atual conjuntura de
mudanças rápidas no mercado.
Ao comparar um DW com um banco de dados, Singh (2001) salienta que um DW tem como
critérios: armazenamento de dados no formato read-only (somente-leitura) e coleta de informações
de fontes diferentes tornando-as compatíveis. Um DW proporciona soluções de negócios às
organizações e envolve a integração de diversas tecnologias em suas implementações, tornando-se
uma ferramenta competitiva que permite aos gestores da empresa consultar informações relevantes
para as tomadas de decisão.
Na implementação do DW, podem ser definidas diferentes arquiteturas, como Data
Warehouse Global, Data Mart (DM) Independente e Data Marts Integrados (MACHADO 2004).
Na arquitetura Global é implementado um DW único, centralizado, que concentra todas as
informações disponíveis. Entre as justificativas estão a facilidade de gerenciamento e o fato da
visualização das informações ocorrer normalmente na matriz, entretanto esse tipo de ambiente
consome muito tempo de desenvolvimento.
O DM é um subconjunto do DW, desempenhando o papel de um DW departamental,
regional ou funcional. Como parte do processo interativo do DW, pode ser construída uma série de
DM’s ao longo do tempo e eventualmente vinculá-los. Os DM’s podem ser independentes de um
DW, onde cada um possui as suas próprias informações, e integrados, implementados
separadamente e interconectados provendo uma visão corporativa maior dos dados.
A partir do conceito da arquitetura de DM’s observou-se a possível utilização de um DW na
empresa Havan Lojas de Departamentos. O DM pode ser uma alternativa para o suporte à decisão
na medida em que a análise das informações geradas pelas operações de venda das lojas havan
como formas de pagamento, produtos vendidos, clientes que visitaram as lojas estão relacionadas à
informações externas como condições climáticas e registro de festas e eventos. Estas informações
são utilizadas pela área comercial da empresa para tomadas de decisões como definição de novas
promoções, necessidade de reposição do estoque, criação de campanhas de marketing, entre outras.
Do ponto de vista da Tecnologia da Informação, a empresa atravessa uma etapa de migração
de software e possui dois ambientes distintos de armazenamento de dados, um em banco de dados
relacional utilizando SQL Server 2000 e SQL Server 2005 e outro utilizando banco de dados
hierárquico MSM (Micronectics Standard Mumps).
Como os dados das operações de venda estão gravados em diferentes plataformas, em
bancos de dados com formato operacional, o processo de extração de informações para apoio a
tomada de decisão torna-se demorado e complexo.
Facilitar este processo melhorando a qualidade das informações para diferentes áreas que
dependem da análise do processo de vendas é imprescindível para a empresa. Para tanto, a
construção de um DM poderá propiciar a geração de informações gerenciais relevantes e integradas
que futuramente poderão compor o DW da empresa.
2
Um outro fator facilitador na geração de informações é ter uma ferramenta de visualização
de dados oriundos do DM, a qual deve ter a flexibilidade necessária para visualizar as informações
de diferentes formas. Para tanto, são utilizadas ferramentas OLAP (On-Line Analytical Processing),
segundo Machado (2004), OLAP é o conjunto de ferramentas que possibilita efetuar a exploração
dos dados de um DW, a partir de uma análise multidimensional.
A exploração das informações do DM proposto será feita com o gerador de relatórios
ItlViewer, da empresa Intelidata, que possibilita a criação de cubos de dados multidimensionais
para atender as necessidades de informações gerenciais e já é utilizado atualmente pela Havan.
1.1 PROBLEMATIZAÇÃO
1.1.1 Formulação do Problema
As empresas do ramo varejista necessitam de tecnologia para vencer a concorrência devido
ao alto grau de competitividade presente no setor, sendo que a utilização de forma estratégica de
tecnologia pode determinar um lugar de destaque no mercado.
As ferramentas para análise de informações relevantes no processo de tomada de decisões
devem ser fáceis de usar e flexíveis, permitindo ao gestor criar visões diferentes sobre os dados
apresentados.
A Havan Lojas de Departamentos atravessa atualmente uma etapa de migração de software,
sendo assim, o processo de extração informações para tomada de decisões torna-se demorado e
complexo.
Entre as informações que merecem uma atenção especial estão aquelas relacionadas ao
departamento comercial como: informações do processo de venda, devolução de mercadorias,
compras para revenda, entre outras.
1.1.2 Solução Proposta
Esta proposta pretende contribuir no processo de tomada de decisões da área comercial da
Havan Lojas de Departamentos LTDA, visando melhorar a qualidade das informações necessárias
nesta atividade, bem como fornecer subsídios para minimizar custos e maximizar os resultados das
vendas. Para isto, propõe-se desenvolver um Data Mart, com informações relevantes às tomadas de
decisão desta área.
3
Sucintamente, pretende-se implementar os processos necessários na construção de um Data
Mart, observando as etapas apresentadas na Figura 1, ou seja, na etapa 1 serão identificadas as
necessidades de informações gerenciais através de entrevistas com os gestores da área. Na etapa 2,
será feita a modelagem dimensional com as definições dos fatos e dimensões necessárias para cada
necessidade de informação identificada. Na seqüência, etapa 3, serão realizados os processos
conhecidos como ETL (Extract-Transformation-Load), esta etapa envolve tudo que existe entre a
base operacional de origem e a área de apresentação dos dados. E, por fim, na etapa 4 serão
implementadas as consultas e relatórios para atender as necessidades identificadas na etapa 1.
1-Identificar
necessidades
Gerenciais
2-Modelagem
dimensional
fatos e dimensões
3-ETL
Extract
Transformation
Load
4 -Desenvolvimento
de consultas e
relatórios
Figura 1. Etapas da solução proposta
A realização deste trabalho também se justifica em nível de Trabalho de Conclusão de Curso
para o curso de Ciência da Computação, pois trata do desenvolvimento de um projeto
computacional que aplica os conceitos das áreas de estudo de Análise e Projeto de Sistemas,
Programação, Banco de Dados e Sistemas de Informação vistos em disciplinas no decorrer do
curso.
1.2 OBJETIVOS
1.2.1 Objetivo Geral
Construir um DM para área comercial da Havan Lojas de Departamentos de maneira a
fornecer informações gerenciais para suporte a tomadas de decisões nesta área de negócio.
1.2.2 Objetivos Específicos
•
Estudar os conceitos de Data Warehouse, Data Mart, Sistemas de Apoio a Decisão, Online Analytical Processing (OLAP);
•
Estudar a área comercial da empresa;
4
•
Identificar as necessidades de informações gerenciais;
•
Construir a modelagem dimensional do Data Mart;
•
Implementar o modelo físico do Data Mart;
•
Preparar a área de transição de dados (mapeamento, extração, limpeza, transformação e
distribuição);
•
Implementar os processos de carga do Data Mart;
•
Construir as consultas no Data Mart na ferramenta ItlViewer; e
•
Validar as consultas junto aos gestores da área comercial.
1.3 Metodologia
A metodologia utilizada no desenvolvimento deste projeto foi dividida em cinco grandes
etapas: (i) estudo; (ii) modelagem, (iii) desenvolvimento, (iv) validação; e (v) documentação.
A etapa de estudo foi destinada à pesquisa e estudo dos conceitos envolvidos na
implementação de um Data Mart, e ao estudo da área comercial da empresa para definição das
informações relevantes à tomada de decisões. Para a fundamentação teórica a pesquisa realizou-se
principalmente em livros na área de Sistemas de Informação e, também, livros específicos voltados
ao projeto e implementação de Data Warehouse, todos disponíveis na referência bibliográfica.
Ainda na etapa de estudo foram explanadas as ferramentas utilizadas para auxiliar o processo de
implementação de um DM.
Na etapa modelagem foi feito o levantamento de requisitos com a identificação das
necessidades gerenciais necessárias para auxiliar o processo de tomada de decisões do departamento
comercial da Havan e definida a modelagem dimensional para atender cada necessidade
consolidando posteriormente em um modelo único.
A etapa (iii) desenvolvimento por sua vez foi dividida em quatro etapas: construção do
modelo físico do DM; implementação dos processos de mapeamento, transformação e extração;
implementação dos processos de carga; e por fim, a construção das consultas no ItlViewer para
atender as necessidades gerenciais.
5
Na etapa de validação foi feita a verificação da consistência da informação através de
comparação com dados do ambiente operacional e a aprovação por parte dos gestores após
disponibilizar as consultas. E concorrentemente com as etapas citadas, houve a etapa de
documentação que contemplou a elaboração do texto final do TCC II.
6
1.4 Estrutura do trabalho
Este projeto está estruturado em 5 capítulos: (i) Introdução; (ii) Fundamentação Teórica; (iii)
Projeto; (iv) Desenvolvimento e (v) Conclusões.
O Capítulo 1 (Introdução) destina-se a contextualizar o projeto sucintamente, descreve o
problema encontrado e qual a solução proposta, expõe os objetivos gerais e específicos a serem
atingidos com o trabalho e a metodologia utilizada para o desenvolvimento do projeto.
O Capítulo 2 (Fundamentação Teórica) apresenta conceitos necessários e relevantes para o
entendimento do processo de construção de um DM, e como ele está integrado com a área de
Sistemas de Informação. São apresentadas, também, ferramentas que auxiliam as etapas de extração
e visualização dos dados.
O Capítulo 3 (Projeto) apresenta informações sobre a área comercial da Havan, as
informações necessárias para tomada de decisões, o modelo dimensional gerado para cada
necessidade e o modelo consolidado.
O Capítulo 4 (Desenvolvimento) descreve quais foram as etapas necessárias para a
construção do DM, como foram implementados os processos de ETL, processos de carga e
relatórios, apresentando também as tecnologias envolvidas, bem como cada relatório criado poderá
auxiliar no processo decisório.
E o Capítulo 5 (Conclusão) relaciona os objetivos do projeto, bem como as dificuldades
encontradas no seu desenvolvimento e expõe os resultados obtidos, além de identificar melhorias a
serem realizadas com o intuito de dar continuidade ao trabalho.
7
2 FUNDAMENTAÇÃO TEÓRICA
Neste capítulo são descritos os principais conceitos envolvidos na implementação de um
Data Mart e que estão relacionados com o objetivo geral deste trabalho de conclusão de curso.
A primeira seção, Natureza do Trabalho Gerencial, descreve-se o trabalho gerencial e o
processo de tomada de decisões, visando demonstrar numa visão geral os papéis que os gestores
representam em uma organização e a forma que as decisões são estruturadas.
Na seção, Sistemas de Suporte a Decisão, são apresentados conceitos e características dos
sistemas que podem auxiliar os gestores nos papéis decisionais.
Na seção seguinte, Data Warehouse, são apresentados conceitos envolvidos em um ambiente
de DW e seu segmento o Data Mart, são apresentados também os requisitos que um DW deve
atender, pontos que justificam sua implementação, características de um DW e os componentes de
sua arquitetura.
Os conceitos e características do Modelo Dimensional são descritos na seção seguinte, na
qual são apresentados os conceitos de tabela Fato, tabela Dimensão e de Medidas. No final dessa
seção é mostrado o esquema estrela de modelagem dimensional.
Na seção OLAP, são apresentadas as finalidades dos Sistemas de Processamento de
Transações (OLPT) e das ferramentas OLAP. Ainda nesta seção são exemplificadas as operações
básicas de OLAP.
Na última seção, Ferramentas de Apoio, são citadas as ferramentas que o SQL Server 2005
disponibiliza para soluções de Business Inteligence (BI) e DW, apresentando mais detalhadamente
as ferramentas que serão utilizadas no projeto.
2.1 Natureza do Trabalho Gerencial
Schermerhorn, Hunt e Osborn (1999) definem gerente, também chamado supervisor, chefe
de departamento, gerente-geral, líder de equipe, coordenador, diretor de projeto, dentre outras
possibilidades, como o responsável pelo trabalho que geralmente é realizado por meio do esforço de
uma ou mais pessoas, com um trabalho intenso envolvendo a realização de muitas tarefas e
atividades diferentes num mesmo dia de trabalho.
Os gerentes desempenham um papel chave nas organizações. Segundo Laudon e Laudon
(2001), para determinar como os sistemas de informação podem beneficiá-los, é necessário primeiro
examinar suas atribuições e quais são as informações necessárias para tomar decisões. Também é
necessário entender como as decisões são tomadas e que tipos de decisão podem ser assistidas por
sistemas de informação.
Podem-se definir as atribuições de um gerente a partir de dois modelos de gerenciamento: o
modelo clássico e o modelo comportamental. No modelo clássico, descrito por Henry Faiol e outros
autores, são apontadas cinco funções dos gerentes: planejamento, organização, coordenação,
decisão e controle (LAUDON e LAUDON, 2001).
O segundo modelo citado, o modelo comportamental, pode ser descrito com
comportamentos menos sistemáticos, mais informais, menos reflexivos, mais realistas, menos
organizados (ibidem).
Segundo Henry Mintzberg (apud SCHERMERHORN, HUNT, OSBORN, 1999), as
categorias de papéis ou atividades que os gerentes devem estar preparados a enfrentar atualmente
são divididas em três, de acordo com a Figura 2.
Papéis informacionais
Como o gerente troca e
Processa informações
• Monitor
• Disseminador
• Relator
Papéis interpessoais
Como o gerente interage
com outras pessoas
• Chefe
Papéis decisionais
• Líder
• Elo
Como o gerente usa a informação
Na tomada de decisão
• Empreendedor
• Mediador
• Alocador de recursos
• Negociador
Figura 2. Dez papéis de gerentes eficazes
Fonte: Adaptado de Schermerhorn, Hunt, Osborn (p. 32, 1999).
9
Os papéis interpessoais envolvem o trabalho direto com outras pessoas. Já os papéis
informacionais envolvem a troca de informação com outras pessoas. Os papéis decisionais
envolvem a tomada de decisões que afetam outras pessoas. Neste aspecto, incluem-se a detecção
dos problemas a serem solucionados, as oportunidades a serem exploradas, a ajuda para resolver
conflitos, a alocação de recursos para vários usos e a negociação com outras partes.
Na visão de Laudon e Laudon (2001), existem sistemas de suporte à informação para
auxiliar os gerentes a desenvolverem alguns papéis no trabalho gerencial. Os sistemas de apoio à
decisão auxiliam os gerentes nos papéis decisionais.
2.1.1 O Processo de Tomada de Decisões
Tomada de decisão é o processo de escolher um curso de ação para lidar com um problema
ou oportunidade (HUBER apud SCHERMERHORN, HUNT, OSBORN,1999).
Numa visão compacta, a tomada de decisão é descrita como a escolha entre alternativas.
Mas não pode ser considerada desta forma, por se apresentar como um processo abrangente, de
acordo com Simonson (apud SOTO, 2002), a tomada de decisão pode se estruturar com os passos:
•
Percepção de uma situação que requer decisão;
•
Proposição de alternativas; e
•
Valorização das alternativas.
A próxima etapa é a escolha da melhor alternativa e sua implementação, após isso torna-se
fundamental a avaliação dos resultados para tomar decisões corretivas se necessário. O resultado da
escolha de uma alternativa pode variar de acordo com o ambiente em que foi tomada a decisão. Em
um ambiente de certeza, o gestor tem informações suficientes para prever o resultado de cada
alternativa, em ambientes de risco as informações não trazem a certeza, apenas um senso das
probabilidades relacionadas a cada alternativa, enquanto, no ambiente de incerteza a decisão é
tomada sem informações sobre o problema (SCHERMERHORN, HUNT, OSBORN,1999).
Segundo Shermerhorn, Hunt e Osborn (1999), no ponto de vista dos problemas que surgem
no local de trabalho as decisões podem ser:
10
•
Programadas: problemas rotineiros que podem ser resolvidos com ações padronizadas,
por exemplo a reposição automática do estoque baseada na movimentação dos produtos;
e
•
Não-programadas: não estão disponíveis ações padronizadas, uma decisão nãoprogramada requer muitas vezes uma solução criativa baseada em informações mais
refinadas, por exemplo, a definição de uma campanha de marketing.
A Tabela 1 apresenta os modelos de tomada de decisão.
Tabela 1. Modelos de tomada de decisão
Modelo Clássico
Modelo Comportamental
Problema claramente definido
Conhecimento de todas as alternativas
possíveis e de suas conseqüências
Escolha da alternativa ótima
Problema não claramente definido
O conhecimento está limitado a alternativas
possíveis e suas conseqüências
Escolha da alternativa satisfatória
Fonte: Adaptado de Shermerhorn, Hunt, Osborn, (p. 255, 1999).
2.2 Sistemas de Apoio à Decisão
Para Laudon e Laudon (2001), como existem diferentes interesses, especialidades e níveis
em uma organização, existem diferentes tipos de sistemas. São quatro principais tipos de sistemas
de informação que podem servir uma organização em níveis diferentes: sistemas de nível
operacional, sistemas de nível de conhecimento, sistemas de nível gerencial e sistemas de nível
estratégico.
Os Sistemas de Apoio à Decisão (SAD) são parte integrante dos sistemas de nível gerencial,
dando suporte aos gerentes para tomada de decisões. Freqüentemente, um SAD traz informações de
fontes externas e tem como característica o fato de ser analítico, condensando grandes quantidades
de dados em um repositório para serem analisados pelos tomadores de decisão. Conforme Laudon e
Laudon (2001).
O SAD assiste a tomada de decisão gerencial pela combinação de dados, modelos e
ferramentas analíticas sofisticadas e softwares amigáveis ao usuário em um único e
poderoso sistema que pode suportar tomadas de decisões semi-estruturadas ou nãoestruturadas. Um SAD fornece aos usuários um conjunto flexível de ferramentas e de
recursos para analisar importantes blocos de dados.
11
Resumidamente, o modelo mais usual de SAD, utiliza informações úteis para tomada de
decisão que anteriormente estavam entre uma grande quantidade de dados de um sistema de
processamento de transação (SPT), estes dados são coletados para um DW e posteriormente
acessados através de ferramentas OLAP (LAUDON, 2001). A Figura 3 apresenta os principais
componentes de um SAD.
Dados
Externos
SPT
Banco de
Dados
SSD
Sistemas de Software SSD
Modelos
Ferramentas OLAP
Ferramentas de
datamining
Interface com o
usuário
Usuário
Figura 3. Componentes de um SAD
Fonte: Adaptado de Laudon e Laudon (p. 320, 2001).
De acordo com Laudon e Laudon (2001), os usuários dos SAD são executivos ou gerentes
de corporações, com pouca ou nenhuma experiência com computador, por este motivo a interface
com o usuário de um SAD deve ser fácil de usar, para facilitar o diálogo entre o usuário e o SAD.
12
Na criação de SAD ocorre uma mudança de enfoque, os dados operacionais que em sua
modelagem atendem ao ambiente funcional, são projetados para um ambiente onde toda noção de
dados é alterada (INMOM, 1997).
A Tabela 2 mostra algumas das principais diferenças entre os dados de um ambiente
primitivo ou operacional e o ambiente derivado em um SAD.
Tabela 2. Mudança de enfoque
Dados Operacionais
Dados em um SAD
Baseados em aplicação
Detalhados
Exatos em relação ao momento de acesso
Baseados em assuntos ou negócios
Resumidos ou refinados
Representam valores de momentos já decorridos
ou instantâneos
Atendem à comunidade gerencial
Não são atualizados
Processados de forma heurística
Requisitos de processamento não são conhecidos
com antecedência
Ciclo de vida completamente diferente
Performance atenuada
Acessados um conjunto por vez
Voltados para análise
O controle de atualizações não é problema
Atendem a comunidade funcional
Podem ser atualizados
São processados repetitivamente
Requisitos de processamento conhecidos com
antecedência
Ciclo de vida de desenvolvimento clássico
A performance é fundamental
Acessados uma unidade por vez
Voltados para transações
O controle de atualizações é atribuição de
quem tem a posse
Alta disponibilidade
Gerenciados em sua totalidade
Não contemplam a redundância
Estrutura fixa, conteúdos variáveis
Pequena quantidade de dados usada em um
processo
Atendem às necessidades cotidianas
Alta probabilidade de acesso
Disponibilidade atenuada
Gerenciados por subconjuntos
A redundância não pode ser ignorada
Estrutura flexível
Grande quantidade de dados usada em um
processo
Atendem às necessidades gerenciais
Baixa, ou modesta probabilidade de acesso
Fonte: Adaptado de Inmon (p.18, 1997).
Segundo Inmon (1997), “no coração de um ambiente projetado encontra-se o Data
Warehouse. O Data Warehouse é o alicerce do processamento dos SAD”. Um DW possui uma
fonte única de dados integrados, enquanto os sistemas operacionais são organizados em torno das
aplicações da empresa, sendo assim, uma vez que os dados de um DW apresentam condições de
acesso, torna mais fácil a tarefa de um analista de SAD.
13
2.3 Data Warehouse
Para Takaoka (2004) o conceito de Data Warehouse (DW) surgiu da necessidade de integrar
os dados de uma empresa espalhados em diferentes plataformas e sistemas do ambiente operacional
para torná-los acessíveis facilitando, assim, o processo de tomada de decisões. Em resumo, um DW
é uma arquitetura de banco de dados que integra e armazena dados de várias fontes para apoiar os
sistemas de suporte à decisão.
De acordo com Inmon (1997), “DW é uma coleção de dados organizados por assunto,
integrados, não voláteis, históricos, cujo objetivo é fornecer apoio à tomada de decisão nas
organizações”.
Integrar os dados de diferentes sistemas em uma visão consolidada capacita a organização a
observar o que está acontecendo em suas operações e com base nisso oferecer melhores serviços
aos clientes. Empresas do ramo varejista devem ser capazes de analisar sua cadeia de suprimentos e
reagir rapidamente. A capacidade de reagir rápida e decisivamente em um mercado cada vez mais
competitivo passou a ser um fator critico de sucesso (TAKAOKA, 2004).
Segundo Kimball (2002), os usuários de um DW possuem necessidades extremamente
diferentes que os usuários de sistemas operacionais. Enquanto os usuários operacionais determinam
o rumo da empresa, os usuários de DW observam o rumo que a empresa toma. Para atender
usuários do nível gerencial um DW deve atender os requisitos:
1. Fazer com que as informações da empresa possam ser facilmente acessadas: o conteúdo
de um DW deve ser compreensível e intuitivo para o usuário da área de negócios, e
também deve retornar os resultados no menor tempo de espera possível;
2. Apresentar as informações da empresa de modo consistente: Os dados devem ser
confiáveis, tendo um rigoroso controle de qualidade antes de serem liberados para uso, já
que são obtidos de várias fontes;
3. Deve ser adaptável e flexível a mudanças: como as necessidades mudam no decorrer do
tempo, o DW deve ser projetado para suportar mudanças inevitáveis, os dados existentes
quando forem incluídos novos dados para atender novas necessidades;
14
4. Ter segurança, protegendo as informações: como um DW é rico em informações sobre o
negócio da empresa, o DW deve controlar de modo eficaz o acesso às informações
confidenciais da empresa;
5. Deve funcionar como base para uma melhor tomada de decisões: O DW é um sistema de
suporte à tomada de decisões, para causar impacto e ter um valor comercial o DW deve
conter informações apropriadas para esta finalidade; e
6. A comunidade de negócio deve aceitar o DW: para que uma solução de DW seja
considerada um sucesso deve ser utilizada ativamente pela área de negócio, sendo que
para sua aceitação o DW precisa ser simples.
Para Machado (2002), a construção de um DW exige a transferência e a transformação dos
dados utilizados nas operações diárias de uma empresa para uma base de dados independente. Esta,
disponibilizada a usuários de nível gerencial. Entre as principais justificativas para a aplicação de
tecnologia de DW em uma empresa Machado (2002) destaca.
•
Existência na empresa de varias plataformas de hardware e software;
•
Constantes alterações nos sistemas transacionais;
•
Dificuldade na recuperação de dados históricos;
•
Existência de sistemas de fornecedores diferentes;
•
Falta de padronização dos dados nos diferentes sistemas;
•
Carência de documentação e segurança no armazenamento de dados; e
•
Dificuldade de aplicação de sistemas de apoio à decisão.
Em uma empresa que não possui um ambiente com nenhuma das características citadas,
onde os dados estão centralizados e devidamente organizados por um sistema ERP (Enterprise
Resourse Planning), também justifica-se a aplicação de um DW. No ERP ou nos sistemas
transacionais, o foco é o controle dos processos enquanto em um DW o foco é o suporte a decisão,
os dados transacionais serão transformados em recursos informacionais.
Resumindo, um DW proporciona a integração dos dados da empresa para realização de
análises gerenciais de uma área de negócio. A integração ocorre entre informações de fontes
15
internas e externas, onde os dados são preparados para auxiliar o processo de tomada de decisões e
armazenados e um ambiente isolado dos sistemas operacionais.
2.3.1 Características de um DW
Segundo Machado (2004), um DW tem como características:
•
Orientação por assunto: as informações são armazenadas em um DW agrupadas por
assunto, sendo que os assuntos neste caso são os processos principais de uma
organização. Em um ambiente transacional o foco é o controle dos processos, enquanto
no DW busca-se dados do processo importantes para tomada de decisão. Hiroo Takaoka
(2004), cita como exemplo para o processo de vendas de uma empresa varejista a
necessidade de identificar quais são os produtos que estão vendendo, quais não estão
vendendo e a necessidade de avaliar com eficácia as promoções;
•
Variação no tempo: os dados de um DW são um conjunto estático de informações que
foram capturadas em um determinado tempo. Em um ambiente transacional, ocorrem
operações de atualização dos registros constantemente, para o DW são carregados s
resultados das atualizações que ocorreram em uma determinada fatia de tempo;
•
Não-Volátil: em um DW não são realizadas operações de atualização, as operações
existentes são a carga dos dados (inicial e incremental) e acesso às informações. Após a
filtragem, limpeza e transformação, os dados são carregados no DW e acessados para
análise, auxiliando o processo de tomada de decisões; e
•
Integrados: a característica de integração torna-se fundamental em um DW, como os
dados são carregados de diferentes fontes existe a necessidade de criar padrões de
codificação para manter a unicidade de informações.
2.3.2 Arquitetura de Data Warehouse
Segundo Kimball (2002), cada componente de um ambiente de DW atende a uma função
específica, para um projeto de DW torna-se importante o entendimento desses componentes e suas
funções. Podem ser considerados quatro componentes que compõem a arquitetura de um ambiente
de DW, são eles: sistemas operacionais de origem, data staging area, área de apresentação dos
dados e ferramentas de acesso a dados. A Figura 4 apresenta esses componentes.
16
Figura 4. Componentes básicos de um data warehouse
Fonte: Adaptado de Kimball (p. 9, 2002).
Sistemas operacionais de origem
Para Kimball (2002), são os sistemas de registro que capturam as transações dos processos
da empresa. São totalmente externos ao DW e, possuem como prioridades o desempenho e a
disponibilidade de processamento, podem ser considerados os diversos aplicativos da empresa que
armazenam dados e possuem consultas limitadas no que se refere ao tempo de análise.
Os sistemas operacionais de origem podem possuir fontes de dados de diversos tipos
(hierárquicos, relacionais, arquivos flat, etc), para construção de um DW o entendimento do tipo de
estrutura de dados desses sistemas permite a escolha adequada de ferramentas e serviços para
extração dos dados (PEREIRA, 2000).
Data Staging Area
A data staging area do DW é uma área de armazenamento que possui um conjunto de
processos denominados ETL (Extract-Transformation-Load), abrangendo tudo que existe entre os
sistemas operacionais de origem e a área de apresentação dos dados (KIMBALL, 2002).
Nesta área os dados são transformados para que posteriormente possam ser consultados.
Após a extração que envolve a leitura e compreensão de dados de uma determinada origem, na
staging area os dados sofrem operações como: correções de erros de digitação, solução de conflitos
17
de domínio, tratamento de elementos ausentes, cancelamentos de dados duplicados, combinação de
dados de várias origens e atribuição de chaves de warehouse.
O principal requisito da arquitetura da data staging area, é que ela não esteja acessível ao
usuário e não forneça serviços de consulta ou apresentação. No ponto de vista da tecnologia, esta
área pode ser composta por um banco de dados físico ou por um sistema de arquivos simples
(Ibidem).
A última etapa do processo ETL é a carga de dados que assumem a forma de tabelas
dimensionais para serem carregadas nos Data Marts.
Área de Apresentação dos dados
Como a data staging area não pode ser acessada, a área de apresentação de dados é vista
pela área de negócio como sendo o DW da empresa, de acordo com Kimball (2002), a área de
apresentação dos dados pode ser composta por uma série de DM’s integrados.
Segundo Pereira (2002), é a plataforma de destino dos dados que poderão estar no formato
atômico ou também agregados. Nesta área os dados são disponibilizados e acessados para geração
de consultas e relatórios.
Ferramentas de acesso a dados
Segundo Kimball (2002), “a consulta, é o ponto principal do uso de um DW”, as
ferramentas de acesso aos dados de um DW devem ser flexíveis, permitindo a visualização das
informações de diferentes formas, e fáceis de utilizar, sendo que os usuários da área de negócio
contam com as ferramentas de acesso a dados para buscar informações necessárias para o processo
de tomada de decisões. As mais utilizadas para esta finalidade são as ferramentas OLAP.
2.3.3 Data Mart
O termo Data Mart, de acordo com Carlos Barbieri apud Pinto (2002), significa depósito de
dados que atende a áreas específicas da empresa e objetiva auxiliar o processo decisório gerencial,
DW e DM podem ser definidos como espécies do mesmo tipo, ficando a diferença entre os dois
centrada no escopo do projeto e nos limites de suas abrangências.
18
O DM, segundo Kimball (2002), é um subconjunto completo de um DW, possuindo as
mesmas características. Um DM representa processos de uma determinada área de negócio e a
integração de vários DM's pode formar o DW da empresa.
Para Machado (2004), o DM normalmente é modelado em um esquema estrela de acordo
com as necessidades da área de negócio. A implementação de DM’s, para posterior integração,
permite um retorno rápido, e um maior envolvimento com os usuários finais, facilitando a análise
dos benefícios de um DW. A Figura 5 apresenta a implementação de Data Mart’s integrados.
Figura 5. Implementação de data mart’s integrados
Fonte: Machado (p. 55, 2004).
Então, fica clara a idéia de que um DM é basicamente um DW reduzido, que contém
informações de uma área de negócios e em sua implementação deve levar em consideração uma
possível integração com outros DM’s.
2.3.4 Granularidade
A definição da granularidade de dados pode ser considerada como um dos fatores mais
importantes para modelagem física de dados, independente da arquitetura utilizada. Segundo
Machado (2004), a granularidade de dados refere-se ao nível de sumarização dos elementos e
detalhes disponíveis nos dados, sendo assim, afeta diretamente o volume de dados no DM e,
conseqüentemente, tem impacto na performance e disponibilização de consultas e relatórios.
19
Além de afetar diretamente o volume de dados e o tempo de acesso às informações no DM
Machado (2004), afirma ainda, que a definição da granularidade afeta também a análise de
negócios, por determinar o tipo de consulta que poderá ser atendida pelo DM. Tendo em vista que
um DM é construído para permitir que se visualizem comportamentos de determinados fatos de
negócio ao longo do tempo.
Para Machado (2004), quanto mais detalhes têm-se nos dados, menor é a granularidade,
quanto menos nível de detalhe maior é a granularidade. Esta definição fica mais clara analisando a
Figura 6.
Figura 6. Definição da Granularidade
Fonte: Adaptado de Machado (p. 61, 2004).
Segundo Inmon (1997), a escolha dos níveis de granularidade apropriados é um fator vital
para o sucesso do projeto. Para escolha desses níveis, deve-se usar do bom senso, prototipando uma
parte do DM, para que os gestores possam acessar os dados e assim ajustar de forma adequada os
níveis de granularidade para atender as necessidades de informação.
A maioria das empresas descobre que precisa de níveis duais de granularidade ao longo do
tempo. Isso é possível mantendo as informações mais recentes em um baixo nível de granularidade,
20
aumentando, assim, as possibilidades de extração de informações. À medida que os dados vão
ficando obsoletos, são resumidos, aumentando nível de granularidade para manter a performance
(INMON, 1997).
Pode-se dizer que uma granularidade alta garante maior rapidez nas consultas feitas, porém,
diminui a riqueza de informações, enquanto uma menor granularidade possibilita a extração de
qualquer informação, mas acarreta maior volume de dados, conseqüentemente, maior tempo de
resposta à consulta.
2.4 Modelagem dimensional
Para Kimball (2002), a modelagem dimensional permite tornar os bancos de dados fáceis e
compreensíveis. Afirma ainda que, a modelagem dimensional difere da normalização dos sistemas
Entidade Relacionamento (ER) uma vez que a normalização tenta remover redundâncias, com a
criação de entidades, ou tabelas em um modelo relacional.
Segundo Machado (2004), modelagem dimensional é uma técnica de concepção e
visualização dos dados em um conjunto de medidas que descrevem aspectos de uma área de
negócio. Este tipo de modelagem normalmente é utilizado para apresentar dados em visões que dão
suporte a análise para tomada de decisões. Sendo assim, Machado afirma que o modelo dimensional
é formado por três elementos básicos:
•
Fatos;
•
Dimensões; e
•
Medidas.
Resumidamente, a modelagem dimensional, representa um projeto de banco de dados que
procura facilitar a compreensão das informações e um maior desempenho no momento da análise
dos dados.
Para Machado (2002), um modelo dimensional pode ser visto como um cubo de dados,
apesar de um modelo dimensional ter mais de três dimensões. A Figura 7 traz a representação de
um fato vendas por meio de um cubo.
21
Figura 7. Representação do fato vendas através de um cubo de dados
Fonte: Machado (p. 82, 2004).
2.4.1 Fatos
Um fato é uma coleção de dados que representam as medidas de um negócio, normalmente é
representado por valores numéricos e implementado em tabelas chamadas de tabelas de fato
(MACHADO, 2004). Para Kimball (1998), as tabelas de fato armazenam medições numéricas do
negócio obtidas na intersecção de todas as dimensões, os fatos mais úteis são aqueles que podem ser
adicionados.
Machado (2004), afirma que na modelagem de Data Marts, o fundamento principal deve ser
o entendimento dos requisitos de negócio necessário à gestão de negócios. Os requisitos
representam fatos a serem inseridos no DM. Portanto, fato é tudo aquilo que pode ser medido em
um negócio e sofre mudanças em suas medidas no decorrer do tempo.
Para Machado (2004), são quatro os principais elementos que participam de um fato:
•
Onde ocorreu o fato;
•
Quando aconteceu o fato;
•
Quem executou o fato; e
•
O que é objeto do fato.
22
A Figura 8 ilustra os elementos no fato compra.
Figura 8. Elementos que participam de um fato compra
Fonte: Adaptado de Machado (p.113, 2004).
Além dos elementos citados, poderiam fazer parte do fato compra outros elementos como,
por exemplo:
•
Porque ocorreu o fato? A compra ocorreu porque o produto estava em promoção
•
Como ocorreu o fato? A compra foi feita em dinheiro, cartão, etc.
2.4.2 Dimensões
Para Machado (2004, p. 80), as dimensões são elementos que participam de um fato
determinando o contexto desse fato ou negócio, apresentado as formas que o fato pode ser visto, por
mês, por país, por região, etc. “As dimensões normalmente não possuem atributos numéricos, pois
são somente descritivas e classificatórias dos elementos que participam de um fato”.
Tabelas de dimensão contêm descrições textuais necessárias para um bom entendimento do
fato, normalmente possui muitas colunas e um número não muito significativo de linhas. Os
atributos das tabelas de dimensão funcionam como restrições de consulta, agrupamento e rótulos de
relatórios de um DM (KIMBALL, 2002, p. 24).
23
Através das dimensões é viabilizada a análise multidimensional, onde podem ser
combinadas informações contidas nas tabelas de dimensão com o auxilio de ferramentas OLAP
(MACHADO, 2004, p. 129).
As dimensões desempenham um papel fundamental na utilização e compreensão de um DM.
É importante garantir a qualidade dos valores das tabelas de dimensão para facilitar a compreensão
dos fatos associados à dimensão.
“As tabelas de dimensão são os pontos de entrada para tabela de fatos. Atributos de
dimensões eficazes produzem recursos de separação e combinação (Slicing and dicing) analíticos
eficazes. As dimensões implementam a interface de usuário para o data warehouse” (KIMBALL,
2002, p. 25). A Figura 9 apresenta algumas dimensões do fato vendas aplicada ao varejo.
Figura 9. Fato vendas e suas dimensões aplicadas no varejo
Fonte: Adaptado de Takaoka (2004).
24
2.4.3 Medidas
A principal utilização de um Data Mart é para consultar dados históricos que estão
normalmente sumarizados por períodos de tempo e as mais variadas combinações de
classificação de uma informação. Mas geralmente o que se deseja ver são valores
numéricos e sua evolução ou não, em um espaço de tempo, com cálculos de transformação
desses dados (MACHADO, 2004, p. 135).
Resumidamente, as medidas ou métricas representam o resultado esperado de uma
combinação de informações em um período de tempo.
A Tabela 3 representa devoluções de vendas nos anos 2003, 2004 e 2005, agrupados por
motivo. Esses valores caracterizam uma medida que interessa na avaliação do fato devolução de
vendas.
Tabela 3. Medidas do fato devolução de vendas
Motivo
Tamanho errado
Produto com grande defeito
Produto com leve defeito
Falta de item na embalagem
Voltagem trocada
2003
123
75
243
89
2
2004
109
90
250
77
24
2005
158
101
312
167
69
Fonte: Adaptado de Machado (2004).
Operações matemáticas com esses valores permitem a obtenção de outra métrica. Baseandose nas informações da Tabela 2 é possível afirmar que os índices de devolução das vendas
aumentaram em 2005 50% sobre os últimos 2 anos.
Sendo assim, Machado (2004), afirma que as medidas se classificam em dois tipos:
•
Valores aditivos: são aqueles que permitem a aplicação das operações de soma,
subtração e media; e
•
Valores não aditivos: são valores percentuais ou relativos que representam indicadores
do fato.
A Tabela 4 apresenta exemplos de métricas aditivas e não aditivas.
25
Tabela 4. Exemplos de métricas aditivas e não aditivas
Dia da semana
Segunda-feira
Terca-feira
Quarta-feira
Quinta-feira
Sexta-feira
Sábado
Domingo
Total
%Domingo
Quantidade de devoluções
4
2
2
1
3
3
5
20
25
Fonte: Adaptado de Machado (2004).
Nesse exemplo, os valores aditivos representam as devoluções de vendas durante a semana,
o percentual de devoluções no domingo em relação à semana representa um valor não aditivo. Na
modelagem dimensional é importante saber diferenciar valores aditivos e não aditivos, a utilização
de forma incorreta de valores não aditivos torna o resultado da análise incorreta.
Segundo Machado (2004), também é importante identificar a composição de uma medida ou
métrica, o valor da receita bruta, por exemplo, pode ser composto pelo valor de venda menos o
custo da venda. Identificar os valores que compõem uma métrica torna o processo de análise mais
claro e eficiente.
2.4.4 Modelo Estrela
Segundo Machado (2004), o modelo estrela ou esquema estrela são os termos comuns para
designação de modelos de dados multidimensionais. É basicamente formado por uma entidade
central denominada Fato e um conjunto de entidades menores denominadas dimensões. A figura 10
exemplifica a formação do modelo estrela.
26
Figura 10. Modelo Estrela
Fonte: Machado (2004).
Um modelo dimensional implementado no esquema estrela possui todas as características
desejáveis para um modelo de dados para DW: é bastante simples, sua analogia com um
hipercubo de dados facilita a compreensão pelos usuários e pela sua simplicidade é possível
memorizar os elementos mais importantes. É um modelo que facilmente suporta inclusão
de novos elementos, como novas dimensões, novos atributos ou novos fatos, sem
comprometer a estrutura existente. Finalmente, sua implementação tanto em bancos de
dados multidimensionais ou bancos de dados relacionais tem um desempenho muito bom
(ZIULKOSKI, 2003, p. 25).
Para Singh (2001), o esquema estrela é um modelo simples e oferece as seguintes vantagens
importantes:
•
Permite definir uma estrutura de dados multidimensional complexa com um modelo de
dado muito simples;
•
Facilita a definição de relacionamentos hierárquicos dentro de cada dimensão e
simplifica a tarefa de criar joins ao longo de muitas tabelas;
•
Reduz o número de joins físicos que a consulta deve processar, isso melhora o
desempenho; e
•
Permite a expansão e a evolução do DW com pouca manutenção, proporcionando uma
base flexível para o crescimento do DW.
27
2.5 OLAP
Os sistemas computacionais que tem como finalidade capturar as transações e dar suporte as
atividades de negócio de uma empresa, são conhecidos como sistemas operacionais, sistemas fonte
ou ainda On-Line Transaction Processing – Processamento de Transações On-Line (OLPT)
(KLAUER e BROBST apud MARIANI, 2004).
Sistemas OLPT executam atividades básicas de inserção, atualização, consulta e eliminação
de dados de um Banco de Dados (BD) operacional, como exemplo, cita-se sistemas de contas a
pagar e receber, sistemas de controle de estoques, controle financeiro e contábil, entre outros. Esses
sistemas têm como objetivo dar suporte as áreas de negócio através do processamento das
transações, normalmente baseada em uma modelagem Entidade Relacionamento (ER).
Segundo Takaoka (2004), “uma visão multidimensional de uma área de negócio, é muito
mais importante para o processo de tomada de decisão do que uma visão baseada nos conceitos de
entidades e relacionamentos para processar eficientemente as transações”.
As aplicações OLAP On-line Processing Analítical permitem uma visão multidimensional
do negócio e caracterizam-se por: acesso a grandes volumes de dados, análise de relacionamentos
entre vários tipos de elementos do negócio, agregação dos dados, comparação de dados históricos e
apresentação dos dados em diferentes perspectivas, isto é, combinação de dimensões e execução de
cálculos complexos (ibidem).
De acordo com Kimball (1998), OLAP é o termo inventado para descrever uma abordagem
dimensional para suporte à decisão. Machado (2004), afirma que OLAP é o conjunto de ferramentas
que possibilita efetuar a exploração dos dados de um DW através da combinação de suas
dimensões.
Para Machado (2004), sob seu aspecto funcional, uma ferramenta OLAP permite análise
multidimensional dinâmica dos dados através de algumas operações básicas, sendo as principais:
•
Drill Down: a partir desta operação, aumenta-se o nível de detalhe da informação, com a
adição de atributos das dimensões para detalhamento do fato. Exemplo: uma
determinada consulta apresenta os produtos mais vendidos no decorrer ano na empresa.
Então, pode-se adicionar novas colunas para detalhar a consulta, como o mês em que
ocorreu a venda, cliente que comprou o produto, filial que realizou a venda, dados
28
relativos ao cliente como estado, cidade onde reside, sexo, idade, etc. Assim, o nível de
detalhe da informação torna-se maior a partir da exploração de atributos das dimensões,
essa operação é o drill down;
•
Drill Up ou Roll Up: Ao contrário da operação de Drill Down, o roll up ocorre quando
diminui o nível de detalhe da informação. A partir de uma visão resumida, são criadas
sumarizações, retirando do resultado os atributos das dimensões para diminuir o nível de
detalhe. A Figura 10, de que forma ocorrem as operações de drill down e roll up entre os
atributos estado e cidade de uma determinada dimensão;
Figura 11. Operações drill down e roll up
Fonte: Machado (2004).
•
Slice: Nesta operação ocorre a redução do escopo dos dados em análise. “Slice é a
operação que corta o cubo, mas mantém a mesma perspectiva de visualização de dados”
(MACHADO 2004). A Tabela 5 apresenta o volume de vendas de celulares e pagers.
Tabela 5. Volume de vendas de produtos por região
Celulares e Pagers
Janeiro
Fevereiro
Março
30
26
22
28
30
32
Volume de vendas
Região Sul
PR
SC
Fonte: Adaptado de Machado (2004).
29
Realizando a operação de slice visualizamos somente as vendas de celulares, conforme a
Tabela 6.
Tabela 6. Operação Slice sobre o volume de vendas
Volume de vendas
Janeiro
22
19
PR
SC
Região Sul
Celulares
Fevereiro
18
27
Março
18
25
Fonte: Adaptado de Machado (2004).
•
Dice: Nesta operação visualiza-se as informações a partir de uma nova perspectiva. Na
Tabela 7, as informações estavam dispostas por estado, após operação dice, são
apresentadas por mês.
Tabela 7. Operação Dice sobre o volume de vendas
Região Sul
Volume de vendas
Celulares
PR
22
18
18
Janeiro
Fevereiro
Março
SC
19
27
25
Fonte: Adaptado de Machado (2004).
2.6 Ferramentas de Apoio
Nesta seção, apresentam-se ferramentas do SQL Server Database Engine 2005, segundo
Battisti (2005), Máquina do Banco de dados SQL Server 2005. Descrevem-se as ferramentas que
serão utilizadas no projeto e suas características.
O SQL Server 2005 disponibiliza um conjunto de ferramentas completo para soluções de
Business Intelligence (BI), “o Business Intelligence Development Studio é um ambiente de
desenvolvimento comum para a criação de soluções de BI baseadas no Visual Studio, incluindo um
mecanismo de bancos de dados, serviços de análise e serviços de geração de relatórios” (DUMLER,
2005).
Um DW é parte integrante do processo de BI, que segundo Carlos Barbieri (apud PINTO,
2003), pode ser definido como “a utilização de variadas fontes de informação para se definir
estratégias de competitividade nos negócios da empresa”. O BI basicamente é composto por
30
estruturas especiais de armazenamento de informações, como Data Wharehouse (DW), Data Marts
(DM) e ODS (Operacional Data Store), contempla também o conjunto de ferramentas de
desenvolvimento de aplicações e de ferramentas ETC – Extração, Tratamento e Carga e aplicações
especiais de tratamento de dados, como OLAP e Data Mining.
Segundo Dumler (2005) o conjunto de ferramentas de BI do SQL Server 2005 é composto
por:
•
Business Intelligence Development Studio: ambiente integrado de desenvolvimento que
tem como base o Visual Studio 2005;
•
SQL Server Integration Services (SSIS): Ferramenta criada para os serviços de
Integração ou ETL.
•
SQL Server 2005 Analysis Services: apresenta ferramentas de gerenciamento e
desenvolvimento de análises OLAP, além de recursos avançados de data mining.
•
Reporting Services: é um ambiente para construção de relatórios ser personalizados e
que podem ser entregues em vários formatos.
A visualização dos dados em um ambiente Microsoft pode ser feita através do Excel nas
versões 2003 e beta 2007.
Neste trabalho, basicamente serão utilizados: o Microsoft Management Studio para criação e
manutenção do banco de dados; o SQL Server Integration Services (SSIS) que realiza as operações
de extração, limpeza, transformação e carga dos dados e o Itlviewer, onde serão criados os cubos e
também a publicação das consultas.
2.6.1 SQL Management Studio
O SQL Management Studio é a ferramenta central de administração de banco de dados do
MSSQL Server. Nela é feita a criação do banco de dados, visualização de tabelas, colunas, triggers,
views, chaves primárias, chaves estrangeiras, usuários, permissões, backup, restore, enfim, todas as
tarefas relacionadas à administração e manutenção do banco de dados.
O SQL Server Management Studio combina os recursos do Enterprise Manager, Query
Analyzer e Analysis Manager em versões anteriores do SQL Server, em um único
ambiente. Além disso, o SQL Server Management Studio funciona com quaisquer
componentes do SQL Server tais como os Reporting Services, o SQL Server Integration
31
Services (SSIS), SQL Server Móbile e Serviços de Notificação. Os bancos de dados
possuem uma única ferramenta compreensiva que combina ferramentas gráficas fáceis de
serem utilizadas com as ricas capacidades de scripts. A integração de gerenciamento e
criação de uma única ferramenta unida com a habilidade de gerenciar todos os componentes
do SQL Server fornecem um banco de dados com produtividade melhorada (DUMLER,
2005).
A Figura 12 apresenta a interface do SQL Management Studio.
Figura 12. Interface do SQL Management Studio
2.6.2 SQL Server Integration Services (SSIS)
Segundo Dumler (2005) o SSIS permite às organizações integrar e analisar com facilidade
os dados de várias fontes de informações heterogêneas através dos recursos de ETL (extração,
transformação e carregamento) de dados para data warehouses proporcionando a integração de
dados espalhados por toda a empresa. A Figura 13 apresenta a interface do SISS.
32
Figura 13. Interface do SQL Server Integration Services
Uma das funcionalidades presentes no SSIS é a criação de pacotes DTS – (Data
Transformation Services). Um pacote DTS permite realizar a cópia de informações entre bases de
dados distintas e também a importação de arquivos formatados. Isso é possível através da definição
de origem e destino das informações no DTS.
Durante a cópia os dados também podem ser transformados e adaptados para gravação
correta no destino, o pacote DTS pode ter sua execução agendada conforme a necessidade do
sistema e incorporado junto a outros pacotes a partir do SSIS. A Figura 14 ilustra uma etapa da
construção de um pacote DTS.
33
Figura 14. Etapa da construção de um pacote DTS no SQL 2005
2.6.3 Gerador de relatórios Itlviewer
No gerador de relatórios Itlviewer, serão criados os cubos OLAP e também será feita a
publicação das consultas e relatórios. O Itlviewer é um gerador de relatórios da empresa Intelidata
Informática LTDA, atualmente utilizado pela equipe de Tecnologia de Informação (TI) da Havan
Lojas de Departamento LTDA para criação de relatórios a partir da base operacional das lojas
Havan.
O Itlviewer trabalha com o conceito de dicionário de dados e permite a geração de relatórios
complexos em diferentes formatos (INTELIDATA, 2006).A Figura 15 apresenta a interface de
edição de relatórios no Itlviewer.
34
Figura 15. Interface de edição de relatórios do Itlviewer
A criação de um relatório no Itlviewer inicia-se com a definição da tabela base. A partir
desta definição, tornam-se disponíveis para uso no relatório atributos de todas as tabelas
relacionadas com a tabela base. Também podem ser utilizados atributos de outras tabelas que não
tem relacionamento direto com a tabela base através de estruturas conhecidas como sub-consultas
ou sub-relatórios.
Outra definição importante é a categoria do relatório, para geração dos cubos OLAP serão
utilizadas as categorias: Cubo Instantâneo, Processar Cubo e Cubo pré-processado.
•
Cubo Instantâneo: permite criar relatórios que processam os dados apresentando na tela
como saída um cubo OLAP.
•
Processar Cubo: permite a criação de relatórios que apresentam como saída um arquivo
com extensão “.cub” contendo os dados já processados para posterior visualização. Isso
permite flexibilidade para trabalhar com as informações geradas no cubo.
•
Cubo Pré-processado: esse tipo de relatório permite a visualização de um cubo préprocessado.
35
A Figura 16 mostra como é feita a definição de categoria de um relatório no Itlviewer.
Figura 16. Definição de categoria no Itlviewer
A disposição dos dados apresentados em um cubo OLAP gerado pelo Itlviewer é definida a
partir do campo orientação no cubo, que podem ser: página, linha, coluna, dado e invisível. Além de
definir a forma que os dados serão apresentados é possível também utilizar máscaras pré-definidas e
aplicar expressões para conversão de tipos ou chamadas a diversas funções disponíveis que podem
também ser criadas no banco de dados e acionadas a partir do Itlviewer. A orientação do campo em
um relatório OLAP é feita conforme a Figura 17.
36
Figura 17. Definição da orientação no cubo no Itlviewer
A interface de apresentação do cubo é amigável e intuitiva, permite a aplicação de todas as
operações básicas de OLAP com as opções do menu movimentação. É possível a aplicação de
fórmulas, utilização de marcadores e filtros e exportação para o MS Excel ou arquivos em formato
HTML, conforme a Figura 18.
37
Figura 18. Cubo de dados no Itlviewer
Baseado na facilidade de utilização e desenvolvimento dos cubos OLAP, esta ferramenta foi
escolhida para criação e publicação dos relatórios do projeto.
38
3 PROJETO
Este capítulo destina-se a especificação do projeto proposto, apresentando os principais
processos da área comercial da Havan, levantamento das necessidades gerenciais e o modelo
dimencional gerado para atender as necessidades.
3.1 Área Comercial da HAVAN
De acordo com os objetivos, o presente trabalho se propõe a construção de um Data Mart
para área comercial da Havan Lojas de Departamentos LDTA.
A Havan é uma loja de produtos nacionais e importados, com sua matriz situada em
Brusque, SC. Em sua linha de produtos comercializados estão tecidos para vestuário e
decoração, artigos de cama, mesa e banho, utensílios domésticos, material esportivo e
escolar, brinquedos, ferramentas, artigos para pesca e camping, eletro-eletrônicos, malas,
calcados e confecções adultas e infantis (HAVAN, 2006).
Considerada a maior loja do Brasil, com 30.000 (trinta mil) m2, a Havan coloca a disposição
de seus clientes mais de 80.000 (oitenta mil) produtos. A rede Havan possui atualmente 10 (dez)
mega lojas sendo 4 (quatro) no estado do Paraná e 6 (seis) em Santa Catarina (HAVAN, 2006). As
operações de venda que ocorrem nas filiais desencadeiam uma série de processos que gravam dados
locais como o Controle de Estoque e outros atualizados de forma centralizada na matriz a exemplo
da atualização da dívida de clientes.
A Área Comercial da HAVAN é responsável por todas as operações que estão relacionadas
entre a compra e venda de produtos. A operação mais importante em todo o processo é a venda, a
partir dela são feitas análises que determinam o ponto de reposição dos produtos, identificação dos
melhores clientes, resultado das campanhas de marketing, etc. Também é na operação de venda
onde estão reunidas as informações de produtos, clientes e condições de pagamento. De maneira
resumida, os principais processos da área comercial são:
Processo de compra: O processo de compra ocorre no departamento de compras. O
comprador e seus auxiliares geram um pedido de compra diretamente ao fornecedor ou seu
representante baseados na movimentação de estoque dos produtos e na análise de mercado para
novos produtos que ainda não são comercializados na rede e portanto não possuem movimentação.
Processo de venda: Os clientes após escolherem os produtos que estão em gôndolas ou
expositores, dirigem-se ao setor de caixa chamado internamente de checkout. No checkout estão os
PDV´s ou pontos de venda, são em media 15 PDV’s por loja, atualmente cerca de 150.
No PDV, o cliente é atendido por uma operadora de caixa que registra os produtos da venda
através da leitura do código de barras e solicita ao cliente a forma de pagamento para finalizar a
operação de venda. Entre as principais formas de pagamento estão: Dinheiro, Cartão de Crédito,
Cartão de Débito, Cheque, Cartão Havan. Nas compras com cheque e cartão próprio, o cliente deve
ser cadastrado no setor de registro de clientes e possui um limite de crédito mensal definido por um
analista de crédito. Todas as informações da venda são registradas na impressora de cupom fiscal e
gravadas em banco de dados.
Dependendo do tipo de produto que o cliente deseja comprar, pode ocorrer também a pré
venda. Nesta operação o cliente é acompanhado por um atendente que registra antecipadamente os
produtos da venda e entrega ao cliente um cartão de controle de compras. O cartão de controle de
vendas segue com o cliente até o PDV onde é lido descarregado para então registrar a operação na
impressora de cupom fiscal enquanto a mercadoria segue para o setor de pacote onde é embalada.
Nesse caso, após o pagamento o cliente retira a mercadoria no setor de pacote.
Processo de Devolução de Vendas: Ocorre no setor de devolução de mercadorias, o cliente
que deseja trocar a mercadoria, apresenta para o funcionário de setor de devolução o cupom fiscal
referente a compra e informa o motivo da devolução. Com estas informações é gerado o boletim de
devolução que além do motivo grava o destino do produto, se ele vai novamente para área de venda
ou para uma assistência técnica, por exemplo. Para o cliente é entregue um documento de crédito no
valor dos itens devolvidos, que pode ser utilizado para pagamento de uma nova compra ou de
parcelas em aberto de compras anteriores.
As tomadas de decisões da área comercial da Havan ocorrem na maioria dos casos baseadas
em informações relativas ao processo de vendas. Existem decisões programadas a exemplo da
reposição de estoque das filiais e decisões não-programadas como definição de campanhas de
marketing, promoções e decisões relativas a novas linhas de crédito para o cartão Havan. Para as
decisões não-programadas, existe a necessidade de informações flexíveis que permitam uma melhor
análise das vendas.
3.2 Comunicação entre Filiais
40
A presente seção descreve o cenário de comunicação entre as lojas, focando na infraestrutura de rede da Havan.
A Havan possui uma estrutura de MPLS (Multi Protocol Label Switch) para comunicação
entre a matriz e filiais, toda rede é protegida por um firewall com sistema operacional Red Hat 9.0.
A MPLS é vista como uma rede única dados destinada ao destinada ao tráfego corporativo
de dados para as empresas necessitam interligar matriz, filiais e parceiros em tempo real,
possibilitando a transmissão otimizada de dados, voz e vídeo entre os pontos de uma rede
corporativa com qualidade e segurança (EMBRATEL, 2006). A Figura 19 representa a estrutura de
rede da Havan.
Figura 19. Topologia da rede Havan
41
Cada filial possui uma estrutura completa e pode trabalhar de forma separada da matriz, o
PDV, responsável pelo registro das vendas possui três modos de operação.
•
Base local: O PDV trabalha isolado, sem comunicação de rede com o servidor da loja,
com um banco de dados local.
•
Off Line: A loja trabalha sem comunicação com a matriz, e os dados são registrados no
servidor da loja.
•
On Line: Todas as operações são replicadas para a matriz.
3.3 Levantamento das Necessidades Gerenciais
O levantamento das necessidades gerenciais ocorreu através de entrevistas formais e
informais com diretores e gerentes da Havan. O gerente de TI da Havan, Valter Soares e sua equipe
apoiaram o levantamento das necessidades gerenciais. Um fator favorável desta participação é o
vasto conhecimento que a equipe possui sobre os processos e sistemas utilizados na empresa (Itlsys
MSM e Itlsys SQL) além de alguns conhecimentos básicos sobre DW.
A etapa de levantamento de necessidades também contou com o apoio de gerentes e
responsáveis da área comercial e marketing que receberam por e-mail uma apresentação contendo
os conceitos envolvidos em um projeto de DW, justificativas para implementação e etapas do
projeto.
Nas entrevistas foram reforçadas as vantagens da implantação de um DM para criação de
relatórios gerenciais em relação a criação destes relatórios diretamente na base de dados do sistema
Itlsys, foram apresentadas algumas sugestões com informações relevantes para tomada de decisões
e que atualmente não são disponibilizadas nos relatórios do sistema. Com base nessas informações,
os gestores definiram quais consultas ou relatórios poderiam suporte as suas decisões. Essa
definição não ocorreu de forma precisa, mas deixando clara a idéia das suas necessidades, foi
definida também a granularidade para as informações.
Foram levantadas necessidades de todas as áreas envolvidas no processo comercial, mas
foram priorizadas pelos gestores as necessidades relativas ao processo de venda, que foram:
•
Necessidade Gerencial 1: relatório que apresente o resultado das operações de venda,
apresentando valor de vendas em promoção, fora de promoção, lucratividade dos
42
produtos e quantidade vendida, organizando o resultado por departamento, setor, filial e
período;
•
Necessidade Gerencial 2: relatório que torne possível a análise das campanhas de
marketing que estão em vigor durante o processo de venda, relacionando o investimento
da campanha por veículo, praça e período;
•
Necessidade Gerencial 3: relatório para análise dos clientes associados a venda buscando
identificar vendas por faixa etária, faixa de renda, cidade, estado, apresentando no
relatório informações de produto, setor, departamento, período, filial, entre outras;
•
Necessidade Gerencial 4: relatório que permita análise da variação do ticket médio de
vendas, total de visitantes, operações de venda e pagamentos entre as filias, por período
levando em consideração fatores como eventos e feriados nas diferentes filiais;
•
Necessidade Gerencial 5: relatório para análise das devoluções de vendas por período,
filial, tempo após a compra, motivo, resolução, e todas as informações relevantes para
classificação de produtos e clientes.
3.4 Modelo dimensional
Para atender as necessidades de informação gerencial, foram criadas tabelas fato e dimensão
para cada necessidade, consolidando depois fatos e dimensões em um modelo único, conforme
metodologia sugerida por Machado (2004). A modelagem foi feita com o auxilio da ferramenta
Microsoft Visio 2007 Beta, nesta seção será apresentado o modelo de dados onde cada atributo do
modelo será detalhado na seção 4.2 Implementação dos processos de mapeamento, extração e
transformação.
O modelo gerado para atender a primeira necessidade, relatório que apresente o resultado
das operações de venda, apresentando valor de vendas em promoção, fora de promoção,
lucratividade dos produtos e quantidade vendida, organizando o resultado por departamento, setor,
filial e período, foi:
•
Granularidade: diária;
•
Dimensões: tempo, produto, indicador_promoção e filial;
•
Fato: vendas; e
43
•
Medidas:
quantidade,
valor_venda,
desconto_comercial,
desconto_promocional,
despesas_tributação, percentual_lucratividade, valor_lucratividade, preço_custo.
Para definição dos valores de lucratividade, será aplicada a seguinte fórmula:
valor_lucratividade = valor_venda - (despesas_tributação + preço_custo + despesas_operacionais).
Estas informações são apresentadas na Figura 20.
Figura 20. Modelo para atender a necessidade gerencial 1
Para atender a próxima necessidade, um relatório que torne possível a análise das
campanhas de marketing que estão em vigor durante o processo de venda, relacionando o
investimento da campanha por veículo, praça e período, foi definido para o modelo:
•
Granularidade: diária;
•
Dimensões: praça, veículo, tempo, programa e mídia;
•
Fato: campanha; e
44
•
Medidas: qtd_inserções, valor_venda, desconto_1, desconto_2, valor_investimento.
O modelo gerado é apresentado na Figura 21.
Figura 21. Modelo para atender a necessidade gerencial 2
A terceira necessidade gerencial é um relatório para análise dos clientes associados a venda
buscando identificar vendas por faixa etária, faixa de renda, cidade, estado, apresentando no
relatório informações de produto, setor, departamento, período, filial, entre outras. O modelo para
atender essa necessidade tem os valores:
•
Granularidade: diária;
•
Dimensões: tempo, produto, cliente, faixa_etária, faixa_renda, faixa_limite e filial.;
•
Fato: vendas; e
•
Medidas: quantidade, valor_venda, desconto_comercial e desconto_promocional.
A Figura 22 apresenta o diagrama criado para atender esta necessidade.
45
Figura 22. Modelo para atender a necessidade gerencial 3
Na seqüência, a próxima necessidade gerencial é um relatório que permita análise da
variação do ticket médio de vendas, total de visitantes, operações de venda e pagamentos entre as
filias, por período levando em consideração fatores como eventos, feriados e condições do tempo
nas diferentes filiais. Para esta necessidade, foi gerado o modelo:
•
Granularidade: diária;
•
Dimensões: tempo, evento, cond_tempo e filial
•
Fato: operações; e
•
Medidas:
qtd_visitantes,
qtd_vendas,
valor_recebimentos e ticket_médio.
46
qtd_recebimentos,
valor_vendas,
Estas informações são apresentadas na Figura 23.
Figura 23. Modelo para atender a necessidade gerencial 4
Para última necessidade gerencial relatório para análise das devoluções de vendas por
período, filial, tempo após a compra, motivo, resolução, e todas as informações relevantes para
classificação de produtos e clientes, o modelo gerado foi:
•
Granularidade: diária;
•
Dimensões: tempo, cliente, produto, motivo, resolução faixa_dias_compra e filial
•
Fato: devoluções; e
•
Medidas: qtd_devolvida, valor_devolução,.
Estas informações estão na Figura 24.
47
Figura 24. Modelo para atender a necessidade gerencial 5
Finalmente, o modelo final gerado de forma consolidada é apresentado na Figura 25.
48
Figura 25. Modelo final consolidado
49
4 DESENVOLVIMENTO
Esta fase dividiu-se, basicamente, em cinco: construção do modelo físico do DM;
implementação dos processos de mapeamento, transformação e extração; implementação dos
processos de carga; a construção das consultas no ItlViewer para atender as necessidades
gerenciais; e por fim a validação junto aos gestores.
4.1 Construção do modelo físico do Data Mart
A construção do modelo físico do DM foi feita com o auxilio do Microsof SQL
Management Studio. Nesta etapa foram criadas as tabelas fato, dimensão e todas as tabelas
necessárias para os processos de mapeamento, transformação, extração e carga do DM.
As tabelas foram criadas em duas bases de dados: a primeira chamada de dm_tmp_havan
para a área de transição, onde foram carregados os dados das bases operacionais e outra chamada
dm_havan onde foram carregadas as informações após o tratamento dos dados. A Figura 26
apresenta a edição da tabela fato_vendas da base de dados dm_havan no Microsof SQL
Management Studio.
Figura 26. Edição da tabela fato_vendas
4.2 Implementação dos processos de mapeamento, extração e transformação
Durante esta etapa, foi feito mapeamento dos dados das bases de dados operacionais para as
tabelas da área de transição. Após o mapeamento os próximos passos foram a extração e
transformação dos dados mapeados. De maneira resumida, está sendo são apresentadas as tabelas
que compõe a área de transição e a implementação dos processos em cada tabela:
•
dimensão_tempo: A geração da tabela dimensao_tempo é feita apenas na carga inicial,
para geração das chaves tempo foi criado um algoritmo na linguagem SQL que gera
todos os dias, meses e anos a partir dos parâmetros ano inicial e ano final, levando em
consideração ano bissexto e trocas de estação do ano. Algoritmo utilizado para carga da
tabela dimensão tempo esta representado no Apêndice B.
•
dimensao_dias_compra: Esta tabela também foi gerada apenas na carga inicial, nela
foram atribuídas as faixas de 0 a 10 dias, entre 11 e 20 dias, entre 21 e 30 dias e acima
de 30 dias. Nesta tabela foram incluídos os atributos limite_inicial e limite_final, para
facilitar a identificação do fato dentro das faixas criadas;
•
dimensao_motivo: Nesta tabela foram mapeados os atributos da tabela motivos de
devolução no banco de dados operacional ITLSYS;
•
dimensao_filial: Para dimensão filial, foram mapeados os campos do cadastro de filiais,
e cadastro de cidades ;
•
dimensao_resolucao: Foram mapeados os campos do cadastro de resolução para
devolução;
•
dimensao_cliente: Para dimensão clientes são carregados valores da tabela de cadastro
de clientes e feito o tratamento do campo sexo do cliente que na base operacional estão
gravado com os valores 0 e 1 convertendo 0 masculino e 1 para feminino. Na carga
inicial foram carregados todos os clientes enquanto na carga incremental apenas clientes
cadastrados ou alterados após a última carga;
•
dimensao_produto: Na dimensão produtos foram mapeados campos do cadastro de
produtos, cadastro de níveis hierárquicos, cadastro de marcas e fabricantes e cadastro de
fornecedores. Assim como no cadastro de clientes, na carga inicial são carregados todos
os produtos enquanto na carga incremental apenas os produtos novos cadastrados ou
alterados. Durante a carga dos fatos associados aos produtos, observou-se inúmeros
51
produtos não cadastrados. Isso porque na banco de dados de produção não existem
registros de produtos que não são mais comercializados. Para não perder a referência
histórica foi feita uma carga adicional de produtos de outra base de dados, na primeira
carga haviam sido carregados cerca de 130.000 produtos, com a carga adicional passou
para aproximadamente 250.000. Para carga adicional, foi criado um programa em MSM
que faz a leitura de um arquivo de produtos e gera um outro arquivo com os produtos
que não pertencem ao primeiro arquivo;
•
dimensao_faixa_etaria: Gerada apenas na carga inicial esta tabela contém as faixas
abaixo de 20 anos, de 21 à 30 anos, 31 à 40 anos, 41 à 50 anos, 51 à 60 anos e acima de
60 anos.
•
dimensao_limite: Esta tabela foi carregada apenas na carga inicial com diversas faixas
de limites de crédito liberados aos clientes.
•
dimensao_parcelamento: Tabela carregada apenas na carga inicial com as faixas de 0 à 5
parcelas, de 6 à 10 parcelas e acima de 10 parcelas.
•
dimensao_renda: Foram carregadas para esta tabela, diversas faixas de renda de clientes
somente na carga inicial.
•
dimensao_veiculo: Os veículos de divulgação das campanhas de marketing, estão
gravados no banco de dados MSM, para carga dos veículos, foi criado um programa que
gera um arquivo texto para ser importado na área de transição.
•
dimensao_programa: Assim como os veículos, os programas também são importados a
partir de arquivos gerados em MSM. Na dimensão programa, foi incluído o tipo de
mídia que no projeto foi considerada uma dimensão, porém no desenvolvimento dos
processos de carga observou-se que mídia estava relacionada ao programa na base de
dados de produção.
•
dimensao_praca: As praças de divulgação são importadas do MSM através de arquivo
texto.
•
dimensao_pagamento: Para esta dimensão foram carregadas as formas de pagamento
aceitas no sistema de caixa das lojas Havan, entre elas dinheiro, cheque, cartão de
crédito e débito. Estas formas de pagamento foram carregadas apenas na carga inicial.
52
•
dimensao_condicao_tempo: Foram carregadas as condições do tempo do banco de dados
MSM através de arquivo texto.
•
dimensao_evento: Para esta dimensão, são carregados os eventos cadastrados em MSM
no sistema registro diário de visitas, cadastro de eventos.
•
fato_devolucoes: Para carga inicial das devoluções foi necessário criar um programa na
linguagem MSM para carregar as devoluções feitas nos anos anteriores a 2007. O
programa criado gera um arquivo texto com separadores que é importado para a tabela
temporária tmp_devolucoes. Este programa foi executado via serviço de terminal remoto
(telnet) em todas as filiais gerando um arquivo para cada filial que posteriormente foi
importado para área de transição na tabela temporária. Após este processo, é feita a
atribuição das chaves correspondentes ao fato e carregada a tabela fato_devolucao. O
programa em MSM que gera o arquivo das devoluções está representado no Apêndice C.
Para as devoluções gravadas no banco de dados SQL Server, foi feito o mapeamento dos
campos do sistema de devolução de vendas e a carga para a tabela temporária, após esta
etapa, foi feita a carga da tabela fato_devolucao com as chaves correspondentes. A carga
incremental é feita a partir das devoluções gravadas em SQL.
•
fato_vendas: para carga da tabela fato_vendas, foram mapeados os campos das tabelas
relacionadas a operação de venda no sistema de caixa das lojas Havan, entre elas, fita de
caixa, itens da fita de caixa e formas de pagamento da venda para uma tabela temporária
denominada tmp_vendas. Os dados foram tratados recebendo as chaves correspondentes
para faixa_etaria, renda, parcelamento, pagamento, limite e carregados para a tabela
fato_vendas.
•
fato_campanhas: A carga das campanhas de marketing, foi toda feita a partir do banco
de dados MSM. Tendo como base o sistema de programação de mídia é gerado um
arquivo texto, feita a importação para a tabela temporária tmp_campanhas, onde é feita a
geração das chaves e carga da tabela fato_campanhas.
•
fato_operacoes: Para o resumo das operações das lojas, foram criadas duas tabelas
temporárias, tmp_msm_operacoes e tmp_sql_operacoes. Uma para dados com origem
no banco de dados MSM e outra para dados do SQL. Para carga da tabela
fato_operacoes foi feita a junção dos dados e geração das chaves correspondentes.
53
4.3 Implementação dos processos de carga dos dados no Data Mart
Esta etapa foi dividida em duas tarefas: carga inicial e carga incremental. Na carga inicial
foram carregados todos os dados disponíveis enquanto na carga incremental são carregados apenas
os dados que foram inseridos ou alterados após a última carga.
Para realização dos processos de carga, foram criados pacotes DTS (Data Transformation
Services) que acessam os bancos de dados da base operacional ou arquivos gerados a partir de
programas criados em MSM, realizam o tratamento e a carga dos dados para a área de transição e
para o DM. A Figura 27 representa a construção de um pacote DTS a partir do SQL Server
Integration Services no SQL Server 2005.
Figura 27. Construção de um pacote DTS SQL 2005
Os pacotes criados para realização das tarefas de mapeamento, extração, transformação e
carga dos dados da área de transição foram:
•
carga_inicial_dimensoes_trn;
•
carga_incremental_dimensoes_trn;
•
carga_inicial_fatos_trn; e
•
carga_incremental_fatos_trn.
54
Além do mapeamento, extração, transformação dos dados, é feita também a geração de
chaves de warehousing, utilizando como técnica a geração de chaves de numero inteiro atribuídas
seqüencialmente que segundo Ralph Kimball, (1998 p.220) a vantagem de usá-las é que ocupam
pouco espaço e permitem, talvez, um melhor desempenho. A Figura 28 apresenta as principais
etapas dos processos de carga na área de transição e no DM.
Figura 28. Processos dos pacotes DTS
Como todo tratamento dos dados é feito durante a carga da área de transição, a carga dos
dados no DM é mais simples. A área de transição sempre contém os dados já tratados e que ainda
não estão disponíveis no DM, assim, no final de cada processo da área de transição é chamado um
processo que faz a carga dos dados no banco de dados do DM. Os processos criados para carga dos
dados no DM são:
•
carga_inicial_dimensoes_dm;
•
carga_incremental_dimensoes_dm;
•
carga_inicial_fatos_dm; e
•
carga_incremental_fatos_dm.
Os processos de carga foram construídos mantendo a compatibilidade entre o SQL Server
2005 e SQL Server 2000, para isso foram utilizados na construção dos pacotes DTS apenas
componentes básicos disponíveis nas duas versões. A Figura 29 representa um pacote DTS
visualizado no SQL Server 2000.
55
Figura 29. Pacote DTS SQL 2000
Após o desenvolvimento dos processos de carga, a carga inicial foi executada de forma
manual, devido ao grande volume de dados carregados nos fatos. Os processos da carga incremental
tiveram sua execução agendada no banco de dados para executar de forma automática durante a
noite, em caso de falha na execução de alguma das tarefas o operador será notificado por e-mail.
Como alguns processos de carga dependem de um arquivo gerado em MSM, também foi
agendada a execução dos programas em MSM que geram arquivos que são importados no DM. A
Figura 30, apresenta o agendamento do processo de carga incremental.
56
Figura 30. Agendamento da carga incremental
4.4 Construção das consultas
Antes da construção das consultas foi necessária a preparação do ambiente para possibilitar
o uso do Itlviewer, foi feita a instalação e configuração do sistema Itlsys na database dm_havan,
isso porque na versão disponível o Itlviewer é chamado a partir do menu do sistema Itlsys. O
próximo passo foi o cadastro da metadata. A metadata do Itlviewer é o conjunto de informações
composto pelas tabelas, campos de tabela e relacionamentos entre as tabelas da base de dados que
será utilizada na geração das consultas.
A Figura 31 representa o cadastro da tabela
fato_devolucoes na metadata.
57
Figura 31. Cadastro de tabelas na metadata do Itlviewer
Após o cadastro das tabelas com seus campos e relacionamentos na metadata, foi possível
gerar os relatórios a partir do Itlviewer, este cadastro foi realizado para todas as tabelas, pois não
existe nenhuma forma de importação das tabelas, campos e relacionamento do banco de dados para
o Itlviewer. No Apêndice D existe um documento que mostra passo a passo como gerar um
relatório com o auxilio do Itlviewer.
No projeto, de acordo com a Seção 3.3, Levantamento das Necessidades Gerenciais, foram
priorizadas cinco necessidades, para atendê-las foram criados relatórios no formato de cubos OLAP,
sendo um relatório para atender cada necessidade conforme estão apresentados abaixo:
Necessidade Gerencial 1: relatório que apresente o resultado das operações de venda,
apresentando valor de vendas em promoção, fora de promoção, lucratividade dos produtos e
quantidade vendida, organizando o resultado por departamento, setor, filial e período.
Para esta primeira necessidade, foi gerado um cubo de dados que apresenta as vendas em
promoção e fora de promoção organizadas por departamento e setor, exibindo quantidade e valor de
vendas. A Figura 32 representa este cubo onde esta sendo feita a operação OLAP de Drill Down, a
58
partir desta operação, aumenta-se o nível de detalhe da informação, com a adição de atributos das
dimensões para detalhamento do fato conforme apresentado na seção 2.5. Neste caso, ocorre o
detalhamento do departamento em setores. Neste cubo a informação poderia ainda ser detalhada em
mais níveis, por exemplo, fornecedor e produto incluindo as informações disponíveis na área de
projeção das informações.
Figura 32. Relatório com Drill Donw, necessidade gerencial 1
A operação contrária ao Drill Down, é o roll up que ocorre quando diminui-se o nível de
detalhe da informação. Isso poder ser feito retirando do resultado atributos das dimensões, gerando
uma visão resumida ou sumarizada. Na Figura 33 que representa a operação de roll up, abaixo, a
informação dos setores foi oculta resumindo os dados por departamento.
59
Figura 33. Relatório com Roll Up, necessidade gerencial 1
As informações disponíveis neste relatório permitem aos gestores analisar o resultado das
vendas dos departamentos, setores, fornecedores e produtos em diferentes períodos comparando de
forma rápida a diferença entre vendas em promoção e fora de promoção, na Figura 33, por exemplo,
pode-se observar que embora os departamentos que tiveram destaque como Bazar e Cristais, Cama
Mesa e Banho e Escolar, foram os que tiveram o maior volume de vendas em promoção.
Necessidade Gerencial 2: relatório que torne possível a análise das campanhas de marketing
que estão em vigor durante o processo de venda, relacionando o investimento da campanha por
veículo, praça e período.
Para atender esta necessidade, foi gerado um cubo de dados com as informações das
campanhas de marketing solicitadas tendo como foco as inserções em televisão, conforme relatório
apresentado na Figura 34.
60
Figura 34. Relatório para necessidade gerencial 2
Como na interface de visualização dos cubos o Itlviewer permite a geração de gráficos, na
Figura 35 esta sendo apresentado um gráfico a onde constam o número de inserções na mídia por
mês em um comparativo dos anos 1, 2 e 3.
Figura 35. Gráfico para necessidade gerencial 2
61
Estas informações permitem ao gestor confrontar este gráfico que representa as inserções na
TV, com um gráfico gerado a partir das informações de venda do mesmo período e verificar se o
número de inserções na mídia teve o resultado esperado no volume de vendas ou no número de
visitantes das lojas conforme Figura 38.
Necessidade Gerencial 3: relatório para análise dos clientes associados a venda buscando
identificar vendas por faixa etária, faixa de renda, cidade, estado, apresentando no relatório
informações de produto, setor, departamento, período, filial, entre outras. A Figura 36 representa o
cubo gerado para esta necessidade, onde através do pivoteamento das informações, esta sendo
apresentada a quantidade de vendas de um departamento por estado e sexo do cliente.
Figura 36. Relatório para necessidade gerencial 3
Já na Figura 37, o mesmo relatório apresenta uma operação de slice, ocorre a redução do
escopo das informações em análise, mantendo-se a mesma perspectiva, visualizando somente
informações do estado de Santa Catarina.
Figura 37. Relatório com slice, necessidade gerencial 3
62
As informações deste relatório permitem aos gestores a análise das vendas a partir de
atributos que classificam os clientes como faixa etária, faixa de renda, sexo, entre outros. Na Figura
36, por exemplo, pode-se observar que a maior parte das vendas do departamento Cama Mesa e
Banho foi para o público Feminino. Com base em informações deste tipo, podem sugeridas
alterações no layout da loja, para promover a venda de produtos que se destinam a um determinado
público.
Necessidade Gerencial 4: relatório que permita análise da variação do ticket médio de
vendas, total de visitantes, operações de venda e pagamentos entre as filias, por período levando em
consideração fatores como eventos e feriados nas diferentes filiais.
Foi gerado um cubo de dados que atende a necessidade, na Figura 38, esta sendo
apresentado um fragmento deste cubo que apresenta o número de visitantes em um determinado
período.
Figura 38. Relatório para necessidade gerencial 4
Na Figura 39, ocorre a visualização das as informações a partir de uma nova perspectiva,
definida como operação Dice. As informações que estavam sendo apresentadas por dias, passaram a
ser apresentadas por mês e condição do tempo.
63
Figura 39. Relatório com Dice, necessidade gerencial 4
Este relatório permite aos gestores visualizar como as condições do tempo, eventos e
feriados podem influenciar nas operações das lojas, é possível também um acompanhamento do
número de visitantes da loja por período, como na Figura 38 pode-se perceber que ocorreu um
aumento significativo no número de visitantes nos dias 6 e 7 no mês 4. Este cubo de dados permite
a identificação do evento esta associado a esse aumento do número de visitantes, assim pode-se
preparar a equipe de vendas para um evento futuro.
Necessidade Gerencial 5: relatório para análise das devoluções de vendas por período, filial,
tempo após a compra, motivo, resolução, e todas as informações relevantes para classificação de
produtos e clientes.
Como as devoluções de vendas ocorrem quando o cliente não está contente com o produto
por algum motivo específico, o relatório criado permite a análise do motivo, a resolução dada para o
produto devolvido, tempo após a compra que ocorreu a devolução, entre outras informações. O
relatório gerado esta sendo apresentado na Figura 40.
64
Figura 40. Relatório para necessidade gerencial 5
O Relatório 5 permite a análise das devoluções de venda, por motivo da devolução, tempo
após a compra entre outros filtros. Com base nas informações da Figura 40 pode-se observar quais
departamentos geram devoluções em diferentes faixas de tempo e realizar ações para reduzi-las.
4.5 VALIDAÇÃO
Após a carga dos dados no DM e a criação das consultas, foi feita a validação das
informações comparando resultados obtidos através dos cubos OLAP com os dados do ambiente de
produção. Este processo foi feito para cada relatório:
•
Relatório 1: Foram comparadas as informações do Relatório 1 referentes a operações de
venda com foco nos produtos da venda, com os relatórios de fechamento de caixa para
fechamento do valor de vendas e da conta corrente de produtos para validar as
quantidades.
65
•
Relatório 2: Neste caso as informações das campanhas de marketing foram comparadas
com a programação de eventos do ambiente operacional. Observou-se durante a
validação que nos dias em que eram feitas duas inserções de mídia no mesmo programa,
no ambiente operacional estava sendo gravado o valor unitário, porém na visualização
da programação de eventos esse valor era multiplicado pelo número de inserções. Sendo
assim foi feita a correção nos processo de carga para fazer o mesmo tratamento.
•
Relatório 3: Para validação das informações do Relatório 3 referentes a operações de
venda com foco no cliente da venda, foram realizadas comparações com a conta corrente
dos clientes, liberação de limite de crédito e fechamento do caixa. Observou-se apenas
que inúmeros clientes da base operacional não possuem em seu cadastro a data de
nascimento, para estes casos no relatório este campo recebeu o valor “Não Definida”
(ND).
•
Relatório 4: Neste relatório que apresenta o resumo das operações das lojas, foram
comparadas as informações com os dados do registro diário de visitas e com o
fechamento de caixa. Observou-se que na base operacional, em alguns dias não existem
informações referentes ao número de visitantes e condições do tempo na filial, para estes
casos será apresentado o valor ND.
•
Relatório 5: Foram comparadas as informações geradas pelo cubo de devoluções com o
boletim de devolução de vendas. Durante as primeiras validações, surgiram muitos
produtos que não estavam carregados no DM, após a carga adicional feita a partir de
uma base operacional em MSM esta situação foi corrigida.
Após o processo de validação citado, os cubos OLAP foram disponibilizados a gerência de
Tecnologia de Informação da Havan Lojas de Departamentos, para análise e aprovação junto aos
gestores da Área Comercial. Como instrumento de validação foram elaborados questionamentos
referentes aos cubos e encaminhados por e-mail juntamente com as necessidades gerenciais e seus
objetivos, as perguntas e a síntese das respostas está apresentada a seguir:
Pergunta 1: O relatório da forma que esta sendo apresentado, atende a necessidade
gerencial identificada?
Pergunta 2: Na sua opinião, o que deveria ser alterado ou melhorado no relatório?
66
Relatório 1
Resposta à Pergunta 1: Sim, é possível analisar a partir do relatório o desempenho das
vendas por classe de produtos e promoção e fora de promoção. Sabemos que a lucratividade não
poderá ser apresentada atualmente, pois estamos passando por alguns refinamentos de processo e
alguns valores não estão sendo gravados na operação de venda.
Resposta à Pergunta 2: Neste relatório seria importante a inclusão do dia da semana que
facilitaria a análise das vendas dos finais de semana.
Relatório 2
Resposta à Pergunta 1: Sim, pois é possível fazer um comparativo das inserções na mídia de
um período curto ou de vários anos em tempo real.
Resposta à Pergunta 2: Se for possível associar o valor do investimento publicitário com a
venda gerada no período.
Relatório 3
Resposta à Pergunta 1: Sim, é possível analisar os dados que compõem o relatório e com ele
identificar o perfil dos nosso clientes.
Resposta à Pergunta 2: Seria muito importante demonstrar o número de vezes que o cliente o
cliente retorna na loja para efetuar compras dentro de um determinado período.
Relatório 4
Resposta à Pergunta 1: Sim, é possível analisar o relatório no formato em que está e, com ele
podemos entender melhor o impacto dos eventos ou ações que são feitas, pois isso também causa
variações no ticket médio e taxa de conversão.
Resposta à Pergunta 2: Nenhuma alteração necessária.
Relatório 5
67
Resposta à Pergunta 1: Sim, atende a necessidade e nos ajuda a entender melhor qual
fornecedor ou produto tem o maior volume de devoluções apresentando todo o histórico de
devoluções tanto do MSM quanto do SQL em um único relatório.
Resposta à Pergunta 2: Nenhuma alteração necessária
De acordo com as respostas fornecidas, os cubos de maneira geral atendem a necessidades
gerenciais e poderão auxiliar os gestores no processo decisório relativo a área comercial da
empresa.
Durante a etapa de validação pelos gestores, foram identificadas algumas necessidades que
não estavam sendo atendidas plenamente no cubo de dados apresentado para validação, mas as
informações estavam previstas no modelo de dados, sendo assim poderiam ser incluídas no cubo ou
extraídas a partir de um novo relatório.
68
5 CONCLUSÕES
Percebe-se que no nível operacional é crescente o volume de dados existentes em diversos
sistemas distribuídos nas organizações, sendo que os Sistemas de Processamento de Transações
(SPT) são responsáveis por garantir entrada e a integridade dos dados operacionais em todas as
áreas da empresa.
Da mesma forma é crescente a busca a informações relevantes que possam auxiliar o
processo de tomada de decisões. Assim, este trabalho de conclusão de curso torna-se mais um
recurso para os gestores da Havan Lojas de Departamentos no apoio a decisão.
Avaliando os objetivos específicos deste trabalho, pode-se perceber que todos foram
cumpridos, atingindo, desta forma, o objetivo geral. Foi criado um Data Mart para área comercial
da Havan, onde foi feita a carga dos dados e a geração de relatórios para atender as necessidades
gerenciais identificadas. A criação do DM tornou possível a análise de um grande volume de
informações de forma rápida e precisa, tornando-se mais uma ferramenta de apoio que irá contribuir
no processo de tomada de decisões da empresa.
Como Sistema Gerenciador de Banco de Dados, optou-se pelo Microsoft SQL Server 2005
por ser um sistema robusto que dispõe das ferramentas necessárias para auxiliar os processos ETL e
estava disponível para uso na empresa. As principais ferramentas utilizadas do SQL Server 2005
foram o SQL Management Studio para criação das tabelas e base de dados, e os pacotes DTS do
SQL Server Integration Services para os processos ETL.
O gerador de relatórios ItlViewer tornou possível a implementação dos relatórios no formato
de cubos OLAP de forma simples e rápida, facilitando a visualização das informações pelos
gestores através de uma interface amigável, sendo identificado como ponto negativo a necessidade
de cadastrar manualmente a metadata.
5.1 Trabalhos Futuros
Pode-se dizer que este trabalho de conclusão de curso foi de extrema importância e o seu
resultado satisfatório, foi possível construir DM para auxiliar no processo de tomada de decisões da
área comercial da Havan, como foi evidenciado através da sua validação junto aos gestores. E a
partir deste DM surgem diversas idéias para aperfeiçoá-lo, conforme segue:
69
•
Ampliação do DM da Área Comercial: carregar novas informações no DM e gerar
novas consultas para atender a outras necessidades gerenciais identificadas.
•
Implementação em outras áreas: além de implementar o DM para área comercial,
considera-se importante à implementação de um trabalho voltado para área
financeira da empresa, onde existem os sistemas: bancário; contas a pagar; contas a
receber e sistema de crédito que controla um cartão de compras próprio, com a
manutenção de limites de crédito da carteira clientes e precisa apurar o nível de
inadimplência entre outras informações; e
•
Mineração de dados: Aplicar técnicas de data mining nos dados do DM com o
objetivo de reconhecer padrões de consumo.
Outra preocupação é referente ao desempenho dos cubos e ao volume de dados do DM que
em alguns fatos como o Fato Vendas gera atualmente cerca de 19.000 registros diários e com a
abertura de novas filiais este número poderá aumentar ainda mais.
Como trabalho futuro poderá ser aplicada também uma técnica de agregação dos dados
objetivando com isso otimizar o tempo de resposta das consultas, melhorar o tempo de
processamento e reduzir o espaço de armazenamento.
70
REFERÊNCIAS BIBLIOGRÁFICAS
BATTISTI, Júlio. SQL Server 2005 Administração & Desenvolvimento Curso Completo. Rio de
Janeiro: Axcel Books, 2005.
DUMLER Michelle. Microsoft SQL Server 2005: Guia do Produto, 2005. Disponível em:
<http://download.microsoft.com/download/4/3/7/43798b24-9af7-4039-960d12361ae367cc/ProductGuide-brz.doc>. Acesso em: 10 out. 2006.
EMBRATEL. Soluções Embratel: Rede Única de Dados. Disponível em:
<http://www.embratel.com.br/Embratel02/cda/portal/0,2997,MG_P_8615,00.html>. Acesso em: 30
out. 2006.
HAVAN. Empresa. Disponível em: <http://www.havan.com.br>. Acesso em: 16 out. 2006.
INMON, William H. Como construir o Data Warehouse. 2 ed. Rio de Janeiro: Campus,1997.
INTELIDATA. Produtos. Disponível em: <http://www.intelidata.inf.br>. Acesso em: 16 out. 2006.
KIMBALL, Ralph. Data Warehouse Toolkit Técnicas para Construção de Data Warehouses
Dimensionais. São Paulo: Makron Books, 1998.
KIMBALL, Ralph. Data Warehouse Toolkit Guia Completo para Modelagem Dimensional. 2
ed. Rio de Janeiro: Campus, 2002.
LAUDON, Kenneth C.; LAUDON, Jane P. Gerenciamento de Sistemas de Informação. 3 ed. Rio
de Janeiro: LTC, 2001.
MACHADO, Felipe Nery Rodrigues. Tecnologia e Projeto de Data Warehouse: uma visão
multidimensional. Ed. Érica, 2004.
MARIANI, Frederico G. E. S. Construção de um Data Mart para apoio as tomadas de decisões
das empresas Proembarque e Casacon. 2006, 102f. Trabalho de Conclusão de Curso
(Bacharelado em Ciência da Computação)–Centro de Ciências Tecnológicas da Terra e do Mar,
Universidade do Vale do Itajaí, Itajaí, 2006.
PEREIRA, Walter Adel Leite. Uma metodologia de inserção de tecnologia de Data Warehouse
em organizações. Porto Alegre, 2000. 122f. Programa de Pós Graduação em Ciência da
Computação – Faculdade de Informática. Pontifica Universidade Católica do Rio Grande do Sul,
2000. Disponível em: <http://www.pucrs.br/uni/poa/info/pos/dissertacoes/arquivos/walter.pdf>.
Acesso em: 12 dez. 2006.
PINTO, Claudia Cristine Crisp Porto. Ferramenta Business Intelligence como Estratégia de
Tecnologia de informação : Estudo de Caso. Taubaté, 2003. 102f. Projeto de Pós Graduação
(Gerência Empresarial e Negócios) –Departamento de Economia, Contabilidade e Administração.
Universidade de Taubaté, Taubaté, 2003. Disponível em:
<http://www.unitau.br/prppg/cursos/ppga/mba/2003/gen/pinto-claudia_cristini_crisp_porto.pdf>.
Acesso em: 15 ago. 2006.
SCHERMERHORN, John R; HUNT, James G.; OSBORN, Richard N. Fundamentos de
Comportamento Organizacional. 2 ed. Porto Alegre: Bookman, 1999.
SINGH, Harry S. Data Warehouse Conceitos, Tecnologias, Implementação e Gerenciamento.
Tradução de “Data Warehousing”. São Paulo: Makron Books, 2001.
SOTO, Eduardo. Comportamento organizacional: o impacto das emoções. São Paulo: Thomson.
2002.
TAKAOKA, Hiroo. Aplicação de Data Warehouse no Varejo in: Marketing de Relacionamento
no Varejo (coord. Ângelo, Cláudio Felizoni de e Giangrande, Vera). São Paulo: Saint Paul Institute
of Finance, 2004.
TORRES, Norberto A. Competitividade empresarial com a tecnologia de informação. São
Paulo: Makron Books, 1995.
ZIULKOSKI, L. Cláudio Chaves. Coleta de requisitos e modelagem de dados para Data
Warehouse: um Estudo de Caso Utilizando Técnicas de Aquisição de Conhecimento. Porto Alegre,
2003. 63f. Projeto de Graduação (Bacharelado em Ciência da Computação)– Instituto de
Informática. Universidade Federal do Rio Grande do Sul, 2003. Disponível em:
<http://www.inf.ufrgs.br/gpesquisa/bdi/publicacoes/files/ColetaRequisDWH.pdf>. Acesso em: 21
set. 2006.
72
APÊNDICES
A Data Mart para área comercial da Havan
A apresentação abaixo foi encaminhada aos gestores no início do projeto.
75
76
B Criação da chave tempo
O script SQL abaixo apresenta a geração das chaves tempo dentro de um determinado
período.
-- Gera chave tempo de um determinado período
declare @ano smallint
declare @mes smallint
declare @dia smallint
declare @semana smallint
declare @trimestre smallint
declare @estacaoano char(15)
declare @numdiasano smallint
declare @bissexto smallint
declare @diamaxano smallint
declare @diamaxmes smallint
-- Ano inicial
set @ano=1989
while @ano < 2050 -- ano final
begin
set @numdiasano=0
set @semana=0
set @mes=0
set @ano=@ano+1
set @bissexto=@ano%4
set @trimestre=1
--- Numero de dias do ano
set @diamaxano = 365
if @bissexto=0
begin
set @diamaxano = 366
end
while @numdiasano<@diamaxano
begin
set @mes=@mes+1
if (@mes%3)=0
begin
set @trimestre=@trimestre+1
end
-- Numero de dias do mes
set @diamaxmes=31
if @mes in (4,6,9,11)
begin
set @diamaxmes=30
end
if @mes=2 and @bissexto=0
begin
set @diamaxmes=29
end
if @mes=2 and @bissexto<>0
begin
set @diamaxmes=28
end
--estacoes do ano
if @mes<=3
begin
set @estacaoano='Verão'
end
if @mes>3 and @mes<=6
begin
set @estacaoano='Outono'
end
if @mes>6 and @mes<=9
begin
set @estacaoano='Inverno'
end
if @mes>9
begin
set @estacaoano='Primavera'
end
set @dia=0
while @dia < @diamaxmes
begin
set @dia=@dia+1
--estacoes do ano
if @mes=3 and @dia>21
begin
set @estacaoano='Outono'
77
end
if @mes=6 and @dia>20
begin
set @estacaoano='Inverno'
end
if @mes=9 and @dia>22
begin
set @estacaoano='Primavera'
end
if @mes=12 and @dia>20
begin
set @estacaoano='Verao'
end
set @numdiasano=@numdiasano+1
if (@numdiasano%7)=0
begin
set @semana=@semana+1
end
insert into dimensao_tempo values (@dia,@mes,@ano,@semana,@trimestre,@estacaoano,0)
end
end
end
78
C Geração de arquivos em MSM
Para carga dos dados do banco de dados MSM, foram criados programas que geram
arquivos para serem importados no DM. Para exemplificar estes programas abaixo esta sendo
apresentado o programa dmh100, desenvolvido na linguagem MSM que gera o arquivo com as
devoluções para importação no DM.
dmh100 ; Arquivo de carga para devolucoes - Marinho [ 04/27/2007 10:07 AM ]
N (CE,CodiUso)
S vet(1)="c:\trf\dmh\devolucao"
S %=$$^entry("^dmh100",.vet) Q:%'=-2
;;
Gera
N (CE,CodiUso,Arquiv,Param)
S N1="^ws1"_CodiUso K @N1
D Aguarde^lib7
O 51:(Arquiv:"W")
S Cod="",emppro=$$CE^lib16(CE,"PRO")
F S Cod=$O(^venbolit(Cod)) Q:Cod="" D
. S reg=$G(^venbolit(Cod))
. S data=+$P(reg,"^",4)
. S dias=$P(reg,"^",4)-$P(reg,"^",3)
. S:dias<0!(dias>10000) dias=0
. S filial=$P(^emplocal,"^",1)
. S cliente=+$P(reg,"^",1)
. S ^wbolit(Cod)=""
. F S Seq=$O(^venbolit(Cod,Seq)) Q:Seq="" D
. . S r=$G(^venbolit(Cod,Seq))
. . S produto=+$P(r,"^",1)
. . S quant=$P(r,"^",3),valor=$P(r,"^",4)
. . S resol=+$P(r,"^",5)
. . S motivo=+$P(^venbolit(Cod,Seq,1),"^",1)
. . S qtdger=$P(@N1@(filial,data,cliente,produto,motivo,resol,dias),"^",1)+quant
. . S vlrger=$P(@N1@(filial,data,cliente,produto,motivo,resol,dias),"^",2)+valor
. . S $P(@N1@(filial,data,cliente,produto,motivo,resol,dias),"^",1)=qtdger
. . S $P(@N1@(filial,data,cliente,produto,motivo,resol,dias),"^",2)=vlrger
;;
;; Grava o arquivo
S filial="",data="",cliente="",produto="",motivo="",resol="",dias=""
F S filial=$O(@N1@(filial)) Q:filial="" D
. F S data=$O(@N1@(filial,data)) Q:data="" D
. . F S cliente=$O(@N1@(filial,data,cliente)) Q:cliente="" D
. . . F S produto=$O(@N1@(filial,data,cliente,produto)) Q:produto="" D
. . . . F S motivo=$O(@N1@(filial,data,cliente,produto,motivo)) Q:motivo="" D
. . . . . F S resol=$O(@N1@(filial,data,cliente,produto,motivo,resol)) Q:resol="" D
. . . . . . F S dias=$O(@N1@(filial,data,cliente,produto,motivo,resol,dias)) Q:dias=""
. . . . . . . S reg=$G(@N1@(filial,data,cliente,produto,motivo,resol,dias))
. . . . . . . S UN=$P(^ESTPRO(emppro,produto),"^",4)
. . . . . . . S QD=$P(^ESTUNID(emppro,UN),"^",2)
. . . . . . . S quant=$TR($$EDT^lib($P(reg,"^",1),QD),",",".")
. . . . . . . S valor=$TR($$EDT^lib($P(reg,"^",2),2),",",".")
. . . . . . . S ano=$$ANO^lib1(data)
. . . . . . . S mes=$$MES^lib1(data)
. . . . . . . S dia=$$DIA^lib1(data)
. . . . . . . S lin=filial_","_ano_","_mes_","_dia_","_cliente_","_produto_","_motivo
. . . . . . . S lin=lin_","_resol_","_dias_","_$P(reg,"^",1)_","_$P(reg,"^",2)
. . . . . . . U 51 W lin,!
;;
C 51
Q
z
;;
;;*3,1*Arquivo de Carga para Devolucoes*
;; Arquivo :|_______________________________________________|
;;
*
79
D
D Construção de um relatório no Itlviewer
Na criação de um relatório utilizando o gerador Itlviewer, é necessário definir o módulo,
grupo e subgrupo do sistema onde o relatório será disponibilizado, conforme demonstra a Figura 41.
Figura 41. Itlviewer – Passo 1, definições iniciais
Após estas definições iniciais, deverá ser escolhida a tabela base do relatório, esta tabela
preferencialmente deverá ter relacionamentos com todas as demais tabelas necessárias à construção
do relatório, a seleção da tabela base está apresentada na Figura 42.
Figura 42. Itlviewer - Passo 2, seleção da tabela base
80
Com estas definições, já é possível definir as colunas de tabela que serão apresentadas como
resultado do relatório ou irão fazer parte do filtro. O Itlviewer apresenta automaticamente todas as
colunas da tabela base e também colunas das demais tabelas relacionadas à tabela base do relatório.
A Figura 43 apresenta a seleção de colunas de tabela para construção do relatório.
Figura 43. Itlviewer - Passo 3, seleção das colunas
Além de colunas de tabela, é possível exibir no relatório:
•
Colunas virtuais: podem conter valores fixos, gerados a partir de alguma condição,
operação matemática, ou ainda chamada de função;
•
Subconsultas: pode ser entendida como uma sub query, necessária quando não existe
relacionamento com a tabela base; e
•
Expressões: pode utilizada para realizar operações diversascomo por exemplo contagem
de elementos.
Após a definição das colunas necessárias para construção do relatório o próximo passo é
definir as propriedades das colunas, entre as principais definições estão: as colunas que serão
visíveis na apresentação do relatório, as colunas utilizadas como referência que não aparecem mas
fazem parte do filtro, colunas que fazem parte do agrupamento, máscara, tamanho, nível e
orientação no cubo. A edição das propriedades das colunas esta na Figura 44.
81
Figura 44. Itlviewer - Passo 4, propriedades da coluna
Além de colunas o relatório poderá ter ainda:
•
Eventos: podem ser outros relatórios chamados a partir de um relatório principal, e
podem possuir ou não alguma condição associada a sua chamada;
•
Cabeçalho: pode ser definido um cabeçalho diferenciado para o relatório, quando não
definido o cabeçalho é o título das colunas visíveis; e
•
Variáveis: serão solicitadas na tela ou passadas por parâmetro no momento da execução
do relatório.
Outra definição importante na construção do relatório são os filtros que basicamente são as
cláusulas SQL where e having. A Figura 45 apresenta a definição dos filtros utilizando variáveis.
82
Figura 45. Itlviewer - Passo 6, definição dos filtros
O último antes da utilização do relatório, é a definição da categoria que pode ser entendida
como tipo de saída do relatório. As principais categorias de geração de relatórios do Itlviewer são:
html, pdf, excel, e-mail, word, arquivo texto, impressora matricial e cubo de dados. A Figura 46
representa a definição da categoria.
Figura 46. Itlviewer - Passo 7, definição da categoria
83
84