Excel 2013 BI versus SSRS 2012

1) Excel 2013 has PowerPivot 2012 integrated / inbuilt into it. This means access to vertipaq engine, data processing, and dashboard related benefits. Powerpivot 2012 is able to harness the power of Windows Azure Data Market too. More on the same can be read from here.

2) Excel 2013 had PowerView integrated / inbuilt into it. This means all the reporting related benefits, visualizations like charts - graphs - bing maps, KPIs and dashboarding and other dashboard related components like hierarchies etc. PowerView is designed to interpret DAX queries on BISM model, and integration with Excel means that now Excel is even more capable of reporting BISM data models than PowerView itself. More on the same can be read from here.

3) Excel 2013 can connect to Hadoop on Azure using Hive ODBC Driver. This means excel can exploit data from cloud from related data stored in Azure databases as well as big data stores like Hadoop. More on the same can be read from here.

4) Excel 2013 facilitates sourcing media and other rich content from the web using it's newly introduced Content app and Task pane app. This means very rich integration using a relatively least complex tool compared to other self service tools available in the industry. More on this can be read from here.

5) Excel 2013 ships with some great advancements in visualizations. Users are not expected to be very deeply insighted into reports development, and components that can even make suggestions on the kind of reports suited for the kind of data they are available. More on this can be read from here.

6) Most of the applications provide features to export data into excel, which means any kind of data can be easily imported into excel. Also most of the applications provide plug-in for Excel to provide deeper integration with excel to make the data exchange process easier.

7) Excel 2013 now supports decoupled PivotTable and PivotCharts, that means better representation of trend analysis. And even Excel Services supports this feature. More on this can be read from here.

This list is not an exhaustive list of features, and I am sure there are many more features worth listing. Above listed are just those that I could remember quickly. I am not saying SSRS is a weak tool by any means and on top of that its a server line of technology and integrated with SQL Server. It has great visualizations and available in the form of service which is suitable from a scalability and architecture standpoint.

But clients with

• lots of data stored in variety of data stores
• limited funds and even limited time and patience to wait for IT to facilitate their analytical needs
• totally resistant to learn another tool claiming to be their analytics angel
• and seeking to retain the power of determining the landscape of analytics in their own hands

would definitely find Excel 2013 as their BI magnet.

If you have more Excel 2013 BI features worth listing, feel free to comment the same on this post.

Excel 2013 Visualizations with SSRS 2012 Visualizations

I see lot of data management and exploration giants busy in building bridges with big data territory driven by Hadoop. But once the mashed query results are out of heterogeneous data stores made up of relational as well as big data, humongous amount of data would be returned. Tabular or even pivotal form of data representation using grids goes almost out of question. Perfect data representation becomes even more important than ever.

In MS BI world, SSRS 2008 R2 came with a lot of visual enhancements in terms of enhancements in visualizations. But SSRS 2012 came with PowerView only in this area. I see improvements in the reporting area from a developer productivity standpoint as graphs and charts comes with granular fine tuning capabilities. But a reporting product should be mature enough to direct its users to build the right kind of reporting.

Generally a reporting team builds a reporting solution with unsuited types of visualizations for data sets. This builds perception of the user that the underlying tools / technology is incapable of delivering the intelligence in an easy manner what they need for their business. As I often say most report developers or even reporting teams don't know what graph is suitable for the kind of data they need to analyze. A component that analyzes data and suggests a visualization is almost inevitable for any reporting product to ensure that the product is getting used in correctly, which is one of the best means to increase adoption and popularity of the tool.

Excel 2013 comes with a "Chart Recommendations" functionality which is unfortunately missing in SSRS 2012. Combo Charts feature lets you combine any two combination of charts or graphs which can be very exciting. Image a Pie-chart with bar-chart in each pie !! Data labels can be as flexible as any autoshapes that you see in a word document. More about Excel 2013 Charting enhancements can be read from here.

SSRS reports can be rendered inside Excel worksheets too through a RS command call. Instead of exposing SSRS reports through reports manager, I would suggest exposing reports by wrapping it up inside Excel, where you could add more value to reports and add better UI, sometime even better than SSRS itself too.

If someone from Microsoft is reading this, I would suggest SSRS team should collaborate with DataViz team of Excel and port those newly developed charting capabilities into SSRS and release it as a cumulative update. SSRS is already bleeding enough due to its age-old parameter toolbar !

Excel Services data source in Performancepoint Services 2010, Hadoop Data Source : New breed of data sources

Data is the only currency that is generated every second in IT business and its the only currency that every business was to gather and utilize in the best possible way. BIG data and Unstructured data are creating tsunami sized data related challenges for storage, processing, as well as analysis. In the world of structured as well as unstructured data, more and more newer breeds of data sources are evolving and its good to keep a tab on these evolving breed of data sources.

We earlier heard the announcement related to connectors for Hadoop environments. In the new announcement made recently, Microsoft is now propagating Hadoop in its on-premise and cloud based platform with full integration with its regular line of products ranging from Excel to Business Intelligence stack. Hadoop, Hive, Pig etc are the new terms you would hear now in microsoft parlance too, and with this comes the new breed of data sources. You can read more about this announcement from here.

Even in the world of structured data, if you have a tab on the advancements happening in the Microsoft BI world, you would find newer category of data sources. One such example is Excel Services Data Source in Performancepoint Services 2010. Here's a tutorial on the same on Performancepoint Services Team blog. One service application acts as a data source for another service application, it is a very interesting concept in itself and opens up a new range of possibilities.

With SQL Server Denali, even SSRS would be deployed as a service application when you install it in sharepoint integrated mode. So by the integration theory we just discussed between two service applications, there is also a possibility in the future that PPS scorecards can be used as a data source for SSRS Reports, which has always been the other way round till date. With more variety of data sources, the newer challenge on the horizon is selecting the best way to source data as virtually anything can become source of data !

Excel Services Technical Evaluation : Considerations for using Excel Services in your BI solution

Technology architecture is one of the foundation for evaluating whether it would be the right choice to cater requirements of your solution. A general human characteristic is that "perception becomes reality". Flashy graphics, elegant color, super speedy slice and dice are such bullets that most business users cannot dodge, but here is where architects need to pitch-in to find the how does the entire machinery work i.e. architecture of the product. Below mentioned are certain characteristics of Excel services which one should keep in view while technical evaluation.

1) Excel services is an enterprise edition only feature in MOSS 2007 and MOSS 2010. So if you are planning to use lower editions of MOSS, this feature won't be available.

2) MOSS 2010 has REST API to support excel services which helps to bring selective data at client side. This API is not available in MOSS 2007.

3) Ability to query data from relational tables and display it in a tabular format is not supported out-of-box. With UDFs this is possible, but it would require custom .net programming, deploying the same on sharepoint. You define the functions when you author the workbook, but you see effect only when the workbook is accessed inside the excel web access web part on the moss site on which excel services is activated.

4) Standard connection methodology to connect to data sources is using ODC files stored in data connection libraries which would be configured as trusted for excel services. Dynamic parametrized queries are not supported at all in excel services. The only way to achieve a flavor of it is by using UDFs only.

5) Fetching data in a pivot table from OLAP data sources is well supported out-of-box. The report that you built out of this data can work smoothly if you have filters fields within the workbook itself. If your filter values are flowing outside your excel workbook, you would be required to use filter web parts.

6) Many features of excel which are frequently used by excel users are not supported, for ex. freeze panes, external pictures etc. So do not be in the impression that excel services is a full fledged excel on sharepoint.

7) If you intend to show data with user specific authorization, you need to contain all the data in the excel workbook that you would host on sharepoint, which in turn would be displayed to users from excel web access web part. Passing parameters to data sources from the client side through excel workbooks is not a straight-through process.

8) You can filter the data visible to end users in the workbook, but if the user chooses to edit the workbook by downloading it locally, the entire data is available to users.

9) You can define parameters in excel workbooks when they are published to sharepoint. The same parameter values can be fed in by users, and the entire report (i.e. data in the workbook) can be programmed using simple excel formulas to respond to the parameter values.

10) Excel is used as the authoring mechanism for excel workbooks and this workbook acts as the template for the data that would be hosted in this workbook from data sources. Conditional formatting is very well supported, but for any web based formatting would require using JQuery on the sharepoint page where excel web access web part would be hosted.

Few helpful links to get started with excel services:

• http://technet.microsoft.com/en-us/library/cc262899(office.12).aspx#subsection8


• http://msdn.microsoft.com/en-us/library/aa973804(v=office.12).aspx#Y2512


• http://blogs.msdn.com/b/cumgranosalis/archive/2006/03/24/excelserviceshelloworld.aspx


• http://blogs.office.com/b/microsoft-excel/archive/2008/10/15/using-office-data-connection-files-odc-and-the-dataconnections-web-part-in-sharepoint-to-specify-external-data-connections-in-newly-created-excel-workbooks.aspx


• http://office.microsoft.com/en-us/sharepoint-server-help/connect-filter-web-parts-to-excel-web-access-HA010105470.aspx?CTT=5&origin=HA010105476

Summary: My conclusion for excel services is that it's a suitable candidate when you need excel like capabilities over static data. It's a nice candidate for what-if analysis. If you intend to use it in the way you use SSRS with OLTP sources, Excel Services is definitely NOT the right choice. It's a good candidate for context-sensitive non-parameterized data, which is mostly the nature of data in OLAP sources having structures like dimensions and hierarchies. It's definitely not the right choice where you need to display data dynamically, real-time, and based on conditional parameterized querying mechanism.

Dependency analysis for estimating BI solution development efforts

Creating a WBS (Work Breakdown Structure) is the starting point to start listing your tasks for any development efforts. After a list is created, a complexity factor is introduced for each task, and based on that a generalized amount of effort for each category of complexity is allocated and the sum of the same becomes the total tentative effort. Categorization of tasks from simple to most complex is generally classified on the basis of deep understanding of the dependency analysis of these tasks.

Dependency can be of different types like dependency on tools, dependency to use a component, dependency on infra, dependency on platform, dependency on operational processes involving approval cycles, dependency on release environment (Dev/QA/APT/UAT/prod) constraints etc.

This needs to be accounted while assigning a complexity factor to even the most modular level of work item. It might sound a very exaggerated theory from the way it looks. The best way to realize this is by the way of a real life example. One of the examples from my experience that suits this flavor is "Activating excel services on MOSS 2007 and ensuring it's available on the site". It sounds a very simple item and one might not even account more than an hour for the same, and the same was the case on a project on which I was working. The issue chain started as below:

1) Firstly one needs to activate excel services on the farm, which is pretty simple (clicking just an action button).

2) You need to have a site on which you need at least a web part page.

3) You need to have Excel Service enabled for the site and site collections, so that you can use the service on the site and get Excel Web Access web part on your page.

4) When you configure excel web access web part with a workbook uploaded to some document library on your site, you need to ensure that the same should be identified by excel services as secured location. For this you need to configure trusted locations for excel services.

5) To configure excel web services related setting you need to have shared services administration site installed in moss 2007 environment, which is an explicit process done only on demand by application engineering i.e. infrastructure and application management teams.

6) To install this site, you need to have Indexer service enabled. Also you need a web application created that you can use for this shared service provider, and this web application should not be configured to use "Network Service" for the security configuration.

7) You might need excel installed on this environment, as Excel 2003 workbooks many a times creates issues and you get an error on the web part when you try to configure your web part with this workbook. Most organizations would consider installing office only on client machines, and installing the same on server would be considered as a security exception.

From the above example, it's very easy to tell that how much experience goes into dependency analysis and how important is this factor to consider during estimation. If you have similar stories, feel free to share it with me.

Excel Services : Alternative / Replacement for SSRS ( SQL Server Reporting Services ) from a User Experience perspective

SSRS is the reporting backbone in the MS BI stack. SSRS has acquired more grace in terms of visualizations by the dundas license, but there are some areas of SSRS which are highly limited in features from a user experience perspective. Any technology is perceived by the user ( who is the end client funding the technology procurement and use ) based on how well is the usage experience followed by performance, especially when the deliverable out of the technology is the face of the solution i.e. reports in case of SSRS.

In most of the environments, you would find SSRS deployed over Sharepoint. It can be either in sharepoint integrated mode, or there would be SSRS Report viewer web part accessing reports deployed on report server. Some of the serious limitations of SSRS, from the user experience perspective are as follows:

1) Parameter toolbar in SSRS report is non-programmable. Say if you have 12-15 parameters and your report returns just 10 records, it can be the case that the length of report toolbar would be equal or more than the length of the report as the toolbar would always display parameters in two columns.

2) On the report body, once the data is dumped, there is no way to filter this data. For example, in a report where I have 100 records, and I do not want to use pagination. On these 100 records if I want to filter records based on a criteria, those filters needs to be pre decided as would have to be implemented as report parameters. User's do not get flexibility to decide the filter / formula at will.

3) There is very limited amount of interactivity available on the report. For example, once report is generated at client end that is having a graph and a table of records related to it, there is almost no interaction possible on this piece of data. Users would want a what-if analysis on almost all reports where a graphical visualization that reflects some sort of comparison is used.

Considering the above points, the answer from Excel Services can be as follows:

1) Excel Services can accept parameter values from Filter web part, and you can customize this web part to a great extent. SSRS reports can also work on same theory, but programming needs to be done to pass the parameter values to SSRS reports. Even Report viewer web part can accept parameters from filter web part, but the report needs to be designed and configured in that way.

2) Excel web access web part provides all the kind of filtering capabilities that excel provides, and it easily outperforms SSRS report user experience. You can easily filter values on the report for each fields in the report.

3) One of the best part of excel services based report is it facilitates what-if analysis in a very interactive manner. User can key in parameter values which appears in a dockable sidebar, and everything on the report can change in an interactive manner without making server trips.

Whether Excel Services is better or SSRS is better, would remain a debatable topic. But Excel Services definitely wins over SSRS, in the User Experience category.

Performance testing, Hardware Setup / Sizing and Capacity Planning for Performancepoint Services , Excel Services , Visio Services and others

One thing that I extremely admire about Microsoft is that you never fall short of reference material for anything with Microsoft. This cuts down almost half of the time you spend on benchmarking and figuring out the right tool of right size for the right project.

I have often seen many companies that engage developer or even a large strength of CoE (Center of Excellence) for collecting best practice documents, carrying out benchmarking exercise, developing POCs (Proof Of Concept) for showcasing their technical strength and maintaining their readiness for upcoming or unforeseen technical challenges. But one thing they miss to see is to check if they are re-inventing the wheel. I mean that one should always check before placing efforts to extract benchmarking results, that if tests have already been done and results are already available from a genuine and reliable source, can the derivation of the targeted tests be built upon something that is already available, is there a real requirement to start from the scratch ? Some people do really enjoy doing from the scratch for whatsoever reasons, but I value my time utmost and to me re-inventing the wheel just to prove that I can build something from scratch is no good reason to waste my time. I even find many bloggers repeating the same content that is already available at tonnes of places, or try placing great efforts to discover something that is already established and available as a whitepaper. With all due respect, in my viewpoint, this just holds the value of making oneself feeling accomplished (though meaninglessly) and nothing else.

Coming to the point, Microsoft has published whitepapers for Performance testing and capacity planning of various services and features of Sharepoint 2010. Few of these whitepapers are pretty useful for a Business Intelligence solution that involves services like Performancepoint Services, Visio Services, Excel Services, Access Services, FAST Search server and others. Below mentioned is the list of download:

Business Intelligence Related

• PerformancePoint Services Capacity Planning
• Excel Services Capacity Planning
• Visio Services Capacity Planning
• Access Services Capacity Planning
• Office WebApps Capacity Planning
• Search for SPServer 2010 Capacity Planning

Other Sharepoint 2010 Features Related

• BCS Capacity Planning
• Designing Large Lists Maximizing List Performance Whitepaper
• Divisional Portal Capacity Planning
• InfoPath 2010 Capacity Planning
• Large Scale Document Repository Capacity Planning
• MySites Social Computing Capacity Planning
• SharePoint Server Caches Performance Whitepaper
• WAS Capacity Planning
• WCM Capacity Planning
• Web Analytics Capacity Planning
• Workflow Capacity Planning

Dashboard Collection / Dashboard Gallery - Design idea for Silverlight based Dashboards

I was going through one of the posts by Marco Russo on SQLBlog.com where a shortcoming ( Excel Paste Picture Link feature ) of Excel Services was explained, but I found a very interesting link to a Dashboard gallery from his post. If you are looking out for a chart gallery or dashboards gallery, I suggest that this is the page you should definitely check out. These dashboards are made out of Excel and a custom component called Microcharts, but this gives an idea of different designs of dashboards which can be quite useful when planning for a performancepoint dashboard design layout.

Moreover these dashboards are created out of Excel, so for Microsoft Excel Business Users this should be no less important than a clip-art gallery, as this provides a spectrum of design insight for designing various kind of Excel Dashboards. Also if your are planning for developing your Analytical Reports by using Excel Services, you should be able to design major parts of your reports similar to the different dashboards that can be found on this gallery.

If such designs are created for Dasboards that are based on Silverlight, they would have the additional advantage of an interactive look and feel. I would wish that such dashboard templates should be made available in performancepoint dashboard designer which should be based on silverlight.

Download Video Tutorial or Webcast on Performancepoint Services , Excel Services , REST API , Powerpivot , Sharepoint Business Intelligence

Any IT product that is used by masses cannot work in isolation, and arguably, same holds to true for SQL Server also. Sharepoint as per my opinion, is one of the biggest delivery partners for the Business Intelligence features that come out of the SQL Server MS BI stack of technologies. Sharepoint has more often been seen (in the group of people I know) as a tool for collaboration when seen from the perspective of a database or in fact a MS BI developer. Apart from being used as a collaboration platform, Sharepoint has come down a long way into the arena of Business Intelligence.

The key Business Intelligence features that are now unique to Sharepoint, which even SQL Server needs to complete the eco-system of any Business Intelligence project are Performancepoint Services, Excel Services, Visio Services which facilitate Strategy Maps, web parts that hosts SSRS and Excel Analytical Reports, and Powerpivot. There is a lot to know and learn in Sharepoint 2010 Business Intelligence Features.

How about a book that gives a higher level overview with a demo ? It might seem boring to SQL Server folks. But how about a video tutorial or webcast that takes you on a guided tour of all of these along with a demo, which is also available free of download !!! Yes, I am not kidding. This is a session presented by Mike Fitzmaurice, and this video is available for download from Channel9.

So download this video tutorial / webcast (whatever you call it) and get going on a Sharepoint Server 2010 Business Intelligence Overview. Be advised that this download is over 1 GB and contains over 1 Hr of video training. I recommend it as a must-watch video for MS BI folks.

What is Excel Services ? What is REST API ? What is Excel Services 2010 REST API ?

What is Excel Services ?

Excel Services 2007 shipped in Microsoft Office SharePoint Server 2007 as part of the Enterprise CAL. Excel Services 2010 provides real-time, interactive, Excel-based reporting and dashboard capabilities which ship as part of SharePoint Server 2010. Also it includes APIs which enable rich business application development. One such API is Representational state transfer ( REST ) API.

What is REST API ?

A RESTful web service (also called a RESTful web API) is a simple web service implemented using HTTP and the principles of REST. Such a web service can be thought about as a collection of resources. The definition of such a web service can be thought of as comprising three aspects:

• The base URI for the web service
• The MIME type of the data supported by the web service. This is often JSON, XML or YAML but can be any other valid MIME type.
• The set of operations supported by the web service using HTTP methods (e.g., POST, GET, PUT or DELETE).

More about REST can be read on Wikipedia.

What is Excel Services 2010 REST API ?

The Excel Services 2010 REST API is a new programmability framework that allows for easy discovery of and access to data and objects within a spreadsheet. The data, including charts, that is returned by the REST API is not static – it’s live and up-to-date.

With the REST API, any changes in the workbook are reflected in the data that is returned. This includes the latest edits made to the workbook, functions that have recalculated (including User Defined Functions), and external data that is refreshed. The REST API can also push values into the workbook, recalculate based on those changes, and return the range or chart you requested after the effects of the change have been calculated.

By crafting the proper URI, the REST API allows you to:

• Discover the items that exist in a workbook, such as tables, charts and named ranges
• Retrieve the actual items in the workbook in one of the following formats: Image, HTML, ATOM feed, Excel workbook
• Set values in the workbook and recalculate the workbook before retrieving items

The content above is a re-draft of the original article on Microsoft Excel Blog, and it has been re-drafted for the sake of simplicity of understanding.