I’ve been doing a fair amount of work with SSRS over the last few days, and with PowerPivot also fresh in my mind it got me thinking about the amount of overlap between SSRS and the PowerPivot/Excel/Sharepoint stack. Of course anyone who’s had to try to sell a MS BI solution to a potential customer over the last few years will have had to deal with conversations like this:
Customer: So, what is Microsoft’s solution for building BI dashboards?
Consultant: Well there’s SSRS, or if you want to build an SSAS cube you can use PerformancePoint, or maybe Excel and Excel Services, or you can go with any of these 50 third-party tools…it depends…
Customer: I’m confused already!
But just about any large software company has a certain amount of overlap between their products, that’s just life. However, that doesn’t mean that sometimes some rationalisation of products isn’t a good idea.
Let’s take a look at some of the things you’d want to do when building a dashboard, and how you can achieve them with both stacks:
| Requirement | SSRS | PowerPivot/ Excel/ Sharepoint | Comments |
| Get data from a number of different sources | Create data sources and then datasets to return the data you want | Import data into PowerPivot ‘tables’ from Excel, RDBMSes, OData feeds | There’s a slight difference between the data sources here, but the most important case is always going to be getting data from a RDBMS, which both do well.
The key difference, though, is that in general with SSRS you get data on demand through parameterised queries, whereas with PowerPivot you import all the data you’re ever likely to need up front. |
| Integrate that data | No real solution here, though SSRS developers have wanted to be able to do joins on datasets for a while. The new R2 lookup functions partly address this. | Create joins between PowerPivot tables | PowerPivot has the obvious advantage here, although for SSRS you can argue that in most cases any data integration should be happening upstream in your ETL. |
| Perform calculations on that data | Use SSRS expressions | Use DAX calculations | I’d say that SSRS expressions, while as not powerful as DAX, are easier for most people to understand; however there are a lot of things that only DAX can do. |
| Create reports from that data | Use BIDS if you’re a developer, or Report Builder if you’re a power user | Use Excel or any client tool that speaks MDX (including SSRS) | For developers, BIDS is a great tool for creating reports. However SSRS has always struggled with Report Builder – in my experience users find it too difficult. And that’s where Excel comes into its own: it’s a powerful tool and most end-users are familiar with it. |
| Publish reports to a wider audience | Deploy up to your SSRS server | Publish to Excel Services/Sharepoint | The advantage SSRS has here is that most companies have no problem with the IT department setting up an SSRS server. On the other hand, Sharepoint is a Big Deal. If your company has a Sharepoint strategy, and is planning on installing Sharepoint 2010 Enterprise Edition, you’ll be fine with PowerPivot. If not, and I guess many companies are in this position, you have a problem. |
| Export reports to a variety of formats | SSRS handles exporting to a lot of different formats | Export to Excel isn’t a problem, but other formats are a bit trickier (though doable) | SSRS has the clear advantage here |
| Schedule report refresh | Again, SSRS has a lot of options for controlling when reports are refreshed | PowerPivot’s functionality for scheduling when data is refreshed is a bit v1.0 | SSRS has the advantage again |
Anyway, you get the idea – there’s a fair amount of overlap and some things are done better by one tool, some things are done better by the other. Isn’t it, though, a bit of Microsoft’s time, money and energy to develop two parallel BI stacks? If they could merge in some way, it would mean less effort spent developing duplicate functionality and a more coherent message for customers to hear.
How could this be done, you ask? Well here are some vague ideas I had about what you’d need:
- Inside SSRS – BIDS as well as Report Builder – in addition to the existing functionality for bring data into datasets, and possibly in the long term as a replacement for it, you get the option of building a PowerPivot model to act as the main source of data for your reports. For Report Builder especially I think this would be a winner, given that the PowerPivot UI for building models is already aimed at the same power users that Report Builder is aimed at.
- The fact that you need to load all of your data into a PowerPivot model upfront is both an advantage and a disadvantage, depending on your scenario. When you know the data’s not going to change much, or you’ve got relatively small amounts of data, it’s good because you get joins and fast query performance. But if the data changes a lot or you don’t want the overhead of loading it into PowerPivot then you’d need the option to pass requests straight through PowerPivot back to your sources – so maybe PowerPivot would need something like ROLAP mode, or proactive caching, or the ability to make its tables work like existing SSRS datasets and send parameters to them.
- Include proper support for MDX queries in SSRS reports (my old hobby horse). This would involve developing a proper, fully-functional MDX query builder (not the rubbish one SSRS has right now – a standard MDX query generator across all MS products which was also available as a control for custom development would be ideal) and the ability to bind the results of an MDX query direct to a tablix (and no messing around with mapping field names to rowgroupthingies in the tablix control please). If power users didn’t have to worry about tablixes and could just build their queries as easily as they could in an Excel pivot table, Report Builder would be a much more popular tool. I think many developers would appreciate it too. Once all the data you need for your report is in a PowerPivot model, and you have full MDX support in SSRS, the business of report design is much easier. You also no longer need to join datasets because the data is already joined in PowerPivot, you have a powerful calculation language in DAX, and query performance is extremely fast. Oh, and with this functionality in place you could probably kill off PerformancePoint too and no-one would complain…
- Blur the line between Excel and SSRS. There’s been talk about being able to author SSRS reports in Excel for a long time (whatever happened to the Softartisans technology MS licensed?), but nothing’s ever come of it. Why not also have the option within SSRS to take a range from Excel Services and make that the body of your report? So your report is essentially still a fragment of an Excel worksheet, but it’s just surfaced via SSRS which then handles the refreshing and rendering to different formats.
- You’d also need SSRS to be able to schedule the refresh of your PowerPivot model, but that should be very doable; it would be great if it could refresh different parts of the model at different times. SSRS would also maintain control over report security, rendering, folders etc etc.
The end result would be that this PowerPivot/Excel/SSRS hybrid would give you the best of both worlds. I also have some ideas about how PowerPivot and SSAS should be integrated which I might get round to blogging about soon too, that would fit nicely with this vision of the future.
What are the chances of all this happening? Practically zero. It would involve the SSRS team, the SSAS team and the Excel team setting aside their differences, co-operating, and possibly sacrificing large chunks of different products. But it’s a nice thought to kick around…
That is right Chris, Powerpivot in R2 and Access Services in Office 2010 are concrete examples of Excel, Access, AS and RS teams not working together 😉
Hi Chris, What if the a customer buys a single product only and is not in a position to bear the cost of licenses of the rest of the products. For example, within my corporate budget if I can just afford just Excel and SSRS, but not Sharepoint / PPS, in that case would sacrificing chunks or merging products would help ?I agree with the point that there are great limitations in SSRS but I feel that what you are suggesting is something of the flavor of Parallel Darawarehouse where a specialized solution can be designed for specialized needs.–Siddharth(http://siddhumehta.blogspot.com)
Hi Chris,Where I disagree is that PowerPivot has real potential as an ad hoc/end user/analyst tool for folks who would never touch BIDS to build an SSRS report. As an example, I was at a client the other day and internal audit asked for security info on the ERP system. With Excel and PowerPivot I crunched the half a a million detail rows into a Pivot Table with drill down to end user task detail in about 5 minutes. In 5 minutes I can get BIDS to open and maybe get/find the right shared data source. I can teach an experienced Excel user PowerPivot very fast. SSRS takes a lot more. I think they both have their place and the overlap exists primarily in organizations that have truly invested in the full stack. Mark
Hi,It is always the question to go for an OLAP or SQL based Dasboard / Scorecard solution. If you use the SSAS components OLAP and Scorecard KPis you can handle a lot of requirements:- Scorecards- Dashboards- AnalyticsIf you use a reporting frontend like SSRS, ReportPortal or Panorama NovaView it is easy to offer a server based solution.Sample on internet:http://www.reportportal.us/reportportal/Proxy.aspx?reportId=342Please don;t forget to use the KPIs in SSAS 2005, SSAS 2008 with click thorugh to OLAP reports for more analytcs !Marco
OK, I have to admit two things: first, I was being a little bit provocative with this post; second, I wrote it in a bit of a hurry and maybe didn\’t explain myself as well as I could. I think the main point I wanted to make here was that you could potentially rip out a large chunk of SSRS – the bit that deals with datasets and running queries – and replace it with the PowerPivot engine. The benefits of doing this would be: fast query performance; extra functionality (DAX, joins between datasets); ease of use for people developing reports, especially Report Builder users, who currently struggle with getting the data they need (ie wrestling with SQL, datasets and parameters) and putting it into the format they want in the report (ie mapping that data into a tablix), because the MDX/pivot table approach is much friendlier than what we have at the moment; and less time spent by Microsoft developing overlapping features in different parts of the MS BI stack.Mixed up in there was the secondary point that it might be good, somehow, to bring SSRS and Excel/Excel Services together, maybe though the ability to design SSRS reports in Excel (why should we have both Excel and Report Builder?), maybe through using Excel Services as a data source for SSRS report. These were only vague ideas though, and probably confused the main point I was trying to make.Let me address each one of your comments individually now:Marco – I think the question of whether to use OLAP or a SQL-based approach would still remain even in the scenario above. What I was suggesting was that integrating PowerPivot into SSRS would have a lot of benefits for the SQL-based approach, even if you didn\’t want to create a full SSAS cube.Mark – did you write \’disagree\’ instead of \’agree\’? I didn\’t want to suggest that PowerPivot be taken out of Excel, and you\’re right that building a PowerPivot model in Excel is faster than building an SSRS report. I was trying to say that we could bring some of the cool stuff in PowerPivot into the SSRS report design process.Siddharth – I agree, licensing is the big problem with PowerPivot and Sharepoint 2010. Hopefully now I\’ve explained my ideas better, so you can see that my main suggestion of integrating PowerPivot into SSRS would be completely covered by the SSRS licence, so it would be a way to use PowerPivot for people who can\’t afford Sharepoint.Thierry – alright, alright, I know the SSRS team does play nicely with the other kids. But give me decent SSAS/SSRS integration and I\’ll stop moaning! And wrt to this idea being unlikely to ever happen, what I was trying to say was that it was unlikely the SSRS team would ever rip out a core part of their product and replace it with PowerPivot.
Time will tell. We have been known to surprise folks once in a while 🙂
Hi Chris, Interesting article. I do agree that there is a clear requirment to bring Excel and SSRS closer. We classify reporting needs as belowSSRS/Report Builder -> Canned ReportsPPS / SharePoint -> DashboardExcel/PowerPivot -> Ad-hoc Analysis This has been resonating well with our customers in east coast, primarily financial industry.ThanksUday Hegde
H\’ya Chris,Merging SSRS and PowerPivot won\’t amount to much in practice. The in-memory engine PowerPivot uses is not scalable enough to replace Analysis Services and SSRS is not simple enough to replace Excel. So you will end up with a product that doesn\’t actually meet any common use case. At the very least it won\’t provide any advantage of existing products that utilize in-memory data stores with a half decent reporting front end. No business case for MS to make here.EladSiSense
Hi Elad,You\’re wrong on one thing: the in-memory engine that PowerPivot uses is much more scalable than SSAS MOLAP storage, and will in time replace MOLAP. And I think the point is that PowerPivot technology would solve the problem that SSRS Report Builder struggles with, namely that end users want to design reports but can\’t design SQL/MDX queries easily. So I don\’t agree with your conclusions… but we\’ll see…!
Didn\’t think you would be 🙂 That\’s fair enough.Just look at the typical issues QlikView having been encountering for years in terms of scalability and you\’ll get a glimpse into the future of PP if it is implemented the same way.If anything, PP will replace ROLAP not MOLAP in some implementations. But funny you should mention that as I have recently written about this: http://elasticube.blogspot.com/2010/08/is-microsoft-to-admit-analysis-services.html
Hi Chris,
Is it possible to ‘blend’ data using SSRS from multiple sources at the same time to produce a single report or a multi report dashboard; I am looking at cube data, xls/xlsx data, powerpivot and flat-file at the same time.
The only reporting tool that I have found that can do this is Tableau; your table would suggest that this is not possible but clarification on this would be most helpful.
Hi Steve,
This is possible in SSRS – you can have multiple datasets from different sources (though Excel might be a problem) and if you’re using R2, you can also do some basic integration between data from different data sources:
http://blog.datainspirations.com/2010/03/19/sql-server-2008-r2-reporting-services-look-up-look-down-look-all-around-part-i/