Archive for February, 2012

Great Add-on for Visual Studio BI Studio

This tool adds great functionality to Business Intelligence Studio. Dimension Heath Check,  Smart Diff change control comparison. Check it out

Historical Fact Table

I have a new project where we need to keep history on dimensions using slowly changing dimensions and keep history of the fact table. So my fact table would look something like this.

sales_skid sales_id employee_id product_id commission type date
1 1 1 1 $2,035.00 insert 2/15/2012 9:20
2 1 1 1 $3,034.00 update 2/15/2012 11:15

 

 The problem being with this model is if you have to get the latest sum of commission you would have to get only the last record. You also cannot tell if the employee did multiple sales…

More to come

 

Ranged Dimensions

I found this very useful in situations where we have for example a fact sales amount and want to group sales into small, medium and large. I have used range dimensions with range, min, max. I then use a table like below to look up the range by using a between SQL statement

range min max
Small 1 1000
Medium 1001 5000
Large 5001 10000000

Here is a much easier way

Dimensional Modeling with Ranged Dimensions