SQL Server 2008 Business Intelligence Development and Maintenance Toolkit Available

/0 Comments/in /by

Today was supposed to be the birthday of the SQL Server 2008 Business Intelligence Development and Maintenance Toolkit by Microsoft Press but it’s not available with retailers, such as Amazon, yet. I guess a couple or so more days as the book is finding its way to resellers.

As with the 2005 version, I was privileged to work together with Erik Veerman and Dejan Sarka (all SQL Server MVPs) on the new revision. My part was the four Analysis Services chapters. Besides updating the book for SQL Server 2008, we re-worked the entire material to flow more logically and make this resource even more useful to help you prepare for the corresponding 70-448 exam.

I hope you’ll find the toolkit useful and pass the exam to certify!

Overwriting Parameter Defaults

/0 Comments/in /by

Issue: An interesting question popped today on the discussion list about overwriting the default of a cascading parameter. In this particular case, the report has two parameters: Frequency and HireDate. The HireDate parameter is dependent on the Frequency parameter. Specifically, when the user changes the Frequency parameter, custom code changes the default of the HireDate parameter. Sounds easy, right? Well, not so fast.

This requirement calls for smashing the HireDate default. However, smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing. There exist uncommon cases such as this one where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, Reporting Services doesn’t not currently have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, the current design handles the common case. (Note: In SQL 2000, SSRS defaulted to smashing the user’s selection with the default when an upstream parameter value changed. And, this resulted in a large volume of customer complaints.

Solution: So, what’s the solution? One option you may want to explore, which the sample report demonstrates (attached), if the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, SSRS is forced to re-evaluate the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default. This is I set the HireDate available values to the same expression, so its available value always changes.

[View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/parameter_5F00_default.zip]

Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies, April 2009

/1 Comment/in /by

As I mentioned in a previous blog, one very important SSRS enhancement in the recently released SQL Server 2008 SP1 is Report Builder 2.0 ClickOnce. This lets you reconfigure the native and SharePoint integration modes to let the user use Report Builder 2.0 instead of Report Builder 1.0.

Note that in SharePoint integration mode, installing SP1 only is not enough. That’s because there are actually two instances of RB2.0 ClickOnce. If you install SQL Server 2008 SP1, this will provide an update for native mode only. For SharePoint integration mode, you must refresh the Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies. This is necessary because the ClickOnce bits must reside on the SharePoint server. The April 2009 version of the add-in can be downloaded from here.

SSAS Import Wizard to the Rescue

/0 Comments/in /by

Sometimes, it’s better just to reset. I came across an issue concerning processing a partition whose containing measure group joins a dimension via another reference dimension. Specifically, when I attempt to process the partition, the server would error out with:

The binding for the ‘dimension key’ column is not a ColumnBinding type

What made this issue interesting is that I was able to repro the issue each time I would process the partition but the Microsoft support engineer, who was assigned to this issue, hasn’t been able to. After some digging we realized that the issue only happens in project mode, that is, when working with the project source in BIDS and deploying to the server.

How do we fix this horrible issue? We reverse-engineer the deployed cube using the SSAS Import Wizard to regenerate the source code.

  1. In BIDS, click File->New->Project.
  2. In the New Project dialog, select Import Analysis Services 2008 (or 2005 depending on the version you have) Database.
  3. Specify the location of the deployed database. BIDS will reverse-engineer the database to its source code.

Magically, after this procedure the error went away although we are still working on finding out what caused the error with the original code.