Thursday Nov 19, 2015

Top Questions to Answer Before Setting Up Primavera Analtyics

The questions below are aimed at larger installations but can also be applicable to single data source installations.  The purpose of thinking about these areas before installing and implementing is
to prepare your environment for expansion, to prevent running into scenarios where you are capturing data you don't require, and to prevent creating an environment that is not suitable to your
business needs and may need to be rebuilt.

#1-  How many data sources do I plan to have?
 Even if you don't know for sure but think you might have 2 to 3 (for example), plan for it.  Better to overestimate than underestimate. Overestimating by a few will leave those slots partitioned and available for future usage. Partitioning is required if using multiple data sources.

#2-  Partitioning ranges
This can effect performance.  It is not recommended setting a large partitioning range (for example - 6 months) if you are capturing a large amount of lower level historical data (SCD's and Activity History).  Partitioning is designed to help with performance by querying the data in that partition rather than querying all data if it can help it. Setting partitions equal to 1 month might be
better for a specific environment based on size and historical capturing frequency.

#3-  Activity Daily History
How many projects have this on option set?  This can heavily effect performance and storage. It is recommended that this is only on for a smaller percentage of projects and then turned off when the need for this level of detail has passed. This has other cascading effects outside of just history capture.  This will also turn on slowly changing dimensions for any data related to the project. Slowly changing dimensions will capture any change on objects that support SCD's.  By default Activity History is not on.  It is recommended to opt in a few projects at a time based on requirements.

#4-  What is the P6 Extended Schema data range?
If P6 is your primary data source what is the date range set on the project publication. A large date range represents a larger set of spread data that would need to be pulled over.
Generally this range is 1 year in the past and 2 years in the future. What this means is you will have daily spread rows for each day for each activity and resource assignment spread bucket for each activity in each project that is opted in.  That can become a large amount of rows quickly. If you have units or costs outside this range that data is not lost and it will come over to Analytics however
you just won't have a daily bucket for that data.  The data outside of the range is lumped onto the beginning or end of the date range.  This way the data is still represented in the totals.

#5-  What are my data requirements, how often do I need my data refreshed and at what level of granularity? 
Decisions based on these questions will shape your entire Analytics environment and Primavera
ecosystem. For example if you have 10 data sources, do you need to report at Activity level and have those Analysis' up to date every day?  
Do you also require global reporting for all 10 data sources but those reports are only run weekly? 
A scenario like this would lend itself to each data source having their own individual STAR with it's own ETL and then a master environment created to pull from all
10 data sources on a weekly interval for global reporting.  The advantages of this are that each individual data source can then get an ETL run in each day.  If all 10 data sources per pumping into the same  STAR and each ETL took 2 hoursto run it would take 20 total hours before ETL #1 could be run again.  Having this global version (while still pulling out of the same data sources) allows for individual sites  to have their own ETL rules while still having a consolidated global data warehouse which is updated once a week for each ETL.

Monday Nov 02, 2015

Refreshing RPD Translation Changes Without Restarting OBI Services

In Primavera Analytics when making changes to the selected UDFs, Codes, and other dynamic fields that come across generally the OBI services would need to be restarted to see these changes reflected. The reason being these fields that are being altered actually have underlying translation values and those are what need to be updated. Translations are managed differently compared to making a change to an existing field in the RPD. The following steps cover how to disable the caching on the RPD initialization block for translations. This allows for translation updates to be seen in OBIEE without requiring a full restart of the BI Server.

Steps for Updating RPD and Refreshing Translations

1. Open the RPD using the BI Admin Tool, in Online Mode.

2. From the Manage menu, select Variables.


3. Under
Session, select Initialization Blocks, then double-click on the External Metadata Strings initialization block.


4. In the
Variable Target section, click Edit Data Target.


5. Uncheck the
Use Caching option, under Row-wise initialization, then click OK

6. Save the RPD changes. When prompted to check-in changes, click OK. When prompted to check global consistency, click No.

7. This example tests mapping a new Activity UDF. First log into OBIEE and create a new analysis using the Primavera - Activity subject area. Expand the Text UDFs - (Activity) folder and note the position of the first unmapped text type activity UDF, in this case Text Activity UDF 6.

8. Run the configutation utility (<ETL_HOME>/, and on the UDF mapping screen add a 6th Activity UDF. Finish the configuration utility, then run the ETL.

9. Once the ETL has completed, log into OBIEE and again create an analysis using the Primavera - Activity subject area. From the refresh drop-down, select Reload Server Metadata.

10. Once that completes, expand the
Text UDFs - (Activity) folder and you will see that the translation for the newly added Activity UDF is now displayed.

Thursday Oct 08, 2015

User Defined Field Fact History

New to Analytics 15.2 release is the addition of historical facts and subject areas for user defined fields fact data such as project, wbs, activity, resource, and resource assignment for their cost
and number udf's.  For these UDF's to be available they will have had to been selected either from configStar or from the Analytics web configuration utility and have the UDF's and opted in.
Opting them in means that history will now be recorded on them. History is captured at different intervals depending on the object.

Because Resource is a global object, the history interval is always set to Week.
For Activity and Resource Assignment UDF's they will be turned on for daily capture based on the setting on the Project associated with this object. The project will need to be opted in with activity daily
history and then so will these UDFs for these objects.
Project and WBS history interval will also be determined by the history interval setting set on this project for the Project\WBS history setting.  The lowest level of granularity here is Weekly.

I Added a User in P6, How To Make Sure That User Gets into Analytics

A common question is what is the data flow for adding a user and getting that user into Primavera Analytics. For this example we are going to follow the P6 user flow.

1- Log into P6 application and add new user.  Give this user module access for P6 Analytics.  Assign Project Access and Responsible Manager\OBS to this user.
2- Run the Global Services Security Service.
3- Run StarETL process.
4- Make sure user exists on OBI side.

 If you are using LDAP or SSO you want your OBI server to be configured to the same.  In Primavera Analytics logging into OBI you want the username to match exactly as it is in p6.
 Make sure there is a user in your OBI, Weblogic, LDAP, or selected security model you are using, with the same name as p6.

The names need to match exactly because there is a security package being loaded as a predefined process in OBI with that selected data source.  

From here the user should match what is in P6 and see the same data as in P6. The global security service is a key component here. The Primavera Analytics data is pulled directly from the extended
schema so you need to make sure this service has run. It is set on a defined schedule. It is recommended to set this schedule to run and finish before the ETL process runs. Keeping your global
services scheduled at the appropriate time when they will finish before the ETL and capture all changes is key to keeping a fully update Primavera Analytics environment.

Tuesday Sep 15, 2015

History Intervals for Unifier Data in Analytics 15.1

In Primavera Analytics\Data Warehouse 15.1 Unifier was added as a possible source database.  There were a few subject areas added specific to Unifier:

Business Process, Cash Flow, and Cost Sheet.

There are also historical versions of these subject areas. The historical versions of these areas are captured by default (there is not an on/off switch in Unifier) and are captured always at a weekly interval. In Unifier there is not an optional start day of the week, so the begining of the week will always be Sunday.


Tuesday Sep 01, 2015

Can I Include P6 and Unifier Data in a Single STAR Schema?

In Primavera Data Warehouse 15.1 Unifier was added as an optional data source. There are certain subject areas in Primavera Analytics that are geared towards Unifier data - example - Cash Flow, and certain subject areas geared towards P6- example - BurnDown.  If you are running an ETL with only P6 or Unifier those subject areas that are specific to the other product may be empty, this is ok. There are some dimensions that are common - such as Project.  When we use common dimensions, adding a field from that dimension (Project) in one of these other subject areas (Primavera- Activity) will return data, but once a fact is added to the Analysis the data will be filtered out based on the joins in the RPD.

If you have an existing Primavera Data Warehouse installation with P6 as your data source and you would like to add a Unifier source the steps are similar to adding an additional data source.  Besides some different options in the configuration utility there is really no difference with the ETL or the storage of the data.  By keeping each installation to it's own data source you could have any number of combinations of installation types or orders of installation (example - DS1 = P6, DS2 = Unifier, DS3 = P6, etc. )

When P6 and Unifier are sharing common data (Project Id) the data can be combined from different subject areas or aggregated or grouped together. This gives you the ability to combine P6 and Unifier data in certain Analysis.

Tuesday Aug 25, 2015

Link of Slowly Changing Dimensions and Activity (Daily) History

Slowly Changing Dimensions on specific projects is activated in P6 by setting the History Level to Activity.  Setting the History Level to Activity also triggers a daily recording of each activity for that
project in the historical tables.  This can trigger a large amount of database growth depending on the amount of projects that have activity level history activated on.

In the Primavera Data Warehouse, during the ETL process there are certain steps where all contents of the folder are executed.  If a script is removed from this folder it would not be executed. If you
are interested in using slowly changing dimensions for capturing changes on a specific project but have no plans to use activity level history you could remove the merge_activity_history.sql script from the \staretl\scripts\hist_merge folder.  If you did so no activity history will be captured and written to w_activity_history_f.  This script could be added back in, or rerunning config or runSubstitution would add the script back from the \etl\oracle\templates\hist_merge folder.

Wednesday Aug 12, 2015

P6 Global Security Service... More Info, How to Run a 'Full'

If you are using the Primavera Data Warehouse (previously Primavera Reporting Database) and pulling data from P6 then the Global and Project publication services are your life line for data. If those services are not running your data in Primavera Data Warehouse\Analytics will be out of date. If you encounter an issue where you Security service seems to be failing repeatedly, which can be caused by bad data (such as duplicate rows in USERS table), it may be appropriate to force a 'Full.' 

By forcing a 'full' update you are essentially telling the global services that security has never been run and to calculate everything again. The extended schema is all based on update dates. It will compare for related tables and compare those rows with entries in the SETTINGS table.  To make it recalculate everything you would remove the related rows in Settings.  Be very careful here, removing all rows from Settings would cause a global wide Full which may take many many hours to run and use server resources to accomplish and could have an impact on P6.  Delete only what is necessary.

In this example we are going to do a 'Full'  for security.  Here are the steps:

1- Connect as ADMUSER and run the following: 

delete from SETTINGS where NAMESPACE in (









2- Run the global service for Security.  Be prepared this will take much longer than the usual daily run as it will be recalculating all security. 

After the service has been completed run your ETL process. This way you will have all the latest on both the extended schema and data warehouse\Analytics side. 



Tuesday Jun 09, 2015

Upgrading Older Analytics\Data Warehouse Versions

This blog will discuss upgrading from P6 Reporting Database version 3.0 all the way to Primavera Data Warehouse 15.1.   

There are several releases between 3.0 and 15.1 (3.1, 3.2, 3.3).  Generally upgrades are created for up to two releases back. There were a few major changes along the way which make it necessary to do intermediate upgrades if you are trying to come directly from 3.0 to 15.1.

In version 3.1 there was the addition of Slowly Changing Dimensions.  In version 3.2 sp1 there was a switch in the type of partitioning from range to interval, also partitioning was now applied to the Slowly Changing Dimension tables.  In 3.4 we introduced a new upgradeSchema path for upgrades which makes it possible to just upgrade the STAR schema with necessary alterations and then allow the normal ETL to run as it has been scheduled.  This is in place of the upgrade.bat or .sh, which does full table backups and reinserts of history and other data.  This upgrade process generally takes longer than a normal ETL run. In 15.1 there was the introduction of Unifier as a source into the data warehouse.

The ideal upgrade path if coming from 3.0 to 15.1 is:
3.0 --> 3.2 (to cover slowly changing dimension infrastructure)
3.2 --> 3.3 (to cover partitioning changes)
3.3 --> 15.1 (the easiest upgrade is to use Upgrade schema method.  There are two to run depending on the version you are coming from - upgradeSchema33 and upgradeSchema34.  In this path run upgradeSchema33)

Friday May 15, 2015

Parallel Loading

The Primavera Data Warehouse uses parallel loading of tables in several steps.  If you look in the \scripts folder you'll notice in the dim_load folder there are several scripts.  Those will load data to different dimension tables and could run in parallel as long as not blocked (the amount of threads can be turned down in properties file which would stop parallel processing, also a priority can be set on the order of the scripts).

This also depends on the database and how many processes the DB instance can allow. If this instance is used for more than just the Primavera Data Warehouse these could be used up. If you begin to see errors like
ORA-12516: TNS:listener could not find available handler with matching protocol stack
showing up for multiple scripts in the same step the likely cause is processes.

Have your DBA check the number of processes (show parameter processes;).  If this is around 100-200 you could run into an issue (depending on what else this instance is being used for). The DBA can then increase the number of processes - try 600 as a starting point. (alter system set processes=600 scope spfile;) then restart your instance. After this increase the ETL process should have enough processes and complete.


Wednesday Apr 22, 2015

Spreads in Primavera Analytics

We get a lot of questions about spreads and how they are handled in Primavera Analytics. Spreads are really the core of P6 resource assignment and planning. 

What are Spreads? In a simple way, Spreads are the distribution of units and costs over the life of an activity or resource assignment. This includes both the early dates (forward scheduling pass) and late dates (backward scheduling pass). Spreads can include curves and calendars, but we will leave those for another time.

If we look at the following example in P6 EPPM:

Activity ID: MN1000 --- Assigned Resource:Automation System Engineer

Assignment Remaining Early Start Date: 01-MAY-2015 --- Early Finish: 11-MAY-2015

Assignment Remaining Late Start Date: 27-JUL-2015 --- Late Finish: 31-JUL-2015

Remaining Units: 6.78d

Below we look at the same Activity, MN1000, in Primavera Analytics. We see that the Remaining Units are being "spread" or distributed over the Early and Late Dates accordingly. We use Remaining Units for spreads over Early Dates (01-MAY-2015 to 11-MAY-2015) and Remaining Late Units for spreads over Late Dates (23-JUL-2015 to 31-JUL-2015).

For Remaining Labor Units, we have 8 hours per day, distributed between May 1, 2015 and May 8, 2015. May 2, May 3, May 9 and May 10th, 2015 are not shown because they are non-work time weekend days, and the remaining 6.20 hours are on May 11, 2015.

For Remaining Late Labor Units, we have 8 hours per day, distributed between July 31, 2015 and July 23, 2015 (backwards since this is the backward pass of the schedule). July 25, and July 26 are not shown because they are non-work time weekend days, and the remaining 6.20 hours appear on July 23, 2015.

Friday Apr 17, 2015

Having Trouble Publishing Projects?

Because the P6 Extended Schema is the lifeline of data for Primavera Analytics it is an important topic to cover.
There are two white papers which go into depths about the feature, how to setup, how it works, and some advanced configurations and techniques.

The key to having project publish is a job service called the Arbiter. If you query the jobsvc table as admuser you will see this job.

select * from jobsvc where job_type = 'JT_ProjectArbiter';

When this job runs it looks through your projects to see which ones have changes, which have crossed the time threshold, and which have been marked as 'Publish Now' and
adds these projects to the queue.  Without this service running no projects would be published.

In a scenario where your database has crashed or other environmental issues may cause this job to be stuck in a running or bad state you will want to get it running again as quickly as
possible. One way would be to put a monitor on this row and trigger a database alert if it is stuck in running state for an extended period of time or does not run on the designated schedule.

If the arbiter is running ok but some projects might not be getting processed there are a few reasons:

1- There are no changes on the project so there is no need to process it.  As discussed in an earlier blog not all changes on the project count as a change, changing a code or udf assignment wouldn't count as a change.

2- There is a setting called 'Publish Idle Projects.'  This really only occurs when you first turn on publication.  At that time it will go through all projects with enable publication
checked and process them, when it is completed this is turned off.  It is turned off by altering a value - ProjectNonDemandStopKey - in the Settings table to zero. If this setting
is zero then new projects will not be published until they have changes, have been scheduled, or have been opted in by choosing 'Publish Now.'  You can turn this back on from the database
by running the following as admuser:

update SETTINGS set setting_value = (select max(proj_id) from project)
where SETTING_NAME = 'ProjectNonDemandStopKey';

This will force the arbiter to go through and add any unpublished projects into the queue.  Once it has completed those projects it will set itself back to zero and turn off.

3- Is a rare scenario where none of the above conditions have been met and you just want to force in all unpublished projects.  You could run the following:

update project set px_next_date = sysdate where proj_id in
(select proj_id from project
and PX_LAST_UPDATE_DATE is null;

We will have more blogs discussing the Project and Global publication services. This one is intended to give a little more background on the services.

Monday Mar 23, 2015

When Configuring Multiple Data Sources...

When you are setting up additional data sources to be applied to your STAR schema (For example - multiple P6 databases being combined into a single STAR schema) there are a few things you need to make sure are configured.

1- New installation directory
    For this new data source it should have it's own installation directory with a new folder name (especially if on the same machine).
    Edit the following files and change the path for the new staretl directory:
        config.cmd or sh
        \etl\common\runSubstitution.cmd or sh
        \res\ - change any paths that are incorrect. If there is a filter define it for DS2 (star.project.filter.ds2=filtername).
        staretl.bat or sh
            -add the data source number into this file so each time it executes it runs with the proper data source
                call staretl.bat "-s2" %1....        

    In the scripts folder, locate staretl.bat or .sh. Edit the file, search for 'primary_link', remove the step where you find this from staretl then save.  The reason for this is the script this step runs drops and recreates database link 01 each time.  It also has parameters which could be populated with another source if executed.

2- Create new database link - DSLINK02
    Create a new database link called DSLINK02 for the 2nd data source. The same pattern would repeat for additional data sources.  This new DSLINK should have the connection defined to the PXRPTUSER or filtered view for this new source.

It is critical to make sure these steps are followed when creating a new datasource and running the ETL processes. If steps are missing the ETL's could run against the wrong source for the specific data source. These steps are also documented in the Installation and Configuration guide.

Friday Mar 20, 2015

I Created an ETL Schedule Through the Web Configuration, How Do I turn the Schedule Off?

In P6 Analytics 3.3 a web configuration utility that can be deployed in Weblogic was introduced.  Through this utility you can define an ETL schedule or run the ETL on demand. This web utility will also create a queue to prevent ETL processes from colliding. If you have multiple data sources you can define a schedule for each and the queue will be built to so the ETL process will wait for the previous or current ETL to finish before starting the next.
When creating the schedule you can choose to run daily at a designated time or define which days of the week to run and at what time each day. When you define your schedule it is actually written into your ETL installation directory (staretl\res) in a properties file ( Any changes you make to your schedule will be reflected here.  You can only have one schedule per ETL installation.

What if you created a schedule to run every week on Monday, Wednesday, Friday at 10pm and now you want to run just daily?
All you need to do is make a change on the Schedule page in the web application. By unchecking weekly and selecting daily, selecting your time and clicking 'Schedule ETL' you will have overwritten the previous schedule.  You can verify this by checking the

What if you want to turn off all scheduled ETL's?
You can choose Weekly and leave all days unchecked.  This will write to the schedule.propertie to turn off for all options, daily and each day.

Monday Jan 26, 2015

Summary Data and the Summarizer, What is Needed for P6 Analytics?

In P6 there is the Summarizer, which runs scheduled jobs to do calculations and aggregations of data. This data is visible when looking at Projects and EPS in the P6 client or web applications.
There are also the Global Services associated with the Extended Schema. In the Global Services there are jobs for Enterprise Summaries, Enterprise Data, Resource data, and Security. These jobs are completely separate and do not depend on or use any data from the Summarizer. The Summarizer and P6 Extended Schema services are two completely separate entities. P6 Analytics only uses the data coming from the Extended Schema, therefore it is not necessary to run the Summarizer to populate data for P6 Analytics. Only the Global services and Project Publication services need to be run.


Provide new information on Primavera Analytics and Data Warehouse


« November 2015