This script is aimed at data warehouse relational databases which don’t have foreign key constraints. Using column names it matches key column values between fact and dimension tables to identify orphans.
Continue reading →A simple framework for implementing a time-framed record deletion policy: deleting records that are older than a certain threshold. Consists of a logging table and a stored procedure which deletes the records. The procedure deletes rows in batches and provides … Continue reading →
A standard method for populating a date table with fiscal periods will always come to grief over exceptions. This post includes a procedure that should be customisable enough to cater for most variations on fiscal periods – provided all periods … Continue reading →
Background Transactional tables exist in Database A. These tables have partner archive tables in the same or another database. For each insert or change of a value in the transactional database a timestamped new row is entered into the … Continue reading →
For measuring short durations, such as telephone calls or handling times. Q1 Linear Grain? The question I haven’t answered is whether the dimension should be at the finest grain for all durations. The grain I am using is seconds but … Continue reading →
Some functions for obfuscating data to protect personally identifiable data. Aims: fast enough to process a large table resulting data is “sufficiently” obfuscated resulting data retains enough characteristics that it makes some sense in context. transformations are repeatable so that … Continue reading →
Options for including literal values in scripts – syntax that I find particularly hard to remember. The feature is named Table Value Constructor. It was introduced in 2008 – see this article. Source of Merge statement MERGE INTO dbo.TargetTable AS Target … Continue reading →
Steps to follow to create a randomised dataset for testing. An online data generator: www.generatedata.com. Prepare the Database We will be loading a lot of data into the tables. So Set Instant File Initialisation Set the data file size sufficient to contain … Continue reading →
The problem in outline A simple expression can assign a random order or row number to a set of records. The following example assigns a random decimal value between 0 and 1 to each row in a set: CAST(RANK() OVER (ORDER … Continue reading →
Some practical reasons why NULL should be used as a no-value indicator in a column. These examples arise from a situation where a column will contain a unique value eventually . Until the value is known the column will have a … Continue reading →