Browsing All Posts filed under »SQL«

Process Modeling – Is this a fact or a dimension?

November 23, 2011

8

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

Populating your date dimension

October 9, 2011

4

My friend and colleague Boyan Penev wrote a post recently on his blog regarding what a date dimension table should contain: http://www.bp-msbi.com/2011/09/building-a-date-table/ I’ve got a script that I’ve been using for a while to populate a date dimension table that I thought worth posting. I’ve not included fiscal periods in this script. Living in Australia this […]

Tracking History with Slowly Changing Dimensions

September 24, 2011

0

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

Dimensional Modeling Worst Practices

May 16, 2011

2

As a consultant I see a lot of BI/DW solutions with various clients, and am often called in to fix performance issues. Often times developers/architects will ignore the Ralph Kimball methodology, or try to “improve it” by breaking a few rules. So I thought it might be worthwhile for me to post some of the […]

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