Pricing analytics report shows incorrect discount calculation in OTBI for multi-tier pricing structures

[timothy_tech]

We’re experiencing issues with our OTBI pricing analytics dashboard in Oracle Fusion Cloud 23C. The discount calculation formulas are returning incorrect values when analyzing tiered pricing structures.

The problem surfaces specifically when we run reports on multi-tier discount scenarios. Our OTBI report shows discount percentages that don’t match the actual applied discounts in transactions. For example, a customer with a 15% tier-2 discount shows as 12% in the analytics report.

I’ve verified the base pricing data is correct in the transactional tables, but the OTBI subject area seems to be calculating discounts differently. We also need to validate if BI Publisher’s advanced calculation capabilities might handle this better than OTBI formulas.

Here’s a sample of the OTBI formula we’re using:

SELECT price_list_id,
  (list_price - net_price) / list_price * 100 as discount_pct
FROM pricing_transactions
WHERE tier_level > 1

Has anyone encountered similar discrepancies with tiered pricing calculations in OTBI? Any guidance on formula validation approaches would be appreciated.

Let me walk you through the complete solution that addresses all three aspects - OTBI formula validation, tiered pricing logic, and BI Publisher advanced calculations.

OTBI Formula Validation: Your current formula has a fundamental flaw - it’s calculating discount as a simple difference without considering the tier application sequence. The correct approach requires joining to the pricing tier tables to get the effective tier discount:

SELECT pt.price_list_id,
  pt.list_price,
  ptr.tier_level,
  ptr.discount_percent as tier_discount,
  pt.list_price * (1 - ptr.cumulative_discount/100) as expected_net_price
FROM pricing_transactions pt
JOIN pricing_tier_rules ptr ON pt.tier_id = ptr.tier_id

Tiered Pricing Logic: The key issue is understanding how Oracle applies tiered discounts - they’re cumulative, not replacement-based. If a customer qualifies for Tier 1 (5%) and Tier 2 (10%), the effective discount isn’t 15% but rather 14.5% (5% off, then 10% off the reduced price). Your OTBI subject area needs to reflect this cumulative calculation.

To fix this in your existing OTBI report:

1. Modify your subject area to include the PRICING_TIER_HIERARCHY table
2. Create a calculated field that applies discounts sequentially: `List_Price * (1 - Tier1_Pct/100) * (1 - Tier2_Pct/100)
3. Add a validation column comparing your calculated discount to the actual applied discount

BI Publisher Advanced Calculations: For complex scenarios where OTBI formulas become unwieldy, use BI Publisher’s data model with a SQL-based calculation layer. Create a BI Publisher data model that:

1. Pulls the base pricing transaction data
2. Applies tier logic using CASE statements and window functions to handle sequential discounts
3. Exposes the calculated fields back to OTBI as a custom subject area
4. Uses BI Publisher’s formula editor for any remaining complex calculations that need conditional logic

The BI Publisher approach gives you more control over calculation precedence and handles edge cases like overlapping promotional discounts better than pure OTBI formulas.

Validation Steps:

1. Run both your original OTBI formula and the corrected version side-by-side
2. Export a sample of 50-100 transactions with known correct discount values
3. Compare the OTBI results against the source transaction data
4. For any discrepancies, trace through the tier application logic step by step

This comprehensive approach addresses the root cause (incorrect tier calculation logic) while giving you the flexibility to handle increasingly complex pricing scenarios. The hybrid OTBI + BI Publisher architecture is the most robust solution for enterprise-scale pricing analytics.

One final note: after implementing this fix, document your tier calculation logic clearly. Future report developers will need to understand the cumulative discount model to maintain accuracy across all pricing analytics reports.

---

This draft is based on general Oracle Fusion Cloud knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

[david_engineer]

I’ve seen this exact issue before. The problem is usually in how OTBI handles null values and rounding in tiered discount calculations. Your formula doesn’t account for cases where list_price might be zero or where tier adjustments are applied sequentially rather than as a flat percentage. Check if your OTBI subject area is pulling from the correct fact table that includes all discount tier adjustments.

[brendaengineer]

Quick question - are you using a custom OTBI subject area or the seeded Pricing Analytics subject area? I noticed in 23C there were some changes to how tiered discounts are modeled in the seeded subject areas. The discount percentage calculation needs to consider the cumulative effect of multiple tier rules, not just the final applied discount. Also, verify your data security policies aren’t filtering out intermediate discount calculation records.

[james_ninja]

We’re using the seeded Pricing Analytics subject area with some custom columns added. I checked the data security and that’s not causing the filtering issue. The discrepancy seems most pronounced when customers qualify for multiple overlapping discount tiers. I’m wondering if we need to switch to BI Publisher for more complex calculation logic, or if there’s a way to enhance the OTBI formula to handle the sequential tier application properly.

Tested this on Oracle Fusion Cloud 23D with OTBI Subject Area ‘Price Lists Real Time’ and the cumulative_discount join to pricing tier tables resolved our multi-tier discount variance immediately.

[arjun_builder]

For complex tiered pricing scenarios, I’d recommend a hybrid approach. Use OTBI for the base data extraction and real-time analysis, but leverage BI Publisher’s calculation capabilities for the actual discount computation logic. BI Publisher supports more sophisticated nested formulas and can handle the sequential tier calculations better. You can create a data model that applies the tier logic in the correct order, then use that as a foundation for your OTBI analysis subject area. This gives you both accuracy and the flexibility of OTBI’s ad-hoc capabilities.

[akash_builder]

I agree with the hybrid approach suggestion. However, before going that route, verify your OTBI formula is using the correct tier hierarchy logic. The issue might be simpler than it appears - often it’s about joining to the pricing tier definition tables correctly to get the cumulative discount calculation.