I wrote that Chris, Alberto and Marco’s SSAS book is the most useful SSAS book to me. This week I read it again and found a lot of very useful things inside. The reason why this book is useful, in my opinion, is because it encapsulates their experience in the field. It’s not the “standard” kind of book where the authors just re-write the BOL content, i.e. how to do this, etc. It contains, decisively, their opinions and views of how things should be done, based on their experience. And believe me, if people like Chris, Alberto and Marco allow you to tap their experience, it’s worth a lot of money. These people are the best in the field! They have tons of experience.
Yesterday and today I learn from these experts:
1. When creating a cube, instead of writing the SQL queries and joins in the DSV, it is better to do it in SQL Server views. Why is using views better?
The book provides 10 reasons. I have come across this many times and can provide 3-4 reasons immediately. Still, reading the other reasons provided in the book illuminated me, of which I’m grateful to the writers.
2. SSAS cubes are populated from a data mart*. Why, ideally, we should build the mart as “star”, not a “snowflake”.
*”But we can populate it straight from the operational system!” you may say. I know, I know, I wrote about it!
3. General rules for indexing the data mart, to be used by SSAS.
This has been my particular interest for some time. Although the book only provides a short coverage, it gave me some ideas which I would treasure for the future.
4. SQL queries that SSAS generates.
I already knew about this before I read the book. I knew that dimension query was a “select distinct”. From my own experience, I knew that reference dimensions, if materialized, could provide incorrect data. I knew that fact dimension did a “select distinct” on the fact table. But still, reading this subject on this book provided me with lots of insight that I didn’t know. For example: the “order by” on distinct count queries – this knowledge is so valuable to me, very useful when optimising cube processing time.
5. Calculation dimension.
A technique that enables us to apply a calculation to many measures. We define the calculation only once but it’s applicable to many measures. I already knew about this technique, but reading the explanation from the experts themselves gives me different ideas which I can use to improve the performance of a cube. Not to be used as “date tools” but for something else. That idea is such a treasure to me so, once again, I’m grateful to the writers.
A lot people consider me as an expert in data warehousing, and in the last 2 years, in SSAS (I never consider myself an expert), but even I learned a lot from this book. My point is very simple: if you are a cube developer, cube designer, OLAP architect, or whatever you want to label yourself, buy their book and tap their experience. The knowledge in the book worth thousands times the price of the book.
If you have read it, or you think you have read it, my advice is: read it again. In my opinion, anybody who is involved in cube building needs to read this book.