Browsing All Posts published on »January, 2011«

Populating a many-to-many bridge table

January 25, 2011

2

STOP! For a simpler, faster and more elegant solution to the following problem, see this: https://jsimonbi.wordpress.com/2011/02/01/populating-a-many-to-many-bridge-table-pt2/ In a recent post on dealing with the many-to-many situation (https://jsimonbi.wordpress.com/2011/01/16/data-modeling-conundrum/) , 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 […]

HierarchyID

January 20, 2011

0

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

Data Modeling Conundrum

January 16, 2011

4

A post on the Kimball forum found at www.rkimball.com has raised a question very similar to one I worked on five years ago when I implemented an Oracle OLTP system capturing Marine Incidents, and the associated data mart for reporting. The question was this: How to model an incident when an incident may involve zero or many […]

Flattening a parent-child hierarchy

January 16, 2011

1

Flattening a parent-child hierarchy can be very useful for both SQL and SQL Server Analysis Services, since SSAS cannot index Parent-Child hierarchies. It should be noted that BIDS Helper, the excellent download available at http://bidshelper.codeplex.com/ provides functionality to “naturalize” a parent-child hierarchy. We are going to build on what we did with the Enumerated Path […]

Using Multi-Select in SSRS

January 15, 2011

4

One of my pet peeves is when developers try to put business logic into the actual report. I strongly believe that any business logic should reside in a stored procedure, and that stored procedure is then called by the report and the results displayed. There are many benefits to using stored procedures as the source […]

Kimball Hierarchy Bridge Table

January 15, 2011

1

The Bridge table advocated by Ralph Kimball is yet another method to navigate hierarchies. It works by storing all of the ancestors for each node. The leaf node is then joined to the fact table, and the ancestor joined to the dimension table ala: The contents of the ProductBridge table would then look like this: […]

EnumeratedPath

January 14, 2011

4

Continuing on our journey of variations of SQL Hierarchies we come to the Enumerated Path. These are especially useful to know, even if you don’t plan to use it for a hierarchy. By using a Recursive CTE we can enumerate the path from the root to the leaf e.g. This: Becomes: Let’s look at the […]