Dan English's BI Blog

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

Archive for 2010

PowerPivot Book Reviews

Posted by denglishbi on August 2, 2010

This past month I purchased both of the PowerPivot books that were available.  The first one I got was the Professional Microsoft PowerPivot for Excel and SharePoint (wow, that was a mouthful).  I would have to say that this book is geared more toward the IT Professional where it does a great job covering the installation setup on the server side, managing and troubleshooting techniques, and going over some of the development.  While I was going through the exercises in the book I did do a few blog postings in regards to some of my experiences with the PowerPivot product

The other part that I really like about this product was the additional insights that were provided by the product team members talking about how the product came to life and the different paths that were explored initially (originally MS Access was looked at…phew).  The exercises that were provided were helpful and the troubleshooting section was very useful, especially coming from the IT side of the world.  All-in-all I would highly recommend this book to the IT Pro and would give this book 5 out of 5 stars – image.  Definitely worth the $$$.

Next on the list came the book from Mr. Excel (Bill Jelen).  I have to admit that I had not heard of him until the great Alpha Geek Challenge came about that Microsoft launched promoting the PowerPivot product.  I tried to get involved in Round 2, but ended up in Round 3 going head-to-head with Mr. Excel.

This book is definitely geared towards the Excel user and that was expected.  I have not read any of Mr. Excel’s books, so I wasn’t used to his style of writing.  There are some comments made in the book about ‘blowing a gasket’, ‘insane things that come out of Redmond’, ‘going hack off the people in Europe’ (not sure what that even means), etc. that I did not find appropriate in a formal published book.  These comments would have been better suited for a blog posting possibly, but not for a book that I might want to recommend or keep on my bookshelf.  The book does a good job explaining the differences between Excel and PowerPivot, going over all of the functions available to you, providing some examples to go through, and providing advice for publishing workbooks (formatting and look-and-feel).  At the very end it talks briefly about the SharePoint side of the world, but at a very high level.  I did get a good laugh when it stated ‘Build a PowerPoint pivot table’ (easy mistake and the names of products now are really easy to mix up).  If you are an Excel user and are looking to make the switch to PowerPivot then this would be a good reference book.  The price is right and I would have to give this book 3 out of 5 stars – image

I know that there are a few more PowerPivot books coming out and I am definitely looking forward to taking a look at them once they are available

Posted in PowerPivot | Tagged: , | 1 Comment »

MSDN Giveaway #1 Winner Announcement

Posted by denglishbi on August 1, 2010

Last month I posted a contest to giveaway a MSDN Visual Studio 2010 Ultimate Subscription – MSDN Subscription Giveaway #1.  Today I am officially announcing that the winner of the contest is Abhishek Gandhi (AGandhi)!  Not only did they meet the requirements to get 10 points in the forums, they reached my original setting of 50 and even received more by getting a total of 71 points in a matter of a couple of weeks.

Abhishek Gandhi’s Profile   image

I want to thank everyone that inquired and participated in the contest.  I really like how people were willing to get involved and help out in the forums.

I have decided to contribute one of my other subscriptions to another contest going on for unemployed developers here – While you don’t get a ‘Free Lunch’, you will get your just desserts… along with a bunch of other MVPs and sponsors.  I will have to think of something else to do with my other subscription, maybe a PASSMN giveaway…

Congratulations Abhishek, your prize is on the way!  Keep up the great work in the forums!

Posted in News and politics | Leave a Comment »

Reporting Services Bing Map – Unable to connect to the remote server

Posted by denglishbi on August 1, 2010

A new feature that is now available in Reporting Services 2008 R2 is the Map report item.  Along with this report item is the ability to display Bing maps.  This is a very neat feature and a very interesting way to visually display information to end-users leveraging their data.  Here is an example that Robert Bruckner put together last year that he made available – RS Maps with Spatial Data and Bing Maps

Adv Works Cust LA Map 

Once you have this created within BIDS or Report Builder 3.0 you are going to want to share this with your end-users, so you will need to deploy this to the server.  Once you deploy it to the server you test it out and you end up with this (not in all cases, but this could potentially happen in your environment)

Adv Works Bing Map Error

Bing Map error: “Unable to connect to the remote server

Hmmm, what is going on?  The report seems to be running, but the map is not being displayed.  After taking a look around it turned out that a proxy was being used, so some additional configurations were needed on the Report Server in order for the Bing map integration to work – Proxy Settings for Map Controls with Bing Maps.

So I went into the Report Server web.config file, which is located in the ReportServer install folder – see image below for the path) and added the proxy information (NOTE: always make sure you make a backup copy of a config file before making a change and utilize a basic text editor like notepad, not an RTF editor like wordpad).

SSRS web.config file

         <defaultProxy enabled=”true” >
             <proxy bypassonlocal=”True” proxyaddress=”http://myproxy:port”  />

Once I had this information added (you will need to replace the ‘myproxy:port: information with the valid information for your environment) I went back and refreshed the report and we were back in business.  No reboot or restarting of the service needed, the Bing map was able to be displayed and everything was back to normal.

Posted in Reporting Services | Tagged: , | 2 Comments »

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.


“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:


“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.



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.


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


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


  • 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


  • 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)


  • 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


  • 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


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


  • 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.


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


  • 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.


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


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


  • 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: , | 2 Comments »

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


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 »

PASSMN July 20, 2010 Monthly Meeting

Posted by denglishbi on June 28, 2010

The next Minnesota SQL Server User Group meeting is on Tuesday, July 20.  This month we have a special speaker, Itzik Ben-Gan, who will be presenting on Query Tuning Tips.  The meeting time has also been modified to accommodate Itzik’s schedule and we will be starting at a later time of 5 p.m.


In order to RSVP for the event you will need to login to the national PASS site and click on the RSVP link.  The sponsor for next month’s event is Digineer.


PASSMN July 2010 Meeting – Query Tuning Tips


5:00 –5:15 : Announcements
5:15 –5:25 : Sponsor
5:25 –6:45 : Query Tuning Tips (Itzik)
6:45 –7:00 : Survey Collection and Giveaways


Query Tuning Tips (presented by Itzik Ben-Gan) – Given a SQL Server querying problem there’s much that you can do to enable a good performing solution. Tuning involves arranging an optimal physical environment, e.g., by creating supporting indexes, as well as writing the query in a way that it would get an optimal execution plan. Many factors can affect the efficiency of the solution including the availability of indexes, data distribution and density, and others. In different scenarios, a different solution could be the most efficient for the same querying problem. Query tuning could be considered an art. This session will provide various tips to do efficient query tuning and demonstrate those through specific tuning examples.

We will have some copies of Itzik’s books as giveaways as wellHot  So not only can you come and hear him talk in person, but you will have a chance to take him home as wellSmile

Hope to see you there! 

Also, don’t forget, if you are planning on attend the national PASS 2010 Summit that there are some additional discounts that you can take advantage of that expire at the end of June – SQL PASS 2010 Summit – June Discounts.

Posted in Training | Tagged: | Leave a Comment »

SQL PASS 2010 Summit – June Discounts

Posted by denglishbi on June 25, 2010

Are you planning on going to the PASS 2010 Summit conference in November but haven’t registered yet? Well hurry up because two major discounts are running out this month.  The current discounted price of $1,395 runs out on June 30 along with an additional $150 code that was part of the 24 Hours of PASS – 24HRR2.

So what are you waiting for?  Head over and sign up now to get in on the deal – http://www.sqlpass.org/summit/na2010/.  They also have a deal going on that once the session list is released next month that you can get your money back if you are not satisfied with the lineup – satisfaction guaranteed.

If you need any assistance in pitching this great learning opportunity to your management or have to fill out a justification form to get approval check out this great list – ROI Justification/Justify your Attendance.

PASS Community Summit 2009

And who knows, maybe I will see you there.

Posted in Training | Leave a Comment »

Training Course: Presenting Data and Information by Edward Tufte

Posted by denglishbi on June 24, 2010

Found out last week from a fellow PASSMN member that Edward Tufte will be coming to Minneapolis (and Chicago) to do a training course next month on July 19.

Topics to be covered in course:

  • fundamental strategies of analytical design
  • evaluating evidence used in presentations
  • statistical data: tables, graphics, and semi-graphics
  • business, scientific, research, and financial presentations
  • complexity and clarity
  • effective presentations: on paper and in person
  • interface design
  • use of PowerPoint, video, overheads, and handouts
  • multi-media, internet, and websites
  • credibility of presentations
  • animation and scientific visualizations
  • many practical examples

This course will be held at the following location in Minneapolis:

Minneapolis Marriott City Center
30 South 7th Street
Minneapolis, MN 55402

Along with the training you also get copies of four books


For more information about this course that is taught by Edward Tufte check out the following link – http://www.edwardtufte.com/tufte/courses.

Posted in Training | Leave a Comment »