terça-feira, 25 de julho de 2017

Apache Spark Streaming Tutorial: Identificando as Hashtags de Tendência do Twitter

Hoje em dia, os dados estão crescendo e se acumulando mais rápido do que nunca. Atualmente, cerca de 90% de todos os dados gerados em nosso mundo foram gerados somente nos últimos dois anos. Devido a essa taxa de crescimento impressionante, as grandes plataformas de dados tiveram que adotar soluções radicais para manter esses enormes volumes de dados.

Uma das principais fontes de dados hoje são as redes sociais. Permita-me demonstrar um exemplo da vida real: lidar, analisar e extrair informações de dados de redes sociais em tempo real usando uma das soluções eco de Big Data mais importantes, Apache Spark e Python.



Neste artigo, vou ensinar-lhe como criar um aplicativo simples que lê fluxos online do Twitter usando o Python e, em seguida, processa os tweets usando o Apache Spark Streaming para identificar hashtags e, finalmente, retorna as principais hashtags de tendências e representa esses dados em um painel de controle.

Criando suas próprias credenciais para as APIs do Twitter
Para obter tweets do Twitter, você precisa se registrar no TwitterApps clicando em "Criar novo aplicativo" e, em seguida, preencha o formulário abaixo, depois clique em "Criar seu aplicativo do Twitter".



Em segundo lugar, acesse seu aplicativo recém-criado e abra a guia "Keys and Access Tokens". Em seguida, clique em "Gerar meu token de acesso".



Seus novos tokens de acesso aparecerão abaixo.



E agora você está pronto para o próximo passo.

Criando o Twitter HTTP Client
Nesta etapa, vou mostrar-lhe como criar um cliente simples que obterá os tweets da API do Twitter usando o Python e os passará para a instância Spark Streaming. Vai ser fácil de seguir para qualquer Python Developer.

Primeiro, vamos criar um arquivo chamado [twitter_app.py] e, em seguida, adicionaremos o código juntos como abaixo.

Importe as bibliotecas que usaremos abaixo:


import socket
import sys
import requests
import requests_oauthlib
import json


E adicione as variáveis que serão usadas no OAuth para se conectar ao Twitter conforme abaixo:


# Replace the values below with yours
ACCESS_TOKEN = 'YOUR_ACCESS_TOKEN'
ACCESS_SECRET = 'YOUR_ACCESS_SECRET'
CONSUMER_KEY = 'YOUR_CONSUMER_KEY'
CONSUMER_SECRET = 'YOUR_CONSUMER_SECRET'
my_auth = requests_oauthlib.OAuth1(CONSUMER_KEY, CONSUMER_SECRET,ACCESS_TOKEN, ACCESS_SECRET)


Agora, vamos criar uma nova função chamada get_tweets que chamará o URL da API do Twitter e retornará a resposta para um fluxo de tweets.

def get_tweets():
 url = 'https://stream.twitter.com/1.1/statuses/filter.json'
 query_data = [('language', 'en'), ('locations', '-130,-20,100,50'),('track','#')]
 query_url = url + '?' + '&'.join([str(t[0]) + '=' + str(t[1]) for t in query_data])
 response = requests.get(query_url, auth=my_auth, stream=True)
 print(query_url, response)
 return response


Em seguida, crie uma função que obtenha a resposta acima e extraia o texto dos tweets do objeto JSON de todos os tweets. Depois disso, envie todos os tweets à instância Spark Streaming (serão discutidos mais tarde) através de uma conexão TCP.

def send_tweets_to_spark(http_resp, tcp_connection):
 for line in http_resp.iter_lines():
     try:
         full_tweet = json.loads(line)
         tweet_text = full_tweet['text']
         print("Tweet Text: " + tweet_text)
         print ("------------------------------------------")
         tcp_connection.send(tweet_text + '\n')
     except:
         e = sys.exc_info()[0]
         print("Error: %s" % e)


Agora vamos fazer a parte principal, um app host de conexão socket, na qual o Spark vai se conectar. Vamos configurar o IP aqui para ser o localhost, pois todos serão executados na mesma máquina e na porta 9009. Então chamaremos o método get_tweets, que fizemos acima, para obter os tweets do Twitter e passar sua resposta junto com a conexão de socket para send_tweets_to_spark para enviar os tweets para Spark.

TCP_IP = "localhost"
TCP_PORT = 9009
conn = None
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.bind((TCP_IP, TCP_PORT))
s.listen(1)
print("Waiting for TCP connection...")
conn, addr = s.accept()
print("Connected... Starting getting tweets.")
resp = get_tweets()
send_tweets_to_spark(resp, conn)


Configurando nossa aplicação de streaming de Apache Spark
Vamos construir o nosso aplicativo Spark streaming que fará o processamento em tempo real para os tweets recebidos, extrairá os hashtags deles e calculará quantas hashtags foram mencionadas.



Primeiro, temos que criar uma instância de Contexto Spark (sc), então criamos o Contexto de Transmissão ssc de sc com um intervalo de lote de dois segundos que fará a transformação em todos os fluxos recebidos a cada dois segundos. Observe que estabelecemos o nível de log para ERROR para desabilitar a maioria dos logs que o Spark escreve.

Nós definimos um ponto de controle aqui para permitir a verificação periódica do RDD; Isso é obrigatório para ser usado em nosso aplicativo, pois usaremos transformações com estado (serão discutidas mais adiante na mesma seção).

Em seguida, definimos nosso DStream dataStream principal que se conectará ao servidor de socket que criamos antes, na porta 9009, e leia os tweets dessa porta. Cada registro no DStream será um tweet.

from pyspark import SparkConf,SparkContext
from pyspark.streaming import StreamingContext
from pyspark.sql import Row,SQLContext
import sys
import requests
# create spark configuration
conf = SparkConf()
conf.setAppName("TwitterStreamApp")
# create spark context with the above configuration
sc = SparkContext(conf=conf)
sc.setLogLevel("ERROR")
# create the Streaming Context from the above spark context with interval size 2 seconds
ssc = StreamingContext(sc, 2)
# setting a checkpoint to allow RDD recovery
ssc.checkpoint("checkpoint_TwitterApp")
# read data from port 9009
dataStream = ssc.socketTextStream("localhost",9009)


Agora, vamos definir a nossa lógica de transformação. Primeiro vamos dividir todos os tweets em palavras e colocá-los em palavras RDD. Então, vamos filtrar apenas hashtags de todas as palavras e mapeá-los para um par de (hashtag, 1) e colocá-los no hashtags RDD.

Então precisamos calcular quantas vezes a hashtag foi mencionada. Nós podemos fazer isso usando a função reductionByKey. Esta função calculará quantas vezes a hashtag foi mencionada por cada lote, ou seja, irá redefinir as contagens em cada lote.

No nosso caso, precisamos calcular as contagens em todos os lotes, então usaremos outra função chamada updateStateByKey, pois esta função permite que você mantenha o estado de RDD ao atualizá-lo com novos dados. Desta forma, é chamado de Transformação de Estado.

Note que, para usar updateStateByKey, você precisa configurar um ponto de controle e o que fizemos na etapa anterior.

# split each tweet into words
words = dataStream.flatMap(lambda line: line.split(" "))
# filter the words to get only hashtags, then map each hashtag to be a pair of (hashtag,1)
hashtags = words.filter(lambda w: '#' in w).map(lambda x: (x, 1))
# adding the count of each hashtag to its last count
tags_totals = hashtags.updateStateByKey(aggregate_tags_count)
# do processing for each RDD generated in each interval
tags_totals.foreachRDD(process_rdd)
# start the streaming computation
ssc.start()
# wait for the streaming to finish
ssc.awaitTermination()


O updateStateByKey assume uma função como um parâmetro chamado de função de atualização. Ele é executado em cada item no RDD e faz a lógica desejada.

No nosso caso, criamos uma função de atualização chamada aggregate_tags_count que somará todos os novos valores para cada hashtag e os adiciona ao total_sum que é a soma em todos os lotes, e salva os dados em tags_totals RDD.

def aggregate_tags_count(new_values, total_sum):
 return sum(new_values) + (total_sum or 0)


Em seguida, fazemos o processamento em tags_totals RDD em cada lote para convertê-lo em tabela temporária usando o Spark SQL Context e, em seguida, execute uma instrução de seleção para recuperar os dez principais hashtags com suas contagens e colocá-los em frame de dados hashtag_counts_df.

def get_sql_context_instance(spark_context):
 if ('sqlContextSingletonInstance' not in globals()):
        globals()['sqlContextSingletonInstance'] = SQLContext(spark_context)
 return globals()['sqlContextSingletonInstance']
def process_rdd(time, rdd):
 print("----------- %s -----------" % str(time))
 try:
     # Get spark sql singleton context from the current context
     sql_context = get_sql_context_instance(rdd.context)
     # convert the RDD to Row RDD
     row_rdd = rdd.map(lambda w: Row(hashtag=w[0], hashtag_count=w[1]))
     # create a DF from the Row RDD
     hashtags_df = sql_context.createDataFrame(row_rdd)
     # Register the dataframe as table
     hashtags_df.registerTempTable("hashtags")
     # get the top 10 hashtags from the table using SQL and print them
     hashtag_counts_df = sql_context.sql("select hashtag, hashtag_count from hashtags order by hashtag_count desc limit 10")
     hashtag_counts_df.show()
     # call this method to prepare top 10 hashtags DF and send them
     send_df_to_dashboard(hashtag_counts_df)
 except:
     e = sys.exc_info()[0]
     print("Error: %s" % e)


O último passo no nosso aplicativo Spark é enviar o quadro de dados hashtag_counts_df para o aplicativo do painel. Então, vamos converter o quadro de dados em dois arrays, um para os hashtags e outro para suas contagens. Em seguida, nós os enviaremos para o aplicativo do painel de controle através da API REST.

def send_df_to_dashboard(df):
 # extract the hashtags from dataframe and convert them into array
 top_tags = [str(t.hashtag) for t in df.select("hashtag").collect()]
 # extract the counts from dataframe and convert them into array
 tags_count = [p.hashtag_count for p in df.select("hashtag_count").collect()]
 # initialize and send the data through REST API
 url = 'http://localhost:5001/updateData'
 request_data = {'label': str(top_tags), 'data': str(tags_count)}
 response = requests.post(url, data=request_data)


Finalmente, aqui está um exemplo de saída do Spark Streaming durante a execução e a impressão do hashtag_counts_df, você notará que a saída é impressa exatamente a cada dois segundos, de acordo com os intervalos do lote.



Crie um painel de controle em tempo real simples para representar os dados
Agora, vamos criar um aplicativo de painel simples que será atualizado em tempo real pela Spark. Vamos construí-lo usando Python, Flask e Charts.js.

Primeiro, vamos criar um projeto Python com a estrutura abaixo e baixar e adicionar o arquivo Chart.js no diretório estático.



Então, no arquivo da aplicação.py, criamos uma função chamada update_data, que será chamada pelo Spark através da URL http://localhost:5001/ updateData para atualizar as matrizes de valores e etiquetas globais.

Além disso, a função refresh_graph_data é criada para ser chamada pela solicitação AJAX para retornar os novos rótulos e matrizes de valores atualizados como JSON. A função get_chart_page renderizará a página chart.html quando chamada.

from flask import Flask,jsonify,request
from flask import render_template
import ast
app = Flask(__name__)
labels = []
values = []
@app.route("/")
def get_chart_page():
 global labels,values
 labels = []
 values = []
 return render_template('chart.html', values=values, labels=labels)
@app.route('/refreshData')
def refresh_graph_data():
 global labels, values
 print("labels now: " + str(labels))
 print("data now: " + str(values))
 return jsonify(sLabel=labels, sData=values)
@app.route('/updateData', methods=['POST'])
def update_data():
 global labels, values
 if not request.form or 'data' not in request.form:
     return "error",400
 labels = ast.literal_eval(request.form['label'])
 values = ast.literal_eval(request.form['data'])
 print("labels received: " + str(labels))
 print("data received: " + str(values))
 return "success",201
if __name__ == "__main__":
 app.run(host='localhost', port=5001)


Agora, vamos criar um gráfico simples no arquivo chart.html para exibir os dados da hashtag e atualizá-los em tempo real. Conforme definido abaixo, precisamos importar as bibliotecas de JavaScript Chart.js e jquery.min.js.

Na etiqueta do corpo, temos que criar uma tela e dar-lhe uma ID para fazer referência a ela ao exibir o gráfico usando o JavaScript na próxima etapa.

<!DOCTYPE html>
<html>
 <head>
     <meta charset="utf-8"/>
     <title>Top Trending Twitter Hashtags</title>
     <script src='static/Chart.js'></script>
     <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
 
 </head>
 <body>
         <h2>Top Trending Twitter Hashtags</h2>
         <div style="width:700px;height=500px">
             <canvas id="chart"></canvas>
         </div>
 </body>
</html>


Agora, vamos construir o gráfico usando o código JavaScript abaixo. Primeiro, obtemos o elemento de tela, e então criamos um novo objeto de gráfico e passamos o elemento de tela para ele e definimos seu objeto de dados como abaixo.

Observe que os labels de dados e os dados são delimitados com rótulos e variáveis de valores que são retornados enquanto renderiza a página ao chamar a função get_chart_page no arquivo app.py.

A última parte restante é a função que está configurada para fazer uma solicitação Ajax a cada segundo e chama a URL /refreshData, que executará refresh_graph_data em app.py e retornará os novos dados atualizados e, em seguida, atualizará o gráfico que renderiza os novos dados.

<script>
   var ctx = document.getElementById("chart");
   var myChart = new Chart(ctx, {
     type: 'horizontalBar',
     data: {
         labels: [{% for item in labels %}
                   "{{item}}",
                  {% endfor %}],
         datasets: [{
             label: '# of Mentions',
             data: [{% for item in values %}
                       {{item}},
                     {% endfor %}],
             backgroundColor: [
                 'rgba(255, 99, 132, 0.2)',
                 'rgba(54, 162, 235, 0.2)',
                 'rgba(255, 206, 86, 0.2)',
                 'rgba(75, 192, 192, 0.2)',
                 'rgba(153, 102, 255, 0.2)',
                 'rgba(255, 159, 64, 0.2)',
                 'rgba(255, 99, 132, 0.2)',
                 'rgba(54, 162, 235, 0.2)',
                 'rgba(255, 206, 86, 0.2)',
                 'rgba(75, 192, 192, 0.2)',
                 'rgba(153, 102, 255, 0.2)'
             ],
             borderColor: [
                 'rgba(255,99,132,1)',
                 'rgba(54, 162, 235, 1)',
                 'rgba(255, 206, 86, 1)',
                 'rgba(75, 192, 192, 1)',
                 'rgba(153, 102, 255, 1)',
                 'rgba(255, 159, 64, 1)',
                 'rgba(255,99,132,1)',
                 'rgba(54, 162, 235, 1)',
                 'rgba(255, 206, 86, 1)',
                 'rgba(75, 192, 192, 1)',
                 'rgba(153, 102, 255, 1)'
             ],
             borderWidth: 1
         }]
     },
     options: {
         scales: {
             yAxes: [{
                 ticks: {
                     beginAtZero:true
                 }
             }]
         }
     }
   });
   var src_Labels = [];
   var src_Data = [];
   setInterval(function(){
     $.getJSON('/refreshData', {
     }, function(data) {
         src_Labels = data.sLabel;
         src_Data = data.sData;
     });
     myChart.data.labels = src_Labels;
     myChart.data.datasets[0].data = src_Data;
     myChart.update();
   },1000);
</script>


Execução das aplicações em conjunto
Vamos executar os três aplicativos na ordem abaixo:
1. Cliente do aplicativo do Twitter.
2. Spark App.
3. Dashboard Web App.

Então você pode acessar o painel de controle em tempo real usando a URL

Agora, você pode ver seu gráfico sendo atualizado, conforme abaixo:



Apache Streaming - Casos de uso da vida real
Aprendemos a fazer análises de dados simples em dados em tempo real usando Spark Streaming e integrando-o diretamente com um painel simples usando um serviço web RESTful. A partir deste exemplo, podemos ver o quão poderoso é o Spark, pois captura um fluxo maciço de dados, transforma-o e extrai informações valiosas que podem ser usadas facilmente para tomar decisões a qualquer momento. Existem muitos casos de uso úteis que podem ser implementados e que podem servir para diferentes indústrias, como notícias ou marketing.



Exemplo da indústria de notícias
Podemos rastrear as hashtags mais freqüentemente mencionadas para saber quais os temas em que as pessoas estão falando mais nas mídias sociais. Além disso, podemos acompanhar hashtags específicos e seus tweets para saber o que as pessoas estão dizendo sobre tópicos ou eventos específicos no mundo.

Exemplo de Marketing

Podemos coletar o fluxo de tweets e, fazendo análises de sentimentos, classificamo-los e determinemos os interesses das pessoas, a fim de direcioná-los com ofertas relacionadas aos seus interesses.

Além disso, há muitos casos de uso que podem ser aplicados especificamente para grandes análises de dados e podem servir muitas indústrias. Para mais casos de uso de Apache Spark em geral, sugiro que você verifique uma de nossas postagens anteriores.

Eu encorajo você a ler mais sobre Spark Streaming a partir daqui para saber mais sobre suas capacidades e fazer uma transformação mais avançada nos dados para obter mais informações em tempo real.

BY HANEE' MEDHAT - FREELANCE SOFTWARE ENGINEER @TOPTAL

segunda-feira, 3 de julho de 2017

Power Pivot for Excel: principais casos de uso e exemplos

Todo analista financeiro é um expert com o Excel. No entanto, à medida que o armazenamento se torna mais barato, nossas organizações estão acumulando mais dados. E cada vez mais dados tornam mais difícil o trabalho com o Excel, chegando no limite das linhas (1.048.576) ou o documento fica extremamente lento para processar os dados.

Frequentemente a decisão é se vamos perder algum dado importante, ou trabalhar ou se vamos trabalhar em um tedioso workbook. Às vezes, temos de ser criativos para combinar dois grandes conjuntos de dados. Recorremos a fórmulas enroladas e esperamos para sempre que os cálculos sejam resolvidos.

Felizmente, você não precisa mais tomar essas decisões. A funcionalidade Power Pivot do Excel fornece uma maneira de extrair, combinar e analisar grandes conjuntos de dados. Apesar de ter sido lançado com o Excel 2010, a maioria dos analistas financeiros que conheço ainda não sabe como usar o Power Pivot, e muitos não sabem que ele existe.

Neste artigo, vou mostrar-lhes como usar o Power Pivot para superar problemas comuns do Excel e dar uma olhada em vantagens adicionais do software usando alguns exemplos. Este tutorial Power Pivot para Excel deve servir como um guia e, no final, explorarei alguns exemplos de casos de uso em que o Power Pivot pode ser inestimável.

O que é Power Pivot e porque ele é útil?

Power Pivot é uma característica do Microsoft Excel que foi introduzida como um complemento para o Excel 2010 e 2013 e agora é uma característica nativa para o Excel 2016. Como a Microsoft explica, o Power Pivot para Excel "permite que você importe milhões de linhas de dados de múltiplas fontes de dados em uma única pasta de trabalho do Excel, criar relacionamentos entre dados heterogêneos, criar colunas e medidas calculadas usando fórmulas, criar tabelas dinâmicas e gráficos dinâmicos e depois analisar os dados de forma que você possa tomar decisões de negócios oportunas sem requerer assistência de TI".

O idioma de expressão primário que a Microsoft usa no Power Pivot é DAX (Data Analysis Expressions), embora outros possam ser usados em situações específicas. Mais uma vez, como a Microsoft explica, "DAX é uma coleção de funções, operadores e constantes que podem ser usadas em uma fórmula, ou expressão, para calcular e retornar um ou mais valores. Dito de forma mais simples, o DAX ajuda você a criar novas informações a partir de dados já em seu modelo. "Felizmente, para aqueles que já estão familiarizados com o Excel, as fórmulas DAX serão familiares, uma vez que muitas das fórmulas têm uma sintaxe semelhante (por exemplo, SUM, AVERAGE, TRUNC) .

Para maior clareza, os principais benefícios do uso do Power Pivot vs. Excel básico podem ser resumidos a seguir:

  • Ele permite importar e manipular centenas de milhões de linhas de dados onde o Excel tem uma restrição difícil de pouco mais de um milhão de linhas.
  • Ele permite importar dados de várias fontes para uma única pasta de trabalho de origem sem ter que criar várias folhas de origem que sofram problemas de controle de versão e transferibilidade.
  • Ele permite que você manipule os dados importados, analise-o e tire conclusões sem diminuir o ritmo do seu computador.
  • Ele permite que você visualize os dados com PivotCharts e Power BI.


Nas seções a seguir, vou percorrer cada um dos itens acima e mostrar-lhes como o Power Pivot para Excel pode ser útil.

1) Importando grandes Datasets

Conforme discutido anteriormente, uma das principais limitações do Excel diz respeito ao trabalho com conjuntos de dados extremamente grandes. Felizmente para nós, o Excel agora pode carregar bem o limite de um milhão de linhas diretamente no Power Pivot.

Para demonstrar isso, gerei um conjunto de dados de amostra de dois anos de vendas para um varejista de produtos esportivos com nove categorias de produtos diferentes e quatro regiões. O conjunto de dados resultante é de dois milhões de linhas.

Usando a guia Dados na faixa de opções, criei uma nova consulta a partir do arquivo CSV. Essa funcionalidade costumava ser chamada de PowerQuery, mas a partir do Excel 2016, ela está integrada na guia Dados do Excel.

Exibição 1: Criando a Nova Query



De uma pasta de trabalho em branco no Excel para carregar todos os dois milhões de linhas no Power Pivot, demorou cerca de um minuto! Observe que eu consegui realizar alguma formatação leve de dados promovendo a primeira linha para se tornar o nome da coluna. Ao longo dos últimos anos, a funcionalidade Power Query melhorou grandemente de um suplemento do Excel para uma parte bem integrada da guia Dados na barra de ferramentas. Power Query pode girar, aplainar, limpar e moldar seus dados através de seu conjunto de opções e seu próprio idioma, M.

2) Importando Dados de Multiplas Fontes

Um dos principais benefícios do Power Pivot para Excel é a capacidade de importar facilmente dados de várias fontes. Anteriormente, muitos de nós criamos várias planilhas para nossas várias fontes de dados. Muitas vezes, esse processo envolveu escrever código VBA e copiar/colar dessas fontes diferentes. Felizmente, para nós, o Power Pivot permite que você importe dados de fontes de dados diferentes diretamente para o Excel sem ter que executar os problemas mencionados acima.

Usando a função Query na Exibição 1, podemos tirar de qualquer uma das seguintes fontes:

  • Microsoft Azure
  • SQL Server
  • Teradata
  • Facebook
  • Salesforce
  • JSON files
  • Excel workbooks
  • ...e muito mais 
Além disso, várias fontes de dados podem ser combinadas na função Query ou na janela Power Pivot para integrar dados. Por exemplo, você pode retirar dados de custo de produção de um workbook do Excel e resultados de vendas reais do servidor SQL através da Consulta no Power Pivot. A partir daí, você pode combinar os dois conjuntos de dados combinando os números de produção-lote para produzir margens brutas por unidade.

3) Trabalhando com grandes Datasets

Outra vantagem importante do Power Pivot para Excel é a capacidade de manipular e trabalhar com grandes conjuntos de dados para tirar conclusões e análises relevantes. Eu vou percorrer alguns exemplos comuns abaixo para lhe dar uma idéia do poder da ferramenta.

Medidas

Junkies do Excel, sem dúvida, concordam que as tabelas dinâmicas são uma das mais úteis e, ao mesmo tempo, uma das tarefas mais frustrantes que realizamos. Frustrante especialmente quando se trata de trabalhar com conjuntos de dados maiores. Felizmente, o Power Pivot para Excel nos permite criar, facilmente e rapidamente, tabelas dinâmicas ao trabalhar com conjuntos maiores de dados.

Na imagem abaixo (Exibição 2), observe como a janela do Power Pivot é separada em dois painéis. O painel superior possui os dados e o painel inferior abriga as medidas. Uma medida é um cálculo que é executado em todo o conjunto de dados. Eu entrei uma medida digitando a célula destacada.



Exibição 2: Criando Medidas



A partir daí, observe como é rápido criar uma tabela dinâmica familiar em um conjunto de dados grande.

Exibição 3: Criando uma PivotTable



Dimensão de Tabelas

Como analistas financeiros usando o Excel, nos tornamos adeptos do uso de fórmulas enroladas para dobrar a tecnologia à nossa vontade. Nós dominamos VLOOKUP, SUMIF, e até mesmo o temido INDEX(MATCH()). No entanto, usando o Power Pivot, podemos lançar muito disso na janela.

Exibição 4: Adicionando uma User-Created Table no modelo do Power Pivot



Para demonstrar essa funcionalidade, criei uma pequena tabela de referência na qual atribuí cada Categoria a um Tipo. Ao escolher "Adicionar ao modelo de dados", esta tabela é carregada no Power Pivot (Figura 4 acima).

Eu também criei uma tabela de datas para usar com nosso conjunto de dados. O Power Pivot para Excel facilita a criação rápida de uma tabela de datas para consolidar por meses, trimestres e dias da semana. O usuário também pode criar uma tabela de data mais personalizada para analisar por semanas, anos fiscais ou qualquer grupo específico da organização.

Exibição 5: Criando um Date Table



Colunas Calculadas

Além das medidas, existe outro tipo de cálculo: colunas calculadas. Os usuários do Excel estarão à vontade para escrever essas fórmulas, pois são muito semelhantes às fórmulas de escrita em tabelas de dados. Criei uma nova coluna calculada abaixo, que classifica a tabela de Dados de Contabilidade por Montante. As vendas abaixo de US$50 são rotuladas como "Pequenas", e todas as outras são rotuladas como "Grande". A fórmula não se parece intuitiva?

Exibição 6: Criando uma coluna calculada



Relacionamentos

Podemos então criar uma relação entre o campo Categoria da tabela de dados contábeis e o campo Categoria usando a visualização do diagrama. Além disso, podemos definir um relacionamento entre o campo Data de vendas da Tabela de dados contábeis e o campo Data da tabela do calendário.

Exibição 7: Definindo Relacionamentos



Agora, sem nenhuma função SUMIF ou VLOOKUP necessária, podemos criar uma tabela dinâmica que calcula as vendas totais por ano e tipo, com um "slicer" para o tamanho da transação.

Exibição 8: PivotTable Usando Relacionamentos



Ou, podemos criar um gráfico de vendas médias para cada dia da semana usando a nova tabela de calendário.

Exibição 9: PivotChart Usando Relacionamentos



Embora este gráfico pareça simples, é impressionante que demorou menos de dez segundos para criar uma consolidação de mais de dois milhões de linhas de dados, sem adicionar uma nova coluna aos dados de vendas.

Ao ser capaz de executar todos esses cenários de relatórios consolidados, sempre podemos seguir detalhando os itens de linha individuais. Nós mantemos nossos dados altamente granulares.

Funções Avançadas

Até agora, a maioria das análises que mostrei são cálculos relativamente diretos. Agora, eu quero demonstrar algumas das capacidades mais avançadas desta plataforma.

Inteligência de Tempo

Muitas vezes, quando examinamos os resultados financeiros, queremos compará-lo a um prazo comparável ao ano anterior. Power Pivot possui algumas funções de inteligência do tempo incorporado.

Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))
YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

Por exemplo, adicionar apenas duas medidas acima para a tabela de dados contábeis no Power Pivot me permite produzir a seguinte tabela dinâmica em poucos cliques.

Exibição 10: Time Intelligence PivotTable



Granulidades Desiguais

Como analista financeiro, um problema que tenho para resolver muitas vezes é a de granularidades incompatíveis. Em nosso exemplo, os dados de vendas reais é mostrada no nível de categoria, mas vamos preparar esse orçamento só no nível sazonal. Para promover essa incompatibilidade, vamos preparar orçamento trimestral, embora os dados de venda sejam diários.

Exibição 11: Granularidades Desiguais - Tabela de Orçamento



Com Power Pivot para Excel, esta inconsistência é facilmente resolvida. Ao criar duas tabelas de referência adicionais ou tabelas de dimensão na nomenclatura de banco de dados, agora podemos criar os relacionamentos apropriados para analisar nossas vendas reais contra os valores orçamentados.

Exibição 12: Granularidades Desiguais - Relacionamentos



No Excel, a seguinte tabela dinâmica se junta rapidamente.

Exibição 13: Granularidades Desiguais - Orçamento vs. Resultados Atuais



Além disso, podemos definir novas medidas que calculem a variação entre as vendas reais e as vendas orçamentadas conforme abaixo:

Actual-to-Budget Variance:=if(ISBLANK([Total Budgeted Sales]),BLANK(),[Total Sales]/[Total Budgeted Sales]-1)

Usando essa medida, podemos mostrar a variância em uma tabela dinâmica.

Exibição 14: Granularidades Desiguais - Variação de Resultados



Total percentual

Finalmente, vamos examinar as vendas em uma categoria específica como uma porcentagem de todas as vendas (por exemplo, contribuição da categoria para as vendas globais) e as vendas em uma categoria específica como uma porcentagem de todas as vendas do mesmo tipo (por exemplo, contribuição da categoria para o tipo de vendas sazonal). Eu criei as duas medidas abaixo:

Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

Essas medidas agora podem ser implantadas em uma nova tabela dinâmica:



Observe como os cálculos são realizados na categoria e no tipo sazonal. Eu amo o quão rápido e sem esforço esses cálculos são realizados em um conjunto de dados tão grande. Estes são apenas alguns exemplos da elegância e pura potência computacional do Power Pivot.

Compressão

Outro benefício é que o tamanho dos arquivos diminui. O tamanho do arquivo original era de 91MB, e agora ele está abaixo de 4MB. Essa é uma compressão de 96% do arquivo original.

Exibição 16: Tamanho dos arquivos



Como isso acontece? Power Pivot usa o mecanismo xVelocity para comprimir os dados. Em termos simples, os dados são armazenados em colunas em vez de linhas. Esse método de armazenamento permite que o computador comprima valores duplicados. No nosso conjunto de dados de exemplo, existem apenas quatro regiões que são repetidas em todos os dois milhões de linhas. Power Pivot para Excel pode armazenar mais eficientemente esses dados. O resultado é que, para dados que possuem muitos valores de repetição, custa muito menos para armazenar esses dados.

Uma coisa a ser observada é que usei valores de dólares inteiros neste conjunto de dados de amostra. Se eu incluísse dois pontos decimais para refletir centavos, o efeito de compressão diminui para 80%.

SSAS Tabular

Os modelos Power Pivot também podem ser escaláveis para toda a empresa. Digamos que você crie um modelo Power Pivot que começa a ganhar muitos usuários na organização, ou os dados crescem para dez milhões de linhas, ou ambos. Neste ponto, você pode não querer que trinta usuários diferentes atualizem o modelo ou façam mudanças. O modelo pode ser perfeitamente convertido em tabelas SSAS. Todas as tabelas e relacionamentos são mantidos, mas agora você pode controlar a freqüência de atualização, atribuir funções (por exemplo, somente leitura, ler e processar) para vários usuários e implantar apenas um pequeno front-end do Excel que se vincula ao modelo Tabular. O resultado é que seus usuários podem então acessar o modelo Tabular implantado com uma pasta de trabalho pequena, mas não ter acesso às fórmulas e medidas.

4) Data Visualization and Analysis

CUBE Formulas


Um dos constantes pedidos de meus clientes é que eu crie relatórios que estejam de acordo com um layout estritamente definido. Eu tenho clientes que solicitam larguras de colunas específicas, códigos de cores RGB e nomes e tamanhos de fontes pré-definidos. Considere o seguinte painel:



Como preenchemos os números de vendas sem gerar tabelas dinâmicas se todas as nossas vendas estiverem alojadas no Power Pivot for Excel? Usando fórmulas CUBE! Podemos escrever fórmulas de CUBE dentro de qualquer célula do Excel e isso irá realizar o cálculo usando o modelo Power Pivot que já construímos.

Por exemplo, a seguinte fórmula é digitada na célula em "2016 Total Sales:"



A primeira parte da fórmula, destacada em amarelo, refere-se ao nome do modelo Power Pivot. Em geral, é ThisWorkbookDataModel para versões mais recentes do Power Pivot para Excel. A porção em verde define que queremos usar a medida Vendas totais. A parte em azul instrui o Excel a filtrar apenas as linhas que possuem uma Data de Vendas com um ano igual a 2016.

Por trás das cenas, o Power Pivot construiu um cubo OLAP (Online Analytical Processing) com os dados, colunas calculadas e medidas. Este design permite que o usuário do Excel acesse os dados obtendo diretamente com as funções CUBE. Usando as fórmulas do CUBE, consegui construir declarações financeiras completas que estejam em conformidade com layouts predefinidos. Esta capacidade é um dos destaques do uso do Power Pivot para Excel para análise financeira.

Power BI

Outra vantagem do Power Pivot para o Excel é que você pode rapidamente tomar qualquer pasta de trabalho Power Pivot que você cria e convertê-la rapidamente em um modelo Power BI. Ao importar a pasta de trabalho do Excel diretamente no aplicativo Power BI Desktop ou no Power BI Online, você pode analisar, visualizar e compartilhar seus dados com qualquer pessoa em sua organização. Essencialmente, o Power BI é o Power Pivot, o PowerQuery e o SharePoint juntos. Abaixo, criei um painel de bordo ao importar o Power Pivot para Excel anterior para o aplicativo de desktop Power BI. Observe como a interface é interativa:

Exibição 18: Power BI



Uma ótima coisa sobre o Power BI é a Q&A da linguagem natural. Para demonstrar, eu carreguei o modelo Power BI na minha conta de Power BI online. No site, posso fazer perguntas e o Power BI constrói a análise apropriada à medida que escrevo:

Exibição 19: Linguagem Natural do Q&A



Esse tipo de capacidade de consulta permite que o usuário faça perguntas sobre o modelo de dados e interaja com os dados de uma maneira mais fácil do que no Excel.

Outro benefício do Power BI é que os desenvolvedores da Microsoft estão constantemente lançando atualizações para ele. Novos recursos, muitos solicitados pelo usuário, são lançados mensalmente. O melhor de tudo, é uma transição perfeita do Power Pivot para Excel. Então, o tempo que você investiu aprendendo as fórmulas DAX pode ser implantado no Power BI! Para o analista que precisa compartilhar sua análise para muitos usuários em dispositivos variados, o Power BI pode valer a pena explorar.

Melhores Práticas

Uma vez que você começa, há algumas boas práticas que você deve seguir.

O primeiro é decidir cuidadosamente o que importa em primeiro lugar. Você já usa o endereço residencial do vendedor? Preciso conhecer o endereço de e-mail do meu cliente no contexto desta pasta de trabalho? Se o objetivo é agregar os dados em um painel, alguns dos dados disponíveis não serão necessários para esses cálculos.

Outra prática recomendada é lembrar que Power Pivot não é o Excel. No Excel, estamos acostumados a criar cálculos expandindo constantemente nossas planilhas para a direita. Power Pivot para Excel processa os dados de forma mais eficiente se limitarmos esse desejo de destino manifesto. Em vez de criar continuamente colunas calculadas à direita de seus dados, aprenda a escrever medidas no painel inferior. Este hábito assegurará tamanhos menores de arquivos e cálculos mais rápidos.

Finalmente, gostaria de sugerir o uso de nomes simples em inglês para medidas. Passei os primeiros anos fazendo nomes como SumExpPctTotal, mas uma vez que outras pessoas começaram a usar os mesmos workbooks, eu tinha que dar muitas explicações. Agora, quando eu iniciar uma nova pasta de trabalho, uso nomes de medidas como "Linha de despesas como porcentagem de despesas totais".

Casos de Uso do Mundo Real

Neste artigo, eu mostrei apenas um punhado das maneiras pelas quais o Power Pivot para Excel permite que você dê um passo importante além do Excel simples. Eu pensei que seria útil realçar alguns casos de uso do mundo real em que Power Pivot para Excel é extremamente útil.

Analise do desempenho de um grande portfólio de ativos em diferentes intervalos de tempo: desde que o Power Pivot for Excel nos permite definir medidas que comparem um período de tempo com o anterior, podemos rapidamente ter um trimestre a trimestre, ano a ano, E o desempenho de um mês a outro de forma contínua, escrevendo apenas algumas medidas.

Resumir dados contábeis usando níveis de agregação personalizados: ao identificar cada item de linha de razão geral por nome, categoria e demonstração financeira, os relatórios podem ser criados rapidamente, que incluem os itens de linha apropriados.

As cadeias podem identificar vendas na mesma loja: usando uma tabela que mapeia quando as lojas estão online, os resultados financeiros podem ser comparados na mesma loja.

BY ELLEN SU - FINANCE EXPERT @ TOPTOTAL