While watching the keynote from the recent BI Conference, I saw a demo of the Pivotviewer Extensions for Reporting Services. This is an interesting tool that will help automate creating Pivot collections. Unfortunately, even as an Microsoft FTE I can’t get my hands on a preview copy until after the end of the month…and I have a project due before then.

So, what the hell, let’s re-create the wheel! You can download some samples here. The samples aren’t a complete solution, but demonstrate the uglier/trickier bits of the process.  The code is ugly and not commented…you get what you pay for.

Goal:

Create a collection which surfaces online advertising metrics (impressions, clicks, click through rates, etc.) for 10 ad agency clients. Each client has multiple campaigns and placements with a number of different packages, ads, and creative items which are served by multiple vendor websites.  

Create a Pivot “card” for each combination of Client-Campaign-Placement-Ad-Site . Each “card” should also display interesting metrics on a day-by-day basis.

All data is to be sourced from an SSAS cube.

Approach:

Short and sweet: Create an SSRS report and code to repeatedly export a JPEG in order to generate the images used in the Pivot collection. Once complete, use the Pivot command-line tools to create the cxml document. Simple, right? Here’s how I did it:

Pull information from SSAS

First, I “wrote some MDX” to pull dimension attributes (like client, campaign, etc.) and metrics (impressions, clicks, click through rate)  from the cube.  I had to add additional fields to this query to return the actual UNIQUENAME of the dimension members I was returning. Why? You’ll see in the next section…

WITH          
MEMBER [Measures].[ClientNameUNIQUE] AS [Clients].[Client Name].CURRENTMEMBER.UNIQUENAME          
MEMBER [Measures].[CampaignNameUNIQUE] AS [Campaigns].[Campaign Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[AdNameUNIQUE] AS [Ads].[Ad Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[SiteNameUNIQUE] AS [Ads].[DFA Site Name].CURRENTMEMBER.UNIQUENAME          
        
SELECT {[Measures].[ClientNameUNIQUE], [Measures].[CampaignNameUNIQUE] , [Measures].[AdNameUNIQUE], [Measures].[SiteNameUNIQUE],
 [Measures].[Impressions],[Measures].[Clicks],[Measures].[CTR],[Measures].[Calculated Cost],[Measures].[Direct Sales], [Measures].[Indirect Sales]} ON COLUMNS,
NONEMPTY(         
{ { { [Date].[Year].[All].CHILDREN } *
{ [Clients].[Client Name].[All].CHILDREN}  *
{ [Campaigns].[Campaign Name].[Campaign Name].ALLMEMBERS} *
{ [Placements].[Target Market].[Target Market].ALLMEMBERS} *
{ [Placements].[Package Name].[Package Name].ALLMEMBERS} *
{ [Ads].[Ad Name].[Ad Name].ALLMEMBERS} *
{ [Ads].[DFA Site Name].[DFA Site Name].ALLMEMBERS} *
{ [Creative].[Technology Type Name].[Technology Type Name].ALLMEMBERS}

 }}

, [Measures].[Impressions]) ON ROWS           
FROM [WWReach]

I wrote a simple SSIS package (PopulatePivotSupport.dtsx) to take the resultset from this query and drop it into SQL for staging purposes.

Create SSRS report

Next, I created a parameterized SSRS report which contains a fictional client logo, as well as multiple SSRS charts which are used to plot daily online metrics for the client/campaign in question.

The report is parameterized so that I can feed in arbitrary values used to filter data in the charts.  My report uses 5  parameters to filter by  client, campaign, ad, (vendor) site, ad serving technology, etc.

Here’s an example :

A sample RDL can be found in the files I mentioned at the top of the article.

Challenge #1

Things normally don’t go 100% smoothly for me when I need to do heavy parameterization on a report which uses SSAS as a data source. I normally have issues with StrToSet-related errors, trying to plug in values which SSRS doesn’t see as valid (due to CONSTRAINED flags in my MDX), etc.  To try and avoid as many of these problems as possible, I did the following for each parameter in my report:

• Deselected Allow multiple values
• Under Available Values, I chose None instead of Query
• Double-checked that I only had one default value per parameter (see bullet #1)
• Removed the CONSTRAINED sections of MDX from my queries

Challenge #2

I need to provide parameter values to SSRS/SSAS in the [dimension].[attribute].&[attributekey] format.  That’s why I created several MEMBERs in my MDX. I used those to return not only a dimension attribute value like “Us Rail” (a client’s name), but the client’s Unique Name: [Clients].[Client Name].&1001. This was the critical step. Life was good once I had all the “friendly” values (like Client Name = “Us Rail”) as well as the “not so friendly ones” that I needed to feed back to SSAS for filtering purposes ([Clients].[Client Name].&1001) in a staging table.

Write code to render (repeatedly) reports to JPEG files

I decided to create another SSIS package named  PrintSSRS.dtsx to do this work. Essentially, the package does the following:

• Grabs rows from the staging table. Each row runs my SSRS report once.  I used an Execute SQL task for this and stuffed the results into an Object Variable
• Uses a For..Each task to iterate through the rows in the Object variable, grabbing values from each row like the Client ID, Campaign Key, Site ID, etc.
• Executes a Script task inside the For…Each loop. The script task populates the SSRS report’s parameter collection and calls .Render() against SSRS to create the JPEG report which is saved to disk.

..I ended up with about 5,500 JPEG “reports” on my hard drive…each one was about 50K. It took about 2 hours in total for this code to generate all the reports I needed.

Generate the Pivot Collection

Since I hadn’t used the (new) Pivot Command-line tools yet , I thought I would give those a try instead of the Excel-Add in. You can download them from http://www.getpivot.com

I still used Excel heavily though:

• First I imported that staging table from SQL into a worksheet.
• I deleted the Excel columns I didn’t care about (the “MEMBER” columns)
• I used the Pivot documentation to create 3 worksheets in Excel which defined my collection
• I ran the Command-line tool to create a deepzoom output and went to dinner.

I’ve dropped the workbook I created into the samples, too.

Other Stuff

I learned the hard way that I should have spent a bit more time with the Pivot developer documents. My first collection actually consisted of over 72K distinct Pivot tiles because I decided to also break down my clients/campaigns/ads/sites/technologies by week. Bad idea – I actually couldn’t get the command-line tools to finish the job of creating the CXML file…they would run out of memory on my 16 GB box before they were through

I next tried breaking things down by month instead of week…I was able to get a working collection of about 15K tiles at that point, but it looked terrible in viewer. So, I threw our the idea of filtering by time altogether, and here is the result (FYI, “City of New York” is purely fictional – I’m a native, so just used them as a make-believe ad agency client):

As I said, I should have RTFM, because it said my collections should try and stick to around 5,000 items.

So there you go. If you’re impatient and don’t want to wait for the “official” PivotViewer Extensions, just roll one yourself. Microsoft’s version is going to be much better than this hack, btw: faster, able to handle more rows, etc.

Proof positive you need to keep writing code or your skills atrophy. This morning I needed to write some simple code to render an SSRS report as an image using the SSRS Web Services….

In VS, I added a Service Reference to ReportExecution2005.asmx and my proxy was dutifully generated. However, I couldn’t find ReportExecutionService – the only thing I saw that looked “about right” was ReportExecutionServiceSoap and ReportExecutionServiceSoapClient.

I flailed around for a bit and then realized I hadn’t actually added a Web Reference, I had added a Service Reference: Two different things. I killed my Service References, opened the Add Service Reference dialog, and clicked the Advanced button – The “Add a Web Reference isntead of a Service Reference” button was all I needed.

Today I installed the BI Indexing Connector for the first time (neat stuff!) and pretty quickly saw a change in the way the PowerPivot gallery renders Reporting Services reports.

When installing the backend component of the connector, you are instructed to append NoGetRedirect=”True” to a partcular element in the ServerFiles_ReportServer.xml file (http://technet.microsoft.com/en-us/library/ff678217.aspx). Doing so allows the connector to crawl Reporting Services reports.

Unfortunately, this change also modifies how the PowerPivot Gallery Silverlight application renders SSRS reports. The default behavior is “click on an SSRS report and I’ll render it for you in the browser”.  However, once you make the change above, when you click on an SSRS report in the PowerPivot Theater, Carousel, or Gallery…whoops!

Rather than rendering the report as one would expect, the actual RDL file for the report  is returned to the browser and you get the standard “Save as File” dialog!

I’ll update this post when I find out more about the behavior, but if you bump into this issue, you’ve done nothing wrong

Update 1:

Looks like this is only occurs if you install everything on one machine. In the real world, I suspect that very few people running FAST will do what I did in terms of building out a machine which acts as a front-end and back-end. However, if you do, be prepared.

Yes, it is totally possible to publish a SSRS report into your PowerPivot Gallery – In fact, the gallery invites you to do so with the “New Report Builder Report” item in the PowerPivot gallery’s silverlight application.

But as my buddy George Orwell might say, “All reports are equal, but some reports are more equal than others.”

Essentially, you’re not going to get a snapshot generated for your SSRS reports stored here.

The status message displayed can be somewhat confusing, too: “Snapshots were disabled to protect sensitive content” is dead on the money, but infers there might be a way to enable this sensitive content. Don’t waste your time looking for the magic property to enable dynamic SSRS snapshots, however. No such animal.

The “Snapshots were disabled” message is used in a number of situations where connections in a xlsx published to the gallery aren’t quite right, and I guess we chose to lump the SSRS scenario in with them.

Last week I was in a bit of a panic when Excel Services and SQL Reporting Services (MOSS Integrated) broke on nearly all of my “demo” Hyper-V images.

I could publish to Excel Services, but attempting to render caused this exception to get thrown:

ProcessWebException: A Web exception during ExecuteWebMethod has occurred for server: http://atlasone:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx, method: OpenWorkbook, ex: System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. —> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. —> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)

All of these machines had worked flawlessly before, so I was flummoxed.

In addition, when I tried to render any SSRS MOSS Integrated reports…no joy:

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host

Interestingly enough, if I manually browsed to http://server/reportserver, I could run reports with no problems…just no luck via MOSS

I also noticed that all of the RSS feeds on my MOSS site had stopped working.

After doing a bit of back-tracking, I saw that all of these boxes had one thing in common: Windows Update had attempted to install security hotfix KB967723, and for reasons unknown, the process had failed. If there was no initial attempt to install KB967723, I had no problems…if WU tried to install it, my image was hosed.

Happy endings: By manually downloading and installing the hotfix and then bouncing the machine, all the broken stuff started functioning correctly. (The hotfix just happens to be network-related in nature, go figure)

Posting this in the hopes I can give someone back an afternoon of their lives!

If you’ve been waiting for the Click Once version of Report Builder 2.0, your wait is over (as long as you don’t mind using non-RTM bits).

The SP1 CTP will allow you to launch RB 1.0 or 2.0 as Click Once applications in both Native and MOSS integrated mode.

Have at it!

http://www.microsoft.com/downloads/details.aspx?FamilyID=6f26fc45-f0ca-49cf-a6ee-840c7e8bb8af&displaylang=en

Oh, and how to do you turn it on? In native mode, go to Site Settings,  then set Custom Report Builder launch URL to /ReportBuilder/ReportBuilder_2_0_0_0.application. MOSS integrated mode has a similar property which can be found in the Set Server Defaults link of the Reporting Services section of the Application Management inside the SPS Central Administration console.

SQL Server 2008 Cumulative Update 1 was released a few weeks ago, and contains a very nice (but not promoted) improvement around PDF rendering.

For those who regularly render reports which require specific fonts and character sets (for example, trying to render a “simple Chinese” language report to PDF), you need to have the font in question installed on client where you read the exported PDF or you see garbage. Our PDF rendering extension didn’t include “font embedding” in 2000/2005/2008 RTM, which something that the Acrobat format does support.

Well, in CU1, we added font embedding to the PDF extension – multilingual reports just got a little bit easier!

Late last week I stumbled into a really informative conversation around how 2005 reports are “automagically” upgraded to 2008. Thought I’d post the broad strokes here in FAQ format for everyone’s use. Thanks to Robert Bruckner who contributed most of the information! 

Q: If I upgrade my instance of SSRS 2005 to 2008, what happens to the reports in reportserver database? Do they get automatically upgraded? 

A: Reports in the catalog are automatically upgraded from 2005 to 2008 when they are first run on the newly upgraded machine. Each report is upgraded only once, not each time it is run.  

Q: If my report gets automatically upgraded to the 2008 schema, can I get my original 2005 report back somehow?  

A: Yes, you can. The upgrade process does not actually delete the original 2005 report, but simply makes a copy of it and stores the compiled result. If you “Edit” the report using Report Manager (to download a copy of the RDL) or call GetReportDefinition(), the original 2005 report definition will be returned. 

Q: What if the report doesn’t get upgraded for some reason – will it still run on 2008? 

A: SQL Server Reporting Services 2008 has the ability to render reports using the new “on demand” engine, and the older 2005 engine. 

Q: So, if my 2005 report gets automatically upgraded to the 2008 RDL schema, is there any way I can get the upgraded version (2008) out of the server for use elsewhere? 

A: No. You’ll need to use BIDS or Report Builder (v2) to upgrade your 2005 report to the 2008 schema. 

Q: I know that every once in a while, a 2005 report won’t auto-upgrade to 2008 successfully. How can I tell if a report I’m running is being rendered in 2005 or 2008 mode? 

A: We attempt to upgrade a 2005 report to 2008 once and only once. If the process fails the first time, we don’t try again. To see which engine is being used to render a report, use the new ExecutionLog2 view in the reportserver database, examine the AdditionalInfo column and check the <ProcessingEngine> element. A value of 2 indicates the new 2008 “on demand” rendering engine was used, while a value of 1 means the older, 2005 engine was used.

With the RTM of SQL 2008, it looks like a slightly older version of the add-in has been removed from microsoft.com. Unfortunately, all of the search engines (including Live) are pointing at the old, dead page.

If you search for the add-in directly from Microsoft.com, you’ll find it here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=200fd7b5-db7c-4b8c-a7dc-5efee6e19005&DisplayLang=en

The SQLCAT team just released a very interesting technical note which compares the relative “scalability goodness” of Reporting Services 2005 to 2008. You can read the article just as well as I can, but here’s the executive summary, and the results are pretty impressive (bold is mine, btw)

Executive Summary

Reporting Services 2008 was able to respond to 3–4 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with Reporting Services 2005, regardless of the type of renderer. In stark contrast, Reporting Services 2005 generated excessive HTTP 503 Service Is Unavailable errors as the number of users and their requests increased, regardless of the report renderer.

Our tests clearly show that the new memory management architecture of the report server enables Reporting Services 2008 to scale very well, particularly on the new four-processor, quad-core processors. With our test workload, Reporting Services 2008 consistently outperformed SQL Server 2005 with the PDF and XLS renderers on the four-processor, quad-core hardware platform (16 cores) both in terms of response time and in terms of total throughput. Furthermore, with these renderers on this hardware platform, Reporting Services dramatically outperformed other hardware platforms regardless of Reporting Services version, responding to 3–5 times the number of requests than when running on either of the other hardware platforms. As a result, we recommend that you scale up to four-processor, quad-core servers for performance and scale out to a two-node deployment for high availability. Thereafter, as demand for more capacity occurs, add more four-processor, quad-core servers.

Finally, with all renderers and with all hardware platforms using our test workload, the performance bottlenecks were the processor on the front-end server and the disk subsystem on the data source with Reporting Services 2008, whereas the Reporting Services front-end Web service was the performance bottleneck with Reporting Services 2005.

It’s a whole new ballgame, folks!