U2U Blog

for developers and other creative minds

Dynamics CRM 2013 and Power BI for Office 365 - Part 2

In the first part of this blog post we used Power Query to create the following Data Model in Excel. As you can see the relationship between the 2 tables is still missing and we would also like to define a Hierarchy in QueryFromContact. And although we could have done this in Power Query itself we would like to give our tables and columns more meaningful names. If you later want to publish this model to Power Q&A you also should define synonyms for your columns and tables but this is something that cannot be done if you have a regular version of Excel installed. Only the Excel versions that comes with Office 365 Professional Plus or the Click-to-Run version of Office 2013 Professional Plus have this capability.

Let's first clean up the QueryForContactSales table by renaming it to Sales. The ContactId field we would like to hide from the End User in the client tools. You can do this by right-clicking on it and selecting Hide from Client Tools.

Next we can right-click on the Sales table tab and give this table a decent description. This can also be done for each column you have in your table.

Let's rename the QueryForContacts table to Contacts. And rename Address1_City and Address1_Country to City and Country. If you have a version of Excel you also might want to define synonyms for your columns. Again hide ContactId from the client tools. The table now looks as follows.

Next we will make FullName the only default field and set ContactId as the row identifier. You can find these options when you press either the Default Field Set or Table Behavior button in the ribbon.

When the City column is selected Power Pivot does not yet know that it actually contains city names. You can see this in the screenshot that shows the Contacts table in the Data Category setting. When the Data Category is explicitly set to city Power Pivot knows that this field contains city information. This is important when we later want to create Power View Reports. We also need to verify that the Data Category for the Country column is set to Country (or Country/Region).

Now that we have cleaned up our two tables let's define the relationship between them and the hierarchy in Contacts. For this we which to the Diagram View. We drag ContactId from the Sales table to the Contacts table to define the relationship. Next we can right-click on the Contacts table to add the hierarchy. We name is Contact Geography. This hierarchy has the levels Country->City->FullName. Depending on which tools we want to support for end-user reporting we could hide the individual fields from the client tools like we did with ContactId. But if you want to use i.e. Power Map you have to leave the fields as is because Power Map does (currently?) not support hierarchies.

The final result looks as follows

Let's now turn back to Excel and add Power View Report to the workbook. A Power View report is created in the Excel Workbook by clicking the Power View button in the Insert ribbon tab.

Once Power View opens you will see the Power View report designer with the Power View Field List on the right. First we drag the Contact Geography hierarchy in the field list. Next we can add our 2 measures from the Sales table. We do not want to go to the sale of each customer however so FullName can be removed from the field list.

Each report you create in Power View initially starts as a table. Tables do not support hierarchies however. Only matrices do. We can switch to a matrix report by pressing the table button in the ribbon and selecting Matrix. In the Options section of the ribbon we could then use do enable Drill-Down/Drill up (via the Show Levels button) and show or hide the (sub) totals (via the Totals button).

In the image below we drilled into the country of Belgium and the totals where hidden. The up arrow can be used to go back to the Country level.

Drill up to country.

Although it's a little bit small we could add a Map to the left displaying the cities as Locations and Sales Amount in the Size field. The Map itself is tiled by the Country field. In the screenshot below a Column chart was added on the Country and Sales Count. Note that this chart also becomes a slicer for the 2 other report items. If you were to click on the bar for U.S. the Matrix and Map would only show U.S. data.

Our Excel workbook is finished and we will save it on the desktop of our local computer. If you have a on premise SharePoint 2013 environment this document could be uploaded to a Power Pivot Gallery.

In the third and final part of this blog post we will upload the Excel workbook to a Power BI site in Office 365.

Dynamics CRM 2013 and Power BI for Office 365 - Part 1

 

In this blog post we will have a look at how you can use Power Query to create a Power Pivot data model in Excel and publish it to a Power BI site in Office 365. Once the Excel file with the Power Pivot data model is in Power BI for Office 365 we can query it with Power Q&A.

If you have downloaded and installed the latest version of Power Query you should see a tab labeled Power Query in Excel 2013. With Power Query you can extract data from the web, relational database, SharePoint lists… In order to access our CRM data we can use the CRM OData feeds. In the installation that I'm using Dynamics CRM is running on the URL http://springfield:5555/AdventureWorksCycles.

 

First we will extract all contacts from Dynamics CRM 2013 using the following OData query:

http://springfield:5555/AdventureWorksCycles/XrmServices/2011/OrganizationData.svc/ContactSet?$select=ContactId,FirstName,LastName,Address1_City, Address1_Country

Since Dynamics CRM requires that we authenticate when we access the OData feed it is going to ask us how we would like to authenticate against the OData feed. You only need to provide this information once since all the authentication settings will be stored by Excel. Because we are accessing an on premise installation of Dynamics CRM we will choose Windows authentication. If you are using Dynamics CRM Online you would choose Organizational Account and provide your username and password.

Once we press the Save button the Power Query window opens.

Because we are accessing an OData feed Power Query is going to ask us some information about the privacy settings of this data. We can tell Power Query whether the data is Public, Organizational or Private. This setting is going to determine with which other data source our data source can be combined with. Public data source are for instance isolated from other Organizational and Private data sources. Since we are working with corporate data we can select Organizational as the privacy level.

Once the authentication and private settings are configured we see the Power Query Editor. We start by giving our query a decent name and description. When we are finished writing our Power Query we want to load the resulting data in a Power Pivot data model and not in the worksheet. The Load Settings in the bottom right of the Power Query editor window must be configured accordingly.

Now it's time to do some simple transformations on the data. In the source we have 2 separate fields for First Name and Last Name. This should become one field labeled Full Name. We can do this by pressing the "Insert Custom column" button in the ribbon. Once the Insert Custom Column window opens the formula can be build.

As you define these transformations on your data source they will be added as individual steps in list box in the Query Settings pane. By clicking on the gear icon you can review and change the settings of each step. And if you right-click on one you can alter its name, position or delete the selected step.

We now need to add another step to rename the column. In order to do this, you can right-click on the column labeled Custom and select Rename. Another step will be added to our Power Query. Next we want to remove the FirstName and LastName column by selecting them and in the context menu we can select remove. The final result looks as follows:

Once the query is finished press the Apply & Close button in the ribbon.

568 Contacts have been loaded into the Data Model using the Power Query we just wrote.

Next we would like to load the Sales Orders from Dynamics CRM. We can use the following OData query for this.

http://springfield:5555/AdventureWorksCycles/XrmServices/2011/OrganizationData.svc/SalesOrderSet?$select=TotalAmount,CustomerId

In the previous OData query all the attributes we retrieved where of a simple type like string, number... In this query however TotalAmount and CustomerId are complex properties which will make things a little bit more interesting in Power Query.

In the above screenshot you can see the TotalAmount and CustomerId. If you are not familiar with Dynamics CRM a CustomerId is a so called Entity Reference and it can either reference a Contact (a Person) or an Account (a Company) in this example. In the snippet shown the CustomerId references a contact record identified with the Guid in the Id field. And the name is Yvonne McKay. This is the value of the so called Primary Field in Dynamics CRM.

If we follow the same procedure as before to build our Power Query we can the following initial result. The query was given a decent name and description. The Load Settings are again set to load this data in the Data Model.

Because we are retrieving 2 complex properties they are displayed as records in the result. Each column has a special drill down icon in the column heading. When you click on it you can selected which fields you want to have in the drill down. Since our first Power Query already has the name of the Contact we can leave it out.

Next we rename the CustomerId.Id column to ContactId. And then drilldown on the TotalAmount column and rename TotalAmount.Value to Total Amount.

Like was said before a sale can be either for a Contact or an Account. We only need the Contact sales. Like in an Excel table we can right click on the small triangle displayed in the CustomerId.LocalgicalName column and filter out all rows where the value is not contact.

Next we can add one more step to actually remove the CustomerId.LocalgicalName column. We now have the following steps in our Power Query

There is one last transformation we would like to do. Since we are building a very small data warehouse we are not interested in having data at the transaction level. So we can group our data on the ContactId field and apply the sum aggregation function on the Total Amount field. We would also like to have a new measure that tells us how many orders a contact has placed. This transformation can be added by pressing the Group By button in the ribbon.

Here is the final result of our second Power Query.

The final query in our exampled loaded about 45.000 rows from Dynamics CRM but because of the Group By we only get 564 rows in the Data Model.

If we open the Power Pivot Data Model this is what we get in the Diagram View

Finishing this Power Pivot Model and creating a Power View report on top on it will be the topic of the second part of this blog post.