Data Solutions for Not for Profits
Continue reading “SQL Connections, Tasks and Multiple Results Sets”
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”
Power BI provides some basic alerting that can be set up on dashboard tiles but for my use case there are some major issues. First they rely on the user to define the alert and I want to push notifications to users. Secondly implementing alerts based on specific users’ areas of concern (the courses or schools that they manage for example) would seem to require a specific dashboard for each user which is not tenable. Since my data is on premise in SSAS however I can go back down the stack to SQL Server where I can get a bit more control of the process. Here’s the basic functionality I’ll be delivering. Continue reading “Creating User Alerts from SSAS Tabular Model”
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)”
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”
Comparing measures year on year is normally a trivial task. Drop your years into the columns of a matrix and months into the rows and there you go. This all works so long as the dates in the years you’re comparing don’t “overlap”. How can they overlap? An example is the best way to illustrate this issue. Say we’re comparing student withdrawals between academic years which run SEP to AUG. But withdrawals from an academic year may be recorded with a date after the end the actual end of the academic year. Continue reading “Handling Overlapping Academic Years”
Setting up a KPI to monitor performance against a single target is simple enough in SSAS (it’s also possible directly in Power BI but I’ll only deal with the SSAS approach here). However if you have different targets for different dimension values then a bit more thought is required. As an example imagine different courses are set different targets for student withdrawals in an academic year. Continue reading “Aggregate Traffic Light KPI with Variable Targets in Power BI and Analysis Services Tabular”
There is a connector for Amazon Redshift (a data warehouse product available on Amazon Webservices) in Power BI that works just fine including support for direct query. But what if you want to pull data from a Redshift database to SQL Server? I wanted this to add content from a third party data warehouse on AWS to an on premise institutional data warehouse. In this scenario there is an ODBC driver that you can use create a linked server to the external DB and pull the data straight out. Continue reading “Query Amazon Redshift from SQL Server”
Salesforce’s storage of picklist values is a bit of a compromise of good data modelling and something of a headache if you want to re-normalise the data elsewhere. Where a user can select more than one item in a pick list field on an object the selections are stored as a semi-colon separated list of items on the object record – so… Continue reading “Normalising Multivalued Fields in SQL (Salesforce Pick Lists)”
A common ask (if you’ll excuse the pun) in selection briefs is some kind of randomisation; as for a test mailing or for AB test. This is not a difficult job but is easy to get wrong – and when it does go wrong it can have serious consequences. Continue reading “Random Splits in Marketing Selections – A SQL Solution”