Prologika Forums

PerformancePoint 2010

Teo Lachev — Fri, 03 Sep 2010 02:52:00 GMT

PerformancePoint? Is it still around? It is (in SharePoint 2010), and it should peak your interest if you are serious about dashboarding. The planning component is of course gone and I have to admit I never had too much faith in it. When comes to dashboards, Microsoft gives you two implementation options:

Reporting services reports in SharePoint web parts – Pros include low cost because Reporting Services is available with SharePoint Foundation, and no need to learn new skills. On the downside, you need to implement your own global filter web parts assuming that you don't use SharePoint Server.
PerformancePoint – This is tool specifically designed for dashboards and it just got better in SharePoint 2010. However, it requires SharePoint Server 2010 which you need for PowerPivot as well. Unfortunately, this puts you in the $5,000+ upfront investment bucket (Vidas has more to say about SharePoint pricing).

Personally, I was pleasantly surprised when I re-discovered PerformancePoint in SharePoint 2010. Here is a cool little dashboard I put together in a couple of hours after importing the Adventure Works KPIs.

I've been complaining for a while that Microsoft doesn't have a web-based OLAP browser. PerformancePoint reporting capabilities (chart and grid) come pretty close. Below is a grid report bound to the Adventure Works reseller data. It would be really cool if PerformancePoint continues the trend to fill in the gap and adds more Excel-like features, such as filters, slicers, etc.

Yes, we now have the ProClarity remnants in the form of a Silverlight-based decomposition tree (requires Silverlight 3.0 on the client). To get it, I right-clicked a cell on the report and clicked Decomposition Tree. This lets me analyze sales by any dimension.

So, what's the catch except the cost? The ridiculously difficult Kerberos configuration of course if you have a multi-server environment. In our case, just when we thought we conquered the Kerberos beast with SSRS, we've found the PerformancePoint doesn't work. As it turned out, unlike Reporting Services, PerformancePoint requires a constrained delegation and uses the Claims for Token service. So, follow the steps in the Configuring Kerberos Authentication for SSRS 2008 R2 with SharePoint 2010 whitepaper closely.

If you have SharePoint 2010 Server already, PerformancePoint definitely warrants your interest.

Kerberos Woes

Teo Lachev — Mon, 23 Aug 2010 20:26:26 GMT

[Wikipedia] Cerberus, (pronounced /ˈsɜrb(ə)rəs/);[1] Greek form: Κέρβερος, /ˈkerberos/[2] in Greek and Roman mythology, is a multi-headed hound (usually three-headed) which guards the gates of Hades, to prevent those who have crossed the river Styx from ever escaping.

I think Microsoft got the name right although the Windows version of Kerberos has more than three heads for sure. Yet another weekend spent in troubleshooting Kerberos. This one has an interesting setup.

SharePoint Server 2010 on a Web Front End (WFE) box.
SharePoint Server 2010 (core install) + SQL Server and Reporting Services 2008 integrated with SharePoint on a second box.
Analysis Services 2008 R2 on a third box.

The customer wanted this setup to minimize the SQL Server licenses. Microsoft recommends installing Reporting Services on the WFE servers but this requires as many SQL Server licenses as the number of the WFE servers, plus probably two more (for SQL Server to host the SharePoint configuration databases and Analysis Services (if it is on a separate box). By contrast, the above setup requires two SQL Server licenses irrespective of the number of the WFE servers.

We hit issue #1 when we tried to deploy from BIDS and we got a login prompt that just won't go away. This was related to the fact that the SQL Server setup configures Reporting Services for NTLM authentication. Since this scenario required Kerberos delegation, we have to change the following setting in the rsreportserver.config file:

</AuthenticationTypes>

</Authentication>

As you can see, we had to disable NTLM and add Negotiate. This helped us to a point where BIDS can deploy reports to the SharePoint site and we can manage data sources and report properties. However, for some reason, after a few minutes the data source and report definitions somehow would get invalidated and trying to access their properties result in HTTP 401 User unauthorized error. Re-deploying the reports and definitions "fix" the definitions for a few minutes and then 401 error again.

More troubleshooting…and we figured out the issue was related to a misconfigured SPN entry for the SSRS service account on the ServicePoint core (database) server by executing setspn -l <SSRS service account>. The results didn't include http/<the account used for SQL Server>. Once we got the Active Directory administrator fix this, the issue went away.

We found the Configuring Kerberos Authentication for SSRS 2008 R2 with SharePoint 2010 whitepaper very useful. It's a must read before venturing into the Kerberos candy land 

Good luck!

Applied PowerPivot Course Available

Teo Lachev — Mon, 23 Aug 2010 00:59:00 GMT

I am excited to announce that Prologika has added an Applied PowerPivot course to the list of our training offerings in response to the strong interest for self-service BI. The class can be delivered as two-day online class (4 hours each day) or as one full-day onsite class. The cost for the online version is $599. Applied PowerPivot is designed to help students become proficient with PowerPivot and acquire the necessary skills to implement PowerPivot applications, perform data analysis, and share these applications with other users. The full course syllabus is available here. I scheduled the first run for September 21^st. Happy self-service BI!

PowerPivot Implicit Data Conversion

Teo Lachev — Mon, 16 Aug 2010 11:52:00 GMT

A student noticed an interesting behavior while I was teaching my PowerPivot class last week. He noticed that PowerPivot lets you join a text-based column to a numeric column. As it turns out, PowerPivot does an implicit conversion to text when it discovers that you are attempting to join columns of different data types. However, as a best practice, you should convert numeric columns to a numeric data type, such as Whole Number. This will help PowerPivot minimize storage and improve performance.

Professional Microsoft PowerPivot for Excel and SharePoint

Teo Lachev — Sat, 31 Jul 2010 03:28:00 GMT

I had the pleasure to read the book Professional Microsoft PowerPivot for Excel and SharePoint by Sivakumar Harinath, Ron Pihlgren, and Denny Guang-Yeu Lee. All of the authors are with the Microsoft Analysis Services team. Together with David Wickert, Denny Lee runs the http://powerpivottwins.com/ blog, which is "dedicated to all things PowerPivot". Siva has written the 2005 and 2008 editions of the Professional SQL Server Analysis Services with MDX. So, the book is straight from the horse's mouth.

I really liked the book. It's easy to follow and includes insightful tips. The book targets business users who are interested in creating self-service BI solutions. IT professionals and BI practitioners who will manage PowerPivot applications will find the book useful as well. Professional Microsoft PowerPivot for Excel and SharePoint incudes tutorials that walks the user through the necessary steps to build a PowerPivot-based solution. These steps include importing the data, enriching the data model, creating calculations, and reports. The code samples use a sample database for the healthcare industry – I guess that's because healthcare is the only industry with a budget for IT applications nowadays, or at least self-service BI apps. Adventure Works is probably waiting for the economy to bounce back.

The authors are quick (page 17) to explain the role of the Corporate BI now that the self-service BI era has dawned on us – something that I was eager to get the official Microsoft opinion about. Their short answer is "not much will change", and that's precisely what I think. IMO, self-service BI is a compromise between corporate BI (best) and reality. Ideally, I don't want business users to do anything but analyze. Why should business users import, relate data, and create calculations that as complexity increases look more bizarre then the equivalent MDX expressions? But we don't live in a perfect world and PowerPivot comes to fill in the void. Here are some scenarios where PowerPivot might be a good fit:

1. You don't have a data mart and Excel spreadsheets rule the reporting world. With some help from IT, business users might be able to get the data in the right format and report from it right in Excel.

2. You do have a data mart but you don't want/don't have the skills to build a cube. But if you've gone that far, why not travel the remaining 20% and have a cool cube that business users trust and love?

3. You have a data mart/cube but not all data is in the data mart/cube or you need to mash up data from various data sources.

4. You suffer from the NIHS (Not Invented Here Syndrome) and you just want to do things your way despite what IT and BI guys like your humble correspondent tell you.

All of these scenarios require compromises in terms of data quality, security, features, and implementation effort. The problems that we aim to solve with corporate BI are not trivial and there are no shortcuts. I know it and you know it. So, be skeptical when you see an impressive presentation that shows how a self-service BI will take upon the corporate BI, no matter if the tool is PowerPivot, QlickView, Tableau, or something else.

Overall, I'd highly recommend this book to anyone interested in PowerPivot and self-service BI. The book does a great job explaining the capabilities and limitations of the tool. I couldn't stop reading it!

PivotViewer Extension for Reporting Services – a Cool BI Fusion with Confusing Name

Teo Lachev — Wed, 21 Jul 2010 21:07:00 GMT

Although late to the party, I announce the arrival of a very interesting BI technology - PivotViewer Extension for Reporting Services – CTP1. What's interesting is that it leverages several BI technologies to provide great data visualization:

The Microsoft LiveLabs Pivot which was developed by Microsoft Research.
PowerPivot – a Microsoft self-service BI tool.
Analysis Services R2
Reporting Services R2
SharePoint 2010

You should keep a close eye of this combination (or at least the last four) as it will become increasingly important in the Microsoft BI stack. Robert Bruckner has provided details about the new arrival. Christian Petculescu, a Principal Architect on the SSAS team, is the driving force behind it. Other MVPs have spread the news. But Kasper de Jonge made my day! He has written a GREAT blog about it with step by step instructions to create your own PivotViewer solution. His blog also cleared my confusion about the role of Reporting Services in this architecture. So, Reporting Services has not been extended in any way. It's just been used an image generator to produce the Pivot images. A better name for the tool could have been a PivotViewer Add-in for SharePoint.

Interestingly, Microsoft Pivot does it work by counting items in collections. It doesn't support any other aggregation functions. Therefore, PivotViewer is not a replacement for Excel PivotTable. It gives you an additional way to visualize you data.

UPDATE

Amir Netz, the mastermind behind PowerPivot, demonstrated PivotViewer during the second day keynote of the BI conference this year. Forward to 1:21. He also talks about the new features that the next version or PowerPivot (SQL Server 11) will bring in, including KPI support, BIDS development support, etc.

Atlanta BI Group First Meeting Topic Announced

Teo Lachev — Sat, 17 Jul 2010 20:32:02 GMT

I've just updated the Atlanta BI Group home page to announce the topic for our first meeting on August 23th. Given the great interest surrounding Self-service BI, I'll present "Self-service BI with Microsoft PowerPivot".

Hope you can make our first meeting!

OLAP PivotTable Extensions for Excel 2010

Teo Lachev — Sat, 10 Jul 2010 22:28:00 GMT

Kudos to Greg Galloway (SQL Server MVP) for upgrading his fantastic OLAP PivotTable Extensions for Excel 2010 add-in (now available in both x32 and x64). This add-in is one of the first third party tools I install on a new machine. Since the Excel team seems to be ignoring customer feedback for improving the Excel BI features, OLAP PivotTable provides what's lacking, including:

Excellent search capabilities with Field List, Dimension Data and attribute options. By contrast, the Excel 2010 search is limited to searching within a hierarchy level only. This is pretty much useless with parent-child hierarchies. If I know what level the member is located, I don't have to search, right?
Getting the MDX query – Excel refuses to expose the actual MDX.
Calculated members – Another feature that business users keep asking about but Excel doesn't budge. The Calculations tab of the add-in lets you create calculated members. It will be great if this is more user-friendly. Ideally, the user should get the same Calculation Member dialog that's included in Reporting Services.
Filtering of attribute members

Yet Another Report Timeout Solved

Teo Lachev — Sun, 04 Jul 2010 15:14:49 GMT

Every now and then, I run into mysterious report timeouts. This time the client had a farm of two report servers integrated with MOSS with long running reports that would time out after two minutes or so. Normally, you shouldn't have such report monsters but life is not perfect, right? The client did a due diligence and had checked/changed every possible timeout setting, including:

Report Processing Timeout in the Reporting Services Server Defaults settings in SharePoint Central Administration.
The httpRuntime settings on all SharePoint farm servers' web.config files:

<httpRuntime maxRequestLength="51200" executionTimeout="9000" />
The "databaseQueryTimeout" value in the RSReportServer.config file from 120 to 900 (15 minutes).
<Add Key="DatabaseQueryTimeout" Value="900"/>
The connection timeout in IIS for the web site on each MOSS server from 120 to 240 seconds.

Alas, no help. Reports would still time out. After much investigation, we discovered a server timeout setting on the load balancer which was set for 2 minutes. After increased it to 15 minutes, the problem went away. So many timeout settings, so little time…

Want more timeout tips? Check out this blog which I wrote four years ago.

Atlanta.MBI Website

Teo Lachev — Fri, 02 Jul 2010 12:43:00 GMT

The website for the Atlanta Microsoft Business Intelligence SIG is up and running although it's still work in progress.

http://atlantabi.sqlpass.org

1. Please register.

2. Please fill in the two polls on the first page for the first meeting attendance and topic of interest.

3. You can post suggestions for our first meeting on the Discussions page (Meetings forum) as a reply to my first post there.

4. Please use the General forum in the Discussions for any general questions.

5. Please spread the news and redirect Atlanta BI fans to http://atlantabi.sqlpass.org.

Venue for Atlanta.MBI Found

Teo Lachev — Thu, 01 Jul 2010 19:08:00 GMT

The great search for a meeting place for the Atlanta BI SIG is over! I am happy to report that I found the perfect place. Matrix Resources graciously offered to host and sponsor our meetings in a training room at their premises at 115 Perimeter Center Place #250, Atlanta, GA, 30346. I visited their location and another location today and I think their place is great. The room is capable of accommodating 50 people and has a projector. The location is nice too since I was looking for place around this area to accommodate the traffic concerns of as many people as possible. I personally live in Norcross and it would have been nice to take advantage of the Data Profit's meetup offer but it would have been too selfish J

I also booked the meeting days for the rest of the year. I suggest we meet up every last Monday of the month from 6:30 – 8:30 PM as follows:

8.23, 9.27, 10.25, 11.22 and 12.27

Our first meeting will on be August 23th at 6:30 PM.

My focus now is to put together the Atlanta BI website that will be hosted on sqlpass.org. I'll let you know when it's ready so you could register and stay on top of the latest announcements. Stay tuned!

Venue for Atlanta.MBI Wanted

Teo Lachev — Wed, 23 Jun 2010 20:19:00 GMT

To follow up on my recent blog about forming an Atlanta-based Microsoft Business Intelligence Special Interest Group, I am planning to have our first meeting in August. The effort now is to find a venue where we can meet. If you have a confirmed location, please post its address to the discussion thread. I'll compile a list and I update this blog and the discussion thread.

Interactive Sorting and Matrix

Teo Lachev — Sun, 20 Jun 2010 13:31:00 GMT

A customer requested the ability to interactively sort a matrix on rows and columns. When they click the sort indicator in a row, the matrix should sort columns based on that row. For example, if the first rows is clicked and we are in an descending sort mode, the following screenshot shows the desired behavior.

When they click on a column sort, all row groups would sort within the column clicked. Sounds simple? I'll be quick to point out that interactive sort is currently not supported directly inside a matrix with dynamic row groups and column groups. The only supported scenario for interactive sort with matrix is a sort indicator in the matrix corner which sorts based on overall column aggregates when a dynamic row group is present. That case is supported because the interactive sort doesn't reach across from the column to the row axis of the matrix; it just sorts column groups relative to each other based on the overall aggregate of an entire column group instance. Robert Bruckner explains this in the discussion forum and provided a sample report.

So, column sort is out of the question unless you get really innovative and post back to the report by following the approach I presented in my Remembering Report Parameters blog and change the actual query once you know the row and column coordinates. As far as sorting on rows, I got this working somewhat by making the following changes.

I set the Choose What To Sort option to the name of the column group, Sort By to the data field (measure), and Apply This Sorting to the name of the dataset. I said this works "somewhat" because a subsequent sort on another row doesn't work. The user must refresh the report to go back to unsorted state before they click on another row.

It will be nice if tablix tells you upfront that this isn't supported so folks don't trash time thinking that they didn't get the options right.

Atlanta Microsoft Business Intelligence Special Interest Group (SIG) Forming

Teo Lachev — Mon, 14 Jun 2010 02:03:48 GMT

To Atlanta Business Intelligence fans:

Some of you have approach Geoff Hiten and me about forming an Atlanta-based Microsoft Business Intelligence group. After an intense internal discussion and brainstorming involving SQL Pass, Atlanta.MDF, and the local Microsoft office, we agreed that we should form such a group as a special interest group (SIG) within Atlanta.MDF. I volunteered to lead the group. I started a discussion on the Atlanta.MDF discussion list to solicit feedback and gauge interest about this endeavor. What should we do so you can get the most from this group? Please share your opinion by posting to the thread!

Attend One BI Training Class - Get One Consulting Hour Free!

Teo Lachev — Mon, 07 Jun 2010 02:45:00 GMT

I am back from vacation in Florida and I am all rested despite the intensive sun exposure and the appearance of some tar from the oil spill. I have scheduled the next two runs of my online training classes:

Applied Reporting Services 2008 Online Training Class
Date: June 28 - June 30, 2010
Time: Noon - 4:30 pm EDT; 9 am - 1:30 pm PDT

Applied Analysis Services 2008 Online Training Class
Date: July 7 - July 9, 2010
Time: Noon - 4:30 pm EDT; 9 am - 1:30 pm PDT

Yes, I am also tossing in an hour of consulting with me to spend it any way you want absolutely FREE! This is your chance to pick up my brain about this nasty requirement your boss wants you to implement. So, sign up while the offer lasts. Don't forget that you can request custom dates if you enroll several people from your company.

In Search of Aggregations

Teo Lachev — Sat, 22 May 2010 20:15:32 GMT

The other day I decided to spend some time and educate myself better on the subject of aggregations. Much to my surprise, no matter how hard I tried hitting different aggregations in the Internet Sales measure group of the Adventure Works cube, when I got the Get Data From Aggregation event in the SQL Profiler, it always indicated an Aggregation 1 hit.

Aggregation 1 100,111111111111111111111

I found this strange given that all partitions in the Internet Sales measure group share the same aggregation design which has 57 aggregations in the SQL Server R2 version of Adventure Works. And I couldn't relate the Aggregation 1 aggregation any of the Internet Sales aggregations. BTW, the aggregation number reported by the profiler is a hex number, so Aggregation 16 is actually Aggregation 22 in the Advanced View of the Aggregations tab in Cube Designer. With some help from Chris Webb and Darren Gosbell, we realized that Aggregation 1 is an aggregation from the Exchange Rates measure group. Too bad the profiler doesn't tell you which measure group the aggregation belongs to. But since the Aggregation 1 vector has only two comma-separated sections, you can deduce that the containing measure group references two dimensions only. This is exactly the case of the Exchange Rates measure group because it references the Date and Destination Currency dimensions only.

But this still doesn't answer the question why the queries can't hit any of the other aggregations. As it turned out, the Internet Sales and Reseller Sales measure group includes measures with measure expressions to convert currency amounts to USD Dollars, such as Internet Sales, Reseller Sales, etc. Because the server resolves measure expressions at the leaf members of the dimensions at run time, the server cannot benefit from any aggregations. Basically, the server says "this measure value is a dynamic value and pre-aggregated summaries won't help here". More interestingly, even if the query requests another measure that doesn't have a measure expression from these measure groups, it won't take advantage of any aggregations that belong to these measure groups. That's because when you query for one measure value in a measure group, the server actually returns all the measure values in that measure group. The Storage Engine always fetches all measures, including a measure based on a measure expression – even when it may not have been requested by the current query. So, it doesn't really matter in this case that you requested a different measure – it will always aggregate from leaves.

To make the story short, if a measure group has measures with measure expressions, don't bother adding aggregations to that measure group. So, all these 57 aggregations in Internet Sales and Measure Sales that the Adventure Works demonstrates are pretty much useless. To benefit from an aggregation design in these measure groups, you either have to move measures with expressions to a separate measure group or don't use measure expressions at all. As a material boy, who tries to materialize calculations as much as possible for better performance, I'd happily go for the latter approach.

BTW, while we are on the subject of aggregations, I highly recommend you watch Chris Webb's excellent Designing Effective Aggregation with Analysis Services presentation to learn what the aggregations are and how to design them. Finally, unlike the partition size recommendations that I discussed in this blog, aggregations are likely to help with much smaller datasets. Chris mentions that aggregations may help even with 1-2 million rows in the fact table. The issue with aggregations is often about building the correct aggregations, and in some cases to be careful not to build too many aggregations.

To Partition or Not – This is the Question

Teo Lachev — Fri, 21 May 2010 23:29:00 GMT

As Analysis Services users undoubtedly know, partitioning and aggregations are the two core tenants of a good Analysis Services data design. Or, at least they have been since its first release. The thing though is that a lot of things have changed since then. Disks and memory got faster and cheaper, and Analysis Services have been getting faster and more intelligent with each new release. You should catch up with these trends, of course, and re-think some old myths, the most prevalent being that you must partition and add aggregations to every cube so you can get faster queries.

In theory, partitioning reduces the data slice the storage engine needs to cover and the smaller the data slice, the less time the SE will spent reading data. But there is a great deal of confusion about partitioning through the BI land. Folks don't know when to partition and how to partition. They just know that they have to do it since everybody does it. The feedback from the Analysis Services team contributes somewhat to this state of affairs. For example, the Microsoft SQL Server 2005 Analysis Services Performance Guide advocates a partitioning strategy for 15-20 million rows per partitions or 2GB as a maximum partition size. However, the Microsoft SQL Server 2008 Analysis Services Performance Guide is somewhat silent on the subject of performance recommendations for partitioning. The only thing you will find is:

For nondistinct count measure groups, tests with partition sizes in the range of 200 megabytes (MB) to up to 3 gigabytes (GB) indicate that partition size alone does not have a substantial impact on query speeds.

Notice the huge data size range – 200 MB to 3 GB. Does this make you doubtful about partitioning for getting faster query times? And why did they remove the partition size recommendation? As it turns out, folks with really large cubes (billions of rows) would follow this recommendation blindly and end up with thousands of partitions! This is even a worse problem than having no partitions at all because the server trashes time for managing all these partitions. Unfortunately (or fortunately), I've never had to chance to deal with such massive cubes. Nevertheless, I would like to suggest the following partitioning strategy for the small to mid-size cubes:

Start with no partitions.
Use XPerf (see my Using XPerf to Test Analysis Services blog) to test a few queries that aggregate data only (no MDX functions or script involved, and no aggregations).
Analyze the I/O time, that is, the time spent in reading data from disk. This is the time that partitioning may help you reduce.
Compare this time with the overall query time. As I explained in the blog, the storage engine is responsible for retrieving and aggregating data. You may very well find that the most of the query time is spent in aggregating data at the grain requested by the query and only a small portion is spent reading data from disk.
If the I/O time is substantial, partition your measure groups and compare times again. Otherwise, forget about partitions and see if aggregations can help you reduce the time spent in aggregating data.

If the above sounds too complicated, here are some general recommendations that originate from the Analysis Services team to give you rough size guidelines about partitioning:

For tiny/small cubes (e.g. less that 100 million), don't partition at all. I don't have statistics but I won't be surprised if 80% of the cubes in existence are smaller.
For mid-size cubes (say 100 million records to 10 billion records), partition based on good slices and data load strategies. This is where you could follow the 20 million/2 GB guidelines that lead to 10 to 500 partitions.
For large cubes, consider larger but fewer partitions. Perhaps, go with larger old partitions and smaller new partitions if possible. Choose an intelligent partitioning strategy where you understand the slices that your typical queries are going to apply and try to optimize based on that.

One area where partitioning does help is manageability if this is important for you. For example, partitions can be processed independently, incrementally (e.g. add only new data), have different aggregation designs, combine fact tables (e.g. actual, budget, and scenario tables in one measure group). Partitioning can help you also reduce the overall cube processing time with multiple CPUs and cores. Processing is a highly parallel operation and partitions are processed in parallel. So, you may still want to add a few partitions, e.g. partition by year, to get the cube to process faster. Just be somewhat skeptical about partitioning for improving the query performance.

Fixing Indicator Images

Teo Lachev — Fri, 21 May 2010 22:20:00 GMT

Indicators, a new feature of Reporting Services 2008 R2, let you show images for state values on your reports and avoid using custom images and expressions. Sean Boon's recent blog shows you how to scale the indicator images. One annoying issue business users reported when working with indicators is that the indicator image stretches if the text grows and spills to the next row. For example, on the report below the second row spans two lines and its indicator images are stretched vertically.

Unfortunately, the indicator region doesn't have a property to "fix" the image size. The only workaround for now is to enclose the entire indicator region in a rectangle: a kludgy hack especially for business users.

Upgrading To Report Viewer 2010

Teo Lachev — Sun, 16 May 2010 02:26:00 GMT

Visual Studio 2010 ships with updated ReportViewer controls which Brian Hartman discusses in his blog. One issue I recently run into with the Visual Studio 2008 ReportViewer ASP.NET control with a chart configured for a dynamic height (DynamicHeight property) where the chart image wouldn't size properly. I found that the issue got fixed in the Visual Studio 2010 ReportViewer. However, in my case we weren't ready to move to Visual Studio 2010 yet. This is what I had to do to upgrade the web application to use the Visual Studio 2010 ReportViewer control:

Install the Report Viewer 2010 redistributable from http://www.microsoft.com/downloads/details.aspx?FamilyID=a941c6b2-64dd-4d03-9ca7-4017a0d164fd&displaylang=en. The setup program will install the Visual Studio 2010 ReportViewer assemblies in GAC as version 10.0.0.0 assemblies.
Updated the web page as follows (changes in bold):

<%@ Page language="c#" Codefile="Demo.aspx.cs" AutoEventWireup="false" Inherits="Prologika.Demo" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

</head>

<body>

<div>

<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager> 

<rsweb:ReportViewer ID="reportViewer" runat="server" Height="100%" Width="100%" Font-Names="Verdana" Font-Size="8pt"
ProcessingMode="Remote" InteractiveDeviceInfos="(Collection)" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt">

</rsweb:ReportViewer>

</div>

</form>

</body>

</html>

In web.config, replace all 9.0.0.0 version references to Report Viewer to 10.0.0.0.

Of course, if you have Visual Studio 2010, it will take of care of making the required changes for you. Finally, by upgrading to the 2010 ReportViewer as an added bonus besides fixing the dynamic height issue, you will also get the new AJAX support which eliminates page reposts and improves the end user experience.

Microsoft SQL Server 2008 R2 Feature Pack Available

Teo Lachev — Wed, 12 May 2010 12:09:52 GMT

Microsoft released the Microsoft SQL Server 2008 R2 Feature Pack. Among other things, it includes Report Builder 3.0, Reporting Services SharePoint Add-in, and PowerPivot for Excel.

Atlanta.MDF Presentation

Teo Lachev — Tue, 11 May 2010 12:28:41 GMT

My presentation "What's New in Reporting Services 2008 R2" for the local Atlanta.MDF group went well last night. We had some 80 people attending and I had some great questions. Too bad I couldn't beat my previous attendance record. I guess rain and traffic were deterrent factors. Thanks for everyone who attended! I posted the presentation materials on my website. It should be available shortly on the Atlanta.MDF site as well.

Online Analysis Services 2008 Class on May 17th

Teo Lachev — Fri, 07 May 2010 16:22:30 GMT

There is still time to register for the online Applied Analysis Services 2008 class run on May 17th. No travel, no hotel expenses, just 100% content delivered right to your desktop! This intensive 3-day online class (14 training hours) teaches you the knowledge and skills to master Analysis Services to its fullest. Use the opportunity to ask questions and learn best practices.

For more information or to register click here!

Synchronizing Reporting Services Groups

Teo Lachev — Mon, 03 May 2010 23:41:00 GMT

One new R2 feature that I discovered while reading the Reporting Services Recipes book, is group data synchronization. Consider the following report (download the two sample reports here):

In this report, the matrix region is nested inside a list region. The list region pages (groups) on product while the nested matrx region displays the sales for the current product grouped by region and year. As you can see, Mountain-200 Sliver, 42 has data for years 2003 and 2004 but if you move to the next page, you will see that Montain-200 Silver, 46 has sales for 2002, 2003, and 2004.

What if you want to synchronize the instances of the matrix region to return the same number of columns, which in this case would it all available years in the dataset? In R2, this takes a few clicks.

In design mode, click anywhere inside the nested matrix region so the Groups pane shows its groups.
Select the CalendarYear column group on which the matrix region pivots.
Expand the Group section in the Properties window and enter the name of the outer region in the DomainScope property. In this case, the outer region is the list region whose name is List.

Now, when you run the report, all instances of the matrix region are synchronized and have the same number of columns irrespective of the fact that they might not have data for given years.

DomainScope is similar to expression scope but not the same. DomainScope can be set on a group leaf member only. So, if the matrix region was grouping on years and quarters, you can synchronize quarters only in which case each year would show four quarters. You cannot synchronize the Year level because it's a parent group. In addition, the domain scope can be an outer group or region. Unlike expression scopes, you cannot synchronize on a dataset.

Group synchronization comes also handy when you work with sparklines. For example, the following report includes an Over Time column that shows a bar sparkline for product category sales over years.

As you can see, the Caps category has sales for four years (The AventureWorksDW2008 database has data for years 2001-2004), while Bib-Shorts category has data for two years. However, the sparklines groups are not aligned. For instance, you can't tell which two years Bib-Shorts have sales for. To fix this:

Double-click the sparkline region to show the Chart Data window.
Click the CalendarYear group and set the DomainScope property to the name of the containing tablix region, Tablix1 in this case.

Now, when you run the report, you'll see that the sparkline group ordinal position reflects the year. Thus, knowing the range of available years (2001-2004) which for clarity you can display on the report, e.g. as a subtitle, Bib-Shorts have data for years 2002 and 2003, while Bike Racks have data for 2003 and 2004.

Book Review – Microsoft SQL Server Reporting Services Recipes

Teo Lachev — Sat, 01 May 2010 21:33:00 GMT

This is a review of the latest addition to the long repertoire of Reporting Services books – Microsoft SQL Server Reporting Services Recipes (ISBN: 978-0-470-56311-3). When you learn a new technology you suggest you pick up several books covering this product because every author writes from his own experience. Thus, even books covering the same technology are not competing by completing each other. Some books target novice users, others are more advanced; some have a strong practical connotation while others are designed to be used more as a reference.

In my opinion, this book will benefit mainly readers who have worked with Reporting Services and have already some report authoring experience under their belt. If you fit this profile, you probably find yourself occasionally wanting a report sample that addresses a specific requirement, a tip for better implementation, or a trick to get around a given limitation. Continuing this line of thought, another title for this book might have been "Reporting Services Tips and Tricks".

Two author names appear on the book cover – Paul Turley and Robert Bruckner – and both names should be familiar to you. Both authors have been heavily involved with Reporting Services since its inception. Paul is a SQL Server MVP and BI architect for Hitachi Consulting. Robert is a technical lead with the Reporting Services team and when he speaks I take a note. Both authors have helped the community tremendously by answering questions on the Reporting Services discussion list, writing and blogging about Reporting Services, and speaking at industry events. Some of the book material has been contributed by other Hitachi employees and SQL Server experts. The foreword is written by Thierry D'hers – Group Program Manager with the Reporting Services team.

The good thing about this book is that it's not tied to a particular release of Reporting Services. What I particularly liked is that in some cases the authors have provided examples that work with previous editions of Reporting Services, coupled with versions that benefit from the latest features found in 2008 or R2. The book is organized in the following parts:

Introduction (80 pages) – Provides the necessary foundation for report authoring.
Part 1: Columnar and Grouped Reports (50 pages) – This part covers features that every report author should know, including alternative row colors, dynamic groups, conditional column visibility, and resetting page numbers.
Part 2 : BI Dashboards and Elements (30 pages) – This is where you would learn to work with indicators, sparklines, charts, and other elements to author dashboard pages that are becoming increasingly popular. You will also learn interesting tips to integrate your reports with Analysis Services.
Part 3: Chart and Gauge Reports (60 pages) – Think of this part as how to get the most out of the Reporting Services data visualization features. It walks you through the cornucopia of chart types that SSRS 2008 made possible, such as histogram, Peretto, bullet graphs, and gauges. It teaches you how to enhance these charts with custom color palettes and exception highlighting.
Part 4: Interactive Reporting (40 pages) – Reporting Services has supported interactive features since the beginning, such as drillthrough, toggled visibility, links, bookmarks, etc. This part takes the interactive features to the next level by showing you how to implement conditional linking, breadcrumbs, dynamic pivoting and document maps.
Part 5: Integrated Reporting Applications (35 pages) – This part shows you how to build report solutions that go beyond a single report, such as conditional subreports, changing the data behind the report, and embedding reports in .NET applications.
Part 6: Enhanced Report Content (100 pages) – This part covers advanced report authoring techniques, including mailing labels, barcodes, custom aggregation, dynamic page breaks, external images, checkbox list and mapping (a new feature of R2).
Part 7: Filtering and Parameterization (70 pages) – Report parameters is one area that takes a lot of criticism from the community and it's one of the weakest links of Reporting Services. The authors present several workarounds for common requirements and limitations, such as advanced filtering, handing multi-valued parameters, top ranking, and custom sorting.
Part 8: Custom and Dynamic Data Sources (30 pages) – This section shows advanced techniques for data retrieval, such as obtaining data from a web service, SharePoint lists (2008 and R2), and Dynamics AX.
Part 9: Games (15 pages) – Now that you've learned all these cool tricks, it's time to chill out. If Xbox is not cool anymore, Reporting Services is here for you to play and delight your users with games such as hangman, and sea battle. Hallo is coming soon… just kidding.

In a nutshell, pick up this book. You'll find yourself reaching for it on a regular basis.

Scope Assignments and Missing Members

Teo Lachev — Thu, 29 Apr 2010 01:59:01 GMT

The Analysis Services server parses the cube script on deploy and complains in case of missing dimension members. You can use the IsError() function to handle missing members, such as Iif(IsError([Account].[Accrual Basis].&[30/360]), null, ([Account].[Accrual Basis].&[30/360]). However, sometimes you might want to ignore the default missing member behavior. For example, an ISV deploying a cube to different customers might prefer to ignore missing members if there are many member references in the cube. In this case, you can see the ScriptErrorHandlingMode cube-level property to IgnoreAll. The unfortunate side effect of doing so is that any scope assignment that references a missing member will be invalidated. For example, suppose you have the following scope assignment:

Scope

(

[Account].[Account].[Account].Members,

{[Account].[Status].&[Open], [Account].[Status].&[Pending]},

);

this = … ;

End Scope;

This assignment changes the cube space for all accounts with Open and Pending status. But what happens if the [Account].[Status].&[Pending] member doesn't exist? The scope assignment simply doesn't work. Since in this case, a set is used to specify the account status, you cannot use IsError(). One workaround is to use the Filter() function as follows:

Scope

(

Filter([Account].[Account].[Account].Members, [Account].[Account].Properties("Status") = "Open" OR [Account].[Account].Properties("Status") = "Pending")

);

this = … ;

End Scope;