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”
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”
SQL Connections, Tasks and Multiple Results Sets
There is a tendency when confronted with a bug to assume that it’s related to the most complex or least understood part of a program. While not unreasonable this can lead to missing simple and obvious problems. I recently wasted several hours convinced that I had a threading issue which predictably turned out to have a much more prosaic explanation.
Continue reading “SQL Connections, Tasks and Multiple Results Sets”
Creating a SQL Server Code Back Up
Over the last few years I’ve got into the habit of setting up an SSIS job to do a daily backup of all the procedures, views and functions on databases that I’m working on. A lot of organisations I’ve worked with don’t have any source control or release procedures on database objects and having a history of these objects can be invaluable. A few times I’ve been able to use this to restore procedures that have been updated without having to find and restore an entire database to get at the old procedure version – well worth the time required set this up. First a view for the procedure and view definitions: Continue reading “Creating a SQL Server Code Back Up”
Converting an Accumulating Fact Table to a Transaction Table (Calculating Peak Call Volumes)
An accumulating fact table has rows that represent a process and typically has dates (amongst other keys) that represent the time of completion of a stage in the process. For some calculations they are also a pain to deal with and where possible I like to convert them into transactional tables as well which makes solving these a lot simpler. Transaction tables represent a single measurement at a point in time which is immutable – it will never change. To take a very simple example consider a call logging table that contains the start and end of a call. Continue reading “Converting an Accumulating Fact Table to a Transaction Table (Calculating Peak Call Volumes)”
De-duping based on Sample Files
A while back I was asked if I could carry out a contact de-duplication exercise based on samples from the actual files to be de-duplicated. My initial answer was no. Clearly checking the level of de-duplication on a sample will not give the same results as for the complete files. Given two identical files (100% duplicated) of 50 records and taking a random sample of 5 records from each the chance of getting two identical samples is a million to one against! My second more thoughtful answer was yes but it would be fiddly (more so than just using the complete files). Still I was curious enough to work out how it would be done. Continue reading “De-duping based on Sample Files”
Power BI Custom Visual Part 5 – Formatting
Setting up the formatting options turns out to be the most fiddly bit of developing a custom visual. There are a few “special” property names that are required to trigger some behaviours, undocumented features and at least one bug that I’ve come across. In this section I’ll explain how the options are set up for the Tree Funnel visual and in an extra post I’ll talk about some of the other issues and features. Continue reading “Power BI Custom Visual Part 5 – Formatting”