JOPX on Microsoft Business Applications and Azure Cloud: Using Microsoft Power BI Desktop to build Dynamics CRM Online Reports Part 5

This is the fifth part in a series of blog posts about Power BI and Dynamics CRM Online – previous blog posts:

Using Microsoft Power BI Desktop to build Dynamics CRM Online reports – Part 1 – Introduction
Using Microsoft Power BI Desktop to build Dynamics CRM Online reports – Part 2 – Using option sets in reports
Using Microsoft Power BI Desktop to build Dynamics CRM Online reports – Part 3 – Relationships and the map control
Using Microsoft Power BI Desktop to build Dynamics CRM Online reports – Part 4 - Sharing and collaborating

When you publish a Power BI report the data will not be automatically refresh (except for direct query data sources e.g. connectivity with SQL Server Analysis Services) – so you will need to define a data refresh schedule.

But before you can define the refresh schedule Power BI needs to be able access the Dynamics CRM Online OrganizationData.svc service, fortunately this service supports certain authentication capabilities found in the oAuth2 protocol. The OAuth 2.0 authorization framework - definition from the spec at Internet Engineering Task Force (IETF) enables a third-party application to obtain limited access to an HTTP service, either on behalf of a resource owner by orchestrating an approval interaction between the resource owner and the HTTP service, or by allowing the third-party application to obtain access on its own behalf. So oAuth is one of the industry standards around federated identity and it’s main goal is to eliminate the need to give system A your user name and password for accessing system B and it allows you to determine what system B can get from system A once it’s been allowed access. So in simple terms – oAuth allows Power BI to talk to Dynamics CRM Online using the access token that you got back when first authenticate using the screen below and in this way Power BI does not need to store the user name and password.

You have to make sure that the credentials for the different data sources are up to date before you can set up the refresh schedule so you have to specify the credentials and make sure that you use oAuth as authentication method.

In Power BI Standard edition you then have the option to schedule a daily or weekly refresh – for an hourly data refresh you will need to upgrade to Power BI Pro. The table below lists the different available refresh options and the required subscription of Power BI (Source: Data Refresh in Power BI).

Data Refresh	Power BI (free)	Power BI Pro
Datasets scheduled to refresh	Daily	Hourly
Streaming data in your dashboards and reports using Microsoft Power BI REST API or Microsoft Stream Analytics	10K rows/hour	1M rows/hour
Live data sources with full interactivity (Azure SQL Data Warehouse, Spark on HDInsight)	Not supported	Supported
On premise data sources requiring Power BI Personal Gateway and on-premise SQL Server Analysis Services requiring Analysis Services Connector	Not supported	Supported

In this second part we will explore how you can use custom visuals developed by third parties into Power BI (a feature introduced with the October 2015 Update – see Visualize your data, your way using custom visuals in Power BI for more details). In this post I will not focus on how you can build your own custom visuals but here is some background information for those who want to get started. To help developers get started, Microsoft published the code for all their visualizations on GitHub Power BI Visuals as an open source project. The project contains over 20 visualization types, the framework to run them and the testing framework. The visuals are built using D3.js which is a JavaScript library for manipulating (html) documents based on data. From the website:
D3 allows you to bind arbitrary data to a Document Object Model (DOM), and then apply data-driven transformations to the document. For example, you can use D3 to generate an HTML table from an array of numbers. Or, use the same data to create an interactive SVG bar chart with smooth transitions and interaction.

So how does this look like from a visualization designer perspective - first you can take a look at the Power BI Visual Gallery for some example custom visuals. Here you will need to download your custom visual definition file – in this example I will use the TadPole Spark Grid Plus – when you click on the download link – you will see that it downloads a pbiviz file.

As a data source I will start from the sales and marketing sample which you can download from the Power BI industry samples (Excel workbooks) . You can import this Excel file within Power BI Desktop, and Power BI Desktrop will try to import the Power Query queries, Power Pivot models and Power View worksheets which you can later on refine using Power BI Desktop. (See Import Excel workbooks into Power BI Desktop for more details).

Next I will create a new report page using data from the sales fact table (Total Units and Sales $) per manufacturer and per year. Afterwards you will need to import the definition file for your custom visual by selecting File>Import> Power BI Custom Visual or clicking the three dots in the visualizations pane and selecting the pbviz file that you just download. Next you can apply your visualization to the report data.

As you see in the example below, it shows a spark line (for sales in units and dollars) with colored and thickened line segments. The black colored segments mean that the value has gone up since last period (desirable), and the red colored segments mean the value has gone down (undesirable). (This behavior is configurable using the properties of the visualization)