Monday, 17 October 2011 20:14 We have heard a lot of experts in forums talk about using SDK for uploading

data quickly to your database . But

most of us dont know how to use them. In this post we will see how we can use SDK to upload data in Primavera. Following are the per-requisites before you can start using SDK: 1) Install SDK using you Primavera installation CD 2) Configure PMSDK connectivity to your Database 3) Primavera SDK Excel Sheets a) Dictionary Can be used to upload/edit Global Data in Primavera b) Project Can be used to upload/edit Project level data for multiple projects c) WBS Can be used to edit WBS for one Project in your database d) Activity can be used to upload/edit all activity attributes of your project Note: I will not be posting any links to these sheets or send them by email. Its against the Primavera support agreement. If you have an Oracle Primavera support account then you can download it from the website or ask in various forums. 4) Your username should be allowed access to SDK in your security profile Once you have done the above mentioned steps then you can use your Primavera username and password to access the database via SDK. You will need to follow a certain workflow in order to use these sheets properly. Since using SDK can potentially damage or corrupt your data, I would suggest that you backup your database every time you use SDK till you are not comfortable with the process. SDK Workflow

The Get and Set commands are very important and you need to always ensure that you use Get command before you start adding/editing data in your excel sheet.

In my next post we will see how to edit large amount of activity level data for a project using the Activity Excel Sheet.

Saturday, 29 October 2011 13:44 In this post we will try and enter data in Primavera using SDK Sheets. In order to do this open Dictionary.xls and do the following steps: 1) Enter Username and Password 2) Press on tab Get Activity Codes 3) Once the process is completed, open the Activity Codes Sheet and add data 4) Press on tab Set Activity Codes

Open Activity Codes Sheet to add or edit data.

In case you need help regarding what data goes where, click on the ? icon for help

Once your done click on the Set button to upload the data in Primavera database and then you can login to Primavera and check if the data has been updated or not.

Dont forget to follow the workflow explained in my previous post. Following the workflow properly would help you to avoid any mistakes while uploading your data in Primavera.

In this tutorial we explain how to use the Oracle Primavera P6 SDK to import a WBS from Excel to Primavera P6. We are using the WBS.xls P6 SDK spreadsheet made available by Oracle Support. The SDK is a very handy tool, but the data must exactly respect the required format, we used a sample

WBS and a step-by-step guide to prepare the data to be imported. We assume you already have the
Primavera SDK installed.

Tutorial Assets

The following assets were used during the production of this tutorial. WBS-Import-P6-Tutorial.xls

Step 1 Copy the WBS from to Excel

For simplification, every step of the tutorial has its own Excel worksheet in the spreadsheet (see WBSImport-P6-Tutorial.xls in Tutorials Assets). Ive numbered each worksheet appropriately. For this tutorial, were using a sample WBS from this site. Weve already cleaned-up and pasted it for you into the WBS-Import-P6-Tutorial.xls on Worksheet 1. I have modified the original WBS to add a root node (SAMPLE the first entry in the WBS list) as the project name.

WBS.xls

Step 2 Split the WBS Path into Distinct Levels

Copy the column WBS Code from Worksheet 1 to Worksheet 2 in Excel. We will use the Text to Columns function on the Excel Data tab (Excel 2010), it a very easy tool to split one column by delimiters into multiple columns.

Highlight the WBS Code column, and click on Text to Columns.

When the wizard screen comes up, select Delimited, and click Next.

Set the delimiter to a dot . .

Make sure to format the columns as Text (if your code is 01, Excel will keep it 01 as text, otherwise it will change to 1.

Heres the result.

Step 3 Organize The WBS by Level.

Copy the new split columns to Worksheet 3 and paste each under the appropriate L1, L2, L3, L4 or L5 heading (use the yellow L columns, not the green ones). If you have more levels, just adjust the spreadsheet to add them in.

The Level column on Worksheet 3 is not needed for this tutorial. I have just added it to show that we can determine the level of each WBS by counting how many times the separator (or the delimiter) is repeated.

The green L-columns contain some text formulas that will recombine part of the WBS Path to build parent and children fields that well need for the import.

Step 4 Organize the WBS by Parent & Child

Were going to combine all of the green L-columns now on Worksheet 4. Start by copying the data under green-L1 and pasting it to the Parent column on Worksheet 4. Copy the green-L2 data and paste it to the Child column on Worksheet 4.

The next steps might seem unintuitive, but they are important to follow correctly. Well then clean things up afterwards. Copy the data as follows: Copy the green-L2 data again and this time paste it to the bottom of the data in the Parent column on Worksheet 4 Copy the green-L3 data and paste it to the bottom of the Child column on Worksheet 4 Copy the green-L3 data again and this paste it to the bottom of the Parent column Copy the green-L4 data and paste it to the bottom of the Child column Copy the green-L4 data again and this paste it to the bottom of the Parent column Copy the green-L5 data and paste it to the bottom of the Child column Essentially the column data should be combined like this:

Parent col L1 data L2 data L3 data L4 data

Child col L2 data L3 data L4 data L5 data

Actually thats the most important step in this tutorial, in order for the SDK (a relational database) to understand the structure of the WBS; this one must be presented as Parent and children table.

Step 5 Remove Duplicates

Copy the columns Parent and Child from Worksheet 4, and paste them to Worksheet 5. Highlight both columns. On the Excel Data tab, click Remove Duplicates to clean up any duplicate entries weve created.

Next, select ok

In the column check the same relation, I put a formula to check if there is the same item in parent and child. Click the column Filter and select only No. This will filter out any other entries we dont need for the import.

Step 6 Separating Child and Parent WBS Items

With the column filter still set to No highlight the Parent and Child columns, copy and paste them to Worksheet 6 as follows: Copy the Parent column and paste under Parent WBS ID Copy the Child column and paste under WBS ID CHILD The WBS Code column is automatically generated by removing from the WBS ID CHILD code the equivalent Parent WBS ID. Now our WBS is ready to be imported to Primavera P6.

Step 7 Open Primavera P6

In Admin -> Admin Preferences, check the maximum WBS Code length that Primavera P6 can accept. The default of 20 will work for this tutorial, however, you may want to increase this value to 30 or 40 characters for future imports.

Youll want to have an empty project already setup in P6 that we will import to. If you dont have one, login to Primavera and create an empty project now, then come back to this step. By default, the first level of the WBS is the name of the project. In this case change the name of the project to SAMPLE, once you finish importing you can modify the name.

Step 8 Open The P6 SDK WBS.xls

As usual input the password, and click on Update Project List. Select the empty project you created. **Make sure you select the right project. Click on Get WBS.

As expected, only the WBS root node is imported into the spreadsheet.

Copy and paste from the Excel file the columns Parent WBS ID, WBS Code and the WBS Name Dont overwrite the root node that is already there.

Click Set WBS.

It will take some time to process the data (the SDK is famous for very being slow).

Step 9 Enjoy

Wrap Up
We explained how to import a WBS dictionary to Primavera P6 using Excel and the P6 SDK. Here are some examples as to why it is very useful for planners:

1. It is very easy to import a standard WBS into your schedule (perhaps your company Cost breakdown Structure). 2. If your schedule is using activities codes, you can easily generate WBS in Excel and imported back to P6.

3. You can design a first cut of your WBS outside Primavera, using many freely available WBS utilities. Once it is approved, it can easily be imported to Primavera. We suggest you try WBStool, a mind mapping WBS tool. All WBS or mind mapping tool can export the WBS path to Excel, which means this file can be used as an integration tool to Primavera.

Things for you to try:

If you want to understand the logic behind all this data processing, google hierarchical database model. Using CTE recursive query in any Database you can generate the WBS Parent and WBS child in one operation. But if you are just a planner, SQL might be black magic for you (as it is for me). The approach discussed in this Tutorial will do the trick, and will you save a lot of time doing what all planners hate the most manual typing.

9 Votes

Mimoune Djouallah
Mimoune is a Project Controls Engineer with solid Planning and Cost experience in Oil/Gas EPC projects, Drop him a line at [email protected]

Download Tutorial Assets

Download the files to complete this tutorial on your own. Project Plan (.xer) : Apex xer.zip P6 SDK Excel Tool : Dictionary6v7.xls (Please note that this tool was created by Oracle Primavera and we do not provide support on its use) WBS to Activity Code xls : WBS-To-Activity-Code-P6.xls

The idea of this tutorial was inspired by a question in LinkedIn forum, a planner have received a primavera schedule using WBS, as in the oil and gas industry it is very useful to have different layouts to present the schedule to different audience, and more importantly the Work breakdown structure is based on geographic area in the construction phase, and systems in the commissioning phase, the tutorial will use the APEX sample schedule to convert a WBS to activities codes, it is assumed you have already worked with the Primavera P6 SDK.

Step 1 Open the schedule.

The APEX schedule organizedby the default classic WBS layout.

Step 2 Export WBS Dictionary to Excel

In P6, goto the WBS screen. Customize the columns so only WBS code, and WBS name are displayed.

Highlight all WBS rows, copy and paste the two columns to the worksheet WBS dictionary of the WBSto-Activity-Code-P6.xls file (see Tutorial Assets). Make sure that your WBS structure is well organized (*). It means every level must have the same exact number of characters. In this regards P3 was more rigorous, as the structure of the WBS is defined first. In P6, it is generated automatically. It is very easy to edit the WBS Code, to have the same number of character for every level. The excel formula in column F generates a unique list of the number of characters sort in ascending order, it is an array formula (thats why there is curly braces between the formula, if not, use CTRL+SHIFT+ENTER to execute it). In this example, WBS code with four characters -eg APEX, this is level 1. WBS Code with 7 characters eg APEX.AM, this is level 2. Of course, L1, L2 are just used as an example, in a real project, levels are usually defined as project, phase, sub-project, area, discipline, items, etc.

Step 3 Export WBS Activity Assignment to Excel

Remove the Group and Sort by WBS, in columns add WBS and Activity ID.

Copy and paste the two columns to the excel worksheet Activity codes per Activity in columns A and B. Excel columns C through G will be generated automatically, by default, if no code is available, a - is assigned.

Step 4 Create Activities Codes Dictionary and Import to P6

First define the new project activity codes in P6 that will represent the WBS.

Back in your excel file, goto the activity code dictionary worksheet. Using an excel pivot table, the excel file will generate an activities codes dictionary that we have to load back in P6 on this worksheet.

Using the dictionary excel utility (Dictionary6v7.xls instructions on using this file can be obtained fromhttp://support.oracle.com. Search for dictionary6v7.xls), click on Get Activity Codes (you need the SDK to be installed and configured).

/p> Copy and paste the activities dictionary from the excel file, for every level add a dummy - without a code description, so P6 will not show the ugly no code when you organize by activity code for activities without a code assigned.

Now you can import back to P6 the activities codes dictionary (click Set Activity Codes on the Login tab). Make sure you import to the right project.

Now your project is loaded with the activity codes dictionary.

Step 5 Import activities codes assignment to P6

Make a new Excel export. Select the newly defined activities codes (L1-L5) as columns in your layout.

Open the exported excel file.

Use a Vlookup formula to retrieve the values of L1, L2, L3, L4, L5 from the excel sheet Activity codes per Activity. In the Excel file, there is sample vlookup sheet to show how the formula works.

Import the updated export file back to P6. The WBS is now transfered to the L1-L5 project activity codes.

Step 6 Enjoy.
Now, with a new layout the schedule is organized by activity codes.

Wrap Up

Primavera P6 has very strong activity codes capabilities. Unfortunately, many planners use only the WBS as it is very easy and intuitive, but once you start tailoring the schedule, printing for a different purpose and audience, in a way that make a sense to them, activity codes should be used as Primavera only permits one WBS per project. (*) : if your WBS has not the same number of character for every level, you can use alternative method, explained here.

Reflection projects are a little known but very handy feature in Primaveras P6 Power Client. A Reflection is essentially a copy of a project but with one snazzy built-in trick the ability to merge the copied project back into the original at any time. yet, it will. This merging ability was designed to help planners work with scenario projects. Lets say youre the planner for a large capital project that is a falling behind schedule. The boss wants you to assess the impact of adding an extra shift to the work schedule. How much time can be caught up with this approach (ie: scenario)? The thing to do here is to create a Reflection of the project, add an extra shift and verify the finish date. If the boss approves the extra shift, then you can merge this scenario project into the original project along to keep the changes you made. This saves you the time of renaming projects, resetting permissions and copying and pasting changes from one project to another. There are also some nice options to help you create a backup of the original project in case you need to reference it again in the future. If the genius of this trick hasnt struck you

Step 1 Creating a Reflection of a project

From the Projects window, right-click on the original project and select Create Reflection from the menu.

If the original project has any baselines you will see pop-up window where you can select whether or not to copy those baselines along with the project. Simply mark the checkbox in the Copy column if you want to preserve any baselines in the Reflected project.

You should now see a copy of your project in the Projects window. The copy will have the same Project ID with -1 appended. This reflection project will also be marked with a Status of What-If which is Primaveras way of indicating the project is a scenario project and not a real project plan.

Step 2 Open the Reflection project and Activity Layout

Now its time to run your scenario. ie: make changes to the Reflection project to test out your scenario, hypothesis, etc. In this tutorial, well make a small change to the reflection projects resources. Open the Project and then Open the Activity Layout provided the tutorial downloads section.

Before we make any changes, youll need to make sure a key setting for working with resources is set correctly in User Preferences. Go to Edit -> User Preferences, and click on the Calculations Tab.

Set the Recalculate the Units, Duration, and Units/Time for existing assignments based on the activity Duration Type option. This will make sure that Primavera will perform the necessary

recalculations when resources are added or removed from an activity. Close the User Preferences window.

Step 3 Making changes to the Reflection project

Find Activity A1020 Convert Legacy Data. Youll be adding another resource to A1020 to see how this will affect the projects finish date. Since the activitys Duration Type is set to Fixed Units, adding an additional resource will have the effect of shortening the activitys duration and spreading the units equally over the 3 resources assigned. Lets give it a try.

Add Tom Bell as a new resource assignment to A1020. Reschedule the project using the current data date and check the projects finish date.

The project should now finish on Dec 20, 2010 whereas before it was scheduled to finish on March 1, 2010.

Step 3 Reviewing the Differences Report

Go back to the Projects window and right-click on the Reflection project. Choose Merge Reflection into Source Project from the menu. You should now see the Differences Report window. This screen is really nice. It provides you with a report on the differences between the original project and the reflection. From here you can choose which changes you want to merge into the original, or leave out by checking the box in the Merge column. Dont like the updates on a particular activity? Just leave them out. P6 also gives you some options to archive the original project, either as a baseline or as an .xer export file. This is essential as youll likely need to keep the original project around. And you can also choose how to handle the reflection keep it, delete it or replace it (update it) with the new merged project.

Step 4 Merging the Reflection into the original project plan

If you agree to merging the projects, you can go ahead and set the options as above, and click Merge Changes to finalize the merge. You may want to print this differences report first, or you might even copy and paste it into a text document before you Merge. You can do either of these by clicking the Display bar.

Wrap Up
The Reflection and merging features in Primavera are great features for the planner or scheduler who needs to run project scenarios or hypothetical analysis.

Things for you to try:

Try making more than 1 reflection of the same project to compare multiple scenarios

What else you should know about reflections:

the Reflections features are only available in the P6 Power Client and not in P6 Web Access A quote from the Primavera help file: Merging will not necessarily remove items from the source project that have been deleted from the reflection. If major elements of a project have been deleted from the reflection, you will have to manually delete them from the source project if you do not want to keep the elements in the source project. Elements that are not deleted during a merge include Activities, Resource Assignments, WBS elements, Work Products and Documents, Project Expenses, Project Issues, and Project Risks.

Download Tutorial Files

Project .xer and Layout [Reflections-Tutorial.zip] Dont know how to import our layouts? Read how here.

If you havent yet mastered all the shortcut keys and cant yet write a 200 word essay on what Store Period Performance does, then youre probably still on your way to becoming a Primavera SuperHero. To get your tights, cape and cowl, youll first have to master Primaveras Activity Layouts

which are an invaluable tool that you will use incessantly. The fact that you can combine column layout, filters, gantt chart options, and overall screen layout and save those setting for use again and again puts layouts on every planners utility belt. The best part, unless you work solo mio is that you can share your layouts with colleagues. In this post, well show you how layouts can be exported and emailed to a colleague who might work in a different Primavera database. PS: This is handy for those who have multiple P6 environments or work with external companies but need to look at the same data in the same way. But more importantly, youll need to know how to work with layouts so you can download the layouts we include in our tutorials here at www.plannertuts.com.

Importing a Layout in P5 / P6
1.

From the Activities window, open the Layouts dialog by clicking the View menu -> Layouts -

> Open. If you get a pop-up asking you to save changes to the current layout, select No (unless you have unsaved changes you want to capture).

2. Next, browse and select the .plf file youd like to import. 3. On the next screen, choose to rename the layout if you like, and how it will be available. Choose, from the Available To dropdown, to make the layout private (current user), global (all users), shared with selected users (another user) or saved in a project for users of that project (project). Click the Save button.

The layout is now imported. Go ahead and open it to view the wonders of your project through your new layout.

2. Exporting a Layout in P5 / P6
Exporting a Layout is just as easy. 1. From the Activities window, open the Layouts dialog by clicking the View menu -> Layouts -

> Open. If you get a pop-up asking you to save changes to the current layout, select No (unless you have unsaved changes you want to capture). 2. Select the Layout to export and click the Export button. 3. Choose what to name the file and where to save it. 4. Now you can email that file to your colleague or import it into another primavera database for use there as well.

Wrap up
Moving layouts around can be very helpful, especially when you need to transfer them across databases. Keep in mind that all of the steps above also work for layouts on the Projects, WBS, Resource Assignments, and Tracking windows. Faster is better. That should be Oracle-Primaveras mantra. Well, in this case it is! When exporting data to excel from P6, it can take a while to choose which columns to export and what filters to use when running the Export Wizard. Heres how to export your Activities list only in less than 10 mouse clicks.

Step 1 Open an Activities Layout

Go ahead and open a layout that has the appropriate columns and filters set already for your export.

Step 2 Use the Export to Excel right-click shortcut

Right-click on any activity on screen. Choose Export to Excel from the menu.

Step 3 Save the Excel file

Choose the appropriate folder and save the file.

Step 4 Open and Edit

Find and open the excel file. Edit, and reimport.

Wrap Up
This is one of those times where a shortcut can save you a significant amount of time. Using the File -> Export avenue to export data to Excel can take a some time and a lot of clicking to choose columns, filters and basically setup an reusable Excel export template. The right-click option is definitely faster.

However, if you need to export other Primavera data, like Relationships, Expenses, Resource Assignments, etc., then youll have to use the File -> Export command as the right-click command only exports the Activities list. For some reason, the right-click Export to Excel shortcut is only available from the Activities screen. AND its only available on the right-click menu, not the Edit menu. so youll have to practice it a few times until it becomes habit.

Things for you to try

If you export / import Activities data from excel regularly, set up an Activities Layout specifically for that purpose. Set the appropriate columns and filters(*see reference to gotcha below) in the Layout. Then you can quickly use the right-click Export to Excel shortcut to do the deed.

What else you should know

Filters Gotcha: rolling date filters do not work when exporting data to Excel in general. So be sure your layout does not use a rolling date filters. Heres a workaround to that gotcha. Primaveras custom Activity filters are great for focusing in on a subcontractors work list or even to hide Activities that are already completed. But when it comes to filtering down a 5000 line project plan to Activities in a specific timeframe, P6s filters fall flat. Heres the Gotcha: any filter that uses a rolling date or P5 / P6s built-in date variables will not work when exporting P6 data to Excel. What variables are you talking about? P6 has the following 6 built-in date variables that can be used when building custom filters: PS : Earliest Project Start PF : Latest Project Finish DD : Data Date CD : the current date (according to Windows) CW : the current week CM : the current month These variables come in real handy to create filters that display a rolling date range. With them, you can create the essential Lookahead layouts like 3 Week Lookahead which displays only the Activities starting within the next 3 weeks.

Lookahead filters are essential for short-term planning. They let you focus on work that is coming up soon. And with large projects, they are essential. Its common for a project planner to send a subcontractor an excel spreadsheet with all of his activities coming up in the next 3 weeks. But since the rolling filters dont work during an excel export, well need a new approach.

Workaround

The workaround is pretty simple. Copy the filter to create a version specifically for use when exporting data to excel. Replace the date variables with specific date values in your filter for the current time period. Proceed with the export to excel.

Although youll have to edit the dates in this filter over and over during the project , its much easier to filter Activities in P6 than it is to try to filter them in Excel.

Occasionally we revisit some of our readers favourite posts from the annals of the PlannerTuts tutorial library, and today were revisiting a topic that is always hot importing updates from excel. This video tutorial was first published on September 30th, 2011 and was very popular. Lets take a look! Often large projects demand many sub-teams or subcontractors update a portion of the master project plan on an on-going basis. If your subcontractors are not up to speed on their primavera skills, you may need a solution that simplifies gathering updates and getting them into Primavera. Enter Excel.and Primaveras export- to-excel feature. This video tutorial will walk you through the process of filtering and exporting a subcontractors activities to excel and reimporting the updated spreadsheet into P6.