This article will show how you can use the new Power BI Designer to create Power View reports on top of data that is stored inside Microsoft Dynamics CRM 2015. First we will build Power Query queries to extract data from the Accounts, Opportunity and Product entities. Next we will use the reporting capabilities to create some Power View reports on top of the extracted data.

The new Power BI Designer

Using the new Power BI Designer you first create Power Query queries that extract data from your Data Sources. The data that is extracted using the Power Query queries is loaded into an PowerPivot data model that is not visible in the Power BI Designer. Once you have loaded all the data you can start defining Power View Reports on top of the data model that you created. In the screenshot below you can see the UI of the Power BI Designer. In the lower left corner you see the two tabs that allow you to access the Power Query queries and Power View Reports.

Everything that you create using the Power BI Designer is saved in a local Power BI file (.pbix file) This Power BI file will not only contain the definition your Queries and Reports, but will also store your data.

Extracting Accounts data 

Lets start by extracting some account data from Dynamics CRM. To load this data we can click the Get Data button in the ribbon and use the Microsoft CRM Online predefined data source. When you enter the URL of your Dynamics CRM organization data service and press the OK button you will be asked for the credentials you would like to use to access Dynamics CRM

Once you are signed in you will see the Navigator window from which you can select the entity set you would like to import. In this example we'll take the Account entity

You have now created a first Query that will load all fields and all rows from the Account entity into Power BI. In the Name textbox of the Query Settings window we can change the name to Accounts. Next the Remove Columns transformation can be used to only keep the Name, AccountId, Address1_Coutry,Address1_City, Address1_StateOrProvince and StateCode fields.

r

Notice that the StateCode field (which is an OptionSet inside Dynamics CRM) comes in as a column of type Record. If you click on the Expand button in the caption you can select the that you want to have the Value field shown.

For all the Active Accounts the StateCode.Value field will be 0, for the Inactive ones it will display a value of 1. Since this is not very user friendly we will need to replace these to values with the labels Active and Inactive. For this w can use the Replace Values transformation of Power Query. But first you will need to use the Change Data Type transformation to make the StateCode.Value column of type Text. After this the Rename Column transformation can be used to give all the columns a decent name. The final query looks as follows:

Extracting Opportunity data 

In the second query we will be extracting the Opportunity data from Dynamics CRM. If you know upfront what entity set you want to retrieve and don’t want to use the Navigator window you can directly enter a valid OData URL in the URL textbox. Since we know that we want to retrieve all opportunites enter https://u2u365.crm4.dynamics.com/XrmServices/2011/OrganizationData.svc/OpportunitySet.

In the Query Settings window we can name this Query Opportunities. Again the Remove Other Columns transformation can be use to only keep the OpportunityId,CustomerId, PurchaseTimeFrame, EstimatedValue, Name and StateCode, EstimatedCloseDate columns.

Notice that the fields CustomerID (EntityReference in Dynamics CRM), EstimatedValue(Money) and PurchaseTimeframe(OptionSet) are again of data type record. If we use the Expand transformation to transform them we get the following result

For the PurchaseTimeframe field we could use the same procedure to replace the OptionSet values with their labels. But this is no very maintainable as these Labels can change over time. Another way of working is to write another Query to retrieve the labels of the PurchaseTimeframe OptionSet and then combine these to Queries in a third Query. This is what we will do in a later step in this blog post.

Also notice how in the column EstimatedCloseDate the dates are stores in UTC date format.

We want to convert these dates into our local time format. Problem is there is no support in the UI for doing this. We will actually need to write a little bit of M code to achieve this goal.

We first use the Rename Column to change the name of the EstimatedCloseDate field to EstimatedCloseDateUTC. Also the data type of this column needs to be transformed to Date/Time/TimeZone. Next we can use the Add Column transformation to insert new column in the Query. To do this, we can go to the Add Column tab in the ribbon and click on the Date->DateOnly menu item. This will add a second column with the date as shown below.

When you add this transformation the following M code was generated for this step.

In this M code we need to manually replace the DateTime.Date function with the DateTimeZone.ToLocal function. Next the data type of this new column can set to Date Only. When this is done we get the following result

To finish we can remove the EstimatedCloseDateUTC column and rename Date to EstimatedCloseDate.

If we use the Rename Column transformation to give all columns a decent name we get the following result for the Opportunities query

To replace the PurchaseTimeframe OptionSet values with their corresponding labels we need to write another Query to retrieve the labels first. Inside Dynamics CRM all OptionSets are store in an entity set called PickListMappingSet. We can create a third Query named PurchaseTimeFrameOptionSet using the following OData URL https://u2u365.crm4.dynamics.com/XrmServices/2011/OrganizationData.svc/PickListMappingSet. Again we can use the Remove Other Columns transformation to only keep the SourceValue, TargetValue and ColumnMappingId columns. We get the following result

Epand the ColumnMappingId Name field and you can now see the name of the different OptionSets. Since we are only interested in the PurchaseTimeframe OptionSet we will filter out all other values.

Notice that there are many duplicate values. We can remove them using the Remove Duplicates transformation.The final version of the PurchaseTimeFrameOptionSet query will return the following result

We now have to Queries for which the results need to be merged. In the Queries window we can select the Opportunities query and in the ribbon press the Merge Queries button. A Merge window will be displayed that will ask us for the second Query to merge the first one with and on what fields this has to be done.

From the first Query we can take the PurchaseTimeframe column and from the second Query the Target Value. When the OK button is pressed a new column labelled NewColumn of type Record is added to the Opportunities Query. If we expand this column and only select the SourceValue column we now have the Labels of the OptionSet. To clean things up we can remove the original PurchaseTimeFrame column and rename NewColumn.SourceValue to PurchaseTimeFrame. 

Loading Product Data

In Dynamics CRM an opportunity typically has many products in it. Let's write one more Query that that extracts all product related data for the opportunities. This data is stored in an entity set called OpportunityProductSet. The URL for this entity set is https://u2u365.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/OpportunityProductSet. If you know upfront that you will only need certain columns of an entity set you can use the $select filter operator to limit which column you are loading in your query. So in the URL text box of the Dynamics CRM Online data source we could actually type in the following URL https://u2u365.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/OpportunityProductSet?$select=OpportunityId, ProductId, PricePerUnit, Quantity, ExtendedAmount, VolumeDiscountAmount, ManualDiscountAmount

Lets name this Query Products. We immediately get the limited list of columns. There is no need anymore to do a Remove Other Columns transformation. 

When we expand all columns and give them a decent name we get the following result.

We now have 4 Queries in Power BI to load the Account, Opportunity and Product data from Dynamics CRM. Since most reporting is also done over Date we want to add a Date Query to our model. To generate the Date data we can use a custom M function. On the internet you will find quite some Power Query M functions to generate this kind of data. If you go to https://gist.github.com/philbritton/9677152 you can copy the source code of a sample M function. Let's copy this M code in the clipboard. When we go back to the Power BI Designer we can now add a Blank Query to our designer and name it Dates. If we go to the Advanced Editor window of your Query we can paste the M code in.

If we press the Invoke button we are asked for the parameters values of the function

When the OK button is pressed, the M function will get executed and the result is displayed. Lets rename the MonthName column to Name and QuarterInCalendar to Quarter.

Enriching the model

When we now click the Report tab in the Power BI Designer all of our Queries start executed and once finished the following window will be displayed

There are 2 more things we need to do before we can create a Report. First we want to hide the PurchaseTimeFrameOptionSet table. This can be done by right clicking on it and selecting Hide. Next we actually need to define the necessary relationships between the different tables. For this we press the Manage button in the ribbon. This will display the Manage Relationships window

Notice that the relationship between Products and Opportunities was automatically detected. First we will define the relationship between Opportunities(EstimatedCloseDate field) and Dates(Date field). Next we can add the one between Opportunities(CustomerId field) and Accounts(AccountId field)

Once we have these relationships in place we can start creating Reports. But to clean up our model even more there some columns we would like to hide in the Report window. From the Accounts table, hide the AccountId field. Next we hide the columns CustomerId, EstimatedCloseDate and OpportunityId from the Opportunities Table. From the Products table we will hide the ProductId and OpportunityId columns. And from the Dates table we only keep Day, Month Quarter and Year as visible columns.

Creating Reports

Lets first create a tabular report that shows the order quantity by product name. From the Products table we select the Product Name and Quantity field and the following visualization is displayed.

Using a column chart we would also like to visualize the sales by year. First select the Year field from the Dates Table. When we do this a column chart will be added to the current page. On the Values axis Count Of Year was added as the field. We would like to see the sum of Quantity. So Count Of Year should be removed and we can drag and drop the Quantity field on the Value axis. This will show us the Sum of Quantity By Year.

We would like to be able to double click on a Year and see the Quantity by Quarter. To achieve this we can drag and drop the Quarter field underneath the Year field on the Axis window. If we now double click on the year 2014 we see the Quantity per Quarter for 2014.

Lets now select the Country field. Again a table visualization is added to our Report. If we convert the able to a map visualization and drag and drop the Quantity field in the Values window we get the following map

We would like to be able to drilldown into this map and see the quantity by State/Provice or City. For we need to drag and drop the StateOrProvince and City fields underneath the country field. When we double click on USA we get the following map.

As a final step we would like to share these report visualizations with the members of our team. This can be done use the new Power BI web site.

Using the Power BI Web site

When you first logon the Power BI web site you will see the following window in your browser