Developing a Salesforce Download

About three years ago I was working on a Salesforce implementation for a campaigning charity. My role was to develop a SQL Server Operational Data Store that mirrored the data in their Salesforce instance. After considering a number of options we decided to develop our own download interface to regularly refresh the SQL Server database from Salesforce. Initially this was developed using Talend as the middleware, which worked pretty well for daily incremental downloads of changed Salesforce records. Continue reading “Developing a Salesforce Download”

Using the Google Maps Distance Matrix API in .Net

Knowing the distance between your staff and students’ locations and your institution has innumerable uses. From timetable planning to calculating the CO2 impact of your organisation. Given a person’s postcode calculating their straight line distance from an address is straightforward but not that useful. Actual driving, cycling or walking distances would be better. Here the Google Maps APIs can help. Continue reading “Using the Google Maps Distance Matrix API in .Net”

Using the Salesforce Metadata API in .Net

The SalesForce Metadata API provides a way of deploying and retrieving customisations in your Salesforce instance such as custom objects. Like with the SOAP API for transactional data the documentation for setting up a connection in C# and .net is not great. So here I’ve outlined the process to connect and retrieve data from the API in this environment. I’ve used Visual Studio 2017 in these examples. Continue reading “Using the Salesforce Metadata API in .Net”

Converting UTC to Local Time in SQL Server

For Salesforce UTC Date Fields

Salesforce stores all datetime fields in UTC (Coordinated Universal Time)  and converts them to the users’ local time when they’re displayed in the UI. If you’re extracting date data from Salesforce via the APIs then they’ll also come across as stored – so in UTC. If you want them to be in local time you need to convert them back. Fortunately this straightforward in SQL Server. Continue reading “Converting UTC to Local Time in SQL Server”

Auto Create Views from the Tribal SITS data dictionary

As I’ve discussed the Tribal SITS Data Model does not have the nicest table and fields names and has a huge number of lookup tables. In this situation I like to build a layer of views over the base tables to provide a friendlier interface to build queries on. Still this is a bunch of work – could I auto create these views from the SITS data dictionary? Short answer: Yes. But they’ll probably need a bit of manual tidying up. That said I can produce working views that will still save a lot of effort. Continue reading “Auto Create Views from the Tribal SITS data dictionary”

Salesforce CRM for Charities

I first looked at Salesforce nearly 10 years ago when I was asked to consider it as a Raisers Edge replacement for a medium sized medical charity. My response then was that it simply failed to meet the basic requirements of a UK charity focused on committed giving and appeals. No gift aid, no regular giving functionality, a sales process more suited to managing high value opportunities rather than volume direct marketing – this was not a comparable system with a sector specific fundraising application like Raisers Edge. Continue reading “Salesforce CRM for Charities”

Navigating the Tribal SITS Data Model

The Tribal SITS database doesn’t have the worst data model I’ve ever used but it’s still not the easiest to work with. The table and column names are logically organised but most are just four characters long. So they’re effectively codes the meaning of which is not always clear. I typically build a view layer for the tables I use frequently to add in more verbose table and column names as well as adding in all the lookup tables of which there are hundreds. Another issue is there are no foreign key constraints on the database. Aside from the fact that referential integrity is not enforced this makes finding table relationships difficult. On the up side SITS does maintain its own data dictionary which helps with these and some other issues. Continue reading “Navigating the Tribal SITS Data Model”

Reporting Timetable Exceptions in Power BI – Student Hours per Day

Having defined a timetable fact table and a timetable calendar dimension in previous articles I’ll now consider a particular reporting use case. I want to identify students who are timetabled for more than a certain number of hours per day. I want two measures here. The number of students who exceed X hours teaching in any single day and the number of days where each student exceeds this total. Using a timetable like this I can summarise the data at student / day level in SQL and add it to the Power BI model or create it in DAX as a calculated table. This will let me define the measures I want. Continue reading “Reporting Timetable Exceptions in Power BI – Student Hours per Day”