Recovering from cryptic errors thrown when importing from PowerPivot

I am borrowing this title from one of the blog post from Cathy Dumas, since this post is just the extension of her blog post which she posted for resolving the error thrown while creating a SSAS tabular Model project in SSDT by importing from Powerpivot.

I started to create a SSAS Tabular Model project by importing the data model from Powerpivot sheet. As we know, for Tabular Model Project, a workspace database is created on the ssas tabular mode instance to facilitate the data load while importing the data in the project.

If you are not familiar with the configuration of the workspace database for SSAS Tabular Model Project, I would recommend you to read the following blog from Marco Russo

http://sqlblog.com/blogs/marco_russo/archive/2011/07/20/change-default-workspace-and-deployment-server-in-tabular-ssas-denali-ctp3.aspx
While creating a project by importing from Powerpivot, I received the following error message

So while researching the error, I encountered Cathy’s blog post here. And I ensured to give read permissions  to the powerpivot file to the virtual service startup account  NT Service\MSOLAP$<Name instance>  of the SSAS instance which is hosting my workspace database.

However I continued to receive the same error. I also ensured that the user with which I have started the Visual Studio has permissions to read the powerpivot file and to create database in the SSAS Instance.

In my quest of finding a solution to the problem, I checked the msmdsrv.log file located in the C:\Program Files\Microsoft SQL Server\MSAS11.<Named Instance>\OLAP\Log folder.

In the Log file I saw the following error message

(9/8/2012 7:53:04 PM) Message: The file ‘\\?\c:\users\<username>\documents\visual studio 2010\Projects\<project name>\Model.abf’ could not be opened. Please check the file for permissions or see if other applications locked it. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.<Named Instance>\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210023)

The above error message made me realize that creating the ssas tabular model project by importing from powerpivot extract the abf the file from the Excel sheet and places it in the Visual Studio 2010 Project folder in My Documents, however the service account NT Service\MSOLAP$<Named Instance> will definitely not have permissions to read/write in this folder.

So I explicitly gave read/write permissions to NT service\MSOLAP$<Named Instance> to my Visual Studio Project Folder in My Documents following which I was able to resolve the error message.

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer