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.

SSIS with Informatica PowerCenter , Cognos , SAP , Netezza and others

These days I keep reading about use of SSIS with different types and kinds of databases, from the community. Some of the experiences shared by professionals from the MS BI community are really helpful and are worthy to keep in your bookmark list as you never know when you would land on a situation where you would feel a desperate need of such information.

Below is a list of such experiences by SSIS and/or MS BI professionals (including myself) who have shared their SSIS experiences with non-SQL Server or non-Microsoft based platforms.

• Comparison of SSIS with Informatica PowerCenter by Dan English
• How to use SSIS with Netezza by Rafael Salas
• Migrating from Cognos to Microsoft BI by James Beresford
• A comparison of Enterprise Class Data Integration Tools by Jamie Thomson and Michael Horne
• How to use SSIS with SAP in a data migration project by Siddharth Mehta
• Unconventional use of SSIS by Siddharth Mehta
• SSIS with Dynamics CRM by Douglas Laudenschlager
• A little dance with SSIS and Informix by Jason Brimhall

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

SQL Azure Video Tutorial and Best Practices , Database size and Billing

The way SQL Azure is being promoted and in the talks on the online media and blogosphere, it can be said that it's the most talked about subject these days. This is the right time when solution provider organizations should start developing their in-house competency on Microsoft flavor of cloud computing i.e. Windows and/or SQL Azure platform. Those who feel that cloud is something new, there is something that they need to know. It's newly introduced in Microsoft parlance by the Windows Azure platform, but there are other established players in this business since long. A nice slideshow on Top 10 Cloud Computing Leaders from Forbes.com can be viewed from here.

Now without drawing much background, I would come straight to the points mentioned in the topic of this post.

1) SQL Azure Team recently announced a very nice video from Tech-Ed which explains different aspects of data migration to SQL Azure. It's a full 56 mins video available for free viewing and download. It's such a nice free learning resource on SQL Azure, that it should not be missed.

2) SQL Express edition of SQL Azure, does anyone know this ? Check this comparison table to get an idea of the same. The Windows Azure account "Introductory Special" pricing offer can be considered as the SQL Express equivalent of SQL Azure. By signing up for this offer, it provides a web edition database of SQL Azure at no charge for limited time usage. One interesting point is that if you carefully study this table, you would find that if you are accessing from Europe / United States you get more limits compared to Asia. Asians would not be happy with this.

3) The above comparison table details would go obsolete in a short while, as SQL Azure team has announced 50 GB sized databases, and also the upper limit of web and business edition is increasing. The upper limit of web edition would be 5 GB (from 1 GB) and that of business edition would be 50 GB (from 10 GB). A nice explanation of the billing details can be read from this article.

Reference: SQL Azure Team Blog

Synchronization and management features for intelligent deployments on Azure Platform

A few posts ago, I mentioned that I am hoping that SQL Azure would flourish with time. And indeed it is flourishing with new features like increased database size, spatial data support, native support from MS Office for SQL Azure and a new web based management tool called SQL Server Web Manager.

With this new upcoming features, data would get more healthy on Azure platform and this would cause even more concerns for faster access over the wire to data hosted on SQL Azure and/or Window Azure. Data can be stored at strategic geographical locations where Azure platform have their data center installations. Data synchronization features like the newly introduced Data Synchronization service for SQL Azure or Window Azure Content Delivery Network can be used for synchronizing copies of data across different locations.

In case of SQL Azure, replicating your data at strategic locations by using the sync service, access to data over the network can be made faster by intelligent deployment strategies. I personally feel that strategic design of data deployment topology on Azure platform is the key to a robust and efficient solution design that depends on Azure platform.

Technical architecture diagrams for Sharepoint 2010 Business Intelligence

Below listed are a few technical diagrams from the technet gallery, which can be useful while planning or designing technical solutions which involves Sharepoint Business Intelligence related components.


1) Services in SharePoint 2010 Products

2) Search Technologies for SharePoint 2010 Products

3) Getting started with business intelligence in SharePoint Server 2010

4) Databases That Support SharePoint 2010 Products

How to efficiently use SQL Azure with SSIS

Microsoft has been taking high bets in cloud based computing industry with SQL Azure. Unimaginable quantity of data lies on OLTP systems, and even if a fraction of it is moved to the cloud, I cannot imagine the quantity of data migration that would have to be made. In Microsoft parlance, SSIS is definitely one great tool which has got required level of potential and support as of date to move data from SQL Server ( OLTP systems in general ) to SQL Azure.

One important point to keep into consideration, which came to my knowledge from a blog post on SQL Azure Team Blog, is that when reading data from SQL Server and writing data to SQL Azure, SSIS would cast or have to cast data to Unicode. So changing the source and destination fields to Unicode data type would spare SSIS from this effort and speed up the execution.

Those who are interested in learning how to read and write data to SQL Azure using SSIS 2008 R2, can read an article authored by me on the same subject from here.