Today's blog post was written by Keith Mescha, a Principal Architect at Sonoma Partners.
There's been a bit of buzz recently in the CRM circles around Power BI and how it fits into the overall CRM landscape. There is certainly no shortage of blogs, podcasts and event demos showcasing the power of this toolset. On a personal note, I'm am seeing the same level of interest based on the meetings on my calendar over the past few weeks.
What we're finding at Sonoma is that the topic of Power BI is still very confusing for most customers. In this multi-part series we hope to help demystify some of the confusion through real-life examples and suggest some best practices based on learnings from our internal use as well as implementations with our clients.
Power BI is Microsoft's analytics and dashboard Business Intelligence (BI) suite of tools and solutions. It's made up of a few main offerings:
- Power BI Desktop – Free development tool for building data models and dashboards
- Power BI Service – Online PaaS offering to host and share Power BI solutions with a monthly licensing per user subscription based model
- Power BI Embedded – Online PaaS offering for embedding Power BI solutions into applications for external consumption with a session based subscription model
- Power BI Mobile – Free mobile app for consumption of deployed Power BI solutions on a mobile device
- Power BI Enterprise Gateway – On Premise software used to extend on premise data to the Power BI Service
For latest and greatest features and release notes, refer to the Power BI site.
Every company I know has data quality issues or challenges to overcome. Unfortunately, we here at Sonoma are not exempt. One of the things we constantly struggle with is incomplete data and stale data. Often times, we will create a record without knowing all of the various data points we typically want to capture. If we do not go back and fill that information in once it becomes known, we are left with an incomplete and potentially inaccurate record. The genesis of the original report request was to provide a means for the data steward team to monitor newly created records.
The original ask was for a custom SSRS report that listed out all Accounts that were missing specific data across a pre-determined list of fields. This report would then be provided to our data steward team on a regular basis to ensure the data is augmented appropriately. When one of my colleagues stopped by to chat about the report, I happened to be in the middle of building a dashboard in Power BI for a customer. He was curious what I was working on, and as we talked and he explained his need, I fired up a new instance of Power BI desktop in an attempt to solve the problem.
Get Your Data
I pulled up the 'Filtered Account' view from the 'Get Data' option in Power BI. My User Account is a System Admin and the Filtered Account view resolves all the option set and lookup fields for us, so this was a quick and easy solution. When getting data for many of the source types, you have an option to Import or Direct connect. I’ll go deeper on that topic in my next post but for this solution I chose to import.
Shape Your Data
Initially Power BI pulls in all the attributes in the table, this can be a bit overwhelming so filtering down the attributes was our next step. My colleague had a list off the fields he wanted to audit and report, so I filtered down the data set to only include those attributes.
Within 15 minutes my colleague and I had a prototype dashboard built with the fields he needed, including a couple charts to use as filters. From there we iterated on a few designs, and ultimately landed on the below look and feel. End to end this entire process took a couple hours to build, including some custom columns to allow linking to our Account Forms on Dynamics and Salesforce.
Deploy Your Solution
Once we had a solid solution built in Power BI Desktop, I created a new O365 group for internal reporting, added a few users to the group and then deployed the Power BI solution to the Power BI service.
One key thing to note here is that we had to assign Power BI licenses to these users before they could login and see our dashboard. The fact that we wanted to schedule this a couple times per day and that we were managing this through our 0365 AD bumped us from the free to the paid license. We had a handful of users requiring access and plenty of licenses available so this was a decent option for us. If you have a different need there are other options, we can consider for staying on the free option.
Manage Your Solution
From there my colleague could interact with the report by logging into PowerBI.com.
After my colleague was happy with the presentation of the data and a few more cleanup steps, I was able to setup the data refresh of the data set in Power BI. We already had a Power BI Enterprise Gateway installed on our SQL Server for another solution, so including my data set in the refresh was very simple with just a few clicks.
Now that our solution was deployed, our data stewards have logged in and subscribed to the dashboards. Every morning they get an email with a snapshot of the dashboard and a link to access the dashboard where they get direct access to the records in Dynamics or Salesforce so they can easily update those with missing data.
In summary, the Power BI platform is a great set of tools that are easy to learn. There is quite a bit of information available through various internet communities, so getting started is easy. As shown here, from idea to solution was less than one day for a very simple deliverable. The toolset can extend into much larger solutions as needed, so please give us a call and let us help you take the next step in your analytics journey within your CRM applications.
In our next Power BI post, we will discuss in more detail the step of getting your data and how to decide on Data Import Vs Direct Query.