Dan English's BI Blog

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

Archive for August, 2010

SQLSaturday #58 – Minnesota 2010, Oct 29

Posted by denglishbi on August 13, 2010

Hello Twin Cities, are you ready for a free all day non-stop SQL Server event?  Well guess what, you are in luck then because we are going to be having our 2nd annual PASSMN event and this year it will be under the ever famous SQLSaturday label.  The official name is now SQLSaturday #5 – Minnesota 2010 and it will be held on Friday, October 29.  Did I do a typo there?  Nope, that is right, a SQLSaturday on a Friday.  We are a little crazy up here in the north and like to change things up a bit.

So what do you need to know?  Here is some of the key information to get you started:

The web site is active and ready for business.  We are actively looking for sponsors for the event and speakers.  Please visit the site and take a look.  For sponsorship information we have three levels designated – Bronze, Silver, and Gold and the details can be found here – Sponsorship Plan.  We are also looking for volunteers, so during the registration submission you will see a spot on the form to let us know if you are interested in helping out at the event (like registration, directing traffic, collecting evaluations, coordinating the event, etc.).

So head on over to the site and let’s get this party started!  I hope to see you there and feel free to reach out to me if you have any questions and there is a contact us email address for this event as well – sqlsaturday58@sqlsaturday.com.

Please spread the word – SQLSaturday #58 – Minnesota 2010 – let’s make this a fantastical SQL event!

Posted in Training | Tagged: | 1 Comment »

24 Hours of PASS Fall 2010

Posted by denglishbi on August 13, 2010

It’s coming back, are you ready for 24 hours of non-stop SQL Server training? Did I forget to mention that it is free?

There will be four tracks: BI | DBA | Dev | PD image

Here is the Business Intelligence line-up: BI Track

Don’t miss out on your chance to join in on the all day event – Session Listings by Track

Posted in Training | Tagged: | Leave a Comment »

PowerPivot Data Refresh with Excel Source Data

Posted by denglishbi on August 11, 2010

So wouldn’t you know, one of the source files that we get to load into PowerPivot is Excel.  Initially the data was being loaded into the PowerPivot Window using either the Linked table or the Copy & Paste options.  I immediately decided to change this option to help streamline the process and decided to use the ‘Excel File’ import option under the ‘From Other Sources’.


The reason that I did this was to be able to quickly refresh them by using the ‘Refresh’ option in the PowerPivot Window and to also be able to eventually leverage the scheduled data refresh option in SharePoint.


The one thing about this setup was that I had to have a fairly clean file to start with, meaning that the data was in a pretty standard format with no special headers or cells in the file, just column headers and data.  It didn’t matter if the data was starting out in cell A1 because the import wizard could detect this and would properly grab the data even if it started in B3.

image    image

What I would run into would be some typical Excel applications that had some crazy layouts with tons of worksheets and formulas all over the place.  These files typically had some data in the top left-hand corner that was helping to drive the entire worksheet.  Now for these worksheets I had to go ahead and setup a named range in the Excel worksheet.


Once this was setup we could then reference this named range when importing the Excel data into PowerPivot.  Now in my example here I wouldn’t have had to have done this, but this is just showing you how you go about doing this type of setup.


Now when you do the import you will be able to reference the data like such:


You will just need to make sure that if the data in the source file changes that the named range gets updated appropriately so that you can refresh the PowerPivot data without any issues.  The other thing that we ended up doing was making sure that the source data was being stored in a common location, which means not on our workstations.  The location we picked was a SharePoint document library.  We then accessed the data utilizing the SharePoint WebDAV protocol meaning we were using UNC paths out to the SharePoint library like such – \\ServerName\DavWWWRoot\Site\DocumentLibrary\ExcelData.xlsx.

Now that you have all of the data loaded into PowerPivot referencing a shared location the next step would be to create some PowerPivot PivotCharts and PivotTables (if you are using PowerPivot reporting functionality).  Once this is done you will want to then upload the data to SharePoint into a PowerPivot Gallery to share with the end-users.  The PowerPivot Gallery is also where you get the option to schedule data refreshes and this is what we are after.


The first time I setup the scheduled data refresh I ended up getting the following failure message:

The provider ‘Microsoft.ACE.OLEDB.12.0’ is not registered. The following system error occurred: Class not registered A connection could not be made to the data source with the DataSourceID of ‘<guid>’, Name of ‘<PowerPivot ConnectionName>’. An error occurred while processing the ‘<PowerPivot TableName>’ table. The operation has been cancelled.

I had received a similar error message when I tried to load Excel data into SQL Server on my x64 workstation.  To resolve that issue I had to install the ‘2007 Office System Driver: Data Connectivity Components’.  I tried this on the server and still ended up with the same error message (slight difference between SQL Server Management Studio, 32-bit, and SharePoint Server, 64-bit).  Thanks to some help from some external resources I realized that I needed to install a x64 version of the drivers, which meant installing ‘Microsoft Access Database Engine 2010 Redistributable’.  But when I went to install this it told me that I had to uninstall Office 32-bit software.  The server that I was using was setup in a Sandbox (single server) setup for development purposes, so all of the software was installed and this included 32-bit version of the Office 2010 suite.  I went ahead and uninstalled the data components that I just installed, Office 2010, and the PowerPivot add-in.  Once that was done I was able to install the x64 version of the 2010 components and I initially got this error message:

OLE DB or ODBC error: Failure creating file.; 3436. A connection could not be made to the data source with the DataSourceID of ‘<guid>’, Name of ‘<PowerPivot ConnectionName>’. An error occurred while processing the ‘<PowerPivot TableName>’ table. The operation has been cancelled.

After some quick research I ended up on Dave Wickert’s site (if only my initial search would have brought me here first, or if I just recalled reading this posting earlier this year…) Working with the 2010 Office ACE provider.  This told me that I needed the Desktop Experience feature enabled on the server.  Unfortunately this was already enabled since this server was setup in a Sandbox setup for development purposes.  After a little more testing, reloading the PowerPivot file in the Gallery, and running the data refresh again I ended up with a successful data refresh.  Ahhh…..relief….satisfaction….scheduled Excel data refreshes into PowerPivot:) Excel-to-Excel, sweet harmony.

Posted in PowerPivot | Tagged: , , | 7 Comments »

PASSMN August 17, 2010 Monthly Meeting

Posted by denglishbi on August 11, 2010

The next Minnesota SQL Server User Group meeting is on Tuesday, August 17.  This month we will be doing the 3rd annual ‘Ask the Experts Panel’.  Unfortunately I will not be able to attend, but I will be thinking about everyone while I am up north at the resort spending some quality time with my family.  One of the special giveaways this month will be a copy of the PASS Summit 2009 Conference DVDs!


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 this month’s meeting is Red Gate Software.


PASSMN August 2010 Meeting – Ask the Experts Panel


3:00 –3:15 : Announcements
3:15 –3:25 : Sponsor
3:25 –5:00 : Ask The Experts Panel

5:00 –5:15 : Survey Collection and Giveaways


Ask the Experts Panel – After the popularity of last years “Ask the Experts”, we decided to bring back the forum this year. PASS members will have the opportunity to pose any burning questions they might have about SQL Server to our expert panel. We will round things out with a couple tips or tricks about SQL Server from each of our panel experts.

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Data Platform Technology Specialist for Microsoft. This is a role that is a pre-sales role for SQL Server. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. Compliance and configuration management are near and dear to her heart. She is an active board member and advocate of the Minnesota chapter of PASS (Professional Association for SQL Server) and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Notably, Lara has presented at Tech Ed, the PASS Summit, the Minneapolis SQL Server Launch, and delivered numerous webcasts on MSDN and TechNet. Lara authored a number of the Microsoft whitepapers and was a contributing author of “Expert SQL Server 2005 Development” (lead author Adam Machanic).

Jason Strate, Digineer Inc, is a database architect and administrator with over twelve years of experience. He is Microsoft MVP (Most Valuable Professional) for SQL Server. His experience includes design and implementation of both OLTP and OLAP solutions as well as assessment and implementation of SQL Server environments for best practices, performance, and high availability solutions. Jason is a SQL Server MCITP and participated in the development of Microsoft Certification exams for SQL Server 2008. Jason enjoys helping others in the SQL Server community and does this by presenting at technical conferences and user group meetings. Most recently, Jason has presented at the SSWUG Virtual Conferences, TechFuse, SQL Saturdays, and at PASSMN user group meetings. Jason is an active blogger with a focus on SQL Server and related technologies.

Eric Strom is a Database Consultant at RBA Consulting and is a member of the Minnesota PASS Board. He has been a SQL Server DBA since 2001 and specializes in performance tuning. Eric studied database theory at the University of Minnesota to earn a B.S. in Computer Science. He loves exchanging ideas with peers and is always looking for a good discussion.

Tim Plas is a Principal Consultant at Virteva, in Mpls MN. His current focus areas are operational-DBA services, storage, server virtualization, cloud computing, & providing level-3 escalation support for IT managed services. His SQL emphasis is on monitoring, management, and high availability.  Previous specialties in his 25+ years in the IT field have included Citrix/TS (app hosting), LAN’s / WAN’s / network security, DB architecture, systems architecture & design, app development, systems-level programming, & various IT mgmt roles. Tim has held a variety of MS certifications since 1994.

Stay tuned for information about our 2nd annual PASSMN SQL Summit 2010 that will be coming up in October.  We are actively wrapping up some details to leverage the SQL Saturday site and finalizing some of the arrangements for our location, Mystic Lake Casino Hotel.  The event is currently planned to be held on Friday, October 29, so save the date.  We will be actively looking for speakers and sponsors to make this event fantastical.

Posted in Training | Tagged: | Leave a Comment »

Minnesota Microsoft BI User Group 2010 Q3 Meeting – Sept 1

Posted by denglishbi on August 6, 2010

The next MN Microsoft Business Intelligence User Group has been scheduled and the agenda is set.  There will be a case study by Polaris going over their use of the Microsoft BI Stack, a talk about digital dashboarding with SharePoint 2010 and SQL Server 2008 R2, and another presentation on map visualizations with Reporting Services 2008 R2.  Tons of great content, don’t miss out on this one.

MN Microsoft BI User Group 2010 Q3 Meeting

Date: Wednesday, September 1, 2010

Check In: 2:30 PM Event Time: 3:00 PM – 6:00 PM

Location: Microsoft Event Center 8300 Norman Center Drive Suite 950 Bloomington, MN 55437

Please Register Now


  • 2:30-3:00 : Registration
  • 3:00-3:15 : Microsoft BI User Group Updates & Announcements
  • 3:15-4:00 : Leveraging the Microsoft BI Stack to provide a Digital Marketing Dashboard – Polaris Industries
  • 4:00-4:15 : Break
  • 4:15-5:00 : Digital Dashboard Using SharePoint 2010 and SQL R2 capabilities
  • 5:00-5:45 : Building Map visualization using Reporting Services 2008 R2
  • 5:45-??? : Social and Networking


Leveraging the Microsoft BI Stack to provide a Digital Marketing Dashboard – Polaris Industries (presenter – Chris Kuelbs, Polaris): Polaris Industries is a leading manufacturer of Off-Road Vehicles and Motorcycles. Polaris leverages internet marketing heavily for brand reconsideration leading to improved customer engagement. In order to effectively manage their digital (online) marketing strategy and measure return on marketing investments, Polaris embarked on an initiative to build a BI solution for their Digital Marketing efforts. This initiative is focused on understanding customer behavior, engagement funnel, campaign performance, influence, and conversion to ultimately correlate marketing investment with sales to provide return on marketing investments (ROMI). For this initiative, Polaris leveraged the Digital Marketing Dashboard Framework that uses the Microsoft BI Stack (SharePoint, Performance Point services, reporting services, analysis services, integration services). In this presentation you will get to learn about Polaris’ challenges in implementing a board digital marketing solution that integrates data from multiple external web sources and correlating it with internal sales data to provide Return on Marketing investments.

Digital Dashboard Using SharePoint 2010 and SQL R2 capabilities (presenter – Neelesh Raheja, GNet Group): In this session we will take a look at the latest and upcoming release of the Digital Marketing Dashboard Framework built using SQL Server R2, SharePoint 2010, Performance Point Services, PowerPivot and PivotViewer. The latest releases uses the entire Microsoft BI stack to bring powerful visualization capabilities such as Bing maps, Funnel reports, gauge, radar maps. These new visualizations make it easier for users to gather insights into customer behavior, campaign and online media performance, and return on marketing investments.

Building Map visualization using Reporting Services 2008 R2 (presenter – Jin Cho, Microsoft): In this session you will get an in-depth review on geospatial visualizations capabilities of Reporting Services.

  • Adding a Map to Your Report
  • Adding Data to a Map
  • Understanding the Map Viewport
  • Adding a Bing Maps Tile Layer
  • Understanding Map Layers and Map Elements
  • Understanding Map Legends, Color Scale, and Distance Scale
  • Publishing report to SharePoint for distribution

Please Register Now

Posted in Training | Leave a Comment »

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 »