Kasper de Jonge PowerPivot Blog

How cool is that .. a free SQL Server 2008 R2 ebook!

The book contains 10 chapters and 216 pages, like so:

PART I   Database Administration

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2   Multi-Server Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189

You can download the ebook in XPS format here and in PDF format here.

SQLCat released Technical Article about the Planning and Deployment of PowerPivot

This white paper contains information for technical decision makers, IT administrators, and system architects who are planning to deploy PowerPivot technology in an enterprise environment. This paper assumes the audience is already familiar with the Windows Server® operating system, Microsoft Office, SharePoint, SQL Server, online analytical processing (OLAP), and self-service BI.

Well worth the read:

http://sqlcat.com/whitepapers/archive/2010/04/14/microsoft-sql-server-2008-r2-powerpivot-planning-and-deployment.aspx

Last weekend i was at the dutch SQLZaterdag (SQLSaturday) where I attendend a session from Hans Geurtsen who showed us the new features of Reporting services 2008 R2. Among others he showed us the new Group Domain Scope property to align columns, i just had to find out how this works. I knew of a excelent blog post by Sean Boon where he used the Domain scope property to align the axis of a sparkleline.

Ok what is the problem, lets say we have a few tablixes with sales per month, not every year has sales for all months, this would result in the following report:

What we would like to do is align the months so each month will be shown at each tablix, i designed the report to place the Tablix within a list.

I named the parent list “Maintablix”, the Group Domain Scope needs to have a scope to sync the axes against. You cannot just add tablixes and sync them, you need to have one parent tablix to sync your child tablixes against. When you create a report like this you can add the parent tablix name to the DomainScope property to your column properties of the child tablix: 

this will result in the following report, as you can see the columns are now aligned:

This is a pretty nice feature that could prove very handy, but what I really would like to see to sync axis over multiple tablixes, i couldn’t get that to work and i don’t think that is possible.

I recently had a question concerning reporting services, they wanted to compare sales of a month to the average sales per month like in the report i reproduced in the report below:

This is not easy in reporting services 2008 and before. To solve this problem we need to divide the total sales by the number of months, which you can obtain by using the CountRows function. But then you have a problem with the country’s. It would be solvable, but using a lot of tricks.

Suddenly i remembered a blog post from Robert Bruckner which mentions the new features of SQL Server 2008 R2:

Aggregates of Aggregates
This enables report authors to nest RDL aggregate expressions inside other RDL aggregate expressions with unlimited nesting levels.  For example, the expression =Avg(Sum(Sales, “Month”), “Year”) would compute the average total monthly sales

This could solve my problem very easy using the new expression:

=avg(Sum(Fields!Internet_Sales_Amount.Value),”Calendar_Year”)

This will give us the avg of the total sum of sales per month per year. We then compare it to each other and determine the percentage.

Using the aggregates of aggregates functions could make my life a lot easier in my daily work, just like the Lookup function i talked about before.

The release date will probably be May 2010:

http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

Can’t wait !

Chris Webb is at the SQL Pass and blogging live from John McAllister’s session on Master Data Services at the PASS Summit, and here are some notes…

• The first public beta is due next week
• MDS is will be packaged with 2008 R2 (rather than Sharepoint), be on the DVD but not part of the main install
• Will have an API – everything you can do in the UI, you can do in the API
• Although it’s part of SQL Server it will still rely on Sharepoint for workflow; the main, web-based UI is not Sharepoint-based though
• Has simple business rule validation capabilities, eg make sure that the list price of a product is greater than its cost
• Includes basic documentation features
• Also has auditing features – you can see every transaction ever made in the system, reverse changes made and so on
• Models are containers for different types of data (products, customers); every model can have a version, and versions can be locked, open for editing etc; models can also be secured
• Also has basic notification features, so users/groups can get emails when something changes
• No direct SSAS integration at the moment, but they hope to have some in the future

Great stuff ! Essentially the SSAS integration great stuff ! Thx Chris for sharing!

By accident I heard one of our sales people talk about showing data from a geographical location on a report. I immediately jumped in the discussion and told about the new Spatial datatype of SQL server and the reporting data region Map. Of course when talking to Sales people the immediatly asked for a demo. In this blog post my findings about Spatial data, Reporting map control and Bing maps. My starting point was Robert Brucker’s reporting blogpost, and try to recreate his demo. Had it up and running in no time.

The first thing i had to do was create a Geography data type, since this is the base of the Reporting map control. This was pretty easy since i had some test data with Latitude and Longitude as float in a table. All i had to do to converting these to a geography datatype was:

update Location set GeoPostion = geography::Point(Latitude, Longitude, 4326)

There are several ways of different ways to add point data in SQL Server 2008, i found them at this blog post: http://blog.colinmackay.net/archive/2008/02/07/1812.aspx

Next up was creating some kind of representable chart, i used the steps as described by Robert Brucker to create a report of my data, the one thing that immediatly caught my attention is that the Reporting map control using Bing maps is data aware. The Bing map automatically centers and zooms in at my locations (i used the Person table from Adventure Works to shoot this screenshot, as there are persons from around the globe he centers on the world).  I created a view of the data so i can change its source on a later stage:

Next up was choosing the visualization of the data, i wanted to create a analytical overview so i chose Bubble map:

Since i wanted to show some analytical data i changed the query to point to my Dutch data and added an amount field (which i filled ranomly with values 15 to 2000) using a top 50 of the Netherlands, you can see the data awareness again, the map switched to the Netherlands only.

In the data visualization step i selected the Bubble size property along with a colour scheme to represent my data:

Resulting in the following map in reporting services, so no programming necessary (except the Query of course):

It would be great if users could zoom in on the data .. luckily the data is a layer in reporting services so the data points are reporting objects where you can set properties like an Action:

So i turned my view into a stored procedure with a parameter and using the data awareness of the reporting control i called my own report with a reporting parameter that selected the top 50 in a region (the Id they clicked)

Using the same report for master and detail, this results in the following map when clicked:

Conclusion: Reporting with geographical data has become very very easy and offers some great possibilities to report developers.

Microsoft has relase a standalone version of  Report Builder 3.0, August CTP, you could start it throught the report manager already.

A small stand-alone MSI for Report Builder 3.0, August CTP, is now available for download here: http://go.microsoft.com/fwlink/?LinkID=160384

Download packages for SQL Server 2008 R2 August CTP are available here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=e19689bd-38dd-46c4-8645-f58ca4d61d1f