Introduction
Tracking Project / Portfolio Progress and forecasting future performance are the cornerstones of all project management methodologies. While the reporting requirement itself is so common and generic in nature, yet every organization has some specific PM practices which demands unique reporting & that results in anywhere from little to very significant custom reports development in Project Server. Therefore, Microsoft Project Server has always provided reporting tools with rich capabilities not only to create and distribute Traditional or Analytical reports but also to facilitate power users to generate ad-hoc reports based on unplanned requirements.
The latest version of Project Server (i.e. 2013) has introduced some new reporting methods. In addition, reporting possibilities of Microsoft Hosted version of the project Server (i.e. Project Online via office 365) is not exactly same as its on-premise counterpart and hence it is essential to analyse, plan and architect reports according to its deployment scenario.
This article summarises the key reporting capabilities of Project Server 2013 for both on-premise and hosted solutions.
Data Access
Querying the reporting database
On-premise solutions have the capability of accessing the reporting data directly by querying the reporting database. This capability is not available in Hosted solutions. The 4 databases (draft, publishing, archiving & reporting) per project server 2010 instance have now been merged as single database per project server 2013 instance. However, reporting table names have not been changed and hence reports written against project server 2010 schema are compatible with Project Server 2013 as well.
oData
oData service exposes Project Server reporting data via web service interface. The query is passed via URL and result is returned as XML document which can then be consumed by Excel 2013 and other client side applications.
oData query syntax support REST and LINQ format which covers selecting, filtering, sorting, joining etc. but if you have complex SQL Queries (With Nested Queries, Functions , IN Clause and many joins), the effort to write and test the queries will be significantly higher. There is no stored procedure equivalent in oData. Below is an example of selecting all projects in a PWA Instance with only the column ProjectName in REST syntax
http://[ServerName]/[PWA Instance Name]/_api/ProjectData/Projects?$Select=ProjectName
See the following link to learn more about Querying OData feeds for Project Server 2013 reporting data.
OLAP Databases
OLAP databases are primarily used to Analytical reporting. These are available for on-premise deployment only and doesn’t contain any significant enhancement from previous Project Server versions.
Data Visualization
SQL Server Reporting Services
SSRS is a tool of choice of for developing traditional and UI Rich reports. SSRS does not natively support oData feeds. Though it is possible to consume oData feeds using XML data source, claims based authentication model of Office 365 makes it even more difficult to consume hosted project server data, hence this tool will mainly be used for on-premise deployments only.
Many EPM Solution providers have developed significant amount of SSRS based reports for previous versions and porting these reports for hosted version will require considerable effort.
Excel 2013
Excel 2013 has native support for oData Consumption and Project Server 2013 hosted version comes with pre-installed oData based Data Connections which are utilized by Excel based reports. Using the oData (or direct SQL data via SQL Query) capabilities of Excel 2013, Tabular, graphical and Pivot Table based reports can be created and published on SharePoint using Excel Services. For on-premise solutions, excel pivot tables can also connect to OLAP cubes.
Excel 2013 with Power View
Power view is an interactive, data visualization and exploration add-in for Excel 2013. This tool empowers power users to create Information rich and interactive dashboards based on Data models created in Power Pivot (Power Pivot can in-turn be a consumer of oData feeds).
Power view combines interactivity of Analytical reports like slicing, dicing, drilling up & down with very rich UI elements like Dashboards, Maps, Charts, Graphs, Tables & Images. Power-view is the key reporting differentiator from previous Project Server offerings as it empower day to day users to create attractive and useful dashboards. See the following links to learn more about power-view capabilities.
http://blogs.office.com/b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx
Other reporting tools
Any reporting tool capable of connecting to SQL Server (or OLAP cubes) directly can be used for on-premise deployment. Examples of such tools are Performance Point, Dundas Charts, Fusion Charts etc.
There are hardly any specialized tools available which consumes Project Server oData feeds natively. However, the open nature of Web Services allow the developer to write their own Custom applications to consume data and create custom Reporting UI. Developers can also use CSOM (Client side object model) to access data which is not available via oData feeds as oData only provides data which is available in Project Server Reporting Database tables and views.
Project 2013 Client side Reporting.
Project 2013 Client comes with many new reports. Creating dashboard based reports from single project data is intuitive and easy. This is definitely going to be a handy feature for Project Managers who wants to report project progress on ad-hoc or periodic basis. These reports are good for printing, presentations and sharing via email but unfortunately can’t be published over PWA.
Summary
Following table summarises various available technologies for Project Sever 2013 Reporting for both on-premise and hosted versions.
Technology
|
On Premise
|
Online
|
Comments
|
Data Query
| |||
Directly Querying SQL Server Reporting Database
|
Yes
|
No
| |
oData
|
Yes
|
Yes
| |
OLAP Database
|
Yes
|
No
|
Same capabilities as Project 2010
|
Data Visualization
| |||
SQL Server Reporting Services
|
Yes
|
Yes
|
Online version requires more work
|
Excel 2013 - Pivot Tables & Charts
|
Yes
|
Yes
| |
Excel 2013 - Power View
|
Yes
|
Yes
| |
Other Tools
|
Many
|
Few
|
View Hammad Arif's LinkedIn profile
0 comments:
Post a Comment