Trabalhando com OData e Excel

Devido à grande quantidade de informações que são geradas constantemente nos nossos dias atuais, tem-se que estas informações, tidas como dados. No entanto, estes dados, só tem algum valor se ele puder ser usado. Para que os dados possam ser acessados, é utilizado um protocolo, conhecido por OData. Neste artigo, veremos de forma introdutória como podemos utilizar o OData, descrevendo o que é e como ele surgiu, sua aplicação, o porquê da sua importância e como podemos utilizá-lo.

Ao trabalharmos com o OData, temos que um dos seus objetivos principais é a criação de uma padronização em como os dados serão acessados e manipulados através da rede. Hoje a disponibilidade de informações é muito grande, onde temos informações sendo processadas através de aplicações nas nuvens, armazenadas em bancos de dados de empresas, navegadores web, Business Intelligence, dentre muitas outras fontes de dados. Como podemos observar, muitos são os meios de acesso a dados, e para a utilização correta destes dados, é necessária a utilização de um protocolo para acessar estes dados. E é neste ponto que entra o OData! O OData define um modelo de dados abstrato e um protocolo que deixa qualquer informação de acesso de cliente ser exposto por qualquer fonte de dados. A ideia base para o OData é que qualquer cliente possa acessar qualquer fonte de dados. Ao invés de criar maneiras exclusivas para a exposição e acesso aos dados.

O objetivo do Open Data Protocol, conhecido como OData, é o fornecimento de um protocolo baseado em REST para a realização de operações CRUD. O OData foi publicado pela Microsoft sob a OASIS, Organização para o Avanço de Padrões de Informação Estruturada, padrão este que permite a construção de servidores ou ferramentas por qualquer um que tenha interesse sem a necessidade de royalties ou restrições.

Quando trabalhamos com o OData, temos a nossa disposição uma estrutura completa de metadados baseada na fonte de dados que estará sendo utilizada, onde para isso, utilizamos a variável $metadata. Com base nesta variável, podemos ter a nossa disposição uma estrutura completa dos dados que foram disponibilizados a partir do OData, assim como os tipos de dados presentes, seus relacionamentos, dentre outras informações.

O Documento de Serviço Metadados descreve os dados em termos EDM (especificação utilizada para definir os dados que são usados por aplicativos que são construídos com o Entity Framework), Entity Data Model, utilizando uma linguagem XML para descrever os modelos chamados de Conceptual Schema Definition Language (CSDL). Quando expostos por um serviço OData como um Documento de Serviço Metadados, o documento CSDL é empacotador utilizando-se o formato descrito em EDMX, que é um formato de arquivo baseado em XML, onde este é o formato utilizado para o empacotamento dos metadados de um serviço de dados.

Para que possamos entender melhor o OData, criaremos um exemplo para recuperarmos os dados de uma base de dados e apresentá-los numa planilha Excel. Em seguida, utilizaremos a linguagem R para criar uma apresentação gráfica dos dados apresentados.

Linguagem R

A Linguagem R é uma linguagem de programação e também um ambiente de desenvolvimento integrado para cálculos estatísticos e gráficos. Ela foi criada inicialmente por Ross Ihaka e Robert Gentleman na Nova Zelândia, no departamento de estatística pela universidade de Auckland. O R é altamente expansível com a utilização de pacotes, que nada mais são que bibliotecas para funções específicas ou mesmo para áreas de estudo mais aprofundados, de forma que elas não são desenvolvidas para uso geral, como podemos citar um exemplo referente ao pacote analyz, que é um Modelo de Análise da camada de Dados automática com base na interpretação de arquivos CSV. Assim como este pacote, podemos encontrar muitos outros disponibilizados através do site CRAN (Comprehensive R Archive Network) através do link disponível no fim do artigo, onde constam todos os pacotes desenvolvidos. A linguagem de programação R é bastante utilizada por estatísticos e profissionais de mineração de dados para o desenvolvimento de softwares estatísticos e análise de dados, devido a facilidade e a simplicidade com a qual os dados são dispostos.

A linguagem R é uma linguagem de programação dinamicamente tipada, de forma que podemos modificar os tipos dos dados contidos nas variáveis em tempo de execução, o que nos resulta uma melhoria na hora de programarmos, onde não precisaremos nos ater a realização de conversões de dados. A maior utilização encontrada para a linguagem R é com relação a manipulação de conjuntos de dados de tamanho médio a grande, análises estatísticas e produção de documentos e apresentações centradas em dados. Além disso, nos fornece uma ampla variedade de modelagens lineares e não lineares, testes estatísticos clássicos e análise de séries temporais. Um dos pontos fortes do R é a facilidade com a qual podemos uma melhor qualidade em relação a plotagens bem desenhadas, a inclusão de símbolos matemáticos e fórmulas, já que o R é um conjunto integrado de facilidades de software voltado para manipulação de dados e exibição gráfica.

Entendendo um pouco mais sobre a linguagem R

Para trabalharmos com a linguagem R, iremos instalá-lo em nossa máquina, e para isso, basta acessarmos o site (vide seção Links) onde temos a nossa disposição uma ferramenta mais manual para trabalharmos e o RStudio, onde este último que é o que iremos utilizar, apresenta um design mais amigável.

Com o intuito de um melhor aproveitamento em relação ao entendimento do funcionamento do R, trataremos aqui algumas de suas funções básicas, assim como alguns de seus pacotes mais utilizados. Ao instalarmos o R, já temos a nossa disposição uma grande quantidade de pacotes pré-instalados por padrão, mas ainda assim, precisaremos adicionar pacotes mais específicos, o que podemos fazer de duas possíveis formas, onde uma é através de linhas de comando, ou acessando o link “pacotes” presente no menu superior e, em seguida, selecionando a opção de “instalar pacotes”. Ao tentarmos instalar um novo pacote, antes é apresentada uma janela na qual temos os espelhos de onde podemos realizar o download. Basta selecionar o que melhor lhe convier, o que no nosso caso, foi entre as opções presentes no Brasil, como podemos ver na Figura 1.

Figura 1. Espelhos de download de pacotes.

Após instalarmos os pacotes, precisaremos carregar os pacotes para trabalharmos com eles. Para que possamos instalar os pacotes através de linha de comando, fazemos isso da seguinte forma:

library(nome do pacote) [/code
      Ao criarmos uma variável, esta será seguida do símbolo “<-”, que indica que as informações passadas no lado direito do símbolo serão gravadas para esta variável. Um exemplo simples, seria a criação de um vetor no qual utilizaremos a função c() para indicar que será realizado um agrupamento de dados em forma de vetor, como podemos ver a seguir:
      [code ]vetor<-c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 

A função c() combina todos os seus argumentos num só vetor R, que é parecido a uma matriz em linguagens de programação. Já a função mean() retorna a média aritmética dos valores presentes no vetor, o que no nosso caso seria retornado o valor 6.

Agora que temos uma noção maior com relação as ferramentas que iremos utilizar, começaremos a desenvolver o nosso exemplo.

Desenvolvimento do exemplo

Com base no interesse das diversas partes interessadas, dentre elas, analistas e stakeholders, realizaremos a implementação do OData utilizando a linguagem C# para desenvolvermos nosso projeto. Utilizaremos para o desenvolvimento do nosso projeto o visual Studio 2013. Utilizaremos para nosso exemplo uma base de dados que é disponibilizada pela Microsoft que é a AdventureWorksDW, a qual está disponível para a versão do SQL Server 2012.

Para que possamos começar com o desenvolvimento do nosso exemplo, criaremos um novo projeto ASP NET Web Application, no Visual Studio, o qual chamaremos de TesteDevmediaOdata. Na tela seguinte, selecionaremos o template Web API e deixaremos as demais configurações de forma padrão, como podemos observar através da Figura 2.

Figura 2. Criação do projeto ASP.Net.

Agora que temos nosso projeto criado, realizaremos uma busca utilizando o nosso gerenciador de pacotes (Manager NuGet Package) pela biblioteca OData, como apresentado de acordo com a Figura 3. Ao começarmos com a instalação do OData, veremos na tela, a solicitação de aceitação dos termos e da instalação dos pacotes dependentes. Aceitaremos os termos para finalizar o processo de instalação.

Figura 3. Pesquisa e instalação do pacote OData.

Após a instalação dos pacotes, criaremos uma nova conexão ao banco de dados SQL Server para que possamos criar nossa base de dados. Para isso, abriremos a guia Server Explorer e selecionemos a opção Data Connection e em seguida, selecionemos a opção Add Connections. Na tela que será apresentada, definiremos algumas informações como o Data source que será utilizado, o qual será passado Microsoft SQL Server; na opção Server name, passaremos o caminho da conexão que será utilizada, sendo este passado da seguinte forma: (LocalDB)\MSSQLLocalDB, para que não tenhamos problemas com relação a versões do Sql Server, pois o padrão utilizado hoje pelo visual Studio 2013 é apenas (LocalDB)\v11.0, o que difere do formato antigo. Feito isso, selecionemos a opção “Attach Database” e selecionemos o arquivo .mdf que baixamos para que este seja utilizado e em seguida, atribuiremos um nome para ele, que será TesteDWOData, como podemos ver de acordo com a Figura 4.

Figura 4. Attach database para utilização.

Agora que temos nossa base de dados associada, construiremos o nosso modelo de dados utilizando o Entity Framework 6, o qual irá gerar nossos modelos de acordo com as tabelas da base de dados que utilizaremos. Para criarmos nossos modelos, clicaremos com o botão direito do mouse sobre o diretório Models e em seguida, adicionaremos um novo Item. A partir disso, selecionemos a opção Data e por último, a opção ADO.NET Entity Data Model, como mostra a Figura 5.

Figura 5. Seleção do modelo de dados ADO.NET.

Após isso, selecionaremos a opção de Modelo de entidade a partir do wizard que irá gerar nossos models, que será o EF Designer from Database, como podemos ver de acordo com a Figura 6. Em seguida, selecionaremos a nossa conexão de dados que será feita com o nosso database que inserimos anteriormente e deixamos as configurações como apresentadas de acordo com a Figura 7. Na última tela que será apresentada, selecionaremos as tabelas que estaremos interessados em utilizar no nosso projeto, como mostra a Figura 8. Ao finalizarmos o processo, aguardaremos um pouco pela criação dinâmica e teremos a visualização em forma de diagrama das tabelas que selecionamos e suas ligações, caso tenha tabelas que vocês não queiram utilizar, basta selecioná-las no modelo e excluí-las.

Figura 6. Selecionando o modelo de criação.

Figura 7. Selecionando a conexão com a base de dados.

Figura 8. Selecionando as tabelas que serão utilizadas.

Agora que finalizamos com essa etapa, daremos um build no nosso projeto antes de prosseguirmos para a criação dos Controllers, pois será necessário atualizar as informações recém adicionadas a nossa solution. Após termos dado um build no projeto, criaremos nossos Controllers, onde para isso, selecionaremos o diretório Controllers e pressionaremos o botão direito do mouse sobre ele, onde em seguida, adicionaremos um novo Controller. O modelo que iremos utilizar será o Web Api 2 OData Controller with actions, que será construído com o Entity Framework, como apresentado pela Figura 9.

Figura 9. Criação dos controllers com o Entity Framework.

Em seguida, selecionaremos o modelo e o contexto que serão utilizados para criar o controlador, e por fim, daremos um nome ao nosso controlador. Por padrão, o nome é atribuído igual ao nome da tabela selecionada, como apresentado de acordo com a Figura 10.

Figura 10. Adicionando o controlador.

Ao gerarmos os nossos Controllers, percebam que são apresentadas algumas instruções que estão comentadas, as quais precisaremos executar antes de prosseguirmos com o nosso exemplo. Seguindo as instruções apresentadas, precisamos do código comentado para mesclar com o já existente no arquivo WebApiConfig, os dados que serão inseridos serão de acordo com o apresentado pela Listagem 1. Para isso, vamos até o diretório App_data e selecionemos o arquivo WepApiConfig.

Listagem 1. Adição de código no webApiConfig.

      // Adicionar aos usings
      using System.Web.Http.OData.Builder;
      using System.Web.Http.OData.Extensions;
      using TesteDevmediaOData.Models;
      // Adicionar após o trecho do config.routes
      ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
      builder.EntitySet<DimProductSubcategory>("DimProductSubcategories");
      builder.EntitySet<DimProduct>("DimProduct"); 
      builder.EntitySet<DimProductCategory>("DimProductCategory"); 
      config.Routes.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());

Agora que temos nossa aplicação configurada, iremos realizar o nosso primeiro teste com o que temos até o momento. Ao executarmos o projeto, teremos a url básica de nosso exemplo sendo apresentada no browser, ao final dela, adicionaremos o /OData para que desta forma possamos ver nosso serviço funcionando. Caso tenham interesse em ver os dados retornados das tabelas, basta que adicionemos ao fim da url o nome do controlador que queremos ver, como por exemplo: http://localhost:41943/Odata/DimProductSubcategories. Como podemos ver de acordo com a Figura 11, temos alguns dados sendo retornados pelo nosso serviço. Dessa forma, temos uma aplicação que possui uma camada de abstração entre a implementação e o consumidor final.

Figura 11. Resultado da utilização do OData.

Veremos agora como iremos fazer para consumir esses dados pelo Excel, o qual possui uma feature que pode receber um OData de forma simples e rápida, apenas com a ajuda de alguns cliques simples. Dessa forma, tanto analista de dados quanto executivos experientes podem realizar a extração dos dados da melhor forma que interessar a eles, para que dessa forma, eles possam criar os protótipos de relatórios ou mesmo obter análises mais simples. Iremos agora trabalhar um pouco no Excel, para isso, vamos abri-lo e em seguida, iremos para a aba de Dados. Nesta aba, iremos ter apresentadas várias opções, dentre elas, um ícone para fontes adicionais, como mostra a Figura 12. Ao clicarmos nele, serão apresentadas várias opções, dentre elas, a opção de dados OData. Ao selecionarmos esta opção, adicionaremos o nosso serviço passando a nossa URL, como podemos ver de acordo com as Figuras 13, 14 e 15.

Figura 12. Visualização da aba Dados.

Figura 13. Seleção de Dados via Odata.

Figura 14. Seleção das tabelas para retornar os dados.

Figura 15. Atribuição do nome do nosso arquivo.

Em configurações de Autenticação, selecionaremos a opção “Nenhum” que é para não haver a solicitação de informações de autenticação do usuário e por fim, prossigam com as configurações. Na próxima tela que será apresentada, selecionaremos o modo pelo qual desejamos que nossos dados sejam apresentados, no nosso caso, deixaremos o padrão que é a criação de Relatórios dinâmicos, como mostra a Figura 16.

Figura 16. Seleção do modelo de importação a ser utilizado.

Ao pressionarmos “Ok”, aguardaremos um pouco e em seguida, será apresentada na planilha uma área na qual estão os campos da nossa tabela selecionada, como mostra a Figura 17, como a presente no canto direito da imagem, já no lado esquerdo, são apresentadas algumas instruções para que os dados sejam apresentados. No caso, basta que marquemos os campos que desejamos apresentar e temos os resultados como mostra a Figura 18.

Figura 17. Apresentação do Excel.

Figura 18. Resultado final dos dados dinâmicos.

Agora que criamos a nossa conexão com o Excel, salvaremos o arquivo gerado com o nome de Relatório1, pois iremos utilizá-lo em conjunto com a linguagem R. Com isso, poderemos consumir o mesmo serviço que consumimos com o Excel utilizando o R e a partir daí, explorar os dados extraídos com o código R. nossa meta neste momento, será extrair os dados do arquivo Excel e apresentá-los de forma gráfica, utilizando alguns comandos na linguagem R, com ela podemos trabalhar em vários formatos, dentre eles, o JSON, que ficará para um outro momento.

Com o RStudio instalado, selecionaremos a opção “import Dataset” presente na aba Environment, como mostra a Figura 19.

Figura 19. Importação de Dataset.

Ao selecionarmos a opção “import dataset”, teremos a apresentação de duas formas de importação que são através de uma url e através de um arquivo de texto, que é a que selecionaremos neste momento. Ao fazer isso, devemos selecionar a nossa planilha .csv que geramos anteriormente. Com a seleção do arquivo, será apresentada uma nova tela, na qual selecionaremos algumas opções de organização dos dados, como é o caso de adicionar cabeçalho, o que definirá a separação dos dados, etc, como mostra a Figura 20.

Figura 20. Importação dos dados.

Após a importação dos registros, utilizaremos uma função do R para manter os dados visíveis que é o “attach”, o qual passamos da seguinte forma:

attach(Relatorio1)

Uma outra função que podemos utilizar é a “summary” que nos traz todas as informações com relação aos dados presentes na tabela, como por exemplo, o valor mínimo, máximo, média de valores, dentre outras informações. Para esta, podemos pegar uma das colunas contendo os valores que queremos testar os resultados e passarmos da seguinte forma:

summary(Soma.de.ProductSubcategoryAlternateKey)

Além dessas funções que foram apresentadas, poderíamos utilizar a Media (apenas para trazer a média de valores), sum, etc. Por fim, podemos visualizar os gráficos através de funções existentes no R ou mesmo, baixar pacotes que contenham outros tipos de gráficos. Dentre os já existentes com a linguagem R, temos as funções hist(), que gera um histograma e o plot, como podemos ver pela Figura 21. Nos momentos em que precisarmos repetir alguns comandos e não estivermos lembrando como fazê-lo, basta selecionarmos a aba History, pois nela são mantidas todas as operações realizadas durante o decorrer do processo de criação.

Figura 21. Visualização de comandos e gráfico.

Com isso fechamos este artigo, onde como podemos observar, todas as funções que precisamos utilizar, nós as definimos na guia console da aplicação. Podemos definir várias outras funções e pacotes que desejarmos com o R, basta darmos uma olhada mais a fundo na documentação. Criamos uma aplicação simples no Visual Studio, utilizando o Entity Framework para gerarmos nossos models e views. Claro que devemos nos preocupar também com relação a segurança nos dados apresentados e para isso, também podemos encontrar pacotes que nos auxiliam nesse quesito, no entanto, nosso foco nesse momento foi apenas de apresentarmos as informações vindas de um arquivo .csv que foi gerado a partir do OData numa aplicação ASP.NET. Esperamos que tenha ficado claro com relação a utilização das ferramentas.

Links

OData
https://msdn.microsoft.com/en-us/library/ff478141.aspx,

CRAN
https://cran.r-project.org/web/packages/available_packages_by_name.html

R-Studio
http://www.rstudio.com/products/rstudio/download/

Linguagem R
http://cran.r-project.org/bin/windows/base/

Base de dados de teste
http://msftdbprodsamples.codeplex.com/releases/view/105902