- 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
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|
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)
In a next post I will take a look at how you can embed Power BI reports in other web applications as well as Dynamics CRM.