For years I’ve been using excel or dynamic SQL to generate SQL scripts to do data loads and updates and to generate IN predicates . For a small number of rows or values this is quicker than importing the required data into SQL Server. A few days ago I idly wondered how difficult it would be to create a SQL Server Management Studio extension to do these jobs directly in SSMS. The answer was quite hard but the results are quite satisfying. Continue reading “Create a SQL Server Management Studio Extension”
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”
Charticulator Export Power BI Custom Visual
In my previous post on Charticulator I produced a really basic bar chart. This was designed to demonstrate some features I found confusing. And to provide a chart to test the Export to Power BI Custom Visual feature of Charticulator. Having dabbled in Custom Visual development I was interested to see how it would perform. Continue reading “Charticulator Export Power BI Custom Visual”
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”
Connect to the Salesforce SOAP API in .Net
C# and the Salesforce Partner API
In general the Salesforce API documentation is excellent but I found the sample code for connecting via C# to be unhelpful at best. So here’s a full guide to connecting to the Salesforce Partner API in C# and dot Net. I’ve used Visual Studio 2017 in these examples. Continue reading “Connect to the Salesforce SOAP API in .Net”
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”