ssrs 2016 pivot capability over tabular models

Question

ssrs 2016 pivot capability over tabular models

stan

SSC Eights!

Points: 980
More actions
September 11, 2017 at 4:34 am

#402477

Hi, my tabular model has survey data with various dims including interviewer (employee), form, study, case #, questions etc. And the core measure called AcceptanceRate which is basically #YES / (#YES + #NO).
ssrs 2012 needed a hand crafted query to see my core measure's aggregated values at levels higher than question. Other client tools (eg excel, power bi) don't need that kind of help. They interface seamlessly with cubes and tabular models. And show aggregated measures appropriately in a "pivot table" presentation.
Will ssrs 2016 see and report aggregated measure values without me needing to handcraft crossjoins in dax or duplicate biz logic right in ssrs? Can ssrs 2016 present my data in more of a pivot table kind of mode than 2012? With expand/collapse capability? I'm pouring over "what's new" articles and see no mention of that kind of pivot table capability. If it isn't capable, how do developers deal with this? Are they forced to migrate over to other client reporting tools? Or duplicate (right in ssrs) the biz logic behind their measures?
Below is a glimpse of how excel sees and pivots my tabular model. Questions whose answers are NO are hidden.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Just looking at your presentation layer doesn't do anything to tell me how your data is actually structured, and given that there are always multiple ways to "skin the cat", so to speak, where any kind of combination of data query and a reporting tool are involved, I don't see any logical way to approach answering your question. As I don't happen to have a copy of SQL 2016 available to me at this point in time, I can't give you any specific background info, but if you can be more specific about exactly what your source data looks like, as well as your reason for needing a tabular model and what that model accomplishes for you that CAN NOT be accomplished any other way without some other kind of pain, I may be able to suggest alternative methodologies. I'm not terribly in favor of thinking that any one tool can "do it all", and in every case I've seen, that's always been a pipe dream, and for good reason. Perhaps once computers start communicating with us in English, I will revisit that concept....

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

stan SSC Eights! Points: 980 More actions · Answer 2

Thx Steve. All contributions are welcome. Below is a picture of the star. I am not committed to the star, tabular, ssrs, power bi, excel, tableau, column store index, multidimensional etc. I'm not looking for one size fits all. I'm looking at how the different alternatives play together. Especially data store technologies with visualization tools...with a keen focus on maintainability and performance. And unfortunately am temporarily unable to play with ssrs 2016. I am able to use each of the other technologies listed above along with ssrs 2012. And suspect from what I'm reading that ssrs 2016 hasn't made any strides (over 2012) in its ability to interface with data stores that do "aggregations". Reading between the lines I believe MS has something different in mind.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Well... that describes the basics about your data, but doesn't entirely convey the hierarchy or the kinds of row quantities you're dealing with. Also hidden from view is the overall objective. Just knowing you have a tabular model that pre-aggregates some data doesn't really tell me all that much about what you're looking to accomplish. When you create some kind of report, what kind of information are you looking to present? FYI, the reason I couldn't take the hierarchy from your previous post is because it refers to conceptual titles that don't necessarily have to represent real world entities. I just need a lot more detail about what you're trying to accomplish and exactly what kind of roadblocks you see as "being in the way". At the moment, I don't have a real clear picture of what you're trying to do.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

pietlinden SSC Guru Points: 63503 More actions · Answer 4

You could mock this up in Excel, then use PowerBI Desktop or something for the visuals. Sounds like a good use of SSAS tabular. The "Questionnaire/Survey" pattern is covered by Ferrari & Russo on daxpatterns.com

Then just connect to your model in either Excel or PowerBI and go to town. You could mock up most of it in Excel and then upsize as necessary.

stan SSC Eights! Points: 980 More actions · Answer 5

Thx Steve and pietlinden. I already hooked power bi (pbi) up to the model directly and saw positive results both visually and performance wise. I'm not a big fan of pbi's expand/collapse approach. I like ssrs's and excel's expand/collapse (the + and -) better.

But, I'd like to focus on one objective. SSRS 2016's ability to produce a simple report that looks like the excel pivot in the first post. Using a tabular model data source. Has SSRS 2016 made any strides over SSRS 2012 in its ability to do that kind of thing? Namely to recognize aggregated values from a "cube" and present them (probably via tablix) in that kind of presentation? Without hand crafting special dax (or mdx) queries or visualization layer based biz rule redundancy?

There are no hierarchies. The fact table has 61.5 million rows. There are close to 48k answers. There are close to 1 million cases. The data spans 5 years with 26 million of the facts in the final year and 25 million in the second to last year. There are 8200 different employees who have conducted surveys. There are 1400 studies. There are 9100 questions. There are 925 forms.

Phil Parkin SSC Guru Points: 246964 More actions · Answer 6

stanteitelbaum - Tuesday, September 12, 2017 5:44 AM
Thx Steve and pietlinden. I already hooked power bi (pbi) up to the model directly and saw positive results both visually and performance wise. I'm not a big fan of pbi's expand/collapse approach. I like ssrs's and excel's expand/collapse (the + and -) better.
But, I'd like to focus on one objective. SSRS 2016's ability to produce a simple report that looks like the excel pivot in the first post. Using a tabular model data source. Has SSRS 2016 made any strides over SSRS 2012 in its ability to do that kind of thing? Namely to recognize aggregated values from a "cube" and present them (probably via tablix) in that kind of presentation? Without hand crafting special dax (or mdx) queries or visualization layer based biz rule redundancy?
There are no hierarchies. The fact table has 61.5 million rows. There are close to 48k answers. There are close to 1 million cases. The data spans 5 years with 26 million of the facts in the final year and 25 million in the second to last year. There are 8200 different employees who have conducted surveys. There are 1400 studies. There are 9100 questions. There are 925 forms.

SQL Server 2016 Developer Edition is free, along with SSDT 2015. Why not get them installed on a dev machine and give it a go yourself?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

stan SSC Eights! Points: 980 More actions · Answer 7

thx ssc guru. I wont be able to install dev 2016 for quite a while because of the OS prereqs. I'm on W7 now. However, I am taking steps to have ssdt 2015 installed on my W7 machine. Since ssdt has a preview mode for reports, we think maybe i'll be able to do what you suggested, namely give it a spin myself. Word is that there are 1 or 2 "gotchyas" without having sql 2016 installed prior to an ssdt 2015 install but there are supposed to be workarounds and we will muddle thru them. If possible, I'll also use ssdt 2015 to upgrade my tabular model from 2012 to 2016. I encountered some bugs in tabular 2012 when trying to craft dax queries that I think ssrs will need. We have a tabular 2016 sandbox.

I've done some more reading and have spoken to a few more people. MS hasn't changed ssrs 2016 much. So there is a good chance that it will do no better than 2012 in recognizing aggregates. I may also have located a person who plumbed multidimensional to ssrs and could learn something from him about ssrs and aggregates.

I will post my conclusions here but will need a little time. It will be interesting to see the DAX that ssrs 2016 generates on its own. ssrs 2012 only generates MDX for tabular models. Early indications are that the DAX queries ssrs will require, perform terribly when compared to tabular model pivots performed in products like excel, pbi and tableau. But I used ssms and my own DAX queries to come to that conclusion. Hopefully I'm wrong.

stan SSC Eights! Points: 980 More actions · Answer 8

I installed ssdt 2015 and went thru the steps one might follow to create a cube driven report over a 2012 tabular model. I believe I am now simulating ssrs 2016 in preview mode because under help in VS the version of ssrs shows as 13.0.1601.5. Nothing has changed. You have to supply the dax query yourself. But I'm reading on the web that the tabular model has to be 2016 also in order for ssdt's report query designer to really help on dax. So I'm taking steps to migrate my tabular model to a 2016 sandbox we have and will then ask ssdt again for help in its query designer. I will post conclusions here.

stan SSC Eights! Points: 980 More actions · Answer 9

...and I just pointed ssdt/ssrs to a 2016 tabular model and there is no assistance building dax. Just mdx. And nothing that looks like any improvement in 2016 when it comes to recognizing aggregates the way pivot clients like excel, pbi, tableau etc do.