Sharepoint and PowerPivot: Connections fail to refresh in SQL Server 2008 Denali CTP3

Sharepoint requires a range of troubleshooting skills because it can involve the integration of various applications, which are necessarily resting on the application stack. I have the deepest respect for SharePoint specialists since i know that the troubleshooting skills can cut across applications and an understanding of the ‘glue’ that welds SharePoint together. Take, for example, the issue which I found recently whilst importing a PowerPivot into SharePoint. Everything looked absolutely fine: the PowerPivot loaded nicely into the PowerPivot gallery, and I could see my Excel spreadsheets too. The black and lime-green SharePoint theme was carried on throughout the Excel workbooks, and the whole thing was looking great. The Excel slicers were highly customised to match the SharePoint theming and customer branding, and the whole pieces, put together, looked fresh.

The PowerPivot was created on a Windows 2008 R2 Server 64 bit machine, which directly accessed the underlying SQL Server 2008 R2 database source. It is roughly 1.8Gb in size, which is just under the 2Gb limit for SharePoint files. I uploaded the PowerPivot from the original machine to the PowerPivot gallery as normal, on the target machine.

The issue came, however, when the user tried to filter the Excel spreadsheets or use the slicers in the Excel Services workbook, based on the PowerPivot. Even though the Excel workbook was part of the file uploaded to Sharepoint, the filters would not refresh. The file was uploaded with ‘All’ so it was not the case that any data had been filtered.  When a filter item was selected, the Excel spreadsheet froze, and then produced the following error message: 

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

This issue is covered by MSDN here. However, none of their resolutions worked for me. The way I resolved this issue was as follows:

My resolution: I recreated a new Excel workbook, which used the PowerPivot as the source. The issue was due to the fact that I had developed the PowerPivot on one machine, and then moved it to another. Otherwise, the initial PowerPivot worked fine, and a new test PowerPivot on the subsequent machine worked fine too. The second machine did not have access to the source data since the PowerPivot was generated from a static data source, so it did not actually need to be refreshed. 

The MSDN suggested resolutions were as follows:

Scenario 1: The cause might be that a domain controller is not available to validate the user identity – this was a test VM that was a full administrator, and this wouldn’t explain why it worked for a PowerPivot that was ‘home grown’ on the server, and not a PowerPivot that had been moved.

Scenario 2: Differing versions of PowerPivot. I was very careful to ensure that the versions of PowerPivot were identical across machines

Scenario 3: IIS Reset in Sharepoint might work; no, I did try.

In the end, it was just easier and quicker to recreate the Excel workbook. The PowerPivot was otherwise unharmed during its movement from one server to another. I could see that the ‘PowerPivot data’ connection, when it was cracked open, pointed to a file that didn’t exist on the target machine. To make sure everything ran smoothly, it was easier just to recreate the Excel workbook in a completely new Excel workbook, which then worked perfectly.

These scenarios show the multi-disciplinary elements of troubleshooting Sharepoint and PowerPivot, since it involved aspects of Sharepoint such as IIS, Windows Claims authentication, and so on. Despite this, the end result of using Sharepoint and PowerPivot together was an great solution and if you haven’t tried it out yet, I suggest that you think about installing a Sharepoint and MS Business Intelligence Virtual Machine and try it out for yourself! Here is a link which might help you to build a VM for yourself.

Hope that helps someone else! 

SQLSaturday in Dublin – a new member of the SQLPass SQLFamily

SQL Saturday #105 in Dublin is a one day FREE training event for anyone interested in SQL Server!  I’m delighted and honoured to say that my session called ‘SQL Server 2012: Business Intelligence & Data Viz‘ has been selected and I will do my very best for the Irish SQL Family! The one day, completely free event will be held on 24th March 2012, at the Hilton Hotel in Dublin. If you need a map, you can find it here

Why would you attend SQLSaturday in Dublin? Well, there are a whole ton of reasons!

The Irish SQLFamily is a growing, vibrant community of sql enthusiasts, and it is great that SQLPass are supporting this growing community! We have a whole sql family next to us, here in the UK, and it would be wonderful to get to know more sql enthusiasts!

There are a range of great speakers who have been selected. At the time of writing, I only know of the following people, but this list is sure to grow:


Allan Mitchell                      Making Data Fit for Business using DQS
Mark Stacey (twitter)         Analysis Services for the DBA

There is no need to mention that is the event is held in central Dublin, which is a great location for a conference! Dublin has a lot to offer for tourists, so why not make a weekend of it? If you are a literary person, then you might like to know that Yeats, Bernard Shaw and James Joyce were all born here, and that Dublin was designated the UNESCO City of Literature in 2010. And there is always the Guinness, of course!

For Dublin, the number of delegates are limited at 240, so you’ll need to register as soon as you can in order to get a place! Here is the link, so please head on over to register and I’ll see you there! 

Blogging is a two-way conversation!


The community blogging event #meme15 created by Jason Strate ( Blog | Twitter ) challenged SQL bloggers to write about blogging, in the hope that this will encourage people. This challenge provoked me to think about the reason that I blog. I have a number of reasons for blogging:


Blogging is a two-way conversation. I get comments and emails via my blog and I cherish them all. Even the challenging comments are superb, because I am glad that someone has taken the time to comment and it helps me to learn. 


Blogging helps me to connect with the community. At SQLBits and other community events, I don’t always get the chance to speak properly with the people that I’d like to meet. I also don’t get the chance to answer all the questions that I receive during my sessions. My blog is a way of reaching out to people, and it is always here. I can use it to respond to queries,and then write to the individual to say that I’ve answered via my blog without naming them.


There are some lonely people in the community. I’m sad to say that I occasionally come across some very lonely people in the community. Blogging means that you can start to touch people, and they can touch you. 


I love Business Intelligence so much because it is a people subject as well as a technical subject.  The ‘people’ aspect is very much a part of the heart of my passion for Business Intelligence. Blogging is my way of understanding different people’s perspectives.


Blogging has also helped me to feel part of the community, regardless of distance or time. It helps me to connect. As we all know, it can be difficult to juggle family, work, life, learning, reading books and ‘me time’. Blogging is, for me, a way of reaching out to people regardless of their distance or time difference or my commitments or their commitments. 


Blogging helps me to really know my stuff. I’m a firm believer in that, to really know something, you have to be able to explain it. I also want to put back into the community some of the help I’ve had over the years, including people on this list, from whom I’ve learned a great deal over the years. It’s always nice to know that you’ve made someone’s day a bit easier just by lending a ‘helping hand’ remotely.


I know that this is more personal than what I usually write, but I hope it helps you to see why I blog. Your feedback is always welcome!


Jen x


SQLBits X – All signed up!

SQLBitsis the largest European community conference, dedicated to Microsoft SQL Server. This SQLBits is going to be extra-special, however: it is the UK Technical Launch for SQL Server 2012, and is held on 29th – 30thMarch. So if you’re interested in the new SQL Server – and who isn’t? – then you better register quick. If you haven’t been before, the event involves a series of quality sessions focusing on various aspects of SQL Server, ranging from hard-core DBA beefy subjects right through to end-user topics. It’s fair to say that there is something for everyone who has an interest in SQL Server.
There are preconference events, which are paid-for, and free community events on the Saturday. I’ve been privileged to hold two preconferences now, one on Data Visualisation, and the other on Sharepoint Business Intelligence. I’ve also been privileged to conduct general one-hour sessions on Reporting Services at SQLBits 7, and Data Visualisation at SQLBits 8, and one on Mobile Business Intelligence on the iPad at SQLBits 9. You are most welcome to download the presentations from Slideshare, which you can do here.  I’ve registered to do the full three days at SQLBits.
You’re probably wondering why I would pre-pay for a precon without the full list being published yet? Basically, I want to pick their brains of the Microsoft SQL Server product teams for a whole day and the SQLBits precon are the best way for me to do just that. 
I feel that the SQLBits precon are an opportunity to ‘touch’ Microsoft Product teams. I believe that, by attending a precon from the Microsoft SQL Server Product teams, there is no better way of getting the real information, straight from the people who manage, run and write SQL Server as well as direct and shape its future. Basically, these are the people who know SQL Server inside out – because they make SQL Server. I would love to spend a day with the Product teams to listen to what they have to say and quiz them on relevant issues. I want to hear them. This is particularly the case where there is a new SQL Server version coming out; I want to make sure I get every detail possible!
There is always room for unscripteddiscussion with the Microsoft SQL Server Product teams. Sometimes I find that the ‘value add’ from a course comes from the unscripted discussion from the presenters. That’s the golden stuff for me, and that’s why I’ll be attending one of the precons. I don’t know which one yet so it is pot luck! However, I’ll be there and I cannot wait!
See you there!

SQLPass Board of Directors Election: Why I voted, and who I voted for!

The PASS Elections 2011 are among us! I’m going to provoke some discussion by telling you who I am going to vote for, and why. I’m not doing this so that you’ll vote for a specific person, although I am going to share my thoughts with you. My main aim is to encourage people to consider the direction of the SQL community and to place a vote. Before we begin, please note that I’m not on the Committee for any community events such as SQLBits or SQLPass. This entirely reflects my own opinions, which you are welcome to take or leave as you wish. My wish is that you’ll vote in the SQLPass Board of Directors election. It is your chance to help to shape and direct the community. You don’t have to speak at events in order to shape the community. You don’t have to speak at an event – ever. It only takes a minute.
What I believe:
You are the community. Own it. Shape it. Use your vote.
Futurology: I think that SQLPass will become a truly global organisation. I support the emerging communities in Sweden, for example, by volunteering to speak at their events. These communities need the chance to grow and with support and advice from ‘old hands’ SQLPass, they can mature more quickly.
Share the passion: Tell people about technical community events. Reach out, make the next generation of new community members. The community is fluid; we are always seeing new bright young things who might have real contributions to the community. Encourage them.
Transparency: I’m all for complete financial transparency in organising community events. If people want to know how much I’ve spent in sponsoring user group or providing coffee or pizza or drinks, come and ask me. It isn’t a lot of money since I am just embarking on the whole sponsorship strategy and it is new to me. To be fair, but I do it because it is a form of Scottish hospitality to offer a drink at events – I mention this because it was Saint Andrews Day recently, after all.
I believe that community organisations such as SQLPass and SQLBits are driven by passion. People who are driven by such a passion for their technical discipline that they want to share this passion and knowledge with other people. You show your passion by turning up to SQL Saturday events, community events such as SQLFaq in the UK, SQLRally in Sweden or SQLServerDays in Belgium. In order to make the community hear you, you should vote.
What I don’t believe:
I don’t believe that the community organisations are about making money. I think that the organisations are about community. Let’s face it, if your objective was to make serious money, would you organise a SQL Server conference to do that? No, I didn’t think so. There. I said it and you can pull me up on it if you like. I think that the community organisations can be a nightmare to organise; they require lots of hard work and long hours by community volunteers. Not a particularly smart or quick way to raise a ton of cash, in my opinion.
Here are my key thoughts on the future of PASS over the next year:
Global: I believe that the global growth of SQLPass will become more prevalent globally. For example, in November, there were eight new chapters stretching from Nepal, Egypt, the UK and the US. For me, that’s just simply amazing. Personally I’m glad that PASS can support these new communities as they reach out to new members in their early stages. The new chapters can have PASS as a resource to depend on, which is particularly vital in the early days.
WIT: I think that WIT will grow more prevalently, and PASS already have a WIT focus already. It saddens me that there is a need for a WIT organisation, since someone’s sex should be irrelevant to their ability to do their job in the IT world. I believe that skills, knowledge and experience are what counts –what projects you have done, not what sex you are. However, I know I can call upon the support of other WIT if I need to, since the framework is already there. I haven’t taken up this facility but I do understand that some WIT may feel that they need the support and encouragement of other WIT for advice.
Passion: Community is at the core, and it needs to be nurtured. Some folks are givers, and others are takers. Which brings me to the key attributes that I think a PASS Director needs:

  •  International focus and global relationships
  • WIT support
  •  Passion
  •  Proven community Leadership
  • Astute Business Sense
  • Knowledge of the technology

So, who will I be voting for? Well, here are some caveats:
They are all outstanding candidates. If they don’t get picked this time, I’m sure that they will get it on another occasion. I am not telling you who to vote for! Just use your vote. Please.
I will be voting for Rob Farley ( blog | twitter | campaign statement ) for the following reasons:
He’d do a brilliant job on the BoD. He’d be a voice for the wider community and has all the credentials. More detail:
·         International focus and global relationships – I am writing this from Europe, so this is my perspective from the Old World. I’ve only met Rob twice but he has been incredibly supportive of my technical and personal life from afar. Rob has a global reach, and is well known here in the UK. Rob’s previously supported community events here in the UK, such as SQLBits, by flying over from Australia to provide sessions. He’s the furthest travelled speaker, as far as I’m aware – what a commitment and a huge personal effort, and I’d like to use this as an opportunity to thank Rob for his huge support for us here in the UK.
·         WIT support – Rob helped to organise the 24 hop WIT campaign. This was an incredibly brave thing to do, and I know this involved a lot of effort for him personally. He stepped me through it all the way and I’d like to thank him again.
·         Passion– about sql, yes!
·         Proven community Leadership – building the user group in Adelaide from scratch, along with other commitments such as non-voting position at sqlpass.
·         Astute Business Sense – building a consultancy from scratch is hard! I should know. I’m still learning!
·         Knowledge of the technology – Rob contributed two chapters to the first MVP Deep Dives book, and has contributed another chapter to the second MVP Deep Dives book.
I also voted for two others but I think that this blog is probably long enough! To summarise, I’ve given this hard thought and I think that SQLPass needs an international perspective at this critical growth time. That’s why I’ll be voting Rob for SQLPass Director. #VoteForRob 
I look forward to your comments