Sentiment Analysis

Dustin Ryan and Patrick Leblanc used Azure ML and Power BI to do sentiment analysis:

Using Azure ML and a free subscription to the Text Analytics API, I’m going to show you how to perform sentiment analysis and key phrase extraction on tweets with the hashtag #Colts (after this past Sunday’s 51-16 beat down of the Colts at the hands of the Jacksonville Jaguars, I’m bathing in the tears of Colts fans. Watch the highlights! ). Although my example here is somewhat humorous, the steps can be used to perform sentiment analysis and key phrase extraction on any text data as long as you can get the data into Power Query.

This is a fantastic example of how Azure ML can be used.  Read the whole thing.

Power BI Analysis Of Quickbooks Data

Rob Collie shows how to use QQube to help with Quickbooks data analysis:

Our financials are the logical first place to start.  And our financials are in the hands of our accounting firm.  Specifically, they are stored in Quickbooks.

This, of course, poses a problem.  Because like ALL accounting and ERP systems, Quickbooks is primarily focused on being a great accounting system.  A system that collects, stores, organizes, and routes data.  Quickbooks is NOT an analytics tool.

And being an analytics (or BI or reporting, whatever you call it) tool is a full-time job.  ANY system whose job it is to collect/organize/route data will NEVER be sufficient for reporting and analysis.  NEVER.  I’m not kidding.  We should never expect different, and that’s not a “knock” on these vendors.  It’s just too many missions for any one company to execute.

This is a nice walkthrough of how you can apply visualization and analytics concepts, especially in a small business scenario.

Visualizing SQL Saturday Data

Tamera Clark analyzes SQL Saturday Nashville data:

Select the funnel from the visualizations (1), select track in the field list (2) and drag track to the values box (3). (Image 5 below) Now we need to customize this visualization.  Select the paint brush to edit. (Image 6 below) I recommend giving each of the tracks a different color. Since Tracks are determined by the organizer the data maybe similar so you might want to use the same colors for more than one data point. You should also update the title Count of Tracks by Track sounds silly. Now we have a lovely display of session distribution by track.

She came up with a nice-looking set of information describing sessions and presenters for SQL Saturday Nashville 2016.  I love seeing this kind of thing and hope it becomes mainstream among SQL Saturday organizers (maybe to the point where some of this is built into the SQL Saturday website).

Power Query And Power BI Compression

Chris Webb shows us how to work with gzip files using PowerQuery:

For example, given a gzip file that contains a single csv file, here’s an example M query showing how the Binary.Decompress() function can be used to extract the csv file from the gzip file and then treat the contents of the csv file as a table:

He goes on ot show how Binary.Decompress is used to read Excel XLSX files.

Fitbit BI

Reza Rad has a three-part series on applying BI tools (specifically, Power BI) to Fitbit.

Part 1:

So for this post we are going to build that dashboard (not all of that obviously, because we don’t have the data required for all of that), but most part of it with Power BI. You will see how easy and powerful is Power BI in this kind of scenarios, and you will see how you can be the BI Developer of Fitbit in a few steps of building this demo.

Part 2:

Unfortunately Power Query or let’s say Power BI doesn’t have a loop structure, and that is because of the functional structure of this language. However there are data structures such as Table and List that can be easily used with each singleton function to work exactly as a loop structure does. Here in this post I will get you through the process of looping into files in a directory and processing them all, and finally combining them into a large big table. You will also learn some Power Query M functions through this process.

Part 3:

Fitbit calculates based on my current weight and age (I assume) how much calories I have to spend each day. I don’t know that calculation, So I create a static measure with the value of 2989 for the amount of calories I have to spend each day. I also create StepsCap measure with 12000 value showing that I have to walk 12000 steps a day, and another one for FloorCap with the value of 10. I created a Calories HighEnd measure with 5000 calories as value (I will die if I burn more than that!). You can create all these measures easily in Data tab.

This is a nice combination of work and play, building an interesting system with a data set interesting to the author and freely available.

Parameter Tables In Power BI

Chris Webb on using parameter tables in Power BI:

And there you have it: a parameter table in To be honest, I think there are slightly too many fiddly steps for users to follow in this technique for me to be happy recommending its use unconditionally, but it should be useful in some scenarios. Hopefully there will be an easier way of accomplishing the same thing in Power BI in future…

Sounds like it’s not as easy to do as in Power Query, but Chris does provide nice step-by-step instructions.

3D Maps In Power BI

Reza Rad gives us an introduction to 3D visualization using Power BI Desktop:

Power BI team did a great step forward with adding 3D map visual in Power BI Desktop. Thank you Microsoft Power BI team because of that! It is really useful for some scenarios that users need to see visualization on 3D map. However this feature is far behind Power Map features for story telling, creating tours, play axis, and many other features. I believe that soon many of these features will be added into Power BI. So I say Power BI said hi to 3D maps, but please be quick on that Microsoft team because Power Map raised expectations of our clients to a very high level! They will be looking for same features (at least) in Power BI desktop.

Looks like there’s still some work yet to be done.

More Power BI Updates

Meagan Longoria is keeping a list of all the things changing in Power BI.  It’s a long one:

Since it’s part of my job to stay on top of the latest Power BI features and capabilities (and because I like lists), I decided to keep a list of the features released for easy reference. It’s much more convenient for me to have a single place to reference when I’m discussing specific capabilities and updates with clients and colleagues. I’m now sharing my list with you.

I’m glad that somebody’s keeping up with everything changing in Power BI.  I’m equally glad that person isn’t me.

SSRS All Grown Up

Paul Turley takes a look at how SSRS and Power BI are maturing.  One of the key grafs for me:

In SQL Server 2016, Reporting Services is getting a significant face lift on several fronts.  The HTML renderer has been completely rewritten to emit pure HTML 5 to produce consistent output in every modern browser on every device.  This capability is in the current CTP today.

I hated having people install executables to view SSRS reports, hated how Firefox and Chrome displayed reports differently than IE, and hated the occasional insoluable error brought about by these two things.  SSRS was due for a modernization, and I hope to look at it again in 2016.  Between these two tools, R support, and PolyBase, SQL Server 2016 is really shaping up to be a huge release for BI teams.


April 2017
« Mar