Friday Jun 14, 2013
Thursday Jun 13, 2013
By Jeffrey McDaniel on Jun 13, 2013
In P6 Reporting Database and P6 Analytics versions 3.1 a metadata feature was added to allow for altering of calculations and a way to add new calculations. There is already an existing path for adding new scripts - user_scripts. User_scripts will allow you to establish new steps in the ETL process such as - adding additional tables or fields and custom calculations to those new objects. The metadata feature allows you to change calculations on existing fields in historical, burndown, or workplanning fact tables.
See documentation for more information:
Validating the metadata before running the ETL is key. If the changes you added will fail, the ETL process will revert back to a default calculation for all metadata calculations. For example, you change the metadata calculation on two fields. One of the calculations causes a failure, both will revert back to the default calculation column for what to run. Here are the steps to validate your metadata calculations:
- After making change. Run StarETL for steps 17 through 20
- Linux: staretl.sh -from 17 -to 20
- Windows: staretl.bat 17 20
- Check the log output for any errors or warnings.
A few more items to be aware of:
- Calculations are per data warehouse/not per data source
- Drop STARUSER and you will lose all custom calculations
- Metadata calculations are stored in the ETL_CALCULATIONS table
Thursday May 23, 2013
By Jeffrey McDaniel on May 23, 2013
This blog applies primarily to P6 Analytics\P6 Reporting Database 3.1. As of 3.1, history was introduced at a daily level at the activity level. Activity level history existed as of P6 Analytics 2.0\P6 Reporting Database 3.0 but was only at the interval level of week, or higher. The settings in P6 for history level and history interval haven't really changed too much, but their meaning has changed. These history settings are on the project level and are defined project by project. This gives flexibility to include lower levels of history for ONLY those projects that require it. These settings are defined on the Project Preferences section of P6.
Choosing activity level history can cause your data warehouse to grow extensively based on the amount of changes and size of projects. If setting projects on activity level history you should first review the Planning and Sizing guide and make sure you have the required space of growth.
If you choose history level = Activity (internally HL_Task), then this project now has Daily history turned on as well as:
If you choose Project or WBS level history the history interval setting would apply. History always rounds up. If you choose Activity, you automatically are opted in for Project and WBS history also. If you choose WBS history you get Project history as well.
Friday May 03, 2013
By Jeffrey McDaniel on May 03, 2013
As you know the Extended Schema services (Project service specifically) runs on a designated time interval based on your Publish Project settings. This could be set to run every 1 min (default), and at this time the 'arbiter' runs and determines which projects need to be queued up to be processed. The arbiter determines this by evaluating each project that has PX_ENABLE_PUBLICATION_FLAG set to 'Y', and threshold of changes on this project or the threshold of time has been crossed. In your database you can see how many changes there are on the specific project by reviewing your PLPROJREF table in the Admuser schema.
Another reason why a project would be queued up is because you right clicked on the project and choose Publish Now. When you choose publish now what actually occurs is a time stamp is made on the PX_NEXT_DATE field for that project row in the PROJECT table. In the application there is not a way to separate out projects into different batches that may follow different publish project settings. The design behind this was to allow data to continually be updating in the system at these different times when the project may have an appropriate amount of changes, time has passed, or you need it now then to calculate the project. Let the system take care of itself and keep the data up to date in a near real time environment. On the P6 Analytics\P6 Reporting Database side controls were added such as filters, and additional data sources to allow you to control what projects get pulled over and possibly setting them up on different ETL runs.
Friday Apr 26, 2013
By Shawn Lafferty on Apr 26, 2013
As an add on post to Jeff’s previous blog regarding how to add additional fields from P6 EPPM to the STAR schema, it may be necessary to add the new fields to your RPD file so they appear within P6 Analytics in Oracle Business Intelligence.
Adding new fields to the RPD field is a pretty straight forward process, assuming that the new fields already exist in the STAR schema. The process involves using the Oracle BI Administration tool to modify the OraclePrimaveraAnayltics.rpd file, manually adding the field to the Physical layer, then dragging it to the Business Layer and finally to the Presentation Layer.
Two recommendations before we get started. First, I would recommend that you make a copy of your existing RPD file prior to moving forward here. Even though the newest version of Oracle Business Intelligence handles this for you with auto RPD iterations, I still think it’s a good idea to keep a good “working” copy of the RPD in a safe place just in case.
Second, I would recommend doing this type of RPD manipulation in offline mode. Although it is quite possible that you could get through it all having the RPD “online”, my experience has been more times than not when performing RPD manipulations, that offline is the way to go.
A quick check in SQL Developer of my STAR schema, select * from w_project_d;
Once I have validated that I have successfully added the fields correctly during my ETL run, the next step is to open the OraclePrimaveraAnalytics.RPD file in Oracle BI Administration tool.
In Oracle BI Administration tool, the far right column is the Physical section. This is where the connection pool to the database exists as well as the physical table objects. Expand the connection for your STAR schema all the way down until you reach the physical tables for STAR. Locate the W_PROJECT_D table here. Right click on the W_PROJECT_D table name, on the resulting menu choose New Object, then select Physical Column…
In the resulting physical column window, we need to add/change the following 3 options (NOTE: These 3 fields need to match identically the field(s) that was added to the STAR schema).
Name: PROJECT_OWNER Type: VARCHAR Length: 255
Click OK. This will added the new physical fields to the RPD. You should also notice that after clicking OK to add the new field, if you expand the logical representation of the physical table (Dim_W_PROJECT_D_Project), you shouldl see the newly added PROJECT_OWNER field here as well. From here, it’s an exercise in a series of dragging and dropping the newly added field into the other layers of the RPD, so the field appears where it’s needed.
Expand the Dim_W_PROJECT_D_Project logical table in the Physical Side of the RPD. Find the PROJECT_OWNER Field, highlight it and drag it from this location to the Business Model Layer under the Dim – Project Section.
The newly added field should appear
at the bottom of the Column List. You can right click on the PROJECT_OWNER
field and select Properties to see the details of this new field. On the
General Tab of the Field Properties Window, you can aslo change the Name of the
field here to match how you want the field to appear in the Presentation Layer.
For Example, you could remove the underscore here and change the case so it
appears more normal (see Below)
The final step in the RPD Administration tool is to drag the field from the Business Layer onto the Presentation Layer in the Subject Area(s) you want to have the new field available in Oracle Business Intelligence. In this example, I am only going to add the new field to the Primavera – Activity Subject Area, but if I needed it in the other ones, I could drag it to those as well.
Save the RPD, and deploy the updated RPD file in Oracle Business Intelligence Enterprise Manager. The new field should be available in the Primavera – Activity Subject Area
Until Next Time...
Friday Apr 05, 2013
By Jeffrey McDaniel on Apr 05, 2013
4- Now these scripts are part of the process. Run STARETL.bat or .sh. This final step of the ETL process will execute ANY scripts in this folder. So beware adding anything you do not want to get executed. Test your scripts in a non production environment to make sure there are no side effects of your custom scripts.
Thursday Mar 28, 2013
By Jeffrey McDaniel on Mar 28, 2013
The P6 Analytics product is driven by Facts. When you are looking at a subject area, take Primavera - Activity for example. You can add from the Project Dimension Project Id and Project Name. This will list out all projects in the w_project_d dimension table. In your Analysis if you add a Fact - such as actual cost - this will join the Project Dimension to the Activity Spread Fact table. When making this join a fact record must exist or you will get No result set found returned. You would have this situation if the project you are looking for either has no activities or does not have any spread records.
In P6 Analytics versions 1.2 and higher this could occur if the project has not been published in the Extended schema. If you know there are activities that should contain spread data the best place to begin evaluating would be the extended schema. In the P6 application go to the Projects section and add columns for enable publication (make sure it is checked) and last published (make sure this date is populated and recent). You can right click on the project in P6 and choose publish now. Check back in a few minutes and see if the last published date has been updated. More more information on the Extended schema check out the P6 Extended Schema white paper.
Friday Mar 22, 2013
By Jeffrey McDaniel on Mar 22, 2013
Friday Mar 01, 2013
By Shawn Lafferty on Mar 01, 2013
Have you ever wanted to show actual values vs. remaining on the same line using different colors for each? This can be a very tricky exercise in OBI. First, you have to create 2 separate date sets, one set of dates that represent time before the data date for actual values, and another set of dates after the data date for whatever is remaining. After this, you want to make sure that you can clearly show the data date so it is known where the actual values end and where the remaining begin <see chart below>. After you have created the time separation, you can begin to assemble your graph. In OBI, the order in which you place your lines matters for the simple fact of which bar will overlay the other. Since our actual bar will stop at the data date, we want this bar to "appear" on top of our remaining, so we will see actual values up to the data date, then remaining after the data date. The last part of this involves the display of the data date. I accomplished this by placing a large number in a field that I used for the data date. This value represents the height of the bar essentially, so when you try this in your environment, you may need to adjust the value according to your projects values.
If you want to see the analysis (OBI 126.96.36.199.7), download the files here
Wednesday Feb 06, 2013
By Shawn Lafferty on Feb 06, 2013
With a default installation of Oracle Business Intelligence and P6 Analytics 3.1, some object in the P6 Analytics 3.1 default catalog create errors because the path length is too long. Please consult the following Oracle Business Intelligence document, section 188.8.131.52 for Object Name Guidelines (http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/prescatadmin.htm).
If you selected to take the default paths when installing Oracle Business Intelligence, then by default your catalog path with be /home/oracle/Middleware/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog
This path may cause errors when copying the P6Analytics catalog.
To workaround this issue, create a new folder in a shorter path location (e.g. \home\oracle\Middleware\catalog), copy the P6Analytics folder from the P6 Analytics 3.1 release media to the new folder, and change the path location in Enterprise Manager for your install of Oracle Business Intelligence to use the shorter path.
Additionally, if you installed Oracle Business Intelligence on Linux/UNIX, you can alternatively create a symbolic link, then reference the symbolic link in Oracle Business Intelligence for the catalog path. In Windows, map a drive to the catalog location, then reference the mapped drive in Oracle Business Intelligence for the catalog path.
Tuesday Feb 05, 2013
By Jeffrey McDaniel on Feb 05, 2013
- Automatic publishing
- Crossing threshold '# of changes exceeded'
- Crossing threshold 'time since last publication exceeds' and has had a change since last threshold period
- Based on the threshold settings when crossing a certain number of changes the project is added to the queue to be published.These changes are calculated from changes on activities in the project, wbs level for the project, resource assignments on the project, and relationships on the activities. Changes in these areas will be added up in an internal table in the PMDB until it crosses the threshold of changes or the threshold of time has been crossed for projects with changes. At either of those times the project is added to the queue for calculation.
- The project must be created, enable publication set. The project must be published. After project has been successfully published the Security global service must be run to calculate security on the project. Naturally over the course of a day the global services are run and this process is taken care of, but if you need the new project immediately you must run this Security global service after publishing the project. When you have confirmed all these processes have occurred run the StarETL. At this point the project should be in the Star database and available for the P6 Analytics application in OBI.
Thursday Jan 17, 2013
By Jeffrey McDaniel on Jan 17, 2013
Thursday Dec 27, 2012
By Jeffrey McDaniel on Dec 27, 2012
If there are any files in the \scripts directory that have been updated that you wish to keep as part of the ETL process the best way to do so is replace the template file to ensure those changes are not overwritten. Whenever replacing or updating a template always make sure to create a copy of the original file outside of the \etl folder. \scripts is an active, live directory. \etl\oracle\templates is the archived version of what is to be used in any further ETL processes if there are any configuration updates.
Also as of P6 Reporting Database 3.0 there is a directory called 'user_scripts' in the \scripts folder which is used for executing custom ETL additions. See P6 Reporting Database 3.0 Installation and Configuration guide for more information.
Friday Dec 07, 2012
By Jeffrey McDaniel on Dec 07, 2012
Thursday Nov 29, 2012
By Jeffrey McDaniel on Nov 29, 2012
Row level security (RLS) is a feature of Oracle Enterprise Edition database. RLS enforces security policies on the database level. This means any query executed against the database will respect the specific security applied through these policies. For P6 Reporting Database, these policies are applied during the ETL process. This gives database users the ability to access data with security enforcement even outside of the Oracle Business Intelligence application. RLS is a new feature of P6 Reporting Database starting in version 3.0. This allows for maximum security enforcement outside of the ETL and inside of Oracle Business Intelligence (Analysis and Dashboards). Policies are defined against the STAR tables based on Primavera Project and Resource security. RLS is the security method of Oracle Enterprise Edition customers. See previous blogs and P6 Reporting Database Installation and Configuration guide for more on security specifics.
To allow the use of Oracle Standard Edition database for those with a small database (as defined in the P6 Reporting Database Sizing and Planning guide) an RPD with non-RLS is also available. RPD security is enforced by adding specific criteria to the physical and business layers of the RPD for those tables that contain projects and resources, and those fields that are cost fields vs. non cost fields. With the RPD security method Oracle Business Intelligence enforces security. RLS security is the default security method. Additional steps are required at installation and ETL run time for those Oracle Standard Edition customers who use RPD security.
The RPD method of security enforcement existed from P6 Reporting Database 2.0/P6 Analytics 1.0 up until RLS became available in P6 Reporting Database 3.0\P6 Analytics 2.0.
Provide new information on P6 Analytics and Reporting Database.
- Before you upgrade...what to expect (P6 Reporting Database)
- P6 Extended Schema - advanced techniques and additional information
- Report on ETL Status of a Multiple Datasource Environment
- How to find resources without any assignments?
- Role Usage in P6 Analytics
- EPS changes in Analytics (and P6 Extended Schema)
- Analytics Date Range is Out of Date
- Why Does Adding a UDF or Code Truncates the # of Resources in List?
- Reference for STAR field mappings
- Usage of Historical UDF's and Current Flags When Creating Analysis'