Browsing All posts tagged under »SQL Server«

SSRS Sparkline report using MDX

March 23, 2011

4

In this post I am going to describe how to create a report with Sparklines using an MDX query. In my current role I do a lot of Proof Of Concept pre-sales work for different industries, and I’ve really grown to love sparklines. I think they are fantastic in giving users an understanding of how […]

Data Modeling Conundrum Follow up

March 17, 2011

9

As a follow up to my previous post on a Data Modeling Conundrum in January (see https://jsimonbi.wordpress.com/2011/01/16/data-modeling-conundrum/) 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

2

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

2

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

1

I have been testing the previous example code (see https://jsimonbi.wordpress.com/2011/01/25/populating-a-many-to-many-bridge-table/) 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 […]

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