Thiago Zavaschi R2 www.zavaschi.com

Novidades do SQL Server 2008 R2 – Parte III – Iniciando com o PowerPivot for Excel 2010

Olá pessoal,

No artigo anterior (http://zavaschi.com/index.php/2009/11/novidades-do-sql-server-2008-r2-parte-ii-powerpivot-conceitos/) eu introduzi os conceitos relacionados ao self-service BI, mais precisamente os relacionados ao PowerPivot. Consulte-os quando surgirem dúvidas. :)

Apenas lembrando, PowerPivot é uma grande promessa de Self-Service BI real da Microsoft (“Mas e o Report Builder?” - Calma, depois comento sobre ele), anteriormente era conhecido como projeto Gemini. E ele é divido basicamente em PowerPivot for Excel 2010 e PowerPivot for SharePoint 2010.

O PowerPivot for Excel 2010, como veremos, é muito simples de usar e possui poderosa capacidade de análise.

“Ok Thiago, este nome significa que nós o usaremos de dentro do Excel 2010, certo? O quão rápida essa solução é?”

Sim, o uso (composição da análise) será de dentro do Microsoft Excel 2010. Isto é devido ao fato de que o Excel é a ferramenta mais usada e familiar para as pessoas que usualmente tomam as decisões ou que fazem análise. Isso já ocorre atualmente com o uso de tabelas dinâmicas associadas a cubos do Analysis Services, ou através dos plugins de Data Mining para o Excel. Nós normalmente temos uma quantidade muito grande de dados para analizar, oq ue pode ser um processo caro computacionalmente se não for otimizado, mas com o novo PowerPivot for Excel nós podemos analisar grandes quantidades de dados de uam forma bem rápida.

Aqui cabem algumas ressalvas: o comportamento em ambientes cuja arquitetura é x64 é melhor que x86 para trabalhar com o PowerPivot (desempenho). O PowerPivot faz operações em memória então pode ser necessário um hardware que acompanhe, porém como eu disse, ele está otimizado e consegue trabalhar com enorme quantidade de dados (dezenas de milhões de linhas) fácil e rapidamente.

O PowerPivot é muito mais poderoso do que simplesmente utilizar tabelas dinâmicas com cubos SSAS (na série de artigos que estou iniciando sobre PowerPivot veremos com detalhes todo o poder disponível). A análise gerada pelo Excel pode ser publicada no SharePoint 2010 e assim ter esses dados disponíveis a todos (entenda-se todos como aqueles que possuem permissões para acessar essa área no SharePoint).

O que você pode estar pensado é que a planilha com a análise uma vez no servidor pode ficar vulnerável e desatualizada. Ou você pode estar pensando neste exato momente “Ih.. é mesmo, então o PowerPivot não é legal, certo?!”.. ERRADO!!!

Você pode fazer (agendar) com que a planilha se atualize sozinha no servidor, além de ter o Excel Services (componente do SharePoint 2010) que torna possível fazer pequenas alterações na planilha para visualização momentânea (mas considere a planilha como sendo sempre read-only, após publicada, isso garante a consistência dos dados).

Mas a integração com o SharePoint 2010 é tema de um próximo post. (:

“Ok Thiago, você me convenceu. Como começo? O que eu preciso para trabalhar com o PowerPivot for Excel?”

Tudo o que você vai precisar já está disponível publicamente, seguem os links para download:

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

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

PowerPivot for Excel 2010 (certifique-se de pegar o plugin correspondente a arquitetura que o seu Office está instalado, pois você pode ter um sistema com arquitetura x64 e ter botado o Office x86): http://powerpivot.com/download.aspx

Instale o Microsoft Office 2010, o SQL Server 2008 R2 (Nov CTP) e então o plugin para o  PowerPivot for Excel. No site oficial do PowerPivot (www.powerpivot.com) também há um guia (em inglês) para o processo de instalação se acharem necessário. O processo é bem tranquilo, até eu consegui! :)

“Bom Thiago eu tenho tudo instalado e agora vou analizar .... Vou analizar o que?”

Com o PowerPivot nós podemos usar uma série de recursos como fontes de dados: cubos SSAS, tabelas do excel, SQL Server, Oracle e assim por diante. o seguinte site contém diversas bases de dados que você pode utilizar como fonte para os seus experimentos: http://www.powerpivot-info.com/post/50-list-suggested-datasets-to-test-powerpivot.

Eu irei usar para os experimentos deste post a seguinte base de dados (em Access): http://powerpivotsampledata.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=35438.

Agora com tudo instalado e um conjunto de dados, podemos começar a diversão!

Primeras Impressões

A primeira coisa que você irá perceber é uma nova aba no Excel, denominada PowerPivot (conforme a figura 1):

Figura 1: A nova aba no Microsoft Excel 2010.

Nesta aba nós temos seis grupos: Launch. Measures, View, Excel Data, Help e Show/Hide.

O início de tudo se dá no grupo Launch, mais precisamente no botão PowerPivot Window presente na Ribbon. A figura 2 mostra a janela que deverá aparecer para você.

Figura 2: A janela do PowerPivot

Nós não temos nenhum dado carregado ainda. O que nós temos basicamente habilitados são os botões de recuperação dos dados, listar/configurar as conexões existentes. Você também tem a opçõ de retornar para a janela principal do Excel.

Antes de importar os dados da fonte que mencionei anteriormente, vamos ver quais são os tipos de fontes de dados que podemos utilizar. Inicialmente você irá ver os tipos mais comuns de fontes de dados, mas você pode clicar em “From Other Sources” para ver a grande gama que temos disponível. E são eles (pelo menos no CTP de novembro do SQL Server 2008 R2):

• Microsoft SQL Server
• Microsoft SQL Azure
• Microsoft Access
• Microsoft SQL Server Analysis Services
• PowerPivot workbooks published to Analysis Services running in SharePoint Integrated Mode
• Oracle
• Teradata
• Sybase
• Informix
• DB2
• DataFeeds
• Excel workbooks
• Text files

Você pode conectar também com qualquer fonte de dados que seja compatível com conexões ODBC ou OLEDB.

Na minha opinião, a possibilidade de usar informação proveniente de um cubo SSAS com outras fontes de informação (por exemplo) é fantástica! Esta capacidade é muitas vezes mais poderosa doq ue simplesmente utilizar as tabelas dinâmicas com cubos SSAS.

Figura 3: Fontes de Dados Disponíveis.

Fonte de dados de Exemplo

A fonte de dados de exemplo contém quatro arquivos:

• AccessQuery.txt - contém uma query de exemplo pra executar contra o banco fornecido no pacote também. Utilizaremos em nosso exemplo.
• AW_Companysales.accdb e IndustryBikeSales.accdb - que são bases de exemplo em Access que estão disponíveis para testes.
• Country.xlsx - que é um arquivo excel simples que contém um mapeamento sigla-país.

Alimentando o PowePivot

Clique em “From Database” e selecione “From Access”, selecione a base de dados IndustryBikeSales. Preencha o nome com um nome amigável ( :) ). esta base de dados não necessita de login e senha, deixe estes campos em branco.

Na próxima janela selecione a opção que deixe você escrever a sua query customizada. Coloque a seguinte query (é a fornecida nos arquivos mencionados anteriormente) e clique no botão Import:

SELECT IndustrySales.Country, IndustrySales.Quarter,
IndustrySales.[Type Of Bike], IndustrySales.Channel,
IndustrySales.Units
FROM IndustrySales
WHERE ( (IndustrySales.Quarter >='2001 Q3')
AND (IndustrySales.Quarter<='2009 Q2') )

A sua janela do PowerPivot deve estar parecida com a figura 4:

Figura 4: Dados resultantes da query sobre a base IndustryBikeSales

Nós temos as informações sobre as vendas por trimestre, divididas por tipo de produto (bicicleta no caso). Vocês devem concordar comigo que é um pouco confuso tomar desições através da análise direta destes dados (1152 linhas). Não seria muito bom se nós pudéssemos simplesmente organizar o total de vendas por tipo, ou por país (trimestre etc.) de uma maneira rápida e com poucos cliques?

Nós podemos fazer isso utilizando o PowerPivot for Excel, com pousos esforços podemos extrair muita informação. Você pode adicionar fórmlas do excel, expressões DAX, criando novas colunas baseadas nos seus dados (sobre isso teremos novos posts) para ter mais subsídios para a sua análise.

na figura 4 você pode observar que nós temos vários outros botões habilitados: Refresh, Manual e PivotTable.

O botão “Refresh” importa novamente os dados das fontes. É como qualquer outro botão de atualizar :). O botão PivotTable é o próximo botão que você tem que olhar. Clicar nele irá te mostrar algo similar a figura 5.

Figura 5: Menu PivotTable

Quando você selecionar um destes itens, o Exel irá perguntar onde você quer criar o template: num novo worksheet ou em um existente. Independente do que você escolher, o template será criado pelo PowerPivot. O seu Microsoft Excel 2010 deverá estar parecido com a figura 6. Para o meu exemplo usei a opção: Four Charts (quatro gráficos).

Figura 6: O template com os gráficos gerados pelo PowerPivot

Eu selecionei para adicionar em um workbook existente (”Sheet 1”). agora nós temos algumas coisas que você provavelmente (espero, hehe) percebeu: nós temos 4 abas novas (Data for Sheet 1 Chart 1, Data for Sheet 1 Chart 2, ... e assim por diante) e quando você clica em um destes gráficos você verá o grupo Pivot Chart Toolsna ribbon (figura 7) e o Gemini Task Pane (figura 8). Eu não vou entrar em detalhes sobre a ribbon, a maioria das opções são sobre formatações e coisas do gênero.

Ess painel do Gemini é familiar pra você? Eu acho que é muito parecido com o painel de tabela dinâmica do Excel, mas com os slicers (veremos a seguir).

Figura 7: PivotChart Tools: Design

Figura 8: Gemini Task Pane

Nós agora iremos selecionar alguns dados para popular os relatóios. Você pode simplesmente marcar as checkboxes ou arrastar e soltar na região que desejar: Legend Fields, Axis fields, Values ou filtrar alguma informação. Você também pode usar os slicers. Os slicers são como filtros que você pode ligá-los ou desligá-los apenas clicando neles! Vamos relembrar que o PowerPivot é voltado aos gerentes, diretores e tomadores de decisão, então este comportamento é realmente útil.

Após simplesmente adicionar algumas colunas aos gráficos e adicionado o Trimestre (Quarter) e País (Country) como slicers (com, realmente, poucos cliques) nós temos algo parecido com a figura 9.

Figura 9: Dashboard sobre os dados sendo analisados com o PowerPivot!!! Criado com pouquíssimos cliques!

Olhe na parte esquerda da figura 9. Estes são os slicers que comentei anteriormente. eu facilmente selecionei apenas as informações de 2003 e 2004 e excluí da análise os Estados Unidos. Se o desejo fosse analisar Estados Unidos x Alemanha, por exemplo, eu poderia simplesmente clicar nos slicers correspondentes e não ateraria nada nos gráficos! Eu também tenho que dizer que essa operação é extremamente rápida, mesmo quando tivermos milhões de linhas.

A última coisa que você tem que olhar agora é nas outras abas criadas pelo PowerPivot. Os dados parecem familiares? Estes são os dados que estão sendo usados na criação dos ráficos apresentados na dashboard.

O próximo passo é compartilhar a análise feta no Excel, para evitar duplicamento de informações e manter tudo em um ponto central. O PowerPivot for SharePoint vem somar recursos às capacidades grande que já temos (veremos com mais detalhes em artigos futuros)!

Bom, isso é tudo que eu queria mostrar hoje pessoal. E essa é só a ponta deste iceberg chamado PowerPivot. Eu irei mostrar features mais avançadas e a integração com o SharePoint 2010 (múltiplas fontes simultâneas, DAX) no próximo artigo!

Abraços a todos!

Thiago Zavaschi