Self-Service BI com PowerPivot

 

Olá Pessoal!

Estou começando um série de artigos sobre o SQL Server 2008 R2 e o assunto escolhido foi o PowerPivot. Vamos abordar os conceitos básicos e faremos uma pequena demontração sobre como essa nova proposta da Microsoft, chamada de Self-Service BI, pode ser poderosa.

Introdução ao Self-Service BI e ao PowerPivot

Inicialmente, vamos começar com uma introdução ao PowerPivot. A Microsoft esta trabalhando há um certo tempo com um projeto, voltado para um dinamismo maior na criação de soluções de BI, sem a “verdadeira” necessidade de conhecimentos avançados das técnicas de BI. Para isso, foi desenvolvido o PowerPivot, antigo projeto “Gemini”, com a idéia de Self-Service BI, ou seja, faça você mesmo o seu BI. A idéia básica é que, com o Microsoft Excel 2010, podemos realizar análises dinâmicas com gráficos, de diversas fontes de dados (independente de ser SQL Server, outro database ou arquivo de dados), com apenas alguns cliques. O Excel foi escolhido por questões óbvias: trata-se uma das mais conhecidas ferramentas da Microsoft, com amplo conhecimento de uso difundido, inclusive pelos membros da alta direção das empresas, que acabam sendo um grande alvo de utilização da solução, por se tratar de uma solução de BI. Para isso, basta instalarmos o plug-in do PowerPivot para o Excel 2010.

Mas, com base nisso, pode surgir uma dúvida: isso não torna a solução “travada”, pois, para distribuir essas informações para a organização, deveremos multiplicar essas planilhas? A reposta é não. O grande trunfo para isso é o SharePoint. Através uma integração com o PowerPivot, é possível prepararmos o nosso Microsoft Office SharePoint 2010 para publicar as análises. Para isso, basta instalarmos o PowerPivot para SharePoint. Abordaremos o PowerPivot para SharePoint futuramente.

Outros pontos são interessantes citarmos, ainda com relação ao Excel 2010 com o PowerPivot: primeiro, o PowerPivot acaba sendo mais versátil que utilizar, por exemplo, um cubo do Analysis Services do SQL Server, pois podemos integrar uma quantidade de dados muito maior, com múltiplas fontes de dados, com relacionamentos, e ainda compartilha-las no SharePoint. Outro fator, é a possibilidade de uso das chamadas Data Analysis eXpressions (ou expressões DAX), que são parecidas às formulas regulares do Excel, mas com mais funcionalidades. Mas e as tabelas dinâmicas do Excel, não fazem a mesma coisa? Não, pois não permitem gerar as mesmas informações, com a mesma facilidade, com múltiplas fontes de dados distintas (e relacionadas), e disponibilizá-las no SharePoint.

Em resumo: usaremos o PowerPivot for Excel 2010 para criar as análises e o PowerPivot for SharePoint 2010 para publicar as análises, com certa liberdade de interação com as mesmas.

Softwares necessários

Para quem quer ser aventurar com o PowerPivot for Excel 2010, precisará dos seguintes componentes:

– Microsoft Office 2010 Beta ou RTM:
http://www.microsoft.com/office/2010/en/default.aspx

– PowerPivot for Excel:
http://powerpivot.com/download.aspx

– Microsoft SQL Server 2008 R2 (Nov CTP):
http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx

Percebam que estamos trabalhando com versão  CTP (Community Technology Preview) de Novembro/2009 do SQL Server 2008 R2. Os processos de instalação desses componentes não serão abordados, pois estão detalhados no site do PowerPivot – www.powerpivot.com e do SQL Server 2008 R2, além de estar fora do escopo do nosso artigo.

Iniciando a demo e explorando o PowerPivot for Excel

Funções básicas

Para começar, vamos iniciar o Excel 2010:

Figura 1: abertura do Excel 2010.  

Com o Excel aberto, podemos visualizar a nova guia chamada PowerPivot. É nela que iremos trabalhar com o PowerPivot.

Figura 2: Aba do Excel 2010 com a opção do PowerPivot.

Para iniciarmos o nosso trabalho, vamos utilizar uma fonte de dados de exemplo. Para isso, temos várias opções em http://www.powerpivot-info.com/post/50-list-suggested-datasets-to-test-powerpivot. O exemplo escolhido por mim, foi http://powerpivotsampledata.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=35438, onde teremos dois bancos de dados Access e um arquivo Excel. Vale lembrar que um dos trunfos do PowerPivot é a manipulação de dados de multiplas fontes de dados.

Vamos abrir a aba do PowerPivot:

Figura 3: Aba do PowerPivot.

Notem que existe um primeiro botão disponível, chamado PowerPivot window. Nele iniciaremos o nosso processo de demonstração.

Figura 4: Janela do PowerPivot iniciada.

A janela do PowerPivot apresenta algumas informações básicas para iniciarmos o nosso projeto. A guia Home permite a conexão com a fonte de dados, quem podem ser bancos de dados, arquivos ou data feeds. Além disso, podemos administrar as conexões existentes. Caso seja necessário, podemos trocar as visualizações com a planilha do Excel que deixamos aberta.

Importação de dados

Vamos explorar um pouco sobre as formas de conexão com as fontes de dados:

Figura 5: Opções de fontes de dados do PowerPivot.

Podemos notar a presença de fontes de dados disponíveis para conexões, como o SQL Server, Access, SSAS & PowerPivot, além de outras fontes de dados, conforme abaixo:

Figura 6: Outras opções de fontes de dados do PowerPivot.

Figura 7: Outras opções de fontes de dados do PowerPivot – continuação.

Figura 8: Outras opções de fontes de dados do PowerPivot – continuação.

Estão disponíveis várias fontes de dados para conexão, como SQL Azure, Oracle, Informix, Sybase, DB2, SSAS, arquivos do Excel, arquivos texto, e outros. Além disso, podemos trabalhar com outras fontes de dados, além das citadas, desde que as mesmas trabalhem com Ole DB. Isso nos dá uma grande flexibilidade para a importação dos dados para análise, e esse é um grande diferencial do PowerPivot, conforme já citado.

Vamos iniciar a conexão com o nosso data source, selecionando a conexão com um banco de dados Access, que faz parte do exemplo que iremos utilizar:

Figura 8: Iniciando a conexão com o banco de dados Access de exemplo.

Na próxima tela, especificaremos os parâmetro de conexão com o banco de dados Access de exemplo:

Figura 9: Tela de conexão com a fonte de dados Access.

Nesse nosso exemplo do banco Contoso Sales, não necessitamos especificar os parâmetros de logon. Aproveitamos para realizar um teste de conexão com o banco, que foi realizado com sucesso.

Nesse momento, iremos especificar quais são os objetos do banco de dados que iremos importar. Notem que podemos escolher os objetos ou trabalhar com uma query. Nesse momento, iremos trabalhar com a seleção de objetos:

Figura 10: Opções de importação de objetos da fonte de dados.

A lista de objetos está disponível. Vamos importar todos os objetos do banco de dados, mas iremos especificar um nome mais amigável para o objeto. Além disso, a tela de seleção permite a criação de filtros e de visualização prévia dos dados, antes da importação.

Figura 11: Lista dos objetos que serão importados.

Nesse momento iremos importar os objetos para o PowerPivot:

Figura 12: Objetos sendo importados para o PowerPivot.

Figura 13: Objetos importados com sucesso para o PowerPivot.

Após o processo de importação dos objetos ser concluído, podemos visualizar os dados importados. Notem que estamos trabalhando com uma tabela de Vendas que possui quase 4 milhões de registros. Os tabelas já estão com os nomes amigáveis definidos. Vamos agora começar a trabalhar com esses dados.

Criação dos relacionamentos

O primeiro passo que devemos realizar, após a importação dos dados, é a criação dos relacionamentos entre as tabelas. Não podemos esquecer que o PowerPivot permite a utilização de dados de diversas fontes de dados. Veremos futuramente no artigo como relacionar os dados que são oriundos de outras fontes de dados. Os relacionamentos darão o poder de criarmos as análises de forma vinculada, entre campos de tabelas diferentes que não estão relacionados, como por exemplo, nome da entidade com quantidade de venda. O relacionamento pode existir entre as tabelas com as chaves primárias, mas não com os campos da análise.

Para criar os relacionamentos, vamos utilizar a guia Table. Nessa guia, temos alguns botões que permitem a criação e manipulação dos relacionamentos:

Figura 14: Guia Table do PowerPivot.

Antes de criarmos os relacionamentos, devemos ter um pequeno conceito de análise/pivoteamento de informação. No caso desse exemplo, devemos indentificar qual é a entidade que possui os “fatos”, conhecida como “fact table”. Fica bem claro, que gostaríamos de realizar algum tipo de análise de venda dessa empresa. Portanto, a tabela Vendas será a nossa fact table. Com base nisso, vamos iniciar os relacionamentos nos campos dessa tabela.

Notem que a tabela possui um dado de identificação única dos registros, que é a coluna SalesKey, e várias outras colunas que, provavelmente, são chaves estrangeiras, como ChannelKey, por exemplo. Caso aplicável, os nomes das colunas podem ser renomeados para um nome mais amigável.

Como existe uma coluna chamada ProductKey, na tabela de Vendas, e possuímos uma tabela chamada Produtos, vamo criar um relacionamento entre essas tabelas com essas colunas. Para, basta clicar na coluna e, na seqüência, clicar no botão Create Relationship:

Figura 15: Criação de relacionamento no PowerPivot.

Figura 16: Criação de relacionamento no PowerPivot – continuação.

Durante a seleção do tabela de Produtos como tabela alvo, o assistente automáticamente detectou que o campo ProductKey é o campo ideal para o relacionamento. Clicando em Create, o relacionamento será criado:

Figura 17: Relacionamento criado.

O novo ícone no campo ProductKey indica que o relacionamento foi criado. Outra forma de visualizarmos é através do botão Manage Relationships. Outros relacionamentos devem ser criados com outros campos, inclusive com outras tabelas. Abaixo segue a lista dos relacionamentos:

Figura 18: Tela de gerenciamento dos relacionamentos.

Importação de dados de outras fontes de dados

Vamos começar a ter uma visão um pouco crítica sobre as informações que o banco de dados disponibilizou. Vejam que existe uma tabela de Produtos e uma tabela de Subcategorias de Produtos. Mas, onde está a tabela de Categorias de Produtos? Ela não está presente. Mas isso é proposital para demonstrarmos a possibilidade de integração de dados, originários de multiplas fontes. As informações de categorias de produtos estão disponíveis em um outro banco de dados Access. Vamos importá-lo, seguindo o mesmo procedimento que fizemos anteriormente:

Figura 18: Iniciando a importação de dados de outro banco de dados Access.

Figura 19: Especificando os parâmetros de conexão com o banco Access.

Nesse momento, vamos utilizar um outro recursos para selecionar os dados desse banco, a escrita de uma SQL query:

Figura 20: Selecionando que será usada uma SQL query para importação dos dados.

Vamos especificar a query para importação dos dados. Aproveitamos para colocar o nome da tabela do PowerPivot como Categorias de Produtos:

Figura 21: Query especificada para importação dos dados de Categorias de Produtos.

Com a query validada, podemos clicar em Finish para importar os dados:

Figura 22: Registros da tabela Categorias de Produtos importados com sucesso.

Vamos aproveitar que estamos trabalhando com importação de dados de outras fontes de dados, e vamos importar os dados da tabela de Estoque que está em um arquivo Excel:

Figura 23: Especificando que os dados serão importados de um arquivo do Excel.

Figura 24: Dados da conexão com o arquivo do Excel especificados e testados.

Vamos especificar a planilha 1, e colocaremos o nome amigável de Estoque:

Figura 25: Planilha 1 selecionada e nome Estoque especificado para a tabela.

Figura 26: Registros importados com sucesso.

Adicionalmente, criaremos novos relacionamentos para essas tabelas. A lista final será a seguinte:

Figura 27: Lista final dos relacionamentos.

Introdução às expressões DAX (Data Analysis eXpressions)

O PowerPivot oferece uma ferramenta poderosa para criação de novas informações, com base nos dados que possuímos. Para isso, usamos as expressões DAX, que são parecidas com as fórmulas regulares do Excel, mas com mais funções. Futuramente, exploraremos com mais detalhes essas expressões. Vamos realizar apenas um pequeno exemplo.

Na tabela de produtos, possuímos um campo chamado TotalCost e um outro chamado de SalesAmount, que são respectivamente, o total de custo e o valor de venda, daquela determinada venda. Vamos criar uma nova coluna, onde apresentaremos qual a porcentagem de lucro dessa venda. Para isso, vamos usa a guia Add Column para criar uma expressão DAX.

Figura 28: Guia Add Column para criação de uma nova coluna, baseada em uma expressão DAX.

Como a expressão valerá para todos os registros, vamos selecionar a nova coluna e criar a expressão, conforme abaixo:

Figura 29: Expressão DAX para criação de uma nova coluna com a porcentagem de lucro do produto.

Figura 30: Expressão DAX criada.

Notem que, para uma melhor visualização, vamos formar esses dados de uma forma mais amigável. A guia Column permite esse trabalho, idêntico aos procedimentos de formatação de células do Excel.

Figura 31: Guia Column para formatação e preparação da coluna.

Vamos colocar o nome da coluna como Porcentagem de Lucro e a formação como porcentagem:

Figura 32: Coluna com expressão DAX renomeada e formatada.

Uma nova coluna também será adicionada e formatada, apresentando o líquido do lucro para cada venda realizada:

Figura 33: Coluna com DAX para lucro líquido.

“Pivoteando” as informações – a análise em si

Vamos para a melhor parte, que é realizar a análise. Uma informação importante é com relação ao desempenho da análise do PowerPivot: toda a análise é realizada em memória, o que significa que os resultados serão apresentados e manipulados rapidamente. Para isso, vamos usa o botão PivotTable, da guia Home. Nela iremos selecionar qual a forma de apresentação (gráficos) que os dados serão apresentados:

Figura 34: Botão PivotTable, com as opções de exibição (gráficos) das análises.

Vamos criar quatro gráficos (Four Charts) para análise. Usaremos a planilha do Excel já existente:

Figura 35: Criação de quatro gráficos para análise, na planilha do Excel já existente.

Pronto! Acabamos de criar a primeira área de gráficos para análise. Caso você esteja no PowerPivot e queira ir para a planilha (durante a criação do gráfico, automaticamente é redicionada para ela), devemos clicar no botão Switch to Workbook, na guia Home do PowerPivot:

Figura 36: Botão para retorno ao Excel, para visualizar os gráficos.

Quatro gráficos foram criados. Cada um deles serão utilizados para uma análise diferente. Esses gráficos poderão ser formatados com os mesmos recursos do Excel.

Figura 37: Gráficos criados.

O recurso novo é a Task Pane chamada Gemini. É com ela que criaremos as nossas análises. Para isso, vamos selecionar o primeiro gráfico. Nesse exemplo, vamos criar uma gráfico que apresenta as quantidade de venda por categoria de produto. Para isso, deveremos colocar o campo ProductCategories, da tabela Categorias de Produtos, em Axis Fields, e o campo SalesQuantity, da tabela Vendas, em Values. A análise é criada no gráfico:

Figura 38: Análise de Quantidade de Vendas x Categorias de Produtos criada.

Usando os recursos do Excel, podemos melhorar a visualização do gráfico:

Figura 39: Gráfico 1 formatado.

Mas vocês podem estar questionando o seguinte: trabalhamos tudo isso para gerarmos um relatório, que poderíamos fazer no Reporting Services ou até mesmo no Excel, sem o PowerPivot? Poderiam, mas notem que, por padrão, já possuímos uma possbilidade de modificação dinâmica, clicando no botão ProductCategoryName. Se clicarmos no mesmo, podemos optimizar dinamicamente a nossa análise, como por exemplo, analisar somente a venda de computadores:

Figura 40: Todas as categorias de produtos selecionadas.

Figura 41: Filtrando somente por computadores.

Figura 42: Gráfico alterado dinamicamente.

Mas isso não é tudo. Podemos ainda ter mais dinamismo “fatiando” os dados verticalmente ou horizontalmente. Isso pode ser feito, na Gemini Task Panel, através da inserção dos campos em Slicers Vertical e Slicers Horizontal. Vamos dar a possibilidade de “fatiar” os dados verticalmente por ano, e horizontalmente por entidade. Coloque o campo YearLabel, da tabela Datas, em Slicers Vertical, e o campo EntityName, da tabela Entidades, em Slicers Horizontal:

Figura 43: Criando os Slicers verticais e horizontais.

Figura 44: Nova forma de visualização dos dados, com filtros verticais e horizontais.

Esse é o chamado “Power” do PowerPivot, já que estmos lidando com milhões de registros. Com apenas alguns cliques vamos otimizar o relatório dinamicamente, filtrando por categoria, ano e entidade. O resultado é processado em segundos:

Figura 45: Gráfico 1 com os dados filtrados por categoria, ano e entidade.

Para finalizar, vamos inserir algumas outras informações nos outros gráficos e formatá-las. Utilizaremos alguns outros filtros verticais e horizontais, resultando em um painel de vendas completo:

Figura 46: Painel de vendas da Contoso Corporation.

Pronto! Estamos com as nossas análises de vendas dinâmicas criadas, sem termos a necessidade de usarmos qualquer conhecimento complexo de BI ou codificação. O passo final será publicá-la no SharePoint. Mas isso será tratado nos próximos artigos.

Conclusão

Nesse artigo, exploramos os conceitos básicos sobre o PowerPivot for Excel e demonstramos os mesmos através da criação de análises, com o Excel 2010. Podemos comprovar a possibilidade de importação de dados de múltiplas fontes de dados, a flexibilidade de criação de novas informações com as expressões DAX e facilidade de criação das análises de forma dinâmica, usando apenas alguns cliques.

Com isso, podemos concluir que o PowerPivot é uma ferramenta poderosa e que o conceito de Self-Service BI da Microsoft deverá ser amplamente utilizado pelas organizações, devido à sua usabilidade, facilidade e flexibilidade. Nos próximos artigos, iremos explorar como poderemos publicar as análises criadas, com o PowerPivot for SharePoint.

Até mais,

Carlos Monteiro.

Tags: BI, Business Intelligence, powerpivot, PowerPivot Excel, PowerPivot for Excel, PowerPivot for SharePoint, PowerPivot SharePoint, Self-Service BI, SQL Server 2008 R2