Friday Aug 30, 2013

Indicator UDFs

Indicator UDF's are not supported in P6 Analytics. The main reason being there isn't currently a way to move those same indicator images over into OBI, and the images that are used for indicators in P6 don't exist in OBI. There are some workarounds that can be used to achieve using UDF indicators in a way that based on the UDF indicator from P6 and image can be displayed in OBI.  

There are two main pieces to this.  First, the STAR data warehouse side.   Second, how to have OBI display an indicator based on a text value. In this blog posting we'll look into the data warehouse side. In a later blog we'll look at the OBI side.

Let's start back at P6.  In P6 you can add a Project UDF with a type of indicator. In P6 add this indicator as a column and then for a specific project,assign a value - for this
example lets say Blue Star indicator is assigned.

If you query the Extended Schema view for UDFVALUE for the projectobjectid of this project:

select * from udfvalue where projectobjectid = 4518;

You will get a result set back that shows UDFTEXT as BLUE, which is the text for the Blue Star icon. It will give subject area type and type of UDF - FT_STATICTYPE. 

In the P6 Reporting Database these UDF's are not available in the configuration utility because they are not supported. But UDF's can be added directly to the file in the \res folder. This file is used for populating all dynamic code and UDF values on the OBI side. In this example this is the entry I added into my 6th position. Status


After you have added to the .properties file you will want to execute runSubstitution.cmd or .sh in the \etl\common folder. Running this will populate this new UDF row you just added through the .properties file into the other necessary files.  Next step is to get the tables to populate this value. We are going to treat it like a text UDF . The only goal from the data warehouse side is to get it acting like a text UDF so a conditional statement can be added in OBI where if text = 'Blue Star' then display a certain icon.

Go to your \scripts\udf_load and add a new script called change_indicators.sql with the following:


update w_udfvalue_ds set DATATYPE = 'FT_TEXT'






Save this file.  (If all works out for you with your UDF's as indicators go back and add this same file as a template in \etl\oracle\templates\udf_load and save file as .tsql.  This will save you from having to make edits to this script each time the config is run. Running config overwrites files in the \scripts directory.)

Now run the ETL process and you will see this indicator UDF appear in the w_udf_project_d table like it was a text UDF .  Check back for a future blog on how to make changes in OBI to based a conditional format statement off this value. 

Tuesday Aug 20, 2013

P6 Reporting Database versioning

How can you tell what version of the P6 Reporting Database you have installed?   There are two main ways.

1- Query the ETL_PARAMETER table with the following:

select p_feature, p_1 from etl_parameter

where p_feature = ''

or p_feature = 'etl.source.version';

The output will be similar to: 3.1

etl.source.version 83

DB.STAR.VERSION will give the major release that this STAR database was installed from.  The ETL.SOURCE.VERSION will tell you which P6 version the ETL process is running with, this helps determine the mappings.

2- In the <star installation directory> there is a version.txt that will also give the version number. The version.txt was added in the last couple releases and is updated when fix packs are added. 

Thursday Aug 08, 2013

How to Generate Your Own RPD from P6 Analytics UDML

Starting in the P6 Analytics 3.1 release we began shipping the UDML that is used to generate the RPD that includes all the Primavera subject areas. If you have the UDML file you can generate your own RPD. This allows for some flexibility with versioning, if there are any changes or releases of Oracle Business Intelligence that might cause conflict with our RPD. Also with the UDML, you can directly edit or update the UDML and generate a new RPD based on your changes. We have seen others take advantage of this to make it easier to add more than 20 codes and udfs. Through the UDML, copy and paste can make it alot easier to add those additional codes and udfs rather than
using the GUI OBI Admin tool for the RPD. 

To generate a new RPD you just need an OBI installation. On that machine copy the UDML file (primavera_analytics_rpd.udml) from the P6_R31_Analytics\obi\rpd folder in the media pack. When generating the new RPD you will be supplying your own password. Open a terminal or cmd line prompt and go to the directory  <OBI HOME>\bifoundation\server\bin
and run the following command (for Windows, adjust the directories as needed for Linux installations) 

nqudmlexec.exe -P prima123vera -I C:\temp\test\primavera_analytics_rpd.udml -O C:\temp\test\PrimaveraAnalytics.rpd

In this example:

-P is the password of the new RPD  

-I is the directory where the UDML file has been copied to  

-O is the directory where your new RPD will be created. 

If there are other options you would like to set you can use the help in this utility to find the parameters. Now that the RPD has been generated you can deploy it through Weblogic like you would the RPD that is shipped with P6 Analytics. 


Provide new information on Primavera Analytics and Data Warehouse


« August 2013 »