Using the Lookup function to create reports on multiple DataSets

With Dynamics 365 Online, the greatest thing I miss compared to OnPremise is the relative limitation on what you can do with reports, as FetchXml is not so powerful as SQL. One option is to use the Data Export Service to get the data into a SQL database, and get the SQL queries out, but there is still a lot that can be done with FetchXml and Reporting Services. This post shows how to combine data from multiple datasets using the Lookup function.

My first rule of thumb when working out if a given SQL query can be implemented as a FetchXml query is, 'can the SQL query be written so that there is just one SELECT statement ?' If so, you've got a good chance of being to rewrite as FetchXml, but if not, you won't be able to do this in FetchXml. This test is useful, as it immediately eliminates Union queries, sub-queries and table expressions, which you can't do with FetchXml.

So, in many cases you can't do get the result that you want with one query, but Reporting Services allows you to define multiple Datasets, and hence multiple queries, in one report, and the Lookup function allows to connect the data across the Datasets.

For this post, I'll use an example I came across recently when the requirement was to get a count of records created per user, broken down by entity type. The simplified output I was looking for was:

User	Leads	Opportunities
David Jennaway	20	10
My Friend	15	8

This will need to get data from the systemuser, lead and the opportunity entities. It is possible to join these in one query, but not in a way that is useful, as you end up multiplying the opportunity and lead records.

Instead, we can create separate queries. Here I'm doing one for each entity, systemuser, lead and opportunity. The systemuser query will end up as the main source for the table, with lookups to the other queries to get the respective record counts. The 3 datasets and queries are:

dsUser:
<fetch > <entity name='systemuser' > <attribute name='systemuserid' /> <attribute name='fullname' /> </entity> </fetch>

dsLead:
<fetch aggregate='true'>
    <entity name='lead' >
       <attribute name='createdby' groupby='true' alias='createdby' />
       <attribute name='leadid' alias='lead_count' aggregate='countcolumn' />
    </entity>
</fetch>

dsOpportunity:
<fetch aggregate='true'>
    <entity name='opportunity' >
       <attribute name='createdby' groupby='true' alias='createdby' />
       <attribute name='opportunityid' alias='opportunity_count' aggregate='countcolumn' />
    </entity>
</fetch>

dsLead and dsOpportunity are both simple aggregate queries to get the respective record counts for each entity by user.

Then, to create the report, I add a table based on the dsUser dataset, with the Fullname in the first column. Then for the count of leads, I can use the following Lookup expression:

=Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!lead_count.Value, "dsLead")

Taking each of the parameters in turn:

• Fields!systemuserid.Value - this is the Guid for the systemuserid in the dsUser dataset. This value will be compared against...
• Fields!createdbyValue.Value - this is the Guid of the createdby in the dsLead dataset. Note that I use createdbyValue to get the Guid, as for lookup attributes the createdby will be the name
• Fields!lead_count.Value - this is the field in the dsLead dataset that I want to display
• "dsLead" - this is the name of the dataset that the Lookup works on

We can then do the same for the expression for the opportunity count:

=Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!opportunity_count.Value, "dsOpportunity")

And that's it to get the basic report. As a nicety, I can add a filter for the row visibility, so that it hides rows where there is no count across any of the datasets. The Lookup function returns Nothing if no record is found, so we can use the IsNothing function.

=IsNothing(Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!opportunity_count.Value, "dsOpportunity")) AndAlso IsNothing(Lookup(Fields!systemuserid.Value, Fields!createdbyValue.Value, Fields!lead_count.Value, "dsLead"))

We can keep adding extra datasets to count other entities, using the same approach. I don't have the patience to work out if there's a practical limit to the number of datasets we can use in one report. 

A couple of points to note:

• You can't use the Lookup function as a calculated field, which is slightly annoying, as I think it would be neater if this were possible. I expect this is due to how Reporting Services first processes the datasets, and will then render the results
• When testing in Visual Studio, you get prompted for credentials (or to use cached credentials)for each dataset in turn. I don't think you can do anything about this. Interestingly, it looks like Visual Studio caches the credentials per dataset, and they can be differ even if they use the same datasource. I once managed to have different datasets querying different CRM organisations, even though they were using the same datasource

I'm intending to post the full report up on GitHub in the next few days, once I've got that working properly

Data Migration Performance to Crm Online

I've recently been looking at the rate of data migration into Crm Online using SSIS, and how this can be optimised. I started with a baseline rate of 12 records per second, and have so far improved this to 430 records per second, all using one client machine.

The easiest way to migrate data into Crm Online is via a synchronous, single-threaded process that writes one record at a time, so this was the baseline. It soon became very clear that the performance bottleneck in this scenario is network latency - i.e. the round-trip time for the network packets to make a request to the Crm Online server, and to receive a response back.

So, the challenge was to improve on this, which can be done by addressing each aspect of the simple scenario - i.e.

1. Synchronous v. asynchronous calls


2. Single or multi-threaded - either within one process, or multiple concurrent processes


3. Sending more than one record at a time

So far, I've not tested asynchronous calls, primarily because SSIS is stream-based, and I can't see a way to write out synchronous error output if using an asynchronous pattern. It would be possible to write out asynchronous error information, but for now that would involve too much code rewrites. In general, though, I would expect use of an asynchronous pattern would give similar performance benefits to the multi-threaded approach, though it may be possible to multiply the benefits by combining the approaches.

Multiple threads
SSIS controls the threading behaviour of a package, so rather than try for a multi-threaded single process, I went for running several instances of the same package concurrently, which you can do with the dtexec tool. There are two main aspects to making this work:

1. You will need to be able to partition the source data, so that each package instance submits different records. For my tests, I had an integer identity column on the source data, and used the SQL modulo operator (%) to filter on the remainder from an integer division. For 10 concurrent packages, the where clause was 'WHERE id % 10 = ?' with the '?' replaced by a package variable.


2. The package will not be able to reference any files, either as data sources, destinations or log files, as SSIS will attempt to get exclusive access to the files. So, I used a SQL Server source, and wrote log information to SQL via the SSIS SQL Log Provider

I tested 10 concurrent packages, and this gave between a 7-fold and 9-fold performance improvement.

Submitting multiple records
The Crm API is primarily designed around modifying one record at a time, with a separate request per record. However, CRM 2011 introduced the facility to pass multiple records, using the RelatedEntities property of a 'parent' entity. This allows you to build (in memory) a RelatedEntityCollection of multiple records, then attach this to one record, and submit this as one request.

There are two limitations to this approach:

1. The entities have to be related via a relationship in CRM.


2. The same data operation has to apply to the parent record, and the records in the RelatedEntityCollection

Initially I'd hoped to use the systemuser entity as the parent entity, as there is necessarily a relationship between this entity and any user-owned entity. However, this wouldn't work with limitation 2, as I wanted to update the systemuser, but create the related entities, and this doesn't work.

Instead, I had to make schema changes. I created a new entity (e.g. exc_batchimport), and a 1-N relationship with each entity that I wanted to import. Each request would then create 1 exc_batchimport record, and a configurable number of related records.

I tried various batch sizes, and had success up to a batch size of 1000, but failures with a batch of 5000. My initial view is that the failures come from the number of records, and not the total data size, but I've not tested this extensively.

This approach also gave significant performance gains, though only when network latency was a main performance factor - i.e. it helped a lot when connecting to Crm Online, but gave no appreciable benefit (and in some case, worse performace) when connecting to an On Premise CRM server. Most of the benefit came with a batch size of 10 records, though performance did continue to improve slightly if increasing the batch sizes up to 1000 records.

Performance results
I did the tests running on Windows 2008 Server with moderate capacity:

• A virtual server running via Hyper-V


• One processor core allocated to the server, running at 2GHz


• 4 GB of memory


• Server was running in a hosted environment in England, connecting to the EMEA Crm Online Data Centre

The tests were done writing 100000 new records to a custom entity, writing 2 text fields, and integer field, and an option set, and allowing CRM to generate the primary key values.

Concurrent packages	Batch Size	Time	Records / sec
1	n/a	832	12
10	n/a	1094	91
10	10	276	362
10	100	267	374
10	1000	233	429

Conclusions
The main performance issue with modifying multiple records with Crm Online relates to network latency. I've successfully tested 2 complementary approaches which give a combined 35-fold speed improvement, and it may also be possible to gain further improvements with asynchronous calls.

The performance figures were for a custom entity. I'm intending to do further tests with the customer entities (account and contact), and the activity entities, as each of these require more SQL updates than a custom entity.

Using WSDL Proxies with CRM Online. It's different outside of North America

I've been spending more time than I'd like using WSDL Proxies with CRM Online (i.e. when I can't use the .Net 4.0 OrganizationServiceProxy class). I'll write up some more about this soon, but this is a quick post about a specific issue with connecting to CRM Online for an organisation in EMEA, rather than North America, which I've not found documented anywhere.

I was basing my code on the wsdlbasedproxies example in the CRM 2011 SDK. Once you find it, this code is reasonably well documented.

However, when testing it, I could connect to the IDiscoveryService without problems, but continually got the error 'An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail' when connecting to the IOrganizationService.

Ultimately the issue was with the AppliesTo constant. The setup.txt instructions tell you to set this based on data in the element from the Discovery.svc wsdl. This gave me "urn:crmemea:dynamics.com" when connecting to an Online organisation in EMEA, which worked for the IDiscoveryService, but not IOrganizationService.

** Update 2012-07-11. IOrganizationService now also uses urn:crmemea:dynamics.com **

So, this needed a few minor code changes to the code in Online\program.cs, as you need different tokens for each service. For EMEA, I used the following:

private const string AppliesToDiscovery = "urn:crmemea:dynamics.com";
private const string AppliesTo = "urn:crmemea:dynamics.com";

static void Main(string[] args)
{
//Authenticate the user
SecurityToken tokenDiscovery = Authenticate(UserName, UserPassword, AppliesToDiscovery, Policy, IssuerUri);
SecurityToken token = Authenticate(UserName, UserPassword, AppliesTo, Policy, IssuerUri);
//Execute the sample
string serviceUrl = DiscoverOrganizationUrl(tokenDiscovery, OrganizationUniqueName, DiscoveryServiceUrl);
ExecuteWhoAmI(token, serviceUrl);
}


Update July 2012: AppliesTo for EMEA should now be urn:crmemea.dynamics.com. See http://mscrmuk.blogspot.co.uk/2012/07/using-wsdl-proxies-with-crmonline.html