To master the tabular model, master DAX
The AdventureWorks tutorial for tabular models may be good for some things (especially for teaching row security), but one thing it is not good at is teaching DAX. Learning DAX is an absolute must before you can be successful at tabular modeling. One thing I get asked about is where people can start with learning DAX. Resources are scattered all over the place, so it’s hard to isolate just one to get started with. Here are some resources that I have found helpful.
Owen Duncan on the Analysis Services documentation team just put up a DAX quick start guide, which introduces some core DAX concepts in a 30 minute overview.
Then, once you have looked at the very basics, you can buy the book PowerPivot for Excel 2010: Give your data meaning by Marco Russo and Alberto Ferrari. Although marketed as a PowerPivot book, it is incredibly useful for tabular modelers. This book can sit on your lap while you mentally CALCULATE() your FILTER() context in ALL() scenarios. The book does not include all the DAX features available in SQL Server 2012, so you will need to supplement it with more material.
Once you have the book, move to the blogs. The DAX whitepaper for SQL Server 2012 is now available, which is great, but I personally find the white paper hard to wrap my head around. I find the blogs much more accessible. Once nice thing that the MSDN team did was aggregate a list of DAX blogs and learning resources on a page called the DAX resource centre. This page serves as a clearing house to DAX blog posts from people like Rob Collie, Chris Webb, Kasper de Jonge, Marco Russo, and Alberto Ferrari. I don’t suggest sitting down and reading these in one sitting, but these are good resources to have on hand if you are trying to solve problems.
Speaking of solving problems. There are a couple of problems that show up time and time again in DAX. One is time intelligence, and one is many-to-many relationships. You can get started with time intelligence using this post on time intelligence on the Analysis Services team blog. You will also want to check out Kasper’s post on what is new for time intelligence in SQL Server 2012. For many-to-many relationships, you should check out the Many to Many revolution white paper by Marco and Alberto.
Finally, a couple of videos. Last year, Kasper and Ashvini Sharma did a DAX talk at TechEd. Also, Alberto did a nice talk on many-to-many relationships at SQLBits 9. Both are well worth watching for the in depth explanations.
This should be enough to get you up and running with a DAX side project (or even a DAX real project!). Ultimately, the best DAX resource is trial and error – there is no replacement for writing the formulas yourself for your data sets. Although DAX looks kind of friendly up front, it gets pretty gnarly pretty fast. It’s worth taking some learning time on a side project to come up to speed before trying a full on implementation.
From → BI Semantic Model