Individual

Transcrição

Individual

Instituto Superior de Ciências do Trabalho e da Empresa
SISTEMAS INTELIGENTES DE APOIO À DECISÃO
Mestrado em Gestão de Sistemas de Informação
2000/2001
Desenvolvimento de uma Ferramenta OLAP
Sobre Informações dos Alunos do ISCTE
Docente: Professora Doutora Maria José Trigueiros
Realizado por:
João Nascimento
Lisboa, 11 de Setembro de 2001
Sistemas Inteligentes de Apoio à Decisão
Sistema OLAP sobre DataMart ISCTE
Índice
1.
Introdução .............................................................................................................................. 2
2.
Conceitos Teóricos ................................................................................................................. 3
3.
Enquadramento...................................................................................................................... 7
4.
Metodologia.......................................................................................................................... 11
5.
Arquitectura dos DataMarts ................................................................................................ 13
6.
Extracção, Tratamento e Carregamento dos Dados........................................................... 18
Dimensões................................................................................................................................. 19
Dimensão Temporal.............................................................................................................. 20
Dimensão Curso .................................................................................................................... 20
Dimensão Disciplina ............................................................................................................. 20
Dimensão Geografia.............................................................................................................. 20
Dimensão Curricular ............................................................................................................. 20
Dimensão Aluno.................................................................................................................... 20
Tabelas de Factos .................................................................................................................... 29
2 – Inscrições acumuladas ..................................................................................................... 29
3 – As reprovações no ISCTE............................................................................................... 36
6 – Rendimento escolar dos alunos ingressados em regimes especiais ................................ 38
9 – Rendimento escolar dos alunos nas disciplinas das licenciaturas do ISCTE.................. 43
10 – As médias finais de curso e a sua relação com as notas de ingresso............................. 46
Ano ........................................................................................................................................ 46
11 – Relação entre as notas de ingresso e as cadeiras relacionadas com as mesmas
consoante o núcleo a que pertencem..................................................................................... 50
14 – Distrito de origem dos alunos do ISCTE ...................................................................... 53
17 – Mudanças de curso ........................................................................................................ 57
7.
Implementação da Ferramenta OLAP................................................................................ 61
Criação das Dimensões ........................................................................................................... 62
Criação dos Cubos................................................................................................................... 73
2 – Inscrições acumuladas ..................................................................................................... 74
3 – As reprovações no ISCTE............................................................................................... 80
9 – Rendimento escolar dos alunos nas disciplinas das licenciaturas do ISCTE.................. 83
10 – As médias finais de curso e a sua relação com as notas de ingresso............................. 84
11 – Relação entre as notas de ingresso e as cadeiras relacionadas com as mesmas
consoante o núcleo a que pertencem..................................................................................... 85
14 – Distrito de origem dos alunos do ISCTE ...................................................................... 87
17 – Mudanças de curso ........................................................................................................ 87
8.
Navegação............................................................................................................................. 90
Utilizando o SQL Server......................................................................................................... 90
Utilizando Outros Clientes ..................................................................................................... 93
Microsoft Excel 2000 ............................................................................................................ 94
Microsoft Front Page 2000.................................................................................................. 102
9.
Conclusão ........................................................................................................................... 107
OLAP sobre DataMart Alunos
10.
Futuros Trabalhos.......................................................................................................... 108
11.
Índice Fotografias.......................................................................................................... 109
12.
Bibliografia e outras fontes de informação................................................................... 111
1
1. Introdução
Este trabalho foi desenvolvido no âmbito da disciplina de Sistemas
Inteligentes de Apoio à Decisão, do Mestrado em Gestão de Sistemas de
Informação do ISCTE. Resultou do desafio lançado pela Docente –
Professora Doutora Maria José Trigueiros - para que fosse dado mais um
contributo para a construção e utilização do Data WareHouse sobre a
situação do ensino no ISCTE.
Partindo de um estudo magnífico (realizado pela mesma professora e
alguns dos seus alunos da Licenciatura em Informática e Gestão deste
Instituto), este trabalho terá como objectivo a construção de um
instrumento OLAP, provando a utilidade do Microsoft Analisys Services
(incluído no Microsoft SQL Server 2000-Enterprise Edition) na execução
de projectos desta natureza. Desta forma, espera-se que seja possível obter
as confirmações às hipóteses geradas no decorrer do estudo já mencionado,
anteriormente.
2
2. Conceitos Teóricos
Para se poder obter a informação necessária, é preciso pesquisar
grandes quantidades de dados (seja por data mining ou por outro
processo), pelo que é necessário guardar todos estes dados em enormes
Bases de Dados (na ordem dos TeraBytes). Uma maneira de guardar
informação é através da criação de um Data Warehouse, tal como veremos
de seguida.
Quando uma organização, como o ISCTE, quer tomar importantes
decisões de fundo que podem decidir o seu rumo, irá provavelmente
basear-se em informação histórica. Com essa informação, poderá através
de técnicas como Data Mining, obter informações valiosas, que lhe
permitirão tomar as decisões certas.
Um Data Warehouse é exactamente isso: A Base de dados que gere
toda essa informação histórica (isto colocado de um modo muito simplista).
Como se pode ver pela figura, Data Mining não é a única técnica que
pode ser usada num Data Warehouse. Deve-se isso sim, usar uma série de
técnicas (como OLAP – Online Analytical Processing), que será utilizada
3
neste trabalho) que se complementem entre si, formando uma poderosa
ferramenta de análise de informação. A informação extraída por estas
técnicas, pode ainda voltar para o Data Warehouse, para ser extraída
novamente com o objectivo de refinar os dados.
A informação que está contida num Data Warehouse é composta por
informação recolhida na organização, das chamadas "Bases de Dados
Operacionais".
Em muitas organizações, já existem aplicações específicas para as
operações do dia a dia, tendo estas as suas próprias bases de dados,
vulgarmente chamadas de "Bases de Dados Operacionais".
Estas aplicações estão completamente optimizadas para o trabalho
especifico que têm que realizar, não tendo assim informação histórica
desnecessária para tal. De tempos a tempos (podem ser horas, dias ou
semanas - depende da organização em causa), toda a informação contida
nessas bases de dados é transferida para um Data Warehouse, onde vai
ficar inalterada. A todos esses dados é ainda acrescentada um "selo
temporal", para diferenciar um mesmo dado, numa altura diferente (por
exemplo, se alterar a ficha de um cliente, a informação da ficha anterior
ainda fica no Data Warehouse, mas com uma data diferente). Pelo exposta
se começa a perceber que as regras de Codd, tendo em vista a normalização
podem não se aplicar a estes ambientes.
Alguns dados encontram-se repetidos em várias bases de dados
operacionais, pelo que é necessário que não hajam repetições na cópia para
o Data Warehouse. Nem todos os dados são suficientemente importantes
para guardar, pelo que para o Data Warehouse só é guardada a informação
"interessante". Por esta razão, é necessário que o Data Warehouse esteja
4
preparado para ser alterado, de acordo com as crescentes necessidades da
organização.
Algumas considerações elementares devem ser levadas em conta,
quando se vai implementar um Data Warehouse.
A informação deve estar dependente do tempo, ou seja, deve conter
um "selo temporal" para que haja uma ligação entre a informação e a data
em que ela entrou no Data Warehouse.
A informação nunca é actualizada. Os dados contidos no Data
Warehouse nunca são alterados, podendo apenas ser consultados. Isto quer
dizer que se uma Base de Dados Operacional altera determinados dados, o
Data Warehouse conserva os dados antigos e as novas alterações, com
"selos temporais" diferentes. Um data Warehouse está assim sempre cheio
de informação histórica.
Num Data Warehouse é necessário que todos os intervenientes no
processo (administradores e utilizadores) tenham acesso a toda a
informação nela contida (tabelas e atributos). Sendo assim, é necessário
alguma coisa que diga que tipo de informação existe, onde está essa
informação, em que formato está, qual a relação entre os diversos dados e
qual a sua origem.
Por estas razões, é necessário existirem dados independentes, apenas
com este tipo de informação. Estes dados são chamados Metadata e servem
para definir a estrutura de uma base de dados. Quando se quer
implementar um sistema bastante complexo, a Metadata é indispensável
para determinar a estrutura das bases de dados operacionais e do Data
Warehouse. Metadata é, então, usada pelos utilizadores para poderem
pesquisar as bases de dados e pelo administrador para poder
correctamente estruturar a organização dos dados.
5
A Metadata também é usada como registo das alterações que vão
sendo efectuadas num mesmo registo mas com novas entradas em datas
diferentes.
Um Data Warehouse pode conter informação sobre toda uma
organização,
mas
por
vezes
dentro
das
organizações
existem
departamentos que apenas precisam de ter acesso a parte da informação
existente.
Para esses departamentos deve-se então criar Data Marts, que são
fracções do Data Warehouse mais pequenas, contendo apenas informação
específica para determinado grupo de utilizadores. Consegue-se assim
optimizar estas bases de dados para a sua utilização específica, uma vez
que não contêm demasiada informação supérflua para os seus utilizadores.
Para além disso, isto permite tornar o processamento mais distribuído, logo
tornar todo este processo mais eficiente.
Depois de construído o Data Wharehouse, entra em acção o OLAP. O
objectivo de uma ferramenta OLAP, como a que vai ser implementada no
decorrer deste trabalho, é conseguir a verificação de hipóteses prévias.
Depois de colocada a questão, são formuladas hipóteses que lhe dão
resposta e, com a ajuda da ferramenta OLAP, é possível confirmar ou
refutar cada hipótese enunciada.
Para que seja possível construir uma ferramenta que possa validar as
hipóteses geradas para cada questão, é necessário ter os dados essenciais,
correctos e guardados num local acessível com essa ferramenta.
6
3. Enquadramento
Como se pode perceber pelas referências anteriores, a construção de
um Data Warehouse envolve um trabalho hercúleo. A prova disso é o facto
de
terem sido precisos alguns contributos valiosos de diversos
colaboradores para que se tenha chegado, no caso em análise, aos
resultados que serviram de base a este trabalho. E este não será o processo
final. Depois de concluída esta fase, muito trabalho ainda está para fazer,
sendo necessário passar os resultados a futuras gerações de alunos que o
continuarão.
Como se pode ler no documento produzido pelo trabalho a que se vai
dar continuidade: “... pretende-se analisar os aspectos críticos que
condicionam a evolução do ensino Universitário no ISCTE... ”.
Segundo o mesmo documento: “Os aspectos a focar mediante esta
proposta de estudo serão:
? a exigência requerida por cada curso;
? os antecedentes dos alunos, em questões de habilitações literárias ,
família, naturalidade, idade, opções de candidatura, entre outros;
? avaliação da evolução das notas finais de curso ao longo de um
período de tempo;
? avaliar a relação entre o modo de ingresso e as notas obtidas ao longo
do curso;
? avaliar o aproveitamento escolar dos alunos;
? analisar a tendência do tempo de realização pelos alunos nos vários
cursos;
7
? avaliar a situação profissional de cada aluno relacionada com o seu
curso;
? estudar a relação existente entre a reforma de educação do ensino
secundário e o aproveitamento no ISCTE;
? avaliar a relação entre o distrito de origem e as notas obtidas ao longo
do curso; “
Como resultado do estudo: “Estes aspectos encontram-se integrados
nos seguintes módulos:
1 – O ingresso no ISCTE
2 – Inscrições acumuladas
3 – As reprovações no ISCTE
4 – Os abandonos no ISCTE
5 – Rendimento escolar dos alunos por contingente de ingresso
6 – Rendimento escolar dos alunos ingressados em regimes especiais
7 – Rendimento escolar dos alunos bolseiros
8 – Rendimento escolar dos alunos que frequentam actividades
extracurriculares do ISCTE
9 – Rendimento escolar dos alunos nas disciplinas das licenciaturas do
ISCTE
10 – As médias finais de curso e a sua relação com as notas de ingresso
11 – Relação entre as notas de ingresso e as cadeiras relacionadas com as
mesmas consoante o núcleo a que pertencem
12 – Relação entre as notas de ingresso e as cadeiras complementares às
cadeiras base do curso
8
13 – Relação entre a área de ensino secundário com a tendência das notas
no ensino superior
14 – Distrito de origem dos alunos do ISCTE
15 – Comparação dos alunos ingressados no ISCTE com os de licenciaturas
suas congéneres
16 – Conhecimento da situação dos recém licenciados perante o emprego
17 – Mudanças de curso
18 – Comparação de notas entre alunos que ingressam após a reforma
educativa e do antigo regime.”
Por uma questão de tempo disponível apenas serão implementados os
módulos definidos como sendo prioritários (a desenvolver a curto prazo),
ou seja:
ISCTE
Já que se trata de uma fracção do problema global, diremos que, em
vez de um Data Warehouse se fará um pequeno DataMart.
9
Cada uma destas questões está bem definida no documento que serviu
de suporte à realização deste trabalho, pelo que será dispensada a
descrição de cada módulo.
10
4. Metodologia
A execução de uma ferramenta OLAP pressupõe, como se pôde
verificar no capítulo dos conceitos teóricos, a existência de um
Datawarehouse, ou pelo menos de um DataMart. Isto porque, como foi
dito, a estrutura das bases de dados operacionais não são as mais
adequadas para o efeito. Portanto, antes de proceder à construção da
ferramenta OLAP há que proceder à transformação da base de dados
operacional num estado mais apropriado para conseguir colocar a
ferramenta a trabalhar.
Deste facto resultou a necessidade de proceder ao processo de ETL
(Extracção, Tratamento e Carregamento) dos dados. Este processo não
pode ser alheio ao objectivo, nem ao estado inicial dos dados, pois a sua
finalidade é, exactamente, colocar os dados operacional numa situação de
integridade e isenção de erros que permita retirar conclusões reais.
Para cada módulo vai ser necessário definir métricas e dimensões que
permitam navegar e obter informação para responder às questões que lhe
estão inerentes.
Posteriormente, é necessário reunir todas essas métricas e dimensões
numa estrutura única à qual se juntarão os dados. Esta pode ser
redundante, conter dados repetidos, mas há-de ser a que melhor se adequa
às necessidades.
11
ETL
Alulicen.mdb
DM alunos
OLAP
Figura 1 : Esquema da transformação dos dados operacionais
Por fim, é necessário colocar em prática tudo isto no Microsoft SQL
2000 Server.
Resumindo:
1º Definir Arquitectura dos DataMarts (Métricas, Dimensões e factos);
2º Procurar os dados necessários nas bases de dados operacionais;
3º Criar os dados e informações não disponíveis;
4º Criar cubos;
5º Navegar...
12
5. Arquitectura dos DataMarts
As informações que vão ser necessárias para cada um dos módulos são
apresentados de seguida:
Aluno
Curso
Semestre
Ano Lectivo
Ano do Curso
N.º Disciplinas inscritas
N.º Disciplinas concluídas
N.º Disciplinas em atraso
N.º Inscrições acumuladas
Curso
Disciplina
Ano Lectivo
N.º Inscritos
N.º Reprovações
Percentagem Reprovações
Curso
Ano do Curso
Ano Lectivo
Média de Curso
Regime de ingresso
ISCTE
Curso
13
Turma
Ano Lectivo
Disciplina
N.º alunos avaliados
N.º alunos inscritos
N.º alunos aprovados
Ano
Aluno
Curso
Ano de Ingresso
Nota de Seriação
Média de Curso
Média das Notas do Curso
Média das Notas de Ingresso do Curso
Curso
Disciplina
Departamento
Ano Lectivo
Nota de Seriação
Média da Disciplina
Aluno
Curso
Ano Lectivo
N.º de Alunos
Distrito
Concelho
Freguesia
Aluno
Curso
Ano de Ingresso
14
Ano Lectivo
Nota de Seriação
Nº Disciplinas concluídas
Ano Conclusão
Média Curso anterior
Ano Ingresso Novo Curso
Como é possível perceber pela descrição anterior, existe um conjunto
grande de elementos em comum entre os vários módulos. Este facto facilita
a reunião de todas as dimensões e métricas numa única tabela de factos
(fact table).
Uma análise insensível daria o seguinte resultado:
Aluno
Ano Conclusão
Ano de Ingresso
Ano do Curso
Ano Lectivo
Concelho
Curso
Departamento
Disciplina
Distrito
Freguesia
Média de Curso
N.º alunos reprovados
N.º de Alunos
N.º Inscritos
15
N.º Reprovações
Nota de Seriação
Regime de ingresso
Semestre
Tipo de Disciplina
Turma
Os elementos assinalados a Negrito são considerados métricas, já que
resultam dos factos. Alguns deles podem ser obtidos através de simples
contas sobre outros dados. No entanto, optou-se por proceder à sua
inclusão na tabela de factos para facilitar a consulta.
Quanto aos elementos em itálico, estes são considerados dimensões ou
parte delas.
Com base no exposto, a estrutura aconselhada teria o seguinte aspecto:
16
Tabela de Factos
Dimensão Curso
Curso
Designação Curso
Dimensão Disciplina
Disciplina
Departamento
DesignaçãoDisciplina
Tipo de Disciplina
DesignaçãoDepartamento
Dimensão Temporal
Ano Lectivo
Lectivo Completo
Dimensão Curricular
CodCurricular
Ano do Curso
Semestre
Aluno
Ano Conclusão
Ano de Ingresso
Ano Lectivo
Disciplina
Média de Curso
N.º alunos reprovados
N.º de Alunos
N.º Inscritos
N.º Reprovações
Regime de ingresso
Turma
Nota de Seriação
CodGeograf
CodCurricular
Figura 2 : Estrela do Data Warehouse com as dimensões e as métricas
17
Dimensão Geográfica
Cod_Geograf
Distrito
Concelho
Freguesia
Região
País
Dimensão Aluno
Aluno
Nome
6. Extracção, Tratamento e Carregamento dos Dados
Para obter as informações, identificadas anteriormente como sendo
necessárias, há que buscar nas bases de dados operacionais por “matériaprima” que possibilite a obtenção do que se pretende.
Esta tarefa não é fácil, atendendo ao estado em que se encontra a base
de dados operacional ao alcance. Contudo, com a excelente ajuda
providenciada pelo Enterprise Manager do Microsoft SQL 2000, é possível,
depois de algum trabalho, obter o que é necessário.
Segue-se uma explicação do processo de produção das informações
necessárias, bem como do processo de gestão da MetaData.
O trabalho a realizar decorre, essencialmente ao nível do Enterprise
Manager do SQL Server 2000. Numa primeira fase constrói-se a Staging
Area (também conhecida por ODS – Operational Data System)
directamente das bases de dados operacionais. Desta forma consegue-se
obter todos os dados relevantes (quer por cópia directa, quer por
construção de fórmulas) agrupados por tema (Dimensões).
Numa segunda fase, procede-se à união de todas tabelas de factos,
dando origem ao verdadeiro DataWarehouse. Alternativamente, a este
procedimento, pode deixar-se as tabelas de factos como estão (separadas),
interligando-as apenas através de elementos comuns.
Neste trabalho optou-se por realizar a primeira hipótese. Mais
trabalhosa mas é a que melhor realça as potencialidades do Microsoft SQL
Server 2000.
18
Dimensões
O primeiro trabalho (e o mais fácil) será, antes de mais, trazer para a
Staging Area as tabelas das dimensões. Incluímos neste grupo a tabela de
Disciplinas, Cursos, Curricular, Geográfica, Aluno.
Para cada uma é necessário procurar na base de dados operacional por
elementos que as preencham. As tarefas que farão essa busca baseiam-se
nos seguintes princípios:
Elemento
Código do Curso
Designação do Curso
Código da Disciplina
Designação da Disciplina
Tipo de Disciplina
Código de Departamento
Nome do Departamento
Cod_Curricular
Ano Curricular
Semestre
Ano Lectivo
Lectivo Completo
Numero do Aluno
Nome do Aluno
Nota de Seriação
Cod_Geograf
Cód_Geograf
Frequesia
Concelho
Distrito
Região
País
Tabela origem
Campo Origem
Codigo
NomeCurto
Codigo
Cadeiras
Designacao
Teoricas/Praticas/TeorPrat
Departamento Codigo
Departamento Designação
Hstins
AnoCurr
Hstins
SemesCurr
Lectivos
Lectivo
Lectivos
Lectivo+”/”+Lectivo+1
Numero
Nome
NotaIngr
AlunosGeograf
NatuNaci/Naturalidade
/NataFreg/NataConc
/NataDist
Cursos
Tabela dos Distritos, Concelhos e Freguesias
Nacionais (retirada na internet)
Resultando, então, nas seguintes instruções:
19
Dimensão Temporal
SELECT lectivos.lectivo, (str(lectivos.lectivo)+”/”+
str(lectivos.lectivo+1)) as LectivoCompleto
FROM lectivos
Dimensão Curso
SELECT cursos.codigo, cursos.NomeCurto
FROM cursos
SELECT cadeiras.codigo, cadeiras.designacao,
IIf([cadeiras].[Teoricas]<>"",'T',IIf([cadeiras].[Praticas]<>"",'P',II
f([cadeiras].[TeorPrat]<>"",'M','I'))) AS Tipo, departamento.codigo as
Departamento, departamento.designacao as DesignacaoDepartamento
FROM cadeiras inner join departamento on
(left(cadeiras.codigo,2)=departamento.codigo);
Dimensão Geografia
SELECT geografia.cod_geograf, geografia.Freguesia, geografia.Concelho,
geografia.Distrito, geografia.Região, geografia.Pais
FROM geografia
SELECT Distinct Hstins.AnoCurr, Hstins.SemesCurr
FROM Hstins
O campo CodCurricular terá de ser preenchido gerando um número para
cada nível.
Dimensão Aluno
SELECT alunos.numero, alunos.nome
FROM Alunos
20
As imagens seguintes resumem apenas a passagem de uma das tabelas
(neste caso, a tabela relativa à dimensão Temporal). Será escusado voltar a
descrever o trabalho realizado para as restantes dimensões, dado que se
tornaria bastante repetitivo.
Para conseguir preencher essa tabela será utilizado um package no
Data Transformation Services. O SQL Server 2000 disponibiliza um editor
de packages que facilita a construção de ferramentas de ETL. O ambiente
de trabalho do Enterprise Manager é mostrado na figura seguinte.
Base de dados a criar.
(Staging area)
Lista de tabelas vazia.
Ferramentas de ETL a
utilizar para criar as
tabelas.
Figura 3: Ambiente de trabalho do Enterprise Manager
Resta agora criar um novo package que permita seleccionar os dados
da Base de dados operacional para dentro da Staging Area. A figura
seguinte mostra como o ambiente de trabalho do editor de package.
21
Figura 4 : Ambiente de trabalho do DTS
Dado que a Base de dados operacional se encontra em Microsoft
Access, o primeiro elemento a levar para o package é representado na barra
lateral por uma chave (símbolo do Microsoft access mostrada uma janela de auxílio à identificação do ficheiro.
22
). Será então
Figura 5 : Identificação do Ficheiro correspondente à BD Operacional
O resultado será o que se mostra na figura seguinte.
Figura 6 : Criação de um package – Ligação à BD Operacional
23
O segundo passo consiste na ligação à Staging Area. Dado tratar-se de
uma estrutura do SQL Server 2000, o símbolo ( ) que a representa é o
primeiro da lista. Terá de ser arrastado para o interior do package e
definido convenientemente, como se pode ver na figura seguinte.
Figura 7 : Definição da ligação à Staging Area - SIAD
Por último, falta o mais importante – definir a regra de transposição
entre BD Operacional e a Staging Area. Isso faz-se através de uma
Transform Data Task (
). Basta fazer clique nesta ferramenta, clique
sobre a origem (BD Operacional) e novo clique sobre o destino (Staging
Area). Aparecerá um traço que simboliza a transformação. Resta agora
alterar as propriedades desta tarefa, por forma a que faça exactamente o
que dever ser feito.
24
Figura 8 : Definição da transformação
O trabalho principal desenvolve-se a este nível. Todo o segredo da
transformação da base de dados operacional para o Sistema de apoio à
decisão está aqui em foco.
Se na base de dados operacional já existir uma consulta (view) cujo
resultado seja exactamente o que se pretende, é só preencher o campo
Table/View com o seu nome. Caso contrário, é possível especificar um novo
comando SQL no campo SQL query. Este último pode ser preenchido com a
ajuda de um editor de expressões SQL, acessível pelo botão de comando
Build Query...
25
Figura 9 : Editor de Queries
Com a lista de tabelas à esquerda e com os campos, a construção da
consulta que se pretende torna-se facilitada.
Como a tabela de origem não está em condições de ser copiada
integralmente para a Staging Area, nem existe nenhuma consulta na base
de dados operacional que transforme os dados da forma pretendida, há
que preencher o campo SQL Query. Depois de vários cliques, obtemos o
seguinte:
26
Figura 10 : Definição da Instrução SQL da Dimensão Temporal
De seguida define-se o destino dos dados recolhidos, utilizando o
separador Destination. Aqui, como não foi criada previamente uma
estrutura que acolhesse os dados, é possível fazê-lo neste momento.
Figura 11 : Definição da Estrutura da Dimensão Temporal
27
O único reparo a fazer, nesta circunstância, é a alteração do nome da
tabela de destino (New Table, não é muito elucidativo). Chamemo-lhe
apenas Lectivos.
No separador Transformations verificaremos, simplesmente, como a
origem passa para o destino.
Figura 12 : Transformações Origem/Destino da Dimensão Temporal
As definições necessárias estão feitas, pelo que accionaremos o botão
OK. Resta, agora, gravar o package (utilizando a
e proceder à sua execução (utilizando o botão
28
), para utilização futura,
).
Figura 13 : Resultado da Execução do Package
Tabelas de Factos
Uma vez passadas as tabelas das dimensões para o interior da Staging
Area, segue-se a construção das tabelas de factos, para cada um dos
módulos.
Campo na Staging Area
AlunoNum
CodCurso
CodCurricular
AnoLectivo
DInscritas
Dfeitas
Rep
InscAcum
BDO Origem
Hstins
Hstins
Hstins
Hstins
Cadins
Cadfei
Cadfei
Cadins
29
Origem
Numero
CodCurso
SemesCurr e AnoCurr
DataInscr
Contar em Hstins
Contar em CadFei
CadIns - CadFei
Inscrição Duplicada
Os elementos a negro terão de ser calculados, ao passo que os restantes
serão apenas copiados. Vai então ser gerada uma tabela na Staging Area
que terá a estrutura constituída pelas linhas da tabela anterior.
Para este primeiro exemplo teremos uma expressão ligeiramente
complexa. Devido às limitações das expressões SQL do SQL Server 2000
(não permite a contagem de elementos distintos automaticamente), foi
necessário implementar uma consulta no Access (fonte dos dados
operacionais), por forma a conseguir obter as inscrições acumuladas.
Na construção desta expressão, assumiu-se que todos os registos na
tabela cadfei (na BD operacional) se referem a disciplinas realizadas,
independentemente da nota que se encontra no campo Nota (dado que
alguns registos não têm nota ou têm uma nota inferior a 9, assumiu-se que
sejam erros de preenchimento).
A expressão que permitiu preencher o primeiro Data Mart é a
seguinte:
SELECT hstins.numero AS AlunoNum, hstins.CodCurso,
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataI
nscr)-1) AS AnoLectivo, Count(hstins.numero) AS DInscritas,
Count(cadfei.Nota) AS DFeitas, [DInscritas]-[DFeitas] AS Rep,
DSum("InsRepetidas","[01-InscRepet]", "AlunoNum=val("+Str(AlunoNum)+")
and (AnoLectivo<val("+Str(AnoLectivo)+") or
(AnoLectivo=val("+Str(AnoLectivo)+") and
(AnoCurr<val("+Str(oAnoCur)+") or (Anocurr=val("+Str(oAnoCur)+") and
SemesCurr<='"+oSemestre+"'))))") AS InscAcum
FROM hstins LEFT JOIN cadfei ON (hstins.numero = cadfei.numero) AND
(hstins.codigo = cadfei.codigo)
GROUP BY hstins.numero, hstins.CodCurso,
nscr)-1)
30
ORDER BY hstins.numero, hstins.CodCurso,
nscr)-1);
Onde a subquery 01-InscRepet contém o seguinte código (no sentido
de obter o número de disciplinas que o aluno se encontra a repetir num
determinado ano):
SELECT H1.numero AS AlunoNum,
IIf(Month([h1.DataInscr])>8,Year([h1.DataInscr]),Year([h1.DataInscr])1) AS AnoLectivo, H1.CodCurso AS oCurso, H1.AnoCurr, H1.SemesCurr,
Count(H1.codigo) AS InsRepetidas
FROM hstins AS H1 WHERE (((Exists (SELECT H2.numero FROM hstins H2
WHERE (H1.numero=H2.numero) and (H1.codigo=H2.codigo) and
(IIf(Month([h1.DataInscr])>8,Year([h1.DataInscr]),Year([h1.DataInscr])
-1) >
IIf(Month([h2.DataInscr])>8,Year([h2.DataInscr]),Year([h2.DataInscr])1))))<>False)) GROUP BY H1.numero,
IIf(Month([h1.DataInscr])>8,Year([h1.DataInscr]),Year([h1.DataInscr])1), H1.CodCurso, H1.AnoCurr, H1.SemesCurr ORDER BY H1.numero,
IIf(Month([h1.DataInscr])>8,Year([h1.DataInscr]),Year([h1.DataInscr])1);
Definidas as expressões, procede-se à criação de um novo package
(Modulo 2) que assegure a transformação. O processo é idêntico ao
descrito para as tabelas das dimensões pelo que não será repetido aqui. O
que se descreve de seguida é, simplesmente, as fases do processo que são
distintas. Nesse caso, inicia-se pela definição da fonte de dados (como
sendo a connection ao Access).
31
Figura 14 : Definição da Query que preenche o primeiro Data Mart
Posteriormente o destino dos resultados das expressões (como sendo
uma tabela na connection ao SQL), a fase seguinte consiste na definição do
destino dos dados recolhidos. Ao passar para o separador Destination,
nesta caixa de diálogo, é proposta a criação de uma tabela nova (uma vez
que a Staging Area se encontra vazia). Aceitando a sugestão da estrutura
para a tabela na Staging Area, altera-se apenas o nome.
32
Figura 15 : Criação da Tabela de Destino dos Dados
De volta à Caixa de mensagem de parametrização de transformações,
teremos criado a estrutura da tabela em função dos campos da origem.
Figura 16 : Estrutura Completa da Tabela de Destino
33
Verificaremos, agora, as correspondências entre campos de origem e
de destino. No separador Transformations. O DTS sugere uma
correspondência directa de cópia.
Figura 17 : Correspondência na passagem Origem-Destino
Neste caso, porque há interesse nisso, mantém-se a configuração
sugerida pelo DTS. No entanto, noutras circunstâncias poder-se-ia definir
expressões de transformação como, por exemplo:
34
Figura 18 : Transformação Personalizada do Número de Aluno
Se fosse necessário transformar o número de Aluno num outro 10
vezes superior. A instrução que faria tal transformação seria:
DTSDestination("AlunoNum") = DTSSource("AlunoNum") * 10
Neste ponto atingiu-se a situação de ter o Package devidamente
configurado para realizar a transformação dos dados Operacionais em
informações
na
Staging
Area.
Basta
executar
seleccionando-a e accionando o botão de execução (
35
a
).
transformação,
BDO Origem
Curso
Hstins e Curric
Disciplina
Hstins e Curric
Ano Lectivo
Hstins
DInscritas
Hstins
DFeitas
Cadfei
Origem
CodCurso
codigo
Ano da (DataInscr)
Contar DataInscr
Contar Nota
Mais uma vez, os elementos a negro terão de ser calculados, ao passo
que os restantes serão apenas copiados. Vai então ser gerada uma tabela
(Reprovacoes) na Staging Area que terá a estrutura constituída pelas linhas
da tabela anterior. O elemento PercRep (percentagem de reprovações), não
será incluído por uma questão facilidade de manipulação no Analysis
Services.
Para proceder à transformação dos dados será necessário recorrer à
seguinte instrução SQL:
SELECT hstins.codigo, hstins.CodCurso,
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)-1) AS AnoLectivo,
Count(hstins.numero) AS DInscritas, Count(cadfei.Nota) AS DFeitas
FROM curric INNER JOIN (hstins LEFT JOIN cadfei ON (hstins.numero =
cadfei.numero) AND (hstins.codigo = cadfei.codigo)) ON (curric.CodCurso = hstins.CodCurso)
AND (curric.codigo = hstins.codigo)
GROUP BY hstins.codigo, hstins.CodCurso,
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)-1)
ORDER BY hstins.codigo, hstins.CodCurso,
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)-1);
Um novo package (Modulo 3) ajudará a transformar e transportar os
dados necessários da base de dados operacional para a Staging Area.
A sua definição encontra-se, resumidamente, nas figuras seguintes.
36
Figura 19 : Definição da Origem(Source) Módulo 3
Figura 20 : Definição do Destino - Módulo 3
37
Figura 21 : Definição da Transformação - Módulo 3
BDO Origem
Origem
Curso
Hstins e Curric
CodCurso
oAnoCur
Hstins
AnoCurr
AnoLectivo
Hstins
Ano da (DataInscr)
Dinscritas
Hstins
Contar DataInscr
Dfeitas
Cadfei
Contar Nota
MédiaNota
Cadfei
Média Nota
RegimeIngresso
Alunos
HabiIngr
Contingente
Alunos
NatuNaci
Com a estrutura anterior fica resolvido também o Módulo 5, que
apesar de não ser requerido a esta altura do trabalho, a facilidade de
obtenção dos dados que permitem executá-lo levou a que se optasse por
desenvolvê-lo já também.
38
Vai então ser gerada uma tabela (Ingressados) na Staging Area que
terá a estrutura constituída pelas linhas da tabela anterior.
Em relação ao Contingente, como não existe na base de dados
operacional um campo que explicite claramente o objectivo, recorreu-se ao
campo da naturalidade do aluno para definir o regime em que terá feito o
seu ingresso no Ensino Superior (ISCTE).
Observando a tabela Tabelas da base de dados operacional, verifica-se
que a forma como o campo NatuNaci é preenchido pela seguinte regra:
tabela
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
B
codigo
04
06
08
10
12
14
16
20
22
24
26
28
30
34
40
44
48
52
54
58
60
62
64
66
68
70
72
74
76
78
80
81
82
83
84
designacao
PORTUGUES
ES
NATURAIS
ACORES
MADEIRA
MACAU
PORTUGUES
ES DAS
PORTUGUES
ES NATURAIS
ARGENTINA
valor1
valor2
ANGOLA
BRASIL
CABO VERDE
GUINE BISSAU
MOCAMBIQU
E
SAO TOME E
PRINCIPE
BELGICA
FRANCA
HOLANDA
ITALIA
REPUBLICA
FEDERAL
DA
REINO UNIDO
AFRICA DO
SUL
VENEZUELA
QUENIA
TIMOR
ZAIRE
ROMENIA
INDIA
CANADA
ESPANHA
FILANDIA
SUÉCIA
ESTADOS
UNIDOS
DA
MARROCOS
MAURITÂNIA
HUNGRIA
POLONIA
39
0
0
0
0
0
0
0
0
tabela
B
B
codigo
85
87
designacao
ESLOVAQUIA
ÁUSTRIA
valor1
valor2
0
0
0
0
Da observação destes dados foi possível constituir a seguinte regra:
04 => Geral
06 => Esp. Açores
08 => Esp. Madeira
10 => Esp. Macau
restantes => Esp. Emigrantes
Relativamente ao Regime de Ingresso (que é o que interessa para este
módulo) estão estabelecidos os seguintes:
Transferências (de outros estabelecimentos) => 6 / J / D / E / F / M
Reingressos => 4 / H
Mudanças de Curso => 7 a 112 / G
Concursos de Acesso => C / A / L / N
Atletas de Alta Competição =>
Oriundos dos PALOP’S => 5 / B / I
Os códigos anteriormente apresentados resultaram de uma observação
da tabela Tabelas da base de dados operacional. É com base nestes códigos
que se dá o preenchimento do campo HabiIngr.
Para proceder à transformação dos dados será necessário recorrer à
seguinte instrução SQL:
SELECT hstins.CodCurso AS Curso, hstins.AnoCurr AS oAnoCur,
IIf(Month([hstins].[DataInscr])>8,Year([hstins].[DataInscr]),Year([hstins].[DataInscr])-1) AS
AnoLectivo, (IIf([NatuNaci]="04","Geral",IIf([NatuNaci]="06","Esp.
Açores",IIf([NatuNaci]="08","Esp. Madeira",IIf([NatuNaci]="10","Esp. Macau","Esp.
Emigrantes"))))) AS Contingente, Count(hstins.numero) AS DInscritas, Count(cadfei.Nota) AS
40
DFeitas, Avg(cadfei.Nota) AS MédiaNota, (IIf([Habiingr]="6" Or [Habiingr]="J" Or
[Habiingr]="D" Or [Habiingr]="E" Or [Habiingr]="F" Or
[Habiingr]="M","Transferências",IIf([Habiingr]="4" Or
[Habiingr]="H","Reingressos",IIf([Habiingr]="G" Or (Val([Habiingr])>=7 And
Val([Habiingr])<=112),"Mudança de Curso",IIf([Habiingr]="C" Or [Habiingr]="A" Or
[Habiingr]="L" Or [Habiingr]="N","Concurso",IIf([Habiingr]="5" Or [Habiingr]="I" Or
[Habiingr]="B","PALOP´S","OUTROS")))))) AS RegimeIngresso
FROM alunos INNER JOIN (hstins LEFT JOIN cadfei ON (hstins.numero =
cadfei.numero) AND (hstins.codigo = cadfei.codigo)) ON alunos.numero = hstins.numero
GROUP BY hstins.CodCurso, hstins.AnoCurr,
IIf(Month([hstins].[DataInscr])>8,Year([hstins].[DataInscr]),Year([hstins].[DataInscr])-1),
(IIf([NatuNaci]="04","Geral",IIf([NatuNaci]="06","Esp. Açores",IIf([NatuNaci]="08","Esp.
Madeira",IIf([NatuNaci]="10","Esp. Macau","Esp. Emigrantes"))))), (IIf([Habiingr]="6" Or
[Habiingr]="J" Or [Habiingr]="D" Or [Habiingr]="E" Or [Habiingr]="F" Or
[Habiingr]="M","Transferências",IIf([Habiingr]="4" Or
[Habiingr]="H","Reingressos",IIf([Habiingr]="G" Or (Val([Habiingr])>=7 And
Val([Habiingr])<=112),"Mudança de Curso",IIf([Habiingr]="C" Or [Habiingr]="A" Or
[Habiingr]="L" Or [Habiingr]="N","Concurso",IIf([Habiingr]="5" Or [Habiingr]="I" Or
[Habiingr]="B","PALOP´S","OUTROS"))))))
ORDER BY hstins.CodCurso, hstins.AnoCurr,
IIf(Month([hstins].[DataInscr])>8,Year([hstins].[DataInscr]),Year([hstins].[DataInscr])-1),
(IIf([NatuNaci]="04","Geral",IIf([NatuNaci]="06","Esp. Açores",IIf([NatuNaci]="08","Esp.
Madeira",IIf([NatuNaci]="10","Esp. Macau","Esp. Emigrantes")))));
Posto isto, será construído um package (Modulo 6) para transformar
os dados da base de dados operacional para a tabela de destino. As figuras
seguintes mostram as definições deste package, relativo ao Módulo 6.
41
Figura 22 : Definição Origem (source) – Módulo 6
Figura 23 : Definição Destino - Módulo 6
42
Figura 24 : Definição Transformação - Módulo 6
ISCTE
BDO Origem
Origem
Curso
Hstins e Curric
CodCurso
Turma
Hstins
Turma
AnoLectivo
Hstins
Ano da (DataInscr)
Disciplina
Hstins
codigo
Ainscritos
Hstins
Contar numero
Aavaliados
Hstins
Contar Notas > 0
Aaprovados
Cadfei
Contar Nota
Ao criar a estrutura anterior, não sendo evidente na Base de Dados
operacional a inferência sobre os alunos que foram avaliados, optou-se por
43
realizar a selecção sobre o campo Nota, assumindo que os alunos avaliados
são todos os que apresentam um valor superior a 0 neste campo.
Assim sendo, a expressão que permite transportar os dados da BD
operacional para a Staging Area é:
SELECT IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)-1)
AS oAnoLectivo, hstins.CodCurso AS oCurso, hstins.codigo AS aDisciplina, hstins.Turma AS
aTurma, Count(hstins.numero) AS Ainscritos, Count(cadfei.Nota) AS Aaprovados, (SELECT
Count(H1.Nota) FROM hstins as H1 Where trim(H1.Turma)=trim(hstins.Turma) and
val(hstins.codigo)=val(H1.codigo) and
IIf(Month([H1.DataInscr])>8,Year([H1.DataInscr]),Year([H1.DataInscr])1)=IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)-1) and
val(H1.codcurso)=val(hstins.codCurso) and H1.Nota>0) AS Aavaliados
FROM hstins LEFT JOIN cadfei ON (hstins.codigo = cadfei.codigo) AND (hstins.numero
= cadfei.numero)
GROUP BY IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)1), hstins.CodCurso, hstins.codigo, hstins.Turma
ORDER BY IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)1), hstins.CodCurso, hstins.codigo, hstins.Turma;
As figuras seguintes mostram a configuração do package (Modulo 9)
que permite criar e carregar os dados da tabela (Rendimento) de suporte a
este módulo.
44
Figura 25: Definição Origem (Source) - Módulo 9
45
Figura 27 : Definição de Transformação - Módulo 9
Ano
BDO Origem
Origem
Aluno
Alunos
Numero
Curso
Alunos
CodCurso
AnoIngresso
Alunos
Ano da (DataIngr)
NotaIngresso
Alunos
NotaIngress
AnoConclusão
Hstins
Ano da (DataInscr)
MediaCurso
Alunos
NotaFinal
MediaIngresso
Alunos
Notaingress
AnosDoCurso
Alunos/Hstins
Conclusão-Ingresso
Mais uma vez os dados disponíveis na Base de dados não são
conclusivos. Neste caso, torna-se impossível pelos dados disponíveis
determinar as notas de ingresso. Dado que, o objectivo deste trabalho em
46
particular, não é primordialmente o rigor, mas sim a construção de um
modelo que permita representar uma ferramenta de visualização de
informação. Assim sendo, optou-se por gerar os dados necessário, ficando
para posteriores fases do trabalho, a substituição dos dados fictícios por
dados reais. Esta situação afecta apenas o campo das notas de ingresso,
sendo todos os restantes verdadeiros e retirados de campos da bases de
dados.
Irá ser utilizada uma fórmula que fará uma série de cálculos gerando
um número entre 10 e 20 (assumindo que só ingressaram alunos com nota
positiva). A fórmula é construída conjugando a naturalidade do aluno e a
sua habilitação de ingresso, numa combinação sem significado intencional.
Em termos de Média de Curso, para evitar cálculos demasiado
complexos a este nível, optou-se pela criação de um membro calculado na
ferramenta OLAP do SQL Server. Assim sendo, a expressão que permite
transportar os dados da BD operacional para a Staging Area não
contemplará este parâmetro, ficando com o seguinte aspecto:
SELECT A1.numero AS Aluno, (select int(avg(year(A2.DataIngr))) from alunos A2 where
A1.numero<A2.numero) AS altano, A1.CodCurso, IIf(round(10+(IIf([Habiingr] Is Not
Null,Asc([HabiIngr]),10)*IIf([naturalidade]
Is
Not
naturalidade<>"",Asc(Left([naturalidade],1)),(IIf([natunaci]
Null
Is
Null,[natunaci],10)*10)))/1000)>20,15,(round(10+(IIf([Habiingr]
Null,Asc([HabiIngr]),10)*IIf([naturalidade]
Is
Not
naturalidade<>"",Asc(Left([naturalidade],1)),(IIf([natunaci]
Null,[natunaci],10)*10)))/1500)))
AS
NotaIngresso,
And
Not
Is
Not
Null
And
Is
IIf([DataIngr]
Not
Is
Null,[altano],Year([DataIngr])) AS AnoIngresso, A1.NotaFinal AS MediaCurso, (Select max(
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr)+1,Year(hstins.DataInscr))) From Hstins
where Hstins.numero=A1.numero and Hstins.codcurso = A1.codcurso) AS AnoConclusao,
[AnoConclusao]-[AnoIngresso] AS AnosdoCurso
FROM alunos AS A1
47
WHERE (((A1.FimCurso)="S"));
que permite criar e carregar os dados da tabela (ComparaES) de suporte a
este módulo.
Figura 28 : Definição Origem (Source) - Módulo 10
48
49
11 – Relação entre as notas de ingresso e as cadeiras relacionadas com
as mesmas consoante o núcleo a que pertencem
Curso
Disciplina
AnoLectivo
MediaDisciplina
MediaIngresso
BDO Origem
Alunos
Hstins
Hstins
Hstins
Alunos
Origem
CodCurso
Codigo
Ano da (DataInscr)
Média(Nota)
Média(Notaingress)
Tal como aconteceu no módulo anterior, também aqui se vai proceder
à
geração
da
Média
de
Ingresso,
pelos
motivos
apresentados
anteriormente. A expressão que permite transportar os dados da BD
operacional para a Staging Area tem o seguinte aspecto:
SELECT hstins.codigo, hstins.CodCurso,
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)-1) AS AnoLectivo,
(SELECT round(avg(h1.Nota)) from hstins as H1 where (H1.codCurso=hstins.codcurso) and
(H1.codigo=hstins.codigo) and
IIf(Month(H1.DataInscr)>8,Year(H1.DataInscr),Year(H1.DataInscr)-1)=
group by H1.codigo, H1.codcurso,
IIf(Month(H1.DataInscr)>8,Year(H1.DataInscr),Year(H1.DataInscr)-1)) AS MediaDisciplina,
(SELECT round(avg(IIf(round(10+(IIf([a1.Habiingr] Is Not
Null,Asc([a1.HabiIngr]),10)*IIf([a1.naturalidade] Is Not Null And
[a1.naturalidade]<>"",Asc(Left([a1.naturalidade],1)),(IIf([a1.natunaci] Is Not
Null,[a1.natunaci],10)*10)))/1000)>20,15,(round(10+(IIf([a1.Habiingr] Is Not
Null,Asc([a1.HabiIngr]),10)*IIf([a1.naturalidade] Is Not Null And
[a1.naturalidade]<>"",Asc(Left([a1.naturalidade],1)),(IIf([a1.natunaci] Is Not
Null,[a1.natunaci],10)*10)))/1500))))) as MediaIngresso from Alunos as A1 group by
A1.CodCurso, IIf(Month(a1.DataInscr)>8,Year(a1.DataInscr),Year(a1.DataInscr)-1) having
A1.CodCurso= hstins.CodCurso and
50
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)1)=IIf(Month(a1.DataInscr)>8,Year(a1.DataInscr),Year(a1.DataInscr)-1)) AS MediaIngresso
FROM alunos INNER JOIN (curric INNER JOIN hstins ON (curric.CodCurso =
hstins.CodCurso) AND (curric.codigo = hstins.codigo)) ON alunos.numero = hstins.numero
GROUP BY hstins.codigo, hstins.CodCurso,
ORDER BY hstins.codigo, hstins.CodCurso,
IIf(Month(hstins.DataInscr)>8,Year(hstins.DataInscr),Year(hstins.DataInscr)-1);
que permite criar e carregar os dados da tabela (CadeIngresso) de suporte a
este módulo.
51
52
BDO Origem
Curso
Hstins
AnoLectivo
Hstins
CodGeograf
Geografia
NAlunos
Alunos
Origem
CodCurso
Ano da (DataIngr)
Concelho
NotaFinal
Este módulo é, talvez o que implica mais trabalho. Isto porque os
campos que constituem a sua essência - identificação geográfica – estão
preenchidos na Base de Dados Operacional de uma forma, perfeitamente,
anárquica. Isto impossibilita que se possa extrair, directamente, os dados
que se pretende.
Para ultrapassar esta dificuldade, é aconselhável separar a tabela dos
Alunos (existente na Base de dados Operacional), nos vários Distritos de
Portugal. Posteriormente, dentro de cada grupo será mais fácil contabilizar.
Continente
Distrito
Concelho
Freguesia
Ilhas
Distrito
Concelho
Freguesia
Nacionais
Estrangeiros Divisão Por Países
Isto será feito recorrendo à tabela de Concelhos e Freguesias obtida a
partir da internet.
De referir ainda a necessidade de tratar os dados das inscrições de
alunos por forma a que seja possível contabilizar apenas um registo de
cada aluno por ano lectivo, independentemente das disciplinas que
frequenta, já que aqui o que importa é o número de alunos por curso e não
o número de inscrições (que é dados pela tabela Hstins da Base de Dados
Operacional). Para agrupar as inscrições dos alunos por ano, criou-se uma
53
consulta (Matriculas) que terá um registo de cada aluno por ano. A
expressão inerente a esta consulta é a seguinte:
SELECT h1.numero, IIf(Month(h1.DataInscr)>8,Year(h1.DataInscr),Year(h1.DataInscr)-1)
AS AnoLectivo, h1.CodCurso
FROM hstins AS h1
GROUP BY h1.numero,
IIf(Month(h1.DataInscr)>8,Year(h1.DataInscr),Year(h1.DataInscr)-1), h1.CodCurso
ORDER BY h1.numero;
O resultado desta consulta servirá para o agrupamento das inscrições
por curso e ano lectivo.
De seguida, fez-se a contagem de alunos por cada Distrito. Depois de
divididos os registos de alunos por grupos em função do Distrito,
procurou-se na consulta Matriculas, todos os alunos inscritos que
pertencem a esse grupo. Em simultâneo, procurou-se identificar mais
precisamente a freguesia a que pertence o aluno, atendendo à coincidência
entre as primeiras 5 primeiras letras da freguesia e concelho
face à
designação de naturalidade do aluno.
Este processo terá que ser feito 18 vezes (já que existem exactamente 18
Distritos em Portugal Continental). Os alunos provenientes das ilhas e de
países estrangeiros serão processados separadamente.
O código seguinte tem como objectivo contabilizar as inscrições de
alunos do Distrito de Aveiro, identificando a sua freguesia, comparando a
sua naturalidade com a designação de Freguesia da tabela retirada da
internet (com todas as Freguesias deste Concelho).
SELECT Matriculas.AnoLectivo, Geografia.Cod_GeografCount(Matriculas.numero) AS
NAlunos, Matriculas.CodCurso
54
FROM (Matriculas INNER JOIN AVEIRO ON Matriculas.numero = AVEIRO.numero)
INNER JOIN Geografia ON ((AVEIRO.naturalidade) Like Left([Geografia].[Freguesia],5)+"*"
and ([AVEIRO].[naturalidade]) Like "*"+Left([Geografia].[Concelho],4)+"*")
WHERE Geografia.Cod_Distrito=1
GROUP BY Matriculas.AnoLectivo, Geografia.Cod_Geograf, Matriculas.CodCurso;
Este código é apenas para contabilizar os alunos do Distrito de Aveiro.
Para os restantes Distritos efectuou-se a mesma consulta, variando as
palavras de “Aveiro” para “Bega” e, na clausula “Where”, em vez de 1
utilizou-se o código 2 (correspondente ao Distrito de Bega, na tabela
geografia retirada da internet). Para os restantes Distritos procedeu-se de
igual forma.
As imagens seguintes demonstram a obtenção dos dados para a
concretização do módulo actual. Os dados serão agrupados na tabela
Origem, na Staging Area.
55
56
Aluno
Curso
AnoLectivo
AnoCurricular
Tipo
CursoAnt
AnoIngresso
NotaIngressoAnt
Ncadeiras
MediaAnt
BDO Origem
Alunos
Alunos
Hstins
Hstins
Alunos
Hstins
Alunos
Mudcur
Hstins
Hstins
Origem
Numero
CodCurso
Ano da (DataInscr)
AnoCurr
HabiIngr
CodCurso
Ano da (DataIngr)
NotaIngress
Count(codigo)
Média(Nota)
Para construir a tabela (Mudança) de factos que suportará este
módulo, foi necessário recorrer à seguinte expressão:
SELECT alunos.numero AS Aluno, alunos.CodCurso AS Curso, hstins.AnoCurr AS
AnoCurricular, IIf(Val([Alunos.HabiIngr])>1 And
Val([Alunos.HabiIngr])<999,"Interno","Externo") AS Tipo, mudcur.CodCurso AS CursoAnt,
IIf(Month([Alunos].[DataIngr])>8,Year([Alunos].[DataIngr]),Year([Alunos].[DataIngr])-1) AS
57
AnoIngresso, (mudcur.NotaIngr/10) AS NotaIngrAnt,
IIf(Month([hstins].[DataInscr])>8,Year([hstins].[DataInscr]),Year([hstins].[DataInscr])-1) AS
AnoLectivo, Count(H1.codigo) AS Disciplinas, Int(Avg(H1.Nota)) AS MédiaNota
FROM (alunos INNER JOIN hstins ON (alunos.numero = hstins.numero) AND
(alunos.CodCurso = hstins.CodCurso)) INNER JOIN (mudcur INNER JOIN hstins AS H1 ON
(mudcur.Numero = H1.numero) AND (mudcur.CodCurso = H1.CodCurso)) ON
alunos.numero = mudcur.Numero
WHERE (((H1.Nota)>0))
GROUP BY alunos.numero, alunos.CodCurso, hstins.AnoCurr, IIf(Val([Alunos.HabiIngr])>1
And Val([Alunos.HabiIngr])<999,"Interno","Externo"), mudcur.CodCurso,
IIf(Month([Alunos].[DataIngr])>8,Year([Alunos].[DataIngr]),Year([Alunos].[DataIngr])-1),
mudcur.NotaIngr,
IIf(Month([hstins].[DataInscr])>8,Year([hstins].[DataInscr]),Year([hstins].[DataInscr])-1);
Com este código assume-se que o campo HabiIngr da tabela Hstins
contém a forma como o aluno acedeu à inscrição. Consultando a tabela
Tabelas, pode verificar-se que, na sua maioria, códigos numéricos indicam
alteração de curso internamente, enquanto que códigos alfabéticos indicam
acesso externo (quer sejam vindos de outras escolas superiores quer
venham de secundárias).
As figuras seguintes mostram a definição do processo de transferência
dos dados entre a Base de Dados operacional e a Staging Area.
Figura 37 : Definição de Origem (Source) - Módulo 17
58
59
Considerações Finais
Em vez de agrupar todos os dados numa só tabela do Data
Wharehouse, optou-se por manter as vária tabelas de factos isoladamente,
por forma a se adaptarem mais facilmente a cada um dos cubos.
Isto faz com que a estrutura se apresente da seguinte forma:
Figura 40 : Esquema de Relações no interior do Data Warehouse
60
7. Implementação da Ferramenta OLAP
Depois de conseguida toda a estrutura de suporte (realizada na secção
anterior), temos a matéria-prima para conseguir realizar o verdadeiro
objectivo deste trabalho – Cubos OLAP.
A ferramenta a utilizar deixa de ser o Enterprise Manager do SQL
Server 2000, para passar a ser o Analysis Manager do mesmo SQL Server
2000.
Após ter acedido ao produto, a primeira operação a realizar é a criação
de uma nova ligação à Staging Area. Só assim será possível criar toda a
estrutura necessária ao suporte dos cubos.
Figura 41 : Ligação do Analysis Manager à Staging Area
O passo seguinte poderia ser a criação directa dos Cubos. No entanto,
dado que é necessário a determinado passo da criação dos cubos referir as
dimensões (e métricas, claro) a contemplar, optou-se por criar desde já
61
todas as dimensões. Este procedimento tem que ver com o facto de se ter
verificado que as dimensões são comuns a vários cubos.
Criação das Dimensões
Pelo que ficou patente na figura 2, serão necessárias 6 dimensões.
Todas elas são comuns a mais do que um cubo, pelo que é útil que se criem
como shared (partilhadas). A pasta Shared Dimensions (Dimensões
partilhadas), permitirá albergar as dimensões que entretanto forem sendo
criadas.
As Seis dimensões a criar são (Temporal, Curso, Disciplina, Geografia,
Curricular e Aluno).
Dimensão Temporal
Esta dimensão será criada sobre a tabela Lectivos da Staging Area,
pois foi o destino dos dados no momento do ETL. A explicação do processo
de criação desta Dimensão será um pouco mais exaustiva por ser a
primeira. As próxima dimensões serão criadas, realçando apenas os
aspectos importantes.
Ao iniciar a criação de uma nova dimensão, é possível optar por ter,
ou não a ajuda de um assistente (Wizard). A utilização do Wizard é sempre
aconselhável para a primeira vez que se inicia uma Dimensão
(independentemente da experiência do operador). Isto porque, desta
forma, tem a certeza que todas as especificações são feitas. Posteriormente,
pode-se proceder às alterações necessárias.
O Assistente desenvolve-se em 7 passos. O primeiro deles é apenas
informativo e pode, inclusive evitar-se ao assinalar a opção Skip this screen
in the future.
62
Figura 42 : Primeiro passo do Assistente de Criação de uma Dimensão
Figura 43 : Selecção do Tipo de Dimensão
63
Das opções à disposição apenas as duas primeiras se adaptam à
dimensão em criação. A diferença entre elas reside no número de tabelas
que serão criadas para a criação da dimensão. No caso presente apenas será
utilizada uma tabela, pelo que a opção mais aconselhada é a primeira.
Figura 44 : Seleção da tabela base da dimensão
Pelo facto de ter sido seleccionada a primeira opção no passo
anterior, neste passo apenas é possível seleccionar uma tabela. A selecção
feita no campo do lado esquerdo já é a que interessa – tabela Lectivos.Do
lado direito aparecem os campos da tabela. Eventualmente, serão os
constituintes dos vários níveis da dimensão (constituindo a hierarquia da
dimensão). Este não é o caso, já que ambos os campos se referem ao mesmo
dado – o ano lectivo.
64
O botão Browse Data… dá uma ideia daquilo que é possível
encontrar dentro da tabela.
Figura 45 : Conteúdo da tabela base da dimensão
65
Figura 46 : Definindo o campo para basear os níveis da Dimensão
Pelo que se pôde observar na figura anterior, apenas interessa o campo
LectivoCompleto, já que o campo Lectivo consiste apenas numa parte do
ano lectivo como é costume referir. Ao optar pelos dois campos obter-se-ia
uma hierarquia como a que se segue:
Figura 47 : hierarquia da dimensão baseada em dois campos
O que se pretende é que a dimensão se apresente da seguinte forma:
66
Figura 48 : Resultado a obter na Dimensão Temporal
Neste passo do processo, a opção Count level members automatically
permite ao programa aperceber-se se, eventualmente, o operador não terá
trocado a ordem pela qual inseriu os níveis na hierarquia. Ao detectar que
um nível contém mais membros do que o seu dependente, o Analysis
Server, informa o operador e sugere a alteração (que, obviamente não é
obrigado a acatar). Para exemplificar, imagine-se que se pretendia para a
dimensão temporal o seguinte:
Figura 49 : Hieraquia completa da dimensão Temporal
67
Em princípio seria necessário um campo para a década que teria por
certo menos membros (Década de 1980, 1990…) do que o nível
imediatamente seguinte (Ano 1990/91, Ano 1991/92 …). Resumindo, há
muito mais anos que décadas. É isto que o programa alerta: o facto de o
operador tentar colocar a década em 1º lugar (já que a hierarquia é
construída de cima para abaixo).
Figura 50 : Definição da Chave de cada nível da hierarquia da dimensão Temporal
Apesar de a designação do nível se apresentar completa (1998/1999,
por exemplo), a chave de cada membro é aconselhável ser apenas o ano
(1998).
68
Figura 51 : Opções Avançadas da Dimensão Temporal
Figura 52 : Conclusão da criação da Dimensão Temporal
69
Chegados ao último passo par a criação da dimensão, resta dar-lhe um
nome e concluir.
Feito isto, surge o desafio de verificar como acrescentar a década à
hierarquia.
Assim que foi accionado o botão Finish, o assistente de criação da
dimensão concluiu a sua tarefa. De regresso ao ambiente de trabalho do
Analysis Manager, é necessário aceder ao editor de Dimensões para
modificar a dimensão temporal - Anos. A tecla direita do rato sobre a
dimensão a alterar e a opção Edit…, permitem chegar lá.
Verifica-se que apenas existe um nível – Lectivo Completo. O botão
serve para acrescentar novos níveis. Ao accionar este botão surge uma
nova caixa de diálogo onde será necessário seleccionar o campo sobre o
qual se baseia o nível. Neste caso, é aconselhável que seja o campo Lectivo,
pois é mais fácil saber a que década pertence.
Depois de definido o campo retorna ao editor e o nível que deveria ser
a década (agora acrescentado), encontra-se por baixo do ano lectivo (o que
não faz sentido). Por isso, vai ser necessário arrastar o nível de cima
(Lectivo Completo) para baixo.
Será agora necessário fazer algumas alterações para que finalmente se
obtenha:
70
Figura 53 : Definição completa da dimensão Temporal
De realçar nesta figura apenas o campo Member Key Column e
Member Name Column.
Dimensão Curso
Figura 54 : Definição da Dimensão Curso
De referir que, para obter Todos Cursos, em vez de All Cursos, é
necessário alterar a designação no campo All Caption, em Advanced
(quando o nível dimensão
está seleccionado).
71
Dimensão Geografia
Figura 55 : Definição da Dimensão Geografica
72
Figura 56 : Definição da Dimensão Curricular
Dimensão Aluno
Figura 57 : Definição da Dimensão Aluno
Qualquer alteração que se faça à dimensão é aconselhável proceder ao
processamento da dimensão. O botão a utilizar é
.
Criação dos Cubos
Dado que o objectivo do trabalho é baseado em 8 módulos, seria de
esperar que se procedesse à criação de um número equivalente de cubos.
73
Este módulo (cujo cubo tomará a designação de Inscrições) envolve 4
dimensões (Cursos, Curricular, Aluno e Anos) e 4 métricas (Dinscritas,
Dfeitas, Drepetidas e InsAcumuladas).
Tal como foi feito para as dimensões também para a criação dos cubos
se aconselha a utilização do Assistente (Wizard). Utilizando esta via, são
necessários apenas 5 passos (se tudo correr bem) para ter um cubo
completo com sucesso. Vejamos:
Figura 58 : Primeiro passa na criação do cubo do Módulo 2
Este passo, pode ser facilmente anulado em futuras criações de cubos,
se a opção Skip this screen in the future, for assinalada.
74
O primeiro passo efectivo consiste na definição da tabela de factos
(fact table) que será a base do cubo.
Figura 59 : Definição da Fact Table do Módulo 2
75
Figura 60 : DEfinição de Métricas a representar no Cubo do Módulo 2
Todos os campos numéricos da tabela de factos que não for
considerado Métrica, em princípio será considerado como base para uma
dimensão. Na figura anterior é possível verificar que não está o campo
CodCurso. Isto explica-se pelo facto de não ser um campo numérico, logo
não poderia ser uma Métrica.
76
Figura 61 : Selecção das Dimensões a incluir no cubo do Módulo 2
À partida, neste passo é disponibilizada a lista de Dimensões
partilhadas (shared dimensions). No entanto, se se pretendesse acrescentar
uma dimensão apenas para este cubo, seria neste passo que teria de ser
feito. Obviamente que, a qualquer momento, podem ser acrescentadas
dimensões e métricas aos cubos. Nesse, como em muitos outros aspectos, o
Analysis Manager é bastante versátil.
À questão seguinte será respondido que Sim, ou seja, os itens podem
ser contados neste momento.
77
Figura 62 : Aviso de incoerência no interior do cubo
Esta informação pode ocorrer no caso de os campos que deviam ligar a
tabela de factos e as dimensões não terem nomes iguais. O Analysis
Manager deixará que esta relação se faça manualmente, mais tarde.
Figura 63 : Conclusão da Criação do Cubo do Módulo 2
78
Para terminar a criação do cubo falta apenas atribuir-lhe um nome.
Antes de concluir definitivamente, falta apenas definir a relação entre a
tabela de factos e as dimensões presentes no cubo.
Figura 64 : Configuração do Cubo do Módulo 2
Agora, sim, o cubo está pronto para ser gravado.
Figura 65 : Aviso da necessidade de definir o Armazenamento de dados
79
A esta questão pode responder-se que Não (No), pois trata-se de
questões de optimização.
Este módulo (cujo cubo tomará a designação de Reprovações) envolve
3 dimensões (Cursos, Disciplinas e Anos) e 3 métricas (Dinscritas, Dfeitas,
Percentagem).
Este cubo tem uma particularidade muito interessante, decorrente da
necessidade de incluir uma percentagem. Como será apresentado na
Navegação, os agrupamentos de dados relativos aos membros da
hierarquia da Dimensão, assume como função de agregação uma de 5
funções (Sum, Max, Min, Count e DistinctCount). Dá para perceber que
não é possível afixar a percentagem de um grupo de valores (por exemplo,
a percentagem de reprovações referentes ao ano de 1995). A soma de
percentagens desvirtua completamente os valores reais.
Para ultrapassar este problema, o Analysis Manager dá a possibilidade
de criar membros calculados (calculated members).
A expressão a incluir na Percentagem é clara:
([Measures].[Dinscritas]-[Measures].[Dfeitas])/[Measures].[Dinscritas]
80
Este módulo envolve 6 dimensões (Cursos e Anos, das que são
comuns a outros cubos, Ano Curricular, Regime de Acesso e Contingente,
específicas deste cubo) e 3 métricas (Dinscritas, Dfeitas, Média).
As dimensões específicas foram criadas com base em campos incluídos
na tabela de factos. Em vez de se criarem códigos para o Regime de
Ingresso do aluno e para o Contingente, optou-se por incluir as
designações directamente na tabela de factos. Este é um procedimento
pouco recomendado, teoricamente. No entanto, para verificar a
versatilidade do Analysis Manager, foi esta a opção.
81
O facto de existir uma métrica que se baseia na função Média - Média
das Disciplinas -, levanta problemas na agregação. Para ultrapassar este
problema, não se poderá utilizar uma métrica normal. É necessário criar
um membro calculado (Calculated member).
Para que este membro calculado seja possível, é necessário inserir duas
métricas novas (Valor e Unidades) que têm como objectivo somar os
valores de todas as notas para um período, curso ou ano curricular, etc. e
contar todas as notas somadas. A Média (membro calculado) envolverá
estes parâmetros da seguinte forma:
[Measures].[Valor]/[Measures].[Unidades]
82
ISCTE
Este módulo (cujo cubo tomará a designação de Rendimento) envolve
4 dimensões (Cursos, Anos, Disciplinas e Turma) e 3 métricas (Ainscritos,
Aavaliados, Aaprovados).
A dimensão Turma é específica para este cubo, pelo que foi criada no
âmbito do mesmo e não partilhada. O campo da tabela de factos que está
na base desta dimensão é, como é óbvio, Turma.
83
Este módulo (cujo cubo tomará a designação de Comparar Entrada
Saída) envolve 3 dimensões (Cursos, Aluno e Anos, das comuns a outros
cubos e AnosdoCurso específico deste cubo) e 3 métricas (MédiaCurso,
MédiaIngresso TotalAlunos).
84
A dimensão Duração é específica para este cubo, pelo que foi criada no
âmbito do mesmo e não partilhada. O campo da tabela de factos que está
na base desta dimensão é AnosDoCurso.
Por se tratar de um número relativamente reduzido de registos, optouse por utilizar membros calculados (Calculated Members), em vez de
armazenar algumas métricas. Assim, a Média da Notas de Fim de Curso e
a Média das Notas de Ingresso, por serem médias (e por não ser possível
agregar directamente médias nas métricas), serão calculadas em tempo real
através de membros calculados.
Para não criar grande confusão ao utilizador, serão ocultadas algumas
métricas, deixando visíveis apenas as que fazem parte dos requisitos.
A métrica TotalAlunos foi acrescentada ao cubo, baseando-a no
campo número de aluno. Desta forma, definindo a função de agregação
como contagem, obtém-se a contagem de alunos.
11 – Relação entre as notas de ingresso e as cadeiras relacionadas com
as mesmas consoante o núcleo a que pertencem
Este módulo (cujo cubo tomará a designação de Comparar Entrada
Departamento) envolve 3 dimensões (Cursos, Anos e Disciplina) e 2
métricas (MédiaCurso e MédiaIngresso).
85
Por se tratar de um número relativamente reduzido de registos, optouse por utilizar membros calculados (Calculated Members), em vez de
armazenar algumas métricas. Assim, a Média da Notas das Disciplinas e a
Média das Notas de Ingresso, por serem médias (e por não ser possível
agregar directamente médias nas métricas), serão calculadas em tempo real
através de membros calculados.
Para não criar grande confusão ao utilizador, serão ocultadas algumas
métricas, deixando visíveis apenas as que fazem parte dos requisitos.
86
Este módulo (cujo cubo tomará a designação de Origem) envolve 3
dimensões (Cursos, Anos e Geografia, que apesar de ser específica deste
cubo foi criada como dimensão partilhada) e apenas uma métrica
(NAlunos).
Este módulo (cujo cubo tomará a designação de Mudança Curso)
envolve 7 dimensões (Aluno, Cursos, AnoCurricular, TipoAcesso,
AnoLectivo, CursoAntigo, AnoIngresso) e 3 métricas (MédiaNota,
Ndisciplinas, MédiaIngresso).
87
Neste cubo, à semelhança do que se passou em cubos anteriores,
optou-se por utilizar membros calculados (Calculated Members), em vez
de armazenar algumas métricas. Isto devido à dificuldade em proceder à
personalização do agrupamento das métricas do cubo. Assim, a Média de
Curso e a Média das Notas de Ingresso no Curso Anterior, por serem
médias, serão calculadas em tempo real através de membros calculados.
88
As métricas que servem de base ao cálculo destes novos membros,
serão ocultadas, deixando visíveis apenas as que fazem parte dos
requisitos.
89
8. Navegação
Desenvolvida que está a ferramenta, resta agora experimentar o efeito,
por forma a perceber se resultou ou não.
Se por todo o texto está referida a excelência do Analysis Manager e do
Enterprise Manager, este é um capítulo onde, lamentavelmente, não
poderá ficar esse testemunho. De facto, os interfaces de consulta aos cubos
criados, são tudo menos agradáveis. As limitações são mais do que
evidentes, como será mostrado.
No entanto, a facilidade com que se constrói um “cliente” (aplicação
que pode absorver o trabalho feito no SQL Server 2000 e mostrar noutra
forma mais agradável), quase que serve de desculpa.
Neste capítulo será mostrado o interface do SQL Server para a
visualização de cubos e também a ligação a outros possíveis clientes.
Utilizando o SQL Server
Existem dois momento em que se pode consultar as informações
geradas pelos cubos. Em ambos, o ecrã tem um conjunto bastante limitado
de funcionalidades.
Logo no momento em que se está a criar o cubo, é possível espreitar o
resultado que se irá obter (antes de visualizar, convém não esquecer de
processar o cubo, pois, de outra forma, as informações obtidas são um
exemplo gerado pelo Analysis Manager).
90
Figura 73 : Visualização das Informações do Cubo do Momento da Criação
As limitações são de tal forma que apenas é possível ver ao mesmo
tempo aquilo que a janela onde está a decorrer a visualização permitir. Não
é possível alargar ou estreitar as colunas, nem seleccionar um intervalo de
valores ad hoc para ver.
As únicas operações possíveis limitam-se ao Drill down/up e à
escolha de dimensões representadas e de membros dentro das dimensões.
91
Figura 74 : Drill Down ao 1º Ano da Dimensão Ano do Cubo Inscrições
Figura 75 : Visualização da Dimensão Ano Lectivo do Cubo Inscrições
92
Figura 76 : Selecção de Valores na Dimensão Curso do Cubo Inscrições
Outra possibilidade de visualizar as informações de um cubo, torna-se
viável após a sua criação e, a partir da consola do Analysis Manager,
seleccionando a opção Browse Data, disponível no menu Acção (depois de
seleccionar o cubo que se pretende consultar.
Esta modalidade de consulta é em tudo idêntica à anteriormente
descrita, diferindo apenas na dimensão da área disponibilizada para a
visualização. Nesta modalidade, o espaço ocupado pelas zonas de
configuração do cubo ficam disponíveis para a consulta de informação.
Utilizando Outros Clientes
Para suprir as dificuldades de visualização do Analysis Manager,
algumas das ferramentas do Microsoft Office disponibilizam uma ligação
93
dinâmica à informação contida nos cubos OLAP. Desta forma, reúnem-se
as potencialidades de criação de informação do Analysis Manager com a
capacidade de visualização dessas ferramentas.
Vejamos alguns exemplos.
Microsoft Excel 2000
Dadas as semelhanças, ao nível da visualização, dos cubos OLAP no
Analysis Manager e as Tabelas Dinâmicas no Microsoft Excel, é possível
fazer utilização deste último conceito para apresentar os dados do
primeiro.
O Processo de criação de um cliente OLAP no Microsoft Excel é tão
simples como criar uma Tabela Dinâmica baseada noutro tipo de fonte de
dados que se deseje.
A opção Relatório de tabelas e de gráficos dinâmicos..., do menu
Dados, é o ponto de partida. O ecrã que surge é o que se pode ver na figura
seguinte.
Figura 77 : Início da criação de um Cliente OLAP em Excel
94
Como se pode verificar, apenas são necessários 3 passos para dar por
concluída a tarefa. Isto se já estivesse tudo preparado, pois, como iremos
ver, existe um conjunto de procedimentos conexos a executar antes de
chegar ao 3, e último, passo.
As opções estão à vista, para quê explicar? O botão seguinte conduz ao
passo 2.
Figura 78 : Segundo passo da criação de um Cliente OLAP em Excel
Este segundo ecrã, pequeno na dimensão, mas grande na função,
permite ligar a origem dos dados à folha Excel. Como ainda não se definiu
nenhuma ligação à folha actual, será necessário Obter dados...
Figura 79 : Criação de uma ligação do Cliente OLAP em Excel aos dados
95
Uma vez que não existem origens de dados, será necessário criar uma.
O botão OK faz isso, se for pressionado.
Figura 80 : Características do Cliente OLAP na ligação aos dados
Para que se possa ligar definitivamente, para além dos dados do lado
do cliente (Nome e tipo de ligação), também é necessário definir as
características do lado do servidor. O botão ligar permite fazê-lo.
96
Figura 81 : Características do Servidor OLAP na ligação ao Excel
Figura 82 : Características do Servidor OLAP na ligação ao Excel
97
De volta ao ecrã de ligação, já é possível associar a componente
servidora à componente cliente. Como se pode ver pela figura seguinte,
define-se, inclusive, o cubo a representar no Cliente OLAP (no Excel).
Figura 83 : Definição completa da ligação Cliente/Servidor OLAP
O botão Ok conduz à escolha da origem de dados que, agora já tem
uma nova opção
Figura 84 : Escolha da Origem dos Dados
98
De novo o botão OK conduz ao passo 2 do processo. Agora a origem
dos dados está definida e, portanto, o objectivo deste passo está concluída.
Passando ao passo seguinte, apenas é pedido o local onde se pretende
inserir os dados obtidos do servidor OLAP.
Figura 85 : Definição do destino dos dados do cubo
O botão Esquema... permite, desde logo, definir a estrutura da tabela
dinâmica de consulta aos dados do cubo OLAP.
99
Figura 86 : Definição do esquema de visualização dos dados no Cliente OLAP
100
Figura 87 : Visualização dos dados no Cliente OLAP em Excel
As potencialidades de cliente começam agora. Desde a formatação dos
tipos de letra, das células (compreendendo o alargamento das colunas,
etc.), à troca de dimensões e métricas, tudo pode ser feito. É ainda possível,
com um simples clique no rato, construir um gráfico.
101
Figura 88 : Gráfico dos dados no Cliente OLAP em Excel
Microsoft Front Page 2000
Quando se fala em Front Page, fala-se, obviamente, em páginas html.
A criação de uma página ligada a uma fonte de dados OLAP é
possível e, relativamente, fácil. Com o processo que se descreve de seguida,
consegue-se uma página dinâmica, que pode ser consultada através de um
qualquer browser.
O processo inicia-se com a inclusão numa página html normal de um
novo componente (Insert > Component > Office PivotTable).
Figura 89 : Objecto PivotTable no Cliente OLAP em HTML
102
O erro é claro! Será necessário ligar o objecto a uma fonte de dados. O
terceiro botão contando da direita para a esquerda da barra de ferramentas,
disponibiliza uma caixa de ferramentas que ajudará a conseguir o
objectivo.
Figura 90 : Caixa de Ferramentas de propriedades do objecto Cliente OLAP em HTML
O Editor de Ligações irá dar uma ajuda na definição do comando de
ligação entre Cliente OLAP e Servidor OLAP.
103
Figura 91 : Definição das Características do Servidor OLAP
Figura 92 : Definição das propriedades de Ligação ao Servidor OLAP
104
Testada e bem sucedida a comunicação, está encontrado o caminho
para os dados. O passo seguinte é a definição do cubo que se pretende
visualizar no objecto PivotTable do Html.
Figura 93 : Selecção do Cubo OLAP a apresentar no Objecto PivotTable em HTML
Assim que for seleccionado, no Objecto PivotTable já aparece o nome
do cubo OLAP. Para visualizar os campos disponibilizados pelo Cubo e
poder integrá-los no objecto PivotTable, accione o segundo botão (
)
(contando da direita para a esquerda) na barra de ferramentas do objecto
PivotTable. Posto isto, é uma questão de seleccionar cada um dos campos e
adicionar (sem arrastar) ao elemento da PivotTable onde se pretende
colocar.
105
Figura 94 : Adcionar Dimensões e Métricas ao Objecto PivotTable em HTML
Uma vez concluído o processo, o aspecto do objecto (tendo por
objectivo a construção de um objecto igual ao exemplo apresentado em
Excel) terá o seguinte aspecto.
Figura 95 : Objecto PivotTable em HTML configurado
Para filtrar, será necessário apenas arrastar os campos para a zona
superior do objecto. Todas as funcionalidades enumeradas para a Tabela
Dinâmica do Excel são válidas para este objecto.
106
9. Conclusão
Muitas horas depois de se ter iniciado a implementação desta
ferramenta OLAP,
está por fim concluída. As muitas dificuldades
encontradas só foram ultrapassadas graças à versatilidade do software
utilizado – Microsoft SQL Server 2000 Enterprise Edition.
Pela realização deste trabalho e pelos resultados obtidos é possível
concluir que a situação em que se encontram as Bases de dados
Operacionais influencia directamente o tempo de duração do ETL. Apesar
de se ter utilizado uma ferramenta bastante poderosa, esta pouco ou nada
pode fazer perante inconsistência. Fica também provado que, na
construção de uma ferramenta de gestão como a que se pretendia, a fase
inicial de análise e concepção, juntamente com a fase de ETL, consomem
mais de 90% do tempo do projecto. Uma vez obtido um Data Warehouse
em condições, a construção dos Cubos OLAP é uma tarefa bastante simples
no SQL Server 2000.
Por último, é possível confirmar mais uma vez que as regras teóricas
nem sempre têm de ser uma obsessão para quem desenvolve ferramentas
do género. Em cada momento, há que avaliar os problemas a resolver,
analisar as alternativas e, circunstancialmente, decidir pelo melhor.
Em termos pessoais, depois de ter frequentado o curso “Designing and
Implementing OLAP Solutions with Microsoft SQL Server 2000”, esta foi uma boa
oportunidade para colocar em prática os conhecimentos adquiridos.
107
10. Futuros Trabalhos
Para além de ferramentas OLAP, o Microsoft SQL Server 2000 também
permite criar “Business Intelligence”, utilizando técnicas de Data Mining.
Em trabalhos futuros, poderiam ser utilizadas técnicas como as árvores de
decisão (decision trees), ou técnicas de segmentação (clustering), por forma
a obter novos conhecimentos sobre os dados armazenados no
DataWarehouse criado neste trabalho.
108
11. Índice Fotografias
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
2 : Estrela do Data Warehouse com as dimensões e as métricas ...................................... 17
3: Ambiente de trabalho do Enterprise Manager.............................................................. 21
4 : Ambiente de trabalho do DTS ..................................................................................... 22
5 : Identificação do Ficheiro correspondente à BD Operacional...................................... 23
6 : Criação de um package – Ligação à BD Operacional................................................. 23
7 : Definição da ligação à Staging Area - SIAD............................................................... 24
8 : Definição da transformação ......................................................................................... 25
9 : Editor de Queries ......................................................................................................... 26
10 : Definição da Instrução SQL da Dimensão Temporal................................................ 27
11 : Definição da Estrutura da Dimensão Temporal......................................................... 27
12 : Transformações Origem/Destino da Dimensão Temporal ........................................ 28
13 : Resultado da Execução do Package ........................................................................... 29
14 : Definição da Query que preenche o primeiro Data Mart .......................................... 32
15 : Criação da Tabela de Destino dos Dados .................................................................. 33
16 : Estrutura Completa da Tabela de Destino ................................................................. 33
17 : Correspondência na passagem Origem-Destino ........................................................ 34
18 : Transformação Personalizada do Número de Aluno ................................................. 35
19 : Definição da Origem(Source) Módulo 3 ................................................................... 37
20 : Definição do Destino - Módulo 3 .............................................................................. 37
21 : Definição da Transfo rmação - Módulo 3................................................................... 38
22 : Definição Origem (source) – Módulo 6..................................................................... 42
23 : Definição Destino - Módulo 6 ................................................................................... 42
24 : Definição Transformação - Módulo 6 ....................................................................... 43
25: Definição Origem (Source) - Módulo 9...................................................................... 45
26 : Definição Destino - Módulo 9 ................................................................................... 45
27 : Definição de Transformação - Módulo 9................................................................... 46
28 : Definição Origem (Source) - Módulo 10................................................................... 48
29 : Definição Destino - Módulo 10 ................................................................................. 49
30 : Definição de Transformação - Módulo 10................................................................. 49
31 : Definição Origem (Source) - Módulo 11.................................................................. 51
33 : Definição de Transformação - Módulo 11................................................................ 52
34 : Definição Origem (Source) - Módulo 14................................................................... 55
37 : Definição de Origem (Source) - Módulo 17.............................................................. 58
40 : Esquema de Relações no interior do Data Warehouse .............................................. 60
41 : Ligação do Analysis Manager à Staging Area .......................................................... 61
42 : Primeiro passo do Assistente de Criação de uma Dimensão ..................................... 63
43 : Selecção do Tipo de Dimensão.................................................................................. 63
44 : Seleção da tabela base da dimensão .......................................................................... 64
45 : Conteúdo da tabela base da dimensão ....................................................................... 65
46 : Definindo o campo para basear os níveis da Dimensão ............................................ 66
47 : hierarquia da dimensão baseada em dois campos...................................................... 66
109
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
Figura
48 : Resultado a obter na Dimensão Temporal................................................................. 67
49 : Hieraquia completa da dimensão Temporal .............................................................. 67
50 : Definição da Chave de cada nível da hierarquia da dimensão Temporal.................. 68
51 : Opções Avançadas da Dimensão Temporal .............................................................. 69
52 : Conclusão da criação da Dimensão Temporal........................................................... 69
53 : Definição completa da dimensão Temporal .............................................................. 71
54 : Definição da Dimensão Curso ................................................................................... 71
55 : Definição da Dimensão Geografica ........................................................................... 72
56 : Definição da Dimensão Curricular ............................................................................ 73
57 : Definição da Dimensão Aluno ................................................................................... 73
58 : Primeiro passa na criação do cubo do Módulo 2 ....................................................... 74
59 : Definição da Fact Table do Módulo 2 ....................................................................... 75
60 : DEfinição de Métricas a representar no Cubo do Módulo 2 ..................................... 76
61 : Selecção das Dimensões a incluir no cubo do Módulo 2 .......................................... 77
62 : Aviso de incoerência no interior do cubo .................................................................. 78
63 : Conclusão da Criação do Cubo do Módulo 2 ............................................................ 78
64 : Configuração do Cubo do Módulo 2 ......................................................................... 79
65 : Aviso da necessidade de definir o Armazenamento de dados ................................... 79
68 : Configuração do Cubo do Módulo 9 ........................................................................ 83
69 : Configuração do Cubo do Módulo 10 ...................................................................... 84
73 : Visualização das Informações do Cubo do Momento da Criação ............................ 91
74 : Drill Down ao 1º Ano da Dimensão Ano do Cubo Inscrições ................................. 92
75 : Visualização da Dimensão Ano Lectivo do Cubo Inscrições................................... 92
76 : Selecção de Valores na Dimensão Curso do Cubo Inscrições ................................. 93
77 : Início da criação de um Cliente OLAP em Excel..................................................... 94
78 : Segundo passo da criação de um Cliente OLAP em Excel ...................................... 95
79 : Criação de uma ligação do Cliente OLAP em Excel aos dados............................... 95
80 : Características do Cliente OLAP na ligação aos dados............................................ 96
81 : Características do Servidor OLAP na ligação ao Excel ........................................... 97
82 : Características do Servidor OLAP na ligação ao Excel ........................................... 97
83 : Definição completa da ligação Cliente/Servidor OLAP ........................................... 98
84 : Escolha da Origem dos Dados .................................................................................. 98
85 : Definição do destino dos dados do cubo .................................................................. 99
86 : Definição do esquema de visualização dos dados no Cliente OLAP ..................... 100
87 : Visualização dos dados no Cliente OLAP em Excel.............................................. 101
88 : Gráfico dos dados no Cliente OLAP em Excel ...................................................... 102
89 : Objecto PivotTable no Cliente OLAP em HTML.................................................. 102
90 : Caixa de Ferramentas de propriedades do objecto Clie nte OLAP em HTML....... 103
91 : Definição das Características do Servidor OLAP .................................................. 104
92 : Definição das propriedades de Ligação ao Servidor OLAP ................................... 104
93 : Selecção do Cubo OLAP a apresentar no Objecto PivotTable em HTML ............ 105
94 : Adcionar Dimensões e Métricas ao Objecto PivotTable em HTML...................... 106
95 : Objecto PivotTable em HTML configurado........................................................... 106
110
12. Bibliografia e outras fontes de informação
Dhar, Vasant & Stein, Roger ; “Intelligent Decision Support Methods – The science of
knowledge work”, Prentice Hall, 1997;
Documento “Estudo do Perfil de Alunos do ISCTE”;
Documento “Descrição dos módulos a implementar a curto prazo”;
Documentação do curso “Designing and Implementing OLAP Solutions with Microsoft
SQL Server 2000”
http://infoline.ine.pt/inf/prodserv/nomenclaturas/refter/nuts.htm
111

Individual

Transcrição

Documentos relacionados

1) Atualmente, segundo Tofler estamos vivenciando a Sociedade do

talvez - Ever After High

Apostila Método VHLS

Inteligência na investigação pessoal e patrimonial

Relatório Individual Sumarizado para bolsa Fapesp de Treinamento

programa preliminar

Folder - EMBRAPII Eletrônicos de Consumo

Web OLAP, Exploração de Sistemas OLAP via Web

Uma Ferramenta Web para BI focada no Gestor de - (CPD)

TCC - FINAL Edson Maia - Sistemas de Informação