Analyzing Big Objects data

Following with the blogs I have recently written about Big Objects, as this one or this one, I want to finish this blog series for now talking about one use case that I am sure you have thought about when reading the previous posts.

Imagine that you have some Big Objects in your organization. You have stored some data there, and now, you want to report on them. As you probably know if you read this post, search, reports and dashboards are not available for Big Objects. How can we accomplish this?

One possible solution to this problem could be to use Einstein Analytics. Einstein Analytics can be used with Big Objects according to the Salesforce team that develops the feature, however, I am not the biggest expert in Einstein Analytics, so I recommend you to explore this option more and ask Salesforce directly if you are going to use it.

A different solution may be implemented thanks to Async SOQL. Async SOQL is a new technology that allows you to move data from Standard / Custom / Big Objects to Standard / Custom / Big Objects, thanks to an async job that runs in the background. You can run Async SOQL executing a simple query though the REST API, and it is important to highlight that this technology is very performant, much more than common SOQL, when it comes to a large number of records.

If we need to analyze data that is stored on a Big Object, we can use Async SOQL to aggregate and move the data you need to report on to an intermediate Custom Object, in which reporting constraints don’t exist. Aggregating the data will allow you to obtain a much smaller dataset, with only the information that matters to you.

In our use case we have a Big Object, CodeReviewHistory__b, in which we have stored information related to a Custom Object CodeReview__c, that was occupying a lot of space in our organization. We removed all our CodeReview__c records, but now, we want to get the average score that each Employee has given to the code review records.

To do show, what we will do is to create a new intermediate Custom Object, called CodeReviewReport__c. Then, with Async SOQL, we will be able to execute this query:

This is, we are calculating the average score by Employee, taking into account all the CodeReviewHistory__b Big Object records, and storying this information, together with the Employee Id, on CodeReviewReport__c Custom Object.

This will allow us to report on CodeReviewReport__c Custom Object later, and get the reports and dashboards that we need. For example:

We could have done something similar, instead of using Async SOQL with aggregations, using it combined with some filtering, obtaining in the same way a smaller dataset that we can copy to a Custom Object and report on.

Did you know Salesforce is already using Big Objects? Do you know that you can start analyzing standard Big Objects just now? I recommend you to read this post in which Adam Torman puts in practice this aggregation technique over Logging Event, one of the first standard Big Objects that Salesforce has created, for Event Log Monitoring.