Tip #663: Turbocharge your CRM to Power BI Connection!

Turbo-Snail-iconTurbocharge your PowerBI Connectivity to CRM – Amaze your friends and strangers!

Working in a hardened bunker deep on the mysterious island of “Advanta”, the Microsoft CRM team’s top scientists have unleashed a powerful new API for connecting to CRM. It’s known only by the secret codename “Instance Web API” and today your semi-humble tipster is here to help you unleash it in Power BI!

Based on the OData v4 specification, this API allows, among other things, PowerBI to read data from CRM 5-10x faster than before! (That said, it’s still not as fast as direct SQL access, but that’s a tip for another day.)

How do you as a CRM Online user take advantage of this within PowerBI?

First, you must promise to use it for good and not for evil (unless absolutely necessary).
Second, you must have CRM 2016 Online, preferably the spring update.
Third, Within CRM Settings, under Customizations / Developer Resource find the Instance Web API Service Root URL for your organization.

2016-06-07_16-22-20

Fourth, having memorized – or at least copied – the service root URL from your organization, in Power BI, choose to “Get Data” from “Microsoft Dynamics CRM Online”

Now unleash that “Instance Web API Service Root URL” as the URL for connecting to CRM.

2016-06-07_16-22-52

Once you connect, the Navigator will display the entities from your environment and you can select as needed.

That’s all there is too it!

Note: There are still best practices to keep in mind for optimum performance – Typically limit your download for a given report dataset to 5 entities (stay focused on a specific purpose for that dashboard) – Filter your datasets to typically 50K records or fewer. – Only include the minimum attributes that you absolutely need. Build related tables for lookup items and link based on the “_attributename_value” field rather than ‘expanding’ the attributes within the record (especially for repeated lookups like ‘systemuser’ or ‘territory’) –
We’ll explore these and more in future ‘CRM Tip(s) of the Day’.

Tip #662: USD 2.1 supports Interactive Service Hub but may stumble over popups

Sparkling new Unified Service Desk 2.1 is available for download.

USD 2.1 Interactive Service Hub

Good news: available solutions have been redone, renamed, improved and now make much more sense. Choose between vanilla, upgrade, CRM Web Client and Interactive Service Desk versions. That’s right, version 2.1 added a hosted control for ISH forms and the entire sample solution is built around those.

Even better news: this version no longer requires ubiquitous file copy operation to install the required assemblies. All done with the help of customization files, and USD client will automatically download required assemblies.

Not so good news: you may find that, after the installation, client would sporadically pop up browser windows with the address beginning with http://event/ and, if you happen to install ISH solution, will display a never-ending “Please stay on this screen while we’re doing something…” message on any attempt to navigate between the entities.

This is a known issue related to the way IE process hosting works and you have to do one of the following:

  • Select the Enable Protected Mode check box for the Local Intranet security zone in Internet Explorer
  • Add the http://close, http://event, and http://uii URLs to the Restricted Sites security zone in Internet Explorer
  • Run USD client as administrator

After that, everything is silky smooth and looks awesome.

Tip #661: New and Improved PowerBI Content Packs

PowerBI and Dynamics CRM are an up and coming celebrity “power couple” – they’re seen in all the trendiest places and news about them is reported widely. That said, it came as a surprise to many that Microsoft quietly overhauled the existing Dynamics CRM Sales Content Pack and added a brand new Service Manager Content Pack for CRM in the PowerBI store.

Sales Manager Dashboard Service Manager Dashboard

If you tried the earlier, 1.0, version of the “Dynamics CRM Sales” content pack and found it ‘lacking’ in performance – please go back to the Power BI content store and install the new “Dynamics CRM Online Sales Manager” content pack. The improvements are significant.

You will need to import the new the sales content pack. (Note that the name changed with this version – if you see a “Dynamics CRM Sales” content pack in your environment – instead of “Dynamics CRM Online Sales Manager” – you have the old version.)

Dynamics CRM Power BI Content Packs

There are a couple of major improvements:

  1. Now there are TWO content packs –
    1. An updated one for Sales Managers (focused on opportunities and sales activities)
    2. A brand-new one for Service Managers (focused on cases, sales activities, and knowledge base articles.)
  2. The Sales content pack has been overhauled for performance.
    1. Dramatically reduced the number of fields being retrieved from the entities.
      e.g. the previous edition pulled a huge number of fields from the Account entity (resulting in performance akin to draining a swimming pool through a cocktail straw.) – The new version pulls a much smaller set of fields and results in a much faster refresh.
    2. Rebuilt using the new OData feed for CRM Online 2016 – (as opposed to the older 2011 version which was the only choice up through CRM 2015) – This alone results in a 5-10 X improvement in the throughput and faster refreshes.
    3. Easier configuration for non-developers – just use the URL for CRM, no need to find the specific OData feed url buried in the developer page.

Note that both content packs require CRM 2016 online to gain the full performance benefit –

For more information on how to install the new content packs, see the official documentation here – https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-microsoft-dynamics-crm/

Enjoy!

Tip #660: Talk to your network administrator if you split Dynamics CRM server roles

Messy networkBe nice to your network administrators. Really. They may be pain in the neck but they are the ones keeping the bad stuff out.

If you install Dynamics CRM with all roles on one machine, it typically goes well and rarely requires fiddling with the firewalls. However, once you start splitting the roles, e.g. offloading Asynchronous services to a separate box, make sure you find time to catch up with yourt network administrator and go over the comprehensive list of ports.

One particular port that is not standard and not usually open is TCP 808, used by CRM servers to communicate with each other. It does not affect the installation but then don’t be surprised if if your workflows and plugins do not work.

Tip #659: Installing Dynamics CRM on a named SQL Server instance

I don't tripIt is possible to install Dynamics CRM On-premises using SQL Server named instance. For most parts, the process is straightforward and no different from a “standard” installation except that you have to use server_name\instance_name notation when referring to SQL Server. But sometimes things don’t go according to the plan and this is what I learned:

  • Have SQL Server Browser service on. On some networks administrators tend to keep SQL Server Browser service off. It does minimize the attack surface and follows the idea that on-premises you ought to know what you’re connecting to anyway. As it turns out, that does not sit well with both CRM installer and CRM Server after the installation is complete.
  • Fix the port. Change the port for the SQL Service from dynamic to a fixed one, e.g. 1434. While it might work with dynamic ports as well, I found it’s easier to deal with known quantities.
  • Open TCP port on SQL Server firewall for that port number. Chances are you already have a rule for the default instance, just add the port there.
  • Open UDP port 1434. This is SQL Server Browser Service. It’s documented but buried towards the end of the document and easy to miss.
  • Install a separate instance of SQL Server Reporting Services. One of the ideas I’ve seen floating around is that, because SQL Server is licensed per physical core, you can install multiple instances on one machine and save a bundle. That is true, until you need to run any custom fetchxml reports. To have that you need Dynamics CRM Reporting Extensions installed. And here is the catch – reporting extensions only support single installation and use fixed registry key to store SQL Server connection string. That means that you need to have SSRS installed elsewhere (could be CRM server itself, for all I care) but that would require an additional SQL Server license. Oops.

Other than the above, named instance installation works and runs smoothly. It totally makes sense in some scenarios, for example, if you have a beast of a server with enterprise licenses already in place.

Tip #658: Tipster guide to Dynamics CRM 2016 Field Services

Field service agentIn the first video in our Field Service series, we explore the capabilities this solution provides when added to Dynamics CRM. We walk you through basic setup and navigation and explore how to create and use work orders.

YouTube player

Give us your feedback, all of it: good, bad, and ugly, I’m sure we can take it. Suggest new topics either in comments or by sending your ideas to jar@crmtipoftheday.com.

And don’t forget to subscribe to http://youtube.com/crmtipoftheday

Tip #657: CRM 4.0 Registration Hack

CRM 4.0 RegistrationRemember good old days of CRM 4.0 when there were in total 42 Dynamics CRM consultants around the world, product names had dots in them, icons had colors and legends like Michael Höhne ruled the newsgroups?

Even if you don’t, there is still a decent chance that you may need CRM 4.0 installation to bail out a stubborn customer still using Windows 98 around the office because fundamentally “there is nothing wrong with it”.

If you need to install crm 4.0, it forces you to register it; however, Microsoft has long shut down the CRM 4.0 registration servers, so you are in a pickle. Daniel “King of soft” Cai found how to trick the database to think it is registered.

Turns out, it is governed by a simple database field, IsRegistered. Changed it from False to True, and CRM 4 instance will be up and running.

Tip #656: Dynamics CRM Online is truly global with multi-regional deployments

Pangea supercontinent If you have or are a multinational customer with the user base spread around the globe, CRM Online offers multiregional instances. It means you can keep your local data in the regional datacenter for performance, regulatory and corporate governance reasons.

Your tenant needs to be enabled for multi-region deployment and you need to request this feature by contacting your account manager or technical support.

The detailed description and step by step instructions are available but these are the key points:

  • Multi-regional deployments are not the same as multi-tenant deployments. In the latter, tenants are completely autonomous and, beside billing, do not share anything including user accounts.
  • Region is selected when a purchased instance is configured for the first time.
  • You will only see Your current region is: drop-down list if you have instances outside of your current region.
  • Edit, Copy and Reset operations only work within a region, i.e. it is not possible to copy database between regions.
  • Databases in different regions are completely independent. They will share your O365 Active Directory for authentication but otherwise are not linked in any way.

Tip #655: Defensive use of undocumented functions

Black boxThis is a sweet lesson for all the lovers of the undocumented functions. On odd occasion yours truly is guilty of being lazy and choosing a shortcut worthy spießrutenlaufen. From time to time those fits of laziness come back hard.

For a long time I’ve been using SaveAsCompleted internal function on a command bar for custom activities. For whatever reason the function is no longer present in CRM 2015 Update 1.1 and above. My short-term fix? Bury myself even deeper in the undocumented internal land:

if(typeof(SaveAsCompleted) == "function")
   SaveAsCompleted();
else if(typeof(Mscrm.CommandBarActions.saveAsCompleted) 
        == "function")
   Mscrm.CommandBarActions.saveAsCompleted();
else {
   // do whatever had to be done in a first place
}

Lesson: if you are going to use internal functions, at least do it defensively.

Tip #654: Use the XrmToolbox to copy templates

No later than 2 days after publishing Gayan’s tip on dangers of moving Word templates between deployments, Tanguy “The XRM Toolbox” Touzard stepped in and automated the process as part of the XrmToolbox.

Document template mover

It’s a first cut, a bit rough around the edges but you are more than welcome to jump in and fix whatever you don’t like at https://github.com/MscrmTools/MscrmTools.DocumentTemplatesMover.