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.

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.

If you’ve played with PowerPivot at all, it’s pretty obvious how flexible the tool is in terms of creating data models. After you initially create a model, you may want to spend some additional time with it to make sure users can easily leverage what you’ve created in other tools. If you don’t, the PowerPivot model you publish probably won’t see lots of re-use outside of the PowerPivot gallery and Excel because users won’t be able to get at the values they want to.

What am I talking about? Here’s an example:

I pointed PowerPivot at the Adventure Works DW sample database, and brought all its tables and rows into my workbook. I then published the workbook directly into a SharePoint 2010 PowerPivot Gallery. I didn’t bother to add any UI to the sheets in the workbook (no PivotTables, PivotCharts, Slicers).

Next, I launched Report Builder 3.0, created a new Analysis Services data source and typed in the location of the *.xlsx I published. I chose the single “sandbox” in the workbook as the database name. Here’s a screenshot of my new RB data source being created (embedded image truncated, click below to view complete screenshot) :

Then, add a DataSet to this Data Source and launch the Query Designer. Notice anything wrong with this picture?

That’s right — our dimensions are showing up as measure groups AND dimensions! And if you scroll down the dimension list, you’ll see that the the fact tables not only show up in the measure group list, but as dimensions, too!

Finally, crack open one of the measure groups – It contains NONE of the measures that are defined in the PowerPivot model – only  “_Count FactSalesQuota” in the screenshot below.

What the hell is going on? What you’re seeing is a function of how PowerPivot “thinks”. To PowerPivot, everything is a dimension unless proven otherwise, and therefore all of the facts in your measure groups are considered dimension attributes until PowerPivot understands that the attribute in question should really be considered a measure.

So let’s fix all this stuff up so we have a usable model, shall we?

To begin with, we’re going to need to help PowerPivot figure out which of our fields represent measures. In PowerPivot there are implicit and explicit measures. Any field you drag into the Values list of a PivotTable/Chart is regarded as an implicit measure by PowerPivot.

I’ve opened up that empty AWDW workbook again, and added a PivotTable.  Next, I’ll add the SalesAmountQuota measure from the FactSalesQuota table to the Values area of the Field List (embedded image truncated, click below to view complete screenshot).

After publishing the updated workbook to MOSS, let’s look at Report Builder again.

There you go – an implicit measure. By adding a field to the Values list of the Gemini Task pane, we’ve tipped PowerPivot off to the fact that SalesAmountQuota is indeed a fact.

However, if you look at the dimension list, you’re still going to see FactSalesQuota as a dimension, and SalesAmountQuota (along with 4-5 other fields) as a possible dimension attribute. Well, that ain’t right.

How to fix? Make that measure an explict measure. Here’s what to do.

Remove the SalesAmountQuota from the Values List in your PivotTable.

Switch back into the PowerPivot window, find the FactSalesQuota table, and select all the columns. Finally, right-click the selected columns and choose to Hide Columns | From Gemini and PivotTable

Back in the worksheet, click Refresh All on the Data ribbon, and your field list should look something like this (note how our measure group name is “dimmed”  – embedded image truncated, click below to view complete screenshot):

Right-click FactSalesQuota, and select Add New Measure. Create a formula which points to the SalesAmountQuota field you hid just a second ago (embedded image truncated, click below to view complete screenshot):

When you’re done, you should have something that looks like this (embedded image truncated, click below to view complete screenshot):

That’s just about it. Publish your workbook again, and fire up Report Builder. Note that the explicit measure you added now shows as part of the measure group FactSalesQuota and that FactSalesQuota doesn’t appear at all under dimensions.

And some notes:

In order to get the data model to refresh in Report Builder as I made changes in Excel and re-published, I found I had to sometimes delete the published workbook and/or IISReset. Using the Reconnect/Refresh button in Report Builder’s SSAS query designer didn’t refresh my schema for some reason.

Many thanks to Oliver Matrat, a senior PM on the Analysis Services team for helping me out when I was confused about how to make this stuff work – most of what I’ve related here comes directly from him.

File this under “duh”, but if your goal is to show PowerPivot charts to their best effect in the SharePoint PowerPivot Gallery, then….

Hide those chart data sheets, ladies!

Simple, but makes a world of difference in terms of how cool your reports look in the gallery and report carousel. Hiding the sheets which contain chart data get rid of tons of visual clutter and make your graphical reports more impactful.

Want to play with PowerPivot (Project Gemini) without going through the hassle of installing Office, SharePoint, etc? Try the newly released virtual lab!

https://cmg.vlabcenter.com/default.aspx?moduleid=ad3bd3e9-8d2b-498d-94fa-e41e1b09730d

Update: Slight bummage: This virtual lab allows you to play with client-side Gemini functionality only – no publish to MOSS and seeing what your results look like….Still a very good lab, though.

I was in an inflammatory mood at the beginning of the week and tweeted the following at #sqlpass:

DBAs don’t like it, but Excel IS the database for many. This hasn’t and won’t change. Manage to this reality.

The lion’s share of people (DBAs, I guess) who responded…Well, let’s say they didn’t agree.  I don’t know, maybe I’m nuts or something but the users I work with generally take the path of least resistance.  Of course Excel isn’t a real database, but do users care? No. They just want to get their work done. If the data they want is available right now from an IT-provisioned data source, they’ll use it. If it’s not, the average user will end-run you so fast your head will spin. 

Are spread marts a good thing? No, they suck.  Are they a fact of life? Who knows…but I see just as many “mission critical” excel workbooks now as I did 5 or 10 years ago. That says something.

So why not embrace Excel as a potential solution for certain scenarios? Introduce them to Project Gemini (PowerPivot!)  and now you at least can get a view into what they’re up to – you can actually begin to manage some of their output.

What do you think? Honesly, I’m curious…I could be out of my mind, after all.