Transcript Twitter, Cloud and Power BI walked into a bar. The bartender looks

Twitter, Cloud and Power BI walked
into a bar. The bartender looks up
and says “I don’t have time for your
Dashboard.”
Ian Pike
About me
• Current employer Swinton Group
• Working for them for 12 years
• Started as .Net/SQL Developer
• Team Leader, back to SQL and Data Warehouse Developer
• Current role Database Specialist Technical Lead (1 year +)
Continued…
• 1st user group talk
• Been involved in the user community over the last 4-5 years
• Took over as event host at Swinton about 12 months ago
• More recently the running of Manchester User event with a group of
people
• Helper at SQL Bits and SQL Saturday events
Background to talk
• It started with a tweet
• A journey of learning new technologies and creating a business case
to use them
• Community help
• Andrew Fryer & Amy Nicholson – 1 day PoC @ Swinton
• ½ day to build and prove end to end
• Further days to polish the Power BI report
Tools
• Power Bi
• Desktop
• Azure
• Visual Studio 2015
• Twitter API
• Sentiment analysis APIs
Demos
Summary
• Twitter App and API
• Power BI
• Sentiment with Odata Feed
• Create connection function for Database/Server
• Overview of September Release features
• Preview Features
• ArcGIS Map
• Forecasting
• Powerpoint Export
• Calendar in DIM table
Twitter
• Set-up a test account
• API
• https://dev.twitter.com/oauth/overview/application-owner-access-tokens
• Fields
• Examples: Date/time, Tweet Text, CreatedBy, Favourite Count, Retweet Count
• https://dev.twitter.com/overview/api/tweets
• 500 tweets at a time
• Full access/Real-time streaming
• https://www.gnip.com/
• https://dev.twitter.com/streaming/overview
Application
• Azure VM
• DS2_V2, 2 CPU, 7GB (£63.63 pm as at 16/10/2016)
• A weeks’ worth of usage about £10
• VS 2015
• Simple C# console
• Twitter Tokens
• Options to write to Azure Blob storage
Power BI
• Bring your data to life
• Power BI transforms your company's data into rich visuals for you to
collect and organize so you can focus on what matters to you.
• Different products:
•
•
•
•
Service
Desktop
Mobile
Embedded
Power BI
• Learn, build, throw away and start again
• Low initial cost for reports
• Tenant Admin if you want to publish, audit etc
• Pros
• Monthly releases
• Good community support
• Cons
• Hard to debug complex queries
Power BI
• Automatically joined queries/tables together with same names
• Don’t go crazy and put everything on one page, less can be more
• Use Functions for connections for queries, makes easy to switch out
• Think about how you publish and who can see it
• How you track your data, is it sensitive?
Power BI - Pricing
• https://powerbi.microsoft.com/en-us/pricing/
• Power Bi Free
• 1GB per user
• Refresh Daily
• Consume streaming data 10K rows/hour
• Power Bi Pro
•
•
•
•
•
10GB per user
Refresh Hourly
Consume streaming data 1Million rows/hour
Consume Live+ and on premise data sources (gateways)
Collaboration (O365, content packs, AD sharing, Data Catalog, RLS)
Sentiment Analysis
• Costs lot inside Power BI
•
•
•
•
•
•
Azure Marketplace
Sentiment Analysis API
Cost free for 10K, then upwards from $300 per 100K transactions
https://datamarket.azure.com/dataset/amla/text-analytics
https://www.microsoft.com/cognitive-services/en-us/subscriptions
Better to store in database once than run every time
• Can be done with Twitter for nothing
Data Preparation
• Store in database
• Use SSDT and SSIS for back-end
Lessons learnt
• Azure and Power Bi are moving fast
• Monthly builds and changes
• Build azure VMs
• ARM Templates
• Storage account for downloads
• Infrastructure as a service (next steps)
• Azure spend cap
• This is just the start…
Next steps
• Spin off Power Bi Dashboards already built
• LanDesk
• DBA On-Call
• Sentiment
• Twitter api for sentiment vs Azure Marketplace
• Real time – Azure stream analytics with Event hubs
• SSIS Sentiment storage
• Azure Task Factory
• Data Lake
• Power Bi
• Build dashboard again!
• Machine Learning
#SQLFamily
• Speak to people, learn and get in evolved in the community.
• SQL Relay/SQL Bits/SQL Saturday/This event and other user groups
• Hash tag #SQLHelp
• Slack channel : sqlcommunity.slack.com
Any Questions?
Contact details
• Twitter : @ianpike
• Slack : @ianpike
• Contact for questions and/or Source code and slides
Thank you for listening
Resources
Edx.org courses : www.edx.org
Real Time Sentiment analysis:
• https://azure.microsoft.com/en-gb/documentation/articles/stream-analytics-twitter-sentiment-analysis-trends/
Power BI Sentiment
• http://www.sqlservercentral.com/blogs/sqldownsouth/2015/12/02/sentiment-analysis-with-power-bi/
• https://community.powerbi.com/t5/Community-Blog/Sentiment-Analysis-in-Power-BI/ba-p/55898
Tips for Bi
• http://exceleratorbi.com.au/top-10-tips-getting-started-power-bi/
• Guy In a cube - https://guyinacube.com/ or YouTube Channel!
Power Bi Template Solutions: https://powerbi.microsoft.com/en-us/solution-templates/
• https://powerbi.microsoft.com/en-us/solution-templates/brand-management-twitter/
• https://powerbi.microsoft.com/en-us/blog/the-alteryx-starter-kit-for-microsoft/
• https://powerbi.microsoft.com/en-us/blog/using-power-bi-real-time-dashboards-to-display-iot-sensor-data-a-step-by-steptutorial/