I cannot believe that I have not posted any blog since May 2015! It’s been a busy few months for my co-workers and I, working on an OLAP re-design project. From the re-design of the ETL processes, to the multi-dimensional model, to building the dimensions, measure groups and cube, to the hundreds of pages of MDX calculation scripts, I’ve learned so much in the last few months. Not mentioning converting all the SSRS reports in MDX queries, which is the part I enjoyed a lot.
MDX is not the only skill that BI/data warehouse developers need, but it’s without doubt, many find that it is a very difficult skill to master. On the other hand, MDX is a pretty easy skill to pick up. Tomislav Piasevoli is a master in MDX. His insight in MDX will bring you from a beginner to an experience MDX writer. It is such a privilege for me to bring the original one in SSAS 2008 to SSAS 2012.
If your work touches OLAP, you need to know something about MDX. One BI/data warehouse developer once said to me that MDX books are so few that he bought every (almost) MDX book on the market. Among the few MDX books available, MDX with SSAS 2012 Cookbook is a popular book. While working on our OLAP re-design project, writing MDX calculation scripts, and converting SSRS reports in MDX queries, my co-workers and I often consulted the MDX Cookbook.
I encourage you to take advantage this one time discount. The 50% discount (with code MDXAS50) can only apply to the eBook version, and is valid through March 12, 2016. I know many of us still prefer the paper book. But the discount is hard to beat.
The MDX with SSAS 2012 Cookbook link is as follows: http://bit.ly/20qjH0a
Expert Cube Development with SSAS 2012 Multidimensional Models was published earlier this year by Packt Publishing. It’s the second edition of the very successful book on SSAS cube development by three well-known industry leaders, Chris Webb, Alberto Ferrari and Marco Russo.
This book is not a tutorial book on using SSAS as a tool. It is more of a guided tour through the lifecycle of building an Analysis Services solution with an informed commentary telling you what to do, what not to do, and what to watch out for.
Reading this book cover to cover
If you are a SSAS cube developer, you would want to read this book cover to cover, no matter what level you are, with the exception of absolute beginners who do not understand basic Analysis Services concepts yet, such as what a cube and a dimension is.
I bought the first edition a few years ago, but didn’t read it cover to cover because at the time I didn’t find some of the topics relevant to my work. Earlier this year I bought the second edition and I found myself unable to put the book down. By the time I knew it, I had already read it cover to cover once, with pages of notes in Microsoft OneNote. Knowing that my cube development skills could have progressed much faster, I wish I had read the book a few years ago cover to cover.
So don’t repeat the same mistake I made. Whether you already have the first edition or just bought the new 2012 edition, go ahead and start reading it now.
What I enjoyed about the book
I don’t wish to spoil your fun with the book, so I’ll just gloss over a few key points about the book.
- Beginner developers might think that cube development is all about how to use SSAS as yet another tool. This book will change your mind. The big chunk of Chapter 1 focused on the data modeling for Analysis Services. Then the book moved on to Chapter 2 to show you how to build basic dimensions and cubes. More complex dimension modeling is covered in Chapter 3. Data modeling for measures and measure groups is covered in Chapter 4. What I enjoyed the most is how the book presented the challenges we all encountered in our day-to-day work and provided the best practices in terms of data modeling in Analysis Services multidimensional model.
- Microsoft Analysis Services is not a standalone technology, it’s part of a family of technologies and disciplines that all work together to make it possible for end-users to do interactive data analysis, reporting, and visualization. From a developer’s point of view, these technologies include the SQL Server engine, the Reporting Services, the Analysis Services, with the Integration Services in the middle as the glue. The disciplines include, but are not limited to, data warehouse data modeling, multidimensional modeling, and designing and implementation for performance and good user experience. I personally find that being able to fit all these techniques and disciplines together in the lifecycle of building an Analysis Services solution is not an easy task. Throughout the book the authors did a fantastic job of showing how each technique and discipline can fit seamlessly to build high performance cubes.
- As a tool, Analysis Services is very easy to use; some might say too easy. Dimensions and cubes are built with various wizards with properties already being filled with default values. You can have a cube up and running in a matter of minutes. Some properties are for cube’s client tools to consume, but many of the properties are cube’s metadata and will end up having some impact on the cube processing performance, query performance, and/or storage engine performance. Assuming that your cube has started its life with a good design, then a good portion of a cube developer’s job is to understand what those impacts are and to make informed trade-off decisions. This book is a life-saving book that tells you what those properties mean, what to do with them, what not to do, and what to watch out for.
- Bad cube query performance can be detrimental for your Analysis Services projects. The book has devoted an entire Chapter 8 to query performance tuning. The concept of query performance tuning is very familiar to SQL Server developers, but cube query performance tuning methodology has its own twist and turns, such as the Formula Engine vs. the Storage Engine, the partitions and aggregations, and tuning an algorithm in MDX. The book explains in detail what to do with each methodology and even the right tools and scripts to use to get the job done correctly.
- I also like the many links in the book to other very detailed white papers, such as “The Analysis Services 2008 R2 Performance Guide”, and “The Many-to-Many Revolution”. Many blog posts are also included in the book, such as the blog posts from Mosha Pasumansky who was considered the most influential person in MDX.
No covering of SSAS Tabular models
As you may know, as of SQL Server 2012, there are two versions of Analysis Services: Multidimensional and Tabular. Although both of them are called Analysis Services and can be used for much the same purposes, the development experience for the two is completely different.
I have bought the first edition a few years ago. Although this is basically the same book as the first edition, I still went ahead and bought it because the 2012 edition has a new section that talks about the DAX query support in SSAS 2012 multidimensional model. Don’t get me wrong, this book only covers SSAS Multidimensional models. But it’s nice to have a new section on how SSAS 2012 multidimensional model supports not only MDX queries, but also DAX queries.
No substantial changes in this second edition
Since there are no substantial changes in this second edition, it’s probably not worth buying a copy of the second edition if you already have a copy of the first edition. What is covered in the first edition should work perfectly fine in SSAS 2008 and 2012, and even in 2014. This is because Microsoft has not added anything that is substantially new to SSAS Multidimensional models since the 2008 version. But if you don’t have the 2008 edition, I’d recommend you to buy this new 2012 edition, even if you are still working on cubes in SSAS 2008.
Not a book for absolute beginners
If you still need to understand basic Analysis Services concepts, such as what a cube and a dimension is, then this book is not book for you. This book does not take the form of a basic tutorial either.
Authors’ personal experience and thoughts are invaluable
Chris Webb, Alberto Ferrari, and Marco Russo are well-known in the SSAS and MDX community. This is an invaluable book because it contains their personal experience and thoughts. I myself visit Microsoft books online (BOL) very often. But if a book is solely derived from BOL then it is not too useful for me, as I can read it in the BOL myself. I am putting this review on my blog, and also planning to put it out on Amazon and Barnes and Nobel, hoping that all cube developers will read the book cove to cover.
Packt Publishing is one of my favorite tech book publishers. Their books focus on practicality, recognizing that readers are ultimately concerned with getting the job done. They also offer a subscription service, which I personally also use. Good job for putting out “Expert Cube Development with SSAS 2012”!
The book’s link is here, SQL Server 2012 Reporting Services Blueprints.
This book is a step-by-step, task-driven tutorial that goes straight to the practical development of reporting skills, explaining actions as they are taken. If you perform the role of report development using SSRS in your job and you already have a basic knowledge of how data source and datasets in SSRS work, this book will advance your reporting skill to the next level.
In order to accomplish all the steps discussed in this book, including the steps on how to integrate SharePoint 2010/2013, PowerPivot, and Power View with SSRS, it is recommended that you use SQL Server 2012 Enterprise or Business Intelligence Edition. You can still use the Standard Edition to accomplish most of the steps in the book but it doesn’t support the advanced BI features, such as PowerPivot and Power View.
To create SSRS 2012 reports, you have two options:
- You can use the SQL Server Data Tools (SSDT). SSDT is Visual Studio shell which is an integrated environment used to build SQL Server database objects. You can install SSDT
from the SQL Server installation media.
- You can also use Visual Studio 2012. If you use Visual Studio, you must install the SSDTBI templates. SSDTBI (SQL Server Data Tools-Business Intelligence) is a component that contains templates for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) for Visual Studio 2012.
To try the examples in this book, you will need to have 2012 AdventureWorks Sample database.
In Chapter one you will learn right away different tricks on how to implement parameters for
different scenarios, ability to exclude one or more parameters at runtime, drop-down parameter, multivalued parameter, cascading parameter and cascading multivalued parameter.
I really appreciate Chapter 2 in which a section is devoted to create custom report template. Report templates not only save time but also creates visual consistency that suit your organization branding. Number crunching is really all about data summaries, aggregates, and groupings. Through practical examples in Chapter 2, you will be able to pick up the skills very quickly.
Another essential skills in creating SSRS reports is to be able to add interactive elements, such as Actions, Tool tips, Document Map, Sorting and Bookmark to a report. In SSRS, one report can also interact with another report through Subreport, Drillthrough report or linked report. You will absolutely love Chapter 3 which focuses on adding actions to SSRS reports.
Have you ever wondered if SSRS is also a good tool for data visualization? In SSRS 2012, in the report designer toolbox, you will find a few report items that are new, they are, Data Bar, Sparkline, Indicator and Map. Chart and Gauge exist in the prior versions.
Chapter 4 will show you how to create reports in SSRS 2012 with data bars, sparklines, gauges, and indicators. Next time if you need to meet the common data visualization requirements, you will find that this book can make your data visualization work seem effortless.
I particularly like Chapter 5 which goes right into map visualization. It not only shows how to use the built-in maps for the United States, but also how to import and use commercial shapefiles (map files) to create world map report.
If you are tasked to develop reports based on the cubes in the OLAP, Chapter 6 will show you how to survive simple implementation just with the query designer in SSRS.
The last chapter in this book goes beyond just report development. It will show you how to get your SSRS reports ready for production. You will find practical information on deployment configurations, user security, shared schedules, cached reports, and snapshots. It also dives into report subscriptions with e-mail delivery / scheduled delivery and data-driven subscription.
Overall, this SSRS 2012 blueprints book is a well written book and worth buying if you just start out developing SSRS reports in 2012. If you started with the prior versions and already are an experienced report developer, then this book might not be right for you. However, if you are like me and new to the data/spatial visualization features in SSRS 2012, you will find very useful information in this book on how to use the data bars, sparklines, gauges, and indicators and map objects.
Congratulations to Marlon Ribunal and Mickey Stuewe on their first SSRS book. And to reviewers Basit A. Masood-Al-Farooq, Varinder Sandhu, SIVAKUMAR VELLINGIRI and Valentino Vranken. Their combined experience in SQL Server Reporting Services report development makes this book a valuable addition to every SSRS developer’s library.
In this blog, I’d like to give special thanks to the people below who have taken time to write reviews for my book MDX with SSAS 2012 Cookbook.
“It helped me a lot in my projects and helped me advanced my MDX skill in a very short time.”
“What I like about the cookbook style is the scenario and solution design. ”
“The structure of the books is very similar and is again very easy to follow.”
"This book is a must have. I have struggled trying to find a book with good illustrations and easy to follow samples UNTIL this book!"
“I recommend this book to anyone regardless of MDX exposure, really great to have a reference like this book when you are trying to work through tough requirements."
"MDX with SSAS 2012 Cookbook" has a good chapter and topic organization which starts simply, but not too simply, and builds in complexity. "Elementary MDX" sets the stage for the expected level of knowledge with the other chapters building from that knowledge base. I particularly liked the chapters on "Working with Time", and "Business Analytics" because the regression and non-allocated expenses are tough problems for MDX newbies to solve."
In the blog below, I’ve given my thanks to Vincent Rainardi for his quick reviewing.
I came across Vincent Rainardi’s blog, and thought I’d share it with the readers.
I’d also like to thank Vincent for writing up the review so quickly.
I browsed through Vincent’s blog, and found that he authored this book:
Many reviews praised that it is an excellent book on data warehousing that combines simple and practical explanations and "how to" for all the key aspects of building a data warehouse on SQL Server.
MDX with SSAS 2012 Cookbook
by Sherry Li and Tomislav Piasevoli
Packt Publishing 2013
The book is now available in both paperback and eBook format. You can order copies from Amazon, Barnes & Nobel, Packt Publishing. It is also available on Safari Books Online.
Hope you will find the book useful in mastering MDX.