Back to school, helping your support team again

Hi Blog!
It's been 3 months since the last post, so here's more tips and tricks using MS SQL with DBAmp to make your job done as a Salesforce data guru.

Today I'm going to show a short piece of T-SQL code that illustrates three helpful techniques, particularly useful if you're dealing with ad-hoc user's request for dashboard data or to provide some data in their inbox on a regular basis.

Here's the gist:

Now let's discuss what and how it does. There are 3 nested data queries in this gist.

  1. The inner data query uses SOQL and executed on the Salesforce side. We have it very specific both in terms of time sequence filtering and additional filters needed. By the way, don't get puzzled by origin = 'GC' condition, you should substitute it for something that makes sense to your organization. Also notice how we using date = LAST_N_DAYS:10 construct to return only needed date interval. All in all, the query is selective, only columns needed in the following calculations travel over the network.
  2. Data enrichment is done on MS SQL side. We calculate our business metric(s), time to resolution in hours for this particular case, on the SQL side instead of defining additional fields in Salesforce. After all, this maybe only a temporary need. I'm utilizing isnull() function to use the current timestamp for calculating the metric for still open issues. Last point here, I want to provide decimals for time expressed in hours, thus I first multiply the TTR by 1.0 to avoid decimal part truncation as the SQL round to integers otherwise.
  3. The last step is to aggregate the calculated values and format the results. Again, nothing particular complicated here, just getting standard aggregates and make them decently formatted. Check out Format function in case you want to dive deeper.

And the result will look like this:

To sum it up,

  • use SOQL selective queries and return only needed data over the network;
  • calculate your business metrics on SQL using rich functions;
  • get aggregates and formatting as it fits your customers.

That's it for today!

No comments: