Using SSRS with Silverlight, HTML5, JavaScript, Flash, Google Charts and other third party products.

SSRS, Powerpivot, Powerview, Excel Services, Powerpoint Services, Sharepoint Dashboarding all offer variety of data visualizations. But most of them end up with a static graphic image on a web browser, which is neither interactive nor appealing enough to the end users. Many architects take the route of custom development or try seeking reusable assets (third party / in-house) that can be used to bring a wow-factor on the user interface. These report platforms are not packaged with those rich interactive graphical capabilities that can blend on a portal or by itself serve the high-end UI needs.

For example, the charts and graphs that are generated by SSRS report output, are in the form of static images. There is no way that user can click on a chart/graph and it would drill down to the next level of hierarchy. Also if I have two charts/graphs and I want to configure them in a way such that I click on one of them and the other shows context sensitive information related to the same. Often reporting systems require such graphical interactivity, but the present stack of reporting tools are not yet capable of presenting such information with user interactivity.

There are different options that can be taken to provide reporting using a rich UI, which are mentioned as below:

1) Develop UI using Silverlight applications (.xap)

2) Develop UI using Silverlight + .Net or plain .Net

3) Develop UI using HTML5 + JavaScript + Adobe Flash based graphics

4) Develop UI using online charting services like Google Charts

5) Develop UI using third party tools.

A sample architecture diagram is shown below where application layers makes calls to SSRS web services. Above mentioned options would fit in one or other layer and have its own advantages and limitations.

Below listed are different chart and graph options to generate interactive charts and graphs, and blend with your UI along with SSRS reports.

1) Highcharts

2) JS Charts

3) gRaphael JavaScript Library

4) amCharts : JavaScript / HTML5 Charts

5) RGraph JavaScript Chart and Graphs

6) FusionCharts : JavaScript, HTML5 and Flash based data visualizations

7) JPowered JavaScript graphing library

8) CHAP Links Library using Google Charts Visualization

9) TeeChart JavaScript and HTML5 charting library

10) jQuery Sparklines plugin

11) jqPlot : jQuery plotting plugin

12) Microsoft Silverlight Toolkit

13) Infragistics NetAdvantage Ultimate

14) Dojo Charting

15) CanvasXpress - JavaScript library based on HTML5 Canvas Tag

16) Flotr2- JavaScript library based on HTML5 Canvas Tag

Using Microsoft Office Project Server with MS BI ( SSIS, SSAS, and SSRS )

Microsoft Office Project Server (MSPS) is one of the healthiest source of data in the microsoft ecosystem. Many departments especially CIOs have the greatest potential and probability to make extensive use of the data contained in Project Server. Almost every organizations have different projects for which they carry out planning, tracking, monitoring, resource assignments and related activities. MS Project Server is a chef's knife for this purpose.

From a technical standpoint, the way MSPS stores data is very interesting. Like Sharepoint, it stores data internally into SQL Server. But unlike Sharepoint, it gives a very neat and clean mechanism to use to data it stores internally in the form of a database intended for reporting known as Reporting database and is operated using a service known as Report Data Service. Also it has a service called Cube Build service (CBS), which can be operated using a web based console known as Project Web App (PWA).

The Reporting database (RDB) is the staging area for generating reports and OLAP cubes. Data in the Reporting database is comprehensive and is updated nearly in real time. The tables and views are optimized for read-only report generation; for example, the RDB tables are denormalized to provide redundant data and reduce the number of relational tables. As data is updated in real time in RDB, in case if you are considering extracting data from it to some other data store, consider reading how data gets to the RDB and Report Data Service. Schema documentation of the reporting database as well as the OLAP cubes is available and  can be downloaded from Project 2010 Reference: Software Development Kit, in the documentation\schemas subdirectory.

Microsot Office Project Server 2010 Architecture Diagram can be seen below:



As apparent in the above diagram, MS Project Server is very well integrated with Sharepoint 2010. Hence using reporting related tools like Excel Services, Performancepoint Services and BI + Dashboarding capabilities in-built into Sharepoint, a rich reporting platform can be provided to end users from data contained into Project Server 2010.

From an MS BI perspective,

• SSIS can be used to extract data from reporting database and merge this data into a corporate warehouse
• SSAS can be used to source and enhance cubes and OLAP database exposed by project server
• SSRS can be used to generate reports on the top of OLTP reporting database and cubes contained in OLAP database exposed by Project Server.

I seriously wish that perhaps Sharepoint can expose such databases for reporting and analysis, as that makes it very easy to facilitate reporting and analysis of the content stored in sharepoint.

To understand more about Project Server, you should consider reading about Project Server Architecture and Project Server Programmability. Also consider reading more about how to configure reporting for Project Server 2010.

SSRS Report prototypes using Google Charts

Contemplating and conceptualizing design of any application starts with almost no tools at hand. MS Office - Powerpoint and Excel, or similar products are the tools that remain available with the design and development teams for developing application screen design prototypes. MS Visio is generally used to develop wireframes. In an application / report centric design, users preference remains viewing reports blended with the web application itself. As the need for a mature prototype develops, html - css - javascript based prototype starts getting developed.

Report prototypes are generally developed as excel spreadsheets or raw reports exported to excel, which are evidently totally isolated and hard to visualize how they would gel with the hosting environment. Google charts provides excellent variety of visualizations. The best part about this visualizations are:

• Visualizations are interactive, but they don't use Silverlight. Interactive term is used very loosely but its a very valuable term. SSRS reports are rendered as an image file which is not interactive. But these visualizations have one or other form of user interaction feature available along with tooltips.
• These visualizations are generated using HTML5 so they are cross-browser as well as mobile device compatible. Also visualizations are drawn using SVG or VML.
• Embedding these visualization and populating it with data is mere couple of html and javascript tags. Visualizations are exposed as google javascript libraries. Include those libraries in your page, create objects from the exposed object model and add data in the form of a very simple array. And your report visual is ready.
• Visualization such as treemap / intensity map, motion charts etc are also available which missing in SSRS.
• These visualizations are exposed in the form of classes, and they also have event listeners. This means you can bind user interaction on this visualizations with your server side code too.

One might also think why not use these visualizations in production environments ? The main reason I would have resistance against using these in production, is that firstly these are provisioned for free on google infrastructure. So you can't commit any SLA with confidence to end-clients regarding performance. Next google publishes a deprecation policy that supports backward compatibility for 3 years. This means that once any visualization is classified as deprecated, after 3 years that would disappear from google charts. This is not acceptable in any serious production environments. If I compare this with Microsoft policies, any deprecated or even discontinued products like Proclarity / PPS 2007 have a support policy for 10 years. But of course these products are not free.

So I suggest that its one of the best tools to use to create great report prototypes that are almost closest to the actual reports. Below is a sample code to create a pie-chart.

SSRS reports on iPhone, iPad, Android, Windows Mobile for Mobile UI

Viewing applications on mobile devices might sound like a small problem statement, but the actual solution requirement is much broader than just resolution adjustment. Some of the major challenges involved in the solution architecture are:

1) Single codebase for the model and controller layer, and using the same for creating different view layers for different devices and platforms.

2) Whether to create a webapp optimized for devices or whether to create a native application that calls webservices / displays web content.

3) HTML5 is supported in different capacities by different browsers, and most of the microsoft frameworks do not emit HTML5 by default. In fact features like local storage is not supported below IE8+. Also its quite heavier to use as the data exchange format for devices, compared to JSON.

4) Whichever framework is used, cross-browser compatibility is always an implicit / explicit business mandate.

5) Using same navigation design, for different sized devices like iPhone, iPad, Tablets and Desktops, would not be admired by users from a usability and user experience perspective.

6) REST based services are more faster and lighter to use compared to WCF based webservices. But WCF has got a very wide support, features and integration with .NET.

Below are some pointers that can be kept in mind while designing the solution / technology architecture:

1) HTML5 is supported by most modern browsers used on different devices. Creating a web application with HTML5, CSS3 and JavaScript is the most advisable step if you are completely inexperienced in mobile application development.

2) Web-based frameworks like jQueryMobile, Sencha, and Dojo can leverage the existing web based SDKs / codebase to build more sharper mobile applications in faster, easier and efficient manner. These frameworks have built-in libraries to use REST and JSON too for client-server communication. Even if web application is converted native application in the future, http/rest/json based communication protocol is supported by platforms like android, iOS and others.

3) To take your web application development framework to the next level, use frameworks like appMobi, Appcelerator, PhoneGap, ApplicationCraft and others to build native applications using JavaScript.

4) More about how to build iOS application from scratch can be read from here.

I have implemented architectures, where we create .NET user controls that makes programmatic calls to SSRS webservice for reports execution. The HTML output returned by SSRS report is collected and rendered in the control. These controls are hosted in .NET pages, which are hosted on Sharepoint. Finally when server leaves control, entire web content gets transmitted as HTML. By introducing HTML5 conversion wrappers at different layers depending upon the design of the solution, not only SSRS reports but any web application can be optimized for mobile devices.

There is lot more to consider like performance, navigation design, user experience, local data storage and others such points. Feel free to share your thoughts and experiences by commenting on this post.

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 !

PowerView with SSRS 2012 Native mode and Excel 2013

Office 2013 Preview has been released and is available for download from here. More about Office 2013 enhancements can be read from here. Powerpivot has now become a native part of Excel 2013, which was available as a separate add-in in prior version. This very of powerpviot comes with few new inevitable enhancements like ability to edit tabular models. This is a very welcome news. But the news that I am not happy with is, there is also an add-in available for PowerView with Excel 2013.

After reading some of you might wonder why am I not happy with this news as this makes Excel a very powerful client tool as the reporting capabilities would get a silverlight based animatory touch. The reason is that as of this draft and to the best of my knowledge, PowerView is not available with SSRS 2012 native mode. It's a Sharepoint only available feature. In my opinion, it should be made available in BIDS as well as SSRS native mode too, in the form of an external add-in and rendering extension respectively.

Another thing that seems strange to me is why is PowerView add-in available as a COM add-in. I am not that deeply aware of office add-ins, but from a general development standpoint, from the name I understand that its a COM component. In .NET terms it's unmanaged code / component, instead of a .NET Framework based managed code. If that's the case, my curiosity asks why COM ?

An interesting outcome of this can be that Powerpivot in Excel 2013 would become the new BIDS as well as reports manager for PowerView based reports, instead of buying entire enterprise class license for better compressed reports, data alerts and powerview which are the major enhancements available in SSRS 2012 Sharepoint integrated mode.

One another such very interesting capability is provided by PivotViewer Extension for Reporting Services, but its in CTP2. But even this application is available with Sharepoint only.

Chart Chooser tool for SSRS. It's Free.

Reporting is perceived to be a very common and underrated skill, as its comparatively easy to learn. DB and well as application professionals gain grip of reports development with a short learning curve. But in my views that does not make one an insightful reporting professional. Often due to the lack of this insight many applications suffer from poor reporting abilities. A typical developer who just knows the tool might be able to drag-and-drop few controls, make data connections and deploy reports that pulls out some data out of databases.

But given a scenario that one has to develop a report that should help in trend analysis as well as distribution analysis, how many developers can answer which is the best way to do so ? Most of the professionals who have used charts and bars are aware of the operational details to use them, but not about the applicability in scenarios where the same can be useful. One needs to have some statistical knowledge to learn the same.

In today's world where one needs to be multi-talented in various skills, professionals often fall short of time to go through such a course and one might not find tools that can suggest the same easily. I often hear myself that me being an MVP I should write better blogs myself than posting links to other sites. My opinion is that there is no point in reinventing the wheel.

If you have an idea of which resource to use when backed up with clear fundamentals, you need not re-invent the same everytime. If you intend to be an SME of a particular subject, then you would enjoy doing every small things about the same subject. But if you intend to function as an architect and your job is solutioning, then you have more responsibilites than just enjoying to write code and demonstrate snippets.

So finally, here is one free tool that can help you choose right chart for SSRS reports based on the purpose of your analysis. It's called Chart Chooser, its web-based and its free too. Some concise reference manual about the applicability of charts can be read from here. There is a lot of reference manual available on the web on the use of reporting tools, but comparatively very less reference manual to teach analytics driven applicability of reporting.

How to install PowerView ? Why Data Alerts and Powerview are not available in native mode SSRS ?

This blog has been silent for quite some time. I am recovering from an accident, a few turbulence in personal life including laptop crash. I am also busy these days in my new book authoring assignment. I would like to keep the details of the book under the covers, till the time gets mature to announce it. SQL Server MVP Deep Dives Volume 2 got released last month, and I have authored a chapter on this book titled "BI solution development - Design considerations". Coming back to business, this is my blog post after a long break from blogging.

SQL Server 2012 RC 0 got released by Microsoft 2 weeks back, with a deadly bug on Distributed Replay Controller in the setup. Due to this bug I had to skip installation of the component itself. SSRS enhancements is sharepoint integrated mode are shipping two new kids - Data Alerts and PowerView which was formerly known as Project Crescent.

1) I have been receiving queries from many developers on how to install PowerView and how to verify whether PowerView is installed. The answer to this question is, if you have installed SSRS Add-in for Sharepoint products on your Sharepoint farm, consider it granted that PowerView has been installed. This component is the one responsible for installing Data Alerts, PowerView as well as the entire user interface related to SSRS in sharepoint. Keep in view, PowerView and DataAlerts are silverlight based applications and can be operated only in a browser. Simplest way to verify whether powerview is installed, is by deploying a shared dataset on a sharepoint document / reports library. Select the drop-down options for this shared dataset, and you should find an option named "Create PowerView report".

2) Why PowerView and SSRS are not available in native mode SSRS ? Question is very valid, as Reports manager is also a web based application that queries SSRS web service. If these silverlight based enhancements are intended to be made available in native mode, I don't see a technical challenge to it. But these enhancements are packed only with the add-in, which means they are strategically intended to be made available only in sharepoint. So to me it seems more of a business decision than a technical decision. Affording Sharepoint just for a client tool is too heavy, is the reaction that I have been observing from the community. Let's see how community reacts to it in the long term.

I promise my blog readers that I would try to come up to speed on blogging as soon as possible. Keep writing to me.

Data change alerts in SSRS Denali and Performancepoint Services 2010

Time is money and this philosophy turns into a requirements when it comes to reporting. RSS changed the way people access information, as one no longer needs to continuously poll the source of information to check for updates.

Reporting environments may start with a modest set of reports, but over the period of time the ecosystem may grow to a huge number of reports. In a BI environment, reports may range from dashboards showing very higher level trends and KPIs to basic operational reports filled with lots of numeric data. When the information has changed and What information has changed are two basic updates that any user would like to know, and check out the reports only then. Pushing reports at regular intervals into user's mailbox is a very inefficient practice, unnecessarily increases volume of data in an enterprise, and also creates lot of duplication. And if users have to open reports regularly and analyze whether any data has changed, even that is unnecessary load on the server resources.

Performancepoint dashboards and SSRS reports are the two pillars of reporting in MS BI stack of technologies.

1) One of the upcoming enhancements of SSRS Denali is Data Alerts in a sharepoint integrated mode installation of SSRS, which is facilitated using SQL Batch jobs that keep polling your data for changes based on the rules defined in the alerts. So users would get a notification when data has changed and only then users would have to bother looking at the reports along with clear indications of what has changed.

2) Bidoma alert is a productivity add-on for Performancepoint 2007 and Performancepoint Services 2010 from Bidoma.com. It provides alerting capabilities and reports on different constituents of performancepoint dashboards, basically scorecards and KPI data changes. More about this product can be read from here.

SSRS Denali enhancements in Sharepoint integrated mode

I apologize to my blog readers that I have been very passive in blogging these days, but I am just recovering from some personal issues and getting on track. I promise to be back on normal pace of my blogging within a weeks time.

Advancements in SSRS Denali, as heard and seen in TechEd seems to be bridging the gaps that should have been ideally filled up in the R2 release itself. But its better late than never !!

1) The first gap that is being filled up is SSRS Sharepoint integration. Though integrated mode has been supported, but implementing this mode has required a cross IT team efforts. Also its a know fact that reports deployed on sharepoint integrated mode, have been found to be performing slower compared to native mode. In my understanding, SSRS Denali brings SSRS as a shared service in sharepoint, and effectively it would benefit from all the advantages of being a shared service in Sharepoint.

On the other side, alarming situation is that endpoints that used to work for sharepoint integrated deployment, might not work in the same manner with SSRS denali. This can be a major migration blow for applications accessing reports programatically from application servers using these endpoints.

2) SSRS Logs have been very limited to ExecutionView3 tables, and rest of the help was provided by tools like Fiddler to troubleshooting. In integrated mode, logging also seems to have been considerably improved.

3) Data Alerts is one of the new enhancements in SSRS Denali, which can be thought of a SQL Scheduled Job implementation that sits in Sharepoint DB, to watch over the change in data. This sounds very good, but it looks little risky from the way it can continuously trouble database servers to check for alerts, as I have not heard about how much control is available of the frequency of alerts.

4) Crescent is another flavor of self-service reporting, requires silverlight and works in a browser in Sharepoint only. Presentation of data looks like fancy, and even controls similar to motion framework for trend-analysis are being introduced in this tool.

5) SSRS Denali is also bringing better export options like ZIP formats, support for Office 2007 based export formats, better compression, better performance in sharepoint integrated mode and more.

From a higher level, most of these are welcome changes. But from an architecture standpoint, it would be interesting to see whether sharepoint integrated mode ssrs deployment, changes architecture in a big way and whether it brings dead end to seamless migration from R2.

MS BI Architecture Design Layers - classifying layer specific logic

Any architecture design diagram is composed of several layers vertically and horizontally. Horizontal layers are discrete logical areas and their association in the diagram describes the way they are connected to each other. Vertical layers run across the entire solution and all these logical areas, which means they are applicable throughout the solution. For example, data repository can be classified as a horizontal layer and auditing can be classified as a vertical layer. This is a very well known fact and most of us would be knowing this very well.

When it comes to implementation, the association of these layers are honored and the solution is developed keeping this layers in view. But this is only in terms of how these layers are associated with each other, i.e. data and process would flow vertically and horizontally as defined in the architecture diagram. One very vital point that many miss is where to deploy your logic. Below are few logic deployment challenges or confusions that most of us would have faced as decision makers at some point of time in our careers:

1) Should logic be stored in .NET App or in DB Stored Procs

2) Should logic be stored in Stored Procs in OLTP DB or in ETL package

3) Should logic be stored in scheduled batch jobs or in ETL driver package

4) Should logic be stored in Stored Proc or SSRS Report

5) Should logic be stored in SSAS MDX Script or Client App

6) Should logic be stored in Dashboard or SSAS Cube

I have seen many genius taking their comfort route to make their jobs easy and jeopardize the future of the solution, just by deploying the logic that belongs to one layer of the architecture into another layer.

Once I had come across a scenario where one genius project manager tried to defend a solution with the argument that as the application was designed as a reporting application, entire logic is stored at the report level. This means SSRS RDLs contained the entire query logic and formatting logic within it. The solution in discussion was developed as a reporting application, and after few months down the line the requirement came up to act as a data source for other systems. As the logic was completely embedded in reports, it was not reusable at all and the solution design fell flat. Looking at embedded SQL in RDLs, any logical developer would ask, what an uncompiled SQL is doing in SSRS report ? SQL belongs to DB inside a SP and formatting the UI of the report is the report specific logic that can be contained in reports.

I have also been evident of scenarios where an application architect is in the driver seat, and the approach pursued it to embed entire logic into .NET code and treat DB as a blackbox to pump-in and pump-out data. In any corporate IT systems history you would find that application layer i.e. the User Interface / Web Front End layers are changed like changing the curtains of your windows, but corporate DB are hardly changed and whenever DBs are migrated they are a result of a large scale corporate IT systems revamping exercise.

I do not intend to hint that all logic should reside in the DB. Whatever logic that belongs to a particular architecture design layer, it should be deployed in that layer only, which is one of the implicit communication of layering in an architecture design diagram. Entire functionality can be achieved by deploying code in a single layer of the solution, but in the long term it would defeat the very purpose of layering and design patterns. Dissecting the right part of the logic in the right layer, followed by best practices of developing the layer would provide the most ideal solution from a stabilized solution design perspective.

In the field of technical architecture design, my career experience has been that each piece of logic should remain with it's deserving layer. Feel free to prove me wrong !!

SSRS Reportviewer webpart / control REPLACEMENT / ALTERNATIVE

SSRS Reportviewer webpart in Sharepoint and SSRS Reportviewer control in .NET are two major application interfaces to SSRS from the microsoft application world. UI limitations and lack of programability of reportviewer control continues to remains a challenge for the application world, as this interface visibly floats out as an odd component from the theme of application.

One of the elegant mechanism that most application environments are resorting today, is an alternative to using this control. And this alternative is by using the SSRS webservice directly. In my whitepaper "Bulk report generation using SSIS and SSRS 2008 R2", I have demonstrated how one can use SSRS webservices, which is a classic example of how much control client applications can gain by consuming these services directly.

How can applications benefit by using SSRS webservices directly and replace SSRS Reportviewer webpart / control ? The technique to implement is as below:

1) Execute your report by calling correct functions from the webservice
2) Collect the output stream in HTML format
3) Display the output on the application page.

This sounds very straightforward, but it is not free of challenges.

1) Firstly one would need to create a custom control, and then embed this functionality within this control.

2) Secondly, report output can contain images too which would be sent by the output stream without any image extension. Displaying the same would not be straightforward. You might need to stage those images to disk, or modify the IIS settings for your application. Code sample in SSRS SDK for PHP shows a fine example of how to deal with this issue.

3) You would not have parameters, page navigation or report exporting functionality, and you would have to program the same yourself too.

But if you have a die-hard requirement to blend SSRS reports with the user interface of your application, instead of abandoning SSRS or duplicating entire report functionality within your Sharepoint / .NET application just due to the limitations of reportviewer webpart / control, a better way out is to replace the reportviewer webpart / control with a custom control, and program and embed your functionality within the same.

I hope someone from Microsoft is reading this post and taking a note of this burning requirement from the application programming world.

What to use for reporting - .NET or SSRS : Application Development Framework / Report Server ?

One quality that has always helped me in my career till date is that when I come across something technical that I do not know / understand, instantly a huge amount of curiosity gets created in my mind. This has been a virtue / trait since the time I was in my academics. Due to this curiosity, our mind generates lots of questions, and this eventually makes us learn a lot as we try to find answer to those questions. With growing maturity and experience in the industry, we start taking many fundamental level things as granted and ignoring things that we do not know or understand. Our cup of mind becomes so full with the acquired knowledge, that we do not agree to empty this cup for a while to draw another cup of knowledge into the bucket of our brain. For example DBAs would want to ignore BI, BI folks would ignore Application Design Patterns, Application folks would ignore databases, still most professionals want to be a Solution Architect !! One thing that I myself struggle after approximately a journey of a decade in the IT industry, is how to sustain myself to continue thinking like a fresher or even a child who is full of curiosity and keep learning more and more everyday. One fresh member in my team asked me this question about reporting, and her curiosity made me author this post.

Though a very simple question, but the rationale behind this can be very interesting and debatable. In my opinion, you firstly need to figure out if you just intend to display data on a screen or you intend to report data. Though it might sound similar, there is a difference. If you just intend to display data, and surrounding it there would be GUI where user can manipulate data, application frameworks like .NET are best suited. And a huge .NET community would already favor using .NET for reporting any data. The main interest would be in why we should NOT use .NET for reporting data ? Though not a complete list, below are a few reasons in my viewpoint for the same:

1) SSRS Charts, Graphs, Gauges is a very unique capability that is not available with almost any other microsoft tools, apart from the ones like Excel. Visualizations are the ideal platform for reporting data, if the intention of reporting is analysis. Does .NET provide equally rich graphing / charting capabilities?

2) Using .NET one would have to hook into SMTP APIs and program to simulate the functionality of subscriptions, which is readily available with SSRS server.

3) From an architecture design perspective, if you want to follow a component model, you decouple report design from application UI as both are two different elements. It the same philosophy with which .NET came with web pages (.aspx) and code-behind files (.vb / .cs) to decouple UI and application logic. In the similar way report logic needs to be hosted and managed in its own layers.

4) SSRS reports introduces a thorough architecture that can be easily integrated and extended. You can collect SSRS reports output and still display in Sharepoint / .NET, and keep your report server sufficiently decoupled.

5) Finally, just give a thought to how would you simulate functionality of snapshots and other kind of reporting available in SSRS? You might be able to re-invent the wheel, but is it really worth the effort?

Custom Visualizations in SSRS for quantitative data analysis

Representing a dataset having hundreds of data points on a single report is not an easy job. If report is displayed in tabular or matrix format, generally the approach followed is pagination or filters. But consider that if neither of it is an option, when the requirement is to comparatively analyze all these data points, grid based data representation is out of question.

The first step to achieve quantitative / statistical data analysis is by using effective visualizations. Certain examples of these visualizations are Tree Maps, Lattice Charts, Correlation matrix, Table Lens etc. These visualizations might not be available out-of-box, but by embedding graphs / charts inside a matrix, such visualizations can be created very easily.

A data set would be having lots of data points in one or more fields, and those fields would be having many axis. For example, a dataset having sales of every month over the past five years. If you try to display the same on a graph, it would be almost impossible to represent 60 data points on the same graph grouped by different years, as this would look very messy. The solution in such scenarios is to create a graph having data for one quarter of a year, and then create a matrix of such graphs. So your result would have four small sized graphs for 4 quarters (i.e. 4 columns) x 5 years (i.e. 5 rows) = 20 small graphs. If you are using a bar graph as your base graph, the visualization that would get created would be called bar plot / trellis graphs. Other examples of such plots are scatter plot matrix, histogram plot matrix etc.

Below sample visualization is an example of how you can display quantitative data i.e. huge volume of data points on the same visualization and make the data comparable as well as analyzable. You might not believe but the below visualization contains thousands of data points plotted on it, and still this data is analyzable for its intended purpose.

Mobile Business Intelligence using SSRS in MS BI and impact on BISM

Mobile business intelligence is a growing need day by day, and with the advent of devices like Apple IPads and Samsung Galaxy Tabs, the requirement would continue to grow more and more. Long back when I was in my academics, we used to develop websites that would emit WML (Wireless Markup Language) over WAP (Wireless Application Protocol), which would be viewed on smart devices. We used to test these sites using Nokia mobile toolkit SDK. Time has changed drastically since then, and more android and symbian based thick clients as well as web based accelerator tools have emerged to cater mobile reporting needs. Mobile BI reporting is even more challenging, as this nature of reporting needs to be rich in visualizations as well as facilitate user interaction.

Blogosphere is celebrating the announcements made regarding SSAS and BISM, as the news are very positive and bright for SSAS and Vertipaq powered Powerpivot, but the set of reporting applications available as of now or on the horizon are still not that powerful. Crescent may be rich in visualizations (compared to SSRS), but not Mobile enabled to the best of my knowledge. SSRS, Powerpivot, Excel and Sharepoint Insights ( Excel Services, Visio Services, BCS, Performancepoint Services) - none of these can be made available for smart devices using any out-of-box features or technologies. The impact is that your BI solution is not mobile, and this can change the entire equation of technology selection. Just consider an example, that enterprises have online libraries for employees like Books24x7, and available over internet and even that is supported on smart devices. So if you plan to build a powerful BI and analytics application for an enterprise with maximum adoption and usability in view, with the tag of "NOT Mobile", how far one can expect the adoption and usability ?

Below is a list of a few prominent Mobile BI Vendors, but unfortunately SSRS / Microsoft is not on the list.

1) BIRT Mobile by Actuate

2) Roambi Enterprise Server (ES3) by MeLLmo

3) Microstrategy Mobile

4) PushBI by Extended Results

5) SAP BusinessObjects

6) QlikView for Mobile by QlikTech

7) SAS Mobile

8) SoftMaster Mobile Business Intelligence for Oracle Business Intelligence Enterprise Edition (OBIEE)

9) IBM Cognos 8 Go! Mobile for Cognos Business Intelligence

10) Other players such as LogiXML and Analyzer from Strategy Companion are emerging players.

Even I am happy with the announcements made by BISM, but still the weakness in reporting stack adds a concern, for which I do not see an out-of-box solution from MS BI Stack. The later Microsoft makes an entry into this area, the harder it would be to promote its adoption as clients would already had invested into other accelerators which are not Microsoft partners in this space. It's not a show stopper, but the results are obvious !! I am sure Microsoft must be having this in their vision, and if someone is reading this post from Microsoft, I would be glad to hear back their comments on this viewpoint of mine. Also please someone correct me if I am not updated in my knowhow on support for smart devices by reporting applications in Microsoft BI ecosystem.

MS BI Dashboard Design : Identifying right components for your dashboard

When you think of dashboard development in Microsoft BI world, the only name that would pop-up is Performancepoint. A general perception is that professionals who are skilled at using performancepoint are dashboard development experts, but this is a very big misconception. To develop an appealing and effective dashboard, one needs the skill of translating user requirements to reporting elements. Even dashboard ultimately is a summarized report.

One of the common issues that can be seen on dashboards developed by professionals, who do not have right kind of reporting experience, can be seen on KPI reporting. Mostly KPIs are reported using traffic light indicators, and this has built-in support in performancepoint to create a scorecard and host KPIs. But over the period of time when user start using dashboards regularly, they feel the need to see the functional detail on the KPI on the dashboard itself. For example, when the user is analyzing a KPI (Cost Performance Index, for example), user would like to check different thresholds associated with this KPI. The values of interest can be, what is actual, what was target, what were the thresholds for Green, Amber and Red indicators etc.

Using stop light indicators, you can just display the status of the KPI but you can't display thresholds associated with the KPIs. Here a PPS professional would not know the solution, and the most that PPS developer would do is show the threshold values in the tooltip, which is not a solution at all. A seasoned reporting professional would immediately suggest a bullet graph for this, which is available in SSRS. You might have gone with the option of PPS just due to the scorecard feature, and when you figure out that even SSRS with Bullet Graph would have sufficed, the earth under your legs might start shaking !! This example is a very commonly found example, and if you have worked on a few reporting projects, you must have come across this scenario.

Selecting right element for the right requirement is the key to any reporting solution, whether it may be a simple operational report or a sophisticated dashboard. So when you start thinking about your dashboard design and development using MS BI tools and technologies, you need to make sure that the skill that should be primarily available with you is a reporting expert and not a SSRS / PPS expert.

Visual Business Intelligence using MS BI

Business Intelligence has a lot of adjectives associated with it, and one that is generally not found in limelight is visual business intelligence. Those who are not aware of this term would even feel that it's just a hoax and no such category of BI exists, but many do not know that lack of attention to this form of BI is one of the major reasons why major BI projects fail. After this statement the question is, what is Visual BI and why I have never heard it in MS BI world ?

There are two groups of professionals:

1) Those who just believe in ideology of "Code is GOD". This group generally looks upon theorist as people who are dumb in knowledge as they do not deal with implementation day-in day-out.

2) Those who believe in associating implementation to methodical and design approach. Design approach is a very broad term and is not limited just to design patterns. User Experience, Information Representation, Visual Analytics does not just reside in theoretical whitepapers or books, but in practice they take a very high prominence when dealing with critical nature or volume of business as well as business users.

Cutting the theory and back to technology, visual business intelligence means how good you understand your data, purpose of data analysis, users who would be doing the analysis and based on this how you design the visual representation of this data on your reports and/or dashboards. Professionals who are seasoned with designing reports using SSRS / Crystal Reports / Business Objects / Actuate would call them reporting professionals, and provided they have a few years they would start considering them reporting analysts. In my views just as you use a reporting tool to report data from a cube, does not make you a report analyst. A report analyst role is a very key role in dashboard design and this is the role who would be mostly responsible for the Visual BI.

Visual BI is all about representing data in the best form suited to the use of the data, so that the users can make analysis using just the shape and eyes, without making any calculations to make the analysis. A report analyst would know whether to use a multivariate analysis using heatmap, or whether to use histogram for statistical analysis, or to use scatterplot for outliers analysis, or use a calculated series with line charts for trend analysis, etc. An ignorant report analyst might use 3D-charts to make the graphics appealing, but would overlook the factor that third dimension does not add any value in terms of data. If you carefully look at a 3D Bar Chart, there is a high probability that you would often get more confused while comparing bars as the slanted view does not provide enough clarity to compare size. This is where Visual BI comes to play, to make the graph useful to the extent that user would take all the required answers just by looking at the graph. For this right selection of graph for the right kind of analysis with the right kind of information representation is required.

SSRS 2008 R2 might not be as strong as it's competitors in terms of features, but with the new graphical and charting enhancements it has a lot to offer in terms of Visual BI. It depends on how informed you are on the visual design of the information and user experience aspect. SparkLines, DataBars, Interlacing effect, Pareto analysis based charts, Customizable gauges etc are highly effective tools, and provided you have the right knowledge to design information visualization using these tools, you can defeat a dashboard designed using some million dollar enterprise software suite. Visual BI is the success ingredient for an effective dashboard design, and in my opinion, one should focus and invest in learning reporting techniques than mere reporting tools. But if you fall in "Code is GOD" category, pray that the tool of your expertise survives forever !!

Free dashboard development tools and softwares

In today's budget economy, everyone wants free tools and softwares, and whosoever provides the same FREE, manages to invite a tsunami full of attention from the target audience. But I see it from a different perspective, and one point is what you want for free and why you want for free ?

I recently received a query from one of my blog readers, requesting me to share any free dashboard development tools or software for use with SSAS. In my perspective if you are having a data warehouse and/or data mart and you intend to use a free dashboard development tool, I interpret it as any or all of the below:

1) You are not serious about your dashboarding solution, so you do not intend to invest much or anything on this front.

2) Your dashboard solution is expected to be of a very modest size, so you do not intend to invest.

3) You might be wanting to get a feel of a dashboard, so you might be intending to create some POC or flavor of dashboard.

4) You want a dashboarding solution, but you are out of budget.

In case if you have a enterprise class or even SMB class data warehouse and/or data mart, right at the time of solution design and estimation, dashboarding would have been considered into your estimates. Now as you are looking for a free dashboarding tool, considering the above points, I derive that you do not need a very high-end complex professional dashboard development platform and/or software. In that case below are the options that can help you create your dashboard with your existing licenses, which would be almost free for you.

1) SSRS 2008 R2 has great UI capabilites like data bars, sparklines, indicators, charts and more. Using the same you can emulate a very rich dashboard UI. If you have an enterprise SQL Server license, you already have SSRS and you would not need anything extra to develop your dashboard.

2) .NET / MOSS Development, is one of the other options to create a dashboard. .NET Framework is free and redistributable, and there are many editors using which you can develop .NET based UI and host it on your reporting / hosting platform to emulate a dashboard.

3) Excel is also known as poor man's or business users dashboarding tool. You can develop dashboards by exploting rich UI capabilites of excel, and make the workbook available through some office viewer component to give the feel of a dashboard to your users. Most enterprises would have MS Office license inevitably and the only cost challenge would be to figure out how you would make your excel based dashboard available to your users. Also if you have Excel Web App and Excel 2010, you have a kind of collaboration platform for your dashboard too.

Performancepoint Services is definitely not free, comes only with enterprise edition of MOSS 2010, has limited customization capabilites and skilled professionals on this tool would be scarcely available in the industry. I do not intend to convey that these options can replace PPS, but considering the reasons you would be looking out for free dashboarding tool, these options should be able to cater most of your requirements. If you are on the Microsoft platform and you are in hunt of free dashboarding tools, you should check your existing set of licensed tools and you might find that your best bet is just below your sleeve !!

MS BI Infrastructure Architect - Developing readiness for the role and responsibilities

Architect is a very appealing word to all the aspiring technical minds, but most people do not realize that architect is an adjective-free role. Have you ever heard terms like SSIS Architect, SSAS Architect, SSRS Architect, PPS Architect etc.. ? If yes, then I would say that Architect word has been loosely used instead of the term SME.

The first difference between developers and architects in my vision is the broadness of domain. Most developers would stick to a technology instead of a platform, whereas this ideology does not suit the JD for an architect. If you have the ideology that "I have worked with SQL Server for 5 - 8 yrs, I am good at T-SQL programming, SSIS, SSRS and have theoretical idea of DW. And if you ask me what is Sharepoint, .Net, Webservices, Cloud, Infrastructure, Data Modeling, etc.. this is not my domain.", I would stamp "Biased MS BI Developer" on your CV. Architect requires changing many hats like Technical Architect, Data Architect, Solution Architect etc, and being an Infrastructure Architect is one such hat. If you are an Architect, many a times you would find yourself in a role where you are the Infra Architect + Data Architect + Application Architect + Solution Architect, and you might be given few technology specific SMEs for consulting. I have been in such situations as I had experiences with all these individual roles as a tech lead through the course of my career. Whatever I am sharing is based on my experiences.

When a solution encompassing application development technologies and MS BI technologies are promoted from environment to environment i.e. from dev -> staging -> APT -> Prod, this requires infrastructure estimation, capacity planning, software configuration, server connection topology etc before the environments are built. If you think that to setup such environment, one can just procure servers, add memory and rig the systems, probably you must be setting up infrastructure for solutions of very modest size. If you are developing solutions for an enterprise class client, there is a high probability that there would be a Data Center with shared application environments where your solution would be hosted.

Here comes the first lesson as well as challenge for the Infra Architect. Virtualization is the SQL of Infrastructure capacity planning. You would have to deal with infrastructure teams, who would discuss, advise and challenge your estimations and talk about technologies like Hyper-V, VCPUs, RAM, Ports and Protocols. You might be using MS BI Stack, Sharepoint and .NET Stack, Microsoft System Center and each of these would have different connectivity and hardware requirements. At a minimum you should know what MS BI stack needs in terms of infrastructure design. Ideally in a virtualization environment, development servers run on 4 VCPU, 4 - 8 GB RAM and Production servers of modest size run on 8 VCPU and approx 16 GB RAM. If you are not aware of what is a core, vcpu, ports etc you should start developing an understanding of the same.

The next major challenge you would be faced with is memory capacity planning, this mostly depends upon data and load. Application Performance Testing environments would be setup to test performance, and you should learn how to interpret the results from those environments. Testing teams would be using Load Runner kind of tools to perform a load testing, and you would be getting regular reports containing performance counters, concurrent users, memory utilization, CPU utilization etc. This is second area where would act as your profiler.

The final major challenge is allocation right amount of memory for different aspects of the solution. For example, if you have SQL Server and MS BI technologies, you need to allocate memory for logs, backups, installation, data etc. Based on this calculation you need to estimate total memory requirements and also setup designs for hosting application environments on the planned infrastructure.

You would not be practically building servers and installing softwares. In an enterprise class IT environment, there are dedicated teams for the same, but the order to march forward comes from the architect of the solution and not the architect of infrastructure teams. So at the minimum you have to create a technical architecture diagrams from infra setup to communicate your design and estimation. Being in such a role is a challenge, and fortunately or unfortunately I have been in such role and had learned a lot from the same. I hope this post brings some vision to professionals prone to such challenges. If you need to borrow my experience, feel free to drop me an email.