Clique aqui para instalar o Silverlight*
BrasilAlterar|Todos os sites da Microsoft
MSDN
|Developer Centers|Biblioteca|Downloads|Assinaturas MSDN
Pesquisa rápida


PivotTables - Visualizando dados multidimensionais com OWC

Por Igor Raphael Alcantara

Este artigo discuteEste artigo usa as seguintes tecnologias

O que são visões multidimensionais e cubos;

Importando controles ActiveX / COM para uso no Visual Studio;

O que são Microsoft Web Components;

Cubos em aplicações Windows Forms com PivotTables.

Visual Studio 2005, VB.NET.

Houve um tempo em que os softwares usados pelas empresas se resumiam a armazenar o histórico de atividades realizadas, não passando de uma versão digital dos velhos arquivos que ocupavam espaço precioso nos escritórios. A função destes softwares era mais de auditoria do que de planejamento.

Além disso, o pensamento era extremamente linear. Ao se analisar as vendas, por exemplo, o mais comum eram relatórios de vendas por região, vendas por cliente, vendas por produto e assim por diante. Ou seja, os dados eram vistos apenas de forma bi-dimensional.

O que são visões multidimensionais

As pessoas responsáveis pela tomada de decisões em uma empresa precisam de mais dinamismo na obtenção de relatórios ou consultas, o que muitas vezes exige mais do que as antigas visões bidimensionais oferecem. Ao invés de um relatório com apenas vendas por produto, por exemplo, é mais interessante analisar as vendas por produto (organizado e sumarizado por categorias) e região no decorrer do tempo (que pode ser ao mesmo tempo dia, mês, trimestre, semestre, ano etc.). Neste caso não falamos mais de duas dimensões e sim de várias, daí o nome visões multidimensionais, que não mais são usadas somente como registro histórico dos acontecimentos, mas principalmente para prever tendências e planejar estratégias.

Uma visão multidimensional também pode ser chamada de Cubo, uma metáfora que ajuda a entender a disposição de linhas e colunas desse tipo de visão. Em um cubo, várias operações são permitidas, como Drill Down (aumentar o nível de detalhes, expandindo uma região e vendo as cidades, por exemplo), Drill Up (diminuir o nível de detalhes, como ao agrupar dias em meses), Drill Across (muda-se o nível de detalhes sem necessariamente obedecer a hierarquia, ao sair de um ano para analisar diretamente meses, sem passar pelos trimestres), Slice And Dice (ação de rearranjar linhas e colunas de um cubo, mudando sua visão), entre outras.

Utilizando os Microsoft Office Web Components

Todos os desenvolvedores conhecem as facilidades das ferramentas do Microsoft Excel, como planilhas e gráficos, mas poucos sabem que existe uma suite de componentes ActiveX que permite que essas funcionalidades sejam disponibilizadas em suas aplicações. A este conjunto de componentes damos o nome de Microsoft Office Web Components (OWC). Os componentes do OWC mais usados são Chart (gráficos), Spreadsheet (planilha semelhante ao Excel) e o assunto principal de nosso artigo, a PivotTable.

O OWC pode ser encontrado no CD de instalação do Microsoft Office ou pode ser baixado no site da Microsoft. Existem várias versões dos OWC, cada uma equivalente a uma versão do Microsoft Office. No exemplo deste artigo foi usado o OWC 11, equivalente ao Microsoft Office 2003.

Seu uso não está necessariamente vinculado a uma licença do Microsoft Office, mas caso o usuário da aplicação não a tenha, terá acesso apenas a algumas funcionalidades de cada componente.

Nota: Apesar do nome, os Microsoft Office Web Components não se restringem ao ambiente da Internet e também podem ser usados em aplicações Windows Forms, como veremos neste artigo.

PivotTable é um componente que permite a visualização de dados de modo multidimensional, conforme mostra a Figura 1. As seguintes partes compõem uma PivotTable: Área de Filtro, Área de Linhas, Área de Colunas e Área de Dados.


Figura 1. PivotTable montada em uma aplicação Windows Forms

Iniciando uma aplicação com PivotTables

Após instalado o OWC, você já está apto a iniciar sua primeira aplicação usando PivotTables. O primeiro passo é iniciar um novo projeto Windows Forms. Com o projeto criado, é hora de adicionar referência ao componente PivotTable. Para isso, clique com o botão direito do mouse na Toolbox e escolha Choose Items. A caixa de diálogo Choose Toolbox Items será aberta. Mude para a tab COM e marque a opção Microsoft Office PivotTable e clique em OK. Um número de versão aparecerá à frente do nome do componente, aconselho a escolher a versão equivalente ao Microsoft Office instalado (versão 11 para o MS Office 2003, 10 para o MS Office XP e assim por diante). Para este artigo, marque também o Microsoft Office Chart.

Agora a Toolbox possui um item novo, que é exatamente o PivotTable, além do Chart. Crie um formulário novo e arraste o componente PivotTable para ele, você verá algo como mostrado na Figura 2.


Figura 2. Formulário com PivotTable

Antes de rodarmos nossa aplicação, vamos alterar algumas propriedades, conforme visto na Tabela 1, de modo a tirar mais proveito da PivotTable. Dentre as propriedades, vale destaque a AutoFit, que diz se o tamanho do controle será proporcional aos dados que ele contém.

PropriedadeNovo Valor

Name

owcPivotTable

Anchor

Top, Bottom, Left, Right

AutoFit

False

Tabela 1. Propriedades a serem alteradas na PivotTable

Codificando a aplicação

Se o projeto for compilado e executado neste estágio, nada poderá ser feito pois falta uma informação fundamental para nossa PivotTable: a fonte de dados, claro. Isso pode ser facilmente definido clicando-se no link Clique aqui para se conectar aos dados, mas essa é uma alteração em tempo de design feita pelo programador. O que é realmente interessante é dar a opção ao usuário de escolher ou alterar a fonte de dados, mas isso exige a escrita de um pouco de código.

Neste exemplo, vamos definir que no evento DblClick da PivotTable uma caixa de diálogo para escolher a fonte de dados será aberta ao usuário, o que é mostrado na Listagem 1 (código em VB.NET). É importante ressaltar que, devido a um bug existente nas versões anteriores, vários eventos da PivotTable como o DblClick somente funcionarão em um Windows Form se você desenvolver sua aplicação no Visual Studio 2005.

Listagem 1. Escolhendo a fonte de dados

Public Class Form1

    Private Sub owcPivotTable_DblClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles 
	owcPivotTable.DblClick

        Try

            ' Escolhe a fonte de dados
            owcPivotTable.LocateDataSource()

        Catch ex As Exception
            MessageBox.Show("Um erro ocorreu." & vbCrLf & ex.Message)

        End Try

    End Sub


End Class

Rodando a aplicação e dando um duplo clique do mouse sobre a PivotTable, você verá que uma caixa de diálogo se abrirá onde você deverá escolher com que tipo de fonte de dados irá conectar o controle. Escolha alguma das existentes, siga os passos do Wizard e note que a PivotTable mudou de aparência.

Através da barra de ferramentas da PivotTable, abra a lista de campos (Field List) e arraste os campos para as respectivas áreas do controle (as áreas existentes são mostradas na Figura 1), sendo que na área de dados, é importante que o tipo de dado seja numérico, caso contrário a PivotTable mostrará apenas uma contagem da quantidade de registros.

Um recurso bem interessante de uma PivotTable é que você pode estabelecer hierarquias entre suas dimensões e o controle faz todo o agrupamento necessário e somatórios automaticamente. No exemplo mostrado na Figura 1, baseado no cubo Sales do banco de dados Foodmart do Analysis Services, foram arrastadas as colunas Product Family, Product Department, Product Category, Brand Name e Product Name para a área de linhas. Se o campo é do tipo data, a PivotTable automaticamente cria as hierarquias Ano, Trimestre (quarter), mês e dia.

Salvando e abrindo a estrutura da PivotTable

Criar uma visão multidimensional em uma PivotTable pode levar certo tempo dependendo da quantidade de campos e possibilidades. Após todo esse trabalho, a última coisa que o usuário quer é perder o que foi feito. Para evitar isso, é necessário criar um método para salvar a estrutura da PivotTable. Felizmente essa é uma tarefa das mais fáceis graças à propriedade XMLData que retorna ou recebe toda a estrutura da PivotTable em formato XML.

Como mostrado na Figura 3, foi criada uma barra de ferramentas (neste caso uma ToolStrip) com três botões: Novo, Abrir e Salvar. Para permitir a abertura e salvamento de sua PivotTable, adicione os componentes OpenFileDialog e SaveFileDialog e mude seus nomes respectivamente para ofdAbrirPivotTable e sfdSalvarPivotTable.


Figura 3. Formulário com barra de ferramentas

Para a ação Novo, devemos limpar o conteúdo da PivotTable e desconectar a mesma da fonte de dados. Uma das formas de se fazer isso é restaurar a estrutura original em XML da PivotTable, que é capturada ao carregar o formulário e aplicada ao clicar-se no botão Novo. Já para as ações de Abrir e Salvar, grava-se ou lê-se um arquivo XML e associa-se seu conteúdo à PivotTable. Todas essas ações são demonstradas na Listagem 2.

Listagem 2. Ações de Novo, Abrir e Salvar

  Private strEstruturaBasica As String
    Private strArquivoXML As String
    Private oXML As System.Xml.XmlDocument

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) 
	Handles MyBase.Load

        strEstruturaBasica = owcPivotTable.XMLData
        ofdAbrirPivotTable.Filter = "Arquivos XML (*.xml)|*.xml"
        sfdSalvarPivotTable.Filter = "Arquivos XML (*.xml)|*.xml"

    End Sub


    Private Sub tsbNovo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 
	Handles tsbNovo.Click

        strArquivoXML = ""
        owcPivotTable.XMLData = strEstruturaBasica

    End Sub

    Private Sub tsbAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 
	Handles tsbAbrir.Click

        Try

            ofdAbrirPivotTable.ShowDialog()
            oXML = New System.Xml.XmlDocument
            strArquivoXML = ofdAbrirPivotTable.FileName.ToString

            If ofdAbrirPivotTable.CheckFileExists Then
                oXML.Load(strArquivoXML)
                owcPivotTable.XMLData = oXML.OuterXml
            End If

        Catch ex As Exception
            MessageBox.Show("Um erro ocorreu." & vbCrLf & ex.Message)

        End Try

    End Sub

    Private Sub tsbSalvar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 
	Handles tsbSalvar.Click

        Try

            oXML = New System.Xml.XmlDocument
            oXML.LoadXml(owcPivotTable.XMLData)

            If strArquivoXML.Length = 0 Then
                sfdSalvarPivotTable.ShowDialog()
                strArquivoXML = sfdSalvarPivotTable.FileName
            End If

            If strArquivoXML.Length > 0 Then
                oXML.Save(strArquivoXML)
            End If

        Catch ex As Exception
            MessageBox.Show("Um erro ocorreu." & vbCrLf & ex.Message)

        End Try

    End Sub

Gráficos

Em uma aplicação deste tipo, além de simplesmente abrir e salvar consultas, existem outras ações que podem ser igualmente importantes. Por que não inserir no formulário um gráfico que esteja automaticamente vinculado à PivotTable e que mude a visão de acordo com a PivotTable? Para isso, acrescente um Chart ao seu formulário, chame-o de owcGrafico e use o código da Listagem 3 para vinculá-los (chame esse método no Form_Load). Veja um exemplo de uso na Figura 4.

Listagem 3. Vinculando um gráfico a uma PivotTable

Private Sub VincularGrafico()
	owcGrafico.DataSource = CType(owcPivotTable.GetOcx(), msdatasrc.DataSource)
End Sub


Figura 4. Gráfico usando OWC

PivotTable e consultas MDX

Quando a fonte de dados da PivotTable é um banco de dados relacional, o componente PivotTable precisa realizar diversas sumarizações e agrupamentos em memória para conseguir transformar a visão bidimensional em multidimensional. O problema neste caso é que a aplicação pode ficar muito lenta. O ideal é que a fonte de dados seja multidimensional. Neste caso, é aconselhável usar o Microsoft Analysis Services, produto que é comprado juntamente com o Microsoft SQL Server mas que deve ser instalado separadamente. Através do Analysis Services, pode-se construir cubos que já conterão todas as agregações necessárias e fazer destes cubos a fonte de dados da PivotTable.

Ao analisar um cubo, uma ação que todo tomador de decisões deseja é poder alterar um valor em um nível e ver imediatamente seu resultado. Para entender melhor essa necessidade, imagine um cubo que mostre a previsão de faturamento da empresa para o próximo ano.

Analisando os dados, o gerente pode querer fazer a simulação de que metas precisaria atingir a cada mês e trimestre para cada unidade e região se aumentasse em R$ 2.000.000,00 a previsão de faturamento. Para isso, o gerente alteraria o valor total do ano e todos os cálculos seriam feitos de modo que os valores derivados também sejam alterados proporcionalmente. Isso pode parecer simples, mas pensando-se em uma visão multidimensional com diversas agregações, é uma tarefa complexa demais para ser feita manualmente. A esta ação dá-se o nome de write-back e para fazê-la, deve-se usar expressões MDX (Multidimensional Expressions - uma espécie de SQL para dados multidimensionais). Não abordarei este assunto neste artigo, pois isso por si só seria tema para vários outros artigos, porém achei importante citar aqui.

Exemplo com banco Northwhind

Tomando como exemplo o banco Northwind, vamos ver como construir uma visão multidimensional em uma PivotTable. Tendo a aplicação deste artigo pronta, execute-a e clique duas vezes sobre a PivotTable para abrir a caixa de diálogo Selecionar Fonte de Dados. Você verá uma lista de conexões previamente criadas (que estão armazenada em Meus Documentos/Minhas Fontes de Dados). Clique sobre a opção Nova Conexão do SQL Server. Um wizard será aberto. Digite o nome do servidor SQL Server desejado, como por exemplo (local), informe o tipo de autenticação e clique em Próximo.

Na próxima tela, escolha o banco de dados Northwind, uma lista com as tabelas do banco serão mostradas na ListView logo abaixo. Escolha a tabela Orders e clique em Concluir. Neste ponto a PivotTable se encontra preparada para ser montada, clique com o botão direito sobre ela e escolha Lista de Campos, uma nova janela com todos os campos da tabela escolhida será mostrada. Selecione ShipCountry, no combobox escolha Área de Linhas e clique em Adicionar. Repita a mesma operação para os campos ShipRegion e ShipCity. Você verá esses três campos adicionados na lateral da PivotTable numa estrutura hierárquica (regiões dentro de países e cidades dentro de regiões).

Um recurso interessante da PivotTable é que campos do tipo data são automaticamente agrupados em meses e semanas, conforme comentei anteriormente. Note que o campo ShippedDate possui outros dois campos chamados ShippedDate por Semana e ShippedDate por Mês, criados automaticamente. Escolha este último, defina o combobox como Área de Colunas e clique em Adicionar. Você verá a data agrupada em quatro níveis: ano, trimestre, mês e dia.

Chegou então a hora de exibir a medida que será analisada. A medida normalmente é um valor numérico e fica contida na Área de Dados. Se você fizer um teste e selecionar o campo OrderID e adicioná-lo na Área de Dados, verá que automaticamente este campo será somado, pois OrderID é um campo numérico. Neste caso, queremos mostrar não a soma mas sim a quantidade de vendas realizadas. Para isso, na barra de ferramentas clique sobre o ícone da calculadora e, dentre as duas opções que aparecem escolha Criar Campo de Detalhes Calculado. Uma nova janela de fórmulas será aberta. Em Nome, escreva Qtde de Vendas e na área de fórmulas digite OrderID e clique em Alterar. Ainda sem fechar a janela clique sobre o ícone de somatória na barra de ferramentas da PivotTable (botão Auto Cálculo) e escolha Cont. Num. Pronto, sua PivotTable que mostra o histórico da quantidade de vendas por país, região e cidade está pronta! (Figura 5) Aconselho selecionar cada parte da PivotTable, como linhas e colunas, e explorar as propriedades como formato de dados, legenda, cores etc.


Figura 5. Exemplo da PivotTable com banco Northwind do SQL Server

Conclusões

O objetivo deste artigo foi dar uma visão geral das visões multidimensionais, suas aplicações práticas e de que modo implementar uma aplicação front-end para exibir esses dados. As funcionalidades de uma PivotTable não se restringem a aplicações Windows e Web Forms, podendo ser acessíveis também em planilhas Excel e em portais Sharepoint. No entanto, mais importante do que o desenvolvimento, é entender as reais necessidades do cliente e que recursos agregam valor e tornam a tomada de decisões ágil e precisa.

Links

http://www.microsoft.com/downloads/details.aspx?FamilyID=7287252c-402e-4f72-97a5-e0fd290d4b76&DisplayLang=pt-br
Download do Microsoft Office Web Components 11

Igor Raphael Alcantara (irarevolution@hotmail.com) é Tecnólogo em Administração de Banco de Dados pela Faculdade IBTA, desenvolvedor a mais de 10 anos, fundador e líder do MUG SJC, analista e gerente de projetos atualmente trabalhando com soluções de Business Inteligence, customizações e implantações de ERPs em clientes no Brasil e principalmente no exterior.


 

©2017 Microsoft Corporation. Todos os direitos reservados. Entre em contato |Nota Legal |Marcas comerciais |Política de Privacidade
Microsoft