Update #ProjectServer lookup tables using #PowerShell #PS2010 #SP2010 #MSProject #PS2013

August 29, 2012 at 10:30 am | Posted in Administration, Configuration, Customisation, Functionality, PowerShell, PSI | 4 Comments
Tags: , , , ,

I recently wrote a PowerShell script that updates a Project Server lookup table with values from a text file. The script is available to download, I have uploaded it to the Microsoft Script Center below:


****Update – this also works for Project Server 2013****

This post will detail what needs to be updated in the script, the text file and a walkthrough of using the script.

Firstly lets take a look at the text file containing the values that I want to add to my lookup table. Worthing noting is that it can either be an empty lookup table or a lookup table that already contains values. The values I am going to add can be see below:


This script does assume that the lookup table values are all level 1.

As mentioned there are variables / lines in the script that will need to be updated, these can be seen below:

  • $values = Get-Content “C:\Lookupvaluestoadd.txt”  – This line will need to be updated to reference the filename and path for the text file that contains the lookup table values.
  • $lookupTablename = “Test Lookup Table” – This variable will need to be updated to reference the lookup table you wish to update
  • $svcPSProxy = New-WebServiceProxy -uri “http://vm353/pwa/_vti_bin/PSI/LookupTable.asmx?wsdl” – This line will need to be updated to reference your Project Server PWA URL, replace http://vm353/pwa with your PWA URL

The following methods will need to be updated to reference to correct local ID, the script currently uses 1033:

  • ReadLookupTables
  • ReadLookupTablesbyUids (used twice)
  • UpdateLookupTables

Now that we have the lookup table values ready in the text file and the lookup table exists, in this example the lookup table is call “Test Lookup Table”, we are ready to execute the script. Also make sure the lookup table is checked in.

As you can see below, currently the “Test Lookup Table” is empty:


After closing the lookup table (the lookup table needs to be checked in), we will execute the PowerShell script to insert the values from the text file. Open Windows PowerShell with a Project Server administrator account and navigate to the location of the script, in this case it is on the desktop:


Press enter to execute:


The script states that everything was successful, now see the lookup table:


I have created some other PowerShell scripts working with lookup tables, I will upload these in the next week or so.

As always, this script is provided as is with no warranties etc. use at your own risk and test on a test environment before using on a production environment.

#SharePoint 2007 / 2010 and #ProjectServer 2007/ 2010 #BI Reporting made easy #SP2010 #PS2010 #businessintelligence #SQL

August 22, 2012 at 12:36 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting, T-SQL | 1 Comment
Tags: , , , , , ,

Following from my previous post where I briefly introduced a new product from CPS called SharePoint ReportLink, as promised, this post demonstrates the ease of use and ease of reporting on SharePoint data when using SharePoint ReportLink.

For the purpose of this blog post, I have already deployed the solution to my test VM. Worth noting at this point, the version I have installed / used for this blog post is not the final release so you will see CPS DataMart referenced as this was the original product name. Functionally the version I have installed on my VM is probably 99% identical. Firstly I need to activate the feature on the site collection, in this case it is one of my PWA site collections. Once the feature is activated a new heading / menu appears on the Site Settings page for that site collection:


You will see CPS DataMart with four links, Application Settings, List Configuration, List Column Mappings and Logging. We will take a look at each page in turn, firstly the Application Settings page:


I didn’t set any database server or database name, as this is has been activated against a PWA site collection the feature automatically populates the database server with the Project Server Reporting SQL Server name and the name of the Project Server reporting database – pretty cool! You can of course update the settings to use a different database server and custom database. If this feature is activated against a normal SharePoint site collection you will see the following on the Application Settings page:


As you can see there is also a logging level, by default this is set to Information but can be updated:


The SharePoint ReportLink will log events to a custom list to ease diagnostics.

Let’s take a look at the next two page pages together, List Configuration and List Column Mappings.

List Configuration:


List Column Mappings:


As you can see these are just standard SharePoint lists, at this point you need to know what lists and what columns on those list you wish to report on. For the purpose of this blog post I will report on the Issues, Risks and Lessons Learned from my projects sites:


See below the populated / configured List Configuration and List Column Mapping lists:



Once the CPS SharePoint ReportLink is activated and configured on the site collection, any items on the lists configured will be synchronised to the SharePoint ReportLink database tables in real time. This includes, new items created, existing items modified and items deleted from those configured lists across all webs within that site collection.

After setting the List Column Mappings, custom tables will be created in the specified SharePoint ReportLink database, in this case the Project Server Reporting database:



Issues list columns:


If those lists already contain items, after setting the column mappings the existing SharePoint items will be created in the SharePoint ReportLink tables.

I then created a few new items on the lists (on PM 1 and PM 2 sites, other items already existed), as you can see from below, the data is available in the custom tables in the reporting database:



Lessons Learned:


As you can see, you can quickly and easily access the SharePoint list data from multiple sites using T-SQL. Also notice the Project_UID, this enables you to easily link the SharePoint ReportLink data to your Project Server data. If SharePoint ReportLink reads data from a non Project Server project site, the Project_UID column will contain an empty GUID.

The final menu we haven’t seen yet is the logging list, this can be seen below:


The warnings are logged above as the new custom list, Lessons Learned, doesn’t exist on the majority of my test project sites.

Now that the SharePoint data is easily accessible using T-SQL, reporting could not be simpler using your preferred reporting tool, SSRS, Excel etc. SharePoint BI made easy!

In case you didn’t see the first post, I have included links to the product below:



I will follow up with a third post and create an SSRS report including the SharePoint ReportLink data in the near future.

#SharePoint 2007 / 2010 Reporting #SP2010 #PS2010 #ProjectServer #BI #businessintelligence #SQL

August 21, 2012 at 11:00 am | Posted in Add-on, Customisation, Functionality, Information, T-SQL | 5 Comments
Tags: , , , , , ,

A quick blog post to highlight a new product that CPS have released. The product is called SharePoint ReportLink. Details for the product can be found below:




This product enables end users to easily create reports on SharePoint data using their favourite reporting technology without having to use the SharePoint web services. The product is an easily installable SharePoint solution (WSP file). Once the solution is installed, configuration is very simple too, users specify what lists and fields in those lists they wish to report on. The data is then available in real time in a specified reporting database. This enables users to create reports on SharePoint data using T-SQL to the custom reporting database rather having to use the SharePoint web services to access the data. This is particularly useful for Project Server programme level reports where you want to read data from multiple project sites in the one report.

Look out for a full blog post very soon where I will demo the configuration and usage to show you how powerful and simple to use this product is.

#SharePoint 2010 Missing Server side dependencies Central Admin Help site orphaned #SP2010 #PS2010 #PowerShell

August 9, 2012 at 12:23 pm | Posted in Administration, Fixes, Information, Issue, PowerShell, Workarounds | 1 Comment
Tags: , , , ,

Whilst working on a client site today to deploy the June 2012 CU to their SharePoint / Project Server 2010 farm I came across an issue in the pre-update health check. The issue was identified by the SharePoint health analyser as Missing server side dependencies. This particular missing dependency was an orphaned site in the Central Administration content database. The Help site was present in the central admin content database but not in the farm configuration. The exact error for search engines is below:

[SiteOrphan] Database [EPM2010_AdminContentDB] contains a site (Id = [d2df5cfd-26fc-419f-bd23-07b61d805bbd], Url = [/sites/Help]) that is not found in the site map. Consider detach and reattach the database. The orphaned sites could cause upgrade failures. Try detach and reattach the database which contains the orphaned sites. Restart upgrade if necessary.

missing server side

The reason why this site was not created successfully or potentially orphaned after the install is unknown.

Anyway enough about the error, what about the fix? As the explanation suggestions the first option I tried was to detach and reattach the content database. I was a bit caution of doing this as it was the farm central admin content database, so I tested this on my own farm first.

Running the Dismount-SPContentdatabase command failed with the error below, probably because this content database is attached to an SPAdministrationWebApplication web application rather than a normal SPWebApplication.

error dismount

Next I tried combining the Get-SPContentDatabase command and piping this to the Dismount command and that worked successfully. The example command is below:

get-spcontentdatabase -webapplication http://vm353:15000 | dismount-SPcontentdatabase

Then I ran the normal Mount-SPContentDatabase command to attach the same content database that was detached above:

Mount-SPContentdatabase -name EPM2010_AdminContentDB -DatabaseServer vm353 -Webapplication http://vm353:15000

The Central admin site opened (good start Smile), I then accessed the http://vm353:15000/sites/help site which prompted me to set the template. After setting the template for the site I was able to access the site without any issues. When the farm was installed I think part of the help site provisioning failed to set the site template for some unknown reason which caused the site creation to be incomplete. I then reanalysed the missing server side dependencies error and the issue was resolved, the June 2012 CU also deployed successfully.

I would strongly recommend having full SQL database backups of all farm databases and SharePoint server snapshots / images before attempting this fix.

Hopefully this will help out anyone else who comes across this error.

Blog at WordPress.com.
Entries and comments feeds.