Powerpivot Architecture Technical Poster / Diagram

It's said that "A picture is worth a thousand words", and it is more easy and interesting to understand any architecture by studying the architecture diagram. SQLCAT Team has released a logical architecture diagram of Powerpivot and components related to it's ecosystem like SSAS 2008 R2, Office 2010, Sharepoint 2010 and other related components and technologies.

This poster can be downloaded in PDF, XPS and VSD format. The original article can be read from here.

Skills set needed for your MS BI Interview Questions

I myself have gone through some of the best technology giants of India and other MNCs and at the same time I have also been on the technical interview panel for junior and senior level candidates with my employers for MS BI Recruitment. Let me share what is generally expected from a MS BI Tech Lead in this competitive industry.

Experience: Generally for a lead kind of role the candidate is expected to have accomplished on an average 3 full life-cycle of MS BI implementation incorporating all the 3 technologies (namely SSIS, SSAS, SSRS) and other technologies revolving it. Other supporting technology experience is considered an added advantage. Also on an average 6+ years of working experience with 2-3 years relevant is seen as a minimum qualification. Candidate should also be able to lead a team of 10+, and should have lead teams of same capacity in his/her career history.

Skills:

1. SSIS - Implemented in any Data warehousing projects (at least 1), Data Migration projects or Data Cleansing exercise
2. SSAS - Implemented in any Data warehousing projects (at least 1 or should have a good working knowledge from an implementation point of view)
3. SSRS - Implemented in any Operational or Analytical Reporting in Native and/or Sharepoint Integrated mode
4. T-SQL - Should have worked at least 2 years on databases and should have very good T-SQL, querying and stored proc writing skills
5. MDX - Must have knowhow of basic level of MDX to the extent that one can create basic queries, calculated measures, named sets, and year-on-year comparison queries/expressions etc.
6. Theory - Data Warehousing, Cube and Analysis, Set theory, Basic Database Administration, Basic to Moderate Dimensional and Database Modeling, Performance Tuning and Optimization. Knowhow of Estimation and creating WBS kind of stuff would be an added advantage.
7. Architecture / Solution design - Solution driven approach to any real-life project issues and understanding of what technology components to choose to devising an end-to-end solution and also any tools knowhow to design the same like a little bit of Visio for example.
8. Sharepoint - It might come as a shock, but yes it's a fact. Not sharepoint development, but knowhow of sharepoint to the extent that the candidate should know how to integrate MS BI Tech components with Sharepoint webparts
9. Performancepoint - This is an added advantage technology expectation. It's not a must in general, but carries a huge advantage
10. Excel Services - This is again an added advantage technology expectation.
11. .NET programming - Again this would come as a shock, but some basic level of at least one .NET programming language know-how is expected as scripting has become a part of many MS BI Technologies we use. For ex. if you are not aware of scripting using C# / VB.NET how would you create custom components in SSIS ?
12. Biztalk - You must be saying this time that cmon !! This guy must be kidding !! And fortunately the answer for Biztalk is yes. Thou humorous enough, I was asked in an interview with a company that I should also be having a bit of biztalk knowledge as biztalk also can be used for crafting business workflows. But I am not of the opinion that a single brain can accomodate Biztalk after the above 11 skills, else you would be some real Microsoft genius.

Jokes apart, the above 11 skills are sought after in any technical interviews with any reputed IT giants and to be honest I have also grilled candidates in interviews of multiple rounds for some of these skills. I thought that I would share this information to help people know about what skills are expected at least in India with any IT giant if you aspire to be a MS BI Tech Lead. I am also just another knowledge worker like you and I thought this information would be helpful as when I was searching for this kind of info, it never existed for me.

Note: This post has been republished as I have been receving frequent queries from my blog readers about career guidance.

Free SQL Server 2008 and SQL Sever 2005 Tools, Books, Downloads, Webcasts, E-Learning courses and Resource Collection

There's a saying that "there are no free lunches", but fortunately there are lots of free resources available in the world of SQL Server. And for the same thanks to all those generous professionals out there who share these information with us. Do not worry, this is not a social message kind of post and quickly coming to the point, below are some of the great resource collection that comes up immediately to my mind.

Having the right material / tools at the right time can really make one's life easier as you do not have to start your quest to hunt for some tool or technique to resolve a highly pressing problem. Below are the posts that lists down links to lots of SQL Server resources. And before you figure out, I admit that I am also blowing my own horn in the first item of the list, but it's not without a reason. MS BI Project Booster Kit lists some very useful resources that can help you in your MS BI project. Read the article and you would agree with me.

1) Microsoft Business Intelligence Project Booster Kit

2) Free SQL Server Spatial Resources

3) SQL Server 2008 R2 Resources - Free Books, articles, webcasts, e-learning courses, and downloads

4) Free SQL Server Tools

Bing Maps Server and SQL Server 2008

Bing Maps layer or Bing Maps Webservices sounds like familiar terms, but the term that I have used in the subject line of this post "Bing Maps Server" is not a typo at all. Microsoft Virtual Earth Webserver is now known as Bing Maps Server. This is definitely a news, at least for me.

The next question which immediately strikes one's mind is what's is its use when we already have features like Bing Maps Layer in SSRS or Bing Maps Webservice for using in SSIS which I have explained in my BI-Satellite project. The answer to this is a question, can you use Bing Maps service without an Internet connection ? This server facilitates the same service for a offline or on-premise based requirements where one needs this features on a restricted or private server instead of querying data each time over the web. And this data is stored in SQL Server 2008. So this completes the circle.

An obvious question is who has got such requirements. Well, this world is quite huge and equally varied are the kind of requirements. Security agencies might not want their investigation data being tracked by some nasty hacking networks and might want to carry out their proprietary analysis within a closed premise. This product can be useful in such scenarios. BTW, this product is exclusive provided by Vexcel Corporation which states itself as the Microsoft's geospatial subsidiary.

SQL Azure and MS BI

These days I have been extremely busy with my BI-Satellite project, and this is one of the reasons I am not getting enough time for my blog. I try to keep blogging which keeps on re-inforcing new blood into my knowledgebase, but it's not easy to find genuine, new and useful topics everytime. I see many popular blogs which have posts that are rehashes of MSDN BOL or topics that are replica of issues picked up from community forums, and I opt not to blog instead of adhering to this practice. I think I should not have started on this track and came straight on the subject of this post.

I recently came to know that a few new SQL Azure features have been made available, namely MARS (Multiple Active Result Sets), features for renaming SQL Azure databases, Application and multi-server management for data tier applications (DACPAC) and introduction of 50 GB sized SQL Azure Databases. I see use of SQL Azure with MS BI applications for quite a few interesting purposes, the most prominent being for permanent staging purposes in ETL solutions. I have written two different articles on how to read/write to SQL Azure using SSIS and how to use SSRS with SQL Azure which can be read from here. Even in database administration purpose, SQL Azure can be used as a low cost bridge for replication and synchronization purposes, apart from it's use as a regular as-on-a-need-size-or-cost database. With the support introduced for data-tier applications, SQL Azure has now managed to be a favorite of .NET fraternity too, and DBA's are definitely going to give out a painful cry due to such ongoing support from SQL Azure coming down their way.

If I go mythical, I see SQL Azure as the flood in the database industry and DBA's better start building Noah's Ark for themselves, as in the near future, cloud computing is going to change the way we look at database and it's administrators administration.

Silverlight Charts - Data visualization for SSRS

I have been always wanting since long, that Microsoft should come out with Silverlight based data visualization controls that can be used in Business Intelligence solutions. Products / Services like Performancepoint Services which are still found digesting the codebase of Proclarity, are not equipped with Silverlight based data visualizations. SSRS Team also bagged the license from Dundas to distribute and use Charts, Bars and data visualization of Dundas but still they do not have that silverlight magical touch. In my previous post about Trend Compass, I mentioned that this is a wonderful way of visualizing or analyzing data as Time can be literally used as a dimension of analysis by the end users instead of using it as a filter, and that too with animation.

Sometimes I happen to find that this BI world is too big and my knowledge is too small. Coming to why I feel so, the reason for the same is Silverlight Toolkit. This toolkit is available from Microsoft and I was completely unaware of one of the wonderful gems that has been developed jointly by SSRS Team and Silverlight team. This gem is known as Silverlight Chart Control which is a part of Silverlight toolkit. This control can be bound to data and has got a lot of functionality to explore, and if you have read my previous post, you would find that this control can cater what Trend Compass offers. A picture is worth thousand words, so watch the below effect that can be obtained from this control. I found this picture on a post of a blogger Tim Heuer.

So if you feel interested, jump on to download this toolkit and try out the use of this control in SSRS. I am also ready to get my hands on trying it's use in SSRS as soon as it get time of my regular work. If you want to check out demo of different controls available from this toolkit including the chart control, you can check out the same from here.

Trend Compass - A raw gem in the Data Visualization treasure box

On my recent post, I received a request is the form of comment from someone claiming to be from EPIC Systems requesting a review of their product. Out of curiosity I took a look of the product and after my initial review, I can say that this product definitely looks like a raw gem which can be made an industry class standard in data visualization and analysis. There are still some questions open in my mind regarding this software, which I would come across very shortly.

If anyone has worked on dashboards using platforms / technologies like performancepoint, novaview, mySap portal, dundas dashboards, telerik dashboards etc, analytical charts is one of the components that works in the context of scorecards. We are accustomed to analyze and represent data in the form of year to year or quater to quater using functions like ParallelPeriod in MDX. Now this period is defined in the form of slicers which functionally works as filters rather than an axis. Also many on these analytical charts developed using excel services and other regular image / graphic based functionality are not that impressive, though the Santa Claus of Microsoft ( I mean to say Silverlight ) is changing the traditional way of dashboard development. In summary interactive motion graphics and time as an axis concept is almost absent in present days dashboards, except Silverlight based ones.

Coming to the star of the post is a Data Visualization software "Trend Compass" from a company called EPIC Systems. I have a favourite quote of mine, "A picture is worth thousand words and A Dashboard is worth thousand reports" (may be I should get it patented), here is a must look demo application of this software.

1) The graphics are very impressive and still it manages the issue of "Too Much Information" in a very descent way. Data Applied ( a silverlight based data mining service provider for modest volumes of data ) suffers too much from the issue of "Too Much Information" in my views.

2) The data visualization presented contains the concept of layers which we see in present days SSRS reports development using Bing Maps layers. This helps to manage the data representation is a very nice manner and give control to the viewer to analyze the information in a very interactive way.

3) The biggest USP of this data visualization software is it provides time scale as an axis with interactivity of data visualization and analysis still preserved at its best. In fact it really fuels the way data is represented for analysis.

4) Exporting data to different formats is one of the most admired functionality in any applications, and Trend Compass scores full marks for the same. It can export data in EXE, SWF, AVI and other formats too which is really fantastic.

Coming to the downside of the application, which is the reason I call it as a RAW GEM, is as follows:

1) For any application to get widely accepted, providing developer information is very vital, which leads to developing an army of technical workers around it. Developer expertise is one of the major factors considered while evaluating any product for procurement. On the product website, there is no technical information available like object model, integration, data sources supported etc.

2) Nice demos are presented, also trial version is available for download. But whether it can be used as a component on collaborative environments like Sharepoint using some webparts, such kind of information is also missing. One might be very much interested in using this component as a constituent of a dashboard instead of using it as a off the shelf tool which does not integrate into the solution.

3) There are no comparative study available on the site, or no whitepapers available where they demonstrate how this software can be used in collaboration with other leading products in the same domain.

It seems like still the viewpoint of the promoters of this application is not that wide, an there is still a lot for them to learn from industry leaders like Performancepoint, Dundas and Panorama. But I have no doubt that this is a RAW GEM and the data visualization it offers is very impressive. Many would argue that it might not be efficient and collaborative, which even I would agree, but it would not fail to appeal the users ( senior management ) of the organization for sure. And some of the features like exporting to formats like AVI and EXE which have never even been heard of, should make the minds of industry leaders ticking in this direction.

Dundas Dashboards v2.0 Technical Evaluation Considerations

Dundas Dashboard v2.0 is in the beta stage and soon going to get released. I am a big admirer of Dundas Data Visualization, and I always wish that after digesting the codebase of Proclarity, if Performancepoint can manage to consume a few data visualization components of Dundas considering it as dessert, the meal would be complete.

As usual I had a quick sortie over the features of upcoming version of Dundas Dashboard, and I feel that some of the features are quite revolutionary, some of them are just leveraged, and some do not manage to influence my viewpoint. Below is the summary of my views:

1) OLAP Support: The features related to OLAP supported are quite okay and in fact necessary to create a bridge for OLAP data to land into Dundas Dashboard territory. One of the features that catches my attention is, it allows creation of what they term as virtual cubes. It would be interesting to see how this feature adds value over perspectives created in SSAS for the cube.

2) Sharepoint Integration: Apart for other normal features, one striking features is yet another star in constellation of Sharepoint webparts - a dashboard viewer web part. This can effectively mean that, you can view multiple dashboards on a single dashboard ? To me this would not make any sense from a usability viewpoint. But yes it can be that using the web part, one can have more grip over customization of the dashboard.

Dundas wins in this aspect over performancepoint, where it facilitates dashboards to make it's way out of Sharepoint world. Dundas dashboards can be used in .NET and other host applications, while this is not possible with Performancepoint. To be honest I was not aware of this feature of dundas, till recently this came to my knowledge.

3) KPI Stubs: I like this feature, and it reminds me of my days in .NET development when we used to start with prototyping until the project sign-off was confirmed. It allows prototype creation for the dashboard without KPIs being available, and I am quite interested to learn how can the same be facilitated in the workflow. How placeholders can be arranged for different sections of a dashboard ? This feature is called Dundas DashFlow.

One unique part that I recently learned about Dundas was that, it facilitates usage of KPI with charts, gauges and other elements while in performancepoint, usage of KPI is limited just to scorecards. This is one major weapon in the technical arsenal of Dundas against performancepoint.

4) Dundas Dashboard Datasets: This feature does not manage to make an entry into my head at all. It allows usage of database tables in dashboard, claiming that skipping of KPI creation would help shortening development time. This seems like more of a step to provide a spinal cord to the feature I described in point 3. I would definitely like to find out how and why Dundas feels this is helpful.

5) Dashboard and Data Visualization Templates: This can be seen more of like master pages in .NET world or CSS for web pages. I do not find anything great about it, apart from the happiness that it offers templates. I like this template concept as it saves a developers life from getting drowned in learning CSS and decoration related workflows of sharepoint.

6) Data Visualizations: If performancepoint would be hearing this, it's answer would be "Okay, I quit this round". There is no comparison where is comes to data visualizations of dundas, as it's the USP of dundas.

Dundas Dashboard Map can be thought of it's own version of Bing layer. It would facilitate viewing of geographical and geometric data, and to the best of my knowledge performancepoint and bing has never been even discussed together to the best of my knowledge.

7) Mashups: It can be seen as something like the concept our ReportBuilder in SSRS, allowing business users to work on a superlative version of Excel flavor customizations. I feel that Virtual Cubes and Mashups are more or less overlapping.

8) User Annotations: It can be seen as discussion forum on a dashboard ! It might be useful in a way, but to be honest, if I were a power user, I would like my dashboard clean and capture all the analysis in some tracking system like Mercury Quality Center. This still is a good to have feature and it makes the dashboard more interactive, but only if it's left to interactivity and not made a discussion forum.

Check out this page for a view of detailed features of Dundas Dashboards v2.o beta and Dundas video explaining features of this version. This version definitely weighs heavier compared to performancepoint as of the present offering landscape.