Microsoft ignored the Power of Report Model

When Microsoft announced the SQLServer 2012  features,  There was  a shock for   SSRS2008 or earlier version users. The bad news is that Microsoft is going to discontinue Report Model approach in SSRS 2012. There will be backward compatibility so that old report model will work fine. But you can't create new report model using SQL Server 2012 technologies. That means  Report Model feature will be discontinued in future.

 Report Model approach is  relatively least popular technology from SQL Server Reporting Services. Business users know what information they need and how their report should look, but they usually don’t have the technical skills necessary to create a rich report design, to understand real-world database schemas, or to use formal query languages such as SQL. In contrast, IT staffers understand report design, the technical aspects of the data source schema, and formal query languages, but they are not available on demand to analyze, design, and deploy urgent or one-time reports. Therefore, businesses need a reporting solution that enables information workers to understand and  create reports on demand in short term without the dependency of IT staff.

Report Model Approach creates  a metadata layer on top of the database schema and preparing it to present the most-frequently requested data in the most intuitive and accessible manner by using business taxonomy. The metadata layer will then translate report design, filtering, and parameters into the correct and optimized native queries against the underlying physical data source.

Since Report creation using Report model is well integrated into Report builder, This feature allows basic business user to create simple reports using report model and Experienced developer can create complex report using SQL from the same SSRS application.

Even though SSRS 2012 provided Web UI for SSAS Cube reporting, there is no user friendly solution for Report generation from RDBMS.

But real power of Report model technology in SSRS is from some other least known area. Let's see about it.

Report Creation in Cloud Environment

Now world is moving towards cloud . Application servers, Database servers etc, are moving to cloud. creating Report from cloud hosted Database Server is a major challenge  As SaaS is gaining popularity with multitenant architecture, Real world problem of report creation from multitenant cloud hosted database is unimaginable.

 In cloud hosted application, Reports needs to be created using some web app or through click once application. Fortunately SSRS provided Report Builder tool which is a click once application. All the access to SSRS happens through web services. It is possible to make the protocol as https.

One of the primary challenge is the access to the database from Report generation tool. Normally ODBC,OLEDB and other connectors connects SQLServer database using 1433 port.If the application is hosted in cloud and if it is shared by multiple applications/customers, It is not practically possible to open 1433 port . You cannot open the Database server to "Public". Most of the Reporting tools access the database using ODBC/OLEDB or other access through 1433.

As per Microsoft, SSRS provided 3 Tier architecture even for report creation. Client Application always contact SSRS App Server and SSRS Report server contact database. This is the architecture that we want. But It is not happening. SSRS issue in this architecture  is discussed well in this post.

Then how to make a non-1433 port access to design/create the report from Report builder. Here is the power of Report builder comes. All the calls made from Report builder to create Report Model approach requires http or https port alone. It doesn't require 1433 port access.

Even though Report Model creation needs some overhead in metadata creation and maintenance , At least this approach solves one major technical problem in cloud . Of course, There are lots of bugs and usability issues with Report model based Report creation , Because of the above advantage, lots of Cloud users tends to use Report model. With The release of SQLServer 2012, Microsoft  announced its intention to stop supporting Report Model without providing proper alternative for RDBMS based reporting.

It is the time for new vendor to come up with reporting product which can design/create report without 1433 port access. If someone comes with above feature with multi tenant RDBMS support, He will rule Reporting Products world in the future.