Powerpivot Books , Training , and Installation

Powerpivot resources are now out and available for public download.

The long awaited learning material on Powerpivot is now available online on BOL, and can be accessed from here.

Download instructions and links to download locations for different flavours of Powerpivot can be accessed from here.

Those who are not able to download and install Powerpivot (as it also requires installation of Office 2010 beta in one or another way), need not to get disappointed. A Virtual Lab of Powerpivot for Excel 2010 Introduction is available from Microsoft. Aspirants can use this lab, and get their hands-on this lab to get the feel of Powerpivot without bothering about any download or installation.

This Virtual Lab can be accessed from here.

Microsoft Business Intelligence ( MS BI ) products on 64 bit / x64

Today all the 2010 version of products got released, and made available for public download. If you carefully study the preliminary requirements of the new line of products, most of them are shifting towards the primary requirement of 64-bit.

Let's start one-by-one.

Sharepoint Server 2010 contains Performancepoint Services for Sharepoint, which is the performance measurement or dashboarding solution candidate from Microsoft. Also I anticipate that it will also contain Powerpivot Services for Sharepoint (PSS).

Sharepoint Server 2010 has the below mentioned preliminary requirements:

• SharePoint Server 2010 will be 64-bit only.
• SharePoint Server 2010 will require 64-bit Windows Server 2008 or 64-bit Windows Server 2008 R2.
• SharePoint Server 2010 will require 64-bit SQL Server 2008 or 64-bit SQL Server 2005.

From the above set of requirements it's very easy to make out that if one wants to have Performancepoint Services and/or Powerpivot Services for Sharepoint or even just Sharepoint Server 2010 for hosting of it's dashboarding solution, the entire landscape of Microsoft BI line of products used in the solution including the Operating Systems needs to be 64-bit. Also the hardware needs to be 64-bit compliant.

If the above is not enough, there's still more left. I recently came to know from one of the threads on SSIS forum that Master Data Services, which is Microsoft's new offering in SQL Server 2008 R2 (and is expected to be released in the coming quarter) for Master Data Management is going to be 64-bit only.

So for aspirants who are keen on getting their hands-on the new products are left with fewer options to try out all these new features, as most of us do not have 64-bit operating systems or hardware within our accessibility. The only hope is that, if Microsoft releases a new Microsoft Business Intelligence VPC that comes loaded with all these softwares, like the previous ones.

Powerpivot Data Analysis Expression ( DAX ) Functions PDF

Data Analysis Expressions ( DAX ) is the new query language or expression language of Powerpivot. It also has a rich set of functions which are almost similar to Excel functions. A dictionary of all the DAX functions are available for download from PowerPivot-info.com. Most of the functions sound very similar to Excel, except the Time-Intelligence functions.

The functions listed in the Time-Intelligence section looks very much aligned towards the structure and usage that we find in the Date / Time dimension. I wonder why all this information is not available on MSDN yet, or at least I am not able to locate down on MSDN.

Powerpivot Client Architecture

With Powerpivot now available to users, more information about architecture and theory revolving it is emerging out slowly on Blogosphere. I recently went thru an article where the author has posted about the client side of powerpivot, which is the powerpivot add-in.

Below is the summary of what I was able to extract out of the article, that I felt of interest:

• PowerPivot processing engine is called VertiPaq
• VertiPaq engine uses AMO and ADOMD.Net for internal processing
• Powerpivot add-in sends requests to this engine using different transport protocols depending upon provider. Transports like HTTP & TCP/IP are supported.
• Powerpivot add-in is developed using C#.Net and other managed libraries of the .NET Framework. .NET Folks can be proud now as they have reserved a seat on this space.
• All the components of the Powerpiovt architecture i.e. Excel, Powerpivot, AMO and ADOMD.Net are implemented and works in-process. This means crashing of any of the component involved in the architecture would crash all the components. Vertipaq crash is an excel crash makes sense to me, but Excel crash is Vertipaq crash is hard for me to digest. As of now, I am not sure if this is a mole or mountain sized limitation, but for sure this is a limitation.
• Powerpivot System Service (PSS) is probably the Sharepoint version of Powerpivot implementation. Pairing of this service with Performancepoint services, would make Sharepoint a big player of Microsoft BI implementation toolset. This definitely has the potential to bring Sharepoint in the league of SSMS and BIDS, or some would probably argue that Sharepoint already is in this league.

Use this link to read the original article.

How to generate SSRS Report output by programming using SSIS or .NET

A new SSIS Task is available on codeplex, named SSIS ReportGenerator Task. As described on it's homepage, the description about this task is : "SSIS Task for SQL Server 2008 to create Reports from a recordset data source."

This task wraps a nice functionality of generating report output behind the scenes, without requiring any programming knowledge for the same. It has a nice User Interface available, with option to view the report during task execution. All the options are self-relevant and the author has provided a nice example of how to use the task on the home page.

The technique employed by this task to generate report output is quite common in the .NET programming arena. Report Server Web Service endpoints are used for managing objects on the report server and execution of reports using reporting service. ReportService2005 / ReportService2006 Endpoint is used for managing objects on the report server, and ReportExecution2005 Endpoint is used for execution of reports.

This component collects all the data from the options selected by the user, and sets it's properties. Value of these properties are in turn passed on to the object of ReportService2005 Endpoint. Finally this object is passed as a parameter to ReportExecution2005 Endpoint and the report is generated programatically using the WebService.

Those who are novice to the concept of webservice and end-points in the context of reporting services, start from this link to learn about the same. After the same, the next step can be to learn more about Report Server Web Service end-points and how to create a client for the same. Finally once you master the same, the source code of this task is the final lesson to learn the best way to implement it. The source code of this task is available for download on codeplex.