A couple of weeks ago I wrote a posting about Combining Dynamics CRM Online and Power BI Preview but since then a lot of exciting things have been released and announced. One of these things is the fact that Power BI Designer has been rebranded to Power BI Desktop and a lot of new functionality has been added.. Power BI Desktop basically ties together Power Query, Power Pivot and Power View in a standalone application, removing the constraint of having to use Excel 2013 to design visualizations but it also extends the existing functionality quite significantly.
The first thing that you need to do when you want to build reports is to get at the data – Microsoft Power BI Desktop support a huge number of data sources but the one I’m interested in is Dynamics CRM Online.
The Dynamics CRM Online data source actually uses the CRM OData endpoint, to find the exact url go to Settings>Customizations>Developer resources, it should look something like https://[yourtenantname].crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/ . I also encourage you to install Dynamics XRM Tools 2015 since it contains an OData Query Designer tool which is quite useful.
The first step you need to take is deciding which columns you will be needing in your data model. I strongly recommend you to remove the columns up front by specifying in the OData query which columns you need. This will decrease the volume to be processed by Power BI desktop and it easier to do this up front. So if you want for example to show the top opportunities based on estimated revenue – you can use the following query.
https://[yourtenantname].crm4.dynamics.com/xrmservices/2011/OrganizationData.svc/OpportunitySet?$select=CustomerId,EstimatedValue
You will notice that the actual values are not being displayed – this is because both CustomerId and EstimatedValue contain complex values, which you can expand by clicking the expand icon in the column header.
Since we only need aggregated data (not the individual opportunities) for the different top customers, we are going to group the data by CustomerId and sum the estimated revenue.
Afterwards you can limit the data by only retrieving the top 20 rows.
Finally we need to visualize the data on a report. In editing mode, we will drop a bar chart control on the designer surface and define the data elements which needs to be displayed
Power BI Desktop also has a wide variety of display options that you can configure for your visualization such as the different options for X and Y axis (show labels, start and end values), colors to use for the data labels, which display unit to use for the data labels(including precision). One thing that I’m still missing though is the option to show the actual values instead of using display units for values below 1.000.
Finally to make your report available to other users you will need to publish it. You either have the option to publish it to Pyramid Analytics Server ( an on-premise alternative for PowerBI.com which was announced end July ) or to PowerBI.com.
References:
- Pyramid Analytics and Power BI
- Tips and tricks for creating reports in Power BI Designer
- Common query tasks in Power BI Desktop
No comments:
Post a Comment