Wednesday, August 19, 2015

Using Microsoft Power BI Desktop to build Dynamics CRM Online reports Part 2

In the previous post I showed a very simple example of how you can create a report in Power BI Desktop – in this post I will show you how to extend this simple example. First we will create a new dataset based on Opportunity data in Dynamics CRM Online but we will add extra columns by specifying them in the oData query https://[yourtenantname]$select=CustomerId,EstimatedValue,SalesStage Next we will expand the columns in the same way that we did in Part 1.

If you have worked with Dynamics CRM you will probably know the concept of an Option Set (a.k.a pick list) – which allows to list a set of available choices for a specific field. Dynamics CRM will store the integer value (not the label) within its database. SalesStage is an example of such an Option Set and you notice that the integer value is also exposed in the OData query.

There are two ways of getting the labels back for the Option Set – one is simply using “Replace Values” function from Power BI. The other option, is a more dynamic method using the PickListMappingSet as outlined in Gotchas when using Power Query to retrieve Dynamics CRM Data – Part 2 – here is a brief summary of the steps:
  • Retrieve the PickListMappingSet and  expand the ColumnMappingId column
  • Duplicate the PickListMappingSet and rename it to SalesStage (given it the name of the Option Set makes the whole more understandable)
  • Filter the ColumnMappingId.Name column to only include SalesStage values

  • Finally merge the values of the SalesStage data source with the OpportunitySet data source by selecting Merge Queries (in the Combine section). You will probably notice that not all of the rows can be matched – this is because some of the records contain null values – you should decide up front what how you are going to clean up your data for these types of input errors. Another thing to keep in mind is the fact that option sets that you create yourself are not exposed in the PickListMappingSet – so this requires you to do “Replace Values”. You can vote on Connect for Make user created option sets also available through the PickListMappingSet odata table

  • After the merge a new column is added of type “Table”, click to expand and keep the “SourceValue” column

Finally, I grouped the columns on CustomerId and SalesStage, sorted by estimed revenue and filtered to keep the top 50 rows. Next I used a simple bar chart to display the data – I also played around with the colors of the data labels – also check out the references listed below for some helpful links about color formatting.


Monday, August 17, 2015

Using Microsoft Power BI Desktop to build Dynamics CRM Online reports Part 1

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] . 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.

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 which was announced end July ) or to


Fixing Windows 10 installation error : We couldn’t create a new partition or locate an existing one

Last week I installed Windows 10 on an Acer Iconia Tab W500 and I encountered an error while trying to create a new partition. The Iconia Tab does not have a DVD drive so the easiest way to start is by creating a bootable USB with the Windows USB/DVD Download Tool – next you will need to boot off this USB drive and you will see the Windows 10 installation screens appear.

First you have to select the language to install, time and currency format and keyboard input. Next click install now. At this point you have the option to upgrade your existing OS or do a clean install – I selected Custom: Install Windows Only (advanced) since I did not need to keep the existing files and applications.

Next you need to decide where you are going to install Windows 10.

I removed both partitions I had but when I tried to create a new partition I got an error “We couldn’t create a new partition or locate an existing one.  For more information, see the Setup log files”. Workarounds such as removing the extra SD card or unplugging the USB stick did not resolve the issue. Luckily the steps detailed in this blogpost Error: "We couldn't create a new partition or locate an existing one. For more information, see the Setup log files." when you try to install Windows 8 (CP) are still valid for Windows 10.
  1. Close the setup window and select “Repair>Advanced Tools”
  2. Go to the command window
  3. Start DISKPART.
  4. Type LIST DISK and identify your SSD disk number (from 0 to n disks).
  5. Type SELECT DISK <n> where <n> is your SSD disk number.
  6. Type CLEAN
  8. Type ACTIVE
  10. Type ASSIGN
  11. Type EXIT twice (one to get out of DiskPart, the other to exit the command line tool)
Afterwards just reboot and start the setup again – you will now be able to use the newly created partition.

Thursday, August 06, 2015

Tips and tricks for using search in SharePoint 2013

As I outlined in About intent, recall, relevance and precision of search solutions building a good search solution is quite difficult. In this post I will focus on on what content publishers and content “searchers”  can do to make search in SharePoint 2013 work more efficient.
Content publishers – I want my content to be found:
  • Use meaningful file names and titles for the documents that you add in SharePoint, by default SharePoint will show the filename (also check out Understanding title information shown in SharePoint 2013 search results and how to make it work better ) – use “_” (underscores) when you want to combine multiple words in the filename.
  • Use Promoted results to push results to the top of your search result page based on specific keywords which are used within your organization. You can use the search logs as a starting point but you can also do a survey amongst your users to see which are documents to look for on a daily basis.
  • Add metadata ( also referred to as document properties or attributes of a document). Metadata allows an author to attach supplemental information to a document without touching the actual contents of the document. When you use a file system you typically also have this kind of “meta-information” available such as Created Date, Modified Date, Author etc… A file system however does not allow you to add extra additional metadata to documents and that is why people will revert to creating a folder hierarchy where the folder names are used to describe the documents. SharePoint provides an alternative by allowing you to add metadata which can be used to sort, group, filter, etc… documents stored in a document library (Take a look at Using folders in SharePoint document libraries : some guidance and tips  about metadata and folders in SharePoint). But the metadata does not only apply to browsing and views of documents but it will also be used in SharePoint search to determine relevancy and push documents higher in search results.
  • Publish documents which are relevant to a lot of people near your root site. Document location, file types, authoritative pages, and content language are things you can manipulate to improve a document’s relevance. The URL depth is quite important – the more slashes “/” (deeper in the site structure) in the URL of a document, the less valuable it is considered to be. The click distance between the document and what is called an authoritative page is also important. By default the home page url of your SharePoint site is considered to be an authoritative page but you can configure this yourself – (See Configure authoritative pages in SharePoint 2013)
Content searchers - I am looking for content using search:
  • Use AND and OR as well as other search operators to limit or expand your search results. Always use capitalizations when using these operators – otherwise they will be ignored.
  • Use wildcards to search for documents – you can add a “*” at the end (but not in the beginning). In SharePoint 2013 you can even search for everything by just entering * in the search box. Next you can use the refiners and sorting to limit the results.
  • Use property searches to search for documents of which you know a name in the title or the filename. If you search for “filename: holiday*” it will search for all documents with holiday in the filename. You can also search for specific types of documents by using filetype – e.g. filetype:docx searches for all word documents
It is also important to keep in mind that it is quite easy to completely tailor the user experience of the SharePoint search center to make it look exactly as the user wants so check out the links below. But sometimes simple things such as the steps recommended -  SharePoint 2013 Search: removing the junk from your search trunk – already yields great results.


Wednesday, August 05, 2015

Using SharePoint Online to store Dynamics CRM Online Documents

Integration between SharePoint and Dynamics CRM has been around since Dynamics CRM 2011 – this integration allows for linking documents to Dynamics CRM entities but the documents will be stored in SharePoint instead of the CRM database. This integration is actually a must have for Dynamics CRM Online since there is a built-in storage limit which initially is 5 GB and which is increased with 2.5 GB per 20 licensed users – you can buy additional storage but it is more expensive than storing the documents in SharePoint Online (See Microsoft Dynamics CRM Online : Service Description (whitepaper) for more details)

With the introduction of Dynamics CRM Online 2015 Update 1 there has been an important change with the deprecation of the the Dynamics CRM list component for Microsoft SharePoint. Initially Dynamics CRM integrated with SharePoint using the Dynamics CRM list component for Microsoft SharePoint but with Dynamics CRM 2013 SP1 Server based integration was introduced.
For the time being you still have two different options to integrate Dynamics CRM Online and SharePoint Online – one using the Dynamics CRM list component for Microsoft SharePoint and the other one using Server-based SharePoint integration but this will not be supported anymore after one of the next updates.

In this post I will explain how the server-based SharePoint integration in Dynamics CRM Online looks like from an end-user perspective (For more details about how to set it up - check out Setup CRM online to use SharePoint Online as well as Enable SharePoint document management for specific entities ) and what you should watch out for.  For a deep dive technical description I recommend that you read SharePoint Integration Reloaded – Part 1 .
In this example I have document management enabled for a custom entity called policy (this is an example from the Traviata CRM solution for Insurance Carriers) and document folders are automatically created based on a specific entity (this is something you will need to configure up

The automatic folder creation is actually a very useful feature since you can also use these SharePoint folders to secure these documents (keep in mind that security/authorization needs to be configured separately in SharePoint Online) and it also a way to overcome the list view limit in SharePoint Online (for an interesting discussion around folders using Dynamics CRM see Scalability considerations for CRM/SharePoint integration ) – so the first time that you attach a document to a CRM record a folder will be created based on the related entities – in our example we have an insurance policy for our contact/party Kristof – so it will create a folder structure based on the party entity.

Now you can upload your documents into SharePoint directly from within the CRM user interface – in the past this component used an iFrame but now it is technically completely integrated.

You can directly upload documents to SharePoint within this interface but you can also create Office documents directly from within the interface using the Office Web Apps.

You can also work from within SharePoint and see the different documents from within the SharePoint document library.

You might also have noticed that there is a GUID in the folder name – from a developer perspective this seems like an interesting additional although as an end-user I don’t like it – also check out the comments section of this post CRM 2013 and SharePoint integration new feature for more debate around this “feature”.  Unfortunately there are still some downsides to the server based integration which are clearly indicated in Important considerations for server-based SharePoint integration as well as New server to server integration with CRM Online and SharePoint provides much more limited functionality. Besides document management integration there are however some other interesting options such as integrating Dynamics CRM, SharePoint  and OneNote and I expect a lot more to available in the upcoming releases of Dynamics CRM. For an overview of integration points check out the Integration Guide: Microsoft Dynamics CRM Online and Office 365