Pular para o conteúdo principal

(T) Como indexar uma tabela Fato - (Best Practice)

A base de qualquer projeto de bi é ter um bom dw/data mart. Podemos falar em modelagem star-schema durante dias, sem falar nas variações do snowflake, mas o objetivo principal deste artigo é apontar algumas negligências que tenho percebido no tratamento da tabela fato. Tabela esta que é o principal pilar da casa que reside um modelo star-schema.

Ouço muitas vezes os clientes reclamando do desempenho das consultas enviadas contra o seu dw/data mart, ou do tempo de resposta das análises solicitadas ao bi. Isto é realmente inaceitável, não só numa perspectiva de implantação do projeto, mas também de desempenho da entrega das informações.

Como eu mencionei anteriormente o meu objetivo neste artigo, é alertar sobre a importância da indexação da tabela fato: o que deveria ser, porque é necessário, porque chaves compostas são boas e más, e porque você deveria se preocupar com isso.

Então, vejamos:

|a| Indexação padrão (default):
De forma rápida, todas as colunas de chave estrangeira (FK) devem ter um índice não-agrupado e não-exclusivo.

|b| E o que isso significa?
Isso significa que uma referência de chave estrangeira (FK) em uma coluna de uma tabela contendo a referência de chave primária (PK) apenas fornece ao mecanismo de banco de dados um ponto de referência para a sua tabela dimensão. Ele não faz nada para saber como os dados na tabela fato é organizada. Então, depois de criar uma chave estrangeira (FK) não se esqueça de criar o índice não-agrupado e não-exclusivo também.

|c| Por que a indexação da tabela fato é necessária?
Eu irei abordar este assunto aqui de forma sucinta. Mas, antes é preciso lembrar que você também é capaz de aplicar à FK, constraints. E que esta é uma das principais razões para se utilizar uma FK. Uma constraint basicamente diz ao banco de dados que um valor passado para a coluna FK da tabela fato, deve existir na própria tabela. Isto pode naturalmente ser desligado definindo o atributo apropriado da FK da tabela fato, mas é melhor deixar ativo. É bom ressaltar que até este ponto, isto nada tem a ver com o desempenho, e sim com a integridade dos dados que é realmente muito importante em um dw/data mart. No entanto, quando você começa a combinar indexação de cada coluna FK da tabela fato, na verdade você está iniciando um link entre a camada lógica e o mecanismo de sorting do banco de dados. Ao fazer isso, estamos evitando que uma consulta enviada ao banco de dados, faça uma varredura completa da tabela para recuperar a informação solicitada sem um índice apropriado. Ao passo que, se a tabela está indexada, essa mesma consulta ao invés de fazer uma varredura completa da tabela fato para recuperar n linhas, agora possui um outro caminho para buscar estas mesmas informações de forma mais rápida.

|d| Os índices compostos são bons ou ruins?
Quando eu comecei a aprender sobre indexação de uma tabela eu ainda era um programador em Visual Basic. Naquela época, eu sabia o que cada consulta ia enviar/solicitar ao banco de dados. Por exemplo, se tivesse que construir um relatório de vendas por produto, sabia que a cláusula 'WHERE' do meu 'SELECT' para exibir o resultado da solicitação era bastante simples e sempre estava filtrada em 'category_id' e algumas outras colunas. Para otimizar o tempo de resposta desta consulta, criaria um índice composto sobre as colunas que fazem parte da pesquisa/critérios da consulta. Isso funciona muito bem para um sistema operacional como este descrito acima, mas não é uma boa prática para um sistema business intelligence (BI). Uma das principais demandas de um projeto BI são os relatórios "ad-hoc", o que significa que os usuários finais irão criar uma infinidade de consultas, cortar e fatiar os dados (slice and dice) independentemente da forma como os dados se apresentam, tudo para obter a melhor resposta a sua análise. Além da dificuldade de definir com antecedência como será o índice composto, acrescento a possibilidade da sua tabela fato sofrer um 'overhead'. A maior dificuldade, estaria mesmo, em estabelecer o número de combinações e a quantidade de índices compostos que você teria para dar conta ou tentar usar em sua tabela fato.

Desta forma podemos observar que, naturalmente, o melhor lugar para se aplicar índices compostos é em um sistema OLTP. Eu não vou entrar aqui em maiores detalhes, mas processos de ETL de um sistema BI para a tabela fato pode ter uma baixa performance durante uma extração/carga devido aos índices compostos.
Em última análise, os índices compostos em tabela fato são ruins para os sistemas BI.


|e| Por que você deve se importar?
Em primeiro lugar você deve estar ciente de que existem diferentes aplicações com lógica de sistemas diferentes. E com BI isso não é diferente, ou seja, devemos sempre entender que o que funciona em uma situação pode não funcionar em outra.
Segundo, se você está criando uma nova solução para a sua empresa/cliente, você vai querer dar a melhor solução possível. Um dos itens mais negligenciados pelos arquitetos de solução (a menos que você tenha sido um DBA em sua vida passada) é a indexação, após descobrirem que o desempenho não é o que eles acreditam que deveria ser. A boa notícia é que sempre há espaço para a melhoria destas estruturas existentes. Para isso, dê uma olhada em sua saída de dados - será que é possível converter índices compostos em índices da coluna FK? Eu vejo isso todo o tempo, a tabela possui um índice composto por 'product_id', 'territory_id' e 'date_id'. E, quando é realizado uma análise mais aprofundada percebe-se que não há um índice somente para a coluna 'date_id', por exemplo. Também não podemos esquecer que existem no mercado hoje diversos tipos de motores OLAP. Se você estiver usando um motor OLAP como o do Oracle Essbase, e dependendo do desenho da sua solução (BSO), esta preocupação em indexar a tabela fato para obter o máximo de desempenho no acesso e na entrega das informações solicitadas deixa de existir e então todos os seus esforços passam a estar voltados para o tamanho do bloco de armazenamento, as agregações, e o cache da aplicação. No entanto, em um OLAP como o Oracle BI (OBIEE) ou SAP BW ou ainda MicroStrategy, a estrutura relacional é parte fundamental da solução, uma vez que esses sistemas funcionam como uma re-edição da consulta SQL enviada ao banco de dados para uma recuperação de dados do tipo OLAP.

Conclusão
Eu estava esperando para obter um exemplo através de um plano de execução realizado em cima de uma tabela para mostrar visualmente as estatísticas, mas isso não será possível e eu posso publicar isto em um outro artigo em uma outra data. Eu sei que com o que foi apresentado hoje neste artigo, muitas discussões podem surgir e no entanto, este é apenas um exercício para a frente que você pode fazer sozinho, se necessário. Mais uma vez, a idéia por trás deste artigo era apenas compartilhar uma preocupação minha com algo que continuo a deparar (nas empresas, nos projetos e clientes) e que é facilmente solucionado com algum esforço inicial e compreensão.

Basta lembrar que você precisa tanto de integridade nos dados (FK) quanto de desempenho nas consultas (índices). E para obter sucesso com o dw/data mart você precisa manter este equilíbrio e neste jogo você é a peça fundamental para fazer isto.

Se sua opinião é diferente, eu gostaria de saber o que você tem a dizer, me envie a sua opinião.

Um grande abraço a todos,

Comentários

Muito bom! sempre tive esta dúvida técnica do comportamento estre fato e dimensão, na prática ja estava ciente do ganho de performance.

Postagens mais visitadas deste blog

(A) Data Science in Practice with Python

The top trending in Twitter or other social network is the term “data science”. But ...
What’s the data science? How do real companies use data science to make products, services and operations better? How does it work? What does the data science lifecycle look like?  This is the buzzword at the moment. A lot of people ask me about it. Are many questions. I’ll try answer all of these questions through of some samples.

Sample 1 - Regression

WHAT IS A REGRESSION? This is the better definition what I found [Source: Wikipedia] - Regression analysis is widely used for prediction and forecasting, where its use has substantial overlap with the field of machine learning.
HOW DOES IT WORK? Regression analysis is also used to understand which among the independent variables are related to the dependent variable, and to explore the forms of these relationships. In restricted circumstances, regression analysis can be used to infer causal relationships between the independent and dependent variable…

(A) Tucson Best Buy Analysis

“Data! Data! Data!” he cried impatiently.  “I can’t make bricks without clay.” —Arthur Conan Doyle
The Ascendance of Data

We live in a world that’s drowning in data. Websites track every user’s every click. Your smartphone is building up a record of your location and speed every second of every day. “Quantified selfers” wear pedometers-on-steroids that are ever recording their heart rates, movement habits, diet, and sleep patterns. Smart cars collect driving habits, smart homes collect living habits, and smart marketers collect purchasing habits. The Internet itself represents a huge graph of knowledge that contains (among other things) an enormous cross-referenced encyclopedia; domain-specific databases about movies, music, sports results, pinball machines, memes, and cocktails; and too many government statistics (some of them nearly true!) from too many governments to wrap your head around.
Buried in these data are answers to countless questions that no one’s ever thought to ask. In…

(A) Data Science in Practice with Python - Sample 2

In this post I'll explain what is a recommender system, how work it and show you some code examples. In my previous post I did a quick introduction:

Sample 2 - Recommender System

WHAT IS A RECOMMENDER SYSTEM? A model that filters information to present users with a curated subset of options they’re likely to find appealing.
HOW DOES IT WORK? Generally via a collaborative approach (considering user’s previous behavior) or content based approach (based on discrete assigned characteristics).

Now I'll get into in some concepts very important about recommender systems.

Recommender System in Details:

We can say that the goal of a recommender system is to make product or service recommendations to people. Of course, these recommendations should be for products or services they’re more likely to want buy or consume.

Recommender systems are active information filtering systems which personalize the information coming to a user based on his interests, relevance of the information etc.…