THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

The updated Survey pattern for Power Pivot and Tabular #powerpivot #tabular #ssas #dax

One of the first models I created for the many-to-many revolution white paper was the Survey one. At the time, it was in Analysis Services Multidimensional, and then we implemented it in Analysis Services Tabular and in Power Pivot, using the DAX language.

I recently reviewed the data model and published it in the Survey article on DAX Patterns site. The Survey pattern is the foundation for others, such as the Basket Analysis, and it is widely used in many different business scenario. I was particularly happy to know it has been using to perform data analysis for cancer research!

In this article I did some maintenance on the DAX formulas, checking that the proper error handling is part of the formulas, and highlighting some differences in slicers behavior between Excel 2010 and Excel 2013, which could be particularly important for the Survey scenario. As usual, we provide sample workbooks for both Excel 2010 and Excel 2013, and we use DAX Formatter to make the DAX code easier to read. Any feedback will be appreciated!

Published Thursday, May 29, 2014 12:24 PM by Marco Russo (SQLBI)



suhail ali said:

I have implemented this pattern and it is indeed very useful. One thing to consider incorporating into the survey pattern are measures. For example, in health care you will have a patient assessment and will say score the ability for example to function from a scale of 1-10 or 1-100.  It still works within the pattern with each number in the scale is a row in the answer table and even allows for banding.  The challenge is to treat these answers as measures and not attributes. I've implemented this but not too satisfied with the complexity of the DAX statement. Hope the feedback helps Marco.

May 29, 2014 1:23 PM

Marco Russo (SQLBI) said:

This is an interesting feedback. I am not sure there is a way to define a "standard" pattern, I'm just worried that at that level, requirements might be very different and this should be adapted on a case-by-case basis. Moreover, also consider the performance implications of adopting a dynamic banding approach using only measures.

If you have more detailed example, it could be interesting for future enhancements/extensions of the pattern.


May 31, 2014 3:16 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement