Querying BigObject in SOQL Query tool yields incorrect exception; query in Developer Console works fine

[Eric Kintzer]

Execute this query in IC2 SOQL Query pane:

SELECT FieldHistoryType FROM FieldHistoryArchive WHERE FieldHistoryType = 'Contact' 

RESULT:

org.apache.cxf.binding.soap.SoapFault: BIG_OBJECT_UNSUPPORTED_OPERATION: Aggregate functions not supported

Execute exact same query in Developer Console and results are returned

FieldHistoryArchive is a BigObject. Doc on how to query

Dev console at V50; IC2 connection at V50; latest IC2

[Scott Wells]

Hmmmm...I'm not sure why that would happen unless the Dev Console is using a different API to run this query. I would assume they're both just using PartnerApi.query(). Let me investigate a bit...

[Scott Wells]

Eric, I'm going to ask a dumb question so please bear with me. I've created an object with Track Field History enabled. I've created instances of that object. While I have the *__History object, I don't have FieldHistoryArchive in the org. To save me from having to dig around, is there something specific I need to do in the org to enable that? Thanks!

[Eric Kintzer]

Scott - FieldHistoryArchive comes with the Field Audit Trail feature of Salesforce Shield. And, just to make testing even more fun, it appears that the records in FieldHistoryArchive get populated asynchronously (and I don't know what the delay is except that it is more than minutes)

FieldHistoryArchive is a Big Object so the behavior I observed might happen on any Big Object, not just the OOTB FieldHistoryArchive. In my org, we’re not using any custom Big Objects

[Scott Wells]

Hah! Yeah, it occurred to me that this was a Shield feature a few minutes after I sent that question. If memory serves, though, the content of the _History object is replicated into the FieldHistoryArchive Big Object, as you said, async. So one way or the other you must configure a standard or custom object for field history tracking, right? Then you also need to have Shield enabled in the org to save off the long-lasting history securely. Or am I misremembering?

Let me see if I can set things up to try to reproduce this...

[Eric Kintzer]

So one way or the other you must configure a standard or custom object for field history tracking, right? 

YES

‌

Then you also need to have Shield enabled in the org to save off the long-lasting history securely.

YES

[Scott Wells]

Thanks. It's funny how things can get evicted from your mental cache. The last company where I worked before going 100% full-time on IC2 was in the health care space and I helped set things up for Shield platform encryption including field history tracking. It just took a minute to pull back together those braincells. I think I almost have it set up properly again and hopefully will either reproduce what you're seeing or minimally have more insight into what might be happening.

[Scott Wells]

Eric, are you by chance doing this in a scratch org? That's what I'm trying and it's failing when trying to set the history retention policy. If you're in a scratch org, what org shape option(s) are you using to allow that? Otherwise I'm going to need to see if I can get Salesforce support to enable it in one of my orgs before I can truly try to reproduce this.

[Eric Kintzer]

I’m using a Sandbox, not scratch org. Doesn’t a new Developer Edition come with Shield feature license? Seems to me there is a trailhead on Shield that requires one to enable / configure in a playground org (though the Trailhead is on encryption) https://trailhead.salesforce.com/content/learn/modules/spe_admins/spe_admins_set_up?trail_id=shield

I could tell stories about Shield encryption and all the hassles it creates once you start encrypting fields

[Scott Wells]

I'm trying to remember if we had to have Shield enabled by support or if we could enable it ourselves in DE orgs. I have a few so I'll try that next.

And yes, we had an interesting time getting Shield encryption going, especially with deterministic encryption since we needed to be able to filter/order by some of the data. I certainly appreciate the complexity of the problem, but man, it's far from a transparent solution.

[Eric Kintzer]

when one encrypts Account.Name or Contact.Name (as we had to), you suddenly discover that you can’t install a lot of appexchange packages because they have coded testmethods using filter or order by on Account and Contact objects! Doug Ayers' excellent package is uninstallable for us and we had to wait a year for Apttus to upgrade their Contract Management package. I also had to manually rewrite some testmethods in the fflib unlocked package. And the list goes on …

[Scott Wells]

Yeah, without going into significant detail, I've run into similar. Our packages were installed in a customer org with some other packages that expected to be able to filter/order by some standard object fields that needed to be encrypted according to HIPAA. Made from some interesting discussions...

I'm going to keep trying to reproduce this today. I'll let you know how it goes.

[Scott Wells]

Eric, I verified that I'd set up this scratch org as indicated in that Trailhead module. The issue I'm hitting is with the propagation of field history from the org's *_History into the FieldHistoryArchive BigObject. The latter doesn't exist. Based on everything I can find, it seems that I need to grant my user the "Retain Field History" permission--which I don't see anywhere--and I need to set up the object with a history retention policy, and when I attempt to deploy that it fails with "Cannot create a Retention Policy in this organization". Do you know if you've done both of those in your org? Or am I overcomplicating this and there's some other way that FieldHistoryArchive ends up in the org?

[Eric Kintzer]

boy – I didn’t think this was going to be so involved … I’m sorry it is causing you grief.

1. A retention policy shouldn't be necessary as we just took the defaults - again, this is in a sandbox/prod org world; no scratch. We merely licensed the whole suite of Shield products - event monitoring, field audit trail, platform encryption and everything was set up automatically
2. Retain Field History shows up as a General User Permission and is checked true for our System Administrator profile (uneditable)

‌

NOTE Once Field Audit Trail is enabled, HistoryRetentionPolicy is automatically set on the supported objects. By default, data is archived after 18 months in a production organization, after one month in a sandbox organization, and all archived data is stored for 10 years. The default retention policy is not included when retrieving the object’s definition through the Metadata API. Only custom retention policies are retrieved along with the object definition.

so, from the above, it would appear it takes 30 days for history to archive.

3. If you create a Dev Edition org (not scratch) - do you see FieldHistoryArchive ? (i.e. perhaps the issue is scratch?)

[Scott Wells]

Okay. That permission definitely doesn't exist in this scratch org. I'll create a new DE org tomorrow and try to reproduce this there. Hopefully that will go better. I'll keep you posted.

[Scott Wells]

Okay, I created a DE org and have configured/populated it with the same stuff I had in the scratch org. Unfortunately I'm having the exact same issues where FieldHistoryArchive does not exist in this org, and when I try to set the history retention policy (which is part of the Salesforce documentation for how to get this all working), the deployment fails with the same "Cannot create a Retention Policy in this organization". Do you know of any other step you had to take to enable FieldHistoryArchive in the org? Obviously that's going to be critical to reproducing, understanding, and (hopefully) fixing this.

[Eric Kintzer]

here’s some good news - I can reproduce the problem using any Big Object - you don't need to fuss with FieldHistoryArchive

1 - Create a Big Object MyBigObject in Setup; add a single field called MyKey and then a single index called MyIndex that uses MyKey. Add a permission set that gives read access to this object and assign to yourself.

2 - Run this query in Dev Console - select id, MyKey__c from MyBigObject__b - it will return 0 records

3 - Run same query in IC2 SOQL tool - select id, MyKey__c from MyBigObject__b RESULT = org.apache.cxf.binding.soap.SoapFault: BIG_OBJECT_UNSUPPORTED_OPERATION: Aggregate functions not supported

‌

as for your questions…
Retention policies are not required to be setup; there are defaults – see https://help.salesforce.com/articleView?id=field_history_workflow_examples.htm&type=5

This example demonstrates how to set a field history data retention policy using Metadata API. Edit the metadata only if you want to override the default policy values (18 months of production storage and 10 years of archive storage). 

‌

There was no special action we took to get this enabled - we licensed the feature. I looked at some of my Trailhead orgs and other dev editions I had lying around and they don’t have FieldHistoryArchive either. I’m going to surmise you’ll need to contact SFDC Support (oh fun!) and have them enable it in your Dev Edition org. What’s interesting in our PROD org is that you don't even see Shield as a feature license listed in `Company Information` - perhaps suggesting that SDFC Support is the only route as there is no way I can directly confirm in our PROD org that we licensed the feature (except by looking at our renewal subscription in the SFDC Store.

For information about enabling Field Audit Trail, contact your Salesforce representative.

‌

[Scott Wells]

That worked, Eric, and it should be a very simple fix. I should have thought about this earlier, but it's the pre-query check for row count that's causing the issue, not the actual query. I'll just exempt queries against Big Objects from that check. Thanks for helping to make this reproducible!

[Scott Wells]

Fix committed for inclusion in the next release. Thanks again for helping with the diagnostics!

[Eric Kintzer]

Not sure how you identify a BigObject; obviously anything ending in `__b` but `FieldHistoryArchive` is an OOTB big object and there may be others I’m (blissfully) unaware of

ah, yes, I remember - real time event monitoring history are big objects - there are many of these, all OOTB

https://help.salesforce.com/articleView?id=real_time_event_monitoring_storage.htm&type=5

[Scott Wells]

Yeah, that might be tricky. I looked at the metadata and there's nothing distinct except for perhaps the ID prefix. I'll see if I can definitively identify which ones are BigObjects. Worst case (aka, likely) scenario I'll see whether the row count check failed specifically because it's not aggregatable and skip that check.

[Scott Wells]

Delivered in 2.1.5.4.