Project Online and OData refresh performance

This post is for those of you already familiar with setting up and using OData and Excel Services in your Project Online environments.

I have received some questions about why the default OData data connections does not provide the custom fields available in the PPM Online. For this reason I decided to do a little research.

1. The default OData  connections in PPM Online uses a Select Statement (meaning the fields you get a predefined and only Project standard fields)

2. By simply opening the OData connection e.g. in Notepad, you can easily remove all the field names from the “select statement”. Removing all the fields will now enable the data connection to capture all fields available including the custom fields you have created

odata 1

odata 2

3. WARNING

Having removed the select statement will slow down the performance of doing a “refresh” – especially from Excel Services in PPM Online.

In order to fix this problem, you will Again have to use the “select statement” but in a way so that only the fields needed in your report(s), are loaded and refreshed.

In my test environment I reduced the loadtime of a report by 52% after configuring the “select statement” to only include specific fields.

4. Remember

Being able to do an Excel Services “refresh” in PPM Online, is only possible when the Excel report is opened in a full page view. This means you cant refresh an Excel report if it used in the Excel Services web part.

Tagged , ,

Project 2013 exams now available!

Two brand new Microsoft Project 2013 exams are now available for consultants, users and others interested.

The first exam is “Managing Projects with Microsoft Project 2013″. This is the exam for those of you with strong experience in using Project Standard/Professional 2013. There will be no questions related to Project Server, but there will be questions related to the out-of-the-box SharePoint integration on-premise/Online.

74-343
Managing Projects with Microsoft Project 2013

The second exam is “Managing Programs and Projects with Project Server 2013″ and covers the server and client side. For this exam, it is recommended that the candidates have a solid understanding of key management concepts and terminology. Hands-on experience from configurering a Project Server 2013 environment such as custom fields, views and portfolio scenarios, are also recommended.

74-344
Managing Programs and Projects with Project Server 2013

Speaking at Tech Ed 2013 in Madrid

Join me for my session and instructor led labs when I am speaking at the European TechEd 2013 in Madrid.

The session will target IT PROS and showcase the new Project and its ease of extensibility.

Register here and read more about the conference:

http://europe.msteched.com

Update for Project Server 2013 released

The Cumulative Update package, that should have been out in February, has finally been released. This CU will act as the baseline for all future fixes meaning that you will need it in order to install e.g. the April 2013 CU.

Download the CU here: http://support.microsoft.com/kb/2768001

Issues that this hotfix package fixes

  • Assume that you upgrade Microsoft SharePoint Server 2010 to SharePoint Server 2013. In this situation, when you create a discussion post in a community, the feed notification is not displayed on your My Site page.
  • Assume that you remove a user profile picture from Active Directory Domain Services (AD DS). Then, you perform a user profile synchronization from AD DS to SharePoint Server 2013. In this situation, the user profile picture is not removed from SharePoint Server 2013.
  • Assume that you attach a SharePoint Server 2010 content database to a SharePoint Server 2013-based server. In this situation, when you upgrade the site collection on the SharePoint Server 2013-based server, picture libraries are not updated.
  • After you run a Build-To-Build in-place upgrade, accounts other than the application pool account lose the SPDataAccess and SPReadOnly roles.
  • When you perform a search on a SharePoint Server 2013-based server, you experience performance problems on the server.
  • When you configure Access Services to use Windows Azure SQL Database (formerly SQL Azure), database connections may time out too early.
  • Consider the following scenario:
    • You open a Microsoft Word 2013 document that contains a footnote reference inside a table.
    • You click Text Direction and then click Vertical in the Page Setup section on the PAGE LAYOUT tab.
    • You scroll down to the end of the document.

    In this scenario, some pages after the table may not be displayed.

  • Consider the following scenario:
    • You create a community site in SharePoint Server 2013.
    • You use the least-privileges configuration to set up the SharePoint farm.
    • You create a new discussion post on the community site.
    • You view the My Newsfeed section of the My Site page.

    In this scenario, the feed for the discussion post is not displayed in the My Newsfeed section.

  • The output cache feature does not work on a page on a SharePoint Server 2013 site. This issue occurs when the page contains a Content By Search (CBS) web part.
  • Assume that you use the Cross-Site Publishing feature to create a site on a SharePoint Server 2013-based server. When you perform a search on the site, the number of search results is greater than the expected number.
  • Consider the following scenario:
    • You open a Word document in Word 2013.
    • The Word document contains some shapes or pictures.
    • You right-click one of the shapes or pictures in the Word document.
    • You click Edit Points for the shape. Or, you point to Wrap Text and then click Edit Wrap Points for the picture.

    In this scenario, other shapes and pictures may disappear from the Word document.

  • When you run a complex query or import a large amount of data in Microsoft Access 2013, you receive the following error message:
    System Resources Exceeded

    Note This bug need registry information.

  • When you try to resize an app part on a SharePoint Server 2010 webpage, you receive a JavaScript error message. This issue occurs after you set the value of the Chrome Type property for the app part to None.
  • If you use the Chinese lunar calendar in Outlook 2013 or in an earlier version of Outlook, the Gregorian date is displayed as an incorrect lunar date. For example, the Gregorian date June 8, 2013, is displayed as lunar date 4/30 instead of 5/1.
  • Assume that you access a SharePoint site that is not in your trusted sites list. When you try to search an item on the site, you receive the following error message:
    The display template had an error. You can correct it by fixing the template or by changing the display template used in either the Web Part properties or Result Types.
  • You cannot edit a Silverlight web part on a SharePoint 2010 mode site collection in SharePoint Server 2013.
  • When you try to perform a search crawl on a list that contains many columns in SharePoint Server 2013, you receive the following error message:
    The filename or extension is too long
  • When you create a meeting on a group calendar on a SharePoint 2010 mode site, the Attendees field is not filled by a default value.
  • Assume that a SharePoint Server 2010 site contains a navigation link that points to an invalid URL. After you upgrade SharePoint Server 2010 to SharePoint Server 2013, you cannot access the site.
  • When user A creates a post that refers to user B in a different SharePoint farm, user B does not receive a notification email message.
  • When no workflow is published to a Microsoft Project Server 2013-based server, you cannot change the enterprise project type for a project through Project Web App.
  • Consider the following scenario:
    • You create a workflow in Microsoft SharePoint Designer 2013.
    • You publish the workflow, and you set the workflow to start automatically in SharePoint Server 2013.
    • You view the workflow status page (Wrkstat.aspx).

    In this scenario, the exception messages are not completely displayed on the Wrkstat.aspx page.

  • Consider the following scenario:
    • You create a site collection in Microsoft Office SharePoint Server (MOSS) 2007.
    • You create a workflow that has the Collect Data from a User task.
    • You upgrade MOSS 2007 to MOSS 2010 or MOSS 2013.
    • You receive a notification email message when the task is assigned to you.
    • You open the email message by using Microsoft Outlook and then click Open this task to open the URL of the workflow task.

    In this scenario, Internet Explorer starts. However, you receive the following error message:

    The webpage cannot be found.
  • When you apply multiple filters to two columns of a custom list, only the filter that is applied to the second column applies to list items in the custom list.
  • Assume that in a list on a SharePoint Server 2013 site, you reference an item from another list. When you try to view the item in the list, you receive the following error message:
    Index was outside the bounds of the array.
  • Consider the following scenario:
    • You add managed metadata columns that are multivalued to the content farm and to the service farm in SharePoint Server 2010.
    • You upgrade from SharePoint Server 2010 to SharePoint Server 2013.
    • You search for a value in SharePoint Server 2013.

    In this scenario, some search results are not displayed in SharePoint Server 2013 as they would be in SharePoint Server 2010.

  • When you start a workflow in SharePoint Server 2013 on folders or on large lists, you receive the following error message:
    Something went wrong. To try again, reload the page and then start the workflow.
  • Assume that you create a site that contains folders and that you save the site as a template. When you try to use the template to create a new site, you receive the following error message:
    Unexpected Error happened
  • SPBasePermission enumeration values are truncated from 64 bits to 32 bits for SharePoint sandboxed solutions.
  • You cannot start an Agave application when you open a document in Excel Web Apps.
  • Assume that you create a custom SharePoint list that contains a multivalued Person or Group field. The field does not contain any values. When you try to insert a new row into the list in Datasheet view, Internet Explorer crashes.
  • Assume that you filter data in an Access form on a non-English version of SharePoint Server 2013 server. In this situation, Full-Text Search in SQL Server does not work correctly. Specifically, the filtering operations are slow or return incomplete results.
  • When you perform a search in a SharePoint Server 2013 search center in backward compatibility mode, the out of box (OOB) site refiner returns incorrect results.
  • When you try to import a large amount of data into an Access application on a Windows Azure SQL Database (formerly SQL Azure) database, you sometimes receive a Time-out error, and you have to cancel the import process.
  • If you create an Access application that uses the Kazakh language, you cannot save the application as a package.
  • Assume that a SharePoint farm receives some requests from client computers. When the farm uses a network load balancer to distribute the requests from clients, you cannot create an Access application on a SharePoint server in the farm. Additionally, you receive an error message that states that the SharePoint server IP address is invalid.
  • Assume that a pinned task is split into two parts in Microsoft Project 2013. When you change the duration of the pinned task in Task Usage view, the duration of the task is calculated incorrectly.
  • Assume that you create two tasks that you must manually schedule in Project 2013 and then assign the same resource to both tasks. When you assign a duration of time that includes nonworking days to the tasks, the resource-leveling feature does not work correctly.
  • After you upgrade Microsoft Project Server 2010 to Project Server 2013, you cannot access document libraries and issues or risks lists in Project Server 2013.
  • Assume that you use non-ASCII characters to name a task outcome field in a task in a custom workflow. Then, you change the default value in the task outcome field. In this situation, the task outcome field continues to display the default value on the workflow status page.
  • Assume that you configure the app-only policy for a SharePoint workflow. Then, you publish the workflow to a website on which a SharePoint application is installed. In this situation, you cannot run an instance of the workflow on the website.
  • When you use Word 2013 to save a .docx file as an OpenDocument Format (ODF) file, the paragraph alignment in the file is displayed incorrectly.
  • Consider the following scenario:
    • You configure an East Asian language input method editor (IME) for Word 2013 on a Windows 8-based touch device.
    • You type some characters in a new Word document.
    • You touch the document to change the cursor location.
    • You type a new character or delete the character before the cursor.

    In this scenario, the new character is displayed in the previous location of the cursor, or the character that is before the previous location of the cursor is deleted.

  • Assume that you have editor permissions on a SharePoint Server 2013-based server. When you try to create an application on the server or upload an application from the corporate catalog to the server, you receive the following error message:
    unexpected error
  • Assume that you open a document that contains a text box. The text box contains a table, and the table contains some text. After you change the background color of the text box to certain colors, the text is no longer visible.

Project Server 2013 “where is Performance Point?”

Some of you may have noticed that Performance Point is not around in the Business Intelligence area of Project Server 2013. How to actually find and launch this amazing BI tool is quite different compared to Project Server 2010, as it is now an app (actually two apps) that is not enabled out-of-the-box.

1-ps

Here are the simple steps to activate and launch the Performance Point designer application:

1. Go to add an app (the screen dump is a little wrong – sorry):-)

2-ps

2. Select and add the two Performance Point apps

3 - ps

3. Find the new apps in the content area of your PWA

4 - ps

4. Open on of the apps and notice the PerformancePoint menu in the ribbon

5 - ps

Create your reports as you would in Project Server 2010 – have fun!

Tagged

Project Online is now released

You can now buy or try the final version of Microsoft Project Online.

Go here: http://office.microsoft.com/en-us/project/

online

 

 

 

How to refresh Excel Services in Project Online

Grant reporting access in Project Online

When a Project Online report is created using Excel 2013, you can open it in Excel Web App and refresh the OData feeds in the report. This retrieves the most recent data in Project Online.

Excel Web App uses the BI Azure Service to refresh the Project Online OData connections. For this to work, you must first grant the BI Azure Service permission to access Project Web App.

 Note    You only need to grant this permission one time for each Project Web App site.

  1. Log on to Project Online.
  2. Add the following to the end of your Project Web App URL: /_layouts/15/appinv.aspx

For example, if your Project Web App URL is http://contoso.sharepoint.com/sites/pwa, type in this URL: http://contoso.sharepoint.com/sites/pwa/_layouts/15/appinv.aspx

  1. In the App ID field, type 00000009-0000-0000-c000-000000000000, and then click Lookup.

 Tip    Try copying and pasting that code. If that doesn’t work, that first set before the 9 is seven 0s, and that last set is twelve 0s.

Lookup button

  1. In the Permission Request XML field, copy and paste the following code:

<AppPermissionRequests>
<AppPermissionRequest
Scope = “http://sharepoint/projectserver/reporting&#8221; Right=”Read”>
</AppPermissionRequest>
<AppPermissionRequest
Scope = “http://sharepoint/content/tenant&#8221; Right=”FullControl”>
</AppPermissionRequest>
</AppPermissionRequests>

  1. Click Create.
  2. On the next page, click Trust It to trust the BI Azure Service on your Project Web App site.

Trust It button

You should now be able to refresh your Project Online reports in Excel.

Automatically create “Dimension Counts” in your OData Excel reports

Here is a little something nice for those of us creating reports on Project Server data.

Previously when working against an OLAP cube in Excel 2010/2013 it was not possible for me to easily get some “Counts” on the various dimensions such as Project Name, Ressource Name, Enterprise Project Type Name and so on. What you would get, if trying to drag a dimension to your “Values”, would be as shown below:

 

owner count 1

However, if I connect to my Project Online OData source and take a dimension e.g. Project Name, pull it to the “Values” area, it automatically creates a count for me. Smooth and fast!

owner count 2

 

This little trick can be used for a lot fun stuff on the reporting side and I guess only the imagination (and version of Excel) sets the limit to what you can use it for.

Tagged , , , , ,

Reporting with Project Online…without OLAP cubes

Project Online does not offer the OLAP cubes which have been around since Project Server 2003. Instead Project Online offers you to use “OData Feeds” and the question is then: “How easy and flexible” is that”?

First of all OData defines an abstract data model, and a protocol that let any client access information exposed by any data source. Below figure shows some of the most important examples of clients and data sources, illustrating where OData fits in the picture.

When it comes Project Online versus OData you actually only need to know the correct PWA path, in order for you to create great looking Excel reports. Here is how you do it:

1. Open Excel and add data from Other Sources – Select OData feed and use your PWA header + _api/ProjectData

odata connect

 

2. Select which tables to get the information from e.g. “Projects”, which also contains all you Project level custom fields

odata connect 2

3. Create the data from a PivotTable Report to get the same “feel” as if you were running on OLAP cubes

odata connect 3

 

4. Create the report the way you would like using the Fields picker on the right side.

odata connect 4

 

And that is how you create a report when not having an OLAP cube around – simple right?

 

Moving data from Project Server 2010 to 2013 Online/Cloud

I am a big fan of Project Server 2013 now being offered as an Online/Cloud solution trough Office365. However, my second thoughts was around how to actually move my Project Server 2010 data to the Online solution in a smooth and painless way. Lucky for me, a company named FluentPro has made a very simple piece of software that easily allow for your 2010 data to be moved directly into you Project Online environment.

FluentPro offeres a simple and a pro edition of the software. I have decided to focus only on the pro-edition as this will work also on Project Online when it reaches RTM time, hopefully by the end of Q1 2013.

Overview of FluentPro:

  • Supports one way migration – from Project Server 2010 to Project Online. Please note, this version will not work with Project Server 2013 on-premise.
  • Software is licensed per PWA and can be used only with 2 PWA on the same server (Test and Production)
  • Email based standard priority support
  • Contains 3 applicaitons: Cloud Migrator Pro, Project Migrator Cloud Edition for Project Professional 2010,  Project Migrator Cloud Edition for Project Professional 2013
  • Will work with RTM

Supportet configuration elements:

  • Custom fields and Lookup tables
  • Views
  • EPT and PDP
  • Workflow Stages and Phases
  • Tasks and Timesheet Settings
  • Resources
  • Time Reporting and Financial Periods
  • Projects are supported with license of FluentPro Project Migrator (add-in for Project Professional 2013); license is provided as part of the package
  • Templates Migration
  • Security Configuration Migration (Project Security Mode)
  • Project Sites (Risks, Issues and Document Libraries)

 

You can read more from FluentPro´s own landingpage using this link: http://www.projectmigrator.com/fluentprocloudmigrator.html

 

Over and out and MAY THE PROJECT FORMULA BE WITH YOU!

 

Follow

Get every new post delivered to your Inbox.

Join 26 other followers