Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for July, 2010

PerformancePoint Services PowerPivot Data Sources Error

Posted by denglishbi on July 21, 2010

So I was working on a PerformancePoint 2010 dashboard and making really good progress.  I then ran had to add a new source into a PowerPivot (Excel 2010) file and the source was a SharePoint 2010 list.  I went out to the SharePoint list and clicked on the Export as Data Feed option in the SharePoint Ribbon List options.  When I pulled this atom feed into PowerPivot I ended up receiving an error message (even though the connection tested out okay) stating

“For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method”

So I checked the advanced settings of the connection setup, but didn’t see anything related to this.  I then went ahead and did a quick search and came across this posting from Dave Wickert Using a SharePoint list as a data source and realized that when the SharePoint 2010 server was setup that the ADO.NET Data Services 3.5 SP1 hotfix was not installed on the server.  I went ahead and installed this which then required a reboot for the fix to take effect.

When the server was back up I could then proceed with the SharePoint list import into PowerPivot, but then I realized that none of the PowerPivot data sources in PerformancePoint Dashboard Designer were working.

image 

“This action cannot complete because PerformancePoint Services is not configured correctly.  Additional details have been logged for your administrator”

And when I went into a report referencing the PowerPivot source in the designer I got this message:

image

“This action cannot complete because PerformancePoint Services is not configured correctly.  Additional details have been logged for your administrator.”

Interesting since everything was working fine prior to the server reboot.  So I went and took a look at one of the deployed dashboards and noticed that nothing was working.

image

image

Well this isn’t good.  So I checked that the PowerPivot files were accessible in SharePoint and were working properly which there were.  I then check the authentication setup on the PerformancePoint PowerPivot data sources to verify everything was still setup properly.  The data sources were configured to utilize the Per User Identity option.  Typically this might require Kerberos to be configured, but in our case we were using PowerPivot which is claims based aware and this should work…assuming that the Claims to Windows Token Service is actually started (Information on SharePoint Services that need to be started).  So I go ahead and take a look at the services running on the server.

image

Oh no, the service has not started yet.  I go ahead and start the service and retest my PowerPivot data source connections in Dashboard Designer

image

Success!  We are off to the races.

For some additional information on troubleshooting PerformancePoint Services check out this posting here – PerformancePoint Services troubleshooting.

UPDATE (7/21/2010): Kasper de Jonge just notified me about an issue with this service and once again Dave Wickert has a blog posting about it – Help: c2wts has fallen and it cannot get up.  Yesterday I waited for over 10 minutes and it still had not started.  Looking forward to implementing this fix.

Posted in PerformancePoint | Tagged: , | 6 Comments »

Reporting Services – Remote Server Returned an Error: (401) Unauthorized

Posted by denglishbi on July 18, 2010

This past week I was working on a new project and the client was leveraging the new Microsoft BI Stack working with PowerPivot, SharePoint 2010, PerformancePoint Services 2010, and Reporting Services 2008 R2 running in Integrated Mode.  Everything was working great with PowerPivot, loading files into the PowerPivot Gallery, and using PerformancePoint Services with PowerPivot as the data sources.  The odd thing was that Report Builder was not working against PowerPivot, well it was kind of.  Let me try and explain and walk through the steps to reproduce the issue:

  • We created a Web Application in SharePoint 2010 – http://<ServerName&gt;:51000 and then deployed the PowerPivot web application (powerpivotwebapp.wsp) to it – in order to deploy the application you need to go into Central Administration->System Settings->Manage Farm Solutions->powerpivotwebapp.wsp

image

  • Now we will create a new site collection within this web application and we will use the Enterprise Business Intelligence Center template (one thing to note is that the URL for this site collection is being set to http://<ServerName&gt;:51000/sites/msbi

image

  • Now we will access the new site collection and add a new PowerPivot Gallery library to the site (go into Site Actions->More Options to add the library)

image

  • Now we will upload a PowerPivot file to the PowerPivot Gallery library to utilize as a source (nothing fancy with the data, just some store sales data broken out by year and month with a PivotChart in the file)

 image       image

  • Now we will launch Report Builder 3.0 from the PowerPivot Gallery to create a report off this StoreSales.xlsx file.  Once Report Builder is running we will check the properties of the data source that is generated and test the connection

image

  • Everything looks good right?  Before we try to create a dataset let’s try clicking the Build… button and testing the source connection one more time

image

  • Oh no…that can’t be good.  Well now let’s back out of the and try to create a dataset

image

  • Hmmm…same error message ‘The remote server returned an error: (401) Unauthorized.’ How can this be?  We are site collection administrators, owners of the site, created the PowerPivot file and uploaded it, and we can even create a PerformancePoint data source and PerformancePoint content without any issues (this portion is not show here, but all of the PerformancePoint functionality does work – I should mention that this is a Single Server development environment).
  • So now what.  I was able to use Report Builder to create SQL Server and SharePoint List reports – ran into issues when trying to save them to the site though.  In order to get them to the site I had to save the RDL files locally and then upload them to SharePoint.  They did work once they were uploaded, so now what is the issue?  How about we check out the Report Server to see what is going on.

image

  • That seems to be working just fine.  Now let’s try clicking on the site collection URL that we created

image

  • Well that can’t be good. ‘An internal error occurred on the report server. See the error log for more details. (rsInternalError) The site http://<ServerName&gt;:51000/ could not be found in the Web application SPWebApplication Name=SharePoint – 51000.’ Now according to the message it is looking for a site that would be at the Root of the Web Application that we originally created.  Remember that the site collection was originally created at http://<ServerName&gt;:51000/sites/msbi.  Okay, so let’s try creating another site collection.

image

  • Now let’s try the Report Server again.  Well what do you know, now we can explore the original site collection.

image

  • Now let’s try creating the dataset in that Report Builder report again against that PowerPivot file

image

  • Well what do you know, it works now:)  Game on!

So the lesson of the day is to make sure that you create a site collection at the Root of the web application in SharePoint, otherwise Reporting Services will definitely have some issues.  Typically I would always do my development this way and then create sub sites within the site collection itself.  In this case though the method that was originally chosen was to create the site collection at what I would consider a sub site level.  This caused some extremely odd behavior within Reporting Services with connectivity and creating reports.  The solution was to go back into Central Administration in SharePoint and create a Root level site collection for the web application.

Just kind of weird though when originally it all pointed to what I would have thought to have been a permission issue.  Then after further testing I thought maybe a PowerPivot issue, then SharePoint Integration issue, and it turned out to be a site collection issue.  Weird.  Is this a bug?  Should it be added to the Connect site?

Posted in Reporting Services | Tagged: , | Leave a Comment »

MSDN Subscription Giveaway #1

Posted by denglishbi on July 7, 2010

As many people know or have heard MVPs have come into possession of three Visual Studio Ultimate MSDN subscriptions (minus the support and magazine offer).  These subscriptions are provided to us to distribute and help drive adoption of the tools.  These subscriptions are the top of the line valued at approximately $12K and it is good for 12 months.  All of the software that is included in the subscription is for development and test purposes only and they must be activated within 180 days.  The winner of Giveaway #1 will receive the subscription activation key along with the URL that is required to activate it.  Typically upon activation it will take a few weeks possibly to activate, could possibly be different with these, can’t say for sure though.msdn subscription image

So, what am I requiring people that are interested to do for the first giveaway?  Good question.  For people that know a tad bit about me, I am big on community support.  I like to see people come together to try and solve and help others with issues.  So for anyone that is interested in receiving a full blown Visual Studio 2010 Ultimate MSDN Subscription here is what I am asking and a few guidelines:

  1. Reply and answer threads that are posted in the MSDN and/or TechNet Microsoft forums.  I am expecting valid and honest replies that are helpful and bring value to the thread.  I will be spot checking to verify this, so if I see meaningless replies you will be removed from the contest.
  2. A Live account is required in order to participate and reply to threads in the forums, so you will have to create one if you don’t already have one
  3. Send me an email with the subject ‘MSDN Subscription Giveaway #1’ to denglishbi@gmail.com and include in the email your forum Display Name that you are using, I should be able to search and find your account when I use the browse forum users functionality (so test and make sure you show up before sending me the email, I will not validate this until contest deadline has been reached).  Subject line must be exact or you will not be included in the drawing and you must provide your forum display name or I will delete your email
  4. For each 10 points you earn in the forums you will receive one entry into the drawing for the subscription – 2 pts for a reply, 10 pts for a reply marked as answer, 5 pts for a reply marked as helpful
  5. When does this start? July 7 at 12:00 a.m. (I am on CST time if that matters for anything)
  6. When does it end? July 31 at 11:59 p.m.
  7. How will the winner be chosen? I will go through and create the entries to place in a hat.  Based on this I will pick a name and that person once I double verify their information in the forums will be the winner of the subscription.
  8. When will the winner be announced? Well that depends on the number of entries I receive, but I would like to announce the winner within 2 to 3 days of contest end date, so hopefully no later than July 16.  I will also update this posting with the winner of the contest with a link to their Profile in the forums.
  9. Who is eligible? Well lets keep this to people that are not partners, Microsoft employees, and MVPs.  I want to make this available to people that do not have access to the software currently and really need this for development purposes that would actually benefit from having this subscription. So please only send me the email if you are truly going to participate in the forums and do not currently have access to an MSDN subscription
  10. How soon will you received the subscription information? As soon as you reply that you received my email congratulation email.  Once I receive confirmation I can email you the subscription code and/or send the NOT FOR RESALE voucher in the mail

So how does that sound?  I want to try and get more people involved in the community support area and give the forums more exposure to people that might not typically use this area.  I am a big fan of the forums and in helping others and this is actually a big step to help you out on your MVP journey.  Community support is extremely important and valued very high and I would like to reward and commend others that help out.  So what are you waiting for?  If you are not registered yet, take the time now to do so and starting tomorrow start helping out in the forums.

And just because I only have this setup for a week does not mean that you need to stop utilizing the forums area…of course that is completely up to you.  I find it very rewarding to be able to offer some advice to people, so hopefully you will as well.

Good luck!

Update (7/7/2010): I modified the points needed to earn an entry from 50 to 10. I originally was going to have this contest go for a longer time frame and switched it to a week. Based on people marking replies as an answer or helpful I felt that 50 points was a bit aggressive. With the entry level set at 10 that is less than 1 reply per day and you can reply more than once per thread.  Don’t be afraid to offer your assistance in the thread, your advice and direction could very well be the solution or input that another person needs to solve their issue or answer their question.

Update (7/13/2010): Since I only had two inquiries about the giveaway and they were both Microsoft partners (which are not eligible) I have decided to extend this giveaway out through the end of the month.  You only need to reply to 5 threads to earn 10 points for an entry. Answer someones thread and you get an entry.  Come on, how easy can that be.  From the activity so far your odds of winning are going to be very high.  What are you waiting for, get in on your chance to win a $12K MSDN Subscription!

Posted in News and politics | Leave a Comment »

Officially a SQL Server MVP!

Posted by denglishbi on July 2, 2010

This afternoon I got the long awaited email that I have been patiently waiting for…”Congratulations 2010 Microsoft MVP!”  I was so thrilled that I had no idea what to do.  Should I scream? Give someone a high five? Do a cartwheel? Maybe a flip?  I hopped on twitter and sent out a quick message to release some of my excitement

image

I then sent out an email to everyone at Superior Consulting Services where I just started this week.  Everything has been a little hectic lately wrapping up work with a client, taking a week off (no idea what to do), starting a new job, and now getting the SQL Server MVP status.

I want to thank my family for being understanding, patient, and for accepting my intense passion for Microsoft technology.  It is an honor to receive the recognition for all ofimage the hard work and efforts that I have done over the years with blogging, presentations, videos, tweeting, forum support, and help running the local PASSMN user group.

So where do I go from here?  Only time will tell.  I am moving on to a very exciting project with a client after the holiday weekend, so looking forward to that.  We have Itzik Ben-Gan presenting at the user group this month PASSMN July 20, 2010 Monthly Meeting and this is a major launch year for Microsoft.  It just seems like all of the stars are aligning and everything is falling into place.  I have a real good feeling about what is in store and I know that we have a lot more to look forward to with the next release of SQL Server, so I can’t wait for the CTPs to start coming out.

Once again, I am very grateful for the recognition and looking forward to taking it to the next level.  Hopefully one of my four session submissions to the national PASS 2010 Summit will be accepted, but before that we need to get ready for our local annual PASSMN SQL Summit 2010.

You can search for MVPs here ‘MVP Search’ (I am on page 1 if you filter for SQL Server and United States – search results) and if you want you can check out my profile here SQL Server MVP ‘Dan English’.

Posted in SQL Server | 11 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 91 other followers