Banco de dados Multidimensionais consultando o cubo de dados (MDX – SQl server 2008)

1. Banco de dados Multidimensionais consultando o cubo de dados(MDX – SQlserver 2008) Andresson Firmino Banco de Dados Avançado - Prof.ª Valeria Times

2. Importando o Cubo de Dados • O SQL Server 2008 possui dois tipos de servidor • DataBaseEngine • Base de dados do ambiente transacional • AnalysisService • Base de dados multidimensional • Por padrão apenas o DataBaseEngine é carregado • Para carregar o AnalysisService • Menu File => ConnectObject Explorer • Server Type: AnalysisService • Connect Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

3. Importando o Cubo de Dados • Para importar o cubo, no AnalysisService • Clique Direito em Databases, Restore.. • Na seção Restore Source, clique em Browser • Especifique o Diretório e o nome do Arquivo • OK, OK • Clique Direito em AnalysisService, Refresh • Cubo Importado Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

4. Esquema Multidimensional do Cubo Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

5. Consultando o Cubo de Dados • Para criar uma consulta • Clique Direito em AnalysisService • NewQuery, MDX • Para executar a consulta • Clique em execute Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

6. Consultando o Cubo de Dados • Minha primeira consulta MDX – Pontos importantes • Resultados MDXs são sub-cubos • Dimensões do cubo são mapeadas para eixos do sub-cubo • Uma consulta MDX pode ter mais de um eixo. Porém, MDX Analysis Server só suporta no max. 2 (columns e rows) • Dimensões não especificadas nos eixos podem aparecer no WHERE Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

7. Consultando o Cubo de Dados • Minha Primeira Consulta MDX • Operação Slice • Os clientes e os filmes alugados no primeiro ano. SELECT ([DIM CLIENTE].[Nome].children) ON COLUMNS, ([DIM FILME].[Nome].children) ON ROWS FROM [POINTFILMES] WHERE [DIM_TIME].[Year].firstChild -- OPERAÇÃO DE SLICE!! Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

8. Consultando o Cubo de Dados • Conceitos importantes • Tuples • Coleção de membros, os quais são de diferentes dimensões. • Não suporta mais de um membro de uma mesma dimensão • Sintaxe: ([Dim1].[Member], [Dim2].[Member], [Dim3].[Member]) • Sets • Conjunto de membros de um única dimensão • Sintaxe: {[Dim].[Member], [Dim].[Member], [Dim].[Member]} Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

9. Consultando o Cubo de Dados • Conceitos importantes • Sets podem conter um conjunto de tuplas • Ex: • Atenção!!! Não se usa Set no WHERE. • SELECT • {([DIM FUNCIONARIO].[Nome].[Andresson]), • ([DIM FUNCIONARIO].[Nome].[Carol])} ON COLUMNS, • ([DIM FILME].[Nome].children) ON ROWS • FROM [POINTFILMES] • WHERE ([DIM_TIME].[Year].firstChild, [DIM CLIENTE].[Sexo].[M]) Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

10. Consultando o Cubo de Dados • Conceitos importantes (vírgula X dois pontos) • Com vírgula, Ex: • o mesmo resultado utilizando dois pontos: SELECT {[DIM_TIME].[Month].[January 2006], [DIM_TIME].[Month].[February 2006], [DIM_TIME].[Month].[March 2006], [DIM_TIME].[Month].[April 2006] } ON COLUMNS FROM [POINTFILMES] SELECT {[DIM_TIME].[Month].[January 2006]: [DIM_TIME].[Month].[April 2006] } ON COLUMNS FROM [POINTFILMES] Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

11. Consultando o Cubo de Dados • Eliminando células vazias • Com: • Sem: SELECT {[DIM FUNCIONARIO].[Nome].[Andresson]} ON COLUMNS, ([DIM FILME].[Nome].children) ON ROWS FROM [POINTFILMES] WHERE ([DIM_TIME].[Year].firstChild, [DIM CLIENTE].[Sexo].[M]) SELECT {[DIM FUNCIONARIO].[Nome].[Andresson]} ON COLUMNS, NON EMPTY([DIM FILME].[Nome].children) ON ROWS FROM [POINTFILMES] WHERE ([DIM_TIME].[Year].firstChild, [DIM CLIENTE].[Sexo].[M]) Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

12. Consultando o Cubo de Dados • Ordenando o resultado de uma consulta • Classificando o resultado • SELECT • ORDER( • {[DIM_TIME].[Month].[January 2006]: [DIM_TIME].[Month].[April 2006]} • ,[Measures].[Valor] ,DESC)ON COLUMNS • FROM [POINTFILMES] SELECT TOPCOUNT( {[DIM FUNCIONARIO].[Nome].children} ,[Measures].[FATOS Count],10) ON COLUMNS FROM [POINTFILMES] Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

13. Consultando o Cubo de Dados • Navegando em uma hierarquia • Obter o número de alugueis de cada cliente durante os anos. Mas, para o ano de 2007 descer um nível, de modo a exibir também os valores nos semestre de 2007. SELECT DrilldownMember ( [DIM_TIME].[Year - Half Year - Quarter - Month - Date].children, {[DIM_TIME].[Year - Half Year - Quarter - Month - Date].[Year].[Calendar 2007]} ) ON 0, [DIM FUNCIONARIO].[Nome].children ON 1 FROM [PointFilmes] WHERE [Measures].[FATOS Count] Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

14. Consultando o Cubo de Dados • Navegando em uma hierarquia • Os cinco filmes mais alugados e exibir as suas quantidades de locações ao longo dos trimestres e meses. SELECT DRILLDOWNLEVEL( {[DIM_TIME].[Year - Half Year - Quarter - Month - Date].[Quarter].members}) ON COLUMNS, TOPCOUNT([DIM FILME].[Nome].children, 5, [Measures].[FATOS Count]) ON ROWS FROM PointFilmes Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

15. Consultando o Cubo de Dados • Mais de uma dimensão por eixo • ou • Cuidado com o CROSSJOIN operação muito custosa!!! • SELECT • CROSSJOIN([DIM CLIENTE].[Nome].children, • [DIM FUNCIONARIO].[Nome].children) ON COLUMNS • FROM PointFilmes • SELECT • ([DIM CLIENTE].[Nome].children, • [DIM FUNCIONARIO].[Nome].children) ON COLUMNS • FROM PointFilmes Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

16. Exercícios • Qual o número de locações para o filme Central do Brasil? • Qual o número de locações para o filme Central do Brasil e Código da Vinci? • Qual o número de locações do funcionário Andresson no mês de Outubro de 2009? • Qual o número de locações do funcionário Andresson no mês de Outubro de 2009 para o filme Central do Brasil? Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

17. Exercícios • Qual o valor dos aluguéis para todos os filmes em todos os semestres? • Qual o valor dos aluguéis para todos os filmes em todos os semestres para os clientes do sexo feminino? • Qual o valor dos aluguéis para todos os filmes por ano e semestre? • Quais os10 filmes mais alugados em 2009 e seu respectivos número de locações? Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

18. Exercícios • Quais os número de locações dos10 filmes mais alugados em 2009 para com os 5 clientes que fizeram mais locações em 2009? • Qual o valor dos aluguéis para todos os filmes nos meses das férias 2006-2007? • Agora, crie suas próprias consultas e pratique bastante. Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

19. Referências • Multidimensional Expressions (MDX) Referencehttp://msdn.microsoft.com/en-us/library/ms145506.aspx • Kimball, Ralph e Ross, Margy. 2002.The Data Warehouse Toolkit. 2ª Edição. s.l. : Wiley Computer Publishing, 2002. • MultiDimensionaleXpressions - Wikipedia, thefreeencyclopedia. Wikipedia. http://en.wikipedia.org/wiki/MultiDimensional_eXpressions. • Nolan, Carl.Introduction to Multidimensional Expression (MDX). s.l. : Microsoft Corporation. • Spofford, George, et al. 2006.MDx Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase. s.l. : Wiley Publishing, Inc., 2006.

20. Próxima Aula • Construindo o Cubo de Dados • Especificação do Projeto. Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times

21. Dúvidas ? Cin/UFPE - Banco de Dados Avançado - Prof.ª Valéria Times