Embed Size (px)
Citation preview
Projeto 2 SINO
Mestrado em Engenharia e Gestão de Sistemas de Informação
Sistemas para Inteligência do Negócio e das Organizações
1º ano/1ºsemestre
[Construção de BI]
Pág
ina1
Índice 1. Introdução ............................................................................................................................. 2
2. Modelação da previsão do income (SAS) .............................................................................. 2
3. Ferramenta SpagoBI .............................................................................................................. 4
4. Ferramenta Pentaho ............................................................................................................. 6
5. Modelo-multidimensional ................................................................................................... 12
6. Análise dos dados - Pentaho ............................................................................................... 15
Iteractive Report ..................................................................................................................... 15
Analyzer Report ....................................................................................................................... 18
Dashboard ............................................................................................................................... 24
7. Análise dos dados - SQL Server Business Intelligence Development Studio ....................... 27
Criação dos Cubos OLAP ......................................................................................................... 28
Análise dos dados ................................................................................................................... 34
Reports .................................................................................................................................... 39
8. Conclusão ............................................................................................................................ 44
Resultados ............................................................................................................................... 44
Limitações ............................................................................................................................... 44
9. Referências .......................................................................................................................... 45
10. Anexos ............................................................................................................................. 46
Pág
ina2
1. Introdução
No âmbito da disciplina de Sistemas para a Inteligência do Negócio e Organizações foi
fornecido, pelos docentes, um dataset (livro de MSExcel) de uma determinada organização e depois
gerar/induzir um modelo de previsão do Income na ferramenta SAS Enterprise Miner com base no
melhor modelo obtido no projecto anterior, gerou-se recorrendo à ferramenta Pentaho BI Suite,
análises, reports e dashboards respondendo assim a algumas questões delineadas anteriormente.
Os dados de estudo são referentes a membros de uma determinada população que uma
organização de estudos estatísticos realizou. Através desses dados o grupo pretende extrair informação
útil de modo a identificar e compreender o tipo de negócio em causa, assim como, explorar os dados
para tomadas de decisão a nível operacional, táctico e estratégico. Neste caso, o nível estratégico será o
mais explorado onde as decisões estratégicas são as mais relevantes para o negócio em questão.
Pretende-se compreender, identificar, preparar e modelar os dados relevantes para o suporte às
decisões da gestão, facilitando relatórios em tempo útil e com a informação necessária para uma
correcta tomada de decisão.
A ferramenta utilizada inicialmente foi o SpagoBI que tecnicamente se tornou de difícil
configuração e atrasou definitivamente o projecto. Optou-se então por outra ferramenta, o Pentaho BI
Suite, que a nível de configurações se mostrou mais acessível e em termos de utilização e
funcionalidades revelou-se mais útil e intuitiva.
A estrutura do relatório será a seguinte:
- Introdução;
- Modelação da previsão do income (SAS);
- Ferramenta SpagoBI;
- Ferramenta Pentaho;
- Modelo-multidimensional;
- Analise dos indicadores, Dashboards, Reports;
- Conclusões.
2. Modelação da previsão do income (SAS)
Na construção do modelo de previsão de income foram utilizados os componentes e as suas
respectivas configurações do melhor modelo do primeiro projecto para a disciplina de sistemas para a
inteligência do negócio das organizações (SINO) e o componente Score e Insight. O modelo é constituído
pelos nós Input Data Source, Data Partition, Regression, Tree, Neuronal Network, Assessment e
Reporter, Score, Insight como ilustra a próxima imagem.
Figura 1 - Modelo de previsão income.
Pág
ina3
A próxima tabela apresenta o melhor resultado modelo do primeiro projecto.
Algoritmos Previsão % Teste Prev. Total Prev. Errados Conf. Modelo
Redes Neuronais (iteração 12)
Income Valid. 30 10826 1880 82,63%
Teste 30 10826 1872 82,71%
Tabela 1 - Melhor resultado.
A próxima tabela apresenta o papel de cada componente no modelo de previsão de income.
Componente Razão/conclusão
Work.Censos foi utilizado para carregar o dataset preparado do primeiro projecto. Work.Censos2 foi utilizado para carregar os dados do dataset disponibilizado pelos docentes, onde os valores de income são desconhecidos.
O próprio SAS Enterprise Miner disponibiliza vários meios para testar a qualidade e a validade do modelo assim como fornece métricas de qualidade. Para separar o dataset num conjunto de treino, validação e teste foi utilizado o nó Data Partition.
Como o objectivo do modelo de Data Mining construído é a previsão de income, a técnica, redes neuronais, é uma boa escolha uma vez que os resultados do modelo são mais importantes do que propriamente a compreensão de como este modelo trabalha;
Este nó fornece uma framework para comparar modelos e previsões, a comparação é baseada nos benefícios/perdas actuais e esperadas que vão resultar da implementação do modelo. Teve pouco impacto nesta fase.
O Reporter disponibiliza as matrizes de validação e de teste para identificar, entre outras métricas, a acuidade, sensibilidade, especificidade e cobertura de cada modelo. Teve pouco impacto nesta fase.
Permite gerar valores de previsão de um modelo de treino.
Este componente foi útil para explorar os resultados da previsão e converter os mesmos para Excel.
Tabela 2 - Componentes da modelo.
A próxima imagem apresenta os resultados da previsão e como se converte os resultados para
Excel.
Pág
ina4
Figura 2 - Resultado da previsão.
Após a execução a obtenção dos resultados de previsão, estes podem ser utilizados pelas
ferramentas de business intelligence (spagobi, pentaho e development studio).
3. Ferramenta SpagoBI
SpagoBI é uma solução Business Inteligence, totalmente gratuita e desenvolvida pela SpagoWorld,
fundada e apoiada pela Engineering Group.
Com o SpagoBI é possível desenvolver projetos BI em ambientes integrados. Cobre todo o gama
de necessidades analíticas: emissão de
relatórios estáticos, análise dinâmica e
multidimensional, procura de informações
escondidas através de técnicas DataMining e
monitorização do desempenho da empresa
através de quadros.
SpagoBI é composto pelos seguintes módulos
principais:
Pág
ina5
Neste projeto foram utilizados os seguintes módulos:
SpagoBI Meta: Ambiente focalizado na chamada de meta dados;
Este módulo oferece uma ferramenta de apoio à base de dados, através do qual os utilizadores definem
objetivos simples e complexos para ser usado dentro da plataforma por ferramentas de análise. Este
módulo também enriquece a base de conhecimento dos meta dados do SpagoBI Server, para que possa
facilmente ser consultado através das ferramentas disponíveis, tais como Reports, OLAP e QbE.
SpagoBI Server: Analytical Engine (AE) plataforma de BI que oferece todas as funcionalidades
principais e de análise. Este é o módulo principal da suite.
o O Analytical Model é o núcleo principal do SpagoBI Server, e cobre todas a gama de
necessidades analíticas, fornecendo muitas soluções para cada área analítica.
o O Behavioural Model (BM) regula a visibilidade sobre os documentos e dados de
acordo com os papéis dos utilizadores finais.
Pág
ina6
o A Administration Tools serve de suporte a desenvolvedores, (testers) testadores e
administradores no seu trabalho diário, promovendo várias funcionalidades.
o O Cross Services inclui as funcionalidades comuns da plataforma que podem ser
usadas em todas as áreas analíticas.
4. Ferramenta Pentaho
O Pentaho BI Suite engloba as melhores capacidades open-source de Business Intelligence com
software e serviços adicionais concebidos para ajudar a organização a:
- Atingir sucesso BI
- Poupar tempo, recursos e dinheiro
- Mitigar o risco
Existe uma evidência muito grande entre os profissionais TI, consultores e analistas industriais
que o sucesso ou falha nos projectos BI está frequentemente mais relacionado com problemas de
“pessoas e processos” do que com a tecnologia. Fraco planeamento, falta de comprometimento,
recursos inadequados ou falta de competências e incapacidade de responder com resultados de forma
rápida podem ameaçar o sucesso de um projecto BI independentemente da tecnologia seleccionada e
utilizada. Não é segredo que a maioria dos projectos de Data Warehousing, migração e análise de dados
são caros, têm grandes ciclos de implementação e altos riscos de falha, surgindo o conceito de Agile BI
que redefine a forma como os projectos BI são construídos e implementados, tendo neste caso o
Pentaho unificado os processos de ETL, modelação e visualização num só ambiente de desenvolvimento
para desenvolver e implementar projectos de Business Intelligence. Este conceito de Agile BI define:
- Competências instantâneas, desenvolvimento de aplicações BI interactivas
- Permite perfeita colaboração entre os programadores e os utilizadores finais
- Junta o complexo desenvolvimento BI num único processo
- Reduz o tempo e a dificuldade de construir ou implementar aplicações BI
Pág
ina7
- Combina a força do rápido desenvolvimento de aplicações BI com a fase ETL para as
necessidades de integração complexa de dados.
Figura 3 - Modelação Metadata, ETL, Visualização – Pentaho BI Suite
Relativamente à arquitectura tecnológica, a figura abaixo mostra a relação entre os maiores
componentes do BI Server e as suas interfaces com o mundo exterior. Pode-se referir que o coração do
server é o Solution Engine, pois é o ponto central para a actividade na plataforma Pentaho BI. Encontra-
se entre o mundo exterior – Web Client, Services, System Monitor, etc e a camada de componentes da
plataforma.
Pág
ina8
Figura 4 - Infra-estrutura tecnológica
A Pentaho Open Source BI Suite é uma solução flexível e personalizável para adaptação e
implementação BI em diferentes tipos de empresas e negócios, incluindo as seguintes áreas de BI:
- Relatórios
- Análises de Informações (OLAP)
- Painéis de Controlo (Dashboards)
- Mineração de Dados (Data Mining)
- Integração de Dados
A arquitectura tecnológica referida acima é implementada como serviços onde os mesmos são
expostos via AJAX e Web Services, onde a plataforma de BI se define e executa regras de negócio,
apresentação de conteúdo, pontos de integração para aplicações e portais, processo de gestão,
integração e segurança centralizada.
Pág
ina9
Figura 5 - Plataforma BI
Relativamente ao serviço Pentaho Reporting, é permitido a acesso a formato de dados de
diferentes fontes, tais como, RDBMS (Relational Database Management System), XML e OLAP,
permitindo também vários tipos de relatório, nomeadamente, relatórios operacionais, analíticos e
financeiros. O acesso pode ser directamente à fonte de dados ou à camada de metadados definida e
centralizada. As principais vantagens do serviço Pentaho Reporting é o design flexível de relatórios,
tendo como características de aceder a bases relacionais, OLAP ou XML, ocultar objectos do relatório de
forma condicional e é multi-plataforma (cliente e servidor).
Figura 6 - Serviço Reporting
Em relação ao serviço Pentaho Analysis, este serviço possui uma arquitectura “ROLAP” e
trabalha com as mais conhecidas bases de dados proprietárias e open source, possui visualização de
Pág
ina1
0
dados de forma “dimensional”, por exemplo, vendas por região, por canal de vendas, por período, etc.
Relativamente à navegação e exploração de dados, é permitido executar análises Ad Hoc, fazer “Drill-
down”do ano para o quartil, seleccionar membros específicos para análise e possui um Front-End Web
ou Excel. As vantagens do serviço Pentaho Analysis são a arquitectura baseada em padrões possuindo
recuperação de dados baseado em comandos SQL, análises interactivas com acesso via Web ou Excel,
desempenho e escalabilidade com suporte a tabelas agregadas e utilização do Aggregation Designer
para auxiliar na construção das tabelas agregadas e integração com o Pentaho Open Source BI Suite
caracterizando-se pela auditoria abrangente das actividades do utilizador, de desempenho e de acesso
aos dados, segurança integrada, alertas, integração e metadados.
Figura 7 - Gerar Reports para posterior análise
O serviço Pentaho Dashboards possui uma forte integração de processos de negócio podendo
receber eventos de sistemas externos ou despoletar eventos em sistemas externos. Uma das mais-valias
é a integração com o Pentaho Open Source BI Suite englobando um “Dril-down” sensível a contexto para
relatórios ou análises OLAP e a arquitectura moderna e baseada em padrões onde todos os
componentes são expostos como webservices. As vantagens do serviço Petanho Dashboards são várias,
entre elas, a grande variedade de componentes visuais, filtos baseados em papéis ou assuntos, “Drill-
down” para relatórios detalhados, análises OLAP ou outros dashboards e integração com Google Maps
para localização inteligente.
Pág
ina1
1
Figura 8 - Serviço Dashboards
Outro dos serviços disponíveis é o Pentaho Data Integration que possui um extenso conjunto
de recursos com um excelente desempenho e escalabilidade e com um amplo suporte de base de
dados. Este serviço está 100% orientado aos metadados e ao nível da maturidade é um produto de 4ª
geração. As várias vantagens deste serviço são a abordagem orientada a metadados onde se podem
fazer transformações complexas, sem escrever uma única linha de código sendo 100% java, ou seja,
multi-plataforma. Possui uma gestão estruturada de modelos, conexões e logs num repositório na base
de dados e tem facilidade para reutilização de consultas e transformações. O Pentaho Data Integration é
uma ferramenta ETL completa, com mais 60 objectos de mapeamento pré-concebido, suporte para as
fontes de dados mais comuns, incluindo os RDBMS’s líderes de mercado e uma variedade de formatos
“flat file”. Contém um suporte avançado para Data Warehouse, para as dimensões do tipo “Junk” e
“Slowly Changing Dimensions”.
Figura 9 - Pentaho Data Integration e perspectiva da estratégia de negócio
Pág
ina1
2
Sendo uma tecnologia Open Source, tem inovações que ocorrem de forma mais rápida, com um
custo mais baixo, com tecnologia projectada para o BI operacional e SOA (Service Oriented Architecture),
utilizando as tecnologias mais recentes. Encerra também uma grande abrangência pois para além de
relatórios possui também análises OLAP, dashboards e técnicas de data mining.
5. Modelo-multidimensional
A estratégia para iniciar a concepção do modelo multidimensional teve por base o dataset
fornecido pelos docentes. Desta forma tenta-se perceber que tipo de tabelas de factos e dimensões irão
ser criadas para responder de forma directa às necessidades da gestão e de modo a optimizar o
desempenho no processamento dessas necessidades.
Figura 10 – Esquema do modelo multidimensional
De modo a dar resposta às necessidades da gestão, foi concebida a estrutura do sistema de
Data Warehousing através da implementação do esquema em estrela, Figura 10, no qual integramos a
tabela de factos, que partilham dimensões comuns.
A tabela de facto corresponde às componentes de negócio que pretendemos analisar. Estas
tabelas são constituídas por um conjunto de atributos numéricos, factos, analisáveis através de funções
estatísticas e por um conjunto de chaves estrangeiras que permitem a relação entre a tabela de factos e
as várias dimensões que lhe estão associadas. Estas tabelas contêm uma grande quantidade de registos.
Já as tabelas de dimensões vão-nos possibilitar a análise da tabela de factos a que estas estão
relacionadas sob diferentes perspectivas, tais como: qual o pais de origem, que escolaridade, qual
Pág
ina1
3
ocupação, entre outras. Estas tabelas contêm poucos registos quando comparadas com as tabelas de
factos. Foram criadas 5 dimensões, DimTrabalho, DimLocalizacao, DimIdentificacao, DimIdade e
DimEscolaridade e a tabela de factos, TbFactosIncome através dos dados contidos no dataset.
Tabela Facto Income
Tabela A. 1 – Descrição dos atributos da tabela de factos Income.
Nome do facto Observações Agregação
IDEscolaridade Chave primária em DimEscolaridade IDIdade Chave primária em DimIdade IDIdentificacao Chave primária em DimIdentificacao IDLocalizacao Chave primária em DimLocalizacao IDTrabalho Chave primária em DimTrabalho CapitalGain Total capital ganho. Soma
CapitalLoss Total do volume de vendas Soma
Hours_per_week Total de horas de trabalho semanais. Soma
ResultadoCapital Facto não aditivo, criado através da subtracção de capitalGain com capitalLoss, admitindo que este atributo indica o montante de liquidez que a pessoa necessita
Soma
Emprestimo Facto não aditivo, obtido através da condição: se ResultadoCapital < 0 então a pessoa necessita de empréstimo
Soma
Bin_Income>50 Número de pessoas com income superior a 50k. Soma
Bin_Income<=50 Número de pessoas com income inferior ou igual a 50k. Soma
Elemento Número de pessoas. Soma
Dimensão Trabalho
Income
CapitalLoss
CapitalGain
Emprestimo
Hours_per_week
ResultadoCapital
Bin_Inco
me>50
Bin_Inco
me<=50
Pág
ina1
4
Tabela B. – Descrição dos atributos da dimensão Trabalho
Nome do atributo Observações Tipo
IDTrabalho Chave primária da tabela int
ClasseTrabalho Classificação segundo o trabalho Nvarchar(50)
ClasseHorasPorSemana Classificação da escala de horas de Trabalho Nvarchar(50)
Ocupacao Classificação segundo a ocupação Nvarchar(50)
Dimensão Localização
Tabela C. – Descrição dos atributos da dimensão Localização
Nome do atributo Observações Tipo
IDLocalizacao Chave primária da tabela int
Continente Criado a partir da associação ao país Nvarchar(50)
Pais Pais de origem Nvarchar(50)
Dimensão Identificação
IDTrabalho
ClasseH
orasPor
Ocupacao ClasseTrab
IDLocalizacao Pais
Continente
Pais
IDIdentificacao Relacao
Raca Sexo EstadoCivil
Pág
ina1
5
Tabela D. – Descrição dos atributos da dimensão Identificação
Nome do atributo Observações Tipo
IDIdentificacao Chave primária da tabela int
Relacao Classificação segundo a relação Nvarchar(50)
Raca Classificação segundo a raça Nvarchar(50)
Sexo Classificação segundo a genero Nvarchar(50)
EstadoCivil Classificação segundo a estado civil Nvarchar(50)
Dimensão Idade
Tabela E. – Descrição dos atributos da dimensão Idade
Nome do atributo Observações Tipo
IDIdade Chave primária da tabela int
ClasseIdade Classificação segundo grupos etários Nvarchar(50)
DescricaoClasseIdade Descrição dos grupos etários Nvarchar(50)
Dimensão Escolaridade
Tabela F. – Descrição dos atributos da dimensão Escolaridade
Nome do atributo Observações Tipo
IDEscolaridade Chave primária da tabela int
EducationLevel Escala de níveis de escolaridade vigente na Europa Nvarchar(50)
EducationLevelDescription Descrição da escala Nvarchar(50)
6. Análise dos dados - Pentaho
Iteractive Report
É usado para responder a questões que ocorrem numa base regular. A quem, o quê, onde e
quando. Sendo as perguntas mais comuns de negócios que podem necessitar de respostas de
imediato. Quais são os melhores desempenhos desta semana? Qual campanha de marketing foi a mais
efetiva para 2010? Eles permitem executar, recuperar e analisar o resultado, mas com pouca
flexibilidade na saída do relatório em si. O relatório é habitualmente exibido em HTML, PDF ou um
formato Excel, podendo ser impresso e é por natureza estático.
IDIdade ClasseIdade Descricao
ClasseIda
IDEscolaridade EL ELDescription
Pág
ina1
6
Características:
- Nível de dificuldade mínimo para os utilizadores na interação e execução.
- Não é necessário nenhum treino para os utilizadores para executarem e compreenderem;
- Criar resumos e relatórios com níveis de detalhe e conecta-los através de passagem de
parâmetros via hiperligação ou expressões de estilo;
- Podem ter parâmetros adicionais adicionados, se necessário, para alterar a estrutura do
relatório, bem como filtros;
- Pode ser enviado por email ou distribuído de diferentes meios.
- Normalmente, a estrutura do relatório é relativamente inalterado mas pode ser dinâmico;
Serão analisados alguns Iteractive Report, sendo que apenas excertos se encontram na análise.
Em anexo podem ser encontrados completos.
Iteractive Report 1
Este Iteractive Report está divido em classes de idade, tendo associado a cada uma delas as
classes do nível educacional. Sendo estas as classes e subclasses, têm associado o income, continente
(filtrado apenas para análise da Europa) e raça obtendo-se com os elementos, o número de casos em
que estes acontecem.
Podemos concluir, por exemplo, que na classe de idade mais jovem, na Europa e com nível 2 de
escolaridade, apenas existe um elemento de raça branca a ganhar <=50k.
Pág
ina1
7
Iteractive Report 2
É possível fazer várias conjugações de dados obtendo-se os dados necessários para tomada de
decisões de negócio. Com este Report é analisado por Income, o número de elementos com baixo nível
educacional, associando a ele a classe de idade e continente de origem.
Com baixo nível educacional (Level0 a Level3) existem 8709 indivíduos, sendo possível observar
em que associação é mais incidente.
Por exemplo, com income <=50k existem 281 indivíduos com nível educacional 3 na classe de
idade mais alta oriundos da América do Norte.
Iteractive Report 3
Pág
ina1
8
Esta análise agrupa pelo income e filtra por nível educacional elevado os elementos
existentes na associação destes com o continente e classe de idades. É possível verificar que mais
de metade dos indivíduos possuem nível educacional elevado e que é nas classes de idade mais
jovens que se encontra a maioria de inquiridos que ganham <=50k com um nível educacional
elevado (Level4 a Level6).
Analyzer Report
O Pentaho Analyzer Report ajuda a operar com a máxima eficácia ganhando as percepções e
compreensão necessária para tomar as decisões corretas. Os serviços do Pentaho Analysis permitem
analisar dados iterativamente através dos datawarehouses, fornecendo tabelas de referência cruzada
com diferentes dimensões.
O Pentaho Analysis oferece as seguintes funcionalidades:
- Pentaho user-console web interface;
- Interface de visualização em tempo real;
- Uma programação complexa do sub-sistema;
- Uma capacidade de enviar email de uma análise publicada para outros utilizadores;
- Capacidade de criar esquemas de análise complexas;
- Capacidade de melhorar o desempenho do cubo ROLAP com designer de agregação.
Características:
- Arrastar e soltar os analytical reporting;
- Ordenar, preencher e perfurar os dados dinamicamente;
- Ver gráficos;
- Exportar dados para MSExcel;
- Personalizar totais e cálculos definidos pelo utilizador;
- Classificação e filtragem avançada;
- Adicionar Iteractive Reports para o Dashboard;
- Guardar e compartilhar relatórios.
São apresentados excertos dos Analyser Report para proceder a uma análise, sendo que se
encontram completos em anexo.
Analyzer Report 1
Pág
ina1
9
Através da construção da tabela onde são associados os dados para análise é criado
automaticamente o gráfico. Neste caso pode ser analisada a percentagem de elementos associado ao
nível educacional, raça e income. Por exemplo, para a raça branca e ganhando <=50k o nível educacional
mais presente é o nível 3, já para a raça branca a ganhar >50k existem em maioria, com percentagens
próximas, indivíduos com nível de escolaridade 6 e 4. Outra analise que pode ser feita é a de verificar
quem tem maior/menor nível de escolaridade. O nível 6 encontra-se, sobretudo, associado a quem
ganha mais de 50k.
Analyzer Report 2
Esta analise mostra a associação do continente de origem com o nível educacional, cruzando-os
com o income obtendo assim a elementos associados nesse cruzamento de dados. Verifica-se com esta
Pág
ina2
0
análise, por exemplo, que os naturais da América do Norte, todos os que têm nível 6 de escolaridade
ganham >50k e que com os Europeus existem elementos a ganhar este valor possuindo qualquer nível
de escolaridade.
Analyzer Report 3
Nesta análise é possível perceber qual a classe de idade associado ao nível educacional e
income que mais elementos possui. Com o nível 6 de escolaridade, por exemplo, encontram-se apenas
indivíduos com idade compreendida entre os 26 e 65 anos. Mais de 7000 elementos encontram-se
inseridos no Level3 a ganhar menos de 50k e a ganhar mais de 50k possuindo o Level5 estão inseridos
certa de 3000 indivíduos.
Analyzer Report 4
Pág
ina2
1
Esta análise mostra o cruzamento entre a classe de
idade, raça e income, obtendo-se o número de elementos
associado a esse cruzamento de dados. Podemos verificar que a
classe de idade dos 26 aos 45 anos é a que possui mais
elementos a ganhar tanto <=50k como >50k sendo que a raça
associada é a branca. Esta é a classe de idade que inclui mais
indivíduos. A raça branca da classe mais jovem é a que possui
maior discrepância no número de indivíduos que ganha mais ou
menos de 50k.
Pág
ina2
2
Analyzer Report 5
Com este Analyzer Report é possível observar os resultados dos cruzamentos da classe de
idades e income, com as raças e a naturalidade dos habitantes. Podemos atestar que, por exemplo, nos
habitantes da América do Sul apenas estão presentes três raças: Amer-Indian-Eskimi, Other e White,
sendo que deste a maioria está presente na classe dos 26-45 anos, ganham menos de 50k e são de raça
branca.
Pág
ina2
3
Analyzer Report 6
Com esta ferramenta é possível classificar os dados automaticamente através da escolha
respectiva de tipo de classificação. Na tabela desta análise é possível observar a associação de cores aos
valores dados, quando melhor o valor mais verde é, quando pior, mais se aproxima do vermelho. Além
da classificação por cores também é possível utilizar o preenchimento da célula conforme o valor da
mesma, tal como também é apresentado nesta análise.
Pág
ina2
4
São apresentados aqui os valores de capital ganho, perdido, respetivas percentagens e
diferença de capital em percentagem. Isto associado à classe de idade e nível educacional. A maior
variação de capital encontra-se associada aos níveis 3, 4 e 5 de escolaridade.
Dashboard
Os Dashboards fornecerem uma visão imediata do desempenho individual, departamental ou
empresarial. Ao oferecer métricas-chave com uma interface atraente, intuitiva e visual, os painéis
oferecem aos utilizadores de negócios as informações críticas que precisam para entender e melhorar o
desempenho organizacional.
Dashboards Pentaho proporcionam essa visibilidade, fornecendo:
Abrangentes capacidades de gestão de métricas que permitam a definição e acompanhamento
de métricas críticas para o indivíduo, departamento ou nível da empresa;
Ricas e interativas apresentações visuais para que os utilizadores empresariais podem ver
imediatamente quais métricas de negócios estão no caminho certo e as que precisam de
atenção;
Integração com Pentaho Reporting e Analyser Pentaho para que os utilizadores podem perfurar
para relatórios e análise subjacentes para entender quais os fatores que estão a contribuir para
o bom ou mau desempenho;
Portal de integração para tornar mais fácil entregar métricas de negócios relevantes para um
grande número de utilizadores, perfeitamente integrado na sua aplicação;
Alerta Integrado para monitorar continuamente exceções e notificar os utilizadores a tomar
medidas.
Dashboard 1
Pág
ina2
5
Com este Dashboard é possível fazer análises alterando os filtros para analisar assim vários
casos em diferentes perspectivas. Neste caso são estudados os cruzamentos entre os continentes e o
income, o nível educacional e o income, e o nível educacional com a nacionalidade. Estes gráficos têm
filtros e a tabela associados. É possível verificar, por exemplo, que os Europeus ganham, na sua maioria
menos de 50k e que estes têm na maioria o nível 3 de escolaridade. Com o nível 6 de escolaridade, a
grande maioria mais de 50k.
Dashboard 2
Este dashboard mostra a associação entre o income e a classe de idade, o nível educacional, o
continente de naturalidade e a raça. Em qualquer dos casos a classe de idade entre os 26 e os 45 são os
mais ganham. Os que possuem nível 3 de escolaridade são os que mais elementos têm a ganhar menos
de 50k. Os com nível 5 são os que ganham mais de 50k.
Pág
ina2
6
Dashboard 3 - KPI
Este dashboard dá-nos um indicador, dependendo de onde se encontra o valor, poderão ser
tomadas decisões ou preverem-se ter de as tomar. Este KPI mostra-nos que para um income menor que
50k existem cerca de 65% dos indivíduos, quando este valor ultrapassar 75% dos casos é grave porque a
população está na grande maioria a ganhar pouco, podendo querer significar que a população está a
empobrecer.
Dashboard 4 - KPI
Com a análise destes KPI’s é possível verificar como os indivíduos gerem o dinheiro, mostra qual o
capital ganho e o perdido. A diferença destes diz-nos o nível de possível endividamento da população.
Os valores de capital ganho são superiores aos do capital perdido, quando estes chegarem a 50% da
população é necessária alguma precaução pois é possível que metade da população esteja a endividar-
se.
Pág
ina2
7
7. Aná lise dos dádos - SQL Server Business Intelligence
Development Studio
Introdução
Uma vez efectuada a migração dos dados do Excel para o modelo multi-dimensional, é
necessário agora proceder à exploração da Data Wharehouse através da tecnologia On-Line Analytical
Processing (OLAP) que permitirá gerar Reports e Dashboards.
A tecnologia OLAP vai permitir a criação de cubos para possibilitar a análise da informação sob
diferentes perspectivas. A análise da informação propriamente dita é efectuada recorrendo, quer aos
Reports, quer aos Dashboards. Todo este processo foi executado através de projectos do tipo Business
Intelligence do Visual Studio 2008 e do aplicativo Pentaho User Console.
Configurações SQL Server Business Intelligence Development Studio
Antes de criar o projecto Analysis Services é necessário iniciar os serviços Server e Server Browser no
gestor de configurações do SQL Server. Neste trabalho, o serviço SQL Server foi útil para facultar acesso
aos repositórios. O serviço Server Browser foi útil para permitir acesso remoto ao Data Wharehouse que
o Analysis Services utiliza para a execução dos cubos.
Figura 11- Configuração dos Serviços SQL.
Após a selecção do Data Source é necessário indicar as credenciais que são utilizadas para a
ligação ao repositório. O modo escolhido foi a utilização da conta do serviço.
Figura 12 - Autenticação à base de dados.
Para realizar o deployment é essencial especificar o servidor de base de Dados nas
propriedades do projecto.
Pág
ina2
8
Figura 13 - Definir o servidor de base de dados.
Criação dos Cubos OLAP
Cubos OLAP permitem uma análise multidimensional dos dados armazenados no Data
Wharehouse. Esta análise intervém num qualquer indicador de negócio que faça sentido para a tomada
de decisão, tirando partido da simplicidade de obtenção de relatórios, análises comparativas e
visualização de subconjuntos de maior interesse. Este tipo de método de análise foca-se sobretudo no
apoio à gestão organizacional.
Figura 14 – Criação de um novo projecto.
Para a criação dos cubos, o primeiro passo consiste em criar um novo projecto do tipo Business
Intelligence, neste caso um Analysis Services Project, conforme Figura 14.
Pág
ina2
9
Figura 15 – Definição da origem dos dados.
De seguida, é necessário proceder à selecção do banco de dados de onde será feita a análise
através dos cubos OLAP, sendo então definido então o caminho de acesso, conforme Figura 15.
Figura 16 – Nova vista.
No passo seguinte deve clicar com o botão direito do rato sobre Data Source Views do Solution
Explorer e seleccionar a opção New Data Source View, conforme Figura 16, para assim criar uma nova
vista, Data Source View, que irá conter as tabelas de factos e de dimensão que podem vir a fazer parte
do cubo.
Pág
ina3
0
Figura 17 – Selecção de tabelas.
É através da caixa de diálogo que surge então, Data Source View Wizard, Figura 17, que se
procede à triagem das tabelas de factos e dimensão, passando as pretendidas da caixa de listagem
Available objects para a caixa de listagem Included objects. No final surge a vista do modelo de
relacionamentos entre as tabelas de factos e dimensões.
Figura 18 – Criar o cubo.
Tendo a nova vista, cria-se o cubo que irá permitir a análise flexível e dinâmica relativamente
aos indicadores de gestão em estudo. Assim, deve clicar com o botão direito do rato sobre Cubes da
área Solution Explorer, Figura 18, e seleccionar a opção New Cube.
Pág
ina3
1
Figura 19 – Selecção das tabelas de factos.
O primeiro passo desta fase consiste na selecção da vista que vai servir de base ao cubo e
definição das tabelas onde se encontram os factos a analisar, Figura 19.
Figura 20 – Selecção dos factos a incluir no cubo.
De seguida indica-se quais as medidas, os factos, a incluir no cubo, conforme Figura 20.
Pág
ina3
2
Figura 21 – Selecção das dimensões a incluir no cubo.
Por fim são seleccionadas as dimensões a incluir no cubo, Figura 21.
Figura 22 – Cubo Income.
Obtendo-se então as tabelas de factos e dimensões que compõem o cubo, Figura 22. A azul
encontram-se referenciadas as tabelas de dimensões e a amarelo as tabelas de factos.
Pág
ina3
3
Figura 23 – Estrutura da dimensão Localização.
É agora necessário indicar para cada tabela de dimensão os campos e a hierarquia, caso exista,
entre esses campos. Assim, após fazer duplo clique sobre a dimensão pretendida, e usando como
exemplo a dimLocalizacao da Figura 23, é necessário primeiro arrastar os campos seleccionados da
tabela dimLocalizacao, que se encontra na área Data Source View, para a área Attributes. Depois é
estabelecida a, ou as hierarquias, arrastando para a área Hierarchies os atributos que vão fazer parte da
hierarquia, do nível mais alto para o nível mais baixo desta.
No caso a dimensão Localização foi criado uma hierarquia, composta pelos atributos Pais e
Continente. As restantes hierarquias das restantes dimensões foram estabelecidas de acordo com o
descrito anteriormente/fase5 do relatório.
Figura 24 – Processamento de dados
Uma vez definidas todas as hierarquias e campos a usar processa-se os dados através do ícone
Process representado no menu do separador Cube Structure, Figura 24.
Pág
ina3
4
Figura 25 – Processamento de dados.
A Figura 25 apresenta o processamento do cubo com sucesso. É de notar que este
processamento pode ser efectuado em paralelo ou sequencialmente.
Análise dos dados
SQL Server Reporting Services fornece um conjunto de ferramentas, serviços que ajudam a
criar, implementar e gerir relatórios para análise de dados e tomada de decisão. As ferramentas
Reporting Services funcionam dentro do ambiente Microsoft Visual Studio e são complemente
integradas com as ferramentas e componentes do SQL Server.
Com Reporting Services, podem-se criar relatórios interactivos através de diversas fontes de
dados relacionais, multidimensionais ou XML. Podem-se publicar relatórios e calendarizar o
processamento de relatórios. O Reporting Services também permite criar relatórios ad-hoc com base em
modelos predefinidos e explorar dados interactivamente dentro do modelo. Pode-se escolher uma
variedade de formatos de visualização, exportar relatórios para outras aplicações e assinar relatórios
publicados. Os relatórios podem ser visualizados numa página Web ou uma aplicação Windows.
Tanto como o Reporting Services, as tabelas dinâmicas em MSExcel permitem a análise de
dados e a tomada de decisão, sendo que com o uso destas pode-se, facilmente, obter múltiplas visões
do mesmo conjunto de dados ou rodar as linhas e colunas para obter resumos diferentes dos dados de
origem.
As tabelas dinâmicas em MSExcel, em comparação com o Reporting Services fornecem um
maior controlo na formatação (diferentes fontes, tamanhos, estilos e cores) dos valores, disponibiliza
uma maior diversidade de gráficos, permite criar diferentes folhas no mesmo ficheiro e pode ser
interpretado por diversos programas.
Para uma grande flexibilidade na manipulação dos dados, e também a possibilidade de criação
de gráficos a partir dos dados manipulados, a apresentação e a manipulação dos dados será feita
através do Microsoft Excel, uma vez que grande parte dos utilizadores já está familiarizada com esta
ferramenta.
Pág
ina3
5
Figura 26 – Escolher origem de importação.
Para a análise de um cubo no Microsoft Excel é necessário em primeiro lugar criar uma ligação a
um cubo do SQL Server Analysis Services, Figura 26, e importar os dados para o Excel como uma tabela
dinâmica.
Figura 27 – Introdução de credenciais do SQL Server.
O primeiro passo da importação consiste na introdução das credenciais para criar a ligação ao
servidor, conforme Figura 27.
Pág
ina3
6
Figura 28 – Selecção do cubo.
De seguida, selecciona-se a base de dados e depois o cubo que se pretende importar para
análise, conforme Figura 28.
Figura 29 – Nome e descrição da ligação.
No passo seguinte, Figura 29, atribui-se o nome e, caso o pretenda, a descrição da ligação a
estabelecer.
Figura 30 – Tipo de visualização dos dados.
Pág
ina3
7
Neste passo, Figura 30, selecciona-se o método de visualização dos dados pretendido, bem
como a localização destes na folha indicada.
Figura 31 – Volume de income>50k para todos os países e agrupados por continente.
Na primeira análise a ser efectuada vai ser usado o campo income>50k, que indica o volume de
pessoas com income maior que 50k por país de origem, proveniente da hierarquia da tabela
dimLocalizacao. Assim, consegue-se analisar a distribuição de income>50k, através de drilldown, e
agrupá-las por continente e país. Utilizando a ordenação é facilmente perceptível o território nativo das
pessoas com income superior a 50k.
Figura 32 – Aplicação de filtros.
No entanto, nem sempre nos interessa toda a informação, podendo então colocar filtros nas
tabelas dinâmicas, Figura 32, para obtenção uma análise mais profunda. Neste caso, foi excluído o área
com maior frequência para se poder identificar e diferenciar mais facilmente as restantes localizações.
Pág
ina3
8
Figura 33 – Formatação de campo.
Existe também a hipótese de modificar a representação dos próprios dados. A Figura 33
demonstra uma transformação efectuada nos dados para apresentar os valores em percentagem, em
que neste caso se observa o peso percentual que cada território teve no volume de vendas efectuado
nos quatro anos em análise.
Figura 34 – Análise percentual volume de income>50k.
A transformação efectuada anteriormente pode também ser observada graficamente. Na
Figura 34 observa-se a análise para todas as áreas excepto a América do Norte. Assim, comprova-se
facilmente e rapidamente que as pessoas de origem asiática têm maior poder de compra. A Europa,
América Central e América do Sul estão num patamar mais baixo em termos de vendas.
Pág
ina3
9
Reports
Figura 35 – Criar um novo projecto.
Para a criação dos reports, o primeiro passo consiste em criar um novo projecto do tipo
Business Intelligence, neste caso um Report Server Project Wizard, conforme Figura 35.
Figura 36 – Selecção do data source.
De seguida, é necessário seleccionar o data source que irá ser utilizado para a criação dos
reports, conforme Figura 36.
Pág
ina4
0
Figura 37 – Selecção dos atributos a analisar e criação de um campo calculado.
No seguimento do passo anterior, é necessário escolher os atributos a analisar no relatório e
criar novos membros calculados que ajudem na análise dos relatórios, como demonstra a Figura 37.
Figura 38 – Query criada.
Sendo de seguida apresentada a query criada automaticamente como demonstra a Figura 38.
Pág
ina4
1
Figura 39 – Selecção do formato do relatório.
Depois, é necessário escolher o tipo de formato de relatório que se pretende, ou formato em
tabela ou em matriz, conforme Figura 39.
Figura 40 – Agrupamento dos dados
Este passo baseia-se no agrupamento dos diversos dados em três áreas, páginas, grupos ou
detalhes. Como mostra a Figura 40, coloca-se cada género por cada página, raça por grupos e os
restantes na área de detalhe.
Pág
ina4
2
Figura 41 – Escolha do layout da tabela
Para cada tabela é preciso escolher o seu layout, como apresenta a Figura 41, foi escolhido o
layout slate e enable drilldown. Nos dois passos seguintes, define-se a cor das tabelas e o respectivo
nome do report.
Figura 42 – Report criado
Terminado o último passo da criação do report tem-se a opção de pré-visualizar o report e de
exportar para outros formatos, como comprova a Figura 42.
Pág
ina4
3
Figura 43 – Report exportado para MHTML
Como forma de demonstrar as capacidades dos reports, exportamos para o formato MHTML.
Como se observa na Figura 43, o browser apresenta uma tabela com o número de income menor ou
igual a 50k e superior a 50k por raça e género.
Conclusão
Nesta fase, grande parte do tempo foi consumida pela criação dos cubos, tendo-nos deparado com
a dificuldade de estabelecer hierarquias. Outro tanto foi investido na análise dos dados utilizando
tabelas e gráficos dinâmicos, através do Microsoft Excel, e pela utilização dos relatórios criados através
do Microsoft Visual Studio.
Na exploração dos cubos foram utilizadas diversas operações, como o Drill-down e Roll-up. Foram
também utilizados filtros e operações matemáticas sobre os dados. Apercebemo-nos assim que a
manipulação dos dados com estas ferramentas se tornam fáceis para a obtenção das informações
pretendidas.
É de notar que, aquando da análise reparámos que o nome dos campos criados no Data Warehouse
poderiam ser mais descritivos para os gestores pois, apesar de quem está a desenvolver o Data
Warehouse saber o conteúdo destes, ou aperceber-se facilmente do significado de cada designação,
para quem está por fora do assunto não se torna tão intuitivo visto alguns terem nomes abreviados.
Pág
ina4
4
8. Conclusão
Resultados
Os conceitos teóricos permitiram a elaboração do modelo multidimensional, criação de reports,
análises e dashboards como proposta para alcançar os objectivos propostos. Com base nos
conhecimentos adquiridos, possibilitou que potenciássemos a nossa aprendizagem na exploração,
análise e possível tomada de decisão com base em dashboards e reports que se basearam em análises
tendo em consideração o atributo de income. Como foi referido, tivemos alguns problemas no
manuseamento da ferramenta inicialmente disponibilizada, o que levou a um atraso definitivo no
projecto. Tendo em conta isso, foi decidido então escolher outra tecnologia que nos permitisse obter os
resultados esperados para respectiva tomada de decisão com base na informação disponibilizada.
As técnicas e as ferramentas neste projecto permitiram obter uma visão melhor e amplificada
do negócio, o que simplifica o processo de exploração de dados, auxiliando a tomada de decisão e o seu
uso nas estratégias de negócio.
A realização deste tipo de projecto, enriqueceu bastante os nossos conhecimentos, uma vez
que pomos em prática conceitos que não utilizamos frequentemente, e além disso permite-nos explorar
novas ferramentas, que estão actualmente no topo do mercado de trabalho.
Limitações
As limitações sentidas estiveram principalmente ligadas com os recursos disponibilizados
sobretudo a tecnologia SpagoBI que se mostrou de difícil configuração. Devido a isso, surgiu a
necessidade de se optar por outra ferramenta, recaindo a escolha no Petanho BI Suite. Outra limitação
foi na construção de dashboards e na análise da respectiva informação e o seu contributo na ajuda de
tomada de decisão.
Pág
ina4
5
9. Referências
Pentaho, Manually Configuring an MS SQL Server 2000 and 2005 Connection, Acedido a 15 de
Fevereiro de 2011 - http://wiki.pentaho.com/display/ServerDoc2x/
Manually+Configuring+an+MS+SQL+Server+2000+and+2005+Connection
Pentaho , Part 2: Configuring Server Side Data Connections – even easier!, Acedido a 15 de
Fevereiro de 2011 - http://blog.pentaho.com/2010/07/23/part-2-configuring-server-side-data-
connections-even-easier/
Pág
ina4
6
10. Anexos
Os Analyser Reports, Iteractive Reports e Dashboards podem ser vistos em ficheiros
anexos a este documento.
Anexo 1
Como criar uma ligação SQL em Pentaho
Para aceder a uma base de dados Microsoft SQL Server, deve-se primeiro instalar e configurar o SQL
Server Driver para JDBC. Um tipo de JDBC driver fornece uma conectividade para um ambiente Java e
como o Pentaho é baseado em Java, ou seja, este Driver é essencial. Este mesmo Driver fornece acesso
JDBC para ambas as versões 32 e 64-bit de SQL Server através de uma aplicação ou applet Java. De
seguida será explicado passo a passo, com recurso a imagens, as configurações necessárias para
efectuar a conexão do SQL Server ao Pentaho BI Suite:
1. Abrir: ‘http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21599’
2. Fazer o download do ficheiro '1033\sqljdbc_3.0.1301.101_enu.exe'
3. Executar o ficheiro e fazer unzip dos ficheiros para a pasta ‘C:\programas’
4. Na pasta que foi zipada 'C:\Programas\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu',
deve-se copiar o ficheiro 'sqljdbc4.jar' para:
C:\Programas\pentaho\design-tools\report-designer\lib\jdbc
C:\Programas\pentaho\server\biserver-ee\tomcat\webapps\pentaho\WEB-INF\lib
C:\Programas\pentaho\design-tools\metadata-editor\libext\JDBC
C:\Programas\pentaho\design-tools\schema-workbench\drivers
C:\Programas\pentaho\design-tools\data-integration\libext\JDBC
C:\Programas\pentaho\server\enterprise-console\jdbc
5. Dar permissões de acesso remoto ao SQL, acedendo ao:
SQL Server Configuration Manager
Pág
ina4
7
Figura 44 - Aceder ao SQL Server Configuration Manager
Figura 45 - SQL Server Configuration Manager – SQL Server Services
Pág
ina4
8
Figura 46 - SQL Server Configuration Manager - Protocols for SQL Server
SQL Server Management Studio
Figura 47 - Aceder ao SQL Server Management Studio
Pág
ina4
9
Figura 48 - SQL Server Management Studio - Server Properties
6. Criar nova conexão no Enterprise Console
Figura 49 - Petanho Enterprise Console - Admnistration
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
User Name: UserSQLSERVER
Password: PasswordSQLSERVER
URL: jdbc:sqlserver://<instancia SQL >;DatabaseName=<Base dados SQL>
7. Criar nova conexão no User Console (imagem: ConexãoUserConsole.png)
Pág
ina5
0
Figura 50 - Criar conexão ao SQL Server através do Pentaho
