Browsing All posts tagged under »T-SQL«

Process Modeling – Is this a fact or a dimension?

November 23, 2011


This is a question I see a lot on forums (and also get asked) and it’s generally based on confusion around events and processes (such as an incident, a bank application process etc). Let’s remember what the difference is between a fact and a dimension. A fact table stores a business process.  A fact is therefore an […]

Tracking History with Slowly Changing Dimensions

September 24, 2011


In this post I’d like to show a few of the different ways to maintain history. As anyone who has been in data-warehousing for a while can attest to, the two most common scenarios that business users want to see are the data as it was (Kimball Slowly Changing Dimension Type 2) , or as-is […]

Data Modeling Conundrum Follow up

March 17, 2011


As a follow up to my previous post on a Data Modeling Conundrum in January (see I left it open as to what the appropriate dimensional model should be. To recap from my first post, let’s say that I’m trying to track Marine Incidents. Zero or many vessels may be involved in an incident, […]

SQL Hierarchy Comparative Performance

March 1, 2011


This is the last post I will be doing on SQL Hierarchies. Since I’ve covered the most common methods for dealing with hierarchies I thought it worthwhile to do some performance comparisons. I will be testing displaying the descendants of the root node, as well as testing aggregations from a sales table for both the […]

Nested Set Hierarchy

February 14, 2011


Another method of modeling hierarchies in SQL, is the Nested Set method created by Michael J. Kamfonas and made popular by Joe Celko. I’ve implemented this method in the past and through testing have found it to be extremely performant. This method involves creating left and right values for each member in the hierarchy. Then […]

Populating a many-to-many bridge table pt2

February 1, 2011


I have been testing the previous example code (see to populate a many to many bridge table with a large dataset and wasn’t happy with the performance. So I’ve  gone back to the drawing board on how to do this. I wanted a set based solution, and something that was both simple and efficient. To recap, if […]

Populating a many-to-many bridge table

January 25, 2011


STOP! For a simpler, faster and more elegant solution to the following problem, see this: In a recent post on dealing with the many-to-many situation ( , I illustrated the use of a bridge table in a dimensional model, as prescribed by Ralph Kimball. So if for example we are recording sales, and many […]