ipmo entity importer for project server 2010 · 3 installing ipmo entity importer for project...
Post on 10-Jun-2020
10 Views
Preview:
TRANSCRIPT
IPMO Entity Importer for Project Server 2010
Client: IPMO
Date: August 12
2 of 23
Contents
1 Introduction .................................................................................................................................... 3
1.1 Purpose ................................................................................................................................... 3
1.2 Architecture ............................................................................................................................ 3
1.3 Product Features ..................................................................................................................... 4
2 System Requirements ..................................................................................................................... 4
2.1 Operating System .................................................................................................................... 4
2.2 Software .................................................................................................................................. 4
2.3 Browser Client ......................................................................................................................... 4
3 Installing IPMO Entity Importer for Project Server 2010 ................................................................ 5
3.1 Run the Installer ...................................................................................................................... 5
3.2 Installing the Software License ............................................................................................... 8
3.2.1 Manual License file installation ....................................................................................... 9
3.3 Activate Site Collection Feature ............................................................................................ 12
3.4 Create Project Server Integration Link .................................................................................. 12
4 Configuration ................................................................................................................................ 14
4.1 Creating a Project Entity Data Source ................................................................................... 14
4.2 Creating an Import Package .................................................................................................. 17
4.2.1 Import Package Entry Form Definition ......................................................................... 18
4.3 Export Project Entity Configuration ...................................................................................... 20
4.4 Import Project Entity Configuration ...................................................................................... 21
4.5 Create an Import Schedule ................................................................................................... 22
4.5.1 Import Schedule Entry Form Definition ........................................................................ 22
4.6 Viewing the Import Log Information .................................................................................... 23
3 of 23
1 Introduction To be a successful Project Manager there are a number of competencies required. Competencies
such as managing the project schedule, cost, resources, scope, documentation, requirements,
change management, procurement, status reporting and time capturing. Implementing an
Enterprise Project Management solution such as Project Server offers a range of benefits to an
organisation. Key benefits such as centralisation, visibility, structure, governance and consistency. In
todays fast paced world of information management we are often required to interface with a series
of internal line of business systems. The golden rule in information management is to enter data in
one location and prevent the need to enter duplicate information across multiple systems. The
Entity Importer for Project Server 2010 allows the business to automatically import Information from
line of business systems into Project Server.
1.1 Purpose The Entity Importer for Project Server 2010 allows the business to automatically import Information
from line of business systems into Project Server and prevents data entry duplication. Automate
multiple scheduled import jobs to update Project Server information at the following entity levels:
Projects
Tasks
Resuorces
Assignments
Assignment Status Updates
Lookup Tables
Keep data in Project Server synchronised with information maintained in other line of business
applications. For example, import information such as actual cost from the finance system, resource
information from the HR system, internal timesheet information or even Project Server lookup table
data from appliucations such as asset management and CRM systems. Capture data in a normalised
format from the source location and map it to the related entity type in Project Server. Set the job
schedule and let the Entity Importer maintain the synchronisation of data.
1.2 Architecture The following image describes the architecture of the Entity Importer for Project Server 2010.
ODC Connection
Entity Importer SharePoint Configuration Site (PWA)
Project Server Databases
Data Connections
Import Package Settings
Import Schedules
External Data Source (SQL Database)
Pro
ject
Ser
ver
Inte
rfac
e (
PSI
Web
Ser
vice
)
Projects Tasks Resources Assignments Assignment
Status Updates Lookup
Tables
Source Data Entity Importer for Project Server2010 Target Data (Project Server 2010)
4 of 23
1.3 Product Features The Entity Importer for Project Server 2010 contains the following features:
Synchronise data from line of business applications with information kept in Project Server.
Establish data connections to multiple external line of business systems.
Ability to schedule multiple import jobs in units of minutes, hours, days, weeks, months and
years
Create data connection files (.odc) using a product such as excel to generate connections
files easily
Create Data mappings from an external data connection against multiple sets of Project
Server data. This includes project, task, resource, assignment, assignment status updates
and lookup table information. Specify which source fields relate to their corresponding
entity target fields in Project Server and how that information is aggregated from the target
data source
Capture and view import log information in a central location. Control the level of data being
logged
Ability to export and import configuration settings between SharePoint environments
Easily installed to a SharePoint Farm running an instance of Project Server 2010
Fully Integrated with SharePoint 2010. configuration site created once the feature has been
activated in the PWA site collection
Product uses Microsoft supported methods. Data is imported into Project Server via web
wervice calls to the Project Server Interface (PSI)
Provided additional granular settings such:
o Ability to create the Project in Project Server if the project does not exist in the
destination environment
o Functionality to run in test mode which emulates the import, logs errors where
encountered but does not actually import the data
o Configure whether the import package stops or skips the current job upon error
o Configure create, update and delete type imports
o Configure how project check-outs are handled, force check-in, skip or stop.
o Set the priority of the job in the queue
o Ability to monitor the queue
2 System Requirements
2.1 Operating System Windows Server 2008 or Windows Server 2008 R2
2.2 Software IPMO Entity Importer for Project Server 2010 requires the following software is installed and
correctly configured.
Microsoft SharePoint Enterprise Server 2010
Microsoft Project Server 2010
SQL Server 2008, SQL Server 2008 R2 or SQL Server 2010
2.3 Browser Client Microsoft Internet Explorer 7.x but recommend Explorer 8 or greater
5 of 23
3 Installing IPMO Entity Importer for Project Server 2010
3.1 Run the Installer 1. Copy the installation files to the SharePoint Server. Right click the ‘Setup.exe’ file and run
the file as Administrator.
2. Click Next.
3. Check the ‘I accept the terms in the License Agreement’ and click ‘Next’
6 of 23
4. Click next once the System check has been performed.
5. Click ‘Next’ After the solutions have been validated.
6. Ensure the ‘Install’ option has been selected and cliack ‘Next’.
7 of 23
7. As this is a farm scoped solution, click ‘Next’.
8. Confirm the installation summary. Click ‘Next’.
9. The installation begins. Click ‘Next’ once the installation has been completed.
8 of 23
10. Click ‘Close’
3.2 Installing the Software License Once the product has been purchased from IPMO you will need to install the license file to ensure
the product does not expire.
1. Navigate to Centrl Administration>Application Management. Under IPMO Product
Licensesm click ‘Project Entity Importer’.
9 of 23
3.2.1 Manual License file installation
1. If there is no internet connection, the following screen will appear, click “Request”.
2. Copy the “License Request String” to the clipboard. (Ctrl + C)
3. Navigate to IPMO’s web site on a machine that has internet access.
10 of 23
4. Login to IPMO’s web site (if you haven’t got a login, you will need to create an account)
5. Click the “My Account” link in the top right hand corner.
6. Click the “Get My Product License” link.
7. Paste your Product Key and License Request String into the fields provided and click “Get
License”.
11 of 23
8. Save your License file to a location accessible from the Server hosting SharePoint Central
Administration.
9. Return to the Server hosting SharePoint Central Administration and click “Upload License”.
10. Click “Browse” and select the License file downloaded from the IPMO internet site. Then
click “Install”.
12 of 23
11. You have now successfully completed the Manual, Online activation process for one of
IPMO’s products.
3.3 Activate Site Collection Feature Activate the IPMO Entity Importer site collection feature under the relevant site collection. This is
typically the ‘PWA’ site collection for general Project Server installations.
1. Navigate to the base site collection which is hosting PWA. Typically
http://projecthostname/PWA
2. Click Site Actions>Site Settings
3. Under the Site Collection Administration group click ‘Site Collection Features’
4. Click the ‘Activate’ button for the ‘IPMO Project Entity Importer’ feature. This will create the
‘PWA Integration Settings’ SharePoint Site.
3.4 Create Project Server Integration Link Once the feature has been activated the ‘PWA Integration Settings’ SharePoint Site will be created.
The linkto this site will be created on the PWA hompage.
13 of 23
1. Naigate to PWA>Server Settings. Click on the ‘Quick Launch’ menu item located under the
‘Look and Feel’ group.
2. Click ‘New Link’ to create a new link in the PWA Quick Launch area.
Name: Integration Settings
URL: /PWA/IntegrationSettings
Heading: Settings
Display in Quick Launch: Yes
3. Click OK. Return to PWA homepage and note the new ‘Integration Settings’ link will now be
available.
14 of 23
4 Configuration
4.1 Creating a Project Entity Data Source When creating a Data, ensure the following accounts have access to read the Data Source.
The account you use to create the Data Source
The SharePoint Timer Service Account
The PWA Web Application Pool Account
In this example the data source will be the “MSPSConnectTimesheet” database.
1. From a workstation or a computer which has Microsoft Office Excel installed, open Microsoft
Excel 2010.
2. Select the “Data” Tab, and then select “From Other Sources=>From SQL Server”
3. Enter the name of the SQL Server hosting the MSPSConnectTimesheet database.
4. Click “Next” and then select the “MSPSConnectTimesheet” database.
15 of 23
5. Click “Next”
6. Rename the File Name and the Friendly Name to “MSPSConnectTimsheet”
7. Then Click “Finish”. Excel will prompt for a Worksheet location to place the Data. Simply use
the defaults and click “OK”.
8. This has created a simple .odc within Excel.
9. Click the “Data” tab and select “Connections” to display the Workbook Connections dialog
window.
16 of 23
10. Select the “MSPSConnectTimesheet” connection and click “Properties…” Select the
“Definition” tab.
11. Edit the query used to source the Data. Change the Command Type to “SQL” and paste into
the “Command text” the following SQL Query.
--** EXAMPLE QUERY ONLY ** SELECT p.ProjectUID , tse.[TaskUID] , '' TaskParent , r.ResourceUID , tse.[WorkDate] AS StartDate , DATEADD(day, 1, tse.[WorkDate]) AS FinishDate , tse.[WorkMinutes] * 1000 AS MSPActualWork FROM [dbo].[TimesheetEntry] tse INNER JOIN dbo.Timesheet ts ON ts.TimesheetUID = tse.TimesheetUID INNER JOIN [dbo].[ProjectTasks] t ON t.TaskUID = tse.TaskUID INNER JOIN [dbo].[Project] p
17 of 23
ON p.ProjectUID = t.ProjectUID INNER JOIN dbo.ResourceTask rt ON rt.TaskUID = tse.TaskUID INNER JOIN dbo.Resource r ON r.UserID = ts.UserID WHERE tse.[WorkDate] <= GETDATE() --Less than or equal to today --Greater than the start of the current month AND tse.[WorkDate] >= CONVERT(datetime, '1 ' + CONVERT(varchar(max), DATENAME(month, GETDATE())) + ' ' + CONVERT(varchar(4), DATEPART(year, GETDATE()))) ORDER BY p.ProjectName , t.TaskName , r.ResourceName
12. Click “Export Connection File…” and save the .odc file to the local file system. Click ‘Cancel’
and close Excel.
13. Upload the .odc file to the Project Entity Importer. Using the web browser on the same
computer you created the .odc file on, open the PWA Integration Settings site using the
same method described earlier.
14. Click “Data Sources” and upload the .odc file you saved to the local file system.
4.2 Creating an Import Package 1. From the PWA Integration Settings site, click “Import Packages=>Add new item”
2. Enter a Title and select a Data Source from the available options. Wait for the page to post
back, as it reads the Data Source to fill in the Source Field dropdown lists.
3. Change the “Project Server Entity” to the level which the data must map to in Project Server
and wait for the page to post back as it reads project server to find the field names to fill in
18 of 23
the target field dropdown lists. Possilbe options for this list include the following:
4. Complete the form as it relates to the type of data being imported.
5. Click “Save” to store the import package.
4.2.1 Import Package Entry Form Definition
The following table helps describe the fields defined in the import package entry form.
Field Name Possible Values Comment
Project Server Entity Project
Task
Resource
Assignment
Assignment Status
Lookup Table
The type of Project Server data which is being imported into
Import Action Create and Update
Create Only
Update Only
Delete
As the names suggest
Error Action Report error and stop import
Report error and
As the names suggest
19 of 23
keep processing
Clear Previous Log Entries
Checked
Unchecked
As the names suggest
Entity Checked out Action
Throw an error
Force Check-in
Skip item
As the names suggest
Project Missing Action
Throw an error
Skip item
As the names suggest
Publish Entity Changes
Checked
Unchecked
As the names suggest
Task Parent Provides a list of field names from the linked data source for the user to select. One value can be selected.
Only visible for the ‘Tasks’, ‘Assignment’ and ‘Assignment Status’ Entity seleciton
Task Parent Field Empty Value
Free Text Only visible for the ‘Tasks’, ‘Assignment’ and ‘Assignment Status’ Entity seleciton
Auto Approve Status Updates
Checked
Unchecked
Only visible for the ‘Assignment Status’ Entity selection
Lookup Table Value Tree Delimiter
Free Text Only visible for the ‘Lookup Table’ Entity seleciton
Entity Identification and Macthing
This section is required to specify the link which joins the target data with the destination (Project Server) data . The link will vary depending on the selected Project Server Entity but will look like the following:
Target Field: This contains a list of all the field names in the target data source which is Project Server data. Matching: The method in which a target field is matched to the source field. Possible values include:
Equals
Target <= Source
Target < Source
Target >= Source
Target > Source
Source Contains Target
Target Contains Source Source Field: This contains a list of all the field names in the source data source which is derived by the .odc connection data.
Field Mapping
Once a join has been made using the Entity Identification and Matching section, the underlying data which matches this criteria needs to be mapped to the corresponding Project Server field.
20 of 23
Target Field: This contains a list of all the field names in the target data source which is Project Server data. Source Field: This contains a list of all the field names in the source data source which is derived by the .odc connection data. Conversion Rule: Possible Value include:
Ignore
Apply Value
Apply Value from Lookup Table Value Description Value Delimiter Delete: Free Text field to indicate the delimiter field Please note this interface allows the user to add, modify and delete as many mappings as the import package requires.
Test Mode Checked
Unchecked
Use Test Mode to test an import package. Test Mode will not change data, but will log import errors as if it did. Test mode will not catch all possible error's.
Minimum Log Level Verbose
Information
Warning
Error
As the names suggest
Priority Numeric
Data Source Filter Free Text
Monitor Queue Don’t Monitor
5 Minutes
10 Minutes
15 Minutes
20 Minutes
25 Minutes
30 Minutes
1 Hour
Indefinitely
4.3 Export Project Entity Configuration 1. From the PWA Integration Settings home page, select the “Export” radio button and click
‘Export’
21 of 23
2. When prompted, select the location and name of the configuration backup. Once saved this
information can be imported into other environments. Useful when keeping a mutli
environment (Development, Test and Production for example) synchronised.
4.4 Import Project Entity Configuration 1. From the PWA Integration Settings home page, select the “Import” radio button.
2. Click “Browse” and select the “XXXXXX.xml” file from the deployment package. Assumption
here is a configuration package has been created previously using the ‘export the package
settings’ link.
3. Click “Import”.
22 of 23
4. You have now imported the Project Entity Importer configuration from the XML file.
5. This includes Data Sources (if they didn’t already exist), Import Packages and Import
Schedules. Items may already be enabled, so double check the Import Schedules to ensure
you are not about to overwrite content in your new environment.
6. If you also imported Data Sources you need to update the Data Source to point to your
current environments server names. Otherwise you might find yourself importing test data
into your production environment.
4.5 Create an Import Schedule 1. From the PWA Integration Settings site, click “Import Schedule=>Add new item”
2. Enter settings as per the following screen shot.
4.5.1 Import Schedule Entry Form Definition
The following table helps describe the fields defined in the import schedule entry form.
Field Name Possible Values Comment
Title
Error Action Report error and keep processing
Report error and stop processing
As the names suggest
Import Packages Values from the Import Packages list
Enabled Checked
Unchecked
As the names suggest
Frequency Numeric Data
Frequency Units Minutes
Hours
Days
As the names suggest
23 of 23
Weeks
Months
Years
Next Start Date Date and time value Date and time of when this import package will commence
Processing Effort Numeric (% value) determines how many threads the importer uses when executing the import package. Setting the Processing effort to 100% will invoke threads up to 4 times the number of CPU Cores available on the server. Setting the Processing effort to 1% will use at minimum 1 thread.
3. Click “Save” to keep changes.
4.6 Viewing the Import Log Information 1. Log information for each import job can be configured. To view this information, navigate to
the PWA Integration Settings site. Click the ‘Import Log’ link from the left navigation menu.
2. Use standard SharePoint nagiation and view setting functionality to review the information
contained within this list.
top related