View
305
Download
0
Category
Preview:
Citation preview
Gestão de dadosData Warehouse
Autores
Bruno Moreira [20218]
Carlos Brito [20224]
Filipe Melo [20230]
João Nascimento [20234]
Pedro Teixeira [20227]
ÍNDICE
DIAGRAMA BASE DE DADOS........................................................................................................................4
Tabela FACT_sales...................................................................................................................................5
Tabela FACT_age_promotion..................................................................................................................6
Hierarquia dimensão Time......................................................................................................................7
Hierarquia dimensão Age........................................................................................................................8
SELECTS SQL............................................................................................................................................9
Vendas por estilo e por cor..................................................................................................................9
Vendas no outlet por quarter (descrescente pelo numero de vendas)...............................................9
Vendas totais por tamanho de sapato...............................................................................................10
Vendas totais por estilo, nos outlets de Gaia, no dia de Natal do ano 2008......................................10
Vendas totais por outlet, ano e cor, com recurso a tabela pivot.......................................................10
SQL Server Business Intelligence Development Studio..............................................................................12
A ferramenta.........................................................................................................................................12
O projecto..............................................................................................................................................12
1º Passo – Criação Data Source.............................................................................................................13
2º Passo – Criação Data Source View....................................................................................................13
3º Passo – Criação Cubo........................................................................................................................14
Hierarquias............................................................................................................................................15
Utilização do(s) Cubo(s).........................................................................................................................18
Microsoft Excel..........................................................................................................................................19
O Excel...................................................................................................................................................19
Conclusão..................................................................................................................................................21
Anexos.......................................................................................................................................................22
ÍNDICE DE IMAGENS
Imagem 1 - Diagrama da base de dados....................................................................................................29Imagem 2 - Diagrama FACT_sales..............................................................................................................30Imagem 3 - Diagrama FACT_age_promotion.............................................................................................31Imagem 4 - Hierarquia dimensão Time......................................................................................................32Imagem 5 - Hierarquia dimensão Age.......................................................................................................33Imagem 6 - Elementos do projecto...........................................................................................................36Imagem 7 - Data Source View FACT_sales.................................................................................................37Imagem 8 - Data Source View FACT_age_promotion................................................................................38Imagem 9 - Hierarquia Time......................................................................................................................39Imagem 10 - Browse hierarquia Time........................................................................................................40Imagem 11 – Quantidade de sapatos vendida por outlet, por estilo, por período, por ano......................42Imagem 12 - Quantidade de sapatos vendida por estilo, por cor em cada ano.........................................42Imagem 13 - Quantidade de sapatos vendida, no outlet Arrabida e Gaia, por estilo desportivo, por tamanho....................................................................................................................................................42Imagem 14 - Vendas de estilo por tamanho..............................................................................................43Imagem 15 - Vendas de cor por outlet......................................................................................................44
DIAGRAMA BASE DE DADOS
Agora que criamos e inserimos os dados, vamos analisar o diagrama da base de dados.Como se pode ver, em redor de cada fact table existe um tradicional star schema, sobre o qual podemos criar os cubos OLAP (Online Analytical Processing é a capacidade para manipular e analisar um grande volume de dados sob múltiplas perspectivas. As aplicações OLAP são usadas pelos gestores em qualquer nível da organização para lhes permitir análises comparativas que facilitem a sua tomada de decisões diária.).
Imagem 1 - Diagrama da base de dados
Assim, temos dois tipos distintos de tabelas. As fact table e as tabelas dimensão
Fact table: FACT_sales e FACT_age_promotion
Tabelas dimensão: Colour, Size, Style, Outlet, Age e Time
Tabela FACT_sales
Analisando a FACT_sales, podemos ver que a mesma possui duas métricas, fs_qtd e fs_value. A métrica fs_qtd representa a quantidade de sapatos vendidos e, a métrica fs_value indica o valor monetário dessa mesma quantidade de sapatos vendidos.
Cada uma dessas métricas possui cinco dimensões, colour, size, style, outlet e time. Podemos então afirmar que a base de dados se tornou verdadeiramente multi dimensional e, pode ser representada por um cubo.
Imagem 2 - Diagrama FACT_sales
Tabela FACT_age_promotion
A FACT_age_promotion possui apenas uma métrica, a fp_value, que representa o grau de afectividade das pessoas às promoções realizadas. Esta métrica possui duas dimensões, a dimensão outlet e a dimensão age.
Imagem 3 - Diagrama FACT_age_promotion
Hierarquia dimensão Time
A hierarquia é uma série de relações pai-filho, onde normalmente um membro pai representa a consolidação dos membros filho. Por sua vez, os membros pais podem ser filhos.
A dimensão Time tem uma hierarquia de quatro níveis, day, month, period e year. Desta forma podemos facilmente associar o dia 4 de Abril de 2009 ao mês Abril de 2009, que por sua vez podemos associar ao 2 quarter de 2009 e por sua vez ao ano de 2009.
Imagem 4 - Hierarquia dimensão Time
Na imagem anterior a hierarquia da dimensão time é perfeitamente visível.
Nível 1 (t_lt_id) representa o ano, estando o elemento t_y_id preenchido e os restantes a null Nível 2 (t_lt_id) representa o periodo, estando os elementos t_y_id e t_p_id preenchidos e os
restantes a null Nível 3 (t_lt_id) representa o mês, estando os elementos t_y_id, t_p_id e t_m_id preenchidos e
os restantes a null Nível 4 (t_lt_id) representa o dia, o nível mais granular de todos, estando todos os elementos
preenchidos
Hierarquia dimensão Age
A dimensão Age tem uma hierarquia de dois níveis, categoria e idade. Desta forma podemos facilmente associar a idade 4 anos à categoria criança, só para dar um pequeno exemplo.
Imagem 5 - Hierarquia dimensão Age
Na imagem anterior a hierarquia da dimensão Age é perfeitamente distinguível.
Nível 1 (a_la_id) representa a categoria, estando o elemento a_c_id preenchido e o elemento a_y_id a null
Nível 2 (a_la_id) representa a idade, estando os elementos a_c_id e a_y_id devidamente preenchidos
SELECTS SQL
Vamos de seguida dar alguns exemplos de SELECTS para obtermos dados da base de dados.
Vendas por estilo e por cor
Alterando os parâmetros da clausula WHERE podemos visualizar os dados do ano desejado, do mês desejado, dos outlets desejados
SELECT STYLE.s_desc as STYLE, COLOUR.c_desc AS COLOUR, SUM(fs_qtd) AS QUANTIDADE, SUM(fs_value) AS VALORFROM FACT_salesINNER JOIN COLOUR ON COLOUR.c_id = FACT_sales.fs_c_idINNER JOIN STYLE ON STYLE.s_id = FACT_sales.fs_s_idINNER JOIN TIME ON TIME.t_id = FACT_sales.fs_t_idINNER JOIN OUTLET ON OUTLET.o_id = FACT_sales.fs_o_id--WHERE TIME.t_y_id = 2007 AND TIME.t_m_id = 1 AND OUTLET.o_id IN (1, 2)GROUP BY COLOUR.c_desc, STYLE.s_desc;
Vendas no outlet por quarter (descrescente pelo número de vendas)
Alterando a cláusula WHERE podemos visualizar dados do ano desejado ou ainda refinar mais (drill down) os dados.
SELECT FACT_sales.fs_o_id AS OUTLET, TIME.t_p_id AS QUARTER, SUM(fs_qtd) AS QUANTIDADE, SUM(fs_value) AS VALORFROM FACT_salesINNER JOIN TIME ON TIME.t_id = FACT_sales.fs_t_idWHERE TIME.t_y_id = 2009GROUP BY FACT_sales.fs_o_id, TIME.t_p_idORDER BY FACT_sales.fs_o_id, VALOR DESC;
Vendas totais por tamanho de sapato
Alterando a cláusula WHERE podemos refinar os dados (drill down) de forma a obtermos exactamente o que pretendemos.
SELECT SIZE.sz_id as SIZE, SIZE.sz_desc as DESCRICAO, SUM(fs_qtd) AS QUANTIDADE, SUM(fs_value) AS VALORFROM FACT_salesINNER JOIN SIZE ON SIZE.sz_id = FACT_sales.fs_sz_idINNER JOIN TIME ON TIME.t_id = FACT_sales.fs_t_idINNER JOIN OUTLET ON OUTLET.o_id = FACT_sales.fs_o_id--WHERE TIME.t_y_id = 2007 AND TIME.t_m_id = 1 AND OUTLET.o_id IN (1, 2)GROUP BY SIZE.sz_id, SIZE.sz_desc;
Vendas totais por estilo, nos outlets de Gaia, no dia de Natal do ano 2008
SELECT OUTLET.o_desc AS OUTLET, STYLE.s_desc as STYLE, SUM(fs_qtd) AS QUANTIDADE, SUM(fs_value) AS 'VALOR (€)'FROM FACT_salesINNER JOIN STYLE ON STYLE.s_id = FACT_sales.fs_s_idINNER JOIN TIME ON TIME.t_id = FACT_sales.fs_t_idINNER JOIN OUTLET ON OUTLET.o_id = FACT_sales.fs_o_idWHERE TIME.t_y_id = 2008 AND TIME.t_m_id = 12 AND TIME.t_d_id = 24 AND OUTLET.o_addr = 'Gaia'GROUP BY OUTLET.o_desc, STYLE.s_desc;
Vendas totais por outlet, ano e cor, com recurso a tabela pivot
WITH VendasTotalOutletAnoCor AS (SELECT FS_VALUE AS Facturação, LT_DESC AS Level, T_Y_ID AS Year, O_DESC AS Outlet, C_DESC AS ColourFROM OUTLET, COLOUR, TIME, FACT_SALES, LEVELTIMEWHERE O_ID = FS_O_ID AND
C_ID = FS_C_ID ANDT_ID = FS_T_ID ANDO_ID = 1 ANDC_ID = 1 AND LT_ID = 1
)
SELECT * FROM VENDASTOTALOUTLETANOCOR PIVOT (SUM(Facturação) FOR Colour IN (Blue, Red, Green, Yellow)
) AS PVTAll
Mais exemplos de querys utilizando drill down (aumentar o detalhe) e rollup (ou drill up que significa diminuir o detalhe) poderiam ser apresentadas, mas considero que estas chegam para fazer a prova de conceito.
SQL Server Business Intelligence Development Studio
A ferramenta
Após o estudo da estrutura de uma data warehouse e, de perceber a sua lógica, como já foi demonstrado anteriormente através do uso de apenas SQL, foi decidido experimentar uma ferramenta de data warehouse de forma a verificar as funcionalidades e a performance real que esta tecnologia permite.
Como estamos a utilizar Microsoft SQL SERVER, a escolha recaiu sobre o Microsoft SQL Server Business Intelligence Development Studio.
Todo o processo de criação da data warehouse foi manual, não foi utilizado nenhum wizard no processo, pois como foi referido, o principal objectivo é académico e aprender o porquê e não meramente o como.
O projecto
No fim, o projecto de Microsoft SQL Server Business Intelligence Development Studio ficou com este aspecto.
É possível ver-se o data source, os diferentes data source views, um para cada cubo, os cubos em si e as dimensões existentes.
Cada um destes elementos foi criado manualmente e, a sua análise foi muito rica, pois permite ver “visualmente” o que já se tinha contastado em sql puro, mas de uma forma mais visual, mais apelativa.
Imagem 6 - Elementos do projecto
1º Passo – Criação Data Source
Este passo, do qual resulta um data source, consiste basicamente na indicação dos parâmetros necessários para criar uma ligação ao servidor de sql e, indicar qual a base de dados que se vai trabalhar que, neste caso, vai ser a base de dados que criamos e que aqui neste estudo está apresentada. O resultado é um Data Source, que no projecto foi apelidado de Dw.
2º Passo – Criação Data Source View
Após a escolha do Data Source que queremos utilizar, temos de escolher quais as tabelas a utilizar no Data source View. Basicamente o que teremos de escolher é uma das fact tables e, todas as suas tabelas dimensão e, desta forma construímos o Data Source View, que nada mais é do que a representação física do star schema que envolve cada uma das fact tables já aqui apresentadas. Neste caso criamos dois Data Source Views, um para a fact table FACT_sales e outro para a FACT_age_promotion.
Imagem 7 - Data Source View FACT_sales
Imagem 8 - Data Source View FACT_age_promotion
3º Passo – Criação Cubo
O procedimento seguinte é a criação do cubo em si. No projecto em causa, foram criados dois cubos, um sobre a fact table FACT_sales e outro sobre a fact table FACT_age_promotion.
Assim sendo, a criação do cubo inicia-se com a escolha de um Data Source View. Após esta escolha, temos de indicar quais das nossas fact table e quais as tabelas dimensão e, se existente, qual a tabela dimensão “time”.
Após a indicação das dimensões e a indicação das métricas a utilizar, o sistema tenta adivinhar a hierarquia de cada dimensão, pedindo ao utilizador para confirmar e alterar as hierarquias. Embora este seja um processo semi-automático, é interessante ver que o sistema nada mais faz do que analisar a hierarquia presente ao nível dos dados da base de dados, conforme já foi demonstrado anteriormente.
Assim, temos o nosso cubo criado e os dados prontos a serem extraídos.
Hierarquias
Embora conforme já afirmado, as hierarquias sejam criadas de forma “automática” pelo sistema, verdade também é que as mesmas têm de ser corrigidas de forma a representarem sem erros a estrutura hierárquica de níveis que estão representados nos dados da base de dados.
Imagem 9 - Hierarquia Time
Como se consegue visualizar, temos a hierarquia devidamente formada, com todos os níveis bem definidos e ordenados. De igual modo, temos alguma informação “extra” (lt_desc, pt_desc e mt_desc) de forma a completar ainda mais a informação disponibilizada ao utilizador, de forma não redundante.
Conseguimos ter uma excelente visualização dos níveis através da opção browse da dimensão. Aqui podemos ver, em forma de árvore, toda a hierarquia presente no projecto.
Imagem 10 - Browse hierarquia Time
De igual forma, houve exactamente o mesmo trabalho para a dimensão Age. Assim, temos a hierarquia devidamente formada, com todos os níveis bem definidos e ordenados.
Novamente temos uma excelente visualização dos níveis através da opção browse da dimensão. Aqui podemos ver, em forma de árvore, toda a hierarquia presente no projecto.
Utilização do(s) Cubo(s)
Após a criação dos cubos, abre-se o browse dos mesmos e, para dar um exemplo, questionamos o sistema sobre as unidades de sapatos vendidos, por Outlet e por Style e, o resultdo surge sobre a forma de uma pivô table, muito funcional e prática, permitindo quer o Drill down como Rollup.
Deixamos de seguida alguns exemplos.
Imagem 11 – Quantidade de sapatos vendida por outlet, por estilo, por período, por ano
Imagem 12 - Quantidade de sapatos vendida por estilo, por cor em cada ano
Imagem 13 - Quantidade de sapatos vendida, no outlet Arrabida e Gaia, por estilo desportivo, por tamanho
Microsoft Excel
O Excel
Após termos analisado o conceito quer em SQL Server quer na ferramenta de data warehousing da Microsoft, quisemos ver como a mais popular das ferramentas de Office da Microsoft se comportava ao extrair dados dos cubos para elaborar tabelas pivot e desta forma, um utilizador normal (não avançado) tratar dos dados, retirar todos os benefícios de um cubo OLAP, sem sequer imaginar a sua existência.
Apenas vamos produzir algumas imagens para apresentar a noção da apresentação dos dados no Excel
Imagem 14 - Vendas de estilo por tamanho
Imagem 15 - Vendas de cor por outlet
Conclusão
A Data Warehousing e o OLAP (On-Line Analytical Processing), são simplesmente incríveis a sintetização de informações sobre a empresa, através de comparações, visões personalizadas, análise histórica e projecção de dados em vários cenários de "e se ...", aliados a respostas rápidas e consistentes às consultas iterativas executadas pelos utilizadores , independente do tamanho e complexidade da base de dados, o que realmente permite ver o porquê do uso destas tecnologias em ambientes empresariais.
Não há qualquer dúvida de que no futuro iremos investir na aprendizagem de modelagem dimensional, a técnica utilizada para se ter uma visão multi-dimensional dos dados, em que os dados são modelados com uma estrutura dimensional, o cubo, com as suas dimensões, com os seus atributos e com a sua consolidação dos dados.
Anexos
Create Tables e Inserts SQL.DOCX
Recommended