Issues with Cost/Budget plan XOG (Project Invalid message)

If you get an error when you XOG in Cost/Budget plan that the project is invalid, check if you are using "Time Entry User Lookup field 1" or "Time Entry User Lookup field 2" lookup as a grouping field in the plan. If you do, chances are the lookup doesn't have the value that you are specifying in the Plan Details (detail2Name) section of the XML.

Setting up Data Warehouse for Clarity

Disclaimer: I am still learning and dont consider myself as a expert, so if you are following this to setup your own Warehouse, you are on your own. So, please dont do this directly for Production setup. Comments/suggestions on the steps I followed are most welcome.

In this post, I would like to share my experience of setting up a Data Warehouse (henceforth will be mentioned as DW) with Clarity DB on SQL Server as Source Data along with some additional data loaded from excel files and Oracle DB as the DW.

Setting up a DW is almost similar to setting up any database with some differences (which I would mention). This is what I have observed. If you feel there is some other differences as well, please share.

So, here are the steps I followed:

1. Setup Temp folder.

    Most often, in a new environment, Temp folder related to your login will point to the default location. Its desirable (and maybe advisable?) to set it to "C:\Temp" or similar directory structure since some installations dont prefer having space in the Temp directory path.

2. If you are using a DHCP enabled IP address (if you dont have static IP), then please install Microsoft Loopback adapter and assign it to a static IP.

     I setup the adapter following steps outlined in "http://social.technet.microsoft.com/Forums/en-US/winservergen/thread/df1dbf9f-58fa-4ac1-b019-8f86863371b7/". There could be other blogs that show you how to do it. I set the IP to "10.10.10.10" and Subnet Mask to "255.255.255.0".

3. Setup Java, create JAVA_HOME variable and include in PATH. Ensure no space in Java installation directory.

    I installed jdk1.6.33 and installed it on "C:\Java" folder. Created JAVA_HOME variable as "C:\Java\jdk1.6.0_33" and set included it in PATH as "%JAVA_HOME%\bin;".

4. Install Oracle DB with "Software Only" option

    Setup only Oracle software since we would use "DBCA" to create DB with different set of parameters tuned for DW. Try setting up Oracle on a different drive or different partition.

5. Create DW database using DBCA

    While creating, ensure to select the DB template as "Data Warehouse", and I have selected UTF8 as Character encoding with "STAR_TRANSFORMATION_ENABLED" and "QWERTY_REWRITE_ENABLED" set to "TRUE". Also, ensure to set "NLS_DATE_FORMAT", "NLS_TIMESTAMP_FORMAT" and "NLS_TIMESTAMP_TZ_FORMAT". These parameters prove a lot useful when you try loading date information from Clarity into DW.

There are some other useful parameters that could be set while creating the database. Please refer to the article "http://docs.oracle.com/cd/E11882_01/olap.112/e17123.pdf", especially page 122.

Ensure to provide adequate memory allocated to the DB and try setting all accounts with same password (at lease easier for me to remember).

6. Setup ORACLE_UNQNAME environment variable

   Setup ORACLE_UNQNAME environment variable and set it to the DB name you chose in Step 5.

7. Unsecure DB Console

    Unsecure DB Console if you want to access "Enterprise Manager". When I tried accessing EM, I was getting strange characters and not seeing login page. When I unsecured the console, was able to login to EM.

 Run the following command to unsecure: "emctl unsecure dbconsole"

Note: Depending on security policy you have in place, you may or may not do this step.

8. Setup OLAP tablespaces.

    OLAP prefer different kind of tablespaces than what comes default with the DB. You could create these tablespaces by following the steps outlined in the link "http://docs.oracle.com/cd/E11882_01/olap.112/e17123.pdf".

Caution: When I created the UNDO tablespace and set EM to the new UNDO tablespace and restarted the DB, the instance wont come up and so had to revert back the changes. But, other tablespaces and settings has not created any problems so far. If you know what was preventing the DB from coming up, please do share.

Rest of the steps are applicable if you are using "ORACLE DATA INTEGRATOR (ODI)" as your ELT tool and "OBIEE" as your BI tool.

9. Create a new schema for ODI to store temporary tables.

10. Run RCU to setup repository for ODI and OBIEE

     10.1 On Screen 2, provide your DB hostname, port (default 1521), Service Name (you can use netca to find your Service Name), Username and Password for sys/system account.

     10.2 On Screen 3, choose "Oracle Data Integrator" and "Oracle Business Intelligence" from existing option. You dont need to change the default Schemas, but do note down them as you would need them while setting up ODI and OBIEE.

      10.3 On Screen 4, provide the passwords for the Schemas for ODI and OBIEE. You could use same password like I did or provide different for each schema. Ensure to remember them as you would need them later.

       10.4 On Screen 5, you setup ODI related parameters. For options 1, 4, 5 and 6, you can choose the default values which are 001, D, 001 and WORKREP. Provide Supervisor and Work Repository passwords.

Proceed with rest of the steps to setup the Repositories in the DW.

11. Install ODI by running the command "setup.exe -jreLoc C:\Java\jdk1.6.0_33" from Disk1 folder. 

      Change "C:\Java\jdk1.6.0_33" to location where Java is installed in your environment.

Will update rest of the steps later....

Clarity Upgrade Experience - From 8.1.x to 12.1.x

I recently upgraded Clarity in our Test and Production environment from 8.1.3 to 12.1.0 with patch 4a applied. I wanted to share my learning and experiences with you. Given below are the steps I followed:

1. Unlock all projects that are currently locked. Also, delete all completed process instances.This would speed up your installation to some extent.

2. Ensure "niku" user's default database is set to "niku" (just make sure).

3. If possible, validate your NSQLs. Else, during pre-upgrade, when the install script validates your NSQL, it would throw out errors.

4. Run NBI_CLEAN_DATAMART_SP Stored Proc to clean up Datamart tables prior to upgrade.

5. Stop app, bg and nsa services. Move old log files to another directory if not needed. That should clear up some space.

6. Install Java JDK 1.6 (preferably on a path which dont have space in the path name) and Apache Tomcat 6. Set JAVA_HOME path to new java location and update the Path variable to the new Java location. Run "java -version" just to make sure it returns 1.6.x, else need to check your path variable (put java path in the beginning of the path variable preferably).

7. Extract install.jar for Clarity 12.1.0 upgrade to a temporary folder

8. Extract patch jar file to another temporary folder -> CA released a patch that was supposed to address some of the issues faced with the install script for 12.1.0. These issues are resolved in 12.1.1. So, if you are upgrading to 12.1.0, request CA to get you access to the patch file.

9. Invoke the install.bat file from the temporary folder where patch is unzipped. It would ask you for location where Clarity 12.1.0 jar file is unzipped. Specify the location. The install script would replace some of the files in the 12.1.0 directory.

10. Run the SQL "delete from odf_ca_constraint".

11. Once step 10 is completed, proceed with upgrade by running the install.bat file from the 12.1.0 directory

12. Once the package.jar file is unzipped and the installer starts pre-upgrade, perform the following operations (while the installer is still running):

    12.1 Unzip projmgr.jar file in the clarity\lib folder and check if AddTaskConstraint class is there. Most probably its not there. If so, you have to find that class file from previous version and add it to the class directory and jar the whole thing back to projmgr.jar file and place it in clarity/lib folder (replace existing file). If you dont know how to do this, you can mail me and I can send you the projmgr.jar file. Copy the projmgr.jar file to the clarity\lib folder.

    12.2 Edit db.xml file under clarity\.setup\scripts and put failonerror to false on line 204. I was getting errors when the script tried to upgrade the content pack. When checked with CA Support, they too advised to change the failonerror to false and proceed. You may or may not get this error. The command that the script runs was run manually outside after the installation was completed.

    12.3 Edit content.xml file under install directory\upgrade\12.1.0\postupgrade and at line 50, comment out the entire <target> section. Again, as above, was getting errors during content pack upgrade.

    12.4 Replace the SQL in tasksPMD.xbl file in Clarity/META-INF/projmgr/pmd folder to the below:

insert into odf_ca_constraint (id, created_by, created_date, last_updated_by, last_updated_date, partition_code)
select prc.prid, ?, ?, ?, ?, c.partition_code from prconstraint prc, prtask t, inv_projects p, odf_ca_project c where
p.prid = c.id and p.prid = t.prprojectid and t.prid = prc.prtaskid and not exists (select 'x' from ODF_CA_CONSTRAINT where id = prc.prid)


13. Once install finishes, run the following two commands to install and upgrade content (the section that was commented out):

nikuadmin content clarityContent -console -Dreports.apply=false
nikuadmin content upgrade -console -Dreports.apply=false

14. Stop all services before proceeding to next step. Install would have started both beacon and nsa and you should stop them to proceed to the next step.

15. Once upgrade finishes, upgrade PMO to 2.2. Before doing that, take back-up of clarity DB as well as Overview page and project view/configuration. I have not done this since we dont use PMO. Beware, when you install PMO, there is no way to uninstall and it would overwrite your object views and Overview page (in addition to any other modification it does). So, if you want to retain your customized configuration and dont want anything extra added by PMO, either take back up of views/pages before PMO install or dont install PMO if you dont use any of its portlets/functions.

16. Once PMO upgrade finishes, apply patch 4a to the Clarity system. You may now restore the Overview page and object views that PMO upgraded. You would need to request CA Support to grant you access to patch 4a.

17. Run Rate Matrix Extraction with all boxes checked except for last one "Incremental Update Only" unchecked. Then run Datamart Extraction, followed by Datamart Rollup

Hope you have a troublefree and painless installation experience. Please do share yours.

Clarity and Sharepoint Integration - Create Site

I have been getting questions on how to create sites in SharePoint using Clarity. Here is a step-by-step explanation.

1. Create a process and create a step with a custom script action.

2. In the GEL script, first check to make sure the site doesn't exist

<core:catch var="soapError">
    <s:invoke endpoint="${Endpoint}" soapAction="http://schemas.microsoft.com/sharepoint/soap/GetWeb" var="Result">
            <s:message>
                    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sp="http://schemas.microsoft.com/sharepoint/soap/">
                            <soap:Header/>
                                <soap:Body>
                                    <sp:GetWeb>
                                        <sp:webUrl>${parent_url}</sp:webUrl>
                                    </sp:GetWeb>
                                </soap:Body>
                        </soap:Envelope>
                </s:message>
    </s:invoke>
</core:catch>

where ${Endpoint} would be "${parent_url}/_vti_bin/Webs.asmx"
and ${parent_url} would be the URL you are trying to test if exists

3. Check the result of the above soap call

<core:catch var="selectFaultError">
    <gel:set asString="true" select="$Result//sp:GetWebResult" var="Exists"/>
</core:catch>

4. Based on the result, create the site if needed:

<core:choose>
    <core:when test="${Exists == NULL}">
            <core:catch var="soapError">
                    <s:invoke endpoint="${addSiteEndpoint}" soapAction="http://microsoft.com/webservices/CreateWebs" var="AddResult">
                            <s:message>
                                    <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                          <soap:Body>
                                <CreateWebs xmlns="http://microsoft.com/webservices/">
                                      <parentUrl>string</parentUrl>
                                      <siteName>string</siteName>
                                      <siteTitle>string</siteTitle>
                                      <siteDescription>string</siteDescription>
                                      <nLCID>unsignedInt</nLCID>
                                      <webTemplate>string</webTemplate>
                                      <ownerLogin>string</ownerLogin>
                                      <useUniquePermissions>boolean</useUniquePermissions>
                                      <bConvertIfThere>boolean</bConvertIfThere>
                                </CreateWebs>
  </soap:Body>
</soap:Envelope>

                </s:message>
            </s:invoke>
        </core:catch>
    </core:when>
</core:choose>

where ${addSiteEndpoint} would be in the form "<SP Entry URL>/SiteManagement/<Admin page>.asmx"

Parameters:
•    parentUrl -: Url of the parent site under which we wish to create a new site
•    siteName -: name of the site which will be the URL of the site
•    siteTitle -: title of the new site
•    siteDescription: description on new site
•    nLCID -: local id of the site (unsigned integer value)
•    webTemplate -: template for the new site
•    ownerLogin -: owner for the new site (should have rights to create sites under the parent)
•    useUniquePermissions -: boolean value whether to keep unique permissions for the new site
•    bConvertIfThere -: boolean value whether to covert the site if already exists



Now, the above is based on our configuration. What I would suggest is to use soapUI, start a new project and in the initial WSDL, provide the following URL "<Sharepoint Entry URL/SiteManagement/<admin page>.asmx?WSDL"

The program may ask you for login parameters - provide the user details who has full rights to create site in Share Point

Once the WSDL is imported into SoapUI, right click on "CreateWebs" and choose "New Request", give a name and the program would show you a sample SOAP call that you could embed in your GEL script (replacing the soap call above with the one you get from SOAPui).