Silverlight Applications ( .XAP ) and Webpart in Sharepoint 2010 Video Tutorial and it's use in MS BI

Silverlight is one of the technologies that is being introduced slowly in all the Microsoft platforms, and Microsoft Business Intelligence is one of them. Though we do not have any applications or services in SQL Server that provide a special support for silverlight applications, but sharepoint 2010 has now got some great support for silverlight applications. Below are some of the facts that are good to keep in view for any business intelligence professional, as the same can help during the planning of any physical / technical design of your BI solution.

Firstly here is a great tutorial on Silverlight Application and Webpart in Sharepoint 2010, but BI folks who might not have any programming knowledge about .NET might not be able to understand a major part of it. Still they can go thru the summary that I have explained in the below points.

1) Silverlight Applications can be developed using Visual Studio 2010, which also has got interfaces to develop and deploy it over sharepoint.

2) Output of any Silverlight application would be a .XAP file, which can be thought of as a .EXE or .DLL file that comes out of any .NET based application.

3) Sharepoint 2010 has a separate webpart for Silverlight called the "Silverlight Webpart" under the "Media and Content" category. And this webpart just need to be provided with the URL of the .XAP file which can be hosted on Sharepoint 2010.

4) These Silverlight Webparts can also be customised or enhanced, to suit the needs of the solution. This can be thought of as a custom component which we can develop in SSIS using script and .NET.

5) With the latest version of Silverlight (version 4 I guess), we have the facility of installing and executing Silverlight out of the browser as a separate application, without the need of any browser.

Below is one of the example which shows how the above facts can be useful in your BI solution planning:

Dundas Dashboards and Telerix Dashboards are providers of Silverlight based dashboarding solutions. Let's take example of Dundas Dashboards. Dundas Dashboard Viewer is a silverlight application, which can be thought of similar to a report viewer control that is available with any .NET application. Now this application is a .XAP file, and this can be programmed to view any Dashboard components or application developed using Dundas Dashboards. Host this .XAP file in a Silverlight Webpart in Sharepoint 2010. When this webpart is made available to any page on Sharepoint, this would make the entire dashboard available to any user.

By the above explanation, the point that I intend to make is that though BI folks would not take much interest into Sharepoint 2010 or Silverlight, it's better to keep information about components that we require to integrate parts of our BI solution into sharepoint.

Strategy Maps , Visio Services 2010 , Performancepoint Services 2010 and Sharepoint 2010 Business Intelligence

Dashboards are a very important end-deliverable in most of the business intelligence solutions. In Microsoft Business Intelligence stack of technologies, the same is developed using Performancepoint and hosted using Sharepoint. Scorecards, KPIs, Charts, Graphs, SSRS Reports are well known and commonly used constituent of any dashboard. But strategy maps is one of the less touched but quite valuable data presentation constituent on the real estate of any dashboard. Sharepoint 2010 now contains two services which would be of particular interest to the BI community : Visio Services and Performancepoint Services.

Performancepoint Dashboard Designer 2010 is a click-once application that comes as a part of Performancepoint Services in Sharepoint 2010. This can be thought of something similar to Report Builder that comes with Reporting Services in SQL Server.

Visio Services 2010 allows viewing of any visio diagrams hosted on sharepoint without any visio installation. If Silverlight is installed on the client machine, this diagrams would be displayed in an very interactive and appealing manner too. The interesting part of Visio services are it's data driven diagrams which allows binding of data from different data sources to the Visio diagrams. Watch or Download a demo of how you can bind data to a visio diagram using Microsoft Office Visio.

So now let's put these different chunks to use as mentioned in the steps below:

1) Bind the KPI data from analysis services to the strategy maps ( which can be created using Visio 2007 or 2010 ) and host this strategy maps on sharepoint 2010.

2) Using the Performancepoint Dashboard Designer 2010, create dashboards and link these strategy maps in a scorecard or web-part on your dashboard.

3) Visio Services 2010 would facilitate fetching of the data bound to the Strategy Maps (which is a diagram in Visio terminology) and display the visual accordingly on your dashboard.

This is what we call the better half of Microsoft Business Intelligence, i.e. Sharepoint Business Intelligence !!

SQL Server 2008 and MS BI Setup for learning and training - The Cheap Way is VPC and VHD

Professionals who work in countries with stronger economy have an invisible advantage over professionals who come from countries with poorer economies - being able to buy or arrange licensed software, hardware and training content. Mostly those who are focused enough are able to self-learn technology these days, as in my belief, information is the cheapest available thing available over the web today and trial versions keep life easy for technology aspirants.

Hardware and software are two of the challenges that developers face in learning new technologies which demands huge resources in terms of hardware and installation know-how. Also a technology which you want to try out might not just have an operating system dependency. There might be lot of supporting software components which might be required to be setup before you can use the product that you want to install and work with. Luckily that is not the case with SQL Server but this is definitely the case with Sharepoint. Also it might come as a surprise, but many developers would not have personal laptops or machines upgraded enough to handle SQL Server 2008 installation. And if you want to develop and test some solution on the same with something like Performancepoint Services (which would require Sharepoint) installation, most of the developers would leave even the idea of thinking about it.

Here is one way that one can still continue to fulfill their dreams of working on a full fledged environment without great investments over hardware or knowhow of installation. Microsoft offers downloads of Virtual Hard Disks which comes with pre-installed softwares for a few products and it comes for free. One should just have a Virtual PC installed and attach this VHD to it. Below are the VHD available which you might feel interesting:

SQL Server 2008 Enterprise Evaluation for Windows Server 2008

Office SharePoint Server 2007 for Windows Server 2003 R2

Windows Server 2008 R2 Enterprise Edition x64 (Full Install)

And if your requirement is a full install of all the MS BI related softwares, go for the MS BI VPC. Latest release in my knowledge as of this draft is version 7.1 and the links are available from here. This would save you from the pain of arranging high end hardware, installation know-how and other resources.

Considerations for opting Dundas Dashboards against Performancepoint

Recently I got a query from one of my blog reader which is the topic of this post. I thought of posting it on this blog, for the review from a greater audience. Below are my 1st level thoughts on the same. Feel free to correct me on the same, as these are just my views and not verified facts.

1) Developer expertise with dundas object model is a big concern and blocking factor in speedy and efficient development. Your team would be able to make it but cannot guarantee efficient development and in case of an issue, it would always be a debate whether it's a limitation with product or developer bug.

2) Most of the visual components are based on Silverlight, and scripting would require development team to learn object model for the same.

3) SSRS already has got now Dundas chars and graphs, so just dashboard part of it makes sense for use.

4) Performance impact - You need to develop a few POCs to make out if these components dump out lot of html or huge images like what Infragistics does. Should not be the case as Silverlight is supposed to be a thin client support, but still its a question.

5) If these are planned to be shared over a tool like Sharepoint for collaboration, these components would be treated as just another ActiveX components. Also if you plan to use sharepoint, PPS is already a part of sharepoint 2010.

6) I cannot comment from a pricing point of view, but Dundas Dashboards would come more economic than Sharepoint 2010. Also both cannot be compared as both are totally different areas, and PPS cannot be extracted out from Sharepoint 2010 though this may be your only requirement.

Dundas may definitely win over pricing, but over a technical tug-of-war, the same cannot be guaranteed.

SSIS Package not supported as a data source in SQL Server Reporting Services ( SSRS ) 2008 R2

In SSRS 2005 version, I worked a lot on the SSIS and SSRS area. One of the interesting application where both of these technologies worked hand-in-hand was using SSIS package as a data source for SSRS report datasets. With the SQL Server 2008 R2 (I am using the Nov CTP), SSIS Data rendering extension is not supported as a data source for SSRS !! This comes as a disappointment to me, thou there are means suggested on how to enable the same.

If you browse BOL for the same topics, you would find a Note that says "The SSIS data processing extension is not supported. This data processing extension is a non-production feature that is off by default. Using the SSIS data processing extension on a production server is not recommended at this time. If you choose to enable the feature and use it on your report server, be aware that at run time, the package will be processed under the security identity of the Report Server Windows service account or the Report Server Web service account. This has implications on how you secure the data sources that the package accesses."

Due to this move those applications that are using this feature would break in SQL Server 2008 R2 version. We can just hope that this would get fixed by the time R2 version is released in May. If not then we need to report it on Connect as a feature request and wait for some service-pack rain to bring this feature back to SSRS. Also I am confused by the point that if we can enable it and get it running, but it's not supported. If that is the case, it should not be made available at all in the release itself when it's not classified in the deprecated or to-be deprecated feature list.

SSIS Books , SSAS Books , SSRS Books , MS BI Books ( My Recommendation )

I am a book-worm and most of the time I spend my leisure time reading books, but only technical and related to Business Intelligence. I have read a lot of books on MS BI and out of those, I would like to recommend a few of those books which I felt are upto my satisfaction. The books below that I am recommending are for all kind of audiences, whether you are novice or a pro, you would find something from the below list for yourself. Also these books covers topics like data warehousing, SSIS, SSAS, SSRS and MDX.

1) MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase (Paperback)

2) Professional Microsoft SQL Server Analysis Services 2008 with MDX (Wrox Programmer to Programmer) (Paperback)

3) Microsoft SQL Server 2008 MDX Step by Step (Step By Step (Microsoft)) (Paperback)

4) Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer) (Paperback)

5) Delivering Business Intelligence with Microsoft SQL Server 2005: Utilize Microsoft's Data Warehousing, Mining & Reporting Tools to Provide Critical Intelligence to A (Paperback)

6) The MicrosoftData Warehouse Toolkit: With SQL Server2005 and the MicrosoftBusiness Intelligence Toolset (Paperback)

7) Foundations of SQL Server 2005 Business Intelligence (Paperback)

8) Microsoft SQL Server 2008 Analysis Services Unleashed (Paperback)

9) Expert Cube Development with Microsoft SQL Server 2008 Analysis Services (Paperback)

10) Professional Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer) (Paperback)

11) Professional SQL Server 2005 Reporting Services (Paperback)

DACPAC Video Tutorial : Developing Data tier Applications using Visual Studio 2010

SQL Server 2008 R2 comes with a new feature called Data-tier application (DAC). Thou it's an Enterprise Edition only feature and has limitations on objects that can be scripted in the .dacpack file that gets created out of Visual Studio 2010, it's a great feature to have, at least for the application developers as it provides them a nice control without getting involved into the complexity of T-SQL to deploy their database side logic and create an installer package out-of-the-box from Visual Studio.

Watch the video tutorial and learn how to develop data-tier applications using Visual Studio 2010 and SQL Server 2008 R2.

Today Visual Studio 2010 and .NET Framework 4 Release Candidate is available to MSDN subscribers and can be downloaded from here.

Also check out Entity Designer Database Generation Powerpack which is a free add-in for Visual Studio 2010 and has some great features on the lines of database generation technologies.

Download SQL Server 2008 R2 Videos , Tutorials , Presentations , Demos and Labs

SQL Server 2008 R2 is one of the most important milestone release in the BI history of SQL Server. It brings also with it, new feature/release like Master Data Services , StreamInsight, Powerpivot and some very interesting and useful enhancements to Reporting Services.

Many of us do pay for training on the new content, and hunt for information in the form of videos, tutorial and guides. Microsoft has made this easy for us to learn the new features of R2 release, by providing a SQL Server 2008 R2 Update for Developer Training Kit. This kit is full of resources you need for learning almost all the new features of R2 release as well as SQL Server 2008 also.

Start learning and get ready for R2 from this kit, as R2 official release is going to hit the market in May 2010. Make your CV and yourself R2 ready, and use this kit as an exercising platform for the same.

Download Free SSMS Add-in to search for any text within your database objects

I am a collector and admirer of free add-ins and I have been a admirer of SSMS Tools Pack. But after that, there are not many great add-ins available for SSMS. A handy-search is always a nice feature to have in any development environment, and even this holds true for database objects too.

SSMS Tools pack provides great features for search within the results returned by your query. But if you want to search for any particular text within your database objects, it required developing some form of scripts to query some sys tables. Redgate software has come out with a FREE SSMS ADD-IN the is very simple to use, very efficient in text search across the database server. It has a very nice GUI, much more granularity and details that what you get in SSMS search and the it's of a pretty small size (less than 3 MB) download.

I would say that this is the next best SSMS freeware utility that I have seen after SSMS Tools Pack. This add-in in called SQL Search and it's still in Beta, but final version is expected to just add some more details on the help feature, thou I don't think any help is required to learn on how to use this add-in. Download it and make your life more easy with your database development.

The only feature missing in this add-in that I would wish to have, is the ability to save this search or export the results of this search to Excel. At least it should allow to select the results from this search and copy it out. Presently it just allows to select one row at a time, so if one wants to select all the results and copy it to Excel to create an Excel report out of it, it's not possible.

Reference : SQLServerCentral (Aloha DBA)

Download Video Tutorial or Webcast on Performancepoint Services , Excel Services , REST API , Powerpivot , Sharepoint Business Intelligence

Any IT product that is used by masses cannot work in isolation, and arguably, same holds to true for SQL Server also. Sharepoint as per my opinion, is one of the biggest delivery partners for the Business Intelligence features that come out of the SQL Server MS BI stack of technologies. Sharepoint has more often been seen (in the group of people I know) as a tool for collaboration when seen from the perspective of a database or in fact a MS BI developer. Apart from being used as a collaboration platform, Sharepoint has come down a long way into the arena of Business Intelligence.

The key Business Intelligence features that are now unique to Sharepoint, which even SQL Server needs to complete the eco-system of any Business Intelligence project are Performancepoint Services, Excel Services, Visio Services which facilitate Strategy Maps, web parts that hosts SSRS and Excel Analytical Reports, and Powerpivot. There is a lot to know and learn in Sharepoint 2010 Business Intelligence Features.

How about a book that gives a higher level overview with a demo ? It might seem boring to SQL Server folks. But how about a video tutorial or webcast that takes you on a guided tour of all of these along with a demo, which is also available free of download !!! Yes, I am not kidding. This is a session presented by Mike Fitzmaurice, and this video is available for download from Channel9.

So download this video tutorial / webcast (whatever you call it) and get going on a Sharepoint Server 2010 Business Intelligence Overview. Be advised that this download is over 1 GB and contains over 1 Hr of video training. I recommend it as a must-watch video for MS BI folks.