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 (

'PxService.ProfilePrivilege.PxProfilePrivilege.Task',

'PxService.User.PxUser.Task',

'PxService.User.adminusersecurity.Sql.Task',

'PxService.User.baseline_security.Sql.Task',

'PxService.User.users.Sql.Task',

'PxService.User.usersecurity.Sql.Task',

'PxService.UserObs.userobs.Sql.Task');

Commit;

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.

http://docs.oracle.com/cd/E20686_01/English/Technical_Documentation/Reporting_Database/P6%20Extended%20Schema%20White%20Paper.pdf

http://www.oracle.com/webfolder/technetwork/tutorials/primavera/OnlineLearning/WhitePapers/P6_Extended_Schema_Advanced_Techniques.pdf


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
where PX_ENABLE_PUBLICATION_FLAG = 'Y'
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 director
    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\staretl.properties - 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 (schedule.properties). 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 schedule.properties.

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.

Friday Jan 09, 2015

Capturing Historical Financial Periods for Analytics

In P6 Analytics you can use Financial Periods as an element of time similar to Week, Month, Year, etc.  Financial Periods are defined in P6.  They can be a week, two weeks, a month, it is a user defined period.
The financial periods are available where the time dimension is available but can react differently dependant on the subject area that is being used. In this blog we will cover two main areas where this might be used and how the two subject areas will work differently.

Primavera - Activity

This subject area is not historical.  This data is as of the last ETL run. The data in this subject area comes from the Activity Spreads.  If you have spread data that falls into these financial period buckets then it will be shown.  If you do not then certain financial periods will not be shown for this project. Remember once a fact is added into the Analysis, or you join to another dimension hence joining to the fact table, data will be filtered out to honor the data in the fact tables.  Analytics is fact driven. You must have a fact to fit the criteria for it to be displayed.




Primavera - Project History

This subject is historical.  As we discussed with the Primavera - Activity subject area, this is still fact driven.  The change in behavior is that these facts are all about being historically captured, this is NOT using spread data it is only using historical data. For example, you have a project that ranges from 1/1/2013 to 1/1/2016.  You have financial periods established for every two weeks during this time period.  Today is Jan 9th, 2015.  If you are just setting up P6 Analytics today and have never captured any history up to this point when you run the ETL process you will only see data for the financial period of Jan 1 - Jan 15th 2015.  The historical facts are added into the specified buckets of the day the ETL is run and what bucket that falls into.  If you have history for Week, Month, Quarter, and a Financial Period (every two weeks) you would see in the w_project_history_f a period_start and period_end_date like the image below:




Because you only have facts for those time periods, you would only see those time periods were historical data was captured. If you only have history captured starting today (Jan 9th) you would only see data that falls into that bucket.  As time goes and you continue to capture history you will see additional buckets of history be stored and made available for viewing. 


Thing to remember

Here are a few items to remember to do in P6 to make sure your projects are setup to capture history for financial periods. In the P6 Extended Schema remember if you are defining new financial period buckets to run the Enterprise Data global service so this data is calculated and stored in the Extended Schema
and available to be pulled over during the ETL. Projects must be published to calculate the spread data and changes to the project so they can be pulled over into STAR schema and available in the Primavera - Activity and Primavera - Project History subject areas. History must be enabled for the projects where you want to have history recorded.  This is off by default. When setting the history interval it will need to be set to Financial Periods.

Monday Dec 22, 2014

How Could I Get a Project to Publish After a Certain Amount of Activity Code Changes?

Following up on the previous blog discussing why making a change to a code assignment isn't enough to trigger the publication of a project, here is an option if there is a business need around a certain P6 object that gets updated often and is considered as important as the tables we currently count changes for (PROJWBS (WBS rows), TASK (Activities), TASKRSRC (Resource Assignments), and TASKPRED (Relationships)) which determine if a project needs to be refreshed.  

Say activity codes are changed very often on your projects. Maybe this is a way of moving activities into different phases or an important statusing practice. Because the Project publication infrastructure is based on changes, you would want to queue up a project based on changes as well. In this SQL example below we are going to update the px_next_date on the row for this project in the PROJECT table.  Px_next_date when updated with a date later than the last run of the arbiter (project publication) service, it will cause this project to be added to the queue.


update project set px_next_date = sysdate where proj_id in
--timestamp, and trigger a publication for projects in the following query
(select p.proj_id FROM
(select proj_id,  count(*) COUNT from taskactv
where update_date > (sysdate -1)
--in this example where there has been an update in the last day, this could be extended to 7 days or any other time choice
group by proj_id
order by proj_id) p
where p.COUNT > 50)
---when there is a total of 50+ changes publish this project


In this example we are saying at the time of running this SQL, go get a count (per project) for the amount of activity code updates within the last 1 day. And if that count is greater than 50 mark this project to be published.  You could change the numbers to better fit your needs.  You could do a similar update on other project
related objects, this is just a high level example (this is not meant to be production code).

Thursday Dec 11, 2014

Why Aren't My Code Changes Being Reflected in Analytics?

The scenario is, you logged into P6, you made a series of activity code changes on your project.  An hour or two later you ran the ETL process, expecting to see the codes changes reflected when you viewed them in OBI or directly in your Star schema.  However they are not updated, why?

Let's walk through the process of how the data gets there and why codes are different than updating activities directly.

Step 1:  Project Publication - what counts as a change.

You are dependent on the thresholds being crossed for updating projects in the Extended schema (See earlier blog for more info on thresholds). Basically changes to a project are counted, then if you reach the threshold (say 100 changes) or the time threshold (say 24hrs. and 1 change) the project will be published.  However items like code assignments do not count as a change. The only updated areas that count as a change are updates to the following tables:

PROJWBS (WBS rows), TASK (Activities), TASKRSRC (Resource Assignments), and TASKPRED (Relationships)

Step 2: Publishing the Project

The design behind this is these are the most commonly updated areas in a schedule.  To throttle the system and avoid overloading the queue with unnecessary updates these are the core areas that drive updates.  If you are making major changes to codes it is good to be aware of this and you can choose to 'Publish Now' which will then force a publication of your project and capture your code changes.

Step 3: Run the ETL

OK now that we have the Extended Schema (Project publication) side understood and worked out you can run the ETL as normal.  Remember the Extended Schema is the heart of all the data that makes it into P6 Analytics.  P6 Analytics and your STAR schema is only as up to date as your Extended Schema.  

Friday Nov 21, 2014

History Selections (Part 2)

In the last blog we discussed how the history selections you make can effect the size of your Star schema.  Now let's talk about how we make the selections and what they mean.  In P6 go to Projects, then the EPS view.  Right click and choose Project Preferences, and click on Analytics.

Here you will be presented with History Level and History Interval.   History Level is the most important setting here.

Choosing Activity:

-This will populate daily activity level history for the Primavera-Activity History subject area.  This is always daily.  There is no setting for Activity History to be a granularity other than daily.

-When you choose Activity History the History Interval drop down is still available.  This is for choosing the granularity for WBS and Project History, which are turned on by default when you choose Activity Level History.  

-Slowly Changing Dimensions will be turned on for changes to this Project.

Choosing WBS:

-If you choose WBS for History Interval this will also turn on Project Level History and will make these available in the Primavera -Project History subject area.

-The History Interval selection will now affect both WBS and Project Level History.

-Activity Level history will be off as well as Slowly Changing Dimensions. 

Choosing Project: 

-If you choose Project for History Interval this will turn on only Project Level History and will make it available in the Primavera -Project History subject area.

-The History Interval selection will now Project Level History.

-Activity and WBS Level history will be off as well as Slowly Changing Dimensions. 

Thursday Oct 30, 2014

How Historical Selections Effects the Size of Star Schema?

For P6 Reporting Database, the Planning and Sizing guide covers expectations for the size of your Star schema based on the size of your P6 PMDB and gives recommendations.  History is discussed in this document as well, but one thing to go a little deeper on is how the selection of history per project can have an effect on your ETL process as well as the size of your Star schema.

If you select Activity History on a project this will:
- record any change on the Project, Activity, and any related Dimension each time the ETL is run (ex. change in activity name). This enables Slowly Changing Dimensions for rows related to this project.
- Activity history will be recorded in the w_activity_history_f on a daily level.  A row will be captured for each activity for each day the ETL process is run. Depending on the amount of projects you have opted in this could grow quite quickly.  This is why partitioning is a requirement if you are using Activity level history.  
- Choosing Activity level history also opts you in for WBS and Project level history at the selected granularity (Weekly, Monthly). Recording a row for each WBS and Project each time the ETL process is run and bucketing the results into the chosen granularity.

Because choosing Activity History can have a such a dramatic effect on the size and growth of your Star Schema it is suggested that this be a small subset of your projects. Be aware of your partitioning selections as this will help with performance.

For determining the amount of rows for Activity History, take number of projects opted in times number of activities in each project times the number of days in the duration of the project times number of ETL runs. This will give you a ballpark idea on the amount of rows that will be added per project.  A similar calculation can be done for WBS history as well, adjusting for number of WBS rows and selected granularity. 

In summary, be cautious of turning on Activity History on a project unless you absolutely need daily level history per activity.  If the project is on, once the project has ended turn off the Activity History on the project.  Keeping project controls accurate and up to date can help improve the ETL process and control Star schema. 

Friday Oct 03, 2014

P6 Analytics and P6 Reporting Database Security Enforcement

In P6 Analytics row level security is used to enforce data security. The security is calculated in P6 by the extended schema global security service. This means that P6 Analytics uses the same security defined in P6. If you gave a user access to a project in P6 they would have access to it in P6 Analytics. It is calculated, stored, and used across both products.  The calculated security data for projects, resources, and costs are pulled over during the ETL process. This is stored in the STAR schema and when querying the STAR schema for data using OBI there is a validation of the user in OBI against a user that should exist from P6. When the user is identified the data is filter based on the calculated out data they have access to. This is handled using a package in the STAR schema - SECPAC. Security policies for row level security are applied during the ETL process and can be found in the \scripts folder. This enables enforcement across the Facts and Dimensions. Using OBI and allowing the ETL process to handle the enforcement is the easiest way.

However, if you do not use OBI but still wanted to use the STAR schema for data and use the security enforcement you can still use just the P6 Reporting Database.  You would setup your STAR schema and ETL process just as you would if you had the full P6 Analytics.  Once the ETL is completed you will need to execute the SECPAC and pass a username to set the context. In a session with this defined, this user would only see the data they had access to as defined in P6. This would be an additional step and need to be pre-defined in any process to access the STAR data.  Using OBI is a much easier option and you can take advantage of all the benefits of OBI in creating Analysis and Dashboards but it is still possible to setup STAR and use the calculated P6 security even without OBI in your environment.


About

Provide new information on Primavera Analytics and Data Warehouse

Search

Categories
Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
13
14
15
16
17
18
19
20
21
22
23
24
26
27
28
29
30
31
     
Today