Powerpivot as data source for Performancepoint Services 2010 - Whitepaper

Performancepoint 2010 supports different kind of data sources that can be categorized into Analytical data sources and Tabular data sources. Analytical (Multidimensional) data sources include any data from SSAS cubes and Powerpivot, Tabular data sources include data from sources like Excel spreadsheets, Sharepoint lists, SQL Server tables, MS Access tables and finally custom data sources. For custom data sources, one needs to use PPS SDK.

Using powerpivot as a data source is not straight forward or I can say that it's different. The reason it's different is that, powerpivot is something that is neither completely multi-dimensional, nor completely tabular. It can be seen as an intermediate between the two types of data sources. So if you are trying out your hands-on for the first time using Powerpivot as a data source with Performancepoint, it can baffle your mind by the way data from powerpivot is classified in hierarchies, measures and dimensions in your PPS Dashboard Designer 2010.

Answer to the above prospective challenge is a whitepaper from Microsoft. This whitepaper explains how to use powerpivot as a data source in performancepoint services 2010 ( in sharepoint server 2010 ). This whitepaper can be downloaded from here.

SQL Server data types serialization format

This is the 200th post on my blog and I am blogging this at 2:00 AM in the morning, and see what a grand resource I have got to make this a grand post. When we look at data stored in spatial data types i.e. geometry or geography ( I know hierarchyid is also spatial data type, but I am ignoring it for the moment ), the binary format looks alien to us, but in fact is called as WKB (Well known binary) format. Also this format is very structured and if you know the serialization logic behind the same, you can easily decode this metadata just by looking at the binary values.

Though one would not be required to use these kind of manuals on a day to day basis, but this can help to deepen your fundamentals about SQL CLR Types, and spatial datatypes in particular. Keep it as a manual to understand storage and serialization of each and every SQL Server data type. It's a PDF available for public download from Microsoft and the same can be downloaded from here.

Difference between SSIS 2005, SSIS 2008 and SSIS 2008 R2

There was no difference architecturally in SSIS 2008 and SSIS 2008, and there is no difference or enhancements in SSIS 2008 and SSIS 2008 R2 version except one. In the ADO.Net destination adapter, an additional property has been made available to bulk insert records when possible. And probably this is more of a move to support inserting data into SQL Azure. I had authored an article on the same subject which can be read from here. Those who are keen to learn difference between SSIS 2005 and SSIS 2008, check out the "SSIS New features in SQL Server 2008" series from here.

Theoretically, when you consider migration there should not be any issue between 2008 and 2008 R2, and I was under the same impression too. But as Einstein said, "In theory, theory and practice are same, but in practice they are not". Recently there was a migration issue reported on migration of packages from 2008 to R2, but this issue is limited to ADO.Net destination adapter control only. And just by modifying the XML of the package, the same can be resolved easily. John Welch explains this issue and the solution to the same in a very elaborate manner, which can be read from here.

Difference between SQL Azure and SQL Server - Whitepaper

There was a time when I used to wonder what is SQL Azure and just by the name I used to think that it's not the stuff that I would into which I would want to peep my head. Then finally when the trial version was out, I opened up my account and tried out everything that I wanted to break my curiosity. Also I authored two different articles on How to read and write data from SQL Azure using SSIS 2008 R2 and How to connect to SQL Azure using SSRS 2008 R2.

As soon as SQL Azure went RTM, all trial accounts were freezed out and got converted to production DBs. And if you want to access SQL Azure now, you need to be ready with your credit card, though whatever small may be the amount. So now if someone want to make a start and check out what SQL Azure is capable of, there no way to do it without paying for the same. Though it's not possible to practically get a hands-on SQL Azure to study the difference between SQL Azure and SQL Server, but this whitepaper can be quite handy and pilot quickly through each details of differences between SQL Server and SQL Azure. Cloud computing is the change in the making, and potential of SQL Azure should not be underestimated. If one day you find yourself in a situation where you need to migrate or develop applications based on SQL Azure instead of SQL Server, this is the first whitepaper that you would open out of your kit.

Microsoft needs to provide a SQL Express version of SQL Azure, I mean a free limited trial version of SQL Azure so that users can at least evaluate the product.

Download best of SQL Server 2008 R2 Feature Pack

SQL Server 2008 R2 Feature Pack is available for download and below is the list of features that are very attractive in my views. For those who are now aware of what is a feature pack, it's a collection of separate independent packages that can be used as tools or components with different services of SQL Server including database engine, SSIS, SSAS, SSRS and related technologies.

1) Download Microsoft SQL Server Report Builder 3.0 for Microsoft SQL Server 2008 R2: This is top highlight as it brings the Map control and Bing Maps capabilities, Sparklines, Databars, and Report Parts Gallery.

2) Download Microsoft SQL Server PowerPivot for Microsoft Excel: Business users would not want to wait and download it ASAP and try out the potential of Powerpivot.

3) Download Microsoft SQL Server 2008 R2 Books On-line: My personal favourite. It's always advantageous to stay up-to-date with the latest reference material.

4) Download Microsoft SQL Server 2008 R2 Best Practices Analyzer: This latest best practice analyzer is available now. So stop playing with the old 2000 / 2005 version analyzer and get this shiny new analyzer for your shiny new R2 version of SQL Server.

5) This is not a part of the Feature pack, but I thought of mentioning it as it's a very valuable resource. It's a introductory level " Free SQL Server 2008 R2 Ebook ".

Above list is not the exhaustive list. Entire feature pack can be downloaded from here.