Dan English's BI Blog

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

Archive for August 11th, 2010

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 »