I’m still getting used to it the days of many dimensions with cubes is going to migrate into tabular. I will be the first to admit its hard to let my SQL, Oracle.. data warehouses go. But using tabular you can process data and handle bad data using DAX. It lets your users use data. Its all free with Excel 2010. Download PowerPivot and see what you can do with you old data marts, data warehouses, SQL Databases Oracle Databases, Access, etc. You can use them all together and let your power users create dynamic PowerPivot sheets on their own. Using SharePoint 2010 you can look at usage and import into tabular if it the data needs to be updated on schedule. Multidimensional still has a purpose but with Tabular you can put it into memory and have the fastest results. Any small data marts I do as Tabular and huge one use Multidimensional and then tabular.
Archive for the ‘ SSAS ’ Category
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′
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.
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