My Oracle Support Banner

OLAP 11g Cube Aggregation Issues (Doc ID 1481454.1)

Last updated on FEBRUARY 28, 2019

Applies to:

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

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious

data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead,

is purely coincidental and not intended in any manner.

 

 

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 MyView

  JOIN (SELECT DIM_KEY PRODUCT, LEVEL_NAME, HIER_ORDER

  FROM MyProductView) 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','MyMainPlanGroup')

 ORDER BY CUSTOMER, HIER_ORDER

Case 1. It shows 35.73. It should show 35.73+739.03+3284.36 = 4059.12 at All Product level

Case 2. It shows 166.00. 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 MyView

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 MyView

JOIN (SELECT DIM_KEY PRODUCT, LEVEL_NAME, HIER_ORDER

FROM MyProductView) 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','MyMainPlanGroup')

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 LeapYR_GrossSales calculation in a leap year . The LeapYR_GrossSales 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 LeapYR_GrossSales query(#2) for 29-feb-2012 does not reflect the sales returned by query #3

#1.

SELECT LeapYR_GrossSales

FROM TPC_VIEW

WHERE TIME = '28-FEB-12'

AND PRODUCT = '1'

AND CUSTOMER = '1'

--RESULT:55508319.8 correct

#2.

SELECT LeapYR_GrossSales

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 LeapYR_GrossSales for 29th Feb 2012 should be 55508319.8

2. Date LeapYR_GrossSales

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 LeapYR_GrossSales

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

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.