2019-01-29

I was pleasantly surprised that out of the Gizmodo list of best free apps for a new Windows notebook I've been using 9 out of 12 apps for years now. I'd add a few more to the list however:

  • Notepad++ as the default replacement of the stock Notepad 
  • Autohotkey for keyboard and mouse automation, hotkeys, signatures, etc, saved probably hours of typing for me since 2005!
  • Far Manager (of course!)
  • Paint.Net my personal preference over Gizmodo's recommendation of GIMP.
  • ShareX for taking annotated screenshots with ability to post to a picture sharing service of your choice
  • Calibre for keeping my e-books organized
And tools and packages for software development make up a whole another universe on their own.

2019-01-15

Generating pseudonymized 1-character IDs from integers

A client of mine asked to generate pseudonymized statistics out of survey data. Because of PII concerns, they wanted to completely eliminate names or static user IDs from the resulting document and, given the usual count of users participating in surveys below 25-30, choosing 1-character IDs seems to be a good choice. To give you context, responses to a survey with the scale between 1 to 5 would look like this:


Here's a scalar-valued function, which would do such a translation:



Points of interest:

  1. The function takes @seed as a parameter, which should be the same for all IDs in the batch. You may, and probably should, get a new random seed every time you start processing a new batch of IDs, in my case generating stats for a new survey;
  2. The maximum number of distinct characters that can be used for encoding currently is 62 – upper and lower case Latin characters and digits. To further extend this approach, one can either append more encoding characters to the set or modify the logic to encode user IDs with 2-character sequences. This would give 52^2 > 6k possible encodings for just using Latin characters, which is more than enough for all practical applications.

Here're two examples of the same stats encoded in two separate batches.
Barch 1:
Same data in batch 2:


As you can see, the encoding characters were randomly changed for the second batch.

2018-10-01

0 calories syntactic sugar for your sp_executesql statements

Today I'll share a helper procedure, which doesn't add much to your code, but some syntactic sugar and readability.

If you actively use integrations with other systems through linked servers, you inevitable ran into a limitation of openquery() statement, as it can only accept static strings as queries to an external data source. A workaround is to build a string containing full statement, and then use insert ... execute sp_executesql ... construct. Putting the whole conversation about efficiency and error handling a side, your code will become not much readable, as you need double twice single quotes - first to escape them in your MS SQL string assignment and second time as you building a parameter for openquery(), which should be a string in its own term.

As there's more than one kind of quotes available, I decided to add a simple procedure, that would save me from counting single quotes in my string and do this trick for me. So, here's the procedure code:



It properly escapes double quotes " with the right number of single quotes for embedding your string parameters, and does the same for grave accents ` as single quotes in the beginning and the end of the query string.

And here's it's usage, you can compare readability of the statements with and without using it for yourself:



The code should be self-explanatory, but take a note of the second optional @debug parameter to sp_executequotedsql. Once set to 1, it will print out resulting parameter for the standard sp_executesql procedure, so you can inspect that quoting is done right. Enjoy!

2018-09-27

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!

2018-06-26

Automating automation

If your shop is big enough, you probably have many automation jobs and may easily have tens of different job steps, that you schedule to run, say, nightly. How do you check the execution status? Here's a short gist, that would take the execution results of all the steps, check if there were any errors and then send an email to all needed recipients listing all the failed steps.



To improve things further, you can split your steps into two or more jobs, based on their importance, or include more information in the notification email, like links to specific urls, that are relevant to your task at hand.


2018-06-04

Manually syncing data from an external data source

In our environment, we have several linked servers defined for our MS SQL central DB. One or two times a day we need to pull data from these external data sources and store it in a local table for further processing. All records have timestamps, but we cannot rely on time being in sync between the MS SQL and linked servers. Moreover, some records can be recorded at a later time, yet having earlier timestamps.
So, here what we did to come up with a workable solution in these constraints. First, we use the current time offset back as a parameter to call to a linked server. The offset is sufficient enough to guarantee we will get all the records we did not see before. Second, we'll use EXCEPT to skip the records we already have in the local table. Here's the gist:

Another point worth to mention, yes, you can use insert ... Exec sp_executesql @sql_string to insert results of stored procedure execution into a table.

2018-05-19

From UNIX timestamps to SQL datetime

Ever struggled to recall what's the beginning of the UNIX epoch? I certainly did, so here's a function FromUnixTime() to conveniently convert UNIX timestamps to datetime values. The usage is straight forward: