Monitorando a utilização dos relatórios pelo log do Reporting Services

É comum encontrarmos nas empresas uma infinidade de relatórios. Porém, você já parou para pensar quantos realmente são utilizados? E quais demoram mais tempo para carregar, ou seja, tem uma performance pior?

No Reporting Services, ferramenta Microsoft de construção de relatórios, é possível ter acesso a este tipo de informação que pode ser bastante útil para entendermos quais são os relatórios mais importantes e quais são aqueles que podem ser excluídos ou modificados. Para isso, vamos utilizar as informações do log de execução que ficam registradas em algumas tabelas do banco de dados do Reporting Services. A ideia deste post é mostrar basicamente como criar um relatório de monitoramento de utilização, semelhante ao da imagem abaixo:

relatorio_utilizacao_relatorios

Banco de dados do Reporting Services

Até a versão do SQL Server 2016 (última lançada quando este post foi escrito), o Reporting Services possui duas opções de instalação: modo nativo e modo integrado ao Sharepoint. Para ambas as opções, após a instalação/ configuração, o Reporting Services gera um banco de dados (que pode ter algumas variações, mas normalmente é chamado de “ReportServer” ou “ReportingService”). É este banco de dados que armazena os logs de uso dos objetos do Reporting Services em geral (data sources, datasets e relatórios).

Por padrão, este banco de dados armazena um histórico de 60 dias de dados de utilização nas suas tabelas. Porém, esta propriedade pode ser alterada através da instância do Reporting Services, conforme descrito neste link.

Consultas SQL para retornar as informações

Para facilitar o acesso aos dados, o banco de dados do Reporting Services possui algumas views que já retornam um conjunto de informações relevantes. A Microsoft recomenda a utilização da view ExecutionLog3, que é a mais recente e possui mais campos. Porém, por questões de compatibilidade, ainda estão disponíveis as views ExecutionLog2 e ExecutionLog, que possuem dados bem semelhantes.

No nosso exemplo, como queremos retornar informações específicas dos relatórios e a view ExecutionLog possui o campo ReportID, vamos utilizá-la para simplificar a consulta.

Abaixo geramos duas consultas que retornam os dados que precisamos para gerar o nosso relatório:

  • Query 1: quantidade de execuções de cada relatório
SELECT
  c.Name,
  c.[Path],
  COUNT(*) AS TimesRun
FROM [dbo].[ExecutionLog] AS l
INNER JOIN [dbo].[Catalog] AS c
  ON l.ReportID = C.ItemID
WHERE c.Type = 2
GROUP BY l.ReportId,
         c.Name,
         c.[Path]
ORDER BY TimesRun DESC
  • Query 2: tempo médio e data da última execução de cada relatório
SELECT
  ReportID,
  C.Name,
  CAST(AVG(
  (TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000.0)
  AS decimal(10, 2)) AS TotalRenderingTime,
  CAST(MAX(l.TimeStart) AS date) AS [LastRun]
FROM dbo.ExecutionLog AS l
INNER JOIN [dbo].[Catalog] AS c
  ON l.ReportID = C.ItemID
WHERE c.Type = 2
GROUP BY l.ReportId,
         C.Name
ORDER BY TotalRenderingTime DESC

 

Criação do relatório

Para gerar o nosso relatório, iremos utilizar o SQL Data Tools, ferramenta que é um template do Visual Studio para a criação de projetos de banco de dados e BI (Integration Services, Analysis Services e Reporting Services). Vamos selecionar um novo projeto do tipo “Report Server Project” (imagem abaixo):

sqldatatools_ssrs

Em seguida vamos adicionar os seguintes objetos ao nosso projeto:

Data source: conexão com o banco de dados do Reporting Services (SSRS_db.rds);

Datasets: criaremos dois datasets (“DataSet_ExecutionTime.rsd” e “DataSet_TimesRun.rsd”) que terão as consultas “Query 1” e “Query 2”;

Relatório: criaremos um relatório chamado “Report Usage.rdl”, e adicionaremos os dois “shared datasets” ao nosso relatório. Assim, poderemos gerar os seguintes objetos:

  • Gráfico de barras contabilizando a quantidade de execuções de cada relatório;
  • Tabela com tempo médio de renderização de cada relatório e a última vez que ele foi executado.

A imagem abaixo mostra como ficou o projeto após a criação destes objetos:

ssrs_project

 

Por fim, vamos realizar o deploy do nosso projeto para o servidor de relatórios (imagem a seguir), definindo nas propriedades do projeto as URL’s do site do Reporting Services ou do Sharepoint onde ficarão armazenados os objetos do projeto. Assim conseguiremos acompanhar em tempo real (acessando o site onde se encontra o relatório) a frequência de uso dos relatórios e a duração média da cada execução.

ssrs_deploy

A grande vantagem dessa solução é a possibilidade de criar outras formas de visualização personalizadas, modificando as queries para utilizar outras views/ tabelas trazendo outros dados mais relevantes para o tipo de monitoramento que você deseja realizar.

Referências:

SSRS Report Usage Queries

How to Display Report Execution Time in SQL Server Reporting Services (SSRS)

Report Server ExecutionLog and the ExecutionLog3 View

Criando um mapa do Brasil no R em 7 passos

Atualmente as áreas de análise e visualização de dados geográficos vêm sendo bastante exploradas por analistas, estatísticos e cientistas de dados devido à quantidade de recursos disponíveis e pela facilidade no entendimento que é proporcionada ao visualizar dados sobre mapas.

Neste post vamos demonstrar em 7 passos como gerar um mapa do Brasil interativo, usando a ferramenta R, para apresentar a distribuição dos pontos ganhos pelos clubes no Campeonato Brasileiro de futebol desde 2003, na visão por estado.

1) Fazer download do shapefile do Brasil no site do IBGE

Inicialmente precisamos importar para o R o shapefile do mapa do Brasil, que está disponível no site do IBGE. No exemplo vamos utilizar o mapa com a distribuição por estado, porém no site há diversos outros tipos de mapa, como mapas de cada estado com divisão por município, entre outros.

Para encontrar o mapa do Brasil por estado, vamos utilizar a URL abaixo e navegar pelo diretório de pastas do site até encontrar o arquivo br_unidades_da_federacao.zip (imagem a seguir):

http://downloads.ibge.gov.br/downloads_geociencias.htm

Vamos navegar pelo seguinte diretório:

organizacao_do_territorio >>> malhas_territoriais >>> malhas_municipais >>> municipio_2015 >>> Brasil >>> BR >>> br_unidades_da_federacao.zip

ibge_site

Diretório de pastas no site do IBGE

Após fazer o download, iremos extrair os cinco arquivos e copiá-los para o diretório de trabalho do R (no nosso exemplo, será criada uma pasta chamada “Mapa”), para que  assim eles possam ser importados.

2) Importar o shapefile para o R

Agora temos que importar o nosso shapefile para o R, atribuindo a uma variável chamada “shp”. Utilizaremos a função readOGR para esta tarefa:

shp <- readOGR("Mapa\\.", "BRUFE250GC_SIR", stringsAsFactors=FALSE, encoding="UTF-8")

Podemos verificar executando o comando class(shp) que o objeto “shp” é do tipo “SpatialPolygonsDataFrame”. Este é o tipo de objeto que precisamos criar para gerar mapas no R.

3) Importar o dataset que possui os dados que serão plotados no mapa

Outra etapa necessária é importar o arquivo que possui os dados que queremos exibir no mapa. No caso deste exemplo, temos um arquivo chamado “ClassificacaoPontosCorridos.csv” que tem dados dos pontos ganhos por cada clube no Campeonato Brasileiro desde 2003.

O script abaixo realiza a importação do arquivo e realiza a sumarização dos pontos ganhos por estado, utilizando as funções do o pacote dplyr:

pg <- read.csv("Dados\\ClassificacaoPontosCorridos.csv", header=T,sep=";")

pg <- pg %>% group_by(Estado) %>% mutate(cumsum = cumsum(PG))
pg <- pg %>%
  group_by(Estado) %>%
  summarise(Score= max(Score))

pg <- as.data.frame(pg)

Podemos verificar executando o comando class(pg) que a variável “pg” é do tipo de dados “data.frame”. Este é um dos tipos de dados mais comuns no R para armazenar tabelas, porém para trabalhar com mapas precisaremos unir esta variável ao objeto de tipo espacial criado no passo 2.

4) Importar os códigos do IBGE e adicionar ao dataset

O shapefile do IBGE que importamos no passo 2 não possui o campo  “UF”, que é o campo utilizado para identificar o estado no nosso dataset. Teremos então que utilizar um campo chamado “CD_GEOCUF”, que é um código numérico do IBGE para identificar o estado. Para conseguirmos relacionar nosso dataset com o shapefile, iremos realizar a importação de um arquivo CSV que tenha estes códigos numéricos e realizar um “merge” entre este arquivo CSV (variável “ibge”) e o nosso dataset (variável “pg”), para associar os dados pelo campo UF:

ibge <- read.csv("Dados\\estadosibge.csv", header=T,sep=",")

pg <- merge(pg,ibge, by.x = "Estado", by.y = "UF")

Após esta operação, o data frame ficou conforme a imagem abaixo:

tabela_pontos

Data frame com o campo “Código.UF”

5) Fazer a junção entre o dataset e o shapefile utilizando o código do IBGE

Tendo o código do IBGE no nosso objeto “pg”, podemos então unir os nossos dados ao objeto do tipo espacial do R. Ou seja, iremos realizar um “merge” entre a variável “shp” (shapefile) e a variável “pg” (dataset de pontos ganhos). Esta junção irá retornar um data frame espacial, e terá os dados que precisamos para exibir no mapa.

brasileiropg <- merge(shp,pg, by.x = "CD_GEOCUF", by.y = "Código.UF")

Para se ter uma ideia, segue abaixo a imagem do RStudio de como o R armazena os dados em um data frame espacial:

dataframe_espacial

Objeto do tipo “SpatialPolygonsDataFrame”

6) Realizando o tratamento e a formatação do data frame espacial

Agora iremos realizar as últimas transformações no objeto antes de gerar o mapa. A primeira é passar as coordenadas de latitude e longitude para o objeto, utilizando a função proj4string ; a segunda é converter os dados da coluna “NM_ESTADO” para a formatação UTF-8, e a terceira é substituir os valores para os estados que não tem nenhum ponto ganho de “NA” para zero.

proj4string(brasileiropg) <- CRS("+proj=longlat +datum=WGS84 +no_defs")

Encoding(brasileiropg$NM_ESTADO) <- "UTF-8"

brasileiropg$Score[is.na(brasileiropg$Score)] <- 0

7) Gerando o mapa

Finalizadas as etapas de importação e organização dos dados, podemos finalmente gerar o nosso mapa.

Inicialmente vamos escolher as cores para o nosso mapa. Utilizando o pacote RColorBrewer temos acesso a várias paletas de cores. Para consultá-las, basta utilizar no R o comando display.brewer.all():

rcolorbrewer

Paleta de cores do pacote “RColorBrewer”

No nosso exemplo será escolhida a paleta de cores (“Blues”).

Para plotar o mapa existem várias opções de pacotes disponíveis no R. No nosso caso vamos utilizar o pacote Leaflet, que possui uma boa interatividade (é possível clicar nos estados e visualizar os valores de pontuação).

O script para gerar o mapa ficou desta forma:

pal <- colorBin("Blues",domain = NULL,n=5) #cores do mapa

state_popup <- paste0("<strong>Estado: </strong>", 
                      brasileiropg$NM_ESTADO, 
                      "<br><strong>Pontos: </strong>", 
                      brasileiropg$Score)
leaflet(data = brasileiropg) %>%
  addProviderTiles("CartoDB.Positron") %>%
  addPolygons(fillColor = ~pal(brasileiropg$Score), 
              fillOpacity = 0.8, 
              color = "#BDBDC3", 
              weight = 1, 
              popup = state_popup) %>%
  addLegend("bottomright", pal = pal, values = ~brasileiropg$Score,
            title = "Pontos Conquistados",
            opacity = 1)

O mapa gerado ficou conforme a imagem abaixo:

mapa_pontos

Mapa de pontos ganhos no Campeonato Brasileiro por estado

Observações:

Os scripts e os arquivos necessários para reproduzir este exemplo se encontram neste diretório do GitHub . O script foi testado na versão R-3.3.2. Lembre-se de instalar no R os pacotes necessários!

Este mapa está publicado na web através de um aplicativo no Shiny (isto já é assunto para um outro post…). Quem quiser visualizar e interagir com o mapa, basta clicar aqui.

Referências:

The leaflet package for online mapping in R

Text Analytics com R, exemplo prático: analisando dados de futebol do Twitter

Durante o programa Bate Bola, da ESPN, do dia 12/12/2016, foi solicitado aos telespectadores que enviassem suas seleções ideais dos melhores jogadores do Campeonato Brasileiro 2016 utilizando a hashtag #bateboladebate. A ideia deste post é mostrar como obter dados dos tweets enviados no programa neste dia e realizar a contagem dos nomes dos jogadores que foram mais citados. Assim, conseguiremos demonstrar uma aplicação real da utilização de Text Mining, extraindo dados do Twitter e montando uma seleção com os jogadores mais mencionados pelos torcedores. Vamos utilizar a ferramenta R, que permite trabalharmos com dados de redes sociais e realizarmos a limpeza dos dados. Pra quem não conhece, o R é uma ferramenta de análise de dados e que possui uma linguagem própria para manipulação dos dados.

Extraindo os tweets

Para realizar a captura dos dados do Twitter, são necessárias 3 etapas:

1- Criar uma aplicação do Twitter utilizando o Twitter Apps (para o nosso exemplo, foi criada a aplicação “twitterbuscacomr”, conforme imagem abaixo);

rtwitter_img

2- Criar as configurações de autenticação;

3- Executar a busca pela palavra-chave (podendo-se definir o número de tweets, a data de início e a data de término).

Para realizar estas etapas recomendo utilizar este link, onde todos estes passos estão muito bem detalhados (inclusive com os scripts para serem executados no R).

No nosso caso, iremos buscar os tweets que utilizaram a hashtag #bateboladebate entre os dias 12/12/2016 e 13/12/2016. A imagem abaixo mostra alguns exemplos de tweets interessantes para o nosso objetivo de montar a seleção do campeonato com base nos tweets:

tweets

Feitas todas as etapas necessárias para realizar a conexão com o Twitter, o script para buscar os tweets no R ficou assim:

search.string <- “#bateboladebate”

no.of.tweets <- 1106
batebola <- searchTwitter(search.string, no.of.tweets,since = “2016-12-12”,
                          until = “2016-12-13”,
                          lang=”pt”)

Limpando os dados

Agora nós já temos os dados de 1106 tweets que foram capturados de acordo com a nossa busca. Porém, para realizar a contagem dos termos que mais aparecem, será necessário um processo de limpeza do texto, deixando somente o que é realmente importante para o resultado final. Por exemplo, vejam o resultado de um dos tweets originais (número 1023 na busca):

rtwitter_img1

Tweet original

 

Neste tweet algumas palavras possuem acento, como o jogador Moisés, por exemplo. Porém em outros tweets o nome Moises está sem acento, sendo que se trata do mesmo jogador. Para que isso não ocasione um erro na contagem, precisamos remover os acentos, para que todas os votos sejam computados para um só jogador. O script abaixo realiza esta conversão:

dados <- twListToDF(batebola) #convertendo lista em data frame
dados_str <- stri_trans_general(dados$text,’Latin-ASCII’) #removendo acentuação das palavras

O tweet agora ficou dessa forma, após remoção dos acentos:

rtwitter_img2

Tweet após remoção dos acentos

 

Para finalizar as transformações no texto, iremos remover caracteres que não irão ser importantes para nossa análise, como pontuação, números, palavras muito comuns ou funcionais na linguagem (que chamamos de stopwords), além de caracteres de uso comum no Twitter. Podemos utilizar o script do R a seguir:

dados_transf <- as.data.frame(dados_str)
corpus <- Corpus(VectorSource(dados_str))

corpus <- tm_map(corpus, stripWhitespace) #realizando tratamento de múltiplos espaços
corpus <- tm_map(corpus, tolower) #transformando em letra minúsucula
corpus <- tm_map(corpus, function(x) gsub(‘@[[:alnum:]]*’, ”, x)) #removendo menções
corpus <- tm_map(corpus, removePunctuation) #removendo pontuação
corpus <- tm_map(corpus, removeNumbers) #removendo números
corpus <- tm_map(corpus, function(x) gsub(‘http[[:alnum:]]*’, ”, x))  #removendo URLs
corpus <- tm_map(corpus, removeWords, c(stopwords(‘portuguese’), ‘bateboladebate’,’rt’)) #removendo “stopwords”

corpus_text <- tm_map(corpus, PlainTextDocument)

O resultado do tweet após todas as transformações acima ficou assim:

rtwitter_img3

Tweet após transformações

 

Análise dos resultados

Tendo sido feita a transformação dos tweets, podemos criar o que chamamos de “term-document matrix”, que é uma matriz utilizada para contar a quantidade de vezes que um termo aparece em cada documento (no nosso exemplo, em cada tweet). Utilizaremos o script do R abaixo para gerá-la:

#Create Term Document Matrix
tdm <- TermDocumentMatrix(corpus_text, control=list(minWordLength=1))
term.freq <- rowSums(as.matrix(tdm))
term.freq <- subset(term.freq, term.freq >=50)

df <- data.frame(term = names(term.freq), freq = term.freq)

View(df[order(-df$freq),])

O resultado é a tabela a seguir:

rtwitter_img4

Trabalho concluído? Certamente não. Quando trabalhamos com análise de dados, é fundamental ter conhecimento do negócio e dos dados, de maneira geral. Por isso é muito importante pedir o apoio de alguém de entenda da área de conhecimento (no caso, o futebol), para que o resultado seja o mais correto possível.

No exemplo, podemos perceber que o termo mais encontrado no tweets foi “tche”. Quem acompanha futebol sabe que existe um jogador do Palmeiras que se chama Tchê Tchê. Logo, quem escolheu o Tchê Tchê para a sua seleção citou a expressão “tche” duas vezes em cada tweet, o que corresponde a um único voto.

Outro caso interessante é do nome Diego. Um dos jogadores de destaque do campeonato foi o Diego do Flamengo. Porém, o Diego Souza do Sport também fez uma ótima competição. Como diferenciar quem votou em Diego ou Diego Souza? Uma forma de analisar é observando que a palavra “souza” apareceu 146 vezes, o que indica o voto para o Diego Souza nestes casos.

Seleção do Campeonato Brasileiro 2016 do tweets do Bate Bola ESPN

Realizando as seguintes adequações:

  • Tchê Tchê = tche / 2 = 379 / 2 = 189,5
  • Diego = diego – souza = 346 – 146 = 200
  • Gabriel Jesus = jesus + gjesus = 333 + 31 = 364

Temos a seguinte seleção dos mais votados:

selecao_twitter

Observação: Os scripts foram testados na versão R-3.3.2 do R. Todos os scripts utilizados neste post e os dados estão disponíveis no GitHub.

Referências:

R DataMining: Text Mining

Stack Overflow: R – delete accents in string

Seleção do Campeonato Brasileiro 2016: melhores aproveitamentos dos jogadores

Sabe aquela sensação que o torcedor tem que quando determinado jogador está em campo, o time vai bem? Podemos traduzir isso através dos números, analisando o aproveitamento de cada jogador no campeonato (considerando apenas os pontos da partida para o jogador quando ele esteve em campo). E se fizéssemos isso para todos os jogadores do Campeonato Brasileiro 2016 e montássemos a seleção dos jogadores com melhores aproveitamentos?

No Brasileirão 2016, ninguém foi melhor nesta estatística que o goleiro Jaílson, do Palmeiras. Ele terminou o campeonato invicto (14V – 5E – 0D), o que dá um aproveitamento de 82,5%. Evidentemente a maioria dos jogadores da seleção é do Palmeiras, que foi o campeão com boa vantagem. Dos outros clubes, destaque para Ricardo Oliveira (Santos) e Diego (Flamengo), que tiveram aproveitamentos superiores a 70% quando estiveram em campo (para se ter uma ideia, o Palmeiras foi campeão com 76,9% de aproveitamento). Se eles tivessem disputado todos os jogos…

Importante: para entrar na seleção, o jogador precisou disputar pelo menos 40% dos minutos do campeonato (ou seja, 1368 minutos). Acredito que definir o mínimo de minutos ao invés de jogos é mais justo, pois muitos jogadores têm muitos jogos mas sempre entrando no final, participando pouco.

Segue abaixo a seleção dos melhores aproveitamentos (titulares e reservas):

selecao_brasileirao_16.PNG

TOP 3 de cada clube

Quer saber quem foram os jogadores de melhor aproveitamento do seu clube? Segue abaixo o TOP 3:

Palmeiras: 1- Jaílson (82,5%), 2- Gabriel Jesus (72,8%), 3- Jean (72,4%)
Santos: 1- Ricardo Oliveira (73,3%), 2- Thiago Maia (65,6%), 3- Luiz Felipe (65,4%)
Flamengo: 1- Diego (70,6%), 2- Pará (69,1%), 3- Rafael Vaz (64,4%)
Atlético-MG: 1- Erazo (63,3%), 2- Leonardo Silva (61,3%), 3- Robinho (58,9%)
Botafogo: 1- Carli (62,5%), 2- Airton (61,9%), 3- Rodrigo Lindoso (60,0%)
Atlético-PR: 1- Léo (58,9%), 2- Thiago Heleno (54,5%), 3- André Lima (54,3%)
Corinthians: 1- Uendel (53,5%), 2- Guilherme (53,3%), 3- Giovanni Augusto (50,0%)
Ponte Preta: 1- Jeferson (56,7%), 2- Douglas Grolli (53,6%), 3- João Vitor (49,0%)
Grêmio: 1- Everton (56,8%), 2- Douglas (52,9%), 3- Maicon (50,8%)
São Paulo: 1- João Schmidt (53,0%), 2- Buffarini (52,1%), 3- Rodrigo Caio (50,0%)
Chapecoense: 1- Kempes (50,7%), 2- Josimar (50,6%), 3- Lucas Gomes (50,0%) #ForçaChape
Cruzeiro: 1- Rafael (54,9%), 2- Lucas Romero (51,4%), 3- Manoel (50,0%)
Fluminense: 1- Diego Cavalieri (51,4%), 2- Wellington (48,3%), 3- William Matheus (48,3%)
Sport: 1- Ronaldo Alves (53,3%), 2- Rogério (46,2%), 3- Magrão (46,1%)
Coritiba: 1- Raphael Veiga (52,6%), 2- Edinho (50,0%), 3- Luccas Claro (44,9%)
Vitória: 1- Amaral (49,2%), 2- Euller (44,4%), 3- Marcelo (43,9%)
Internacional: 1- Danilo Fernandes (49,4%), 2- Vitinho (44,0%), 3- Fabinho (42,7%)
Figueirense: 1- Marquinhos (44,4%), 2- Elicarlos (40,4%), 3- Rafael Moura (37,9%)
Santa Cruz: 1- Neris (39,7%), 2- Tiago Costa (37,3%), 3- Tiago Cardoso (30,4%)
América-MG: 1- Ernandes (31,9%), 2- Jonas (29,6%), 3- Juninho (27,5%)

Para consolidar estes dados foi aplicada uma técnica que é muito utilizada pelos cientistas de dados chamada de web scraping, que consiste basicamente em capturar dados da web. Foi escolhido o site do Fox Sports para buscar os dados de todas as escalações de todos os 379 jogos do campeonato.

Referências:

Lineup Builder – Site para montar esquemas táticos

Fox Sports – Campeonato Brasileiro 2016

Sugestão de leitura relacionada:

Estatísticas destacam os melhores jogadores na luta pelo título brasileiro

Exchange + R + Power BI: e-mails recebidos exibidos no gráfico de calendário

Quem trabalha criando relatórios sabe como escolher a melhor forma de exibição das informações faz toda a diferença para facilitar o entendimento dos dados. E as ferramentas de BI a cada dia possuem recursos visuais mais avançados. Geralmente gráficos comuns já estão disponíveis em ferramentas como Tableau, QlikView, Power BI, entre outras.

Porém é praticamente impossível uma ferramenta ter todos os gráficos que alguém já imaginou. Por isso, muitas vezes é importante para uma ferramenta de relatórios ser extensível, ou seja, permitir alguma forma de customização para que o usuário possa incorporar novas funcionalidades.

A ideia deste post é mostrar como criar um “Calendar Heatmap”, ou seja, um gráfico em formato de calendário, que possa apresentar dados de e-mails recebidos por um usuário durante algum período. Quanto mais forte é a cor, maior o número de e-mails recebidos naquela data. A ideia é interpretar de forma rápida os dias de semana e os meses que ele mais recebeu e-mails.

Além deste exemplo dos e-mails, este tipo de visual pode ser interessante para as seguintes aplicações:

  • Mostrar a atividade de um usuário em um site (GitHub, por exemplo);
  • Exibir os chamados atendidos por dia em uma equipe de suporte;
  • Identificar picos de compra em um determinado dia da semana.
postcalendar_github

Gráfico de atividade no GitHub

Vamos utilizar como ferramenta o Power BI Desktop, e utilizar a sua integração com o R para criar este gráfico, customizando os nomes, títulos, cores, etc.

Trazendo dados do Microsoft Exchange

Inicialmente, na tela inicial do Power BI Desktop, vamos escolher a opção “Obter Dados” no menu superior, navegar até a opção “Outras” e escolher “Microsoft Exchange” (imagem a seguir). Dessa forma, realizaremos a conexão ao e-mail que você utiliza no Outlook da sua organização:

postcalendar_exchange

Utilizando Microsoft Exchange como fonte de dados

 

Na tela que solicita o endereço da caixa de correio, preencha com o seu e-mail corporativo e realize a autenticação para se conectar ao servidor de e-mail do Exchange (imagem abaixo):

postcalendar_email

Preenchendo a informação do endereço de e-mail

 

Selecionando e agrupando os dados para contar os e-mails por dia

Após realizar a conexão com sucesso, clique no botão “Editar” para realizarmos as transformações necessárias para termos os dados dos e-mails recebidos por dia e a quantidade.

Inicialmente vamos selecionar as colunas “FolderPath”, “DateTimeReceived” e “Id”, que são as colunas que vamos utilizar. Em seguida, clique com botão direito e selecione a opção “Remover Outras Colunas” (imagem abaixo):

postcalendar_removecolumns

Removendo colunas desnecessárias para a análise

 

Depois de filtrar as colunas, agora vamos selecionar somente as linhas que nos interessam. Clique na seta ao lado do nome da coluna “Folder Path” e marque a opção “\Caixa de Entrada\” (imagem a seguir) para mantermos apenas as linhas que possuem dados dos e-mails que estão na Caixa de Entrada (lembre-se, os e-mails recebidos é que são importantes neste cenário):

postcalendar_selectcaixaentrada

Selecionando linhas de pasta “Caixa de Entrada”

 

Como queremos realizar a contagem de e-mails recebidos por dia e os dados da coluna “DateTimeReceived” contém também informações de hora, vamos modificar o tipo de dados desta coluna clicando com botão direito nela, selecionando “Alterar Tipo” e “Data”, para converter o campo do tipo “Data/ Hora” para o tipo “Data” (imagem abaixo):

postcalendar_changetype

Alterando o tipo de dados da coluna “DateTimeReceived”

 

Agora que já temos nossos dados organizados, podemos aplicar a função de agrupamento para contar as linhas da tabela agrupando por data. No menu superior do Power BI Desktop, clique na opção “Agrupar por” (imagem abaixo):

postcalendar_groupby

Botão “Agrupar por” no menu do Power BI

 

Selecione “DateTimeReceived” como a coluna que deverá ser agrupada e “Contar Linhas” como a operação, conforme a imagem a seguir. Clique em “OK” para realizar o agrupamento.

postcalendar_groupcount

Definindo coluna e operação de agrupamento

 

No último passo, renomeie a coluna “Contagem” para “EmailsDia”.

Calendar Heatmap

Agora vamos adicionar o gráfico de calendário no Power BI Desktop utilizando um script do R. Inicialmente clique no ícone do R que aparece na aba “Visualizações” para incluir um visual do R no seu relatório.

postcalendar_rbutton

Botão do “R” no Power BI Desktop

 

Se o ícone do R não está aparecendo para você ao lado dos ícones dos outros tipos de gráfico, provavelmente você ainda não realizou a  integração do Power BI com o R. Clique aqui para ver o que é necessário para utilizar scripts do R no seu Power BI Desktop.

O script do R que vamos utilizar basicamente cria algumas colunas para exibição dos dados e gera o gráfico do calendário. É importante que você tenha os pacotes do R instalados para que o script funcione corretamente. Ele está disponível no seguinte diretório do GitHub (Calendar_HeatMap.R): https://github.com/mvbfontes/calendarheatmapvisual

Após clicar no ícone do R, selecione os campos “DateTimeReceived” e “EmailsDia”. Em seguida cole o script do R que cria o visual de “Calendar HeatMap” na área destacada em vermelho na imagem abaixo.

postcalendar_rscript

Editor de script do R no Power BI Desktop

 

No editor de script do R, clique no botão “Executar Script” para gerar o gráfico no seu relatório:

postcalendar_chart

Gráfico de calendário mostrando e-mails recebidos por data

 

Pronto! Gráfico “Calendar Heatmap” gerado!

Caso você tenha algum problema, há a opção de editar o script do R em alguma IDE externa, como o RStudio. Assim, fica mais fácil de realizar a identificação e a correção de possíveis erros.

Recursos adicionais

O Power BI Desktop possui, além da integração com R, uma funcionalidade denominada “Custom Visuals”, onde você pode adicionar visuais já prontos ao seu relatório. Para este cenário, um visual interessante que pode ser adicionado é o “Timeline”, onde você pode filtrar com facilidade o período de tempo que deseja exibir!

Caso você queira reproduzir este cenário utilizando o RStudio, por exemplo (sem utilizar o Power BI Desktop), uma cópia dos dados em formato CSV está disponível no mesmo diretório do GitHub.

Referências:

Analyzing your Microsoft Exchange Account Data with Power Query

ggplot2 Time Series Heatmaps

 

Web scraping com Power BI Desktop: o que você precisa saber

Web scraping é a técnica computacional que permite extrair informações dos websites. Este processo de busca de dados da internet certamente é uma das atividades que um cientista de dados precisar realizar, já que atualmente a maior parte dos dados estão na web. O processo basicamente consiste na busca de dados não estruturados das páginas web, normalmente em formato HTML, e a transformação destes dados em estruturados, possibilitando a criação de relatórios com maior facilidade.

São bons exemplos de aplicações de web scraping buscas de ofertas em sites de comércio eletrônico, monitoramento de palavras mais pesquisadas em mecanismos de busca, consultas às redes sociais para identificar a reputação da empresa, entre outras.

Existem diversas ferramentas especializadas em web scraping, mas neste post vou comentar um pouco sobre conceitos básicos da funcionalidade de web scraping em uma ferramenta que, além da captura dos dados, permite também realizar a limpeza e a organização destes dados; gerar modelos lógicos relacionando estes dados; e, ainda, a criação de relatórios e dashboards interativos: o Power BI Desktop!

Seguem abaixo algumas dicas úteis para buscar dados da web utilizando o Power BI Desktop:

O básico

Se você nunca utilizou o Power BI Desktop para capturar dados da web, recomendo começar por este tutorial: Tutorial: Importing and analyzing data from a Web Page using Power BI Desktop . O Power BI Desktop possui diversas funcionalidades disponíveis que permitem rapidamente buscar os dados e organizá-los em um formato ideal.

Utilize funções para percorrer várias páginas

Buscar dados de uma única URL é útil mas pode não ser suficiente, dependendo da quantidade de dados que precisamos. Para isso o Power BI Desktop dá a possibilidade de criarmos funções, que permitem percorrer mais de uma página tendo como base um diretório raiz. Ou seja, com base em uma URL principal, podemos retornar dados de várias páginas que são subdiretórios desta URL.  Estes exemplos de como buscar dados de medalhas nas Olimpíadas e da bilheteria dos filmes explicam detalhadamente o uso de funções para percorrer múltiplas páginas web.

pbidesktop_function

Consulta utilizando função no Power BI Desktop

 

Opção de timeout

Conforme aumentamos a complexidade da nossa captura dos dados, especialmente percorrendo várias páginas, e dependendo da velocidade da conexão, pode acontecer o que chamamos de “timeout”, ou seja, foi atingido o tempo limite de conexão. No Power BI Desktop, em alguns minutos de busca isso já acontece. Mas como então podemos aumentar este tempo?

Para isso temos que adicionar um comando de timeout com a opção duration após a URL chamada pela função Web.Contents(), modificando a query na linguagem “M” que é gerada (que pode ser encontrada na opção “Editor Avançado”). Abaixo um exemplo em que estamos definindo o tempo de timeout como 5 horas:

Web.Contents(“http://bing.com/“, [Timeout=#duration(0,5,0,0)])

Atualização dos dados

Um recurso importante e muito útil no Power BI Desktop é a possibilidade de atualização dos dados. É comum para quem quer utilizar dados da web sempre procurar alguma opção nos sites para exportar os dados para o formato CSV ou algo do tipo. Mas e se estes dados sofrerem inclusões ou modificações?

Visando manter sempre os dados atualizados, o Power BI Desktop cria consultas às páginas web, e assim podemos estar buscando os dados mais recentes sempre que quisermos clicando no botão “Atualizar”.

pbidesktop_atualizar

Botão “Atualizar” no Power BI Desktop

 

Podemos configurar também quais consultas são estáticas, ou seja, que não precisam ser atualizadas já que os dados não sofrerão alterações. Assim, ao clicar no botão “Atualizar”, todas as consultas serão atualizadas, exceto aquelas que estiverem com a opção “Incluir na atualização do Relatório” desmarcada.

pbidesktop_carregamento

Opção “Incluir Na Atualização do Relatório” desmarcada

 

Também é possível atualizar cada uma das consultas individualmente, clicando com botão direito nela e clicando em “Atualizar dados”.

pbidesktop_atualizarconsulta

Opção “Atualizar Dados” de apenas uma consulta

 

É importante explicar o funcionamento do Power BI Desktop para as atualizações. Ao enviar o comando para atualizar os dados, na prática o processo que ocorre é a exclusão dos dados antigos e a inclusão dos novos, ou seja, não é feita uma atualização incremental. Caso ocorra alguma falha na atualização, os dados serão perdidos. Por isso, é importante testar o funcionamento das consultas para garantir que tudo funcione corretamente.

Inclusive existe uma ideia com bastante votos no site Power BI Ideas solicitando uma modificação nesta funcionalidade de atualização: Incremental Data Loads .

Lembrando que o Power Query para Excel também possui estes mesmos recursos para tarefas de web scraping.

Referências:

Web scraping

What are examples of how real businesses use web scraping?

Power Query HTTP timeout setting

Sugestão de leitura relacionada:

Power BI Desktop Web Queries to Screen Scrape Data from Websites

Parte 2: Gerando gráficos de forecast utilizando R para previsão de medalhas nas Olimpíadas

Na parte 1 do post, mostramos como capturar dados das medalhas conquistadas na história das Olimpíadas utilizando o Power Query. Agora, vamos criar análises a partir destes dados utilizando o R. Para quem não conhece, o R é uma ferramenta que permite criar visualizações gráficas utilizando técnicas estatísticas e é um dos softwares mais populares na área de data science.

A nossa ideia é tentar prever, utilizando os dados históricos do total de medalhas conquistadas por cada país e algoritmos estatísticos de forecast, quantas medalhas os países deverão conquistar nos próximos Jogos Olímpicos. Será realizada uma análise básica apenas para entendimento do que é necessário para utilizar as funções de forecast do R – não serão feitos ajustes de tendência, sazonalidade, testes de estacionariedade, etc, ou seja, não será feita uma validação estatística da série.

Leitura dos dados e criação de subsets

Inicialmente vamos indicar o nosso diretório de trabalho e ler o arquivo CSV que possui os dados das medalhas conquistadas pelos países nas Olimpíadas, que organizamos na parte 1 do post, atribuindo ele a uma variável chamada “olimpiadas”. Em seguida vamos utilizar o comando head() para consultar os dados que a variável possui.

setwd("C:/") #diretório de trabalho
olimpiadas <- read.csv(file="MedalhasAtualizado.csv",head=TRUE,sep=";") #lendo arquivo CSV
head(olimpiadas) #verificando as primeiras linhas
r_head_olimpiadas

Resultado do comando head(olimpiadas)

Como podemos perceber, o nosso arquivo possui dados de medalhas conquistadas por todos os países em todas as Olimpíadas. Para criarmos gráficos separados com a previsão de medalhas para cada país, vamos utilizar o conceito de subset do R, ou seja, nós vamos criar três subconjuntos com dados de medalhas para três países: Estados Unidos, China e Brasil. Como as Olimpíadas não foram realizadas em 1940 e 1944 devido à Segunda Guerra Mundial, vamos realizar também um filtro de tempo, buscando dados a partir de 1948. No caso da China, que conquistou a primeira medalha em 1984 e dos Estados Unidos, que não participaram das Olimpíadas de 1980, vamos trazer dados a partir de 1984, para não perdermos a sequência de tempo.

usa <- subset(olimpiadas, Country == "United States" & as.Date(Data) >= as.Date("1984-01-01")) #subset estados unidos
china <- subset(olimpiadas, Country == "China" & as.Date(Data) >= as.Date("1984-01-01")) #subset china
brasil <- subset(olimpiadas, Country == "Brazil" & as.Date(Data) >= as.Date("1948-01-01")) #subset brasil

head(brasil,n=30) #verificando dados atribuídos

Para organizar os dados para traçar a linha de projeção corretamente, vamos ordenar os registros em ordem crescente de data utilizando os comandos a seguir:

ord.usa <- usa[order(usa$Data),] #ordenando por data
ord.china <- china[order(china$Data),] #ordenando por data
ord.brasil <- brasil[order(brasil$Data),] #ordenando por data

Convertendo os dados em séries temporais

Para utilizar a função de forecast do R, um dos requisitos é converter os valores em um objeto de “time series”. Utilizando a função ts() do R, vamos definir o início desta série e a frequência (como as Olimpíadas ocorrem de 4 em 4 anos, definimos a frequência como 0.25). No final do script vamos traçar um gráfico básico do R com os dados atuais (ainda sem a previsão) usando a função plot().

ts_olimpiadas_usa = ts(ord.usa$Total, 
                    start=c(1984,1),frequency=0.25) #início 1984, frequência de 4 em 4 anos

ts_olimpiadas_chi = ts(ord.china$Total, 
                    start=c(1984,1),frequency=0.25) #início 1984, frequência de 4 em 4 anos


ts_olimpiadas_bra = ts(ord.brasil$Total, 
                    start=c(1948,1),frequency=0.25) #início 1948, frequência de 4 em 4 anos


plot(ts_olimpiadas_usa) #gráfico de medalhas dos estados unidos até 2016
plot(ts_olimpiadas_chi) #gráfico de medalhas da china até 2016
plot(ts_olimpiadas_bra) #gráfico de medalhas do brasil até 2016

Gerando o gráfico de forecast

Agora que nós já temos os dados das medalhas conquistadas por Estados Unidos, China e Brasil organizados, podemos gerar a previsão de medalhas para este países. Vamos utilizar o modelo de ETS do R, que implementa o modelo estatístico “Exponential smoothing” . Este modelo gera no gráfico os valores médio, máximo e mínimo do intervalo de predição. Vamos realizar a previsão de medalhas para as próximas 4 Olimpíadas. O script abaixo gera os respectivos gráficos de forecast para cada país (utilizando a função plot()) e também mostra o resumo do resultado (função summary()).

library(forecast) #biblioteca de forecast

m_ets_usa = ets(ts_olimpiadas_usa) #modelo de ets, estados unidos
m_ets_chi = ets(ts_olimpiadas_chi) #modelo de ets, china
m_ets_bra = ets(ts_olimpiadas_bra) #modelo de ets, brasil

f_ets_usa = forecast(m_ets_usa, h=4) # previsão próximas 4 Olimpíadas
f_ets_chi = forecast(m_ets_chi, h=4) # previsão próximas 4 Olimpíadas
f_ets_bra = forecast(m_ets_bra, h=4) # previsão próximas 4 Olimpíadas

plot(f_ets_usa) #gráfico de forecast básico, estados unidos
plot(f_ets_chi) #gráfico de forecast básico, china
plot(f_ets_bra) #gráfico de forecast básico, brasil

summary(f_ets_usa) #resumo das informações do forecast, estados unidos
summary(f_ets_chi) #resumo das informações do forecast, china
summary(f_ets_bra) #resumo das informações do forecast, brasil
forecast_basico_brasil

Gráfico de forecast inicial do Brasil

Obs: foi utilizada a versão 7.1 do pacote de forecast do R.

Aperfeiçoando o gráfico de forecast

Para conseguir utilizar recursos visuais mais avançados, vamos utilizar o pacote ggfortify do R. Inicialmente vamos modificar as cores das linhas dos dados históricos, as cores e o tipo da linha de projeção e as cores do preenchimento do forecast. Utilizaremos a função autoplot() e algumas propriedades adicionais, conforme abaixo:

autoplot(f_ets_usa, ts.colour = 'blue',predict.colour = 'red',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'lightblue') #gráfico de forecast dos estados unidos
autoplot(f_ets_chi, ts.colour = 'red',predict.colour = 'black',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'yellow')  #gráfico de forecast da china
autoplot(f_ets_bra, ts.colour = 'darkgreen',predict.colour = 'blue',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'yellow') #gráfico de forecast do brasil
forecast_brasil

Gráfico de forecast do Brasil com modificação de cores

Agora vamos acrescentar as descrições no gráfico: título, nomes para o eixo X e para o eixo Y, além de um texto abaixo com uma descrição da previsão de medalhas.

autoplot(f_ets_usa, ts.colour = 'blue',predict.colour = 'red',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'lightblue') + ggtitle("Projeção de Total de Medalhas") + labs(x="Ano",y="Total de Medalhas") + annotate("text",x=2000,y=-1,label="Previsão para 2020: 118 medalhas",color="red")
autoplot(f_ets_chi, ts.colour = 'red',predict.colour = 'black',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'yellow') + ggtitle("Projeção de Total de Medalhas") + labs(x="Ano",y="Total de Medalhas") + annotate("text",x=2000,y=-1,label="Previsão para 2020: 72 medalhas",color="black")
autoplot(f_ets_bra, ts.colour = 'darkgreen',predict.colour = 'blue',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'yellow') + ggtitle("Projeção de Total de Medalhas") + labs(x="Ano",y="Total de Medalhas") + annotate("text",x=1980,y=-1,label="Previsão para 2020: 15 medalhas",color="darkblue")
forecast_brasil_titulos

Gráfico de forecast do Brasil com descrições adicionadas

Adicionando imagem no gráfico do forecast

Para finalizar, vamos adicionar as imagens das bandeiras dos países aos nossos gráficos de forecast. Vamos utilizar os pacotes png e grid, as funções readPNG (para ler a imagem .png) e RasterGrob (para posicionar a imagem no gráfico), e o comando annotation_custom(), para adicionar a imagem no gráfico. O script a seguir realiza esta tarefa:

packs <- c("png","grid") #lendo bibliotecas
lapply(packs, require, character.only = TRUE)

imgusa <- readPNG("usa.png") #carregando imagem bandeira estados unidos
imgchina <- readPNG("china.png") #carregando imagem bandeira china
imgbrasil <- readPNG("brasil.png") #carregando imagem bandeira brasil

gusa<- rasterGrob(imgusa, x=.8, y=.1, height=.15,width=.2,interpolate=TRUE) #definindo posição bandeira estados unidos
gchi<- rasterGrob(imgchina, x=.8, y=.1, height=.15,width=.2,interpolate=TRUE) #definindo posição bandeira china
gbra<- rasterGrob(imgbrasil, x=.8, y=.1, height=.15,width=.2,interpolate=TRUE) #definindo posição bandeira brasil

autoplot(f_ets_usa, ts.colour = 'blue',predict.colour = 'red',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'lightblue') + annotation_custom(gusa) + ggtitle("Projeção de Total de Medalhas") + labs(x="Ano",y="Total de Medalhas") + annotate("text",x=2000,y=-1,label="Previsão para 2020: 118 medalhas",color="red") #adicionando função annotation_custom()
autoplot(f_ets_chi, ts.colour = 'red',predict.colour = 'black',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'yellow') + annotation_custom(gchi) + ggtitle("Projeção de Total de Medalhas") + labs(x="Ano",y="Total de Medalhas") + annotate("text",x=2000,y=-1,label="Previsão para 2020: 72 medalhas",color="black")
autoplot(f_ets_bra, ts.colour = 'darkgreen',predict.colour = 'blue',predict.linetype = 'dashed', conf.int = TRUE,conf.int.fill = 'yellow') + annotation_custom(gbra) + ggtitle("Projeção de Total de Medalhas") + labs(x="Ano",y="Total de Medalhas") + annotate("text",x=1980,y=-1,label="Previsão para 2020: 15 medalhas",color="darkblue")

Obs: é importante que as imagens PNG das bandeiras dos países estejam no diretório de trabalho definido.

As imagens a seguir mostram a versão final dos gráficos:

forecast_estadosunidos_final

Gráfico de forecast final dos Estados Unidos

forecast_china_final

Gráfico de forecast final da China

forecast_brasil_final

Gráfico de forecast final do Brasil

O arquivo CSV “MedalhasAtualizado.csv” que possui os dados das medalhas, os scripts em R para instalação dos pacotes necessários e dos comandos para gerar os gráficos de previsão, além das imagens e dos pacotes do R utilizados, estão disponíveis no GitHub .

Referências:

Plotting Time Series with ggplot2 and ggfortify

Add a background png image to ggplot2

Subsetting Data

Time-Series Objects

Forecasting using ETS models

ggfortify : Extension to ggplot2 to handle some popular packages – R software and data visualization

Parte 1: Capturando dados das medalhas nas Olimpíadas de múltiplas páginas web utilizando Power Query/ Power BI Desktop

Este post será divido em duas partes. O nosso objetivo principal é conseguir prever – com base nos dados históricos do total de medalhas – quantas medalhas os países devem ganhar nas próximas Olimpíadas. Na parte 1 iremos buscar e carregar os dados necessários sobre o histórico de medalhas conquistadas por cada país em todas as Olimpíadas utilizando o Power Query; na parte 2, iremos gerar os gráficos com as informações de previsão usando o software estatístico R.

Onde encontrar os dados

A etapa mais difícil em qualquer processo de análise de dados é encontrar os dados, garantir que eles estejam corretos e dentro de um formato esperado. Para buscar dados da quantidade de medalhas conquistada por cada país em cada uma das edições da história das Olimpíadas utilizaremos o site Sports Reference, que possui todas estas informações até os Jogos Olímpicos de 2012*.

Captura dos dados utilizando Power Query

Mas e como capturar dados da web? Para isso vamos utilizar o Power Query, que é um add-in para Excel, mas que também funciona dentro do Power BI Desktop, a ferramenta de criação de relatórios de BI da Microsoft. O Power Query é considerado uma ferramenta de “self-service ETL”, ou seja, permite que usuários sem tanto conhecimento de técnicas de extração de dados consigam buscar e transformar seus dados com facilidade. Utilizando tanto o Power Query para Excel como ele no Power BI Desktop conseguimos obter o mesmo resultado. No exemplo a seguir vou utilizar o Power Query para Excel 2013 para no final exportamos o resultado para o formato CSV.

Criando uma função para buscar dados de várias páginas web

Para ler uma página web utilizando o Power Query temos que, na aba “Power Query”, escolher a opção “From Web” e inserirmos a URL que desejamos nos conectar (na imagem abaixo, colocamos o endereço da página das Olimpíadas de 2012). Após isso, clicar em “OK”:

powerquery_web

Realizando a conexão no Power Query com a página web que possui dados de medalhas das Olimpíadas de 2012

Vamos precisar fazer algumas alterações na consulta do Power Query para conseguirmos ler todas as páginas, com dados de todas as Olimpíadas. Para verificarmos a consulta que o Power Query está fazendo, basta clicar na opção “Advanced Editor”:

powerquery_advanced_editor

Query inicial na opção “Advanced Editor”

A query abaixo é a nossa query inicial, que traz dados de uma única edição das Olimpíadas:

let
    Source = Web.Page(Web.Contents("http://www.sports-reference.com/olympics/summer/2012/")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Rk", Int64.Type}, 
{"Country", type text}, {"Gold", Int64.Type}, {"Silver", Int64.Type}, 
{"Bronze", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type"

 

Para buscarmos dados desta e de todas as outras Olimpíadas ao mesmo tempo, precisaremos criar uma função que realize esta consulta a todas as páginas e acrescentar o parâmetro desta função (chamaremos de “year”) no lugar do ano. Para isso, vamos adicionar duas novas expressões no código:

  • (year as number) as table => : para criar a função;
  • “& Number.ToText(year) &” : para concatenar o ano na URL.

A nova query com a função criada ficará desta forma:

= (year as number) as table =>
let
    Source = Web.Page(Web.Contents
("http://www.sports-reference.com/olympics/summer/"& Number.ToText(year) &"")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Rk", Int64.Type}, 
{"Country", type text}, {"Gold", Int64.Type}, {"Silver", Int64.Type}, 
{"Bronze", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type"

É possível testar o funcionamento da função clicando no botão “Invoke” e passando o ano de referência (imagem a seguir):

powerquery_function

Botão “Invoke” para testar o funcionamento da função

Vamos renomear a query para “GetData” (este nome será importante para a chamada da função a seguir).

Adicionando dados dos anos olímpicos à função

Testado o funcionamento da função, iremos criar uma tabela com os anos em que houve Olimpíadas para, dessa forma, podermos passar estes anos como parâmetros para a função, retornando assim os dados de todas as páginas. Na aba “Power Query”, vamos escolher criar uma nova conexão “From Other Sources”, “Blank Query”.

No primeiro passo, adicionaremos os anos dentro do espaço disponível para preenchimento da fórmula. Chamaremos esta consulta de “Medalhas”:

{2012,2008,2004,2000,1996,1992,1988,1984,1980,1976,1972,1968,1964,1960,1956,1952,1948,1936,1932,1928,1924,1920,1912,1908,1904,1900,1896}

No segundo, é preciso converter a lista gerada em tabela clicando na opção “To Table” (imagem abaixo):

powerquey_convert_to_table

Botão “To Table” dentro do Power Query

No terceiro passo, vamos adicionar uma nova coluna (imagem a seguir) nesta consulta “Medalhas” com a seguinte chamada da função:

GetData([Year])
powerquery_customcolumn

Botão “Add Custom Column” no Power Query

Feito isso, o Power Query irá carregar a página conforme a imagem abaixo. Vamos expandir todas as colunas para carregar todas linhas referentes às medalhas conquistadas pelo países em todas as edições das Olimpíadas para o Power Query (imagem abaixo):

powerquery_expand_columns

Tela que retorna dados de todas as Olimpíadas

Para finalizar esta etapa, como iremos trabalhar com séries temporais no R para realizar as previsões (parte 2), vamos criar uma coluna customizada de data para facilitar nosso trabalho (imagem abaixo):

powerquery_date_column

Adicionando coluna de data no formato YYYY-MM-DD

Complementando com dados das Olimpíadas Rio 2016

Feita esta etapa inicial, agora vamos adicionar as informações de medalhas das Olimpíadas Rio 2016. Para isso, vamos utilizar o quadro de medalhas da Wikipédia. De forma idêntica ao que fizemos na etapa anterior, basta criar uma nova consulta para buscar os dados da Wikipédia e realizarmos algumas transformações básicas para deixar esta nova consulta com as mesmas colunas que a consulta já criada.

Por fim, precisamos unir as informações de medalhas de 1896 até 2012 com a lista de medalhas de 2016. Para isso vamos utilizar a funcionalidade de “Append Queries” do Power Query. Selecionamos a nova consulta (chamamos de “Medalhas Atualizado”) e clicamos nesta opção (imagem abaixo) para juntar os dados da consulta “Medalhas” com os dados da consulta “Medalhas Atualizado”.

powerquery_append_medalhas_atualizado

Botão “Append Queries” do Power Query

Pronto! Basta clicar em “Close em Load” para carregar os dados para o Excel e depois salvá-los no formato CSV.

Na parte 2, iremos utilizar estes dados já organizados para criar os gráficos de forecast utilizando o R para prever o total de medalhas que os países devem conquistar nas próximas Olimpíadas.

P.S: Para facilitar, adicionei os scripts do Power Query (linguagem M) no GitHub (quem quiser reproduzir, é só copiar estes scripts e colar na aba “Advanced Editor” do Power Query para obter os dados sem precisar fazer todos estes passos). Quem quiser somente o arquivo CSV final para testar, também está lá.

*Acesso: 24/08/2016

Referências:

Iterating over multiple pages of web data using Power Query

Sugestão de leitura relacionada:

Tutorial: análise do Facebook com o Power BI Desktop

Parte 2: Gerando gráficos de forecast utilizando R para previsão de medalhas nas Olimpíadas

Gerando relatórios e análises da Premier League 15/16 com R e Power BI

A inspiração para este post veio do blog do Jason Thomas, que criou no Power BI um mapa dos arremessos do jogador de basquete Stephen Curry durante alguns jogos da NBA (vale a pena dar uma olhada clicando aqui).

Mantendo esta linha de realizar análises com dados de esportes, minha escolha então foi utilizar os dados do Fantasy da Premier League, que apresenta estatísticas de todos os jogadores que disputaram o Campeonato Inglês 15/16. O Fantasy da Premier League possui uma API onde conseguimos acessar os dados no formato JSON.

Como são vários arquivos JSON que precisaremos ler, vamos utilizar a ferramenta estatística “R” para retornar os dados que precisamos, e em seguida iremos levar o script do R para o Power BI Desktop para gerar os relatórios.

Integração do R com Power BI Desktop

Para utilizar scripts do R no Power BI Desktop são necessários alguns passos que você pode verificar neste link.

Leitura e carga dos dados da Premier League

Para conseguirmos obter os dados de todos os jogadores que estão disponíveis na API do Fantasy da Premier League, iremos utilizar um script na linguagem R para realizar a coleta destes dados.

Para adicionar o script do R no Power BI Desktop, selecione “Obter Dados”, “Outras”, “Script do R” (imagem abaixo):

r_script_powerbi

Script do R no Power BI Desktop

 

O script do R a seguir¹ carrega os 716 arquivos JSON, um para cada jogador. Inicialmente instalamos os pacotes necessários no R, e depois é feito o loop para trazer todas as informações.

Dica: execute o script no Microsoft R Open² para testar se ele está funcionando corretamente, e só depois leve-o para o Power BI.

install.packages("jsonlite")
 install.packages('curl')

library(jsonlite)

url <- "http://fantasy.premierleague.com/web/api/elements/"
 names(fromJSON(paste0(url,1))) # Concatenate URL and player id to fetch player data

url <- "http://fantasy.premierleague.com/web/api/elements/"
  
 ## Both paste commands produce same output
 paste(url, 1, sep = "")
 paste0(url, 1)

url <- "http://fantasy.premierleague.com/web/api/elements/"
 toJSON(fromJSON(paste0(url, 1)), pretty = TRUE)

## List of relevant fields we are interested in
  
 relevantFields <- c("points_per_game","total_points","type_name",
  "team_name","team_code","team_id",
  "id","status","first_name","second_name",
  "now_cost","value_form","team",
  "ep_next","minutes","goals_scored",
  "assists","clean_sheets","goals_conceded",
  "own_goals","penalties_saved","penalties_missed",
  "yellow_cards","red_cards","saves",
  "bonus","bps","ea_index",
  "value_form","value_season","selected_by")
  
 numCols = length(relevantFields) # Length of relevant string vector
 # Initializing an empty dataframe
 allplayerdata <- data.frame(matrix(NA,nrow=1,ncol=numCols))
 allplayerdata <- allplayerdata[-1,]
  
 fetchData <- function(i) {
  
  res <- try(jsondata <- fromJSON(paste0(url,i)))
  
  if(!inherits(res, "try-error")) {
  
       jsondata <- jsondata[which(names(jsondata) %in% relevantFields)]
  }
 }
  
 allplayerdata <- lapply(1:716, fetchData)
 allplayerdata <- do.call(rbind, lapply(allplayerdata,
                                            data.frame,
                                            stringsAsFactors=FALSE))

Após isso, aguarde o tempo de leitura dos arquivos, e você terá os dados carregados para o seu relatório do Power BI Desktop, conforme a imagem a seguir:

premierleague_data_powerbi

Dados dos jogadores da Premier League 2015/2016 importados

 

Obs: realizei a consulta a estes dados em Junho/2016. Quando você reproduzir este script, irá buscar os dados referentes à temporada que estiver sendo disputada no momento.

Geração de relatórios

Após a obtenção dos dados, temos infinitas possibilidades de análises que podemos realizar. Dois relatórios de exemplo que podemos gerar são os seguintes:

  • Comparação entre estatísticas de ataque (gols marcados) e defesa (jogos sem sofrer gol) para identificar jogadores mais completos
relatorio_premierleague

Relatório comparativo entre gols marcados e jogos sem sofrer gol

 

No gráfico acima foi realizada uma comparação entre os jogadores do Leicester City e do Arsenal, onde destacaram-se os jogadores Giroud, Sánchez, Mahrez e Vardy pelo número de gols marcados.

Para gerar este relatório utilizei o gráfico de dispersão do Power BI, e adicionei informações como nome do jogador, time do jogador, gols marcados, “clean sheets” (jogos sem sofrer gol) e minutos jogados.

Para o filtro por clube, que exibe os escudos das equipes para filtrar as informações exibidas no gráfico, utilizei um recurso do Power BI chamado “Custom Visuals” (mais detalhes aqui), e importei um visual chamado “Chiclet Slicer”. Montei uma planilha Excel com as URL’s das logos de todos os clubes e importei esta planilha para o Power BI, definindo esta coluna sendo do tipo “URL de imagem”, conforme figura abaixo:

dados_logos

URL para exibição das imagens dos escudos dos clubes

 

  • Identificação de jogadores que precisam de menos minutos para fazer um gol
relatorio2_premierleague

Relatório de jogadores com melhor média de gols por minuto

 

No relatório acima realizei filtros para trazer somente os jogadores que fizeram pelo menos 5 gols e que tiveram média de pelo menos 1 gol a cada 200 minutos, para ter uma análise mais real e trazer os melhores jogadores nesta estatística.

Percebe-se que os jogadores Iheanacho e Aguero, ambos do Manchester City, foram os líderes nesta comparação, sendo que Aguero disputou mais minutos durante o campeonato.

Para calcular esta média de gols marcados por minuto foram criadas três medidas dentro do Power BI Desktop:

Gols Marcados = SUM(allplayerdata[goals_scored])
Minutos Disputados = SUM(allplayerdata[minutes])
Minutos por gol = IF([Gols Marcados]= 0;0;[Minutos Disputados]/[Gols Marcados])

Assim, conseguimos chegar no resultado correto através deste cálculo (por exemplo, Iheanacho fez 8 gols em 766 minutos, que dá a média de 1 gol a cada 96 minutos, aproximadamente).

Utilizei o gráfico de linhas e colunas empilhadas do Power BI, e adicionei informações como minutos disputados, minutos por gol (medidas criadas), nome do time e nome do jogador.

Publicação e geração do dashboard

Terminado o processo de criação, podemos publicar nossos relatórios utilizando o serviço do Power BI, e assim montarmos e compartilharmos o dashboard com outros usuários. Basta clicar no botão “Publicar” e entrar na sua conta do Power BI (utilizando um e-mail corporativo).

powerbi_publicar

Botão de publicação dos relatórios para o Power BI

 

Após adicionarmos as visualizações e criarmos um dashboard, o resultado pode ficar dessa forma:

dashboard_premierleague

Dashboard exibindo os gráficos criados

 

Este dashboard pode ser visualizado também pelo aplicativo do Power BI, que está disponível nas principais lojas virtuais³.

Para quem quiser reproduzir este exemplo e criar outros relatórios com estes dados, disponibilizei o script .R e as imagens dos escudos dos clubes em um diretório do GitHub.

Atualização: houve uma mudança na URL da API do Fantasy Premier League para a temporada 16/17. Em vez da URL antiga (http://fantasy.premierleague.com/web/api/elements/1), utilizar a nova (https://fantasy.premierleague.com/drf/element-summary/1).

Referências:

  1. Post que encontrei o script em R para ler os arquivos JSON do Fantasy da Premier League – Importing JSON Data
  2. O download do Microsoft R Open pode ser realizado aqui
  3. Os links de download do Power BI Desktop, do aplicativo do Power BI e de outros recursos pode ser encontrado aqui

 

Importando dados JSON da Premier League com SQL Server 2016

Quando pensamos em projetos de BI e Big Data, um dos principais pontos que temos que planejar são as fontes de dados que iremos utilizar, estas que contém os dados que precisamos consumir para gerar as análises e os relatórios. E cada vez mais temos que trabalhar com formatos diferentes, devido às novas maneiras de armazenar as informações que existem.

O SQL Server 2016, visando se adaptar à esta realidade, traz nesta versão diversas funcionalidades e recursos novos, entre eles a possibilidade de manipular arquivos JSON. Podemos não somente importar arquivos, como também escrever consultas para retornar informações destes arquivos JSON e até gerar saídas neste formato, parecido com o que já existe com o formato XML.

Para exemplificar esta funcionalidade, realizei uma consulta à API do Fantasy Premier League 15/16, que pode ser acessada através da URL a seguir, e que gera um arquivo JSON para cada jogador (no exemplo abaixo, dados do jogador de id 170):

http://fantasy.premierleague.com/web/api/elements/170 *

Para quem não conhece, o Fantasy da Premier League é um game onde o desempenho dos jogadores de futebol que jogam no Campeonato Inglês valem pontos, ou seja, eles marcam pontos de acordo com as suas estatísticas. E são exatamente estas estatísticas que nós vamos importar para o SQL Server.

Utilizei o script abaixo para realizar a importação:

—Criando banco de dados de exemplo

CREATE DATABASE Sports

GO

USE Sports

GO

—Criando tabela com coluna DOC no formato JSON

CREATE TABLE Player

(id INT IDENTITY CONSTRAINT PK_JSON PRIMARY KEY,

DOC NVARCHAR(MAX) constraint [Properly formatted JSON] CHECK (ISJSON(DOC)>0));

—Gerando loop para carregar todos os arquivos para o SQL Server

declare @i int = 1

declare @file nvarchar(max)

declare @json nvarchar(MAX)

declare @sql nvarchar(max)

WHILE(@i<724)

BEGIN

SET @file = ”’c:\json\’ + cast(@i as varchar(5)) + ‘.json”’;

SET @sql = ‘INSERT INTO Player

SELECT BulkColumn

FROM OPENROWSET (BULK ‘+@file+‘, SINGLE_CLOB) as j’

SET @i = @i +1;

EXEC sp_executesql @sql

END

Assim, conseguimos ter os arquivos JSON de todos os jogadores dentro do nosso banco de dados do SQL Server 2016!

Podemos, por exemplo, realizando a consulta abaixo, retornar os 10 jogadores do Leicester City que mais fizeram gols no campeonato:

—Consulta para retornar os 10 jogadores do Leicester City que mais marcaram gols

SELECT

TOP 10

JSON_VALUE(doc,‘$.web_name’) AS player_name,

JSON_VALUE(doc,‘$.goals_scored’) AS goals_scored

FROM Player p

WHERE JSON_VALUE(doc,‘$.team_name’) = ‘Leicester’

ORDER BY cast(JSON_VALUE(doc,‘$.goals_scored’)as int) DESC

Resultado (imagem abaixo):

gols_leicester_json_query

Para facilitar, disponibilizei os códigos e os arquivos utilizados no seguinte diretório do GitHub:

GitHub – Premier League Datasets

* Atualização: houve uma mudança na URL da API do Fantasy Premier League para a temporada 16/17. Em vez da URL antiga (http://fantasy.premierleague.com/web/api/elements/170), utilizar a nova (https://fantasy.premierleague.com/drf/element-summary/170). Para reproduzir o script, utilizar arquivos .json que se encontram no GitHub.

Referência:

Importing JSON files into SQL Server using OPENROWSET (BULK)

Sugestão de leitura relacionada:

JSON support in SQL Server 2016