It seems appropriate that I write about my love for Power BI shortly after Valentine’s Day in the US. It’s a tool that I reach for fairly regularly and I’ll explain why in this post. It’s also something that I wrote about previously in Getting started with Power BI Embedded.
No matter how much love I express in this post, it won’t come anywhere close to the impact of the love that Power BI got from Gartner last week. Microsoft landed in a very strong position in the Leaders section of the magic quadrant for analytics and BI platforms. The company was the strongest of any company in both the Ability to Execute and the Completeness of Vision dimensions of the quadrant. You can read more about this at in this blog post.
While Power BI is a Microsoft product, I use it pretty much like an external data product author would use it. That said, I will offer a couple of disclaimers before I proceed. I do work in the same division, the Business Application Group, as the Power BI team (but I don’t have any interaction with this team). While I’ve used visualizations in R and Excel, I don’t have experience with competitive products in the marketplace. Lastly, I am insulated from any cost impact so I can’t speak to the value equation.
What I can tell you is a little bit about how I use Power BI. I’ll frame that discussion with the three biggest factors that drive me to use it: data modeling, usability, and sharing and refreshing.
Data Modeling
Internally, I’m regularly looking at data related to our Dynamics 365 Finance and Operations product. There are a few different data sources that consistently are needed:
- Telemetry data – we use the Kusto platform for querying the huge volume of telemetry data that flows from our product installations. Kusto is another tool that I love, and it’s available as Azure Kusto and is in public preview. It is very easy to integrate Kusto queries into Power BI.
- SQL – we have a variety of data stored in SQL databases.
- Lifecycle Services (LCS) data – we frequently will pull information from our LCS databases for insights into the implementation lifecycle.
- Azure DevOps (formerly VSTS, formerly TFS) – we pull in information about our internal engineering execution from here.
- Excel on SharePoint sites – occasionally the master data for an activity sits in an Excel file on SharePoint.
- Excel on local PC – for one time analysis scenarios, a local file might be part of the equation.
A key strength of Power BI is the ability to bring in data from disparate sources and establish relationships. A challenge might be that you need to figure out a way to join the data from these sources, but fortunately this can be done in most cases for me with the data above. There are many built in data connectors in Power BI and establishing a relationship is as simple as drag and drop. You also have the DAX language available to manipulate data if needed.
Usability
Power BI makes it very easy to build reports that enable data exploring. The relationships between the data sources ‘light up’ in the reports. Probably the most common pattern I use for creating reports involves either a tree map or a bar chart on the upper half of the page and a table on the lower half. The details in the table can easily be filtered by selecting an area on the tree map or bar chart. It’s also very easy to add filters to slice the data in interesting ways for the user.
It would be great to share some visuals here, but that’s challenging given the internal nature of most of our reports. I’ll share some visuals in an upcoming post on the NCAA basketball tournament. And I’ll be able share more details on an externally facing report soon.
Sharing and Refreshing
Perhaps most important is the ease of sharing a report. This is where the cloud native approach for Power BI demonstrates its value. Reports are authored locally with the Power BI Desktop application and then published to a cloud workspace. That could be your personal workspace or it could be a shared workspace.
If it’s a personal workspace, you simply go to the report and share it with those you want to share it with. I will do this most frequently when doing exploratory analysis. Instead of data modeling in Power BI in this scenario, I will export data from one or more data sources, do some data munging and analysis in R, and then output the results in a CSV file. Instead of sending the CSV file to others in e-mail, I will do a quick Power BI report with that CSV file as the only input to highlight the insights from the analysis.
If building a data product, the publishing will typically go to a shared workspace. From there, I will create a Power BI App with multiple reports from the workspace. I can control who has access to the report as part of the App creation or update operation as well as provide a URL for the app.
Lastly, I can easily set up the report to refresh at a regular cadence in the workspace. In most cases, a daily update happens.
There are other things that I like about Power BI – embedding and R integration (future post) to name a couple. And there are a few things that I would like see improved, mostly around the application lifecycle for reports. But overall, it’s an easy to use and powerful tool that I’m glad is in my toolbox!
Picture details: 2/18/2019, Grass Lake, Canon PowerShot SD4000 IS, f5.6, 1/1000, ISO-1600, +1 step