How do I display salesforce reports or aggregate metrics over a large salesforce object in a Skuid page?

Questions
1

I searched this forum and couldn’t find a clear answer.
My goal
I would like to be able to display a Skuid page with basic aggregate metrics on my Contact Salesforce object with over a million records and for this data to update automatically with my Contact object.
1st Approach
I developed a Skuid chart that performs the aggregate count function over my salesforce Contact object containing over a million records and ran into two constraints.
For normal users, it could only count records they have read permissions to, which would not be the entire dataset.
For admin users, the loading the page would generate an Apex CPU time limit exceeded error, likely because a million records were being counted at that time. The records should be counted once and stored as a value to access, so that’s why I next tried roll-up summary fields.
2nd Approach
I created custom object with a roll-up summary field. My Contact object was already a master in a master-detail relationship so creating a master to Contact isn’t allowed. I also explored declarative lookup roll up summary application. That solution required creating a lookup relationship to my new custom aggregate object, specifying the row I want to update. However I ran into issues with this as well. My simple count function would always return zero with no errors. I don’t know why this isn’t working and I have a question out in the DLRS community forum. I suspect permissions issues.
3rd Approach
Next I tried to create a new model in Skuid, pointed it to my Contact object and set the model behavior as aggregate. Chose the field I wanted to count and when I tried to display that value in a chart it says “No data to display”… in a text field the value stays blank. My guess is that this is due to permissions issues. I can’t give read access to the entire Contact data set to everyone, so this approach may be a dead end. Are there any logs in salesforce I can check to verify if this is a permissions issue?
4th Approach
Tried to create a salesforce report with the information I need and access it in Skuid. Unfortunately, after creating a report over my Contact object in salesforce, I can’t see it among the list of objects when I tried to create a model in Skuid. I can only view salesforce objects. I’ll be ok with simply displaying salesforce reports and charts on a Skuid page, but it looks like that may not be possible. reference This reference also suggests to try using Skuid reports, but I’ve tried those.

Any suggestions on any of my approaches would be helpful. I’ve been trying to solve this issue for over a week.

2

This is fun. You have dug really hard here. I do think we can get you across the end line!!!

Limitations:

  1. Skuid is always going to query records using the users security model. Even aggregate queries. So we are never going to show data about contacts that the user doesn’t have access to. Normal Skuid models cannot run in system mode - even aggregate models.

  2. Skuid does not currently have declarative access to Salesforce Reports. You are right - they don’t show up in your list of objects. There is a Reporting API which could be accessed by using a REST model to get at SFDC data. (See this). But the strcuture of the REST response is not really helpful. You really can’t easily turn that output into a Skuid model without a lot of javascript… But hey… You could try.

The idea that I think could work would be a Reporting Snapshot. This tool runs a report on a defined schedule and then creates rows in a custom object with the output. These Reporting Snapshots are available to use in Skuid.

Just be careful that Salesforce Reports will only return 2000 rows. So make sure the categories you are aggregating on are more limited…

Good luck

3

Thank you so much! Setup Reporting Snapshots this weekend and they insert rows with aggregate metrics into my custom object as expected.

Since I’m using the custom objects to display charts, I’d prefer the behavior of the Reporting Snapshot was to overwrite existing rows with matching field values on subsequent runs instead of creating new records.
I set the field in my custom object to only allow unique values, hoping it overwrites, but instead the Reporting Snapshot errors and doesn’t update.
Any ideas on how to not create a growing set of records over time with Reporting Snapshots?
Or delete all rows before the Snapshot is run?
Or any other strategy to create a sensible chart from the rows and aggregate values?

4

You are asking a more core Salesforce question here - but it seems others have this idea too. Checkout this post with both some Code solutions, and an App Exchange package that may help with the challenge of automatically deleting old records.

You could also configure your skuid model to only retrieve records in the current period (this week, this month…) - so even if the old records had not been deleted - they wouldn’t show up in the chart.