Exemplo-de-Motiva‡Æo

UFPB – UNIVERSIDADE FEDERAL DA PARAÍBA.
CENTRO DE CIÊNCIAS E TECNOLOGIA.
DEPARTAMENTO DE SISTEMAS E COMPUTAÇÃO.
CURSO DE MESTRADO EM INFORMÁTICA.
DISCIPLINA: BANCO DE DADOS MULTIDIMENSIONAL
PROFESSOR: MARCOS SAMPAIO
PROJETO DA DISCIPLINA
Projeto de um Sistema DWing para uma Empresa
de Consultoria Fictícia
Campina Grande – Abril de 2002
ALEX SANDRO DA CUNHA RÊGO
ELOI ROCHA NETO
ELY WAGNER AGUIAR
Alunos do curso de Pós-Graduação em Informática da
Universidade Federal da Paraíba – UFPB
Projeto de um Sistema DWing para
uma Empresa de Consultoria Fictícia
Trabalho
que
apresentamos
ao
Professor
Marcus Sampaio, da disciplina Banco de Dados
Multidimensional, referente à segunda etapa do
projeto.
Campina Grande – Maio de 2002
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
2
Í N D I C E
A N A L Í T I C O
1.
INTRODUÇÃO ............................................................................................... 4
2.
ESTRELA BÁSICA ........................................................................................ 5
3.
CONSULTAS SQL CORRESPONDENTE ÀS QUERY PATTERNS ...... 6
4.
QUERY PATTERNS ...................................................................................... 6
4.1
4.2
4.3
4.4
4.5
5.
ANÁLISE COMPARATIVA DE CUSTOS E FATURAMENTO ENTRE CLIENTES AO
LONGO DO TEMPO ............................................................................................... 6
FATURAMENTO ASSOCIADO COM DIFERENTES TIPOS DE SERVIÇO DE
CONSULTORIA ..................................................................................................... 6
CLIENTES MAIS RENTÁVEIS................................................................................. 7
ÍNDICE DE UTILIZAÇÃO DOS CONSULTORES......................................................... 8
RENTABILIDADE DE PROJETOS ESPECÍFICOS ....................................................... 8
OTIMIZAÇÃO DE CONSULTAS .............................................................. 10
5.1
5.2
5.3
CÁLCULO DO TAMANHO DAS TABELAS ............................................................. 10
CRIAÇÃO DE ESTRELAS SECUNDÁRIAS ............................................................. 11
ANÁLISE DE ESPARCIDADE ............................................................................... 12
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
3
1. INTRODUÇÃO
Este trabalho apresenta a segunda etapa do projeto de um Data Warehouse para uma
empresa fictícia de consultoria. Nele são mostradas as consultas SQL das Query Patterns, as
estrelas secundárias e uma análise de esparcidade das tabelas de fato primária e secundárias.
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
4
2. ESTRELA BÁSICA
TEMPO
#cod_tempo
hora
data
dia_mês
semana
mês
ano
LANCAMENTO
#cod_tempo
#cod_consultor
#cod_atividade
num_horas
custo
lucro
faturamento
CONSULTOR
#cod_consultor
nome_consultor
ATIVIDADE
#cod_atividade
tipo_atividade
nome_cliente
tipo_servico
nome_projeto
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
5
3. CONSULTAS SQL CORRESPONDENTE ÀS QUERY
PATTERNS
4. QUERY PATTERNS
4.1
ANÁLISE COMPARATIVA DE CUSTOS E
FATURAMENTO ENTRE CLIENTES AO LONGO DO
TEMPO
SELECT
T.semana,
T.mes,
T.ano,
A.nome_cliente,
SUM(L.custo),
SUM(L.faturamento)
FROM
Lancamento AS L,
Tempo AS T,
Atividade AS A
WHERE
L.cod_tempo = T.cod_tempo AND
L.cod_atividade = A.cod_atividade AND
T.data BETWEEN ? AND ?
CUBE BY
T.semana,
T.mes,
T.ano,
A.nome_cliente
4.2
FATURAMENTO ASSOCIADO COM
DE SERVIÇO DE CONSULTORIA
DIFERENTES
TIPOS
SELECT
T.semana,
T.mes,
T.ano,
A.tipo_servico,
SUM(L.faturamento)
FROM
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
6
Tempo AS T,
Lancamento AS L,
Atividade AS A
WHERE
L.cod_atividade = A.cod_atividade AND
T.data BETWEEN ? AND ?
CUBE BY
T.semana,
T.mes,
T.ano,
A.tipo_servico
4.3
CLIENTES MAIS
RENTÁVEIS
SELECT
T.semana,
T.mes,
T.ano,
A.nome_cliente,
SUM(L.lucro) AS lucro_total
FROM
Tempo AS T,
Lancamento AS L,
Atividade AS A
WHERE
T.cod_tempo = L.cod_tempo AND
L.cod_atividade = A.cod_atividade AND
T.data BETWEEN ? AND ?
CUBE BY
T.semana,
T.mes,
T.ano,
A.nome_cliente
ORDER BY
lucro_total DESC LIMIT 10
Consideramos que seja do interesse do usuário visualizar os 10 clientes mais rentáveis.
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
7
4.4
ÍNDICE DE
UTILIZAÇÃO
DOS CONSULTORES
SELECT
T.semana,
T.mes,
T.ano,
A.nome_consultor,
SUM(L.num_horas) AS total_horas
FROM
Tempo AS T,
Lancamento AS L,
Consultor AS C
WHERE
T.cod_tempo = L.cod_tempo AND
L.cod_consultor = C.cod_consultor AND
T.data BETWEEN ? AND ?
CUBE BY
T.semana,
T.mes,
T.ano,
A.nome_consultor
ORDER BY
total_horas DESC
4.5
RENTABILIDADE
DE PROJETOS
ESPECÍFICOS
SELECT
T.semana,
T.mes,
T.ano,
A.nome_projeto,
SUM(L.lucro) AS lucro_total
FROM
Lancamento AS L,
Atividade AS A,
Tempo AS T
WHERE
T.cod_tempo = L.cod_tempo AND
L.cod_atividade = A.cod_atividade AND
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
8
A.nome_projeto = ? AND
T.data BETWEEN ? AND ?
CUBE BY
T.semana,
T.mes,
T.ano,
A.nome_projeto
ORDER BY
lucro_total DESC
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
9
5. OTIMIZAÇÃO DE CONSULTAS
5.1
CÁLCULO DO TAMANHO DAS
TABELAS
Para otimizar as consultas ao Data Warehouse, é necessário conhecer o tamanho das tabelas
de dimensão e suas consultas mais freqüentes. Consideramos que a empresa está em
operação há 40 anos e que neste período tenha tido 100 consultores, 100 atividades, 3 tipos de
serviço, 50 clientes e 150 projetos. Calculamos o tamanho máximo possível de cada uma das
tabela a seguir:
TEMPO
24 hs x 365 dias x 40 anos = 350.400 linhas
CONSULTOR
100 linhas
ATIVIDADE
100 linhas
LANÇAMENTO
350.400 x 100 x 100 = 3.504.000.000 linhas
A tabela Lancamento possui menos linhas do que seu tamanho máximo possível.
Consideramos que durante os 40 anos de funcionamento da consultoria, em cada semana,
houve uma média de 50 lançamentos a cada hora e que se trabalhava 8 hs durante 5 dias da
semana.
LANCAMENTO
8 hs x 5 dias x 52 semanas x 40 anos x 50 lançamentos = 4.160.000 linhas
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
10
5.2
CRIAÇÃO
DE ESTRELAS
SECUNDÁRIAS
Consideramos que a agregação por semana é a mais freqüente nas consultas. Por isso
criamos a estrela secundária LANCAMENTO_SEMANAL, com os valores da tabela de fato já
agregados por semana. Essa tabela tem um tamanho inferior ao da tabela LANCAMENTO, e
isso agiliza a maioria das consultas do usuário. Ao invés de 4.160.000, o banco terá que
manipular apenas 2.080 linhas para realizar as consultas.
TEMPO_SEMANAL
52 semanas x 40 anos = 2.080 linhas
O tamanho máximo da tabela LANCAMENTO_SEMANAL pode ser calculado a seguir:
LANCAMENTO_SEMANAL
2.080 x 100 consultores x 100 atividades = 20.800.000 linhas
Consideramos que ao longo dos 40 anos, em média 15 consultores realizaram o lançamento
de 20 atividades em cada semana. O tamanho da tabela LANCAMENTO_SEMANAL pode ser
calculado como a seguir:
LANCAMENTO_SEMANAL
2.080 x 15 consultores x 20 atividades = 624.000 linhas
Consideramos também que a consulta 4.2 (Faturamento associado com diferentes tipos de
serviço de consultoria) é a utilizada mais freqüentemente pelo usuário. Por esse motivo,
criamos a estrela secundária LANCAMENTO_TIPOSERV. Considerando que existam 3 tipos
de serviço de consultoria, o tamanho da tabela pôde ser calculado a seguir:
LANCAMENTO_TIPOSERV
2.080 x 3 tipos de serviço = 6.240 linhas
Ela é muito menor que as outras tabelas e permite que a consulta 4.2 seja gerada rapidamente.
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
11
TEMPO
#cod_tempo
hora
data
dia_mês
semana
mês
ano
LANCAMENTO
CONSULTOR
#cod_tempo
#cod_consultor
#cod_atividade
num_horas
custo
lucro
faturamento
#cod_consultor
nome_consultor
LANCAMENTO _SEMANAL
ATIVIDADE
LANCAMENTO _TIPOSERV
#cod_tempo
#cod_consultor
#cod_atividade
num_horas
custo
lucro
faturamento
#cod_atividade
tipo_atividade
nome_cliente
tipo_servico
nome_projeto
#cod_tempo
#cod_tipo_servico
num_horas
custo
lucro
faturamento
TIPO_SERVICO
TEMPO_SEMANAL
#cod_tipo_servico
tipo_servico
nome_projeto
#cod_tempo
semana
mês
ano
O Agregate Navigator desviará as consultas para as tabelas secundárias sempre que isso
trouxer um desempenho melhor em sua execução. Por exemplo, a consulta 4.5 (Rentabilidade
de projetos específicos) quando agrupada por semana, terá sua cláusula FROM alterada para
utilizar as tabelas LANCAMENTO_SEMANAL e TEMPO SEMANAL. Isso certamente trará um
desempenho melhor do que se o processamento tivesse que ser sobre as tabelas
LANCAMENTO e TEMPO, que são bem maiores.
5.3
ANÁLISE DE ESPARCIDADE
Na tabela a seguir, podemos comparar os tamanhos das tabelas de fato primária e
secundárias.
TABELAS
LANÇAMENTO
TEMPO
CONSULTOR
ATIVIDADE
MÁXIMO LINHAS
LINHAS
ESPARC.
350.400
100
100
3.504.000.000
4.160.000
0,12 %
LANÇAMENTO_SEMANAL
2.080
100
100
20.800.000
624.000
3,00 %
LANÇAMENTO_TIPOSERV
2.080
-
3
6.240
6.240
100,00 %
Observamos que não ocorre a falha de esparcidade nas tabelas de fato do Data Warehouse da
empresa de consultoria, visto que quanto mais agregada é a tabela, menor é seu tamanho.
UFPB – Universidade Federal da Paraíba
Banco de Dados Multidimensional
12