Dynamics CRM 2016 generates massive Excel files for marketing lists

Question

Background
One of our customers sends out monthly information together with an invoice to a subset of their contacts. These contacts are first added to a marketing list, and the contacts' names, address information and OCR number is then extracted. When exporting these members to an Excel file (so it can be sent for printing), our customer has had massive problems to even generate the file. Once we succeeded, we realized that the generated Excel file is about 550 MB in size, for about 40k rows and less than 10 columns of name and address information. After a while, we figured out that Dynamics CRM generated an additional 160 hidden columns, containing no data. Deleting these columns brought the file size down to a more reasonable 4 MB. These columns alternated between being named "processid" and "processts".

This problem does not occur when exporting e.g. invoices from advanced find, so I'm curious to know if Dynamics CRM does something special because we're running a plugin at export time in this case.

Details
The flow our customer uses, in more detail, is the following:

1. A campaign is created. This campaign gets a sequence number, a unique ID that we generate.
2. A marketing list is created, and connected to the campaign.
3. Members are added to the marketing list. These members also have a sequence number, a unique ID.
4. Switch from the marketing list form to Members of marketing list.
5. Selecting the "Export view", a custom view that we've created. In my minimal repro, this view contains only the complete name of the contact plus the "generated OCR" field.
6. A plugin, registered on post contact retrieve multiple, is triggered and creates an OCR number for each member of the marketing list, based on their sequence number plus the sequence number of the campaign. This is then added to the "generated OCR" field.
7. The view is then exported to Excel. The plugin is triggered once again.
8. If the export succeeds, the generated file contains a number of empty columns, labeled "processid" and processts".

The PostContactRetrieveMultiple plugin

protected override void Execute(PluginVars variables)
{
    if (variables.Context.InputParameters.Contains("Query") && variables.Context.InputParameters["Query"] is QueryExpression)
    {
        QueryExpression objQueryExpression = (QueryExpression) variables.Context.InputParameters["Query"];

        //Generate and fill the ocr number field when requested
        if (objQueryExpression.EntityName == Contact.EntityLogicalName && objQueryExpression.ColumnSet.Columns.Contains("company_generatedocr"))
        {
            if (objQueryExpression.LinkEntities.Count > 0 && objQueryExpression.LinkEntities.Count(le => le.LinkToEntityName == ListMember.EntityLogicalName) > 0)
            {
                var contacts = ((EntityCollection)variables.Context.OutputParameters["BusinessEntityCollection"]);
                Guid relatedListGuid = (Guid)objQueryExpression.LinkEntities.First(le => le.LinkToEntityName == ListMember.EntityLogicalName).LinkCriteria.Conditions[0].Values[0];
                CampaignExtensions.GenerateOcrNumbersForCollection(contacts, relatedListGuid, variables.Dao);
            }
        }
    }
}

The CampaignExtensions (snipped irrelevant code)
The OCREngine used below is our tool for creating OCR numbers based on sequence numbers for e.g. contacts and campaigns, as well as interpreting the created numbers when the corresponding invoice is paid.

public static void GenerateOcrNumbersForCollection(EntityCollection entityCollection, Guid marketingListId, DataAccess dao)
{
    var campaignSequenceNumber = GetCampaignSequenceNumberFromList(marketingListId, dao);

    foreach (var entity in entityCollection.Entities)
    {
        string sequenceNumber = entity.GetSequenceValue(dao); //Get the sequence number for the contact

        var spec = new OCRSpecification();

        spec.DonorNumber = sequenceNumber;
        spec.CampaignNumber = campaignSequenceNumber;

        entity.Attributes.Add("company_generatedocr", OCREngine.GenerateOCR(spec));
    }
}

The result
When exporting members for a marketing list with only one member, the file contains only one set of "processid" and "processts" columns (see below). When exporting 40 000 members, it contains 80 sets of these columns. These columns are not present in the view in Dynamics CRM.

    Complete Name    Generated OCR    processid    processts
    Henric Fröberg   800004450000165

My questions

• Why are these extra columns added?
• Why does there seem to be a correlation between the number of members exported and the number of columns added?
• What can we do to prevent this, to bring the file size of the exports down?

We're using Dynamics CRM 2016 on-premise, rollup 1 (8.1.0.359), but we've had difficulties exporting Excel files for marketing lists before installing rollup 1.

Answer 1

As far as we can see, there's is something inherent in the platform that adds these extra columns. We've tried exported members from a marketing list with only columns from the default solution, and the processid and processts columns are still added. We were, however, able to get around this, by observing two facts.

Observations

• The query run during the export contains a LinkEntity which performs an outer join with processes, in addition to the expected listmember LinkEntity. This LinkEntity is not present when viewing (and exporting) marketing list members from Advanced Find.
• The number of processid and processts columns added are inversely proportional to the PageInfo.Count property of the query.

Actions
Hence, the following modifications to the query seems to be working so far:

• Remove the processes LinkEntity from the query
• Increase the PageInfo.Count

Code
This was done by adding a PreContactRetrieveMultiple plugin, which modifies the query accordingly. The plugin basically looks like this:

public class PreContactRetrieveMultiple : CrmPlugin
{
    protected override void Execute(PluginVars variables)
    {
        if (variables.Context.InputParameters.Contains("Query") && variables.Context.InputParameters["Query"] is QueryExpression)
        {
            QueryExpression objQueryExpression = (QueryExpression) variables.Context.InputParameters["Query"];

            var processQuery = query.LinkEntities.FirstOrDefault(le => le.LinkFromAttributeName == "processid");
            if (processQuery != null)
            {
                query.LinkEntities.Remove(processQuery);
            }

            query.ColumnSet = new ColumnSet(query.ColumnSet.Columns.Distinct().ToArray());
            query.ColumnSet.Columns.Remove("processid");
            query.ColumnSet.Columns.Remove("processts");

            query.PageInfo.Count = 5000;
        }
    }
}

Result
As the PageInfo.Count has been increased, it means that the view will load these 5000 records at once (instead of the regular 50/100/250). As a result, loading the page often takes some time and the "Unresponsive page" warning often pops up. But by waiting a little bit, the view will load. When exporting, the processid and processts will still show up in the file, but in much smaller numbers as PageInfo.Count has been increased. The file size is now much reduced and more reasonable for the amount of data it contains.

Answers to the questions

• I don't know why these extra columns are added.
• The platform seems to add the pair of columns once for every paging required to fetch all the data.
• The plugin shown above seems to do the trick, to decrease the file size of the exports.