BI Project Manager, Tel Aviv Municipality
Ella Maschiach is a System Analyst and OLAP Developer. She has planned and developed numerous BI ... more>>
Reporting Services 2012 and Report Builder 3.0
Reporting Services 2012 and Report Builder 3.0 give you the ability to develop static reports for your user. (Though Report Builder 3.0 was marketed as tool for the power user, I do believe that it is more for developers. Even with a good UI you may still need to know T-SQL).
Both Reporting Services reports and Report Builder 3.0 reports give you the option to share report parts and datasets and re-use them in other reports, show data on maps, use rich visualization such as sparklines and databars, calculate aggregates of aggregates, render reports to data feeds and so much more.
They also offer export option to Excel and Word 2007 onwards removing the limitation of 65K rows. Finally, they also offer data alerts, but that’s only relevant for installations integrated with SharePoint.
Still, Report Builder 3.0 and Reporting Services 2012 differ on one basic point – their installation. You can download Report Builder 3.0, with no need for an SQL Server license or a Visual Studio License for that developer. For Reporting Services 2012 you do need one of them.
The flip side?As Report Builder is not a part of Visual Studio, the Report Project you develop can be saved on your computer, but cannot be tied to Team System. For Reporting Services on the other hand, you can save your Report Project through Team System and have a version history.
Analysis Services Multidimensional,Tabular Mode and PowerPivot
In SQL Server we find Analysis Services on its different parts stretched to the max. all of them offer the option to develop a solution giving the option of ad – hoc reporting, each with a different advantage and disadvantage.
I’ll start with the most “basic” of all applications – PowerPivot. PowerPivot is an Excel add – in that enables either you the developer or your power user to create a model in Excel based on several data sources. It uses DAX for advanced calculations. What do I see as a minus in this application? First of all, the fact that you again need SharePoint to have it shared in your organization. Otherwise, it’s another Excel file sent in the mail between different people also with no version history on the changes made in it. Even in if used in SharePoint, security is defined for the entire workbook and not for a specific table or row.
Tabular mode is new to SQL Server 2012 and is quite similar to PowerPivot. The first and main difference between them lies in the fact that Tabular mode is developed in SSDT and requires an SQL Server license or Visual Studio. This again enables you to do version history with Team System. It also enables you to develop much larger models as it is not limited to 2GB file size limit in SharePoint (relevant for PowerPivot). It also has Direct Query Mode and xVelocity mode for ROLAP or MOLAP like modes in Tabular models. Last but not least, it has row level security.
There seems to be more changes ahead for all of the above with the release of Excel 2013.
Finally, Analysis Services in its Multidimensional version is the cube we know from 2005 also developed in Visual Studio or SSDT. New advantages to SSAS 2012 are amongst other: Distinct Count performance improvement in ROLAP mode, support for NUMA and more than 64 processors, new Profiler events and more.
In comparison to PowerPivot and the Tabular mode, SSAS uses MDX as a query language, enables role playing dimensions, visual totals and translations, if to name but a few. Most importantly, Multidimensional Analysis Services has many reporting tools, such as Excel, PerformancePoint and other third party tools.
A new option for SQL Server 2012. It enables you to develop ad- hoc reports for your users based on data models, replacing Report Builder 1.0 which has been deprecated. PowerView offers great visualizations and an easy to use UI. Power View is a browser-based Silverlight application to display a Tabular model or a PowerPivot model that creates rdlx reports.
What’s the downside? Again, this new application runs only if you have SharePoint 2010 Enterprise Edition.
Experts on BI