MSBI # 37 – MPS # 2 – Microsoft Project Server 2010 Reporting with Excel Services – Overview for Configuration, Sources, Connections, ODC, Templates

Hi folks,

Continuing from my last single post on Microsoft Project Server 2010

• MSBI # 30 – MPS#1 – Microsoft Project Server 2010 Reporting with Excel Services – Introduction « (B)usiness (I)ntelligence Mentalist

In this post we are going to see following points

• SharePoint Server 2010 and Project Server 2010 Configuration
• Excel and Excel Services
• Data Sources
• Data Connections
• ODC Files
• Report Templates
• Securing Reports

Lets continue with same and understand what we are going to achieve in this series as well

and as we know Microsoft EPM Solution relies on Microsoft SharePoint Server 2010, Microsoft SQL Server 2008 or 2008 R2, Microsoft Project Server 2010 and Microsoft Project Professional 2010.

Various components available for Excel service

1. SharePoint Server 2010 and Project Server 2010 Configuration
2. Excel and Excel Services
3. Data Sources
4. Data Connections
5. ODC Files
6. Report Templates Lets see each one at one step further and see with perspective with Excel Service

Now lets discuss one by one ,

SharePoint Server 2010 and Project Server 2010 Configuration

As we differentiate various tools in two parts one is Server and other is client side

On the client side we have:

• Microsoft Excel 2010 or Excel 2007 for authoring the reports

On the server side we have:

• Excel Services
• Secure Store with an application profile that stores account credentials
• Excel workbooks and ODC
• The SQL Server Reporting database
• The OLAP database that contains the cubes Above tools are as shown in following

Figure which explains Configuring Reporting in Project Server 2010

Now lets dig bit into initial configuration

The next step for configuration of Project Server 2010 for Reporting configuration

OLAP database and Analysis Services configuration

• Add the Farm Administrator account to the OLAP users local group.
• Configure the Farm Administrators account to have administrative permissions in SQL
  Server Analysis Services.

Configure reporting for Project Server 2010:

• Add a logon for the report authors group in SQL Server
• Install SQL Server 2008 Analysis Management Objects (AMO)
• Start Excel Services
• Create an Excel Services service application
• Configure Excel Services settings
• Configure a trusted file location for the Templates library
• Configure a trusted file location for the Sample Reports  library
• Configure trusted data connection libraries (one per language)
• Start the Secure Store Service
• Configure Secure Store Service settings
• Create a Secure Store target application
• Populate the Report Authors and Report Viewer Active Directory Groups
• Configure Business Intelligence Center access
• Grant permission to external report viewers

Remember for each new project Web App site is created, the following steps will need to be performed:
• Configure a trusted file location for the Templates library
• Configure a trusted file location for the Sample Reports  library
• Configure trusted data connection libraries (one per language) Best part is if  you forget to do it, you will get this error message

Excel and Excel Services

Through Excel we can have following sharing scenarios:

1.Data can be shared via web interface using Excel Services

2.Data can be distributed via XLSX files via email or some other facility and using Excel client for viewing

• Files can be static snapshots of the web view, allowing you to share broadly or to modify the data for your own uses
• Files can also remain data-connected, allowing the user to refresh the data as needed, using the client.  Note that this functionality requires Report Author level security, with a direct access to the databases.

In fact Best part of this is organization can build its own reports by using the Office Excel 2007 or Excel 2010 client and publish them to PWA BI Center.

Data Sources

We have two option for Project Server related data sources for your reports

1. Relational (Project Server Reporting DB)
2. Decisional (14 Project Server Analysis Services OLAP Cubes) Based on following specific criteria we can choose our Data Source

Relational:

• Up-to-date data
• Superset of data available in
• No drilldown required in repo

Decisional:

• Aggregation across dimension
• Efficient for reports on time
• Key Performance Indicators

   

  It is not recommended to mix these two type of data sources in the same report, because the Reporting data is always up-to-date and the Decisional data is only up-to-date when the OLAP database has been processed, which may occur only daily or weekly depending on your configuring choices. The data itself can be separated into two main categories: OLAP and non-OLAP data.  Generally, SQL Server tables are suitable for providing a snapshot of the project or resource data as it is right now.  OLAP data is more appropriate for providing a snapshot of the data right now, and then allowing a comparison of the same data over time – for instance, resource availability over the next three months, or the actual cost of all projects on a monthly basis for the last six months. The OLAP data is kept in an instance of SQL Server Analysis Services and may be consumed via any number of reporting tools.  The non-OLAP data is stored in the SQL Server instance.

Relational Tables

Project Server data is stored in a set of four SQL Server databases:

• Archive
• Draft
• Published
• Reporting

Microsoft only documents and supports developing reports using data from the Reporting database.  For a detailed description of the Project Server 2010 Reporting database schema, refer to the Project Server 2010 Software Developer Resource Kit available here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=46007f25-b44e-4aa6-80ff-9c0e75835ad9&displaylang=en

Understanding the Reporting Database Schema

he Project Server 2010 SDK documentation comes with a Reporting Database Schema Help file and a neat utility consisting of a PivotTable to help select fields in the Reporting database
(RDB_Field_Selector.xlsx).

So there is no need to read all the documentation to locate a specific field in the Reporting database.

OLAP Cubes

OLAP cubes provide the capability to analyze over time period, roll up and drill down into Project Server data. 

The 14 OLAP cubes provided natively contain data extracted from the Project Server Reporting database.

Within Project Server 2010, each department may have its own set of OLAP cubes

which may then be surfaced by using

• Excel
• Visio
• PerformancePoint
• Other reporting tools. 

OLAP cubes are configured in PWA Server Settings, and once configured, may be accessed by using any of the reporting tools identified above.

Note that any custom fields developed by the organization must be added to the OLAP cube
configuration through the standard user interface and the cube must then be rebuilt before those fields are available for report writing

Data Connections

Project Server 2010 is shipped with default Office Data Connections (ODC) files that can be used once the configuration is complete.  It is also possible to create new Data Connections from within Excel when you are authoring reports.  

There are also preconfigured blank templates provided that are connected to the included ODCs to help users create new reports quickly.  For each OLAP database, a blank template and accompanying ODC is created for each cube within the OLAP database.

Each time you create a Project Web App site, the following Office Data Connection (ODC) files are available in the Business Intelligence Center:

Data Connection Files:

1.Enterprise Project Management

• Simple Project List (SimpleProjectList)
• Milestone due This Month (MilestonesDueThisMonth)
• Resource Capacity (ResourceCapacity)

2.Timesheet
• Timesheet Actuals (TimesheetActuals)

3.SharePoint Lists
• Deliverables
• Issues And Risks (IssuesAndRisks)

4.Portfolio
• Rejected Project List (RejectedProjectList)
• Top Projects (TopProjects)
• Workflow Chart (WorkflowChart)
• Workflow Drill Down (WorkflowDrillDown)

ODC Files

ODC files allow for the combination of multiple tables or views to create useful Excel and Visio reports. 

One trick to leverage this feature is to add the parent task to task summary reports.  

For users who are not comfortable developing ODC scripts, Microsoft has provided a helpful tool that allows users to select the desired fields and then generates the required ODC file.

The ODC Report Builder tool is a free download and is available as part of the Project Server 2010 Solution Starter pack  : (http://code.msdn.microsoft.com/P2010SolutionStarter)

Report Templates

Project Server 2010 is shipped with default report templates and Office Data Connections that can bused once the configuration is complete.  These Excel-based report templates can either be used as can be used as a basis for creating additional reports.

There are also preconfigured blank templates provided that are connected to the included ODCs to users create new reports quickly. For each OLAP database, a blank template and accompanying ODC created for each cube within the OLAP database.

Each time you create a Project Web App site the following reports are available in the Business Intelligence Center:

Here is the list of the default Templates:

• Dependents Project (DependentProjects)
• Issues
• Project and Assignments (ProjectsAndAssignments)
• Resources
• Risks
• Timesheet

Sample Reports

1.Enterprise Project Management

• Simple Project List (SimpleProjectList)
• Milestone due This Month (MilestonesDueThisMonth)
• Resource Capacity (ResourceCapacity)

2.Timesheet

• Timesheet Actuals (TimesheetActuals)

3.SharePoint Lists

• Deliverables
• Issues And Risks (IssuesAndRisks)

4.Portfolio

• Rejected Project List (RejectedProjectList)
• Top Projects (TopProjects)
• Workflow Chart (WorkflowChart)
• Workflow Drill Down (WorkflowDrillDown)

Useful actions

 

Securing reports

The security for Reporting is distinct from Project Web application security, because Report viewers may not be part of PWA Users.

The Business Intelligence Center uses the native SharePoint Security.

The PWA Administrator administers the user with rights to manage the BI Center suite.

By default all the PWA members have read-only access to the BI Center site.

There is a need to use a specific SQL Security Group for Project Server Report Author with DB_Reader access rights to the relational database.

The following security model is used for Reporting:

Excel Services Security Model

The security model for Excel Services is based on the concept to ensure:

• Data integrity
• Data quality

Hope this help !!

Thanks for visiting my blog !!

Hope you have understood basic aspect of Data Flow Task and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource # 24: Sept 08–Sept 12 « Dactylonomy of Web Resource