I could be a little late to the party, but I caught wind of a blog post, “Booth Babes Don’t Wear Glasses,” circulating in the Twitterverse that prompted me to investigate and I was a bit startled by some of the statements made. I was trying to wrap up my latest chapter on my current book project (the primary reason for my silence this month – that and a 2 week vacation!), so I didn’t spend a lot of time analyzing the blog post other than to tweet my initial reaction that I found it hard to believe. Not the booth babe part. I’ve seen those gals before, but not so much at PASS, or SQL Connections, or TechEd. Or at least I haven’t noticed them if they’re there. Maybe it’s just me…

No, the statement that caught my attention first was “…when I approach male attendees, they seem a bit shocked that I am talking technology with them.” And I thought, really? In this day and age? I mean, it’s 2010, right? If I am reading this post correctly, this quote comes from something that Denise Dubie of NetworkWorld wrote, but I couldn’t locate the article online and the blog post didn’t provide a link.

Conceding that I read the post in a hurry yesterday, I decided that perhaps I’d been too hasty and read something out of context. So I went back and re-read the post again today. The author, Lori MacVittie, appears to be quite a prolific blogger at DevCentral. I don’t know anything about her, but her blog archives are impressive. Clearly, she’s a woman with several years of experience, not someone who just stepped out of college and entered the work world. I’d expect that across those years, she’d have a variety of experiences with all types of men – some who were favorably disposed towards working with women, and some who weren’t, but her post implies a preponderance of those who weren’t. She doesn’t really provide any evidence, anecdotal or otherwise, but makes the following observations:

• Offering an explanation for the failure of women to enter science, technology, engineering or math (STEM), “Young women, according to research [which she fails to cite], aren’t thinking about the difficulties that exist in a traditionally male-dominated field…[it has] everything to do with men and attitudes.”
• Why do these men have these attitudes? She explains that IT is still young compared to other STEM, it’s dominated by men, and women in general haven’t been out in the work force all that long (less than 40 years,she says). “It’s no surprise that men in general, then, haven’t had a whole lot of time yet to adjust.”
• “It would likely be difficult to find a woman in technology that hasn’t had this same experience. The shock, the surprise, the change in tone and demeanor that comes from male counterparts upon realizing that the woman they’re talking to knows what she’s talking about.”
• “The debate over ‘booth babes’ isn’t really about booth babes… It’s about the reaction of men to a technically competent woman, no matter what she’s wearing…It’s about the assumption that no woman is technically competent — at a trade show or on a conference call.”

Wow. Those are some heavy-duty attitudes she’s talking about. And it’s quite possible those are attitudes that she has to contend with. I just haven’t seen anything like it in my career, at least not for a very long time. And even when I did, I would consider the experience an outlier. No, actually – the time I’m thinking about had nothing to do with the man in question going into shock upon learning that I knew what I was talking about. In my situation, he was blatantly discriminating by holding me back from a job he knew I could do in favor of a man and kindly asked me do the work required of that position until he did find a man. In retrospect, it really did work out for the best. I was the one in shock that I actually heard him say – to my face – that he wanted to put a man in that position. But I digress…

I started my IT career in 1984, installing computer systems for tractor dealerships and training the personnel how to do everything with that new computer — running the parts department, managing the service department, selling farm equipment, doing payroll, and keeping the books. As a very young woman in a very male-dominated industry – agriculture – it was quite an experience. I had one good ol’ boy in Kansas refuse to hand over the $250,000 check for his newly installed system because he didn’t think it was right that my company send someone that was still wet behind the years (and female, although he didn’t say it out loud). I just smiled and reminded him that he had a working system and trained personnel, so I thought he got exactly what he paid for. He smiled back and handed me the check.

Other than dealing with the very male client base, I didn’t realize at the time what a novel position I was in from a sociohistoric point of view. The software company that employed me had a lot of women. More than 50% women as I recall. The men there didn’t have an attitude that I recollect. But maybe it’s just me…and a bad memory. Anyway, it just seemed normal to have women in the workplace. It wasn’t until years later when I took a women’s history course that I realized that 1984 was the year that women entered the workplace en masse, in professions other than teachers and nurses and secretaries. I was so busy being in the middle of it that I didn’t notice. And had nothing to compare it to.

If I had no reason to appreciate the novelty of what I was doing in 1984, because I had nothing to compare with the experience, why should men who are younger than me require a period to adjust? That’s the implication in the “Booth Babes” post. We simply need to give men more time. And let me do that math – 2010 – 1984 = 26 years. If any man my age or older can’t adjust in 26 years, well, I’m sorry. I think he’s got other issues, since the world is moving much faster and requiring many more adjustments than having women around. Maybe it’s just me…I might need to develop a more sympathetic outlook towards men who are having such trouble coping with technologically-savvy women. If I can find one.

I guess I’m luckier than women like Lori and Denise who are encountering men with bad attitudes. I am surrounded by a community that is admittedly male-dominated. I no longer work for that software company surrounded by women, and as I get older, I notice there are fewer and fewer women in IT. The facts bear this out if one considers just computer science degrees – only 18% were earned by women in 2008 as compared to 37% in 1985 (National Center for Women & Information Technology).

My daughter graduated from MIT in 2008. Not with a computer science degree, but one in theoretical math. I thought young women weren’t supposed to do that sort of thing? Maybe it’s just me…and my genes. She decided that the proper thing to do with a theoretical math degree would be to continue on to grad school, but ultimately decided against that and became a business intelligence consultant like me. (See, I’m doing my part for WIT!)

I asked for her perspective on this notion that men have negative attitudes about women in IT or STEM for that matter. She said that in school she had heard that women in math could have problems with old professors, but she never personally encountered it and never felt disadvantaged, although being female clearly put her in the minority. No, the bigger problem was with other male students, but she chalked that up to their introverted natures. Hmm, could it be the shock and awe that Lori and Denise described was not a negative attitude, but rather the social awkwardness that sometimes characterizes guys who work in IT? Just a thought…

As for her experiences in the work world, my daughter says her biggest obstacle to acceptance has not been gender, but age. She’s still young and has to prove herself. And that’s to be expected.

Maybe it’s just me… Perhaps my worldview of the attitudes towards women in IT is too narrowly focused. Over a 26 year career, I’ve worked in a lot of industries with a lot of different technologies, but I certainly have no idea what’s going on in the IT world at large. My focus has been limited to the Microsoft SQL Server community for the past 10 years. From the way Lori and Denise make it sound out there, I’m rather glad I’m having such a sheltered experience. The SQL Server community has been quite welcoming to women for as long as I’ve been a member. In fact, there is an active effort to get more women involved through a Women in Technology (WIT) special interest group. At every conference I attend, there is always some event devoted to WIT and this year I’ve been invited to participate in the panel discussion at PASS Summit 2010.

Maybe it’s just me… but I think the guys in the SQL Server community are awesome and certainly don’t deserve to be lumped into the same group with others who might have a problem with women in IT. Looking forward to seeing all of you in Seattle in November!

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

Recently I came across a problem with an Analysis Services data connection when I was trying to create a status indicator in SharePoint 2010 (formerly known as a key performance indicator in SharePoint 2007). I had successfully created the data connection in Excel, verified that I could create a PivotTable with it, and then saved the connection to Excel Services as an Office Data Connection (ODC) file. However, when I tried to use the ODC to create a status indicator, the following error displayed:

"A connection cannot be made. Ensure that the server is running."

Well, I knew that the server was running, because I had just created a PivotTable with the same ODC. Then I tried searching the Web, but couldn’t find anything useful (which prompted me to write this post!). In this scenario, I am running all servers on the same machine for demonstration purposes, so I knew it wasn’t a double-hop problem. I had read that enabling SQL Browser would help, or using the IP address rather than the server name could be solutions also, but neither of those recommendations helped.

I tried different connections that I had created over time and several had the same problem – connection cannot be made. After fussing around with different options, I finally went back to study my connection strings and saw my problem:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008R2;Data Source=.;Location=.;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

When I removed Location=.; from the string, and exported the new connection to SharePoint, the connection worked just fine! In further testing, I found that I put the server name, rather than the . placeholder to represent localhost, the connection also worked.

Now the mystery is how I got that property into the connection string in the first place. I can’t reproduce it by using the Data Connection Wizard in Excel, but that’s generally the only way I produce an ODC file. Regardless of how it came to be in my connection string, I now know that the Location property is not required for the ODC file to work, or must reference the actual server name if it is included.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

“Laissez les bons temps rouler” is a Cajun phrase that I heard frequently when I lived in New Orleans in the mid-1990s. It means “Let the good times roll!” and encapsulates a feeling of happy expectation. As I met with many of my peers and new acquaintances at the Microsoft BI Conference last week, this phrase kept running through my mind as people spoke about their plans in their respective businesses, the benefits and opportunities that the recent releases in the BI stack are providing, and their expectations about the future of the BI stack.

Notwithstanding some jabs here and there to point out the platform is neither perfect now nor will be anytime soon (along with admissions that the competitors are also not perfect), and notwithstanding several missteps by the event organizers (which I don’t care to enumerate), the overarching mood at the conference was positive. It was a refreshing change from the doom and gloom hovering over several conferences that I attended in 2009. Although many people expect economic hardships to continue over the coming year or so, everyone I know in the BI field is busier than ever and expects to stay busy for quite a while.

Self-Service BI

Self-service was definitely a theme of the BI conference. In the keynote, Ted Kummert opened with a look back to a fairy tale vision of self-service BI that he told in 2008. At that time, the fairy tale future was a time when “every end user was able to use BI technologies within their job in order to move forward more effectively” and transitioned to the present time in which SQL Server 2008 R2, Office 2010, and SharePoint 2010 are available to deliver managed self-service BI.

From the Microsoft BI Conference Keynote delivered by Ted Kummert

This set of technologies is presumably poised to address the needs of the 80% of users that Kummert said do not use BI today. He proceeded to outline a series of activities that users ought to be able to do themselves–from simple changes to a report like formatting or an addtional data visualization to integration of an additional data source. The keynote then continued with a series of demonstrations of both current and future technology in support of self-service BI. Some highlights that interested me:

• PowerPivot, of course, is the flagship product for self-service BI in the Microsoft BI stack. In the TechEd keynote, which was open to the BI conference attendees, Amir Netz (twitter) impressed the audience by demonstrating interactivity with a workbook containing 100 million rows. He upped the ante at the BI keynote with his demonstration of a future-state PowerPivot workbook containing over 2 billion records. It’s important to note that this volume of data is being processed by a server engine, and not in the PowerPivot client engine. (Yes, I think it’s impressive, but none of my clients are typically wrangling with 2 billion records at a time. Maybe they’re thinking too small. This ability to work quickly with large data sets has greater implications for BI solutions than for self-service BI, in my opinion.)
• Amir also demonstrated KPIs for the future PowerPivot, which appeared to be easier to implement than in any other Microsoft product that supports KPIs, apart from simple KPIs in SharePoint. (My initial reaction is that we have one more place to build KPIs. Great. It’s confusing enough. I haven’t seen how well those KPIs integrate with other BI tools, which will be important for adoption.)
• One more PowerPivot feature that Amir showed was a graphical display of the lineage for calculations. (This is hugely practical, especially if you build up calculations incrementally. You can more easily follow the logic from calculation to calculation. Furthermore, if you need to make a change to one calculation, you can assess the impact on other calculations.)
• Another product demonstration will be available within the next 30 days–Pivot for Reporting Services. If you haven’t seen this technology yet, check it out at www.getpivot.com. (It definitely has a wow factor, but I’m skeptical about its practicality. However, I’m looking forward to trying it out with data that I understand.)
• Michael Tejedor (twitter) demonstrated a feature that I think is really interesting and not emphasized nearly enough–overshadowed by PowerPivot, no doubt. That feature is the Microsoft Business Intelligence Indexing Connector, which enables search of the content of Excel workbooks and Reporting Services reports. (This capability existed in MOSS 2007, but was more cumbersome to implement. The search results in SharePoint 2010 are not only cooler, but more useful by describing whether the content is found in a table or a chart, for example.)

This may yet be the dawning of the age of self-service BI – a phrase I’ve heard repeated from time to time over the last decade – but I think BI professionals are likely to stay busy for a long while, and need not start looking for a new line of work. Kummert repeatedly referenced strategic BI solutions in contrast to self-service BI to emphasize that self-service BI is not a replacement for the services that BI professionals provide. After all, self-service BI does not appear magically on user desktops (or whatever device they want to use). A supporting infrastructure is necessary, and grows in complexity in proportion to the need to simplify BI for users.

It’s one thing to hear the party line touted by Microsoft employees at the BI keynote, but it’s another to hear from the people who are responsible for implementing and supporting it within an organization. Rob Collie (blog | twitter), Kasper de Jonge (blog | twitter), Vidas Matelis (site | twitter), and I were invited to join Andrew Brust (blog | twitter) as he led a Birds of a Feather session at TechEd entitled “PowerPivot: Is It the BI Deal-Changer for Developers and IT Pros?” I would single out the prevailing concern in this session as the issue of control. On one side of this issue were those who were concerned that they would lose control once PowerPivot is implemented. On the other side were those who believed that data should be freely accessible to users in PowerPivot, and even acknowledgment that users would get the data they want even if it meant they would have to manually enter into a workbook to have it ready for analysis. For another viewpoint on how PowerPivot played out at the conference, see Rob Collie’s observations.

Collaborative BI

I have been intrigued by the notion of collaborative BI for a very long time. Before I discovered BI, I was a Lotus Notes developer and later a manager of developers, working in a software company that enabled collaboration in the legal industry. Not only did I help create collaborative systems for our clients, I created a complete project management from the ground up to collaboratively manage our custom development work. In that case, collaboration involved my team, my client contacts, and me. I was also able to produce my own BI from that system as well, but didn’t know that’s what I was doing at the time. Only in recent years has SharePoint begun to catch up with the capabilities that I had with Lotus Notes more than a decade ago. Eventually, I had the opportunity at that job to formally investigate BI as another product offering for our software, and the rest – as they say – is history. I built my first data warehouse with Scott Cameron (who has also ventured into the authoring world by writing Analysis Services 2008 Step by Step and was at the BI Conference last week where I got to reminisce with him for a bit) and that began a career that I never imagined at the time.

Fast forward to 2010, and I’m still lauding the virtues of collaborative BI, if only the tools will catch up to my vision! Thus, I was anxious to see what Donald Farmer (blog | twitter) and Rita Sallam of Gartner had to say on the subject in their session “Collaborative Decision Making.” As I suspected, the tools aren’t quite there yet, but the vendors are moving in the right direction. One thing I liked about this session was a non-Microsoft perspective of the state of the industry with regard to collaborative BI. In addition, this session included a better demonstration of SharePoint collaborative BI capabilities than appeared in the BI keynote. Check out the video in the link to the session to see the demonstration. One of the use cases that was demonstrated was linking from information to a person, because, as Donald put it, “People don’t trust data, they trust people.”

The Microsoft BI Stack in General

A question I hear all the time from students when I’m teaching is how to know what tools to use when there is overlap between products in the BI stack. I’ve never taken the time to codify my thoughts on the subject, but saw that my friend Dan Bulos provided good insight on this topic from a variety of perspectives in his session, “So Many BI Tools, So Little Time.” I thought one of his best points was that ideally you should be able to design in your tool of choice, and then deploy to your tool of choice. Unfortunately, the ideal is yet to become real across the platform. The closest we come is with the RDL in Reporting Services which can be produced from two different tools (Report Builder or Business Intelligence Development Studio’s Report Designer), manually, or by a third-party or custom application. I have touted the idea for years (and publicly said so about 5 years ago) that eventually more products would be RDL producers or consumers, but we aren’t there yet. Maybe in another 5 years.

Another interesting session that covered the BI stack against a backdrop of competitive products was delivered by Andrew Brust. Andrew did a marvelous job of consolidating a lot of information in a way that clearly communicated how various vendors’ offerings compared to the Microsoft BI stack. He also made a particularly compelling argument about how the existence of an ecosystem around the Microsoft BI stack provided innovation and opportunities lacking for other vendors. Check out his presentation, “How Does the Microsoft BI Stack…Stack Up?”

Expo Hall

I had planned to spend more time in the Expo Hall to see who was doing new things with the BI stack, but didn’t manage to get very far. Each time I set out on an exploratory mission, I got caught up in some fascinating conversations with one or more of my peers. I find interacting with people that I meet at conferences just as important as attending sessions to learn something new. There were a couple of items that really caught me eye, however, that I’ll share here.

Pragmatic Works. Whether you develop SSIS packages, build SSAS cubes, or author SSRS reports (or all of the above), you really must take a look at BI Documenter. Brian Knight (twitter) walked me through the key features, and I must say I was impressed. Once you’ve seen what this product can do, you won’t want to document your BI projects any other way. You can download a free single-user database edition, or choose from more feature-rich standard or professional editions.

Microsoft Press ebooks. I also stopped by the O’Reilly Media booth to meet some folks that one of my acquisitions editors at Microsoft Press recommended. In case you haven’t heard, Microsoft Press has partnered with O’Reilly Media for distribution and publishing. Apart from my interest in learning more about O’Reilly Media as an author, an advertisement in their booth caught me eye which I think is a really great move. When you buy Microsoft Press ebooks through the O’Reilly web site, you can receive it in any (or all) of the following formats where possible: PDF, epub, .mobi for Kindle and .apk for Android. You also have lifetime DRM-free access to the ebooks. As someone who is an avid collector of books, I fnd myself running out of room for storage. In addition, I travel a lot, and it’s hard to lug my reference library with me. Today’s e-reader options make the move to digital books a more viable way to grow my library. Having a variety of formats means I am not limited to a single device, and lifetime access means I don’t have to worry about keeping track of where I’ve stored my files. Because the e-books are DRM-free, I can copy and paste when I’m compiling notes, and I can print pages when necessary. That’s a winning combination in my mind!

Overall, I was pleased with the BI conference. There were many more sessions that I couldn’t attend, either because the room was full when I got there or there were multiple sessions running concurrently that I wanted to see. Fortunately, many of the sessions are accessible for viewing online at http://www.msteched.com/2010/NorthAmerica along with the TechEd sessions. You can spot the BI sessions by the yellow skyline on the title slide of the presentation as shown below.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

This month’s T-SQL Tuesday is hosted by Jorge Segarra (blog | twitter) who decided that we should write about our favorite new feature in SQL Server 2008 R2. The majority of my published works concentrates on Reporting Services, so the obvious answer for me with regard to favorite new features is…Reporting Services. I can’t pick just one thing in Reporting Services, so instead I thought I’d compile a list of my posts of the new features in Reporting Services 2008 R2:

• Map Wizard for spatial data (The World is But a Stage)
• Pagination features (I’ve Got Your Page Number)
• Lookup functions (Look Up, Look Down, Look All Around – Part I, Part II, Part III)
• Test Connection button (Testing, Testing 1-2-3)
• Conditional formatting based on format, i.e. RenderFormat (As You Like It)

And I wrote an overview of the business intelligence features in SQL Server 2008 R2 for Microsoft Press in the free e-book, Introducing Microsoft SQL Server 2008 R2, if you’re curious about what else is new in both the BI platform as well as the relational engine.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

Many people like me want (or need) to do their business intelligence development work on a laptop. As someone who frequently speaks at various events or teaches classes on all subjects related to the Microsoft business intelligence stack, I need a way to run multiple server products on my laptop with reasonable performance. Once upon a time, that requirement meant only that I had to load the current version of SQL Server and the client tools of choice. In today’s post, I’ll review my latest experience with trying to make the newly released Microsoft BI products work with a Windows 7 operating system.

The entrance of Microsoft Office SharePoint Server 2007 into the BI stack complicated matters and I started using Virtual Server to establish a “suitable” environment. As part of the team that delivered a lot of education as part of the Yukon pre-launch activities (that would be SQL Server 2005 for the uninitiated), I was working with four – yes, four – virtual servers. That was a pretty brutal workload for a 2GB laptop, which worked if I was very, very careful. It could also be a finicky and unreliable configuration as I learned to my dismay at one TechEd session several years ago when I had to reboot a very carefully cached set of servers just minutes before my session started. Although it worked, it came back to life very, very slowly much to the displeasure of the audience. They couldn’t possibly have been less pleased than me.

At that moment, I resolved to get the beefiest environment I could afford and consolidate to a single virtual server. Enter the 4GB 64-bit laptop to preserve my sanity and my livelihood. Likewise, for SQL Server 2008, I managed to keep everything within a single virtual server and I could function reasonably well with this approach.

Now we have SQL Server 2008 R2 plus Office SharePoint Server 2010. That means a 64-bit operating system. Period. That means no more Virtual Server. That means I must use Hyper-V or another alternative. I’ve heard alternatives exist, but my few dabbles in this area did not yield positive results. It might have been just me having issues rather than any failure of those technologies to adequately support the requirements.

My first run at working with the new BI stack configuration was to set up a 64-bit 4GB laptop with a dual-boot to run Windows Server 2008 R2 with Hyper-V. However, I was generally not happy with running Windows Server 2008 R2 on my laptop. For one, I couldn’t put it into sleep mode, which is helpful if I want to prepare for a presentation beforehand and then walk to the podium without the need to hold my laptop in its open state along the way (my strategy at the TechEd session long, long ago). Secondly, it was finicky with projectors. I had issues from time to time and while I always eventually got it to work, I didn’t appreciate those nerve-wracking moments wondering whether this would be the time that it wouldn’t work.

Somewhere along the way, I learned that it was possible to load SharePoint 2010 in a Windows 7 which piqued my interest. I had just acquired a new laptop running Windows 7 64-bit, and thought surely running the BI stack natively on my laptop must be better than running Hyper-V. (I have not tried booting to Hyper-V VHD yet, but that’s on my list of things to try so the jury of one is still out on this approach.) Recently, I had to build up a server with the RTM versions of SQL Server 2008 R2 and Sharepoint Server 2010 and decided to follow suit on my Windows 7 Ultimate 64-bit laptop. The process is slightly different, but I’m happy to report that it IS possible, although I had some fits and starts along the way.

DISCLAIMER 1: These products are NOT intended to be run in production mode on the Windows 7 operating system. The configuration described in this post is strictly for development or learning purposes and not supported by Microsoft. If you have trouble, you will NOT get help from them. I might be able to help, but I provide no guarantees of my ability or availablity to help.

DISCLAIMER 2: The instructions below apply only to an environment in which the Windows 7 client is joined to a domain. You must log into the client machine using a valid Windows user account for that domain. Thanks to Kevin who pointed out this requirement which I had neglected to mention in my initial post (Updated May 30, 2010).

I won’t provide the step-by-step instructions in this post as there are other resources that provide these details, but I will provide an overview of my approach, point you to the relevant resources, describe some of the problems I encountered, and explain how I addressed those problems to achieve my desired goal.

Because my goal was not simply to set up SharePoint Server 2010 on my laptop, but specifically PowerPivot for SharePoint, I started out by referring to the installation instructions at the PowerPiovt-Info site, but mainly to confirm that I was performing steps in the proper sequence. I didn’t perform the steps in Part 1 because those steps are applicable only to a server operating system which I am not running on my laptop. Then, the instructions in Part 2, won’t work exactly as written for the same reason. Instead, I followed the instructions on MSDN, Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008. In general, I found the following differences in installation steps from the steps at PowerPivot-Info:

• You must copy the SharePoint installation media to the local drive so that you can edit the config.xml to allow installation on a Windows client.
• You also have to manually install the prerequisites. The instructions provides links to each item that you must manually install and provides a command-line instruction to execute which enables required Windows features.

I will digress for a moment to save you some grief in the sequence of steps to perform. I discovered later that a missing step in the MSDN instructions is to install the November CTP Reporting Services add-in for SharePoint. When I went to test my SharePoint site (I believe I tested after I had a successful PowerPivot installation), I ran into the following error:

Could not load file or assembly ‘RSSharePointSoapProxy, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.

I was rather surprised that Reporting Services was required. Then I found an article by Alan le Marquand, Working Together: SQL Server 2008 R2 Reporting Services Integration in SharePoint 2010,that instructed readers to install the November add-in. My first reaction was, “Really?!?” But I confirmed it in another TechNet article on hardware and software requirements for SharePoint Server 2010. It doesn’t refer explicitly to the November CTP but following the link took me there. (Interestingly, I retested today and there’s no longer any reference to the November CTP. Here’s the link to download the latest and greatest Reporting Services Add-in for SharePoint Technologies 2010.) You don’t need to download the add-in anymore if you’re doing a regular server-based installation of SharePoint because it installs as part of the prerequisites automatically.

When it was time to start the installation of SharePoint, I deviated from the MSDN instructions and from the PowerPivot-Info instructions:

• On the Choose the installation you want page of the installation wizard, I chose Server Farm.
• On the Server Type page, I chose Complete.
• At the end of the installation, I did not run the configuration wizard.

Returning to the PowerPivot-Info instructions, I tried to follow the instructions in Part 3 which describe installing SQL Server 2008 R2 with the PowerPivot option. These instructions tell you to choose the New Server option on the Setup Role page where you add PowerPivot for SharePoint. However, I ran into problems with this approach and got installation errors at the end.

It wasn’t until much later as I was investigating an error that I encountered Dave Wickert’s post that installing PowerPivot for SharePoint on Windows 7 is unsupported. Uh oh. But he did want to hear about it if anyone succeeded, so I decided to take the plunge. Perseverance paid off, and I can happily inform Dave that it does work so far. I haven’t tested absolutely everything with PowerPivot for SharePoint but have successfully deployed a workbook and viewed the PowerPivot Management Dashboard. I have not yet tested the data refresh feature, but I have installed. Continue reading to see how I accomplished my objective.

I unintalled SQL Server 2008 R2 and started again. I had different problems which I don’t recollect now. However, I uninstalled again and approached installation from a different angle and my next attempt succeeded. The downside of this approach is that you must do all of the things yourself that are done automatically when you install PowerPivot as a new server. Here are the steps that I followed:

• Install SQL Server 2008 R2 to get a database engine instance installed.
• Run the SharePoint configuration wizard to set up the SharePoint databases.
• In Central Administration, create a Web application using classic mode authentication as per a TechNet article on PowerPivot Authentication and Authorization.
• Then I followed the steps I found at How to: Install PowerPivot for SharePoint on an Existing SharePoint Server. Especially important to note – you must launch setup by using Run as administrator. I did not have to manually deploy the PowerPivot solution as the instructions specify, but it’s good to know about this step because it tells you where to look in Central Administration to confirm a successful deployment.

I did spot some incorrect steps in the instructions (at the time of this writing) in How To: Configure Stored Credentials for PowerPivot Data Refresh. Specifically, in the section entitled Step 1: Create a target application and set the credentials, both steps 10 and 12 are incorrect. They tell you to provide an actual Windows user name and password on the page where you are simply defining the prompts for your application in the Secure Store Service. To add the Windows user name and password that you want to associate with the application – after you have successfully created the target application – you select the target application and then click Set credentials in the ribbon.

Lastly, I followed the instructions at How to: Install Office Data Connectivity Components on a PowerPivot server. However, I have yet to test this in my current environment.

I did have several stops and starts throughout this process and edited those out to spare you from reading non-essential information. I believe the explanation I have provided here accurately reflect the steps I followed to produce a working configuration. If you follow these steps and get a different result, please let me know so that together we can work through the issue and correct these instructions. I’m sure there are many other folks in the Microsoft BI community that will appreciate the ability to set up the BI stack in a Windows 7 environment for development or learning purposes.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.