Use reference tables for previews in Power BI

In my quest to provide customers with awesome reports I’ve been looking at why the performance in Power BI can be so slow at times. As I pointed out in earlier posts the OData.Feed can be quite the resource hog depending on the endpoint, so I’ve started to fall back on Json.Document to get much better preview times (but not without it’s drawbacks, like record count, connected tables and option set labels).

Just because I’m kinda new to this I like to perform a lot of the logic using M in the query editor, then I sew it all together with relationships and measures inside the reports I build. I noticed that the previews kept getting slower the more I logic I added, so get the ultimate magic tool and looked at what was happening.

First step: Json.Document(Web.Contents(“https://<org>.api.crm4.dynamics.com/api/data/v8.2/opportunities”))

OK, so it makes a request to Dynamics and retrieves the data in 3.435 seconds. That’s not fantastic, but it’s not bad.

Second step: = Source[value]

OK, no requests made. That’s what I would expect from it

Third step: = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

Still no requests made, that’s great

Fourth step: = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"actualclosedate", "actualvalue", "budgetamount", "estimatedclosedate", "estimatedvalue", "name", "opportunityid", "pzl_countryregion", "statuscode"}, {"Column1.actualclosedate", "Column1.actualvalue", "Column1.budgetamount", "Column1.estimatedclosedate", "Column1.estimatedvalue", "Column1.name", "Column1.opportunityid", "Column1.pzl_countryregion", "Column1.statuscode"})

Guess what? Still no requests.

Fifth step: = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1.estimatedclosedate", type date}})

No requests. Great, so we only make 1 request per step, as long as we don’t merge in more columns? Not quite.

Refresh preview

Wait what? 5 requests, that’s 1 request per step. That makes the loading time increase from the original 3+ seconds into well over 15 seconds. I don’t have the answer for why it makes a new request for each and every step, but let me show you the consequence of this when you use OData.Feed instead of Json.Document

These are the steps taken:

1. = OData.Feed(https://<org>.api.crm4.dynamics.com/api/data/v8.2/opportunities)
2. = Table.SelectColumns(Source,{"actualclosedate", "actualvalue", "budgetamount", "createdon", "estimatedclosedate", "estimatedvalue", "name", "new_calculatedrevenue", "new_product", "opportunityid", "pzl_countryregion"})
3. = Table.TransformColumnTypes(#"Removed Other Columns",{{"createdon", type datetime}})

Now that should be enough to make you think twice before reporting on Dynamics data in Power BI. As you can see it’s the metadata documents which takes the longest, but for some reason there are 4 requests to the “all opportunities” endpoint and the metadata endpoint, while there are 3 requests to the opportunity endpoint with reduced number of columns (fields). Also, there is one top=1000 request which completes before the last two. This tells me that when you refresh the data, the requests are made in sequence for each step, but the steps are performed in parallel. It almost smells like premature optimization.

So let’s break this down and do it step by step.

Step 1

OK, great, one request for the opportunities and one for the metadata, finally one to get the top 1000 records for the preview.

Step 2

Now we see that it makes the same three requests as it did in the previous step, but it also performs another set of requests against “all opportunities” and metadata before it performs a request against opportunities with reduced number of columns (fields).

Step 3

Finally, it does all the requests from previous steps, but it also adds another duplicate for this current step (which only converts the type, and doesn’t require collecting more data from the source system).

The magic workaround

Just by pure luck, when I tested this originally I got 4 duplicates of the “all opportunities”+select requests, and I couldn’t figure out where the 4th duplicate came in. Turns out, because I made a reference from this opportunity query into a new query, and the data is refreshed in all referenced queries as well as the parent(s).

That made me test one final thing. I created a new reference query after I had done all my logic in the main query.

Then I refreshed the data while in the reference query.

Step 1: = Query1

Voilà! Only one set of requests, even though it’s technically the fourth step.

This means I just saved myself many duplicate requests, and the data loads much faster than for each subsequent step. This was only for 3 steps, imagine how much this will matter if you have 10, or 20 steps in your queries.

The wrap up

So in summary, when you refresh the preview for a query in a Power BI, all the requests are are repeated and added on for each step you have in that query. It’s an exponential growth of data gathering, and can make everything slow as nothing else.

Creating new queries with references removes the need to duplicate all the requests, and you can keep working with and massaging the data without having to worry about minutes of data reloading.

How to retrieve OData v4 fast in Power BI

I recently blogged about how Power BI has a bug which ignores the select statements when querying an OData v4 endpoint. Even though the columns would include any data, it still bugged me so much that I wanted to find a way around it.

Turns out that’s much easier than you would expect.

I recently explored some of the different data loading capabilities in PBI, and it turns out that there’s a Json.Document function which allows you to handle OData at an even lower level.

So what I did was take my original uri and query string: https://<org>.api.crm4.dynamics.com/api/data/v8.2/opportunities?$select=name

Then I put it into a Json.Document(Web.Contents())

And voila, this is the result

Navigating into that list, you can then right click the column and select “To Table”. This will present you with a table of records ready to be exploded:

So, time to do a little speed testing

Using: = OData.Feed("https://<org>.api.crm4.dynamics.com/api/data/v8.2/opportunities?$select=name")

Using: = Json.Document(Web.Contents("https://<org>.api.crm4.dynamics.com/api/data/v8.2/opportunities?$select=name"))

Enough said?

Dynamic option sets in PowerBI

I recently had a requirement from a customer who needed to filter reports based on global option sets. The kicker for this requirement was that the option set would change rapidly for some time (don’t ask), so they needed a dynamic way to retrieve the labels and use them in a report.
Now, there are several solutions out there to retrieve the option set data, and I got some tips from the talented Ulrik “Why is he not an MVP yet” Carlsson. While his solutions work, I don’t want a static transformation in PBI, I don’t want a custom entity to store information already in the system, and I don’t want to use fetchxml as a source. Are those unreasonable demands? Maybe. Is that going to stop me? Never.
So to start with, I checked the global option set definitions for my organization, found from the following URL
https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions
Sure enough I found all the option sets in the system, so I tried to add that url as a source in PowerBI to see if I can get the option set values. After waiting some time for it to load I can see that there are no lists or subsets to load that gives me anything useful.

So I try to load up the direct url for the option set in question, using the metadata id of the option set. The url looks like this:
https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(bd3519cf-3d67-43f2-8a94-d6849872f615)
While this does give me a result, it’s not anything I can use.

So I load the URL into chrome to look at the data. Here I can clearly see all of the option sets, complete with locales and values. That means that the data is available, but I am not getting it in the format I want.

Alternative 1, parse the content as a JSON document

Instead of using an OData.Feed to load the data, I can just take the specific optionset metadata and load it into a Json.Document instead. To do this, create a new blank query, then paste the following value
= Json.Document(Web.Contents("https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(bd3519cf-3d67-43f2-8a94-d6849872f615)
This will provide you with “Options” as an attribute which contains a list of records. You can then click on the List to navigate to it.

You now get a list of records, where you can right click the column and select “To Table”. This will convert the list into a table, and from here you can expand the columns. I chose to expand Value and Label, as that will give me the option set int value and the label string. The label is a record itself, so you have to expand it to get more options. The choices here is LocalizedLabels and UserLocalizedLabel. The difference being that the LocalizedLabels contains all the labels for all the localizations defined in your system, while the UserLocalizedLabel contains only the label for the user’s language.

UserLocalizedLabel

Expanding the UserLocalizedLabel gives us a new record, which we then can expand to Label, LanguageCode, and other additional metadata attributes. I’ve chosen to expand the first two, and we now have a useable table with both int values as well as label strings in the user’s language

You can now rename the columns and use them to join with the primary records in your PBI report.

LocalizedLabels

Expanding the LocalizedLabels will give us lists of labels for each value. You can’t expand a list to new columns straight away, so you will have to expand the table with new rows. If you have additional languages, which is probably why you’re doing this, then you will duplicate the integer values for all option set values that have several language definitions.
In my example I’ve just duplicated the table because I don’t have multiple languages enabled, so don’t worry about duplicate values, it’s on purpose.

So the next step is to right click the Column1.Value column, and select group by. Give the new column a sensible name (I named mine labels), then choose operation “Max” and select the label column as the column. This is just to get a basis for the grouping.
Now, in the next stage, the formula should look somewhat like this:
= Table.Group(#"Appended Query", {"Column1.Value"}, {{"Labels", each List.Max([Column1.Label.LocalizedLabels.Label]), type text}})
You need to change it so it looks like this instead:
= Table.Group(#"Appended Query", {"Column1.Value"}, {{"Labels", each Text.Combine([Column1.Label.LocalizedLabels.Label], ","), type text}})
The result will look something like this:

Now, right click the Labels column and select “Split Columns” –> “By delimiter”. Choose to split each occurrence. The resulting table now looks like this:

You can now give the columns sensible names and use them in your PBI report.

Alternative 2, add query options to get the list of labels

The other solution to retrieve the odata feed is to add additional columns. As we saw earlier the OData.Feed(… just gave us metadata about the entity, but none of the options. This is because the options, which includes data, is excluded by default. To include them in the query simply change the source definition from this:
= OData.Feed(https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(4565bea1-7c02-e811-8113-5065f38bb391))
To this:
= OData.Feed(https://<org>.crm4.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(4565bea1-7c02-e811-8113-5065f38bb391), null, [MoreColumns = true])
The “null” is for additional headers, while the second is a PBI record which states that MoreColumns should be true (default false). All the available options are specified in the following article https://msdn.microsoft.com/en-us/library/mt260868.aspx
The result of this is the following screen.

We now have an attribute named “More Columns”. Clicking on that will navigate into the record, which is a list of records. Right click the column and choose “To Table”. You will now have a table where you can expand the columns just like specified in alternative 1.

Wrap up

Getting option set labels isn’t obvious when you’re creating reports in Power BI thanks to how attributes and attribute metadata is defined in Dynamics 365. Luckily there are ways to retrieve additional data in a dynamic way which allows us to utilize the system in awesome ways.
Until next time!

Dynamics 365 App for Outlook stops working after URL change

I encountered an error where the Dynamics 365 App for Outlook stopped working for a customer after we changed the URL for his instance.

As you might already know, as a D365 administrator you can go to the Dynamics Admin Portal to change the organization settings, and one of those settings is the organization URL. When we did this for a customer who was going live with a Dynamics 365 v9 organization the App for Outlook stopped working.

To fix this simply go to Settings –> Dynamics 365 App for Outlook

Even thought the app isn’t working the status will say “Added to Outlook”. To fix this simply select the users with the app, then click the “ADD APP TO OUTLOOK” (or “ADD APP FOR ALL ELLIGIBLE USERS”) button to re-publish the app to the users. After a few minutes the app was, again, available and working for all users.

Using related entities in PowerApps with MSDYN365 as a data source

Getting related records to work in PowerApps and MSDYN365

PowerApps is a relatively new feature to the Office365 portfolio which allows advanced users to create their own apps which can be used on mobile (and desktop) platforms to perform daily tasks. It is a great tool for creating those small functions you usually have to book a developer for a few hours/days, and is ready for authentication, authorization, and distribution right out of the box.

PowerApps comes with the ability to connect to a wide array of sources to collect and manipulate data, and today I'm going to demonstrate how to connect with MSDYN365:CRM and how to use connect with related records.

Connecting to MSDYN365:CRM

Now this is the easiest part. To connect with MSDYN365 in your powerapp simply go to the 'Content' tab and select Data Sources, and you'll be presented with the option to add new connections and data sources. Here it will probably show you something like this (I have a completely new trial organization with only O365 E3 and MSDYN365 Plan1:

What I'm doing is connecting to the MSDYN365 suggestion, and I'm going to add 3 tables (entities) to my data source list; Accounts, Contacts and Users (systemusers).
Once that's done I'm going to add a new screen with the browse template. This will give me the option to search and display records from the chosen data source.
Now, from the right hand side, choose the advanced tab and select your Gallery component from the dropdown list. Next through the Home tab on the top toolbar select "Items" from the property dropdown box. We're going to do some drastic changes to the formula used to collect data, but first a little insight.

When a function is run/validated in powerapps it returns the result set to the parent function (or context, whatever you'd like to call it). This means that we can build an advanced data source or query by nesting functions inside each other, and end up with a pretty good set of data to play with.

So to get the data I want in my gallery browser I'm going to use the following formula:
SortByColumns(Search(AddColumns(Accounts, "User", LookUp(Users, systemuserid = _ownerid_value), "Contact", LookUp(Contacts, contactid = _primarycontactid_value)), TextSearchBox1.Text, "accountnumber", "name"), "name", If(SortDescending1, Descending, Ascending))

Now this might seem a little frightening at first, but I'll go through it step by step and you'll see how I thought through it. Remember the part about nesting functions that I mentioned above? Well that's how I start building a function, by nesting backwards.

1. I want a collection of accounts, but I also want to add the primary contact and the account owner (I'm not checking for teams, I'm just assuming that all accounts are owned by users. This is just a quick demo). That means I'll have to use a function to join those sources. A quick search online says that I can use AddColumns to do joins in a PowerApp formula, and thanks to the built in intellisense it's easy to create the formula required.
   AddColumns(<data source>, <table alias>, LookUp(<data source>, <condition>))
   This will nest the related table underneath the table alias (or namespace if you will), and you can add additional tables by just specifying new column aliases and lookup expressions. To get the ones I needed I filled in the following:
   AddColumns(Accounts, "User", LookUp(Users, systemuserid = _ownerid_value), "Contact", LookUp(Contacts, contactid = _primarycontactid_value))
   This means that I've nested the Users entity underneath the "User" column alias, and the Contacts entity underneath the "Contact" column alias.
2. I need to be able to search for these records, I don't simply want all the accounts listed at all times. Search is a function designed for this, but I could also use Filter if it was a set query I wanted to use. Now I know that the AddColumns(...) functon I used will return the result set of that function, so I could potentially search through the related records as well. I'm going to do it a lot simpler so I'll want to search through the account number and name field on account. The syntax is :
   Search(<data source>, <Search text>, <Column to search in>, <Column2 to search in>, ...)
   The data source in our case will be our previous AddColumns formula. The search text we'll get from the default search box, TextSearchBox1.Text, and the columns we want are "accountnumber" and "name", which makes the formula look like this:
   Search(AddColumns(.....), TextSearchBox1.Text, "accountnumber", "name")
3. Lastly I'd like to sort the result, so I'm going to add a SortByColumns function just to get a predictive list instead of a random one. This starts just the same as our previous function, the result of everything we've done so far has been returned, and we can order by the columns we want. The syntax looks like this:
   SortByColumns(<data source>, <column name>, If(<logical test>, <true value>, <false value>))
   We want to use the data source we've built so far, and we want to use the "name" column of the account. The logical test will be the default sorting button, with true as Descending and false as Ascending (false is the default value). We end up with the following formula:
   SortByColumns(Search(...), "name", If(SortDescending1, Descending, Ascending))

Now we've got a search gallery were we can search for accounts and get the related primary contact and the owning user data values. We can use that to further style our result list so it will look something like this:

Customizing the details form

So now that we have a decent-ish looking gallery we want to do some modifications to our details and edit forms. There isn't really any magic to this, you simply add and remove the data fields that you want. There are however two things I would like to point out so that you don't walk into the sweet honey traps.

First of all, we want to set a data source and item that PowerApps has a relation to and can work with. If we choose the same data source as we used in the gallery then we can certainly we the data and place it on the form, but updating the record or creating a new one could prove a bigger challenge. So what we do here is that for the data source, we set the "Accounts" entity table. The Items property is a bit more special though, and here we have two options (I'm going for option 2):

1. We use the item from the gallery, ie. BrowseGallery1.Selected. This will work perfectly fine as long as you don't try to update any other fields than the main account fields. However, you will get an error in the PowerApp user interface because the data source chosen for the details form is not the same data source as you have in your gallery. The upside is that you don't have to do a new query to get the account data, but on the downside you will have to live with a warning sign that comes with not following the best practices.
2. We use the accountid from the gallery to look up the specific account from the account data source, ie we use the following formula:
   LookUp(Accounts, accountid = BrowseGallery1.Selected.accountid)
   The positive thing about this is that we're now 100% compliant with the best practices, but we now don't have direct access to the sub-components, and we're doing an additional query to CRM for a lookup. Luckily, this lookup is very cheap because we're getting 1 entity based on the Guid, and I'll be picking this one just so we can demonstrate how to do a lookup inside a field.

So now that we've got the data available we can start adding the fields to the forms. If we want to have some fields from related records we can simply use the LookUp function to get them. For example, if I want the full name of the primary contact I can use this formula in a data form:

LookUp(Contacts, contactid = _primarycontactid_value).fullname

So now that I've done a very small amount of customization my detail form looks like this.

Customizing the details form and changing the primary contact

So now we're ready to do some customization to the edit form. I've gone ahead and added the same data source and item as we did on the detail form, and I've added the address fields just so we have something to play with. The results so far looks like this:

Now, as a power user I would love to be able to edit the primary contact of the customer without having to specify a Guid, I want a search window so that I can find the right one and add it to the account I'm looking at. The first thing I'll do is to add a new browse gallery screen, and then I add a new button on the edit form which navigates to the new screen:

Now, on the contactselector screen I want to show all active contacts. I'll start off by creating the same kind of formula as we did in the beginning of this blog post, but this time I'm just searching through the Contacts data source. My formula now looks like this:

SortByColumns(Search(Filter(Contacts, statecode = 0), TextSearchBox2.Text, "fullname"), "fullname", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

Now the right arrow on each result will usually try to navigate to a details window, but we don't have that window here and we don't want to see the details either, we just want to add this contact as the primary contact to the account. So what we're going to do is to use the "patch" function in PowerApps. The Patch option allows you to edit items on an existing record, while the update function replaces an old record with a new one. Since we just want to update the contact I'm going ahead with the patch method. The syntax for the patch function is like this:

Patch(<data source>, LookUp(...), { field1: value1, field2: value2})

So we want to update the account we have selected in previous windows. In additon, we want to update the primary contact Id for that account. That means we have to do a formula like so:

Patch(Accounts, LookUp(Accounts, accountid = BrowseGallery1.Selected.accountid), { _primarycontactid_value: ThisItem.contactid})

This is starting to look pretty nice, but we also want to navigate away from this form after updating. To do that we simply add a semicolon after the Patch function, then call the Back() function to go to the previous window. What we end up with is a window that looks something like this:

So now we have a complete app that can search for Accounts, display related record information, and finally edit the fields and set a new primary contact. This should work just as well for all related records, but now you have a pretty good example to start out with.

Wrap-up

So as we've seen not everything in PowerApps is just point-and-click, but you don't have to be an advanced developer to find out this stuff on your own. The documentation for powerapps is decent, so you'll find plenty of examples you can experiment with on your own.

What I find very exciting is that not only can I create these simple apps very fast, I can also distribute them to other users in my organization so that it will be available from the https://home.dynamics.com menu.

I've added a copy of the powerapp to to my public google drive so you can download it and use it as you want (TestAccounts.msapp). 

All you have to do is to go to the "Content" tab and add the data sources, you'll need "Accounts", "Contacts" and "Users", and then you're ready to go.

Sending email to an alternate / secondary email address

Small plugin to send email to an alternate / secondary email address

Those who have worked with Dynamics365:CRM for a while has undoubtedly encounter this issue, which is that whatever address you enter as a recipient the system will resolve the record and substitute the address with the primary email address of the record.

There are several different ways of working around this issue, like creating leads that can be merged into existing contacts, duplicate contacts with links, etc. but I have created a small plugin which allows you to send an email to the address you want.

All you have to do is to compile the following code and register it as a synchronous, pre-operation plugin, and add a pre-entity image with the "to" field included. The secret here is to register it on the "send" message for the email entity.

Just add the microsoft.crmsdk from nuget to your project, and you'll have all the libraries you need to compile this little thing.

public class SendToSafeEmail : IPlugin

{

    public void Execute(IServiceProvider sp)

    {

        var context = (IPluginExecutionContext)sp.GetService(typeof(IPluginExecutionContext));

        var factory = (IOrganizationServiceFactory)sp.GetService(typeof(IOrganizationServiceFactory));

        var service = factory.CreateOrganizationService(context.UserId);

        Entity email = context.PreEntityImages.Values.First();

        var recipient = ((EntityCollection)email.Attributes["to"]).Entities.FirstOrDefault<Entity>();

        var to = (EntityReference)recipient.Attributes["partyid"];

        var contact = service.Retrieve("contact", to.Id, new ColumnSet("emailaddress3"));

        ((EntityCollection)email.Attributes["to"]).Entities[0].Attributes["addressused"] = contact.Attributes["emailaddress3"];

        service.Update(email);

    }

}

As you can see this isn't particularly advanced, and I would like to start with pointing out that I've removed exception handling and tracing for illustration purposes, I'm usually a lot more tidy (it's true!).

First off we retrieve the usual suspects, the execution context and the orgservice factory. Then we generate a new org-service connection based on the user ID of the user which triggered the plugin. Next up we retrieve the email from the pre entity images. This imlies that this is a pre-operation, synchronous plugin, and we need to add a pre-entity image when we register the plugin.

Next up we get the recipient from the "to" list. My code example does not take into account several recipients, it doesn't do any exception handling, and it's hard-coded for contacts. You'll have to make it work for your purpose on your own :)

The contact is retrieved from the system, and finally we do the following:

From the to recipient list, we update the "addressused" field on the first one, to the "emailaddress3" of the contact collected.

Next we update the entity back into the system, and continue the execution context.

That's all it takes, and some of your own logic of course.

A few thought on this solution before I wrap up:

It seems like a small, simple plugin for sending to alternate email addresses, but notice how we're hooking up to the "send" message for the email entity? That means that this plugin will trigger for all emails sent from the system. This doesn't need to have a large impact on your system, because there are a lot of optimizations in place in MSDYN365, but every line of code that has to execute causes a tiny bit of extra load on the system, and then multiply it by the number of emails sent from the system.

Microsoft CRM + Azure Service Bus, part 4 (two-way relaying and using Azure Event Hubs)

Integrating Microsoft Dynamics CRM with Microsoft Azure Service Bus, using queues, topics, relays and the Event Hub

In the previous posts we've looked at using endpoints in CRM, how we can create a simply workflow to push the Activity Context to the Azure Service Bus, using queues and creating a simple one-way listener. In this post we'll focus on extending the code to enable two-way relaying, which will allow us to return data back to CRM. We'll also look at how we can integrate CRM with Azure Event Hubs, a service based on ASB which allows for a lot of throughput.

Click here for part1

Click here for part2

Click here for part3

Allow return value in the CRM Workflow step

The first thing we'll do is to write some more logic for our workflow, to enable us to use a return value. The relay service only returns a string, which means that we could easily push JSON or base64 through the bus to have even more meaningful feedback from the service. Be sure to remember the maximum message size for your service bus, and the timeout values. I do not recommend keeping a connection for a long time to transfer end-to-end data, but there are loads of scenarios where you'd like to get something back.

[Output("Return value")]
public OutArgument<string> ReturnValue { get; set; }

OK, so what we've done here is to add an output variable to our workflow step. This allows us to take the return value and use it inside our workflow in later steps.

protected override void Execute(CodeActivityContext executionContext) 
{
    var context = executionContext.GetExtension<IWorkflowContext>();
    var serviceEndpointNotifySvc = executionContext
        .GetExtension<IServiceEndpointNotificationService>();
    var result = serviceEndpointNotifySvc.Execute(
        ServiceEndpoint.Get(executionContext), context
    );
    ReturnValue.Set(executionContext, result);
}

The code looks mostly the same as it did in part2 of this series, with a couple of additions. Firstly, we assign the return value from the notification service's Execute method to a variable "result". Next we set the value of the new output variable "ReturnValue" to the result, using the execution context of the workflow.
Now just build and deploy the updated workflow, and we'll head into MSCRM. Navigate to the Settings -> Processes area, and open up the workflow created earlier. Deactivate the workflow to enable editing, and then add an update step after the custom workflow step.
I'm going to update the Description field on the Account to the ReturnValue from our custom workflow step.

Finally, go into the plugin registration tool to update the relay service we added in part3. Make sure it's changed to a two-way relay, and eventually update the name and path if you want that. Just make sure to update the same values inside the service.

Writing a two-way relay service

To enable two way communication we first have to change our service behavior class. We have to change the interface it inherits from and add a return value.

[ServiceBehavior]
public class RemoteService : ITwoWayServiceEndpointPlugin
{
    public string Execute(RemoteExecutionContext c)
    {
        Console.WriteLine(
            $"Entity: {c.PrimaryEntityName}, id: {c.PrimaryEntityId}");
        return "Message processed in two-way listener";
    }
}

As you can see we're now inheriting from the ITwoWayServiceEndpointPlugin interface, modified the Execute method to expect a return value, and we added a return statement after printing out to the console. This means that whenever our relay service is triggered it will print out the message as before, but will also return our magic string.

The only other change we have to make is to change the typeof implementation. Earlier we specified IServiceEndpointPlugin, so we have to change it to ITwoWayServiceEndpointPlugin. I've done a little dirty hack, because I've been changing back and forth between relay types while testing, so mine looks like this:

sh

    .AddServiceEndpoint(

        typeof(RemoteService).GetInterfaces().First(), 

        new WS2007HttpRelayBinding(), 

        AppSettings["SBusEndpoint"]

    ).Behaviors.Add(tcEndpointBehavior);

sh.Open();
Console.WriteLine("TwoWay-listener, press ENTER to close");
Console.ReadLine();

sh.Close();

While this may seem like reflection and a good idea, it will only work until you've built yourself a framework to reuse across different projects/customers, and all of a sudden you're implementing several interfaces and everything stops working. For this demo, it's OK, but I'd rather specify the interface type in production code.

Testing two-way relaying

Now that we've got that out of the way, it's time to test our new implementation. Start your new and improved service, and go to CRM and trigger the plugin. If everything goes as planned you'll be looking at the following window for your relay service

 And if we go into CRM and check out the description on our contact entity we'll see the following

So that's all it takes to have two-way communication working in a relay service. We've got CRM on one side, Azure Service Bus as the communication channel, and a simple console project on the other side.

What's a REST listener?

One option I haven't mentioned so far is the REST listener which can be specified in the plugin registration tool. This is simply a two-way relay which uses REST endpoints instead of .Net binaries. This would allow you to create and run a relay service in Node.js, apache tomcat, powershell, IIS, or whichever web server you'd want. Just to trigger all the popular buzwords, this can enable you to use MSCRM, Azure Service Bus, and deploy node.js relay services in docker containers.

Azure Event Hubs

Azure Event Hubs is a special kind of service bus which is designed to handle huge amounts of messages, we're talking millions of messages per second. It's probably not what you're looking at for general integration purposes, but there are several scenarios where it could benefit your company in a big way.
The first thing I thought of was using this for auditing. Just write a simple workflow or plugin which pushes any creates, updates and deletes as messages to the event hub. Then you can use stream analytics or some other technology to populate an auditing system with actions performed by your users and integration systems. Anyone who's used the out-of-the-box auditing functions in MSCRM knows that processing the information is tedious, at best, and more often than not close to impossible to get any useful data from. But if you start pushing it into an external auditing system based on Azure services then you could use clever stuff like temporal tables to design a robust, maintainable system.

The second thing I thought of was predictive analysis. Pushing raw data to the event hub, which allows for transfer to an Azure Data Warehouse for real-time data crunching and you have a great additional source of data that can be used for predictive analysis or (buzzword warning) machine learning.

There are probably a lot of cool things you can do with this, but I won't elaborate all my ideas in this blog post. What I want to stress is the price tag. It is incredibly cheap compared to legacy systems based on clusters of servers running different messages queues with some expensive bus technology on top. And the performance is great, no matter which size you pick. It doesn't matter if you're running hundres, thousands, or hundreds of millions of messages per day, the performance is always great but there's no entry level cost, the price scales with usage.


That's all for this blog series (at least for now). I might come back to visit later on when I've done some more in-the-field deployments with the ASB.

Microsoft CRM + Azure Service Bus, part 3 (creating a relay service and endpoint)

Integrating Microsoft Dynamics CRM with Microsoft Azure Service Bus, using queues, topics, relays and the Event Hub

In this, third part of my blog series on using the Azure Service Bus capabilities I'm going to demonstrate how to set up a relay service and add the relay namespace as an endpoint in MSCRM.

Relaying allows us to have active listeners who can either just accept messages or accept and reply to them. This allows for business critical systems like ticket reservation and receipts to ensure we're working on updated and valid data.

Click here for part1
Click here for part2
Click here for part4

What is a relay service?

A relay service works by using the Service Bus like a kind of tunnel, in which the relay is an active listener at the other end. Unlike queues and topics, where the message is "dropped off", using a one-way or two-way (or REST) relay requires that somebody pick up the message immediately. With one-way the sender is happy as long as somebody accepts it, while in a two-way scenario the receiver has to return a value. In this post we'll start out with a one-way relay, but in the next one we'll look at how we can extend that into a two-way relay.

Creating a one-way relay

First off we write a service behavior class. This represents the code we're running whenever a message is received. I'm just going to write out something to the console.

[ServiceBehavior]
public class RemoteService : IServiceEndpointPlugin
{
    public void Execute(RemoteExecutionContext c)
    {
        Console.WriteLine(
            $"Entity: {c.PrimaryEntityName}, id: {c.PrimaryEntityId}");
    }
}

So nothing magical happening here. It's an extension of the IServiceEndpointPlugin, which writes the entity name and id to the console in it's execute method.
Next up we go to the main method and define a servicehost variable and a new endpoint behavior.

var sh = new ServiceHost(typeof(RemoteService));

var tcEndpointBehavior = new TransportClientEndpointBehavior(

    TokenProvider.CreateSharedAccessSignatureTokenProvider(

        AppSettings["SharedAccessKeyName"],

        AppSettings["SharedAccessKey"]

    )

);

OK, so here we've got ourselves a new servicehost variable, which we'll use to add a new service endpoint, with our newly created RemoteService service behavior as the type. Then there's the endpoint behavior. We create a new TransportClientEndpointBehavior, with a shared access signature as the token provider.
NB! In the SDK and the MSDN article this is specified as "Shared Secret Token Provider", but that's ACS and is no longer supported in Azure. You have to use Shared Access Signature (SAS) for authentication or it won't work.

sh
    .AddServiceEndpoint(
        typeof(IServiceEndpointPlugin), 
        new WS2007HttpRelayBinding(), 
        AppSettings["SBusEndpoint"]
    ).Behaviors.Add(tcEndpointBehavior);

Here we add a service endpoint to the host. The type is IServiceEndpointPlugin, which is what the CRM async service sends to the Service Bus. We use WS2007HttpRelayBinding to match the source system, and we collect the endpoint from the app.config (formatted like the following: https://yournamespace.servicebus.windows.net/yourpath/ ).
What's important to note here is that the path specified should not be the same as an existing path. If you have a queue or topic with the same path specified, it will be overridden and you cannot recreate it in the Azure Portal afterwards. This also means that it's up to you which path you want to use for the relay, which also means you can modify it dynamically using the web.config or in advanced integration scnearios.
Finally we add the endpoint behavior we created ealier.

sh.Open();
Console.WriteLine("OneWay-listener, press ENTER to close");
Console.ReadLine();
Close();

Finally, we open up the service host connection, which makes it start listening to new messages. This means we're ready to set up the service in the plugin manager and start sending messages.

Switching to relaying in MSCRM

First off we need to create a new shared access policy in Azure. It's the same as we did in part1, except that this time we create it on the bus itself instead of on a queue.
Then we head over to the plugin registration tool, connect to our organization and add a new service endpoint. This time it'll look kind of like this when it's filled in:

What's important to note here is the namespace address and path. The namespace address should be the complete path, and the path should just be a forward slash (the tool does not accept blank values). If you specify the path in the path box then it won't be able to find and connect to your relay service (that took me a while to figure out). In addition, this is an https endpoint, not an sb endpoint
Next up we go into CRM to edit our workflow (for more info see part2 of this blog series). The only thing we need to do here is deactivate and update the service endpoint. Then save and activate it again. I've kept the workflow running synchronously, just to be able to verify that it works as expected.
Now, just to demonstrate how it looks if you've specified the wrong endpoint, or if the relay isn't running, here's the error message you get. It is the same error you'll get if you specify the path in the path box instead of in the namespace box.

Now, for the fun part instead. Start your relay service and wait for the host to be ready. Then go into CRM and trigger the plugin. If you've done everything right then you'll see a command window looking something like this

And that means we've successfully posted a message from our MSCRM system, through our azure service bus and out of the relay service. This example isn't very exciting, but just think of the possibilities you get if you put this service out into Azure (or on a web server if you're still into that whole old school infrastructure stuff ;) ).

That's it for this blog post. In the next and (for now) final post in this series we'll look at how we can extend this into a two-way relay, as well as integrating with Azure Event Hub which is a service based on the ASB.