Return Records From Salesforce Analytics API (no SOQL)

SOQL is the best. I mean, Dynamic SOQL? C’mon, that’s just cool. But SOQL has its problems. One big problem is big objects. I mean, more than 200k records big. You start querying that object, and you’re probably get a message that looks like this:
System.QueryException: Non-selective query against large object type

Bummer. Maybe you can just add selective fields to your 'where' clause. Maybe you can turn the field you are filtering by into an external ID (so that it is indexed). Maybe you can contact Salesforce and see if they feel like making the field you are filtering by a custom index. And maybe you can’t, or maybe they won’t. Then what?

There’s a couple good options left, but the one I want to talk about is leveraging the Analytic API to return your results. For this you are going to need:

  1. At least one report that uses the fields you were trying to put in your 'where' clause as its filters
  2. A Custom Setting (list type) to keep track of your report’s name & Ids (which can change on deployment)
  3. Apex that sets a report filter and processes the result

Before we being, and important disclaimer: You cannot call the Analytics API from a trigger, but that’s OK. You can replicate all the same functionality with other tools. If you are running your Apex class based on changes to your data that would typically cause a trigger to fire, you need to call that Apex class from Process Builder instead (I will be making a blog post soon about how to pass multiple, strongly typed parameters from Process Builder to Apex in bulk).

Here’s an example. Let’s say I have the following report:

A few results show up where the Account Number is blank. But where’s the Account named “Demo 1”? I want Account Demo 1! I know it is Account Number “12345”. So I query for it: [SELECT Name FROM Account WHERE AccountNumber = '12345']. But, uh-oh! There’s 500,000 Account records, and Account Number is not indexed. So I get an error. And, let’s just say there’s no good way to make the query selective. No worries, because what I’m going to do next is add the name and ID of the Report we just made to a Custom Setting, like so…

Now we’re getting somewhere. At this point, I can run Apex that will set the report filter, then process the results and end up with a list of Account, just like if I had run a query:

Notice a few things:

  1. I had to put the name of the Custom Setting record that holds our Report information into lines 2 & 10.
  2. Line 5 is hardcoded for demo purposes. In an actual class, I would follow my “10 Rules of Apex”  rule #2 (NO hardcoding values) and use a variable to provide the Reports.ReportFilter.setValue() parameter.
  3. When we set the filter this way, it does not change in the saved report definition. If we went to the point-and-click the UI and had a look, the report would still have the null value for the “Account Number” filter as it does in the picture above.

Assuming there is one Account that has an Account Number of ‘12345’, the debug statement on line 17 will return:
(Account:{Id=001i000001d7uKzAAI, Name=Demo 1})

There’s the “Demo 1” Account Name we were looking for! In a neat little array, just a like a SOQL query returns.

After this, we can keep on writing the class to use this array, safe in the knowledge we can keep adding records to our Account object, and we’ll be able to use the Analytic API to access them in our Apex code!

Posted in Learning