Project Server 2010 - Configure Excel Data Connections

I have seen quite frequent postings related to the excel services configuration, but the good thing is that Microsoft is supporting the Project Server 2010 the way they never done it before. Details postings related to every issue can be found on TechNet, MVP blogs etc. to help users.

Few days back I came across this issue for which I was unable to find any specific solution on Google. It was straight forward though, but still worth posting.

You will face this if you create a excel report using one of the cubes and try to open the report in your browser and refresh the data. There are some very useful articles in TechNet available to configure the excel services to work with Project Server data. Following the steps explained in that article will let you open the built-in reports and data connections.

But when you configure your cube to be build, the system will save the Cubes on a predefine location i.e.: within Business Intelligence -> Data Connection, a new folder will be created <server name-cube name> to save the cube. And when you use cube to create report in excel and publish to the web, the system will decline to authorize the data connection when refresh because the cube location is not defined in the Trusted Data Connection library.

The only location you define following the article is the OOB connection library. Any new data connection you create, you also have to define in the trusted data connection library to enable to refresh the data.

Following are the screen shots to explain the issue.

Fig-1 shows the error:

“The data connection path in the workbook points to a file on the local drive or is an invalid URL…..”

Fig -2: shows the folder system created to store the Cubes. You will find the difference between the URLs if you verify this location with the location you have saved in the trusted data connection library.

Fig -3: If you click on the folder, you will see all the cubes created. The modified date shows the latest Cube built date.

Fig -4: Define the path of the cube folder in the trusted connection.

Fig-1

Fig -2

Fig -3

Fig -4

Conclusion: the solution is pretty simple, for every custom data connection – you have to define the location in the trusted connection library.

Read More

Project Server 2010/2007 Permission Settings

Assuming yourself at client side, finalizing your PWA configurations including Group permissions and planning to sit with the client to go through the PWA home page - and just found that the end user being part of the Project Manager group can also be able to delete any item from the custom list, able to change the web part settings, able to change the PWA theme and so on.

Now this will leave you wondering that how this is possible, and you might go through your Group/Category settings agains to verify the permissions. Actually apart from Group/Category permission, you also have to customize the PWA site permission to avoid any such situation. When you provision the Project Web App site, the default PWA site permission settings grant way too much permission to a user which in most cases are not acceptable. The groups permission settings allows user to use the PWA features, few of them are such as:

• Accessing Projects
• Accessing Resources
• Accessing Views
• Administrative settings

Now as stated above, after provisioning the PWA site, Project Server creates four levels of permissions for the PWA site:

• Web Administrators (Microsoft Office Project Server)
• Project Managers (Microsoft Office Project Server)
• Team Members (Microsoft Office Project Server)
• Readers (Microsoft Office Project Server)

Do not confuse this Project Manager group here with the Project Manager groups available in server settings of your PWA. This Project Manager group include all the users:

• who have the permission to save project OR
• permission to publish the project on project server.

which means that if you have configured your Portfolio Manager Group/Team lead group to have any one of the above permission, they will be part of this permission level. So basically we have to customize the permission level of Project Manager Group and Team Members Groups.

To resolve the issue, we are simply going to set the permissions of the Project Managers (Microsoft Office Project Server) and Team Members (Microsoft Office Project Server) permission levels within the PWA Root site to be equal to that of the Readers (Microsoft Office Project Server) permission level. To do this:

1. Log in to PWA as an Administrator
2. Expand the Site Actions menu by clicking on it
3. Click Site Settings

    4.   On the Site Settings page, under Users and Permissions, click Site Permissions

    5.   On the Permissions: Project Web Access page, click Permission Levels

You should now see the Permission Levels page. You should see thirteen Permission Levels listed — the four i have described earlier, and the standard SharePoint ones (Full Control, Design, Contribute, Read, Limited Access, View Only, Approve, Manage Hierarchy, and Restricted Read).

Click on the Project Managers (Microsoft Office Project Server) permission level, and uncheck the permission as per your requirement, such as:

• Manage Lists
• Overide Checkout
• Delete items
• Manage permissions
• create subsites
• Apply theme and borders
• Manage Alerts
• Create Groups, etc.

Click Submit, which will return you to the Permission Levels page. Now, click on the Team Members (Microsoft Office Project Server) permission level, and uncheck the not required permissions as above.

Removing these permissions prevents non-admins from altering the look, structure, or content of pages and etc. within PWA. We also prevent them from altering lists, discussion boards, and document libraries in PWA. Note that changes to these List permissions do not affect the ability to link Tasks to Workspace Items (documents, issues, deliverables, or risks); this behavior is controlled by the Create Object Links Category permission.




Read More

Project Server 2010 Version after OCT CU

Since its been a while a Project Server 2010 is released, and Cumilative updates for the product are also been released. You might come across a situation where you want to make sure if your Project Server version is the latest version or not.

Check the following locations for the Microsoft.Office.Server.dll to determine the version. If the version is 14.0.5128.5000 or greater, the October CU, which apparently is the latest hot fix of Project Server 2010 so far, is applied.

• C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.Office.Server.dll
• C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Server\14.0.0.0__71e9bce111e9429c\Microsoft.Office.Server.dll

Read More

Project Server 2010: October Cumulative Update now re-released!

This is just to announce that the new CU of Project Server 2010 is now re-released. See the revised article related to the CU: http://blogs.technet.com/b/projectadministration/archive/2010/10/27/microsoft-project-server-and-sharepoint-2007-and-2010-october-cu-2010-are-live.aspx.

So all the Project Server consultants/administrators, visit the following website to read the details and download the Update:
http://technet.microsoft.com/en-us/projectserver/gg176680.aspx

Ref: http://blogs.msdn.com/b/brismith/archive/2010/11/18/project-server-2010-october-cumulative-update-now-re-released.aspx

Read More

Migrating Content Data from Project Server 2007 to Project Server 2010

Recently i have migrated Project Server 2007 data to Project Server 2010. Restore and Backup of the following 4 Project Web App 2007 databases had restored all the Project data, custom fields, views and etc.

• Project Server Archive
• Project Server Draft
• Project Server Published
• Project Server Reporting

I have also had to migrate the content data from Project Web App, such as:

• lists
• folders
• list items (including files)

The migration of PWA artifacts is something that’s perhaps more complex than it should be. And in almost all the cases the requirement is to migrate the content without modifying the date and author details ( the other case is quite easy that moving the content from one site to another without being worried about the date/author details). I discovered that there are a few ways to accomplish this task.  You can migrate a single list from Project Server 2007 to Project Server 2010 using one of the two methods:

1. Migrate a single list from Project Server 2007 to 2010 using the detach PWA Content database method
2. Migrate a single list from Project Server 2007 to 20110 using PowerShell

The limitation with the first option is that if there is any custom branding applied to your source PWA 2007 site, the migration will bring that branding to your target PWA 2010 site as well. Which in my case was not requried, so we will discuss the second option in detail. I would also like to introduce this very handy tool i have found after bit of a googling over the net, it would ease your life alot. The tool is  developed by Chris O’Brien to move the content from one farm to another, its bascially using the stsadm import/export commands to do the job, but its little tricky when its come to moving the data from Project Server 2007 to Project Server 2010. if its the same version of Project server, it would work straight away.
The tool provides a wizard-like approach to deploying content between PWA sites. The selected content is exported using the Content Migration API (PRIME), giving a .cmp file (Content Migration Package) which can be copied to other servers. the tool must be installed locally on the server which hosts the site, so you have to install it on both your source and target servers respectively to import and export the job.

EXPORT CONTENT
1. Run the application as an administrator, choose Export and enter the URL of your PWA site.

2. Choose from the Export content tree view, right click on the selected content and click Export. You can also use Include Descendant option on the container objects such as Webs. I would prefer to select one list at a time, import it as .cmp file and then re-run the wizard to create a .cmp file of another list. The reason is that stsadm Import command breaks the .cmp file in more than one file if the size of the content exceeds 25MB. Even if the single selected list exceeds the maximum size limit (25MB) you have to opt for the non-compressed option (that we will discuss in next stage), because creating and importing multiple files gave me a hard time.

 
3. After selecting the list, click on Next to move to the next scree where we have to configure the settings before creating a export file. Do the following settings and click next.

• Export method: Export all
• Include Version: All
• include Security: All
• disable compression: select if your data is more than 25MB.

4. The next will create a *.cmp file for you. Re-run the wizard again if there are other date need to be exported.

IMPORT CONTENT


5. Once you will have all your *.cmp files with you, copy them on the target server. Prior to running the wizard to import the files you have to verify the version in SYSTEMDATA.XML file which resides within the *.cmp file else it will fail to import the content and the error dialog box with the following error will appear:

The import process failed with following error:
FatalError: The version of the package 12.0.10.0 is different from the current version this program supports, 14.0.0.0.
Debug:    at Microsoft.SharePoint.Deployment.SPDeploymentSchemaVersion.Validate(SPSite site)
at Microsoft.SharePoint.Deployment.SPImport.Run()
Progress: Import did not complete.

The import process save the current version of Sharepoint server during the process, and when you try to export the file into the different Sharepoint version, the process fails.  follow the steps to update the version number and then run export the file, repeat the same steps for all the *.cmp files.

5a. Change this CMP file to CAB, extract the file so that you can access SystemData.xml in the CAB file, and modify the version information there. Open the SystemData.xml file in a text editor, and change Version=”12.0.0.0″ to Version=”14.0.0.0″, and Build=”12.0.0.6514″ to Version=”14.0.4762.1000″.

5b. You will have to create a new CAB file again after making the changes above. go to the link: (http://www.larshederer.homepage.t-online.de/cabpack.htm) and download the cabpck14.zip. Unzip the file and run the cabpack.exe, browse and select the folder where you have your modified files are and run the cabpack. It will create a new *.CAB file for you which you can rename to *.CMP to run import.

6. Run the wizard, select the Import option and enter the URL of your PWA 2010 website in both the boxes.
(if you have created a non-compressed export data of your list, jump to the point 9 directly)

7. On the next screen, browse the *.cmp file, do the following settings and click Next:

• Retain object IDs: Keep it un-checked if you want your list to be create and configured within the PWA site.
• Include Security: All (if you dont want to modify the date/author details of the items)
• Version Options: Append
• User Info: Import all

8. The import process will execute in the next step and will open a log file for you at the end of the process. If it will be finish successfully without any errors, you can simply go to your PWA 2010 site refresh the page and will witness the changes.

9. In a case of uncompressed export, you can have a folder of files consisit of *.dat and the other configuration *.xml files.

Follow the same steps 5a and 5b to modify the version and create a *.CAB file. And then run as administrator the sharpeoint 2010 Management Shell to import the content. Run this instruction:

import -spweb -identity <PWA site URL> -path <*.CMP file path> -IncludeUserSecurity

In summary, if you have a need to just import a PWA list from an existing Sharepoint 2007 farm to a SharePoint 2010 farm, there are several ways to accomplish this task. Which method you should use will depend on the tools that you have available, and your familiarity with each of them.

Please leave your comments and queries and dont forget to rate the article,  thanks.

Read More