Kasper de Jonge BI Blog

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

I made a blogpost yesterday about Gemini and in it I showed an issue about Slicers in a PivotTable that kept turning up empty:

Tatyana from Microsoft apperently read about the issue and posted the resolution in a comment:

Go to “regional and language options” in control panel and change current format to English (United States). That should fix slicers issue.

And indeed it did fix the issue, great fast reply from Microsoft:

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.

The Microsoft World Wide Partner Conference 2009 is just finished and loads of BI news, i made a small linkflood:

MS BI wpc round up for microsoft bi
Office 2010: Excel 2010 – New Buttons on Ribbon for Pivot Tables – Custom Named Sets!
What’s new for Analysis Services users in Excel 2010?
First impression of Excel 2010

Some great stuff in here !  I will try to get a hold of Office 2010 Tech preview to try it out myself !

Setting up security for SSRS in SharePoint integrated mode can be a bit tricky, particularly if you want to set up some of your users to only be able to run reports, but not to be able to modify or change them. I found a great post explaining how to create a Reporting group in SharePoint:

http://agilebi.com/cs/blogs/jwelch/archive/2009/07/10/view-permissions-for-reporting-services-in-sharepoint-integrated-mode.aspx

I needed to determine tha last day of previous month in a view, i found this on internet and works great:

Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

found this at http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

I regulary have to load some Excel sheets into a datawarehouse, what happens a lot is that some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.

This appears to be a strange behavior of the Excel ISAM driver, when you load a column it reads the first 8 rows and tries to determine what it should load the data values as. Now if your data has a mix of characters and numbers, you need to make sure you reflect this in the first 8 rows or it will not load correctly. The official explanation and workarounds can be found at ms support: http://support.microsoft.com/default.aspx?scid=kb;en-us;194124

My users want their report to default select the last year and month available in the cube, most of the times this wont be the current month as the dataware house is behind. In a previous blog post  I talked about creating date time sets like current year, month, this way we can select the last month and year. I want to use these sets as default value.

I found a litte trick to make selecting the default parameter a walk in the park

I created my report as i would usually and created 2 parameters year and month, thus automaticly creating 2 datasets with all the data from year and month attributes. 

Now here comes the tricky part, i copied the mdx created by visual studio and added a filter using the set i have in my cube and created a new dataset called CurrentMonth. The MDX ends with “ON ROWS FROM [CUBE]“  and you can change it to ”ON ROWS FROM ( SELECT ( [CurrentMonth] ) ON COLUMNS FROM [CUBE])” using the set which returns the current month. This new dataset returns the current month and the All dimension, i excluded the all dimension in a filter on the dataset.

Now you can use the new dataset to set the default value in the parameter by selecting “get values from a query” and selecting the new dataset and as value ParameterValue.

This will enable you to set the default value on default generated parameters.