I may blog about that solution in the future, but with the Future of SharePoint event rapidly coming up, my BI Focal fellow collaborator, Jason Himmelstein convinced me that there was something more interesting that we could do with this. How about near real time monitoring of Twitter conversations for the event? All of the pieces were in place.
We rolled up our sleeves, and in relatively short order, had a solution. Jason has written about the experience on his SharePoint Longhorn blog, and he has included the videos that we put together, so I can be a little less detailed in this post.
The good news is that Power Query in Power BI is flexible enough for us to take advantage of the Bing Maps API so that we can lookup the missing pieces of information we need. In this blog post, I’m going to show you how you can use the Bing Maps API to look up an address based on a latitude and longitude or use a street address to find a latitude and longitude for the location.
I love how map visualizations nowadays are so easy.
While it was understandable that initially Power BI wanted to focus on visualizations first and foremost, the display for pure tabular data in Power BI was lackluster. There was no control over any of the following:
Colors –of any element in a table, font, background color, title, totals. Nada
I am simultaneously impressed that the Power BI team can so consistently push out crazy numbers of updates and glad that I don’t personally need to keep up on all of the changes…
Apparently, in PBI the domain is used, not the email. Good to know if row level security is implemented in PBI someday (note: currently it’s only available in the Power BI Service).
OK, now let’s upload this report to the Power BI Service so we can implement the RLS. When the report is opened, it seems that my carefully crafted measures are ignored. Maybe Power BI ignores the USERNAME() function as long as there is no RLS defined.
I like the fact that you can test by role, making it a lot easier to see what others would see under those circumstances and finding bugs with security implementation. If you have any non-public information and you use Power BI, this is a must-read.
But what about if you want the chart title to change depending on what is selected? For example, you might be using slicers or filters to allow a user to choose which days of the week they want to see data for. In that situation you might want to add a title that shows which days of the week have actually been selected; this would be particularly important if the report uses filters, or if the report is going to be printed. Unfortunately the built in Title Text property can’t be used to display dynamic values but in this blog post I’ll show you how to solve this problem using DAX.
The solution isn’t trivial, but it is cool.
And which colors do we love to use with KPIs? Red and green, of course! Color is a very powerful tool in data viz. We use it to indicate meaning and to draw attention to something important. KPI boxes are used to display key metrics in an efficient manner. These key metrics are usually rather important, and our users need to be able to see their status at a glance.
I quite like the design of the KPI boxes in Power BI, but for some reason they were created without the ability to adjust the color associated with the state (good/bad). Shown below, they use the common red/green color scheme.
It sounds like Microsoft is already working on fixing the issue.
As I’m currently planning to migrate the entire BI architecture of one of my customers to the cloud, this made me think: can we ditch SSAS as we know it already in favor of Power BI? What are the alternatives?
To study that, I’ve put some diagrams together to show the possibilities of moving BI to the cloud. First, I’ll discuss the possible architectures, then the impossible architecture (but maybe the situation I was looking for).
One man’s opinion: there will be SSAS for Azure. I have no proof of this, and the nice part about having no proof is that I can throw out wild speculation without fear of violating NDA…. But to me, Power BI solves a different problem and acts in conjunction with SSAS rather than as its replacement. I also don’t see any technical reasons why SSAS couldn’t live in the cloud, and so that leads me to believe that it will be there eventually. But hey, it turns out wild speculation is occasionally wrong…
Fortunately Power Query still is available with Live Connection. This gives you ability to join tables, flatten them if you require, apply data transformation and prepare the data as you want. Power Query can also set the data types in a way that be more familiar for the Power BI model to understand. If you want to learn more about Power Query read Power Query sections of Power BI online book.
Reza shows some techniques and also the negative repercussions to using Live Connection. This is a good read if you’re getting into Power BI.
So Pivot is easy and simple to do, but you have to be careful about the nature and quality of source data set. If it is normal to have a name repeated in the source data, then an aggregation needs to be set properly. if you expect each name to appear once, then setting it as Do Not Aggregate works better because you can use error handling mechanism in Power Query to handle error somehow.
This is a good sight easier than writing a bunch of SUM(CASE) statements or using the PIVOT operator in T-SQL.
In M, the DateTime.LocalNow() function returns the current system date and time at the time the function is called – which means that you could call it multiple times in the same query and get different results. There’s also the DateTime.FixedLocalNow() function which also returns the system date and time; the difference between the two is, as the documentation says:
This value is fixed and will not change with successive calls, unlike DateTime.LocalNow, which may return different values over the course of execution of an expression.
This is good to know for those query-tuning scenarios.