Extra Cents added to Benefit Plan totals in DWH for Annual Plan Types

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When we export an ad-hoc view from Advanced Reporting for an investment, the export shows extra cents added that was never added to the benefit plan. In checking the main database (Niku/Clarity) and the data warehouse database (DWH), we see the extra cents is being stored in the DWH. Example: For a 10,000 Benefit Plan it shows as 10,000.04 This is seen in Benefit Plan Adhoc Views (including exporting to CSV). 

STEPS TO REPRODUCE: 

1. Create a new project in Classic (start date 1/1/2019 and finish date 12/31/2020
2. Associate it to a Department to enable financial plans
3. Go to the Financial Plans tab of the project
4. Click New Manual Plan
   1. For Period Type, select Annually
   2. For Start Period select 2019 and Finish Period, 2020
   3. Click Save
   4. Go to the Detail tab and populate 10,000 cost for 2019 and 2020
5. Click Return and from the Financial Plans drop down, select Benefit Plans
6. Create a new Benefit Plan using details below:
   
   1. Click New
   2. Set Period Type = Annually
   3. Start Period = 2019
   4. Finish Period = 2021
   5. Click Save
   6. Go to the Detail tab and click Add
   7. Select a value for Benefit Class and Benefit Subclass (IE Reduce Cost/Headcount respectively)
   8. Click Save and Return
   9. Populate 10,000 benefit for 2019, 2020, 2021
   10. Save
7. Go back to the cost plan and populate the benefit plan created above in the Benefit Plan field
8. Run the Load Data Warehouse – Incremental
9. Create a new Adhoc View for the benefit plan:
   1. Go to Home->Advanced Reporting->Create->Ad Hoc View
   2. Select Financial Management and select Choose Data…
   3. From source add the following to Selected Fields:
      1. Investment Name
      2. Start Date
      3. Benefit Detail Description
      4. Benefit (under Benefit Plans->Benefits)
   4. Click Ok
   5. Add Benefit (Measure) to Columns
   6. Add Investment Name, Benefit Detail Description, and Start Date to Rows
   7. View the Totals for the project created above

Expected Results: No Extra cents added, the Benefit total equals 30,000.00

Actual Results: Extra cents are added. In the above project example, the Benefit Total equals 30,000.04 (extra 4 cents)

Notes:

• If the adhoc report isn’t set to show decimals, the issue may be first observed when you export the report to CSV and set the format of the Totals field to display 2 decimals.

• Query that shows issue in DB: Select * from dwh_fin_Benefit_summary_facts where investment_key=xxxxxx

  • Benefit, Billing_Benefit, Benefit_Actual_var all show the incorrect amount

Cause

Caused by DE57440. The amount is off by a few cents due to DWH DB adding the additional decimal amount. 

Environment

Release : 15.7, 15.8, 15.8.1, 15.9, 15.9.1

Component : CA PPM DATA WAREHOUSE

Resolution

Fixed in 15.9.2.