Web based OLAP client for SSAS : Report Portal

In my views, OWC (Office Web Components) available in SSMS is by no means a professional OLAP browser. Excel is one of the widely used client for browsing OLAP cubes, but there are some limitations with it when one wants to consider it as an enterprise client, some of which are listed below:

1) It needs to be installed on every client workstation
2) Data visualizations available are more suited for operational reporting than analytical reporting
3) There are almost no features for collaboration i.e. sharing generated reports with other users on a common platform unless you use Office Web Apps or Sharepoint
4) No spatial data support either in consumption or presentation with Bing Maps / Google Maps

Actually Excel has the capability to use OLAP / SSAS cube as a data source, but that does not mean that this capability can be treated as the primary purpose of using Excel. In one of my posts, I mentioned about Analyzer which is a product from Strategy Companion. Recently I came across one another such product - Report Portal.

Zero footprint, SaaS, Cloud are some of the buzz words while selecting a technology / product for solution development, and Report Portal is also a zero footprint OLAP web client for SSAS. In my view, this product lacks that professional and sleek features that is expected from a web client. Documentation is quite poor, export formats of reports are limited, there is no mention of SaaS offering. But some of the noteworthy features of this product are as below:

1) Report portal has got very distinguished data visualizations, which are quite impressive from a data browsing point of view. You would not use these kind of data visualizations generally for reporting, but to develop analytical reports, these visualizations can be quite helpful for analysis. Check out the Pie-Chart Tree Report, which is one of the quite unique kind of data visualization for data analysis.

2) Collaboration and organization of the analyzed data, which can be saved in the form of reports, is considerably mature.

3) Metadata can be browsed very efficiently, and this product makes a very extensive use of XML/A ( XML for Analysis ). The browsing features are quite friendly to take away the pain of forming MDX queries.

If you use it as a tool to save your OLAP analysis i.e. the results that you gathered while browsing or analyzing OLAP data, and want to stick it in the form of reports on a collaborative platform, it's a nice web client with zero footprint installation ( as it's a web based solution ). But if you are looking out to use as a full fledged self-service enterprise reporting solution out of your OLAP, I would feel much more comfortable and confident in Analyzer than Report Portal.

A wise strategic move can be to use Report Portal when you are taking your baby steps in your OLAP reporting i.e. the phase when you are developing your data marts / data warehouse. This product could well support your OLAP browsing needs efficiently as well as facilitate reporting at the same time, keeping the pressure of immediate reporting from your evolving data warehouse, at bay. When the solution is in the next level, you can choose a SaaS reporting solution like Analyzer and retire Report Portal. Please keep in view, this always depends upon scenarios and it's not a generalized strategy. The key difference between Analyzer and Report Portal is that Analyzer is a reporting solution, and Report portal is a web client and reporting is one of it's features and not a primary function.

StreamInsight Tutorial , StreamInsight Video Tutorial , StreamInsight Webcasts , StreamInsight Labs and StreamInsight Presentations

In my views, StreamInsight is one of the most undiscovered enhancements of SQL Server 2008 R2. StreamInsight is a development platform for targeting complex event processing (CEP), and though this is newly introduced in SQL Server 2008 R2, but CEP is not a new market. CEP is a very well know term in the BI parlance and there are already other players in this game. Some of the reasons for limited awareness / popularity of StreamInsight is that it involves a bit of .NET programming ( LINQ ), applications of this feature is limited and this is not something that masses would use frequently unlike other database development features or business intelligence related features. Also in my present knowledge there is no book available that is dedicated to StreamInsight.

In such cases when reference material available is scarce; articles on blogs and community websites, webcasts, technical presentation by experts, and video recordings of demonstrations are a very effective and economic way to fill up this reference material gap. Nick MacKechnie has shared an exhaustive resource collection of StreamInsight related webcasts, videos, presentations, hands on labs and demos. Though you might not be interested immediately to get your hands-on StreamInsight, but this page is definitely worthy of a place in your bookmarks section. Check out this StreamInsight resource collection from here.

MS BI Interview questions book covering SSRS SSIS SSAS interview questions and answers

Recently one of my blog readers asked me a query, whether I know of any book that provides interview questions and answers related to SSIS , SSAS and SSRS i.e MS BI related topics. Candidate who posted this question has/had prepared for the interview, but wanted such book to gain confidence for appearing interviews. I felt that I would discuss this topic in the form of a post, rather than replying in the form of a comment, as I find many of my blog readers sending me queries through email or in the form of comment, specifically for interview related topics.

Firstly, to answer this question straight, there is no book dedicated to MS BI interview questions. Now the question comes that how to get a mock interview session or how to evaluate oneself to test whether you are ready for a MS BI interview. Here are a few steps that should help you prepare for the same.

1) Bank on your area of expertise : As I always say, no interview is a generalized interview. You need to be very thorough in your area of expertise and prepare in accordance to the role that you are targeting. If you are appearing as an MDX / Cube developer, preparing thoroughly in T-SQL won't add much value. Even if you score passing marks in that area, that won't impact your selection as your interviewer should be sensible enough to understand that you would rarely work in that area. So instead of concentrating your energies in all the areas (unless you are targeting an architect / MS BI Tech Lead role), concentrate your energy on your area of strength.

2) Mock Test / Practice Test questions : Many certification exams books like Self Paced Training Kit , Exam Cram and others come with a lot of practice questions (approx min 300 questions) which are scenario based. For example, self paced training kit book from microsoft press comes with a practice test software and lots of practice questions in each area of MS BI. And when you appear this practice exams, based on your performance, it would also recommend the areas where you need improvement. One additional benefit of such tests is that your vocabulary (theoretical knowledge) would improve a lot.

3) Theory of Top 5 : One more theory that you can use is pick up top 5 hardest questions in SSIS , SSAS and SSRS. Start with it and try to figure out the maximum questions you can ask from the start-up question. For ex, "What is an Execution Tree in SSIS" and from this question I can ask another question "How can I find number of trees in my package" , "What are blocking and non-blocking transformations" etc. If you are able to score 70% in all these areas, you have a great probability of crossing the interview. In summary, think like an interviewer to appear for an interview.

4) Community Service : Confused, how this is going to help for interview preparation / gaining confidence to appear for an MS BI Interview ? By community service I mean, join MSDN SSIS / SSAS / SSRS forums and spend around a month addressing various questions. You would generally get a feel of how good are you at addressing regular technical issues.

5) Prepare your Notes : The moment you start preparing for your interview, keep collecting your questions. After spending around a month or two and following the above 4 steps, you would have collected decently large number of questions instead of wandering from website to website for interview questions.

I am of the opinion that create your own book, instead of depending or searching for books related to interview questions. I have not appeared for any job interviews from the past 3.5 years, but when I would start looking out for a change, I would definitely practice this theory :-)

How to apply Conditional Split in a SSIS package without using Conditional Split transform

Conditional Split is a very simple and effective transform, and is one of the most commonly used transforms in a SSIS package. But those who do not pay attention to detail or those who are do not work regularly / extensively in SSIS, often miss the detail that once a record matches a criteria, it's moved to the first matching output path. So each output path has exclusive records, and no repeated records.

Many times we come across a situation where we have records that are matching more than one criteria and we need such records in more than one output paths. Generally we take help of multicast transforms in such scenarios, but that is not an efficient design, and definitely such design won't scale. When the question / task comes of splitting data, right from the schools / parlance of SSIS, we are used to answer / use "Conditional Split" Transform. But we know the limitation of this transform for the scenario in question and design issues with Multicast transform. So what is the way to conditionally split data in an SSIS package without the help of this transform ?

Today one of my article got published and it's titled " Intelligent Conditional Split in an SSIS package "; it is aimed to present a solution at this issue, and the same can be read from here. The origins of this technique is derived from the niche art of custom SSIS components / transforms development methodology. If this solution is wrapped as a custom component, it can be used very well as an Intelligent Conditional Split component. To avoid ruining the value of my article, I would limit my explanation and would let you check out the original article. Feel free to share your opinion about this article.

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.