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.

Over on the Forrester Blog, Boris Evelson has written an interesting post about the real cost of creating a single BI report in the enterprise. He estimates a price tag of between $1,840 and $20K per report after one factors in expenditures on software, services, salaries, hardware etc.

He suggests four general approaches to lowering BI costs, and PowerPivot neatly enables at least two of the four strategies.

Is PowerPivot a BI panacea? Nah, of course not. But intelligently leveraged, I believe the tool will drive down costs associated with creating BI reports.

Over the week-end I was doing some analysis on SQL Server disk usage, and wanted to be able to display current disk usage by database. Up to this point, I’d mainly been doing a SUM over  my measures. Well, that would make no sense in this scenario – I’d end up with a meaningless number just like I would if I took a SUM of an Inventory value.

I found myself dealing with a classic semi-additive measure. These are easy to deal with using SQL Server Analysis Services, but a tiny bit trickier in PowerPivot.

I tried various DAX-related ideas, and some actually kind of worked, but Marius Dumitru, a co-worker at Microsoft, suggested the best solution.

I happen to have a date dimension which we could lean on for this challenge – I just needed to find the last “Disk Use” value that had been recorded. Since all of my readings are associated with a date, slam dunk!

Here’s what I did:

Created a calculated measure which SUMs the measure in my table (I named it “Sum Disk Size”):

     = SUM(‘Fact_DatabaseMetrics’[Disk Size])

Created a second calculated measure which filters the first, looking for the value related to the last associated date in my date dimension (via DAX’s LastNonBlank function) . I didn’t know you could filter a measure so easily!

        = ‘Fact_DatabaseMetrics’[Sum Disk Size](LastNonBlank(‘Dim_Date’[DatePK], ‘Fact_Database’[Sum Disk Size])

The second measure is what I added to my PivotTable.