Master Data Services and IIS Dependency

Before I start to explain the question in topic, below is a list of tasks summarized from BOL for installing Master Data Services.

Pre-Installation Tasks

• Verify Installation Requirements
• Configure required roles, role services and features
• Review language support considerations.

Installation Operations

• Run master data services Setup
• Allow Master Data Services Configuration Manager to open and complete post-installation operations

Post-Installation Tasks

• Create a Master Data Services database
• Create a Master Data Manager Web application
• Associate a Master Data Services database with a Web application
• Enable Web services

Now coming to the point that I am not pleased with is the point highlighted above.

1. When you want to create the Master Data Manager web application, you need to have IIS installed as it's one of the prerequisite for this application. For those who are completely blank about MDS, MDS Configuration Manager can be seen as SSRS Configuration manager and this web application can be seen as Reports Manager kind of application.
2. If you want to enable web services or you want to associate MDS database with the web application, you need to have the web application installed on the local machine on which the database exists.
3. If I consider a scenario, where I am deploying MDS in a server farm or in an environment where I have multiple servers having MDS on it, from the above two points I derive that I need to have IIS on all those machines. Even though using SSRS 2008 or higher versions, I can skip IIS dependency, if I want to use MDS, I would be again stuck with IIS.
4. My point is, if we take a look at the history of SSRS, in 2005 version IIS was a prerequisite. With 2008 and higher versions, this dependency was removed smartly. Why MDS didn't took lessons from this architecture revision of SSRS ?

I am sure there would have been some real technical challenges for hosting on IIS, but I would wish that in the next version this dependency should be removed like what SSRS did in it's release cycle. Also one more feature in my wishlist is that it should be enabled for 32-bit systems also. As of the date of draft, it's supported only on 64-bit systems. Do feel free to share your views on this with me by your comments.

1. For a thorough step-by-step tutorial on how to install Master Data Services, please read this article by MDS Team.
2. If you want to know why IIS dependency was removed for SSRS 2008, the same can be read from here.

SSIS and SSRS Integration for generating reports and sourcing data

SSIS and SSRS has got one of the best integration and this is also one of the factors that fills the gap or limitations of each other. SSRS is implemented as a webservice and has two endpoints. The limitation of SSIS is that there is no built-in way to generate reports, and technically it's an ETL tool, so report generation is not a normal / regular requirement too.

But in cases where report generation is required from SSIS, SSRS can be very well used for that. Read this Whitepaper on MSDN authored by me, which shows how to generate reports in bulk using SSIS and SSRS 2008 R2. In the solution that I have explained, I have used a parameterized SSRS report as a model to generate reports in different formats. For more, please read the article. Any programming language capable of making call to webservices can take the advantage SSRS Webservice.

The other way integration, i.e. from SSRS to SSIS is also very critical. There might be cases when one would need data from multiple data sources with a lot of transformation and every time you might not have a staging database where you would massage this data. SSIS Package can be used as a data source from SSRS using the SSIS Data Processing Extension.

Not only in the case of multiple data sources, in cases where you have the same kind of data source but you need to fetch data from more than one database server or more than one database on the same server, even in that case, using SSIS Package as a data source is one of the option apart from creating some routine in the source database for cross database or cross server data retrieval. My article on the same topic can be read from here.

BTW, In case if you have any feedback on my MSDN Whitepaper, please feel free to email me your feedback for the same.

Performancepoint Services 2010 architecture diagram

A picture is worth a thousand words, or in another words you can say that I am a lazy person. Instead of stretching ten paragraphs to describe PPS architecture, I thought of sharing this very informative diagram from BOL which describes Performancepoint Services 2010 architecture in a much better way than I can describe in words. Less words and more information, just perfect for my taste !!

Impact of SSRS in solution design

When I was a taking my baby steps in MS BI, I used to think that SSRS is not that influencing in the overall constituency of the solution. And considering the potential and capabilities of other competitive reporting products like Crystal or Business Objects, it's very easy to overlook the importance of SSRS. Over the period of time, my maturity as well as potential and influence of SSRS on different MS BI and Sharepoint components has also increased, and I believe that it would be a careless strategy to underestimate the potential and/or influence of SSRS. Below are some quick points which would justify how wide is the integration of SSRS with Microsoft and Sharepoint components.

1) Report Builder tool empower business users to create reports, and this reporting is catered by SSRS. I feel that it's the only tool in MS BI stack that has the capability of providing self-service business intelligence to users.

2) SSRS is exposed as a webservice, and for this reason SSIS and other applications that has the potential to use webservices can use it for generating reports.

3) Using ReportViewer control from asp.net, applications can view reports hosted on report server.

4) SSRS works in Sharepoint integrated mode, and Sharepoint 2010 has got a broad integration with SSRS. Sharepoint lists are even available as data source for SSRS, and the AJAX report viewer / Reportviewer webpart in Sharepoint facilitates viewing of the report.

5) Access Services in Sharepoint 2010 uses Reporting Services as it's processing engine when data from access services is published as a report.

6) Performancepoint Services 2010 can use SSRS reports as one of it's constituent elements for making up a corporate dashboard.

7) One of the best and only suitable way of representing data to the business users from a data warehouse and/or cube (in my viewpoint) is by means of a report.

So when you plan development and deployment strategy of SSRS, I feel that above points should be kept in view.

Price of SQL Server depends upon the license : Microsoft Licensing Video Tutorial

Microsoft Licensing is a book of economics in itself and to be honest I almost get confused every time I think of licensing. I see many professionals searching on the web with queries like " Price of SQL Server in India / United States / United Kingdom / Australia ". Actually the key to figuring out the price depends upon the kind of license you want to buy. Microsoft has got a lot and lot of variety in licensing, and when you are working for a business intelligence project especially on the estimation part while planning of the solution, licensing needs to be figured out not just for SQL Server but for a lot of other products like Microsoft Office, Microsoft Sharepoint, Microsoft SQL Server and others.

Most of database or business intelligence professionals do have a fair idea of the versions of SQL Server, but version defines the feature set and depending upon that the price varies. But that is just the beginning point of the licensing part. The kind of purpose for which you intend to use the product, number of machines, nature of industry, scope of use of the product and many such factors like these are applied to derive the final license that fits your needs. It's always good to have a fair idea of Microsoft licensing framework in mind as it can help greatly if you care about the pocket of your organization and effectively yours too.

Resources that explain Microsoft licensing are limited, and a video tutorial on the same is like a dream come true. And here is where you make it a reality. Download this video tutorial which is a recording of a Microsoft UK Tech-Ed where Microsoft Licensing is explained in great depth. After you have understood in-depth detailed meaning of Microsoft Licensing terminologies, it would be beneficial to download and read Microsoft SQL Server Licensing Guide which should help to derive the correct license you need, effectively helping you to figure out your final aim of finding out the price or cost of SQL Server in your country.