User Review
( votes)Power BI with Microsoft Dynamics 365 Customer Engagement can empower organizations by turning data into knowledge with easy to implement visualizations.
RSM has provided implementations for Microsoft Dynamics for over 30 years with over 2,000 clients across the country. In this recorded webinar Brian Connelly and Faisal Muhammed take a deep dive into Power BI with Dynamics 365 Customer Engagement.
Power BI with Dynamics 365 CE
Video Transcription:
Hello and welcome to today’s RSM webcast – Power BI with Dynamics 365 CE: Empowering organizations by turning data into knowledge with easy to implement visualizations. Our presenters for today are Brian Connelly, Senior Associate with our Business intelligence, and Faisal Muhammed.
Faisal: Thank you and hello everyone. Welcome to this webcast. My name is Faisal Muhammed, and I’m a manager in our business intelligence group out in the Boston office. I’ve been with the firm for 11 years now. I would now turn to Brian for him to introduce.
Brian: Hello, everyone, I am Brian Connelly. I’m a senior associate at our Blue Bell office. I have about eight-plus years in Business Intelligence, and I’ve been with the firm for about two years.
Faisal: Thank you, Brian. So here is the agenda for our webcast today. Before we talk about Power BI and Dynamics 365, I’d like to give you an overview of our Business Intelligence services. There are five key services that we offer. The first and most important one is Strategy. Strategy focuses on understanding the businesses, processes, business objectives, and developing a road map to meet those business objectives. The key here is to align data and analytics with the overall organizational strategy.
The next service is Data Foundation, which focuses on the integration of data from multiple disparate data sources into a trusted and understandable form. For example, if you have your data residing in the ERP systems, CRM systems, and/or other systems, data foundation integrates data from all disparate data sources into a repository to provide a unified experience. This repository works as a foundation for all of the reporting and analytic needs of the business.
The next service is Reporting and Analytics. It focuses on providing the finalization of data into usable information and knowledge. There are four basic types of analytics that we provide here at RSM. First is Descriptive Analytics, which focuses insight on what happened into the past. Diagnostic Analytics focuses on why something happened or why something did not happen. For example, why did we miss our target this year? Predictive Analytics focuses on understanding the future. Predictive Analytics provides advice on possible outcomes, and this is an example – if we think we are going to miss our target this year, these analytics will provide suggestions on what can be done to still hit the targets. The next one is Information and Delivery, which focuses on how end-users consume the data or consume the reports and analytics. It’s a collaborative process that helps users navigate several delivery options and help them select the right interface at which they interact with the data. This interface could be a portal, either in the cloud or on-premises. It could be a mobile device, or report and analytics could also be embedded on applications that users utilize on a day to day basis and applications that they are familiar with. Finally, Support and Maintenance focuses on supporting and maintaining customers’ BI infrastructure to ensure business continuity. It provides continuous training to customers and makes sure that their solutions are up to date and will benefit from product upgrade and so on. So that was a quick overview of our BI services here at RSM. Feel free to reach out to us if you have any questions. And with that, I am going to turn over to Brian Connelly.
Brian: So for the remainder of this presentation today, we’ll focus on the introduction to Power BI. We’ll talk about Power BI with Dynamics 365 and we’ll provide a demo.
So let’s start with Power BI. It has several layers, and the first layer is the Power BI Desktop. For most, it is considered a free application and allows users to do analytics and connect to data sources. Then there’s the Power BI service and it is a software service application that allows end-users to interact with organizational data. So really what we’re talking about is within the desktop we’re preparing the information they need to be presented or exploring or analyzing our information and then we’re building reports and sharing with them and collaborating with our colleagues.
The three elements. There’s the Dataset, and so particularly with CRM, the question is what data is coming out of CRM that will be used to build the reports? Then we have the Power BI report, which consists of one or more visuals, and it could also consist of one or more pages. It can combine data sets from different sources. We can also pin to the same reports to multiple dashboards for the Power BI service. And then most importantly, it allows for the users to drill through and interact with the dashboard not only from our information at a glance but also those additional insights that standalone reports don’t actually deliver. Then there’s the Dashboard, which consists of one or more report visuals and it could also include an entire report at the dashboard. They are listed as your own, or it might not have been created by you, so this is the part around collaboration, wherein one person can create the dashboard within the organization or someone can create their own dashboard for the Power BI. Again dashboards allow for interactivity, but you’re not able to drill through, and so normally within the Power BI we use dashboards and reports for different purposes depending on the business requirements and what needs to happen.
So let’s talk about the data sources. As I have mentioned, when developing the different visuals within the report element, we can connect Power BI to multiple data sources. So we can connect Power BI to file sources and this could be your standard Excel report data, it could be a text or CSV as well as more technical XML. We can connect to multiple types of database, SQL database, MYSQL database, etc. Of course, there’s the cloud aspect so we can connect Power BI to hazard databases as well as hazard warehouses and analysis services, etc. Power BI can connect to online services. With Dynamics 365 there is a direct connector from Power BI to CRM online. Dynamics you can connect to Google Analytics and such, and there are plenty of connectors to connect to your OBDC connectors and such. This is important because there are over a hundred different types of connectors and so when you think about your organization data you may have to share on data, and you have some other source of information that you want to merge within your Analytics goal. This is really important to identify what sources with the Power BI are enabling connectivity to.
So let’s talk about Power BI with Dynamics 365 CE. So as mentioned the Power BI connector for CRM. Every CRM platform there’s the developer references in the settings to provide the web API. Once Power BI connects to the CRM 365 connector, it is as easy as placing the URL into the connector and then see the list of the tables within your environment. This includes the out of the box CRM custom entities as well as the custom entities. So whatever exists between your CRM environment would be available to the web API such as opportunities, leads, and accounts. Now when you connect to the web API, you don’t need to bring every element or every cable from CRM into Power BI. So the best practice is really choosing which cables are you going to develop your reports on and try to minimize the cable and the data on your file.
Power BI allows for quick insights. So this is really so that everyone will know the passwords. So AI or artificial intelligence. So the ability for Power BI to analyze you on data is really AI-assisted right so we’re talking about interactive data based on authorizations and we’re talking about using the cross-data visuals to take our data, aggregate it and find particular insights fast. What’s really interesting is that the AI-assisted visualizations, Power BI kind of maps your data and enables you to look for additional insights that exist within your underlying data. Visualizations within themselves can include a large spectrum of factors such as comparison, change over time, your flow and ranking, etc. It really enables rapid insights within the data set.
One really fascinating feature within Power BI we’ll see is that you’re able to ask questions about your data. So discovery of who is our most profitable customers or discovery of how many new leads were generated and by who were they generated. Which products should be inventory? So depending on your organization, and thinking about the questions you would ask yourself, you can barely type in a question using natural language and Power BI is going to return results for you. Some key considerations when you think about the Q and A, which uses common phrases with the natural language. That means, if your data set is a lot more technical and scheme-structured, then you might want to think about providing aliases for particular fields to put them into more commonly used terminology. Their Q&A is also going to provide an answer that it considers best. So when thinking about Power BI reports you have multiple data sets – that the questions you ask can be asked from both data sets. It’s going to view both data sets and return whatever the best results are. So you may ask what does that mean? So think about it this way: if you have one data set and it had a set of your customers and then you had another data set and had a different subset of customers, or you ask ”who are our top customers based on sales volume?”. So sales volume and customers on both data sets is going to answer that question based on the best possible answer between both data sets.
Power BI and drill-through on your CRM Visual. This is a really special feature within Power BI. You create the particular drill-through reports that allow the user to drill in through different subsets of data that it’s aggregating what the result says. To put that into context, think about this visual: you have a column chart and it is showing the count of open opportunities over different quarters of the year. So, what are those opportunities? This is what the drill-through features, and it is really helpful in understanding the data by interacting with that particular column/visual that you are interested in. You can drill in and actually see the details. It is the ascertaining of additional details that really drive insights. Another helpful example is that you have a call center and there was an additional increase in the average speed to answering a phone call. Using Power BI and depending on your data structure and the data set, you can drill into that particular detail and find out or peek into our average speed to answer, we can see that we stocked well and the call volume was high, and the result was an increase into our average speed to answer.
Going back as I’ve mentioned with the data visualization tab in AI assistance, which was kind of built into them, is analyzing your CRM data. Your Power BI and CRM, deliver additional insights based on the visual, so what is driving the increase or decrease for the measure? So if you think about the visual where you see maybe growth, right? So we’re looking at sales growth or some kind of aggregate overtime, by clicking and saying ”analyze this for you”, Power BI using machine-learning algorithms, we’ll be able to provide you that this increase is being driven by these users. Or this increase is being driven by this product line. So that’s a really important feature within Power BI and how it’s going to continue to drive additional insights as long as you interact with the report. There are also considerations particularly with the analyzer and CRM, where one of the key points is that it’s only going to be based on the previous value. So when you think about your visual and say analyze this, it’s going to look at the consideration based on each previous period right?
Another key feature in Power BI that we see most organizations are loving and using is the Analyze in Excel. So often we have the Power BI and dataset built inside the report, we have the data visualized. But we do have users within organizations that want to work themselves for the data, so within Power BI and once this is deployed, there’s a feature called ”Analyze in Excel”, which connects Excel to the Power BI dataset and it allows users to use what looks familiar to a pivot table. So users are able to drag and drop the dimensions that they want to look at and create their own charts and analyze the data differently than what’s being presented. One key piece of information here to is depending on how Power BI is structured when Power BI is deployed, and you set up the data set that we want scheduled, once you analyze an Excel and download it, you are able to save that particular file as long as you have permission to view that report, allows the users to save the Excel file and reopen and refresh it, and the information within that Excel file will be as requested as the data set that the Power BI dashboard is built on. We are allowing end-users to not just visualize it within the dashboard but they would go in and have additional capabilities for analyzing what the changes are and the like. One other thing to consider is that this does work with SQL server services, and another key point here is that the data within the Excel file when you analyze an Excel is only as fresh as the dataset that is being provided to it.
So some standard features that most organizations do like is that Power BI has the ability to email the report view, which is important for one of those cases like ”I don’t wanna connect my phone to Power BI at this point. I just want to come into the office on a Monday morning and see the report within my email.” So the subscription to emails is really valuable. The email itself could only be sent daily or weekly or any other frequency. Though you have a Power BI report looking at your CRM data, and you’d want that report to come out daily, it’s only gonna send it once. In some considerations wherein the report is including low-level security, this will restrict the ability to email the report. The emails are sent just when the underlying data set has been updated or refreshed. Once the data set for that report has been refreshed, that’s the trigger for the email to be sent.
Another feature when you think of your CRM data and how Power BI plays is the ”Power BI and Mobile View”. With the mobile app, you are able to connect your phone into Power BI, you can see whatever dashboard that has been created presenting your information. One additional feature here is that you can take the dashboard or report that has been created for wide consumption within the browser and then you can create the view for mobile. From a mobile view, the things that you would consider is we do see many visuals. So you’d want to keep the key visuals and then remove anything that is not valuable. The mobile view also allows you to rearrange the information at random, offering flexibility. The key point here is that when you are creating the mobile view, you can optimize it by having multiple pages and or just a single view. Users can also go to their powerful dashboard to see the different levels of context that support whatever the dashboard is about.
Let’s explore what this looks like from the user. So as we covered the drill-through, the ability to analyze, we talked about interactivity, this particular view that I’m going to share to you is on a browser so this is not a mobile view. The first part, I talked about interacting with our data, taking the static view of the dashboard and looking at what is happening with those numbers. In the table above, here we’re looking at just opportunities, a total of 87. It’s broken down to several factors: open, won, etc. Then we look at account ratio, account won ratio and account lost ratio. Account ratio is the number of opportunities that an account would have, what is the ratio of won opportunity for an account, and also the lost ratio with the same logic.
So we’re interacting with CRM data. If I hover over a column, it will show me a tool kit that shows me the estimated close and the number of opportunities. This tool can be customized. When I click on Open Opportunities, it updates the Open Opportunities by Closed Probability. One of the opportunities has 80% closed probability, while 3 have 60% and one has 40%. We have the total five counts as well as the close probabilities. We can also look at the Total Amount by Opportunity at the right portion. When we click on won opportunities by account, you can see that there are six opportunities that are won and you can see how it interacts.
If we look at the open opportunities by Sales Stage, you can see that for these two open opportunities that the open opportunity value is at $2.79M. Just by clicking throughout your dashboard, you can interact with the dashboard and gain additional insights.
The drill-through can be a visual format like a table or additional insights, like a pie graph or bar graph. The details are as customizable as the report itself. The idea here is to give additional context to whatever you have seen on the front layer. So if you were looking at leads or any kind of entity within your organization regarding your business process, this is going to aggregate it and give you the additional detail. For open opportunities, I can look at them by region, by national account, by sales rep, etc.
One other piece is the ‘Analyze’. This is the AI being built on top of what the reports are showing. You can have insights based on Step Name, Account IDs, Postal Code, Owner ID, Sales Page, etc. All of the insights being provided comes from your dataset.
The email subscription is where you use your active directory. You can add anyone who is in your organization. They will receive a report embedded in their email upon receipt. The manage subscription option allows you to easily find which subscription you need to update.
If you have five or twenty reports, there might be a need to sort them by need or importance, and you can do this by pinning the important ones and then create new dashboards for them. Both the reports and the dashboards can be shared within the organization. Your colleagues need to have access to the original report that you are sharing as well so they can view it. There is a level of detail when it comes to permissions.
You may want to see who’s using the insights from CRM. So you build a CRM dashboard, you want to see how well it’s being leveraged, and for some of those things, you can look at the Usage Metrics. You can see data such as the views per day, unique views per day, and views by user.
The home page is as shown below:
At this point, we covered a lot of material over what Power BI can do for your CRM data. The key takeaway is (1) There is a direct data connector from Power BI to CRM online. (2) There are 3 elements: the dataset (the data connector connecting to CRM, the tables that you want to bring into your dataset), the report (the visualizations built on top of the dataset), and dashboards (the additional capability within the Power BI service that allows you to pick or create your own dashboard based on the reports).
Q & A:
1) Is Power BI able to connect to CRM on-premises?
Yes, and to be able to do that, you use the Out of the Box SQL server connection on a desktop. So when you open your Power BI on a desktop, when you click on good data, you will click out all of the Out of the Box connectors and one of the connectors is SQL server. So select that and you specify the name of your CRM server and there you go. As long as you have access to the database, you will be able to connect to the connection that I have just mentioned.
2) Is Power BI free with CRM?
It depends on the types of customers. The retail of Power BI probably has two different types of versions – Power BI free and Power BI Pro.
Power BI free is pretty much free for personal use. For example, if I want to connect to CRM, my ERP systems, and some other systems to create my visuals, dashboards and publish them and use it on my own without sharing to my colleagues, then up to that point it’s free.
As soon as I want to share my content with my colleagues, that’s where we need the Power BI Pro, which is $999.99 per user per month.
3) How does CRM join other data sources?
Power BI is capable of connecting to Dynamic CRM, as well as other data sources such as Dynamics ERP, AX, and GP. It can connect to Dynamic CRM either on-premises or in the Cloud. It can connect to your ERP system or payroll system and much of the data within the Power BI desktop so that when you pull the data into the Power BI for reporting, you would not even know where the data is coming from. It would be all seamless.
Listen to the full recorded webcast here.
Related recording: Keeping up with Power BI updates: Take your reports from basic to impressive
RSM is an award-winning Microsoft partner, recognized for our ability to satisfy the needs of Dynamics customers in multiple industries. Our proven methodology helps you take full advantage of your Microsoft investment. With over 950+ consultants across the U.S. supporting our technology consulting practice you can be confident that we assembled a team based on your industry and technical needs.
Contact RSM online or call at 800.274.3978.
By RSM US LLP, www.rsmus.com/dynamics
