Using filled maps in Microsoft Power BI for provinces, regions and counties in European countries

A couple of weeks ago I wrote a blog post Problem with filled maps (choropleths) in Power BI for Belgian provinces in which I stated that there are some problems with using filled maps for international locations. Thanks to the excellent support of the Power BI team I managed to resolve the issue for the Belgian provinces.

If you ever want to use filled maps with Power BI it is important to use the correct data category and this can be quite confusing for Europe where we have a multitude of different ways of dividing up a country which do not always correspond to the way it is done in the U.S. The data categories for locations that you can use on a filled map are Continent,Country/Region, State or Province and County .

So when I switched the data category for my Belgian provinces to county – the filled map already looked at lot better but not completely since it shows the Grand Duchy of Luxembourg (https://en.wikipedia.org/wiki/Luxembourg)  and not the province Luxembourg (https://en.wikipedia.org/wiki/Luxembourg_(Belgium) ) on the map.

So I tried it out for a number of neighboring countries - the next table gives an example of what the different data category labels (in bold in the first row) correspond to for a number of European countries.

I tested it with Belgium, Germany, the Netherlands and France. I did not get it the regions to work correctly for  France but this might be because France has officially merged a number of regions since January 1st. France is also subdivided into a number of departments  and these were shown on the map as expected.

Power BI Data Category to use	State or Province	County	Remarks
Belgium	Flanders, Walloon region and Brussels Capital region	Antwerp, East-Flanders, Flemish Brabant, Limburg, West-Flanders, Hainaut, Liège, Luxembourg, Namur, Brabant-Walloon	Province Luxembourg not depicted correctly
Germany	The different bundesländer: Baden-Württemberg, Bavaria, Berlin, Brandenburg, Bremen, Hamburg, Hesse, Lower Saxony, Mecklenburg-Vorpommern,North Rhine-Westphalia Rhineland-Palatinate, Saarland, Saxony, Saxony-Anhalt, Schleswig-Holstein,Thuringia
France		The different departments as outlined on https://en.wikipedia.org/wiki/List_of_French_departments_by_population .	I removed the overseas departments to make it workable
Netherlands	The different provinces: Drenthe, Flevoland, Fryslân, Gelderland, Groningen, Limburg, North Brabant, North Holland, Overijssel, South Holland, Utrecht, Zeeland

Different German states with the GDP per capita.

French departments with population density.

Dutch provinces with population density

Technorati Tags: Power+BI,Microsoft,visualization,geocoding

Data science for the rest of us

A couple of weeks ago I followed an interesting webinar from Microsoft called Data Science for the rest of us. I have been interested in data science ever since I read the excellent book Doing Data Science: straight talk from the frontline from Cathy O’Neill and Rachel Schutt and articles like the Data Scientist: the sexiest job of the 21st century sparked this interest even more.

In this webinar Brendan Rohrer (@_brohrer_)  explains with a number of great examples some key ingredients or trade secrets of doing data science in easy to understand terms – here’s a quick recap (although I really recommend you to watch the video):

• Trade secret 1: Data is not the starting point (and you have to ask sharp questions): I really like the definition as formulated by  Jeff Leek (@jtleek) (taken from Data science done well looks easy, which is a big problem) Data science is the process of formulating a quantitative question that can be answered with data, collecting and cleaning the data, analyzing the data, and communicating the answer to the question to a relevant audience. So you first need a precise question and then you need to look for the right data or as indicated in the webinar relevant, connected, accurate and enough data. I’m not a data scientist but this really seems like the hardest part (or as phrased here For Big Data scientist, ‘janitor work’ is the key hurdle to insights )
• Trade secret 2: Turn your data in a picture – check out the example used in the seminar below. It is important to understand that people effortlessly recognize and classify objects among tens of thousands of  possibilities so visualization of your data can help you to make sense of the data (For an interesting scientific article on this topic – take a look at How does the brain solve visual object recognition? )

• Trade secret 3: Data science can only answer five questions: predict how much/how many [regression], which category does something belong to [classification], which groups exist in a dataset [clustering], is something weird [anomaly detection] and which action should you take[reinforcement learning].
• Trade secret 4: Machine learning is simple. This statement is a little aggerated – but the analogy of mastering a foreign language and mastering machine learning is indeed correct. You need to learn the lingo (everyone probably knows tables – either in Excel or a database, but data scientist will refer to these  rows of data in a table as data point or samples by data scients. The columns in your table typically describe a specific characteristic – well  data scientist will call this a feature.)
• Trade secret 5: there are a lot of right ways to solve a specific problem. If you look at the Machine learning algorithm cheat sheet for Microsoft Azure Machine Learning Studio you will notice that there a lot of different ways to solve a specific problem (with certain nuances such as the number of features available, or speed of calculating the model, …) but in most cases it apparently does not seem to matter that much.

To get an overview of other Microsoft webinars on similar topics check out Big Data and Advance Analytics: On-demand and upcoming live webinars
References links:

• What can data science do for me?
• Data exploration through visualization
• What types of questions can data science answer?
• Which algorithm family can answer my questions?
• Machine learning algorithm cheat sheet for Microsoft Azure Machine Learning Studio
• How to choose algorithms for Microsoft Azure Machine Learning

Technorati Tags: datascience,data+science,azure,analytics,azureml,microsoft,machine+learning

Resolving the Dynamics CRM minFreeThreads error

A while ago I booted up a new Hyper-V virtual machine with Dynamics CRM installed and I received the following error “The value for ‘minFreeThreads’ must be less than the thread pool limit of 400 threads.” when opening the Dynamics CRM web page.

Apparently the  Microsoft .NET Threadpools settings of the machine.config where changed based on the guidelines defined in Optimizing and maintaining a Microsoft Dynamics CRM 2011 Server Infrastructure.

Parameter	Value
maxWorkerThreads	100
maxIoThreads	100
maxconnection	12*n (where n is the number of CPUs)
minFreeThreads	88*n
minLocalRequestFreeThreads	76*n
minWorkerThreads	50 (manually add this parameter to the file

So if you change the number of processors assigned to the machine - you will also need to change the machine.config

Technorati Tags: dynamics+crm,administration,optimization

Problem with filled Maps (choropleths) in Power BI for Belgian provinces

Update 2016/02/06: Thanks to the Power BI team feedback – I managed to get this working correctly – check out Using filled maps in Microsoft Power BI for provinces, regions and counties in European countries for the explanation.

A couple of weeks ago I wanted to try out the new filled map functionality (also referred to as choropleth) in Power BI ( See Tutorial: Filled Maps (Choropleths) in Power BI) – I wanted to start with a very simple data set

Province	Dutch name	French name	Capital	Surface	Population
Antwerp	Antwerpen	Anvers	Antwerpen	2860	1813282
East-Flanders	Oost-Vlaanderen	Flandre orientale	Gent	2982	1477346
Flemish Brabant	Vlaams-Brabant	Brabant flamand	Leuven	2106	1114299
Limburg	Limburg	Limbourg	Hasselt	2414	860204
West-Flanders	West-Vlaanderen	Flandre occidentale	Brugge	3151	1178996
Hainaut	Henegouwen	Hainaut	Mons	3800	1335360
Liège	Luik	Liège	Liège	3844	1094791
Luxembourg	Luxemburg	Luxembourg	Arlon	4443	278748
Namur	Namen	Namur	Namur	3664	487145
Brabant-Walloon	Waals-Brabant	Brabant wallon	Wavre	1093	393700

Unfortunately I could not get the filled map to display correctly – I tried the province names in three different languages but nothing seemed to work.

According to Bing Maps Geographic Coverage – geocoding precision for Belgium should be fairly good. What are your experieces with this – do filled maps work correctly for provinces/regions outside of US? Leave a comment.

Technorati Tags: Power+BI,visualization,geocoding

Using Microsoft Power BI Desktop to build Dynamics CRM Online Reports Part 5 –Refreshing data and custom visuals

This is the fifth part in a series of blog posts about Power BI and Dynamics CRM Online – previous blog posts:

• 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

When you publish a Power BI report the data will not be automatically refresh (except for direct query data sources e.g. connectivity with SQL Server Analysis Services) – so you will need to define a data refresh schedule.

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

In this second part we will explore how you can use custom visuals developed by third parties into Power BI (a feature introduced with the October 2015 Update – see Visualize your data, your way using custom visuals in Power BI for more details).  In this post I will not focus on how you can build your own custom visuals but here is some background information for those who want to get started. To help developers get started, Microsoft published the code for all their visualizations on GitHub Power BI Visuals as an open source project. The project contains over 20 visualization types, the framework to run them and the testing framework. The visuals are built using D3.js which is a JavaScript library for manipulating (html) documents based on data.  From the website:
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.

Technorati Tags: Power+BI,Visualization,analytics,dynamics+crm,crm+online,msdyncrm

It’s alive– Open Live Writer

I have been using Windows Live Writer since the first beta in 2006 and I it has been my preferred blogging tool ever since. So it was great to hear that there finally is a successor – welcome Open Live Writer.

Open Live Writer is an open source application enabling users to author, edit, and publish blog posts. It is based on a fork of the well-loved but not actively developed Windows Live Writer code. Open Live Writer is provided under a MIT license.

Also check out @Shanselman post – Announcing Open Live Writer – An Open Source Fork of Windows Live Writer -  for some more background information.

Technorati Tags: blog,blogging,livewriter

Office 365 Tip–Change an Office 365 password to never expire

Sometimes you don’t want a password of an Office 365 user to expire e.g. when you use it as a system account. You can’t do this using the Office 365 Admin center but it is quite easy using the Azure Active Directory Module for Windows PowerShell.

1. Connect to Office 365 using your Office 365 global admin credentials by running the following cmdlet: Connect-MsolService
2. Set the password of one user to never expire, run the following cmdlet: Set-MsolUser -UserPrincipalName <serviceaccount@contoso.com> -PasswordNeverExpires $true

For an extensive walkthrough, check out the links below

References:

• Manage Azure AD using Windows PowerShell
• Set an individual user’s password to never expire

Tags van Technorati: Office365,Azure,AAD,administration,identity+management

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

This is the fourth part in a series of blog posts about Power BI and Dynamics CRM Online.

• 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

In this part we will look at how you can publish reports and share and collaborate on your reports and dashboards within Power BI. Before you can publish Power BI artifacts from within Power BI Desktop you need to be signed afterwards you can publish your Power BI artifacts in one click.

If you are working with multiple Power BI/Office 365 tenants don’t forget to sign out and sign in again with the correct credentials (unfortunately I haven’t found a way yet to see with which user identity I’m signed in on Power BI Desktop).

After you have published your report and created a dashboard you can share and collaborate the published information using three different techniques:

• Sharing dashboards and tiles
• Office 365 groups (Power BI Pro License required)
• Organizational content packs (Power BI Pro license required

It is however important to understand that all users will see the same data (except for connections to on-premise Analysis Services datasets where you have the possibility to define security at a more fine grained level). See Power BI Security for more details (TODO tekst toevoegen)
The next figure gives a great overview of the different techniques but I will elaborate the different techniques later on.

Sharing dashboard and tiles
It is important to understand that shared users must belong to the same Office 365 tenant (no external sharing foreseen) and should have signed up for Power BI. Sharing a specific tile is only possible from within the Windows mobile app (see screenshot below). The dashboard owner can allow others to reshare, review shared access and stop sharing with specific users at any time. When a dashboard owner changes a dashboard, the changes become immediately available to all shared users.
For a detailed walkthrough take a look at Share (and unshare) a dashboard from Power BI

Office 365 Groups
Support for Office 365 Groups in Power BI is only available with the Power BI Pro license. The person who creates the group will also become the owner of the group and is able to manage memberships and promote other users as admin. You should only use Office 365 Groups with edit permissions if you trust the different members since they will all be able to create, update and delete the group content.

For more details take a look at Groups in Power BI. It is also important to understand that Office 365 Groups are not something specific to Power BI but that is an essential building block which is part of Office 365 (See Find help about groups in Office 365) and that the concept of Office 365 groups is being leveraged by the Power BI team (although the integration is still rough on the edges – see Power BI’s odd integration with Office 365 Groups). Some features like the ability to using a group’s OneDrive for Business also require you to have both Office 365 and Power BI licenses assigned to the different users.

Organizational content packs
Content packs are made discoverable in the Content gallery and can be made available to the entire organization, members of a security group, members of an Office 365 Group or specific individuals. Unlike sharing where dashboards and reports are read-only, members can unlock and personalize content packs. Updates made to a content pack will apply automatically to non-personalized content and users who have personalized content, will be notified that a new version is available and get the updated pack without losing the personalizations (they will have both versions).

For more details take a look at Organization content packs: an introduction

Tags van Technorati: Power+BI,Microsoft,Visualization,analytics,dynamics+crm,crm+Online,msdyncrm

Quick Tip: forcing use of Lync/Skype Web App to join a conference

Recently I had some issues joining a conference call hosted by a customer – every time the Skype full client tried to open it gave me an access denied error. Luckily somebody suggested to use the Lync web client instead so I used the workaround described in Forcing use of Lync Web App to join a conference and it actually worked.

To force connecting to a Lync meeting using the Lync Web App instead of the Lync(Skype) full client, open a web browser window, copy & paste the URL for joining the meeting that you received and append the following string to the URL  "?SL=1"

 

Tags van Technorati: Skype,Lync,Microsoft

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

This is the third part in a series of blog posts about Power BI and Dynamics CRM Online – for the previous posts check out the links listed below:

• 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

In this section we will explore how you can define relationships between different tables as well as how you can use the map control in Power BI. This example is based on the Traviata CRM for Insurance Carriers which provides a solution tailored to fit the unique processes of CRM users in the insurance industry. The Traviata CRM solution extends the standard CRM metadata model and supports both a direct and indirect sales model for insurance policies. A customer can purchase an insurance directly our using a broker (typically work with multiple insurers) or an agent (people who work on behalf of the insurance company). 

In this example I will outline how you can track insurance sales and show sales on a map using the address of the insurance broker. The base table to use is the SalesOrderSet which contains all sold insurance products. Next we need to know which channel was used to sell the product, the SalesOrderSet contains a field rdiac_Intermediary.Id which contains the identifier of the intermediary for which the details can be found in the AccountSet.

In PowerPivot for Excel you could create relationships by dragging a link from one table in your model to another – in Power BI Designer you should use the “Manage RelationShips” button in the ribbon – next you define the type of relationship in the dialog window – by selecting the field in the source table and next selecting the field in the destination table. Afterwards we do the same to show the types of intermediary (broker or agent).

The AccountSet table contains a city field and a country field which can be used to show data on a map, but to make the geocoding of CRM records more accurate I added a separate custom column “Location”, which contains a concatenation of the City and Country Field.

So now we can start designing the report – let’s drag the “Location” field and the “Yearly Premium” field on the design canvas. By default it will show in a table format but can you switch to “Map” format by selecting the map icon. I expected the values to be plotted nicely on a map but the Belgian city names where not recognized. It is essential that you tell Power BI that the new column contains a text value which can be used as input for geo-encoding – this can be done by specifying the data category: city (See Data Categorization in Power BI)

After applying this change, I also added the intermediary type (Agent or Broker) to the legend so that we can distinguish between the different intermediary types. I noticed that on the map all of the dots had the same size which was not really what I expected since the size should be bigger, the larger the value. But when I looked at the data set I noticed that the location was blank for a large number of brokers which kind of skewed the representation so I added a page filter to filter out the brokers which have no location. I also added a slicer to make it easier to filter revenue by year. I was surprised that you can’t create a horizontal slicer – please leave a comment on “Create different slicers” if you think this would be an interesting feature.

References:

• Data sources that support refresh in Power BI
• Create and manage relationships in Power BI Desktop

Tags van Technorati: Power+BI,Microsoft,Visualization,analytics,dynamics+CRM,crm

Understanding non-interactive users in Dynamics CRM Online

A non-interactive user is a user account in Microsoft Dynamics CRM Online that will only be used for programmatic access to CRM (using the web services layer) such as as for integration with an ERP system or other LOB systems. A non-interactive user can not use the user interface. You can create up to  5 free non-interactive user accounts in Dynamics CRM Online.
The benefit of doing so is that non-interactive users do not require a license. Go to Settings>Security>Users – on the Administration tab, you will see the Access mode for a specific user – in Dynamics CRM Online you will see the 3 different types of access modes.

Non-interactive users are apparently something specific for Dynamics CRM Online – if you open the same screen in Dynamics CRM 2015 – you don’t see the non-interactive access mode option.

References:

• User and team entities in Dynamics CRM 2015
• Microsoft Dynamics CRM 2015 editions and licensing
• Licensing plans for Microsoft Dynamics CRM Online

Tags van Technorati: Dynamics+CRM,CRM,CRM+Online,Dynamics

Renewed as SharePoint MVP for 2015–11 times in a row

I must admit that I was surprised that I got renewed as SharePoint MVP (For those who don’t know what a Microsoft MVP is check out http://mvp.microsoft.com/en-us/overview.aspx ) for the 11th time in a row on October 1st. The last couple of months I have been working – and also blogging - mostly on  Big Data and advanced analytics solutions, Dynamics CRM and Office 365 in general. But I’m still proud to be part of such a great community of MVPs who got renewed (Welcome nearly 1000 New and Renewed MVPs!). 

After 10 years on the board of BIWUG – the Belgian SharePoint User Group (www.biwug.be ) – I also said farewell as an active board member but I’m convinced that Andy, Elio, Gerrit,Ben, Bram, Jim and Thomas will continue to make it worthwhile for all of the 800+ Belgian members of BIWUG. Don’t forget to register for the next BIWUG session on October 29th about Azure AD Authentication and Developing SharePoint Online applications using PAAS building blocks.

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].crm4.dynamics.com/xrmservices/2011/OrganizationData.svc/OpportunitySet?$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.

References:

• Using Microsoft Power BI Desktop to build Dynamics CRM Online Reports Part 1
• Getting started with color formatting and axis properties in Microsoft Power BI
• Tips and tricks for color formatting in Power BI

Tags van Technorati: Power+BI,Microsoft,Visualization,analytics,Dynamics+CRM,CRM

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

Tags van Technorati: Power+BI,Microsoft,Visualization,analytics,Dynamics+CRM,CRM