Using a Date Parameter in SSRS with MDX

March 22, 2011


There are two ways of using a date parameter within SQL Server Reporting Services for reports using SSAS as the source. I’d firstly like to thank my friend Boyan Penev for helping me out with some MDX questions I’ve had – please visit his excellent blog: The first method is to use a MDX […]

Posted in: SSAS, SSRS

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, […]

Posted in: Data Modeling, SQL

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 […]

Posted in: Data Modeling, SQL

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 […]

Posted in: Data Modeling, SQL

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 […]

Posted in: Data Modeling, SQL

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 […]

Posted in: Data Modeling, SQL


January 20, 2011


Introduced in SQL Server 2008, the HierarchyID is a CLR that uses a binary form of the enumerated path method described in a previous post on this blog. Using prebuilt CLR functions it is relatively simple to write queries to retrieve descendants and ancestors. So let’s look at retrieving the descendants of Baby Food as […]

Posted in: Data Modeling, SQL