OLAP 11g Cube Aggregation Issues (Doc ID 1481454.1)

Last updated on AUGUST 06, 2012

Applies to:

Oracle OLAP - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Issue 1:

Gross SalesProduct at All Products level is not summing correctly.
Screen below is from Analytical workspace Manager  Measure Data viewer.

This data set is generated using the query below:

SELECT DISTINCT TIME, CUSTOMER, PRODUCT, LEVEL_NAME, HIER_ORDER, GROSS_SALES
  FROM INV_VIEW
  JOIN (SELECT DIM_KEY PRODUCT, LEVEL_NAME, HIER_ORDER
  FROM PRODUCT_PLANNING_GRP_MAJO_VIEW) PV
 USING (PRODUCT)
  JOIN (SELECT DIM_KEY CUSTOMER FROM CUSTOMER_VIEW
  WHERE DIM_KEY IN ('0519F0000', '063962A00', '0673F0000', '076420000'))
 USING (CUSTOMER)
 WHERE TIME = '02-JUN-12'
  AND LEVEL_NAME IN ('ALL_PRODUCTS','MAJOR_PLANNING_GRP')
 ORDER BY CUSTOMER, HIER_ORDER

Case 1. It shows 35.73(marked in red). It should show 35.73+739.03+3284.36 = 4059.12 at All Product level

Case 2. It shows 166.00 (marked in red). It should be 166.00+2442.40 = 2608.40

 

Issue 2:

Aggregation can return duplicate records at the lowest level.

This behavior happens while using the queries:

SELECT TO_DATE(TIME, 'DD-MON-YY') INV_DATE, TRIM(CUSTOMER) CUSTOMER, TRIM(PRODUCT) PRODUCT, ROUND(GROSS_SALES) CUBE_GROSS
FROM INV_VIEW
WHERE TIME = '02-JUN-12'
AND PRODUCT IN (SELECT DIM_KEY FROM PRODUCT_VIEW WHERE LEVEL_NAME = 'ITEM')
AND CUSTOMER IN ('0519F0000', '063962A00', '0673F0000', '076420000');

SELECT DISTINCT TIME, CUSTOMER, PRODUCT, LEVEL_NAME, HIER_ORDER, GROSS_SALES
FROM INV_VIEW
JOIN (SELECT DIM_KEY PRODUCT, LEVEL_NAME, HIER_ORDER
FROM PRODUCT_PLANNING_GRP_MAJO_VIEW) PV
USING (PRODUCT)
JOIN (SELECT DIM_KEY CUSTOMER FROM CUSTOMER_VIEW
WHERE DIM_KEY IN ('0519F0000', '063962A00', '0673F0000', '076420000'))
USING (CUSTOMER)
WHERE TIME = '02-JUN-12'
AND LEVEL_NAME IN ('ALL_PRODUCTS','MAJOR_PLANNING_GRP')
ORDER BY CUSTOMER, HIER_ORDER;

Results in the following:

INV_DATE CUSTOMER PRODUCT CUBE_GROSS
06/02/2012 00:00:00 0519F0000 000-L8003-000 458
06/02/2012 00:00:00 0519F0000 000-L8003-000 458
06/02/2012 00:00:00 0519F0000 000-L8003-000 458
06/02/2012 00:00:00 0519F0000 000-L8003-000 458
06/02/2012 00:00:00 076420000 002-80723-00W 2
06/02/2012 00:00:00 076420000 002-80723-00W 2


 Issue 3:

There is an issue with Gross_Sales_MTD_LY calculation in a leap year . The gross_sales_mtd_ly is not correct on 29th Feb 2012. Please refer the explanation below:

The first correct value(#1) is for 28-feb-2012. It shows that the formula calculated correctly the last years MTD value untill 28th. This value should be on 29th feb query(#2) also.
Other correct value(query#3) is gross_sales_mtd for 28-feb-2011. This means the Gross_sales was 55508319.8 last year in feb and this number is returned correctly by the first query(#1)
The gross_sales_mtd_LY query(#2) for 29-feb-2012 does not reflect the sales returned by query #3

#1.
SELECT GROSS_SALES_MTD_LY
FROM TPC_VIEW
WHERE TIME = '28-FEB-12'
AND PRODUCT = '1'
AND CUSTOMER = '1'
--RESULT:55508319.8 correct
#2.
SELECT GROSS_SALES_MTD_LY
FROM TPC_VIEW
WHERE TIME = '29-FEB-12'
AND PRODUCT = '1'
AND CUSTOMER = '1'
--RESULT:2874010.31 This is wrong. It should be as below.
#3.
SELECT GROSS_SALES_MTD
FROM TPC_VIEW
WHERE TIME = '28-FEB-11'
AND PRODUCT = '1'
AND CUSTOMER = '1'
--RESULT:55508319.8 correct

1. The correct value of gross_sales_mtd_LY for 29th Feb 2012 should be 55508319.8
2. Date GROSS_SALES_MTD_LY
26-FEB-12 51955176.05
27-FEB-12 51955176.05
28-FEB-12 55508319.8
29-FEB-12 2874010.31 (It should be 55508319.8 )
01-MAR-12 5951413.53
02-MAR-12 8631905.97

also

Date GROSS_SALES_MTD_LY
26-FEB-11 54031450.23
27-FEB-11 55194760.47
28-FEB-11 55237891.66
01-MAR-11 1753519.72
02-MAR-11 4385370.51

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms