Kasper de Jonge BI Blog

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

Even though it’s my holiday i couldn’t resist to download and install SQL Server 2008 R2 and play around with Gemini. I forgot you have to have access to Excel 2010 to do so, luckily some co-workers have access to the Technical Preview so i could get it there.

After installing SQL Server 2008 R2 and Office 2010 i still didn’t have a Gemini tab, it appears you have to download the Gemini add-in separately, you can do so here: https://sharepoint.connect.microsoft.com/SQLServer/Gemini. You do have to get access from Microsoft before you can download the plug-in.

Ok on to Gemini.

After installing the plug-in you get an extra tab in Excel 2010 that looks like this:

To start we have to load & Prepare data, i decided to use the AdventureWorksDW2008 database and recreate a part of the AdventureWorks cube.  When you click the Load & Prepare data button the Gemini client will open in a new window.

As you can see there a few options to choose from, i’ll choose the database now but the Data Feed and Report options look very promising (think connecting Berlin to the Data feed in an ESB environment).  After clicking the From database button you have to choose a data source and then select a table or write a query of the data to import. I’ll choose for a the DimSalesTerritory and the FactInternetSales tables to start with, you can even choose a friendly name:

At the last step the preparation is completed:

The Gemini add-in has now loaded the data into the Gemini client

It even found the relationship on the tables and created it in Gemini as we can see in the manage relationships window:

Now before looking at the result i want to add the time dimension to the data by hand, to do that we need to click the  “from database” button again and add the DimDate table

Now important in Gemini is to create the relationships for each table to the fact table, we’ll do the DimDate by hand, click on Create relationships and select the corresponding fields:

Make sure you get primary and foreign key in the right order. Now its time to see the result and add the data to a PivotTable by clicking the PivotTable button.

You now have a full working PivotTable in excel:

After playing around with data you can’t even feel the difference between the PivotTable on the AdventureWorks cube and the same data from Gemini. Great stuff and very powerful.

The only thing i can’t get working are the slicers, they keep coming up empty where there should be data:

Thanks to the comment of Tatyana below the slicers work as well, i love this functionality, it, besides looking great, works great too

Overall a very powerful tool and i see many great applications for it. I for one am very enthusiastic and I know some clients who are waiting for this.

Although i have my reserves about it still being somewhat too technical with the relationships for the real business analysts, it would be better if the relations where somehow automatically suggested when you add a table. I would see a DBA creating various views on the datawarehouse which the business analyst then can use to analyze. On views relationships don’t exist so Gemini won’t recognize them, a automatic relationship recognition would be great. Well maybe after some training the analysts will be able to do it themselves with the right training.

UPDATE: news on twitter travels fast i got a reply from Donald Farmer on my blog post and apparently MS is working on automatic relationship detection right now. Can’t wait to see the final product

Today we are excited to announce the availability of the first community technology preview (CTP) of Microsoft SQL Server 2008 R2 for MSDN and TechNet subscribers, with the CTP generally available on Wednesday, August 12th.

Building on the momentum of SQL Server 2008, “R2” improves IT efficiency by reducing the time and cost of developing and managing applications; empowers end users to make better decisions through Self-Service Business Intelligence and enables organizations to scale with confidence by providing high levels of reliability, security and scalability for business critical applications.

This public preview offers the opportunity to experience early, pre-release feature capabilities including:

• Application and Multi-server Management
• SMP scale up with support for up to 256 logical processors
• Report Builder 3.0 with support for geospatial visualization

This CTP provides the first opportunity to explore some of the features of SQL Server 2008 R2 and see how it all comes together to enhance performance and scalability, enable self-service BI and improve IT and developer efficiency. The CTP process is also a great way for you to give us feedback on the new technologies.

Read more at: http://blogs.technet.com/dataplatforminsider/archive/2009/08/10/download-sql-server-2008-r2-august-ctp-today.aspx

Microsoft released a BI update to SQL Server 2008, aside from Gemini it has some other great new functions like the Lookup Function in reporting services. I was waiting for this one, you can lookup the first matching value for the specified name from a dataset that contains name/value pairs. This gives you the opportunity to join 2 datasets and whats best from 2 different cubes!

One mayor drawback is that you can only join on one key and a SSAS dataset usually doesn’t have a single key since you have data on an x and y axel, but with some custom work we can fix that.

In the following sample I have put data of 2 adventureworks datasets on one tablix. Each dataset contains year and Sales Territory Country and a measure. Lookup uses 2 keys to match the data from 2 datasets, I made a new unique key for each dataset combining the the uniquename of the x and y axel and added it as a calculated field to each dataset, added a field to each dataset with value “Fields!Sales_Territory_Country.UniqueName + Fields!Calendar_Year.UniqueName”.

Add the value of one dataset to a tablix

Then add a new column with the following expression to add the Internet_Order_Count value from the other dataset to the tablix:

=Lookup(Fields!CombKey.Value,Fields!CombKey2.Value,Fields!Internet_Order_Count.Value,”DataSet2″)

and thus adding the value from the 2nd dataset to the tablix, works like a charm. I even tested putting a filter on dataset 2 to make sure there aren’t an even amount of values and that works too, it just returns a null value.