Tell Me About Your SSAS Environment: Results

This post is a follow up to the survey I created back in late March. Thank you to all of the participants who took the time to fill out they survey – your input is greatly appreciated!

Instead of digging into the data and spoon feeding you the results, I’ve created a Power Pivot model (powerpivot model, raw data) that you can use to explore for yourself. I’d also like to invite you to share any interesting insights you uncover in the comments section and/or provide feedback on this little survey experiment (missing questions, phrasing, etc).

The rest of this post will focus on how to explore the Power Pivot model.

Survey Data Model

The Power Pivot workbook is based on the survey data model discussed in the Many-to-Many Revolution 2.0 whitepaper and demonstrated by Alberto Ferrari in his PASS Summit 2014 session, Advanced Modeling with Analysis Services Tabular.

The beauty of this data model is its flexibility. Not only can it store any combination of questions/answers, it also allows you to probe the data at a very high level as well as zoom in to a very precise level of detail. That said, if your questions and answers never changed, you’d probably use a more specific data model.

In any case, here’s a quick demo of how to use it…

Let’s say for example, we start with the following pivot table layout and take high-level look at the breakdown of responses to each question:

Perhaps we then want to explore the 5 (out of 50) respondents who are spending 5-10 hours per week troubleshooting SSAS issues – that is, after all, quite a bit of time to be spending each week on Analysis Services issues.

No problem, just add a set of Question/Answer slicers based on the next Filter-table (in this case Filter2) to apply the desired filtering:

From this view, we see that of those 5 respondents who are spending between 5-10 hours per week, 3 of them are not using a third party monitoring solution.

Let’s see if those 3 respondents are capturing and reviewing information about their query/processing workloads…

Wrap Up

By now you should have a pretty good feel for how to navigate this data model – asking questions and discovering insights. Let me know what you find! And please feel free to also leave feedback on the actual survey itself (good/bad/ugly) as well as any questions you’d like to see on future surveys.