Fundamentos e Projeto de Big Data Tema 4 Projeto físico de modelo dimensional para Data Warehouse

Prévia do material em texto

DESCRIÇÃO
Conceitos para aplicação no projeto físico de Data Warehouse, técnicas avançadas de
construção de hierarquias no modelo dimensional, agregação, consolidação de dados para
otimizar consultas analíticas no DW/DM, e a implementação do modelo no SGBD.
PROPÓSITO
Compreender técnicas avançadas com objetivo de auxiliar na construção da estrutura física do
modelo de dados dimensional, definir a padronização da nomenclatura utilizada e a
configuração do ambiente do banco de dados, assim como ter o entendimento da importância
de provisionar a estrutura para o volume de dados esperado, viabilizando assim um tempo de
resposta exequível, são atribuições esperadas em um profissional de Business Intelligence (BI).
PREPARAÇÃO
Os exemplos deste conteúdo foram construídos na ferramenta SQL Power Architect, que pode
ser utilizada opcionalmente para o acompanhamento do aluno, para que esse possa exercitar a
parte prática. Em relação ao banco de dados, para armazenar o DW, você pode utilizar
qualquer SGBD de sua preferência que utilize o paradigma relacional. Para ver algumas
opções mais usuais, consulte a seção Explore+.
OBJETIVOS
MÓDULO 1
Descrever o projeto físico do Data Warehouse em um banco de dados relacional
MÓDULO 2
Descrever conceitos avançados de modelagem dimensional envolvendo hierarquias de
dimensões
MÓDULO 3
Descrever o processo de agregação e consolidação dos dados em tabela fato visando à
otimização de consultas
INTRODUÇÃO
Business Intelligence (BI) torna-se cada vez mais presente nas organizações; sendo assim,
projetos que envolvem BI têm sido cada vez mais demandados para auxiliar setores
estratégicos, com o intuito de viabilizar apoio à decisão. Sabemos que o desenvolvimento de
um projeto de software é dividido em etapas e possui um início e um fim, além da necessidade
de ser mantido e possibilitar evoluções incrementais ao longo do seu ciclo de vida.
Dentro desse ciclo de vida do projeto de BI, os dados são protagonistas e precisam ser
estruturados de maneira que as consultas dos usuários tenham um desempenho satisfatório. A
construção da estrutura física do modelo de dados dimensional é um fator determinante para
atingir esse objetivo.
Neste conteúdo, serão apresentadas as hierarquias que podem ser construídas no modelo de
dados dimensional, tanto no esquema Floco de Neve quanto no esquema Estrela, além das
técnicas para a agregação e consolidação de dados a fim de otimizar as consultas analíticas
realizadas no Data Warehouse (DW). Por fim, será realizada a implementação do modelo
proposto em um sistema gerenciador de banco de dados (SGBD).
MÓDULO 1
 Descrever o projeto físico do Data Warehouse em um banco de dados relacional
PROJETO FÍSICO
No ciclo de vida de projetos de DW/DM (Data Warehouse/Data Mart), temos a trilha de dados,
que se dedica ao tratamento dos dados e encadeia a fase de Modelagem Dimensional, a
definição do projeto físico e a especificação e o desenvolvimento do processo de ETL (do
inglês Extraction-Transformation-Loading ou Extração-Transformação-Carga), conforme
apresentado na imagem a seguir.
 Ciclo de Vida de um Projeto de Data Warehouse
A implementação física do modelo de dados dimensional considera o Sistema Gerenciador de
Banco de Dados (SGDB) escolhido para o projeto e alguns outros pontos que serão detalhados
a seguir.
O modelo de dados dimensional físico parte do modelo lógico e une os padrões estabelecidos,
as regras de negócio, as características do SGBD e o envolvimento de alguns especialistas
que darão suporte e aplicarão soluções para que a implementação do modelo seja feita com
sucesso, buscando um bom desempenho nas consultas analíticas.
Para que o projeto do DW/DM continue seu desenvolvimento, o modelo de dados dimensional
lógico desenhado precisa ser transformado em um ambiente físico onde os dados possam ser
acomodados.
Nesse momento, as características do SGBD devem ser observadas, pois o projeto físico utiliza
essas informações para sua construção, e isso pode variar entre os SGBDs.
 COMENTÁRIO
Nesse aspecto, o projeto físico de um DW não difere muito de projetos de banco de dados
convencionais, no sentido de que as funcionalidades específicas do SGBD escolhido devem
ser consideradas no mapeamento do modelo lógico de dados para o modelo físico.
As informações de restrições de integridade e de valores nulos devem ser avaliadas com
atenção para que sejam aplicadas corretamente no projeto físico.
Outra questão importante são os padrões utilizados para os nomes de tabelas, colunas, índices
etc. O padrão da nomenclatura deve ser estabelecido antes mesmo de iniciar o desenho do
modelo de dados dimensional físico para que todos os elementos sigam corretamente a
definição. Não há um padrão obrigatório a ser usado e, normalmente, utiliza-se o padrão
especificado pela organização, através do papel de Administrador de Dados.
O projeto físico envolve, além das tabelas do modelo de dados dimensional, algumas tabelas
de suporte ao processo de ETL que veremos mais à frente. Essas tabelas são chamadas de
tabelas temporárias e são a porta de entrada para a chamada staging area ou área de
manobras/preparação dos dados.
A staging area é o conjunto de espaço e elementos que fica entre o sistema de origem dos
dados e a área de apresentação dos dados.
As tabelas temporárias recebem os dados extraídos do sistema origem para que eles possam
ser tratados no processo ETL. Somente após os tratamentos, os dados são carregados e
acomodados nas tabelas Dimensão e Fato.
Além das tabelas temporárias, outras tabelas de suporte à segurança, tabelas do tipo “De para”
de dados e tabelas de metadados podem ser construídas. A criação dessas tabelas depende
da necessidade do projeto que está sendo desenvolvido.
Também é avaliada nessa etapa a construção de índices, partições e tabelas agregadas por
meio de visões (views), estas podendo ser materializadas ou não. Esses recursos melhoram o
desempenho das consultas que serão submetidas ao ambiente analítico e são muito
importantes para o DW/DM que trabalha com um volume de dados muito grande.
Vamos explorar melhor esses pontos e aplicá-los ao projeto físico do DW, utilizando o modelo
de dados dimensional denominado Vendas a Varejo, ilustrado pela imagem a seguir.
 Esquema dimensional (Estrela) de Vendas a Varejo.
PADRONIZANDO A NOMENCLATURA DOS
ELEMENTOS DO MODELO DE DADOS
DIMENSIONAL FÍSICO
Vamos adotar a seguinte nomenclatura para o desenvolvimento dos elementos do modelo de
dados dimensional:
TABELA DIMENSÃO
Os nomes das tabelas dimensões serão iniciados com dim_.
TABELA FATO
Os nomes das tabelas fato serão iniciados com ft_.
TABELA TEMPORÁRIA
Os nomes das tabelas temporárias serão iniciados com tmp_.
COLUNA DE CHAVE IDENTIFICADOR
Os nomes das colunas que representam identificadores serão iniciados com sk_.
COLUNA DE CÓDIGO
Os nomes das colunas que representam códigos serão iniciados com cd_.
COLUNA NUMÉRICA
Os nomes das colunas que representam dados numéricos serão iniciados com num_.
COLUNA DE DESCRIÇÃO
Os nomes das colunas que representam descrições serão iniciados com ds_.
COLUNA DE NOMES
Os nomes das colunas que representam nomes serão iniciados com nm_.
COLUNA DE DATA
Os nomes das colunas que representam datas serão iniciados com dt_.
COLUNA DE VALOR
Os nomes das colunas que representam os valores serão iniciados com vl_.
 DICA
Algumas ferramentas são sensíveis a letras maiúsculas e minúsculas, então, para minimizar
problemas futuros, é recomendado definir se os nomes serão criados todos em caixa alta ou
em letras minúsculas.
TABELAS NO SGBD
A seguir, serão apresentados os tipos de tabelas que fazem parte do projeto físico do modelo
de dados dimensional.
TABELA DIMENSÃO
A Dimensão Produto contém os dados Código do Produto e Descrição do Produto.
Apesar de a informação sobre o Fabricante do Produto estar armazenada na tabela Fabricante
no sistema origem, foi adicionada na dimensão Produto como um atributo.A dimensão
receberá o nome dim_produto, junção do prefixo definido na nomenclatura e da palavra produto
que representa os elementos dessa dimensão. As colunas da dimensão devem seguir o critério
estabelecido para a formação dos nomes.
A imagem a seguir ilustra o desenho da dimensão Produto com os nomes físicos dos atributos
nas colunas, o tipo de dados e a informação se a coluna pode ou não ficar nula.
 Tabela: Exemplo da tabela dimensão Produto.
A dimensão contém uma coluna que identifica um registro na tabela, a Surrogate Key. Essa
chave será inserida na tabela fato como chave estrangeira (Foreign Key) para que o
relacionamento entre elas seja realizado.
 COMENTÁRIO
Na dimensão Produto, essa chave se chama sk_produto e é identificada pela sigla PK (Primary
Key).
TABELA FATO
Sabemos que a tabela fato reúne as métricas que serão analisadas pelas dimensões e é
relacionada às tabelas de dimensões do modelo. Agora vamos entender os efeitos dessa
relação!
A tabela fato recebe todas as chaves primárias das dimensões que estão ligadas a ela. Como
toda tabela em um banco de dados relacional, a tabela fato possui uma chave primária, que,
em geral, é uma composição de chaves estrangeiras referentes às chaves primárias das
dimensões. Essa chave composta garante que um registro na tabela fato seja único; e caso
haja dois registros com a mesma combinação de chaves, haverá uma exceção que deverá ser
tratada no processo ETL.
Observe o modelo ilustrado a seguir:
A tabela ft_vendas no centro do modelo, recebeu, além das métricas, as chaves estrangeiras
correspondentes às chaves primárias das tabelas dimensões.
 Tabela: Exemplo do modelo de dados dimensional físico.
Cada registro da tabela fato representa a venda de um produto de uma categoria para um
cliente, em determinado dia.
Se o mesmo cliente comprar vários produtos no mesmo dia, haverá várias linhas para esse
cliente relacionadas aos diversos produtos comprados.
O campo num_pedido é um dado numérico que não pode ser sumarizado. Ele é o número que
identifica o pedido no sistema transacional. Dados como número de pedido, número de nota
fiscal etc. podem ser importantes para as análises, mas não possuem características que os
definam como uma dimensão. Nesse caso, eles são adicionados diretamente na tabela fato e
são denominados de dimensões degeneradas por não estarem ligados a tabelas de dimensão.
RESTRIÇÕES DE INTEGRIDADE
As restrições de integridade (do inglês integrity constraints ou simplesmente constraints)
servem para garantir que os dados cumpram corretamente as regras estabelecidas para a
carga na base de dados.
 EXEMPLO
No cenário Supermercado, diariamente, ocorrem muitas vendas de produtos. Obrigatoriamente,
temos de informar o produto que está sendo vendido, pois ele possui o preço que deverá ser
pago pelo cliente. No entanto, os dados do cliente podem não ser informados no ato da venda
na loja física, diferentemente da venda realizada pela loja on-line, onde a identificação do
cliente é obrigatória. Com essa particularidade, a informação do cliente pode ser preenchida ou
não, e devemos considerar um tratamento adequado para os casos em que informação não for
preenchida.
As dimensões do DW/DM podem receber os elementos Não Informado e Não se Aplica para
solucionar problemas desse tipo. O elemento Não Informado é utilizado quando um dado
apresenta o valor nulo na área de preparação dos dados. O elemento Não se Aplica é utilizado
quando o preenchimento de um dado para o contexto do registro não se aplica.
 Tabela: Exemplo de violação de integridade na tabela fato Vendas a Varejo.
A imagem anterior ilustra um exemplo sobre a unicidade da chave primária na tabela fato e o
caso do cliente não informado. O código sk_cliente igual a 1 representa o dado Não Informado.
Observe que, nas linhas 1 e 3 do exemplo, a coluna relacionada aos clientes está preenchida
com o elemento 1 - Não informado. Além disso, como os clientes compraram o mesmo produto
no mesmo dia, a restrição de unicidade será violada. Para resolver esse problema, o número
do pedido deve ser adicionado na chave primária da tabela fato.
Veja o resultado na imagem a seguir.
 Tabela: Alteração da PK da tabela fato Vendas a Varejo.
Com essa alteração, o problema da unicidade do dado será contornado e o resultado obtido
será conforme ilustrado pela imagem a seguir.
 COMENTÁRIO
As restrições pertinentes às características do SGBD, como, por exemplo, o preenchimento das
chaves primárias e estrangeiras, são facilmente observadas na construção do modelo de dados
dimensional. Contudo, restrições por parte do negócio, como, por exemplo, o cliente Não
Informado, são variadas e devem ser analisadas com atenção para evitar problemas futuros.
 Tabela: Resolução da violação PK na tabela fato Vendas a Varejo.
TABELAS TEMPORÁRIAS
As tabelas temporárias dão suporte ao processo de ETL. Elas recebem os dados que são
extraídos dos sistemas de origem e auxiliam os tratamentos que devem ser aplicados aos
dados.
Nessas tabelas, não há restrições de chaves e o dado é copiado e carregado sem qualquer
crítica.
Após a carga dos dados, a transformação dos dados pode ocorrer para o conteúdo
armazenado.
Nesse momento, são aplicadas as validações dos dados, a checagem de existência dos
elementos e das chaves, ocorre ainda a integração de dados de sistemas diferentes, entre
outras tarefas.
O resultado das validações, assim como os dados informativos a respeito da limpeza dos
registros, é armazenado nessas tabelas, onde será possível informar que ele deverá ser
carregado na tabela definitiva ou descartado pelo processo.
Assim, normalmente, para cada uma das tabelas dimensões e tabelas fato há uma tabela
temporária que registrará o processo de validação dos dados.
PRATICANDO – COMPLEMENTANDO O MODELO
DE DADOS DIMENSIONAL
Conforme o levantamento de requisitos para a construção das consultas, observou-se a
necessidade de que o modelo de dados dimensional contivesse o desenho apropriado para
acomodar os dados referentes ao estoque dos produtos. Com isso, complete o modelo de
dados dimensional com a tabela fato Estoque (ft_estoque) e os relacionamentos com as
dimensões dim_produto, dim_data e dim_cliente.
 ATENÇÃO
Uma observação importante é que, para relacionar a tabela fato Estoque com as dimensões,
não é necessário duplicar as tabelas dimensões, elas devem ser apenas relacionadas com a
nova tabela fato, compartilhadas com os demais fatos relacionados.
Acrescente também as tabelas temporárias ao modelo. Elas não devem ser relacionadas a
nenhuma das tabelas do modelo de dados dimensional, pois darão suporte ao processo de
ETL. Utilize a ferramenta SQL Power Architect Community Edition para fazer a modelagem.
Na imagem a seguir, vemos o resultado do modelo com a inclusão da tabela fato Estoque e das
tabelas temporárias.
Na cor azul, estão as dimensões;
Na cor laranja, as tabelas fato;
Na cor verde, as tabelas temporárias.
 Modelo de Dados Dimensional DW Supermercado.
 ATENÇÃO
Note que não é necessária a criação de uma tabela temporária para a dimensão Data.
POR QUE A CRIAÇÃO DESSA TABELA NÃO É
NECESSÁRIA?
Porque a dimensão Data é carregada previamente, abrangendo o período desejado para o
registro histórico das transações, e não sofre alterações em seu conteúdo, daí sendo
desnecessário manter uma tabela temporária para suportar o processo de ETL.
ARMAZENAMENTO DOS DADOS
A estrutura de armazenamento dos dados de um DW/DM conta com espaço em disco
disponível, processos de backup e deve ser apoiada por um grupo de atividades importantes
para o bom desempenho do DW/DM, como a estrutura correta da criação dos elementos com
os nomes padronizados.
Em caso de extensão do DW/DM, deve ser verificado se os elementos estão adequados, se
não estão sendo criados com repetição, ou se os dados com conceitos já existentes estão
sendo inseridos nas tabelas corretas, entre outras verificações. Importante destacara
necessidade de manutenção da documentação e do registro das suas versões.
O Administrador de Dados (AD) é o responsável por essas atividades e está presente no
desenvolvimento de projetos de DW, atuando na trilha de Gerenciamento do Projeto, junto ao
gerente do projeto.
Outra atividade importante está relacionada ao Administrador de Banco de Dados (do inglês
DBA – Database Administrator), que é responsável pela criação e manutenção da base de
dados, pela integridade de dados, e se preocupa com o desempenho do sistema de banco de
dados como um todo, muito importante para o DW que possui grandes volumes de dados
armazenados.
O particionamento das tabelas fato e a criação de índices são tarefas realizadas pelo DBA para
que o DW tenha um desempenho melhor nas consultas. O particionamento de tabelas e
índices é usado para facilitar o gerenciamento de grandes volumes de dados armazenados;
lembre-se de que as tabelas fato podem armazenar milhões ou bilhões de linhas, dependendo
do histórico das transações.
O particionamento divide a tabela em várias tabelas e essa fragmentação pode ser feita
verticalmente ou horizontalmente.
No particionamento horizontal, a quantidade de linhas é reduzida.
No particionamento vertical, a quantidade de colunas é reduzida.
Em geral, o particionamento da tabela fato é feito por fragmentação horizontal baseada no
tempo.
 EXEMPLO
Podem ser criadas partições por mês ou ano, e quando uma consulta for submetida para o ano
2020, apenas a partição que está com o conjunto de dados para 2020 será consultada.
Quando os dados são agrupados nas partições, a busca fica restrita apenas à partição em que
os dados requeridos estão armazenados. Isso minimiza o tempo de consulta, pois evita que a
tabela seja totalmente verificada para trazer os dados solicitados.
Outro recurso que pode ser aplicado pelo DBA são os índices.
Os índices são estruturas que auxiliam a recuperação dos dados de maneira mais rápida. No
DW/DM, que possui alto volume de dados, é recomendado criar os índices para otimizar as
consultas submetidas à base de dados.
Para dados com baixa cardinalidade, isto é, com poucos valores distintos (por exemplo, estado
civil, gênero, forma de pagamento etc.), normalmente são usados índices do tipo bitmap, mas
cada caso deve ser examinado para que a melhor ação seja tomada, dependendo também das
funcionalidades do SGBD utilizado.
Além das partições e dos índices que podem ser criados pelo DBA para melhorar o
desempenho das consultas no ambiente analítico, há também as agregações de dados que
são armazenadas em tabelas. Esse ponto será mais bem explorado nos próximos módulos.
IMPLEMENTAÇÃO DO MODELO DE DADOS
DIMENSIONAL
Após a correta preparação da base de dados, a implementação do Modelo de dados
Dimensional pode ser realizada. Como em qualquer projeto de banco de dados, algumas
ferramentas de modelagem geram os scripts para a criação das tabelas, restrições, chaves etc.
Esse recurso facilita a criação dos elementos, que também podem ser criados diretamente no
SGBD seguindo as definições do modelo de dados dimensional físico.
 EXEMPLO
As ferramentas MySQL Workbench e SQL Power Architect, entre outras, possuem esse
recurso.
O SQL Power Architect permite que você escolha o banco de dados onde o modelo será
implementado. No nosso exemplo, vamos criar a base de dados (database) no SGBD
PostgreSQL. A ferramenta usa a conexão feita no início da criação do modelo e executa o
script criando a base de dados, conforme ilustrado a seguir.
 Criação da base de dados.
Nesse momento, a base de dados para o DW Supermercado está criada, no entanto, com o
andamento do projeto e até mesmo depois da conclusão, novas necessidades podem surgir.
Isso significa que o modelo criado pode sofrer alterações para atender às novas demandas.
 ATENÇÃO
Esse trabalho deve ser feito com cautela para assegurar que o modelo criado e os dados
contidos nele não sofram perdas devido ao crescimento do ambiente.
É altamente recomendado que as tabelas sejam construídas no ambiente de desenvolvimento
onde os testes são realizados, e somente após esses passos as alterações sejam efetivadas
no ambiente de produção.
Em empresas de grande porte, há ainda um terceiro ambiente chamado Homologação, onde
os elementos desenvolvidos e as alterações feitas no processo são testadas pelo usuário e,
somente após esse passo, a alteração pode ser refletida no ambiente de produção.
CONSTRUINDO O MODELO DE DADOS
DIMENSIONAL E IMPLEMENTANDO NO
SGBD
No vídeo a seguir, o especialista construirá o modelo de dados dimensional proposto neste
módulo utilizando a ferramenta SQL Power Architect Community Edition.
VERIFICANDO O APRENDIZADO
MÓDULO 2
 Descrever conceitos avançados de modelagem dimensional envolvendo hierarquias
de dimensões
DIMENSÕES E HIERARQUIAS
As tabelas de dimensões contêm as descrições e explicações dos fatos ocorridos. Geralmente,
possuem muitas colunas de texto de baixa cardinalidade e seus atributos têm importante papel
no filtro das consultas ou nas agregações dos dados contidos na tabela fato.
As tabelas de dimensões podem ser desnormalizadas (esquema Estrela) ou normalizadas no
esquema Floco de Neve, que estende as dimensões em decomposição hierárquica. Assim, as
dimensões são organizadas em níveis, onde uma tabela em um nível superior se relaciona com
a tabela do nível abaixo por meio da chave primária.
Essa relação apresenta uma hierarquia que estabelece os níveis dentro de um contexto. A
seguir, vamos entender como as hierarquias se comportam, quais são os seus benefícios e
como elas são representadas no esquema Estrela, que é o modelo dimensional mais utilizado.
Uma hierarquia estabelece a relação entre elementos de um conjunto de dados de um
contexto.
As hierarquias são organizadas em níveis, e o nível mais alto é o elemento que agrupa os
demais elementos que estão abaixo.
Isso permite que os dados da tabela fato sejam visualizados conforme a navegação na
hierarquia, de modo agrupado (nível mais alto) ou detalhado (nível mais baixo).
Algumas dimensões possuem atributos que formam uma hierarquia. Esses atributos possuem
um relacionamento que estabelece a ordem de prioridade dos elementos. Por exemplo, no
modelo de dados dimensional do DW Supermercado, a dimensão Data possui uma hierarquia
formada pelos elementos Dia, Mês e Ano.
 Hierarquia de Data.
A imagem ilustra a hierarquia de Data que é composta pelo Ano, no mais alto nível da
hierarquia; o Mês, no nível intermediário; e o Dia, no nível mais detalhado. Com os dados
básicos de data, é possível criar elementos intermediários que agrupam o dado menos
detalhado. Por exemplo, podemos agrupar os dias em semanas, os meses em bimestre,
trimestre, semestre, entre outros.
No esquema Estrela, os dados ficam desnormalizados em uma única tabela dimensão. Nesse
caso, todos os atributos são colunas de uma mesma tabela. Veja o exemplo de outra hierarquia
bastante utilizada nos projetos de Data Warehouse, a de Região geográfica, que relaciona os
dados País, Região, Estado, Cidade e Bairro. A imagem a seguir ilustra a Dimensão Região
Geográfica e suas colunas disponibilizam toda a hierarquia de região contendo País, que é o
nível mais alto da hierarquia, Região, Estado, Cidade e Bairro, que é o nível mais granular
dessa visão.
 Hierarquia Região Geográfica.
Algumas hierarquias podem ser representadas sem as repetições dos elementos nas linhas.
Esse tipo de solução utiliza o autorrelacionamento na tabela, em que uma chave estrangeira
aponta para a chave primária da própria tabela.
 Hierarquia de autorrelacionamento.
A imagem anterior ilustra um exemplo da hierarquia de um quadro funcional.
Observe que Joana é a presidente, por isso ela é o primeiro nível da hierarquia. Abaixo de
Joana estão os gerentes Júlia, que possui um subordinado, Miguel, que não possui
subordinado, e Ricardo, que possui dois subordinados.
A dimensão acomoda a relação de hierarquiapor meio da coluna Código Superior, que é usada
para retornar uma consulta sobre os funcionários e seus superiores.
Para retornar os valores, a consulta deverá montar a hierarquia aplicando busca com
recursividade, sendo a coluna Código Superior relacionada com a coluna Código Funcionário
da mesma tabela.
As hierarquias são muito úteis para a navegação dos dados e possibilitam realizar as
operações de Drill Down e Drill Up, detalhando mais ou menos os dados contidos na tabela
fato.
CARGA DAS DIMENSÕES
As dimensões são carregadas com elementos contidos em tabela do banco de dados
transacional. A primeira carga dos dados no Data Warehouse deve inserir todos os elementos
existentes na tabela origem, considerando algumas regras que possam existir, como carregar
no DW somente os registros ativos. Um exemplo disso são clientes antigos, que no sistema de
origem receberam o status inativo por não realizarem compras nos últimos 5 anos.
Após a primeira carga dos dados, os elementos são atualizados conforme forem atualizados no
sistema de origem.
 COMENTÁRIO
Normalmente, as alterações nas dimensões acontecem de maneira esporádica. No entanto, se
pensarmos em uma grande empresa e na sua dimensão de cliente, com 3 milhões de clientes
ativos, ela pode ser atualizada diariamente, já que o volume de dados é muito grande.
Além disso, uma dimensão desse porte pode receber novos clientes diariamente e ainda
cancelar os que estão ativos.
 EXEMPLO
Uma operadora de telefonia que diariamente vende pacotes para novos clientes, ou quando
vários clientes precisam ser cancelados por terem migrado para outra operada. Os registros
desses clientes precisam ser modificados de acordo com a situação atual, com a data de
cancelamento, entre outros dados.
DIMENSÕES HISTÓRICAS
Alguns negócios precisam, além de acompanhar os fatos ao longo do tempo, manter o histórico
de atributos para analisar os dados conforme a situação em um momento do tempo, mas não
em relação ao fato, e sim ao contexto que o descreve. Para uma organização, pode ser
importante acompanhar a situação de estado civil dos clientes, não somente se estão casados,
mas se eram solteiros quando se tornaram clientes.
Para resolver essas questões, há algumas soluções que podem ser aplicadas, como criar uma
tabela de suporte que armazene as informações históricas dos atributos relacionando a
dimensão e quais são as alterações realizadas; para cada dimensão, criar uma tabela de
suporte que guarde apenas as alterações do atributo desejado; ou ainda, na própria dimensão,
criar colunas para informar a data de início de vigência e a data fim de vigência do registro,
onde o registro com a data de fim de vigência fechada mantém os dados sem alteração e o
novo registro com nova data de início de vigência recebe as alterações realizadas pelo sistema
origem.
A imagem a seguir ilustra:
Um exemplo de alteração direta no registro existente (Dimensão Cliente 1).
Um exemplo de alteração com datas de início e fim de vigência do registro (Dimensão
Cliente 2).
 Tabelas: Tabela Dimensão Cliente 1 e Tabela Dimensão Cliente 2.
Com as colunas de data de início e fim de vigência, é possível saber que do dia 11/03/2001 ao
dia 26/06/2010 o José constava na base de clientes com o status Casado. Esse tipo de
informação pode, por exemplo, ser usado para a oferta de produtos destinados à situação que
o cliente se encontra.
APRESENTANDO CONCEITOS AVANÇADOS
DE DIMENSÕES
No vídeo a seguir, o especialista apresentará os conceitos avançados de modelagem
dimensional envolvendo hierarquias de dimensões e dimensões históricas.
VERIFICANDO O APRENDIZADO
MÓDULO 3
 Descrever o processo de agregação e consolidação dos dados em tabela fato
visando à otimização de consultas
TABELA FATO AGREGADA
A tabela fato armazena as métricas que serão descritas pelas dimensões.
Quanto maior a granularidade da tabela fato, maior a quantidade de registros contidos na
tabela. O grão atômico armazenado na tabela fato correspondente ao grão do nível mais
detalhado das transações operacionais e garante que qualquer consulta submetida pelo
usuário poderá ser respondida, desde que a transação tenha sido registrada no sistema
transacional.
Para agregar os dados contidos na tabela fato, algumas funções são aplicadas às métricas
para que os dados sejam apresentados conforme a necessidade do usuário. Essas funções
realizam somas nas métricas (SUM), apresentam o menor valor ou o maior valor de uma
métrica (MIN e MAX), realizam contagem (COUNT) e aplicam médias (AVG).
Realizar esses cálculos em uma tabela fato com milhões de registros, no grão mais baixo, é
muito oneroso para as ferramentas de visualização. O relatório ou dashboard demora certo
tempo para a exibir o resultado da consulta, e isso pode causar transtornos pela demora da
informação. Para resolver esse problema, podemos trabalhar com a agregação dos dados,
visando a melhorar a performance das consultas.
 COMENTÁRIO
A agregação de dados é um resumo dos dados da tabela fato que visa a um bom desempenho
das consultas do Data Warehouse. Esse recurso não traz custos ao projeto e pode evitar o
investimento em hardware, então a relação custo/benefício se torna muito atraente para a
melhoria de desempenho.
A tabela fato agregada armazena informações pré-calculadas de acordo com nível de
granularidade, mais alto do que a tabela fato transacional, que apresenta os dados no nível
mais detalhado. Por esse motivo, esse tipo de tabela apresenta o volume de dados menor do
que a tabela fato transacional.
A carga e atualização dos dados podem ser feitas com os dados lidos diretamente do sistema
origem ou das tabelas fato transacionais. Se a origem da agregação for as tabelas fato
transacionais, as tabelas agregadas não precisam de tabelas temporárias para darem suporte
ao processo ETL, pois os dados armazenados já estão validados. No entanto, se os dados são
lidos diretamente no sistema transacional, existe a possibilidade de ocorrer um problema, então
é recomendado que a leitura seja feita com base na tabela temporária da tabela fato.
Para criar uma tabela fato agregada, você precisa analisar quais são os fatos submetidos ou
que serão submetidos às funções de agregação constantemente, e por quais atributos eles são
ou serão agregados. Esse conjunto de dados é um forte candidato a virar uma tabela fato
agregada. Caso a consulta submetida apresente os dados de forma resumida, mas contenha
um atributo que não esteja presente na tabela fato agregada, a consulta é direcionada para a
tabela fato transacional que contenha a combinação solicitada.
O exemplo ilustrado a seguir apresenta os dados da tabela fato Vendas no nível de Número do
pedido, Produto, Cliente e Data da venda.
 Tabela fato Vendas a Varejo.
Vamos construir a tabela fato agregada baseada em uma das análises realizadas pelos
gerentes do Supermercado:
QUAIS SÃO OS PRODUTOS MAIS VENDIDOS NO
VERÃO?
Observe que a necessidade é avaliar a venda de produtos para construir a tabela agregada
para análises referentes aos produtos. Podemos agregar os dados preservando apenas as
dimensões que fazem parte do contexto da análise. Nesse caso, vamos manter o produto, a
categoria do produto, a data da venda e as métricas.
Veja o resultado na imagem a seguir.
 Tabela fato agregada Vendas Produto.
Esse comportamento é refletido no modelo de dados dimensional que receberá o desenho da
tabela agregada mantendo apenas o relacionamento com as dimensões que atenderão às
análises focadas no produto.
 Tabela: Modelo da tabela fato agregada agr_vendas_produto.
A tabela agregada agr_vendas_produto, ilustrada na imagem anterior, mantém apenas as
chaves da dimensão Produto e da dimensão Data. Na tabela fato agregada, a métrica
qt_produto_venda representa o somatório das quantidades de produtos que foram vendidos no
grão Dia, e a métrica vl_produto_venda representa o somatório dos valores pagos pelos
produtos no grão Dia.
A tabela fatoagregada é transparente para os usuários do DW, pois a ferramenta de
visualização direciona as consultas para as agregadas sem que haja interferência e
conhecimento de quem está submetendo a consulta.
Ainda sobre as agregações, existem algumas métricas que não podem ser sumarizadas, pois
são métricas não aditivas, ou que só podem ser sumarizadas por determinadas dimensões, as
métricas semiaditivas. Fique sempre atento a quais métricas você aplicará à agregação para
que não obtenha valores errados ou distorcidos.
Agora, adicione a tabela fato agregada ao modelo de dados dimensional e a relacione com as
dimensões Produto e Data. Para as tabelas fato agregadas utilize o prefixo agr_. Na imagem a
seguir, o resultado dessa tarefa é apresentado.
No SGBD, a criação e atualização da tabela fato agregada são realizadas através do recurso
de visão (VIEW), que nada mais é do que uma consulta à tabela fato transacional realizando a
sumarização desejada. Existe um conhecido trade-off no projeto de DW/DM que é a decisão
sobre armazenar ou não o resultado da VIEW, ou seja, materializar ou não a consulta como
uma nova tabela no banco de dados. O conteúdo da tabela fato agregada pode depender do
momento da execução da VIEW sobre a tabela fato transacional, o que significa que a tabela
fato agregada pode se tornar obsoleta, requerendo uma nova execução da VIEW para atualizar
a tabela.
A decisão sobre armazenar ou não uma tabela fato agregada dependerá de estudo de
desempenho do sistema quanto ao espaço de armazenamento necessário (que pode ser
exponencial, dependendo no número de hierarquias e de níveis de agregação) e quanto ao
tempo de execução da reorganização do banco de dados sempre que uma recarga das tabelas
fato transacionais ocorrer no sistema.
 Modelo de Dados Dimensional com tabela fato agregada Vendas Produto.
TABELA FATO CONSOLIDADA
Algumas necessidades do negócio envolvem análises compostas por métricas armazenadas
em diferentes tabelas fato. Muito semelhante à tabela fato agregada, a tabela fato consolidada
agrega dados unindo aqueles contidos em mais de uma tabela fato. Para que isso aconteça, os
dados precisam estar no mesmo nível de granularidade de uma dimensão comum aos dois
assuntos. Uma dimensão muito utilizada nesse tipo de análise é a dimensão data.
Para exemplificar esse conceito, vamos utilizar outra análise do cenário Supermercado: Quais
são os fabricantes dos produtos que oferecem maior lucro na comercialização dos seus itens?
Para que seja possível avaliar o lucro referente aos produtos, é necessário consultar dados na
tabela fato Vendas, onde está o valor do produto vendido, e na tabela fato Estoque, onde está o
preço de custo do produto. Contudo, a tabela fato Vendas está no grão Dia e a tabela fato
Estoque está no grão Mês. Isso significa que só será possível avaliar o lucro do produto por
mês, devido à granularidade da segunda tabela fato.
A solução para esse problema é agregar o dado da tabela fato Venda para o mês e então
consolidá-lo com a tabela fato Estoque. A tabela fato Estoque é relacionada à dimensão Data
sempre pelo dia 01 de cada mês, representando o mês.
O primeiro passo é obter o valor das métricas calculadas, o Valor da Receita Total e o Valor do
Custo Total, onde:
VALOR DA RECEITA TOTAL = VALOR DO
PRODUTO VENDIDO X QUANTIDADE DE
PRODUTO VENDIDO
VALOR DO CUSTO TOTAL = CUSTO DO
PRODUTO X QUANTIDADE DE PRODUTO
VENDIDO
 Tabelas: Tabela Fato Vendas e Tabela Fato Estoque.
Na imagem anterior, a tabela consolidada reunirá os dados necessários para apresentar o
resultado esperado nas consultas sobre o lucro dos produtos.
 ATENÇÃO
Observe que a data na tabela fato Vendas é nomeada como data_vendas e na tabela fato
Estoque é nomeada como data_estoque.
Para representar a data na tabela consolidada, podemos nomear a data com uma descrição
que represente a união das duas informações. Assim, vamos usar em nosso exemplo o nome
data_competência, pois os registros consolidados competem a determinado mês.
As métricas calculadas Valor Receita Total e Valor Custo Total são adicionadas à tabela fato
consolidada, assim como a métrica calculada Lucro, onde o Lucro é obtido do cálculo Valor
Receita Total menos o Valor Custo Total. Conforme ilustrado na imagem a seguir.
 Tabela: Tabelas Fato Consolidada Lucro.
Além das métricas calculadas criadas, podemos deixar pré-calculado o percentual de lucro
obtido. Para isso, basta dividir o lucro pelo valor receita total e multiplicar o resultado por 100.
Por exemplo, o Lucro do produto 1 é R$ 85,88 e a Receita total é R$ 199,88, assim temos:
 Atenção! Para visualização completa da equação utilize a rolagem horizontal
Logo, para o produto 1, o lucro obtido no mês 09/2020 é igual a 43%. Normalmente, esse tipo
de cálculo é realizado pelas ferramentas de visualização.
Agora, adicione a tabela consolidada ao modelo de dados dimensional do DW Supermercado e
relacione com as dimensões adequadas. Após a inclusão das tabelas no modelo, o resultado
deve estar parecido com a imagem a seguir. Então, gere o script e execute apenas o DDL das
novas tabelas e das constraints no SGBD.
 Modelo de Dados Dimensional com tabela fato consolidada Lucro Produto.
A implementação das tabelas fato consolidadas também se dá por meio de consultas do tipo
VIEW, assim como nas tabelas fato agregadas. Assim, o mesmo trade-off entre materializar ou
não as tabelas ocorre com as tabelas fato consolidadas.
R$ 85,88 / R$ 199,88  =  0,43  =  43%
EVOLUINDO O MODELO DE DADOS
DIMENSIONAL COM FATOS AGREGADOS E
CONSOLIDADOS
No vídeo a seguir, o especialista evoluirá o modelo de dados dimensional proposto no módulo
anterior com o uso de fatos agregados e consolidados utilizando a ferramenta SQL Power
Architect Community Edition.
VERIFICANDO O APRENDIZADO
CONCLUSÃO
CONSIDERAÇÕES FINAIS
Ao longo deste conteúdo, abordamos técnicas a serem aplicadas no projeto físico do modelo
de dados dimensional, algumas restrições que devem ser consideradas e aspectos relevantes
para o armazenamento dos dados.
Por fim, apresentamos conceitos avançados importantes na construção do Data Warehouse,
como estruturas das hierarquias de atributos nas dimensões, as tabelas fato de agregação e
consolidação de dados. Realizamos ainda a implementação do modelo de dados dimensional
físico no SGBD.
O conhecimento dessas técnicas de projeto de banco de dados é essencial para o profissional
de Business Intelligence, por se tratar da base para a qualidade da análise de dados.
 PODCAST
Ouça agora um resumo dos assuntos abordados no conteúdo que você acabou de estudar.
AVALIAÇÃO DO TEMA:
REFERÊNCIAS
KIMBALL, M. R. R. The Data Warehouse Toolkit - The Definitive Guide to Dimensional
Modeling. 3. ed. Indianapolis, Indiana: John Wiley Sons, 2013.
MONTEIRO, V. G. Arquitetura de Data Warehouse e Data Marts. Rio de Janeiro: YDUQS,
2021.
NAVATHE, S. B.; ELMASRI, R. Sistemas de Banco de Dados 6ª ed. São Paulo: Pearson
Addison Wesley, 2011.
PITON, R. Data Warehouse Passo a Passo – O guia prático de como construir um Data
Warehouse do zero. Porto Alegre: Raizzer, 2018.
EXPLORE+
Você já possui um SGBD instalado? A criação da base de dados pode ser feita em qualquer
SGBD relacional, mas caso ainda não tenha um preferido, você pode visitar os sites dos
SGBDs: PostgresSQL e MySQL, e escolher um deles para realizar os exercícios. Eles possuem
licença livre, são simples de instalar e atendem ao padrão SQL do modelo relacional de banco
de dados.
Para saber mais sobre funcionalidades dos SGBDs relacionais existentes no mercado, busque
tabelas comparativas em “Comparison of relational database management systems”, no site
Wikipedia.
O Capítulo 2 do livro The Data Warehouse Toolkit - The Definitive Guide to Dimensional
Modeling, de Ralph Kimball, traz um resumo sobre modelagem dimensional, com seções sobre
hierarquias em dimensões e fatos agregados.
Uma excelente fonte de recursos sobre Modelagem Dimensional, além dosconsagrados livros
textos do autor Ralph Kimball, estão registrados no site do Kimball Group, contendo referências
a artigos e dicas de projeto. Embora o grupo tenha “fechado as portas” em dezembro de 2015,
o site vem sendo atualizado com recursos disponibilizados pelos consultores que sucederam o
patriarca.
O livro Data Warehouse Passo a Passo, de Rafael Piton, também aborda esses temas nas
seções “Dimensão hierárquica: pai-filho” e “Fato agregada”.
CONTEUDISTA
Conteúdo original: Vivian Gabriela Santos Monteiro
Adaptação e conversão: Antonio Felipe Podgorski Bezerra
 CURRÍCULO LATTES
javascript:void(0);
javascript:void(0);