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.

Looks like May!

http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

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 had a bear of a time getting SQL Server 2008 R2 November CTP’s Integrated PowerPivot feature installed.  It looks like several other people in the Twitter/Blogosphere are running into the same issue, but for potentially different reasons.

For me, ProClarity Analytics Server (PAS) 6.3 was the culprit: If it was already installed on my machine, I could NOT get Integrated PowerPivot installed. As a result, I was forced to install Gemini first, then PAS. In order for everything to play nicely, I then needed to manually move the PAS install from port 80 into a new website which listened on a different port. How did I do it? Here’s how:

WARNING: Before you install PAS, I suggest you read the couple of sentences & 3 bullets towards the end of this entry – you are about to inadvertently (but temporarily)  BREAK MOSS by changing all of MOSS’s x64 app pools to x86/x64. I suggest you record a list of all your applications pools as they exist right now and note whether they Enable 32-bit Applications or not so you can make sure they have the correct values when you’re all done.

• I set the identity of IIS’s DefaultAppPool to my domain admin account to make life simple. You might not want to.
• Install PAS as you normally would, including all the extra goodies that come with PAS like ProClarity Web Pro, etc.
• Install PAS 6.3 Service Pack 3 http://www.microsoft.com/downloads/details.aspx?FamilyID=E6B784A8-E7C2-4E27-9D98-41E2F2FD7467&displaylang=en
• Install WebDAV. Here’s a link which gets more specific (see section “Installing and configuring WebDAV”): http://office.microsoft.com/download/afile.aspx?AssetID=AM103875331033
• Open IIS Manager via Start  | Run | inetmgr
• Create a new website named ProClarity. Bind it to port 81. Along with it, you’ll be creating a new AppPool named ProClarity, as well.
• Modify the AppPool named “ProClarity“, set it’s Framework Version to v2.0.50727, Pipeline Mode = Integrated. Set the Identity of this pool to your domain admin account or something more secure.
• Copy the PAS and PASUploads applications plus the ChartFXInt62 VDIR currently under Default Web Site to the ProClarity website
  • Under Default Web Site, right-click the PAS application, choose Manage Application, then Advanced Settings. Copy the Physical Path string to your clipboard
  • Right-click the ProClarity web site, choose Add Application, and create a new application with the same name/alias (PAS). Paste in the Physical Path string.
  • Repeat for PASUploads
  • Repeat for the ChartFXInt62 Virtual Directory, but create a VDIR under the ProClarity web site instead of an application
• Browse to C:\Windows\System32\inetsrv\config and back up ApplicationHost.config somewhere safe
• Open ApplicationHost.config and scroll all the way to the bottom of the file until you hit the closing </configuration> element.
• Paste the following stuff right before the closing </confiiguration> tag. I’m assuming you named your website ProClarity, btw:

     <location path=”ProClarity”>
             <system.webServer>
                <isapiFilters>
                     <clear />
                     <filter name=”PHttpFilter” path=”C:\Program Files (x86)\Common Files\ProClarity\Server\PHTTPFilter.dll” enabled=”true” />
                 </isapiFilters>
             </system.webServer>
         </location>
         <location path=”ProClarity/PAS”>
                  <system.webServer>
                 <directoryBrowse enabled=”false” showFlags=”Date, Time, Size, Extension” />
                 <handlers accessPolicy=”Read, Script” />
                 <security>
                     <authentication>
                         <windowsAuthentication enabled=”true” />
                         <anonymousAuthentication enabled=”false” />
                         <digestAuthentication enabled=”false” />
                         <basicAuthentication enabled=”true” />
                     </authentication>
                 </security>
                 <defaultDocument enabled=”true”>
                     <files>
                         <clear />
                         <add value=”ProClarity.asp” />
                     </files>
                 </defaultDocument>
                 <asp enableParentPaths=”false” bufferingOn=”true”>
                     <session allowSessionState=”true” />
                     <limits scriptTimeout=”00:03:00″ />
                 </asp>
             </system.webServer>
         </location>
         <location path=”ProClarity/PASUploads”>
             <system.webServer>
                 <directoryBrowse enabled=”false” showFlags=”Date, Time, Size, Extension” />
                 <handlers accessPolicy=”Read, Write, Script” />
                 <security>
                     <authentication>
                         <windowsAuthentication enabled=”true” />
                         <anonymousAuthentication enabled=”false” />
                         <digestAuthentication enabled=”false” />
                         <basicAuthentication enabled=”true” />
                      </authentication>
                 </security>
                 <defaultDocument enabled=”false”>
                     <files>
                         <clear />
                         <add value=”ProClarity.asp” />
                     </files>
                 </defaultDocument>
                 <asp bufferingOn=”true”>
                     <session allowSessionState=”true” />
                     <limits scriptTimeout=”00:03:00″ />
                 </asp>
             </system.webServer>
         </location>
         <location path=”ProClarity/ChartFXInt62″>
             <system.webServer>
                 <directoryBrowse enabled=”false” showFlags=”Date, Time, Size, Extension” />
                 <handlers accessPolicy=”Read, Script” />
                 <security>
                     <authentication>
                         <windowsAuthentication enabled=”true” />
                         <anonymousAuthentication enabled=”false” />
                         <digestAuthentication enabled=”false” />
                         <basicAuthentication enabled=”true” />
                     </authentication>
                 </security>
                 <defaultDocument enabled=”true” />
                 <asp>
                     <limits scriptTimeout=”00:03:00″ />
                 </asp>
             </system.webServer>
         </location>

        What is this? It’s the basic configration of your Default Web Site with PAS installed (only PAS, nothing else) . I just snipped it out of my config file, and renamed “Default Web Site” to “ProClarity” in the appropriate places).

• Save, and do an IISReset
• Browse to http://machinename:81/PAS, and ProClarity should be happy.
• Assuming all is well, remove the PAS, PASUploads and ChartFXInt62 applications/VDIRS from under Default Web Site.
• Next, select the Default Web Site and click ISAPI Filters in the Features View pane.
• Delete PhttpFilter, which is most likely the only thing there.

If you’re trying to install the Integrated PowerPivot engine for SharePoint, then I know you’re on a 64-bit machine, which leaves you with another problem. Since you just put PAS (32-bit) on your box, IIS has switched all of your Application Pools  around so that they allow 32-bit apps. Your x64 MOSS install won’t like this at all. It is broken. SO, fix it:

• In Internet Information Manager, click Application Pools. You’ll see a bunch of stuff from MOSS including multiple app pools with a GUID for a name, Classic .NET AppPool, DefaultAppPoolk, Sharepoint – 80, SharePoint Central Administration v4, SharePoint Web Services Root.
• Right-click each one, choose Advanced Features, and then set the Enable 32-bit Applications property back to False. (You must leave the ProClarity app pool set to True).
• IISRESET

 That’s it, you should be good. BTW, if you chose not to set the identity of the ProClarity and/or DefaultAppPool to a set of domain admin \ admin credentials, you still might have a bit of work to do to make sure that ProClarity can read everything it needs to. Read this link for more information http://blogs.technet.com/proclarity/attachment/3171346.ashx. You’ll be interested in steps 5 and 6 of section 1.

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.

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!