My first article on SQLServerCentral.com - Microsoft Business Intelligence Project Booster Kit

Today on 24-Nov0-2009, my first article has been published on SQLServerCentral.com. And I feel extremely happy and excited about this. Many a times I get confronted by an expectation that I need to push my team towards delivery through technical expertise, by empowering them with whatever they need and thus decreasing time to delivery which can result into increased revenues.

It's not always that one himself / herself needs to be a super expert. Knowledge is always power and there's no doubt in it, but when you work in a team, you can't expect each and every team member having same level of expertise and the same speed of execution of development. So remaining within the boundaries of costs, as a team lead one needs to educate as well as facilitate oneself and team with tools and stuff that can boost up the development process and reduce time in tackling known challenges.

Microsoft Business Intelligence Project Booster Kit is my first attempt to share my experiences about a basic set of tools and documents / documenting methods which I term as "Booster Kit" that can help any development team with stuff they can use in their day to day work to help them gear their project faster directly / indirectly in the shorter / longer term at one / another phase of the project life-cycle.

If you came across my blog through my article on SQLServerCentral.com, please feel free to drop your valuable feedback a comment on this post. Also feel free to reach out to me on siddhumehta_brain@yahoo.co.in.

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.

Free Tool to synchronize files and folders

SyncToy 2.1 is a free application that synchronizes files and folders between locations. Typical uses include sharing files, with other computers and creating backup copies of files and folders.

In project environments, where data feeds are received in the form of files, in a development environment such tools are quite helpful. Also for general content management, having one such tool which is also FREE is a big boon. Below in an excerpt from the download page:

In some cases it is necessary to regularly get copies of files from another location to add to primary location; in other cases there is a need to keep two storage locations exactly in sync. Some users manage files manually, dragging and dropping from one place to another and keeping track of whether the locations are synchronized in their heads. Other users may use two or more applications to provide this functionality.

Now there is an easier way. SyncToy, a free PowerToy for Microsoft Windows, is an easy to use, highly customizable program that helps users to do the heavy lifting involved with the copying, moving, and synchronization of different directories. Most common operations can be performed with just a few clicks of the mouse, and additional customization is available without additional complexity. SyncToy can manage multiple sets of folders at the same time; it can combine files from two folders in one case, and mimic renames and deletes in another case. Unlike other applications, SyncToy actually keeps track of renames to files and will make sure those changes get carried over to the synchronized folder.

New Features in Performancepoint Services 2010 , Difference between Performancepoint Server 2007 and Performancepoint Services 2010

I worked on a Performancepoint last year for one of the end-to-end cycle of BI Implementation using the Monitoring and Analytics Module. There were quite a lot of shortcoming and it seemed very apparently like an incomplete product, released in a hurry.

With the advent of Performancepoint Services in Sharepoint Server 2010, it seems like there are lot of new improvements from a user interface and designing point of view. One of the feature that was very strange was that, the graph that was available had only two colours, and there was no option to add another colours to it. We struggled quite a lot to find out a solution for the same but without any success.

Looking at the new features that are available in Performancepoint Services, the graph control looks much more matured now with the Pie-chart that was missing in the earlier version. Also there are new visualization available for the representation of data, more detailed representation of KPIs and many more. It seems like now Performancepoint has digested Proclarity to a great extent.

For a complete list of new features in performancepoint services 2010, check out this article.

Free Dashboard Basics Tutorial / Online Training

For those who are new to Performance Management, there is a FREE and useful course for Dashboard basics. This course would also give insight on how a Performancepoint Dashboard can help business. Check out this online course:

PerformancePoint Monitoring and Analytics: Dashboard basics I

PerformancePoint Monitoring and Analytics: Dashboard basics II

What is Excel Services ? What is REST API ? What is Excel Services 2010 REST API ?

What is Excel Services ?

Excel Services 2007 shipped in Microsoft Office SharePoint Server 2007 as part of the Enterprise CAL. Excel Services 2010 provides real-time, interactive, Excel-based reporting and dashboard capabilities which ship as part of SharePoint Server 2010. Also it includes APIs which enable rich business application development. One such API is Representational state transfer ( REST ) API.

What is REST API ?

A RESTful web service (also called a RESTful web API) is a simple web service implemented using HTTP and the principles of REST. Such a web service can be thought about as a collection of resources. The definition of such a web service can be thought of as comprising three aspects:

• The base URI for the web service
• The MIME type of the data supported by the web service. This is often JSON, XML or YAML but can be any other valid MIME type.
• The set of operations supported by the web service using HTTP methods (e.g., POST, GET, PUT or DELETE).

More about REST can be read on Wikipedia.

What is Excel Services 2010 REST API ?

The Excel Services 2010 REST API is a new programmability framework that allows for easy discovery of and access to data and objects within a spreadsheet. The data, including charts, that is returned by the REST API is not static – it’s live and up-to-date.

With the REST API, any changes in the workbook are reflected in the data that is returned. This includes the latest edits made to the workbook, functions that have recalculated (including User Defined Functions), and external data that is refreshed. The REST API can also push values into the workbook, recalculate based on those changes, and return the range or chart you requested after the effects of the change have been calculated.

By crafting the proper URI, the REST API allows you to:

• Discover the items that exist in a workbook, such as tables, charts and named ranges
• Retrieve the actual items in the workbook in one of the following formats: Image, HTML, ATOM feed, Excel workbook
• Set values in the workbook and recalculate the workbook before retrieving items

The content above is a re-draft of the original article on Microsoft Excel Blog, and it has been re-drafted for the sake of simplicity of understanding.

Agile Microsoft Business Intelligence Implementation - My next presentation at London SQL Server Users Group

BIDS and SSMS are not always sufficient for any MS BI Project. Right from the documentation that might be required just for compliance to tools that are able to fill up the gaps that SSMS or BIDS leave behind, a lot of accessories are required for a project for a leveraged development and delivery.

MS BI Project Booster is a kind of kit that consists of a collection of tool & utilities (freewares) and douments and/or documenting methods, that is good to have installed or available in advance, that can help any MS BI Team to tackle issues or cater requirements in a speedier manner at various stages of a project development life cycle. It's a resource kit, and can be used at various stages of the project and by the use of the same, I believe that a project can have the benefit of an Agile BI Development from an implementation perspective.

I have also submitted an article on http://SQLServerCentral.com on the same topic, so I would not publish any write-up on the same topic. Soon the article should be published and available for public reading.

Those of you who would like to make to the event, please register your seat. Entire details about the agenda, speakers and registration can be accessed on this URL:

http://sqlserverfaq.com/events/193/Joing-SQLUGWindows-Manageability-UG-SQL-Server-internals-and-best-practice-for-Absolute-Beginners-Tony-Rogerson-SQLUG-Session-from-WMUG-to-follow-MS-Business-Intelligence-Project-Boosters-Siddharth-Mehta.aspx

Free E-book : Sharepoint for SQL Server Database Administrators ( DBA )

Below is the excerpt from the Microsoft Technet site, where I found the below information regarding the free download of this e-book:

This book is a guide for database administrators who implement or maintain Microsoft Office SharePoint Server 2007. It provides guidelines for improving the performance of Office SharePoint Server 2007 solutions that run on Microsoft SQL Server 2008. It also provides information about key considerations for configuring storage and SQL Server 2008 before installing Office SharePoint Server 2007; the required steps in installing Office SharePoint Server 2007 when using DBA-created databases; backup and recovery considerations for Office SharePoint Server 2007, including what can and cannot be protected by SQL Server 2008 backups; and maintaining and monitoring SQL Server 2008 databases that support a Office SharePoint Server 2007 implementation.

The content in this book is a copy of selected content in the Office SharePoint Server technical library (http://go.microsoft.com/fwlink/?LinkId=84739) as of the date above. For the most current content, see the technical library on the Web.

Download this book as a Microsoft Word document (.docx) (http://go.microsoft.com/fwlink/?LinkId=168786).

Download this book as a PDF file (http://go.microsoft.com/fwlink/?LinkId=168793).

SSRS / BIDS 2008 Reports cannot be consumed by report rendering control in SSMS 2008, but only SSRS / BIDS 2005 Reports

I started working on a task to create a report that can be used in SSMS to keep track of SSIS packages and profile them. Jamie Thomson has written a code snippet that I would be using as a starting point for this project. In the course of developing this, I encountered my first barrier which I would like to share.

Develop any regular report using BIDS 2008 and SSRS 2008. If you use the same report for rendering in SSMS 2008 by using the Custom Reports functionality, the report won't get rendered however simple it would be. I got confused with this behaviour, and started investigating this issue over web. The error that I was getting continuously was "The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition".

After spending quite some time, I was able to find an issue on Connect that this is a bug with SSMS. The bug definition is as follows:

When I try to run a custom report in SSMS created with BIDS 2008 it fails with the following error message:

The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded. (Microsoft.ReportViewer.Common)

As of now this bug has no workaround, and the only possible solution is to install BIDS 2005 and even probably SSRS 2005 to develop a report (or if someone has a tool to work with RDL 2005 specifications) side by side to SQL Server 2008. I can't believe, all this to just develop a simple SSRS 2005 report which can be consumed by dumb report control in SSMS 2008.