Hadoop tools for SSIS, SSRS and SSAS like Integration, Reporting and Analytics

Data hosting, processing and reporting is dramatically changing on a variety of extremely different platforms than ever. With the emergence of NoSQL and Big Data, systems such as Hadoop host unimaginable volumes of data. Google is soon to hit 1 Billion Android device activations. US and China collectively contributes to almost 300 million iOS + Android activations. Sourcing data from systems like Hadoop, mashing it up with relational data sources and provisioning reporting and analytics on the most aggressively growing platforms like Android and iOS is not an easy job, leave apart the complexity, cost and skills involved in the process.

Recently I have seen quite a couple of SQL Server and MS BI related blogs writing about how to write code for HBase, Pig and for other similar sources. Industry matures in terms of developer productivity and user friendliness much aggressively than one knows. Talend - an open source provider of tools for managing Big data, provides a tool called Talend Open Studio for Big Data. Its a GUI based data integration tool like SSIS. Behind the scenes this tool generates code for Hadoop Distributed File System (HDFS), Pig, Hbase, Sqoop and Hive. This kind of tools really take Hadoop and Big Data to a extremely wide user-base.

After you have the ways to build a high-way to a mountain of data-source, the immediate need is to make meaning of these data. One of the front-runners of data visualization and analytics, Tableau, provides way to create ad-hoc visualizations from extracts of data from Hadoop clusters or straight live from the Hadoop clusters. Creating visualization from in-memory data and staging extract of data from Hadoop clusters into relational databases and creating visualizations from the same; both are facilitated by Tableau.

Other analytics vendor like Snaplogic and Pentaho also provides tools for operating with Hadoop clusters, which does not require developers to write code. Microsoft has an integrated platform for integration, reporting and analytics (in-memory/olap) and an IDE like SSDS (formerly BIDS).

If tools similar to Talend and Tableau are integrated into SSIS, SSAS, SSRS, DB Engine and SSDT, then Microsoft is one of the best positioned leaders to take Hadoop to a wide audience in their main-stream business. When platforms like Azure Data Market, Data Quality Services, Master Data Management, StreamInsight, Sharepoint etc join hands with tool and technology support integrated with SQL Sever, it would be an unmatched way to extract intelligence out of Hadoop. Connectors for Hadoop has been the first baby step towards this area. Still lot of maturity in this area is awaited.

Till then look out for existing leaders in this area like Cloudera, MapR, Hortonworks, Apache and GreenPlum for Hadoop distributions and implementation. And for Hadoop tools, software vendors like Talend, Tableau, SnapLogic and Pentaho can provide the required toolset. 

Data warehouse certification , Business Intelligence and Analytics certification

In Data warehousing and Analytics, lack of standard certifications have made it very hard for recruiters to identify "A" league professionals from impostors. When the topic of certification comes, there are basically two questions, why to get certified and how and what to get certified. I would first answer what and then answer why. 

Irrespective of technology, The Data Warehousing Institute (TDWI) has been a provider of Certified Business Intelligence Professional (CBIP) certification where professionals can choose their core skills and appear for the exam that suits the same. The flip side that professionals see to it is that it's not associated with any product in specific and just theory can be good at Architect profile but at career levels where professionals need to implement solutions this might not be able to impress recruiters with this certifications.

Being a Microsoft patron, I would discuss about certifications related to MS BI. Microsoft has recently introduced two certifications for Data Warehousing and Business Intelligence.

1) Exam 70-463 : Implementing a Data Warehouse with Microsoft SQL Server 2012

Skills Measured:

• Design and implement dimensions
• Design and implement fact tables
• Define connection managers
• Design data flow
• Implement data flow
• Manage SSIS package execution
• Implement script tasks in SSIS
• Design control flow
• Implement package logic by using SSIS variables and parameters
• Implement control flow
• Implement data load options
• Implement script components in SSIS
• Troubleshoot data integration issues
• Install and maintain SSIS components
• Implement auditing, logging, and event handling
• Deploy SSIS solutions
• Configure SSIS security settings
• Install and maintain Data Quality Services
• Implement master data management solutions
• Create a data quality project to clean data

2) Exam 70-467 : Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Skills Measured:

Keep in view this exam has approx 30% weightage on designing and planning of BI Infrastructure.

• Plan for performance
• Plan for scalability
• Plan and manage upgrades
• Maintain server health
• Design a security strategy
• Design a SQL partitioning strategy
• Design a backup strategy
• Design a logging and auditing strategy
• Design a Reporting Services dataset
• Manage Microsoft Excel Services/Reporting for SharePoint
• Design a data acquisition strategy
• Plan and manage reporting services configuration
• Design BI reporting solution architecture
• Design the data warehouse
• Design a schema
• Design cube architecture
• Design fact tables
• Design BI semantic models
• Design and create MDX calculations
• Design SSIS package execution
• Plan to deploy SSIS solutions
• Design package configurations for SSIS packages

Why to get certified on Business Intelligence platform ? There are two groups of people divided by faith in certification, one who believes in certifying and benchmarking their skills, others who do not feel there is any value in investing and getting certified.

1) For those who are not in the favor of certifications, the prominent reasons are:

• Product version changes after few years and the certification is seen as outdated by recruiters
• Software required for the same for practicing is not available easily
• Many professionals indulge into malpractices, use exam dumps and pass exams with full score even without any knowledge of the subject
• Its hard for them to self-study and prepare for certifications, and they don't have enough resources to invest into a professional training programme.

2) For those who are in the favor of certifications, the prominent reasons are:

• Certifying your skills with changing product versions reflects your attitude to your employer, about how seriously you take your skills that earns your bread and butter.
• With cheap developer editions coupled with free virtualization software like VirtualBox and readily installed images in VHD format, resource management is possible for them.
• Professionals who pass exam using corrupt methods are digging a backfire gunshot for themselves as they are raising expectations from them, and inviting their interviewer to screen them more thoroughly as they are certified professionals.
• Those who can't self-study in IT and can't manage in investing for resources and training programmes for themselves, have already surrendered to the thought that one or other day they would go obsolete in technology. And delivery management or other avenues are right for them than remaining technical. Even in that area, certifications like ITIL or PMP or PGMP would be required.
• Certifications adds bargain power to your resume to negotiate better for your skills as you go up the ladder. With a couple of years of experience and few certifications you can't ask for 1.5 times the salary that your peers get paid, but the benefit is when your experience meter hits double digit in number of years, you won't be appearing your first ever certification at that age and experience !!

Summary: Invest in your career. Many would ask what certifications have I appeared till date. I am certified MCSD in Microsoft .Net, MCTS in SQL Server Implementation and Maintenance, MCTS in Business Intelligence and MCTS in Performancepoint Office Applications and now working as a senior architect with Accenture Services Private Ltd at Mumbai office. Still I am interested and positive to take up above mentioned certifications.

MS BI and Hadoop Integration using Hadoop Connectors for SQL Server and Parallel Data Warehouse to analyze structured and unstructured data

Not-only SQL (No SQL) is ruling the world of unstructured data for data storage, warehousing and analytics, with Hadoop being the most successful and widely used technology. There are two choices you can make when something is gaining immense acceptance: either you can abandon and keep competing with your own league or you can partner with it and extend your reach deeper. Microsoft is without doubt one of the leaders in database management, data warehousing and analytics apart from IBM, Oracle and Teradata, but on structured data only. Microsoft Research is trying to churn out its own set of products to deal with BIG data and unstructured data challenges, using federated databases capable of MPP. But Hadoop has already earned a proven reputation and acceptance in this world of unstructured data.

The good news is that Microsoft is embracing Hadoop environments slowly and adopting a symbiotic policy. No organizations would have exclusively structured data or exclusive unstructured data, it's always a combination of both. Azure platform is already support Hadoop implementations. Recently Microsoft announced an upcoming CTP release of two new Hadoop connectors for SQL Server as well as Parallel Data Warehouse. Many visionary DW players are already offering a hybrid BI implementation that allows to use MapReduce (used to query data from Hadoop environments) and SQL together. With the release of Hadoop connector for SQL Server, its highly probable that SQL Server becomes a source for Hadoop environments rather than vice-versa as the ocean full of unstructured data sits in Hadoop environments which is nowhere in the reach of SQL Server to accomodate.

Still the interoperability facilitated by this connector, would empower SQL Server to extract data of interest from this ocean of data hosted in Hadoop environments, making MS BI stack even more powerful. Database Engines, ETLs as well as OLAP Engines would see bigger challenges than ever when clients start using Hadoop as a source for SQL Server, but my viewpoint is that it would mostly work other way round. These connectors are opening a door to the possibility where SQL Server based databases as well as data warehouses can/would be used in combination with Hadoop and MapReduce, effectively creating new opportunities for the entire ecosystem of database community from clients to technicians.

Its too early to know the taste of the food before you actually taste it, but you can predict about the taste from the flavor, and that's what I am trying to do as of now. You can read the announcement about these connectors from here.

Infographics in a BI Solution

These days my interests have been converging a lot on subjects related to extracting BI from unstructured data. Editing a few properties, learning a few functions, developing few queries for faster data access, authoring colorful reports and more tasks like these make many professionals feel so accomplished that they no more feel to broaden their horizons. In my personal opinion, such are good for professionals whose scope are limited to being product practitioners / testers / salesmen / market makers (I mean evangelists). I am sure that those who envision the world of IT, find these limits too short to contain their potential. Enough of thought work, lets get back to business.

Have you ever thought that if you would try to transform unstructured data into semantic data, and intend to report the same data, how would you report it? SSRS Reports are generally used for operational reporting and Dashboards are generally used to performance monitoring and measurement, know as CPM (Corporate Performance Measurement). But for example, when you try to structure data from a source like Wikipedia which is completely unstructured data, create intelligent structures to mine data, and intend to report the same, what would be the ideal way to report the same. In my viewpoint, intelligence extracted out of such data, can be effectively reported using Infographics. If you generally watch some Infographics gallery, you might find some dilbert kind of cartoon symbolizing some meaning. But Infographics are a very powerful way of reporting and gives you the flexibility to report data in a way that no dashboard / visualization can deliver.

Don't believe me ? Below is an example. Study it carefully and you would become a fan of Infographics. Data mined from unstructured data source can very well generate content to extract intelligence reported in the below infographic.

MS BI for Time Series Analysis, Deviation Analysis, Regression Analysis, Correlation Analysis and Distribution Analysis

Few questions are very important to get answered before anyone dives into the sea of business intelligence. You would find many professionals who cannot make out the difference between reporting data and business intelligence. I recently met one senior professional having approx 14 years of experience, and he was not clear of what is a data warehouse. He used to consider anything that stores data in the form of dimension and facts tables is a data warehouse, which is completely incorrect. The biggest drawback of this lack of understanding is that one cannot understand the life-cycle of how an organization gets BI ready.

I define BI readiness in 4 steps : Data Intelligence, Reporting Intelligence, Integration Intelligence, Business Intelligence. You can read more about this in my article titled "Select a BI software with full knowledge of what it does".

Now say that you understand the importance and concept of gaining organizational data maturity to harvest Business Intelligence. But the next question is that if an organization wants to get BI ready, do they need to open up a new data center or a few servers running on memory steroids? Cloud is a growing trend for infrastructure elasticity, and Microsoft is also gearing up fast on Azure platform. But again the question is, are you ready for Cloud BI?

Migrating or developing BI solutions on the cloud needs an assessment of your existing line of business systems. I have authored another article titled "5 cloud BI considerations for a successful rollout", which can be read to gain a little deeper insight into this topic.

Finally when your understanding of BI and your infra requirements to host the same are resolved, one another and most important question would hit you - How to recognize / build a true BI software / solution ? Every business has Sales, WorkForce, Products, Business Operations and other Business As Usual constituents. If you have ever studied statistics and statistical methods which are used for analysis by senior business management to analyze different aspects of the business, you would have heard of analytical methods like Time Series Analysis, Deviation Analysis, Regression Analysis, Correlation Analysis, Distribution Analysis and more. These are high level categories of analytical methods and needs a mathematical and scientific approach to analyze and visualize data.

Based on the analytical capabilities of your BI solution, you can assess its BI value. To understand and assess any BI tool you should consider to assess these methods, and to read more about how to analyze any BI tool / software, you can read my article titled "Business Intelligence tools Analyzed" where I have explained four essential analysis features for BI solution. Once you understand what these analysis methods are, you can try to figure out features in MS BI which can help you build these forms of analysis, and then you can derive if MS BI is a powerful BI solution development platform.

Feel free to share your feedback on these articles.

Evaluate MS BI Architecture Design

Architecture Design of any solution in general is done by a solution architect or a group of architects like App Architect, Data Architect, Infra Architect etc and this group is guided by a solution architect. Design is one part of the SDLC but other part is reviewing the design. Design review is generally a very debatable matter, and almost every time there is a design review, one can expect a debate on right and wrong. The reason for the same is none of the sides have a defined parametrized process, against which one can assess the design. And as every design as well as design review is carried out on one's gut feeling and historical career experience / organizational design templates, design review ends up just as a formality than as a productive exercise.

There are two ways to deal with this issue.

1) Architecture TradeOff Analysis Method (ATAM) : It's an ideal solution where architectures are designed in an ideal way. If you live in an ideal world, the SEI Architecture Tradeoff Analysis Method (ATAM) is the leading method in the area of software architecture evaluation. An evaluation using the ATAM typically would require a trained evaluation team, architects, and representatives of the architecture's various stakeholders. You can read more about this from here. This kind of review would also prepare you to shield your architecture against any audits like CMM, PCI etc.

2) Custom tailored design review process: The above mentioned design review process is a very standard one from a SEI perspective. If you live in a non-ideal world, many organizations would not be complying to SEI methods, standards and practices. Even those organization that claim to comply, would not have their 100% projects complying to the SEI methods. For projects and organizations of those scale, a very modest complexity of evaluation process is required, where a modest team or even a IT professional that wears many hat can conduct the design review.

For projects and requirements in such orbit, a custom tailored methodology for design as well as review should be defined, and architecture should be based on the defined method. One cannot develop the architecture on X method and review it against Y method. I have posted about one such custom tailored architecture design document template earlier, which can be read from here.

I by no mean intend to signal that one should avoid using ATAM, but if that method is not achievable start with a custom tailored one, which of course would not be a standard one. But once you have the maturity, skills and resources at your disposal, you should try to benchmark your architecture design against SEI standard methodologies, and that evaluation would be the correct design review for your architecture.

Planning visually appealing dashboard using MS BI

The notion of developing something that is very visually appealing is so much ingrained into the minds of business stakeholders and professionals, that whatever technology that provides fancy, animating, and colorful visuals, they develop an image that its the technology of choice to develop visually appealing dashboards. There are several points to consider, while planning to develop a visually appealing dashboard. Below are few points which are very important to consider when you plan to develop a visually appealing dashboard using MS BI and Sharepoint BI.

1) Visually appealing is a subjective measurement, so the design should be developed keeping the end users in mind. There is a difference between a visually appealing website design and visually appealing dashboard design. Keep in mind that websites might be meant to capture data whereas dashboards always report data.

2) Consider controlling the level of fanciness on your dashboard, as the intention of dashboard is to make the user concentrate on data and facilitate the user to visually and effortlessly analyze data. Too much fanciness like animations, too much colors, etc would take distract users attention from data and might even confuse the user.

3) You might have seen MS BI VPC and got impressed with the visuals of Excel Services, Performancepoint Services and other elements. But do not get carried away but just what you see. Consider studying the inventory of features that these technologies provide and map it against your requirements.

4) Do not base your POCs or analysis based on easy data sources like XML files, Tabular data or Sharepoint lists. Mostly data flows from an OLAP model into dashboards (at least in tactical or strategic flavors).

5) Consider studying the architecture of the technology before selecting it for your visually appealing wonder. You might create a POC and find that data from one spreadsheet displays data well with excel web parts and when you need context-sensitive and user role and privilege specific data, the model might fall apart.

6) In the dashboard parlance, visually appealing would mean analyzing data interactively like drilling, slicing-dicing etc and other form of visually appealing would be data analysis using visualizations and info-graphics.

You do not hire a fashion designer to design the interiors of your home, to make it pleasing to the eyes. In the same way, theory of making a visually appealing website cannot be applied to develop a visually appealing dashboard.

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.

Silverlight based Business Intelligence using Infragistics Motion Framework

Data discovery is a branch of BI that is applied over strategic data i.e. long-term historic data. Visualization plays a very vital role in BI data representation to facilitate effortless data analysis. Silverlight based BI components and motion frameworks is the new trend gradually propagating in the MS BI world. Dundas Dashboards, Telerik Charting and Grid controls, Silverlight Toolkit from Microsoft etc are fine examples of Silverlight based components that can be used for BI reporting.

Before few years, the way bullet charts came as a new candidate for KPI reporting, motion graphics based data analysis is coming up as a new visualization for data discovery. When you have a set of attributes of an entity, and you want to analyze individual and group entities changing over a scale, typically time scale, motion graphics based analysis can be a very effective tool.

This statement may sound very confusing to interpret and digest, but can be better understood by this example. For ex, when you have a trend analysis report, you have the entire trend on a graph. If you want to study trend step by step, that is possible only in your mind but not on the graph on which trend is displayed. If you want to see trend step by step, you need to keep changing the scale of the graph and keep on increasing the same to study it step by step.

Studying trends using motion graphics solves this problem. Silverlight is a rich ingredient for an appealing user experience and to abstract lot of detail on the report, still making is available conveniently on demand. In my last experience, I remember Trend Compass having a motion graphics visualization. I also vaguely remember Telerik having a similar component in their charting controls. Now Infragistics is a new player coming up with a new rich motion framework, that sounds very appealing from the way it has been portrayed.

Infragistics has been a very popular name in the .NET world for the rich UI capabilities it offers with its frameworks. The new offering that I see from Infragistics in the context of BI is its Motion Framework. You can understand the use of this framework only from a demo, and one of the very intresting demos can be accessed from here. Over a slow internet connection it might take a little time than usual to load, but your patience would pay off by the way the experience of the demo would delight you.

I suggest to check out the following from the demo:

1) Click on the Play button on the top-left of the screen. This is the main highlight of what motion-framework offers, and what you can study from trends that change over time.

2) Hover your mouse on the colored world-map on the top-right in different colored sections, and see the effect on the upper chart. This map acts like a filter !!

3) Click on a few random countries in the bottom-right. It acts as parameters to the bottom chart.

4) Watch out the tooltip of the countries, and see the visual of the tooltip.

5) Watch out the tooltip of any of the country icon in the upper chart, which has numeric as well as graphical details.

There is much more to explore, and I leave the rest of the exploration to the delight of the readers.

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.

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 !!

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.

When should you use thrid-party MS BI tools and utilities ?

Most companies would not entertain use of third-party tools and utilities, for the obvious reasons of cost, developer expertise and corporate policies. But it is not that tools should not be used at all, it's just that there should be correct reasons when you should consider the options of using freeware or third-party licensed tools and utilities. Below are some of the reasons in my viewpoint when usage of tools can be justified and considered.

1) Shortage of skills: As an architect or tech lead or team lead or dev lead, you would always want to have "The A Team" for your project. But this is hardly the case if real-life situations. Most IT companies who serve their clients as services / solution provider, houses thousands of employees and it's always a mix of knowledge workers with all the different level of proficiencies in skill sets. For example, for your reporting project where you plan to use SSRS, you might get a Crystal Reports developer who have been trained in SSRS for a week. You might be in a situation that you need to start development instantly and you would have to live with such team composition. At such times, tools can make your life easier and take care of many things that you would not get out-of-box.

2) Time to market: Say you are having a product development project in-house or for a client, more the time you take to release the product in production, more is the loss of business. Developing components in-house is always possible provided one has the luxury of time, but in real-life you would find such projects very rare where you have such luxury. In such cases using third-party products can be a huge catalyst and in many cases it would be the only option. Say you need an ETL component that reads data from Amazon EC2 EBS data store, connect to EBay web services and makes a transaction. Developing such component can take a significantly long time, and third-party components which does the same can be your silver bullet in this case.

3) Development Accelerators: In medium sized companies or in the in-house IT department of an organization, IT staff would be very limited. You might not be in the need of any specialized component that is available only from third-party market, but the set of artifacts that you need to develop would be such huge that it would take lot of hands to complete and manage the task within a given time frame. For example, you intend to create a data warehouse end-to-end solution where you need DW, schema, ETLs, Data Marts, Stored Procs in OLTP etc. All these are regular development artifacts, but if I ask that the data warehouse needs to be delivered in 15 days, however modest the complexity of the solution may be, a modest staff would not be able to complete it. You need a reasonably large team size with a lot of experienced hands. In case you do not have this option, third-party tools can be your rescue boat.

4) Value Addition: For this category, most companies would not entertain tools unless and until they are either free or the technology / product that has been used is seriously paralyzed and some oxygen is required to survive the same. Freewares from community portals like Codeplex can be a huge value addition even if the technologies you are using are up to date. For example BIDS Helper is one of the highly recommended add-on to BIDS, and practitioners know the real value of the same.

I would love to hear your viewpoint on this topic. Feel free to comment on this post or email me your viewpoint.

Planning MS BI project / solution deployment

In my views, "Failing to Plan means Planning to Fail". Most project managers and technical leads undervalue the importance of deployment in the solution development life cycle. Deployment planning is often considered even out of solution development perspective, and the complexity of this process is only realized when the deployment sits on the hot seat. In any professional IT products and services providing organization, you would find regular environments like Development, Staging (UAT), APT (Application Performance Testing) and Production. Each of these environments can have different corporate policies that implies a set of security and access methodologies in these environments. So if you have not planned your deployment strategy, each environment can compel you to make changes to your development.

A typical MS BI solution would consist of technologies like Microsoft .NET, SQL Server - SSIS, SSAS, SSRS, MOSS with/without enterprise services, Microsoft System Center, Team Foundation Server, Microsoft Office among the major application and database components. Below are some of the points to keep in view, which can have a huge impact on the overall solution development, and ignoring these points generally would emerge as a show-stopper to develop a generic build and deployment package.

1) Server and Instance names: Generally to make speedy progress development in development systems, often developers use hard-coded database names / instance names / server names. Different environments can have different server and instance names.

2) Service Accounts: Initially when development machines are setup for the team, developers tend to use whatever account has the best privileges or their own account. This would immediately fail in environments where a separate team would be deploying your build and there would be a separate dedicated service account created for an application as per the corporate policies. Also in many secure production environments, network admins keep on changing service account password at regular intervals. So if you are using user id as well as password to access web services and you have not programmed your services to pick up credentials from a configuration store, you just hit another show stopper.

3) Software Configuration: MOSS and .NET development can require creating many proxies and web / service references, which are mostly sourced or build in the form of DLLs. These can make your solution dependent on certain libraries, which are hard to detect as the development would federated across various development machines. This creates a discrepancy in software configuration. Again certain developers might be using different IDEs with add-ons that would dump external DLLs, for example AJAX Toolkit with VS 2008 and Sharepoint Designer. This needs to be carefully planned, as your server environment might not contain all these libraries.

4) Security Configuration: This is one of the most complex bit. Different technologies talking to each other would be communicating mostly through Windows Integrated Authentication or Active Directory Federation Services ( Windows Identity Foundation / Single Sign-On) configured with a claims / token based mechanism. It is quite possible that development environments can have different security configuration than other environments. During development this goes easily out of focus and the prize to pay for this is very heavy afterwards.

Site Collections / Web applications created on MOSS would be using HTTP / HTTPS with certificates which would need to be installed and configured on the site. Corporate policy might not allow you to copy certificates across servers, and this may come to light only when you might want to deploy your solution and infrastructure teams would raise a red flag for certificates.

I have been personally evident of a scenario where different developers developed their SSIS packages with ProtectionLevel property of the package set to EncryptSensitiveWithUserKey. Developers keep joining and leaving the team and they log on to systems using their Active Directory IDs. When the solution was deployed on a different environment, the solution started failing. Now these packages can be fixed only using the credentials of the users who developed them, and this iceberg almost sank the ship.

5) Automation: Many things are done manually like executing DB Scripts, deploying SSRS reports using BIDS, deploying MOSS site using Sharepoint Designer. You would find yourself with a new development cycle, when infrastructure teams responsible for staging and production environments would refrain you from access to these environments.

6) Shared environments: Solutions that are equipped with exclusive servers are quite lucky and also are generally first of their breed or mission critical. In any organization that is having an infrastructure setup in data centres for their IT Services would have shared environment for different application services like SSRS Reporting Servers, MOSS Application Servers etc. One may overlook this detail considering that fact that the same version of the service is used. But at certain levels overlooking key piece of information can be very heavy, for example, one may change web.config file which is the configuration file for entire server. Shared servers would be catering an array of applications, so when you deploy your solution you would not have the luxury of changing these files. At this point again your solution would fail.

Planning your infrastructure followed by deployment can provide insight into many restrictions and dependencies that you may discover very late. And such discoveries can either break your solution or invite a huge effort to develop your deployment setup. Remember one thing, "Failing to Plan means Planning to Fail" !!

MS BI Technical Evaluation against Gartner's BI capabilities requirement list

Gartner's Magic Quadrant for Business Intelligence platforms is one such reports, that almost every serious BI professional should read in detail to get a summarized report of the winds of change in the BI industry. The latest version of the report, that has been published as of 27-Jan-2011, can be read from here.

A section of this report states that BI platform is a software platform that should deliver 13 capabilities, classified in 3 categories of functionality: Integration, Information Delivery and Functionality. Below are my views for Microsoft BI platform against these 13 capabilities. Before reading the below, it would be beneficial to go through the details of these capabilities from the original report.

Integration:

BI Infrastructure - Microsoft has tight integration between it's development platforms like .NET, Sharepoint, SQL Server, Biztalk and others. Appliance solutions like Parallel Data Warehouse, brings MS BI in competition to IBM for appliance based solutions too.

Metadata management - MS BI still struggles in this area, and metadata management has been the major ask from customers and practitioners from SQL Server.

Development tools - In terms of development tools, it's often termed that Microsoft BI is more friendly to developers / IT than to users, such is the extent of efficiency and variety in this area.

Collaboration - Sharepoint is the highest selling server technology of Microsoft, and probably Microsoft is the only BI player to provide BI integration with it's collaboration platform.

Information Delivery:

Reporting - My personal opinion is that this is the weakest corner of MS BI. Reporting can be seen as a strong supplement in the MS BI Stack, but still it needs to evolve a lot. Developer tools are limited and professionals still need to depend upon tools like Fiddler and frameworks like SCRUBS. Report access from smart devices is far from even imagination. Players like QlikTech are sweeping this market.

Dashboards - Microsoft has been comparatively weaker in the area of innovative visualizations. With the license acquired from Dundas, SSRS has received a healthy boost in this area. But PPS is still quite immature and needs to attain the same level that Proclarity used to have. After dissolving Performancepoint Server and integrating it with Sharepoint has been the only progress. Visualization like performance maps are still missing, and MS BI can see seen to be doing just okay in this area.

Ad-hoc Query - This means self-service BI. Powerpivot is a strong candidate in this area, and with the news of BISM, MS BI can be seen as doing great in this area. Technology / Component like PivotLabs adds a thrust to this area.

Microsoft Office Integration - MS Office is one of world's most successful softwares ever built, in my views. MS Office, especially Excel, has evolved so powerfully, that it can no longer be seen just as a spreadsheet. It's a thick client that is integrated with almost every MS server technology. Office Webapps takes collaboration to a new level.

Search-based BI - Microsoft's candidate in this category is FAST Search. Two indexors available with FAST deployed on Sharepoint, are the providers of search on it's collaboration platform. Sharepoint is equipped with BI capabilities in terms of reporting and collaboration facilitation. I do not have much experience in this area to comment confidently, but I feel this is still not such a strong area. Metadata search is still weak in MS BI parlance.

Analysis:

OLAP - SSAS and OLAP are the most successful adopted areas of MS BI. Columnar databases and in-memory analysis are used as weapons of creating great hypes, but customers still bank on SSAS as the technology for their DW needs.

Interactive visualization - This area has always been weaker with MS BI. To measure this, compare visualizations available in PPS and SSRS, and compare it with vendors like Tableau, QlikTech. You would get the reason for my such viewpoint.

Predictive modeling and data mining - MS BI stack has it's offering of data mining, but this area has not been that successful in terms of adoption by the industry. There are dedicated players in this area, who are considered as standards. I am even of the opinion that Microsoft might just slice out this section from MS BI stack and come out with a new product in itself to cater this area.

Scorecards - MS BI still facilitates scorecard creation using traditional grid like UI and indicators / trends. Visio comes in as a backup force to create strategy maps and associate KPIs with interactive visualizations. But still it's not as good as what BeGraphic provides.

These are my unbiased viewpoints on what MS BI delivers in these 13 capabilities. I am of the strong opinion that, if you are a serious BI professional, you should definitely read Gartner's Magic Quadrant for Business Intelligence Platform, and enlighten your maturity about BI industry.

Considerations for selecting a technology stack for your BI Solution

Once you have your functional requirements ready, and your BA's would head to you with some powerpoint slides having some fancy graphics of some complex graphs and visualizations, you can consider it as a sign that you would be hit with the question of which technology are we going to use to develop such visually appealing solution. Selecting the right technology stack is a part of every solution development, and it is almost given that SQL Server 2008 R2 and Sharepoint 2010 are NOT always the right answer. There are certain factors that one needs to consider, especially if you are acting as the technical architect for the project. Cost is one of the most important parameter, and till it gets figured out, there is no point in even selecting a technology stack. Apart from this, a list of few such considerations in my viewpoint as listed below:

1) Analytical Visualizations: List down all the type of visualizations required by your solution. Many a times, Business Analysts just get carried away by visualizations, though what they need to analyze is very simple. So get this sorted out first. A very basic example is "Tree Map". PPS 2010 and SSRS 2008 R2 still does not have this visualization. If the limitations of visualizations are too much with PPS and SSRS for your requirements, you might want to use some another reporting solution and continue with SSIS, SSAS and DB Engine with SQL Server 2008 R2.

2) Technology Integration: Mostly whatever you develop out of any technology, it needs to be deployed on a collaborative platform like Sharepoint. If the technology / component cannot integrate on your collaboration platform, it is useless. Most organizations like security configuration using ADFS 2.0 for single sign-on and say that some third-party tool that you selected is not known to be good with claims based authentication. If you select it, you would sign a contract to get yourself roasted.

3) Time to Market: PPS 2010 might be able to cater all your requirements. But ramping up a team having trained skills in development using PPS 2010 can be a challenge. And if you plan to go to market with your solution in a very aggressive timeline, skills like MDX, PPS, etc can be hard to find. And provided you already have skilled resources, it would definitely need time to design - develop - test and - deploy the solution that you would develop from scratch. You might want to use off-the-shelf ready tools / components for a part of your solution.

4) Ease of deployment: Most IT enables services providers and consumers have their own infrastructure where they host their IT solutions and services. This infra can be as huge as few datacenters in different cities, and hardware like SAN, RAID, Load balancers and Clusters. The technology you select should be able to fit in this topology as well as have the potential to exploit the capacity of the existing infra.

5) Performance: Almost every solution provider or consumer would have got burnt little or more with performance challenges. This factor is on the cards for almost every technology stack, before it even gets nominated for evaluation. So the technology that you plan to consider should already have some benchmarked performance results or you should consider creating some POCs to extract some form of performance results to compare against standard benchmarks.

6) User Training: You might want to select a tool that is too rich in feature set and visually appealing, for ex Tableau. But your user base are power users who just wants ready dashboards without any efforts, and they would discuss the results of this dashboards on a collaborative medium like Sharepoint. Though you have the best tool and it might pass all of the above criteria, it might not pass this parameter.

In summary, while selecting the technology stack for your solution, ensure that you go through a rigorous analysis from all aspects, not just feature set.

MS BI Appliance Solution - HP Business Decision Appliance

Recently Microsoft announced availability of a new appliance solution known as "HP Business Decision Appliance", which can be seen as next product after Parallel Data Warehouse solution. HP has been a partner even in the Parallel Data Warehouse solution, and this solution has been developed in the exclusive alliance between Microsoft and HP. Though the purpose both of these appliance solution serves are quite different, the common characteristic is that both of these solutions are powered using SQL Server in one or another way, and the purpose it serves primarily is business intelligence. You can learn more about this solution from the announcement published here.

In case you intend to learn still more about this solution, here is an announcement from SQL Server Appliance Engineering Team, where you can also watch two screencasts explaining the details of this solution. A flavor of Appliance Engineering Team resides in the CoE (Center of Excellence) of every organization, it's just that most of the organizations do not provide right nurturing to it. Solution offerings in the form of appliance solutions is a growing market, and there are already few market players who are engaging alliances with local vendors to develop appliance solution to tap a market that has not been explored / exploited to it's maximum potential.

Big IT players, can definitely harness the potential of such markets by strategically aligning CoEs in the area of developing solution offerings in the form of appliance solution. In my views, standardized appliance solutions are an ideal candidate for consideration as a value-added service in addition to the regular IT services.

Appliance solution market is one such place where aspiring entrepreneurs, can make their entry into the IT business too. Most of the developers who work in IT factories housing thousands of employees, aspire to start some sort of business in IT. Working Capital is one of the biggest challenge that a service classed person faces to start a business. The first step can be to develop a tool / component and start business by licensing the same to clients. Once you grow in this field and you have a reasonable and modest working capital, appliance solution is another business that IT developers can venture.

Selecting the best Business Intelligence software / service provider for your BI needs

Selecting the best Business Intelligence service-provider involves a very tedious analysis process, and this is one such decision where no client would like to go wrong. Millions of dollars gets invested when any BI project contract is awarded to any vendor, and however fool-proof your SOWs (Statement of Work) may be, if the BI service provider is not mature in the kind of service it has been selected to provide, this would mean that your bad time started at the very moment of selection !

Before I jump to the core of the subject, one thing that I would emphasize, any organization should not just bank upon advice of just any reputed technology consultant. There is a distinction between a BI consultant and a Technology consultant. There are many professionals who excel in one tool / technology and they extend their profile by adding the rest of the technologies to make their profile look complete. For example, I have come across professionals who are SSIS or SSAS or SSRS experts and they claim that they excel in Business Intelligence, as they are familiar with just the vocabulary of all the three. In my views, professionals who have worked in different roles like business analysis, data cleansing, systems integration, cross bi integration, data warehouse development, corporate reports planning, MID / DSS development, etc., adds a flavor of BI to one's profile. Having worked in SSIS / SSAS / SSRS and being an expert at any one of them can mean that they are ETL / Reporting / Cube development experts, but it does not mean that they are BI consultants / experts.

Below are some questions / checklist of actions that one should consider before selecting a BI service provider.

1) Check whether you really need to build your BI solution from scratch or you can use a PaaS / SaaS BI service provider. Study the advantages of SaaS as well as PaaS based BI solutions. A few whitepapers from TDWI can be a very easy reference manual to start.

2) If you decide that a PaaS / SaaS service provider is your choice, check the list of clients that it caters and analyze the nature of business of those clients. This can be seen like a kind of reputation check.

3) Key areas to check in a PaaS / SaaS BI provider are: Robustness of cloud facilities, Flexibility in availing customization, Billing plans, Security Compliance, Cloud Gateways, Ease of switching between in-premise and off-premise networks, and Systems Integration with production systems.

4) In case you decide to go with a BI software / services development vendor like Accenture, IBM, Atos, Capgemini, TCS, Infosys, Wipro or other such famous vendors, there are a different set of checkpoints to consider. Issue with selecting among these is that most of these vendors would provide similar propositions, similar architectures, similar billings and similar quality of work-force. So how do you select the best BI vendor?

5) In my views, if I was in charge of selecting the vendor, the first thing I would check is which architecture is going to keep my Total Cost of Ownership to a minimum in the long run. This can be a result of support staff requirements, enhancements to the solution over the period of time, upgrade in licensing, scaling of hardware to meet growing needs of performance etc.

6) How updated is the vendor with the current BI trends and how deep is the BI development experience is very important. Clients generally would like to build a solution which they think is the best for them, but they do not know for sure that it's the best. This is the reason they need to hire a professional BI software / services vendor, as the vendor is expected to be deeply experienced with BI software / services manufacturing and up-to-date with industry trends. Only an experienced BI vendor can advise a client from making mistakes, the rest would be yes-man to clients. Believe me, I have been part of meetings where Directors of BI practice are not aware that DatAllegro has been digested long before, and they suggest their Competency and CoE head to promote and recommend this to their clients !! Just think how catastrophic can this be to the client !!

7) One another point to get some insight about the BI maturity of an organization can be to check it's affiliation within the BI community. For example, if you are having a MS BI project and you want to check the BI strength / maturity, it is worthy to check if the technical / solution architect of the project has any whitepapers, publications, blogs, community projects on his name. Infosys, for example, has infosys blogs which I quite admire. Many other organizations have a huge manufacturing IT work force but no affiliation in the technical space. This does not mean that the entire organization is not technically competent, but it definitely increases the risk that the IT workforce that may be working for your project might be outdated with technology. Certifications like MCP, MCTS etc are of no value if they have not been put to practice.

8) Finally, one should check for what are the different innovative solution offerings that a vendor's CoE has to offer. An admirable BI practice would have a healthy CoE, and developing BI solutions offerings are an integral part of any such practice.

Market reputation, historical business relations and budgetary constraints are definitely the driving force to shortlist vendors. But after this boundary when you have a set of vendors still to choose from, above points might be of some help to get an insight in the capabilities and characteristics of a Business Intelligence software / service provider.

MS BI POC and SQL Azure POC for better ROI on future projects

In my views, POCs ( Proof Of Concepts ) is a very versatile way of creating and promoting business, especially for vendors and solution providers. There was a very interesting article on POCs on BeyeNetwork some days back, that talked about how decisions based on limited scoped POCs can be catastrophic for an enterprise.

If POCs are carried out with a viewpoint of extracting results that can be translated into patterns / benchmarks that can catalyze agile and/or economic BI solution development methodology as well as provide avenues for appliance / product development, such POCs have a very high probability of an assured Return On Investment (ROI) against the investments done for conducting such POCs. For example, MDX is quite a complex query language, and developers need time and experience to master the same. If templates or CLR class libraries are created using ADOMD.Net and MDX, same can be reused via template explorer in SSMS and .Net application development, which can effectively boost up the development process and reduce the learning curve. This is very similar to DAX available in Powerpivot. Also if such libraries are developed with a broad vision in mind, it can be promoted as a off-the-shelf component. And I am sure that the ROI would be exponentially more compared to investments.

Below is a brief list of few such POCs, which has the potential to provide a very high ROI, provided it's carried out with a vision and target in mind.

1) SaaS solution development methodology using MS BI Stack and SQL Azure

2) Pattern development for synchronizing in-premise SQL Server and SQL Azure

3) Plugging / Integrating Cloud gateway tools at different phases of BI lifecycle

4) Economic BI solution development using SQL Azure / SQL Express , SSRS and Sharepoint Foundation 2010

5) MDX Script library in compiled / script format

6) Pattern development for economic cloud database (SQL Azure) consumption in BI and application development projects

7) Pattern development for Inter cloud integration and multi cloud incorporation into MS BI projects for provider-dependency free cloud consumption in MS BI projects.

I am not claiming that these are the best ones, but I can say that these are the best ones in my mind.

Self service enterprise reporting solution for data warehouse powered by SSAS

In a typical Microsoft solution involving MS BI and .NET, after the data warehouse development generally a BI development team hands over the next lap of development to the application development team. BI team and/or App team remains responsible for developing reports that are hosted within the proprietary .NET application and/or Sharepoint portal. The disadvantage is that, it takes away the self-service feature from the business users. When the requirement is "race against time", i.e. when one needs reporting solution asap which can serve enterprise class BI reporting on the top of your data warehouse or cubes, the selection moves towards ready to use reporting solution that can sit on the top of your BI artifacts.

One such solution is a product from Strategy Companion known as Analyzer. This reporting solution is said to be developed using ASP.NET and DHTML. Some of the features that I found pretty regular in line with what other such reporting products might offer are reporting using KPIs and indicators, drag-and-drop report creation, security integration with LDAP and Kerberos, different visualizations and gauges, integration with IE, SSRS, PPS, Sharepoint, Excel. But some of the features are quite impressive which any project would desire, and they are as follows:

1) Zero foot-print installation: Thin client is one of the most sought after requirements for any kind of enterprise wide client access. If you can facilitate your analytics requirement just using a browser, and still facilitate drag-and-drop report creation which for self-service report creation with rich report development features, it's very much favorable for any business.

2) Software as a Service solution: No business would want to invest straight away for a full fledged enterprise wide license, just based on an evaluation version, until and unless the product is a premiere class product listed in Gartner's report. Also small and medium businesses might want such reporting in an on-demand fashion. Analyzer provides the option for an SaaS deployment too.

3) Logic as a service: This is not a standard term, it's just my synonym for the term webservice. Analyzer features can also be used in the form of webservices, to embed reporting functionality in existing applications. This is a nice to have feature, but in my opinion, I would not recommend for this option. Instead there are better options than this, but still if you have deployed Analyzer for your enterprise wide BI reporting requirements, and some proprietary applications might be getting left out, this option can help in such cases.

4) Support for load-balancer / clusters: Analyzer can be deployed on IIS that is deployed on multiple servers and managed by a load balancer.

If I am given the requirement of agile DW development and facilitating reporting on the top of my MS BI artifacts, and provided that neither I do have enough funds to invest into ramping up a team to carry out a full fledged development life cycle nor I have time to wait for the availability of such solution, I would opt for tools driven DW and BI Reporting development. For the same I would pick up products like WhereScape RED for agile DW development and Strategy Companion's Analyzer for BI reporting. I do not intend to say that these are the best options, but these are definitely the two prominent ones.