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!

The title says it all!

x86: http://www.microsoft.com/downloads/details.aspx?FamilyID=90c596a5-aca4-4ded-9072-facf834bc0c6&displaylang=en

x64: http://www.microsoft.com/downloads/details.aspx?FamilyID=3ad75ae5-d2cd-4953-87cf-5f74d79804c6&displaylang=en

docs: http://technet.microsoft.com/en-us/library/cc514367.aspx

On a fairly regular basis, customers I work with wonder out loud (read: gripe) why SSAS 2005 and 2008 don’t allow them to easily drill-down to the relational tables that back a cube using an action. SSAS 2000 did this nicely, but SQL Analysis Services 2005 and 2008 want to return rows from the cube vs. directly from the tables.

There is a sort-of-but-not-too-painful workaround available which encompasses using ROLAP partitions which point to the drill targets in question, but it’s still kind of inconvenience to deal with because you may end up setting up quite a few ROLAP partitions to support everything you need to do.

Brian Knight has come up with a really nice, clean solution which leverages a helper assembly to do the same thing. I like this approach much more because once you’ve installed the assembly, creating drill-through actions is quite easy. Thanks, Brian!

http://blogs.pragmaticworks.com/brian_knight/2009/09/creating-a-ssas-rowset-action.html

http://blogs.pragmaticworks.com/brian_knight/2009/09/calling-an-external-query-from-mdx.html

I’m giggling like a little girl because today (well, last night) CTP2 (also known as the “August CTP”) hits the streets.

I jumped out to MSDN and TechNet and see subscribers will be able to download it today – I’m actually looking at the bits out on TechNet right now.  A general release will be available two very long days from now, on Wednesday the 12th.  You can go for here for more information. The download link is here.

So what’s in CTP2? That’s sort of a good news / bad news proposition, actually.  As you know, R2 is hitting on the self-service BI theme with all cylinders. The main focus of this effort will be Project Gemini, but don’t forget about SQL Reporting Services which has much new coolness, too.

Project Gemini

I’m sure you all know about Project Gemini, and if you don’t, you should read about it here. Gemini is part of the next wave of innovation represented in the R2 / MOSS 2010 and Office 2010 releases. It is generally discussed in relation to the SQL 2008 R2 release. However, the CTP2 release of SQL doesn’t actually include the Gemini add-in for Excel 2010 – and most folks don’t have access to Excel 2010 yet, anyway.

The next community tech preview, CTP3 will contain Gemini bits. And, no, I don’t know when it will be released. I’m also unclear if this will be a public CTP or if you have to be invited.  In any case, if you want to sign up for notifications about CTP3, go here:   https://profile.microsoft.com/RegSysProfileCenter/wizard.aspx?wizid=27d33a54-f9f5-4357-b652-6f03ba8a4d23&lcid=1033.

For those of you lucky enough to be part of the Office 2010 Tech Preview, you can get your hands on a CTP2 synced version of the add-in right now. More information. You’ll see links at the very bottom of  the page that allow you to register and then download.

SQL Reporting Services

In terms of SSRS, you’re going to be able to play with Report Builder 3.0. It includes the new mapping functionality, which is still a work in progress in CTP2. It’s quite cool, however.

For all you recovering Microsoft Access junkies: Remember the love-hate relationship you had with DLoopkup? The function everyone maligns and then uses anyway? Well, SSRS has got it! Ours will be called Lookup(), and I won’t spoil the fun…check out Lookupset() and MultiLookup(), too.

Some really compelling functionality isn’t in the CTP yet, and I’m not sure whether the SSRS team has talked about it yet, so I’m not going to name/whine about its absence and inadvertently let the cat out of the bag.  Additional good stuff is coming, though!

Books Online

Don’t forget to RTFM! Download it here.

ProClarity users tend to miss the ability to create calculated measures when they first start experimenting with Excel-as-ProClarity-replacement. Fear no more!

Tyler Chessman, another Microsoft Technical Specialist has created an add-in to allow you to do just that in 2007.  It’s called PTPower, and you can check it out here!

I’m not a big fan of this guy, but a recent guest post was a great read. The author, Eric Ries essentially argues that if metrics you’re tracking aren’t actionable, they’re “vanity metrics” and not that useful:

http://www.fourhourworkweek.com/blog/2009/05/19/vanity-metrics-vs-actionable-metrics/

When you hear companies doing PR about the billions of messages sent using their product, or the total GDP of their economy, think vanity metrics. But there are examples closer to home. Consider the most basic of all reports: the total number of “hits” to your website. Let’s say you have 10,000. Now what? Do you really know what actions you took in the past that drove those visitors to you, and do you really know which actions to take next? In most cases, I don’t think it’s very helpful.

Eric focuses on online / web analytics  scenarios, but it’s easy to make the mental jump to more everyday BI applications and metrics like sales / calls handled / whatever. You should give this article a read.

Most people have discovered by now that in order to use Windows 2008 as a “client” in BI demonstrations that you need to install the “Desktop Experience” feature of the OS. Without it, you’ll have problems getting into SharePoint from Office.

During a recent POC,  I was working with an “all 2008 image” (SQL 2008/Windows 2008/MOSS SP1/PPS SP1/Office 2007), and had a bear of a time getting Excel to browse MOSS libraries. Desktop Experience was installed, but when I attempted to browse to a MOSS library in Excel, the Web Query dialog would pop instead:

If I just plugged the URL of a report or document library into the Open: dialog, Excel was actually importing the page as HTML!

Office 2007 leverages WebDav in order to open MOSS libraries as folders, so I figured that was the problem – I found I was unable to Map a Network Drive to any of the MOSS libraries so I knew I was on the right track.

After several un-install / re-installs of the Desktop Experience component with no change in behavior,  I went ahead and  installed the IIS 7.0 WebDav Extension: It adds some additional configuration UI to InetMgr, and I thought having access to those extra knobs and dials might solve my problem. No joy.

Long story short, I took off my geek hat, put on my consumer hat and got the help of Rakki from PSS. After noticing that I had the IIS 7.0 WebDav Extension installed, he mentioned that MOSS uses its own implementation of WebDav and we didn’t need the extension. He suggested we remove it. I frankly didn’t think it would make a difference since I had the problem before I had installed it and putting the component on the box was an attempt to solve the issue to begin with, but whatever.

So, we un-installed it, and lo and behold, I was able to map drives to my MOSS libraries and Excel / Office was able to browse them. Why? We’re not sure – perhaps the un-install of IIS WebDav did something to nudge the “broken” MOSS WebDav implementation back to life. Complete speculation on my part.

But, if you run into the same issue, maybe this will save you some time (or maybe not, who knows).