Blackboard Data provides access to data about activity on your Blackboard LMS via the Blackboard reporting stack. The data is stored in a Snowflake data warehouse instance. Here you can query the data directly via the Snowflake console or connect a reporting tool such as tableau or Power BI. But what if you want to export the data out of snowflake, for example to an institutional data warehouse. There are number of ways to do this but .NET is my preferred platform so that is what I’ll be using. The Snowflake documentation on this makes the task look quite confusing- but actually it’s very straightforward. Here’s how to connect to and download from Snowflake to an on premise SQL Server instance. Continue reading “Downloading Blackboard Data from Snowflake DB in .NET”
Applications by UK Region: A Choropleth Map in Power BI
The Power BI shape map allows you to create simple choropleths (a map where the shading of a map region indicates some value) but comes with a very limited set of map regions. For the UK you only get UK countries so if you want to show other UK regions, you’ll have to source additional maps. Here I’ll show how you can map student applications by UK NUTS regions (Nomenclature of Territorial Units for Statistics). Continue reading “Applications by UK Region: A Choropleth Map in Power BI”
Query Amazon Redshift from SQL Server
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”
Normalising Multivalued Fields in SQL (Salesforce Pick Lists)
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)”
Random Splits in Marketing Selections – A SQL Solution
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”
Comparing Response and Attrition Curves in SQL
I recently needed to test for similarity between several sets of time series data – specifically response curves and attrition curves – and find the most similar data sets. I’m in no way an expert on curve fitting but using the squared deviation method seemed to work quite well and lends itself to a nice implementation in SQL so thought I’d share it here. Continue reading “Comparing Response and Attrition Curves in SQL”
Where’s the Old Default Power BI Theme?
A small thing this but, at some point, the Power BI default theme was changed to an eyesore of a palette reminiscent of the awful highlighting sometimes used in Excel by finance teams. Aside from being a bit ugly I’ve used the previous default theme for all my historical reports and want to keep this style. The old default is still available as the Classic theme. Continue reading “Where’s the Old Default Power BI Theme?”
Generating an Integers Table
One of the first jobs I do when setting up any new database is to create a tool set of useful objects. And an integers table is normally the first of these – having a source of numbered rows is often handy for generating test data and to use in cross joins to add additional rows to a query. Generating a permanent table of values will save generating these on the fly whenever they are required. Continue reading “Generating an Integers Table”