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.
