I just made a new page with the basics on SQL Server Analysis Services. This is for the beginner to understand how to design a cube in SSAS. I try to build off my example of SQL Integration Services and OLTP Datamarts.
Just for best practices you should have a date dimension with a key that reflects the value of the date. That is what they say. The biggest issue I have always had is you would have to calculate it as you inserted it and all ETL would have to use the same calculation. This leads to the possibility of bad data being inserted into your key.
Example: 1/6/2012 1:34:32 pm = 201201061334
As you can see here I have cut off seconds as the minute is the lowest level I will be drilling down to.
My biggest issue has always been that you can not set date_id to be a primay key. I used a calculation on the date_id and then added a unique idex and in BIDS add date_id as a logical primary key
One thing to note if because of the way I round the seconds if I were to do a regular SQL query SELECT COUNT(*) FROM orders WHERE date BETWEEN ’1/1/2012′ AND ’1/2/2012′ I would count orders that are rounded out by the second in my date dimension. So I would loose order less than ’1/1/2012 12:00:50′ and greater than ’1/2/2012 23:59:50′. they would be rounded to previous day and ’1/3/2012′
This tool adds great functionality to Business Intelligence Studio. Dimension Heath Check, Smart Diff change control comparison. Check it out
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
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