3

I am trying to achieve the following with the Java API:

enter image description here

for this purpose I am using the default schema/example GLOBAL from the Oracle OLAP download section.

This is the modified version of the code from the Oracle OLAP Java guide:

MdmCube unitsCube =
(MdmCube)mdmDBSchema.getTopLevelObject("PRICE_CUBE_AWJ");
MdmBaseMeasure mdmUnits =
(MdmBaseMeasure)unitsCube.getMeasure("UNIT_PRICE");
NumberSource units = (NumberSource)mdmUnits.getSource();

MdmPrimaryDimension mdmPRODDim = (MdmPrimaryDimension)mdmDBSchema.getTopLevelObject("PRODUCT_AWJ");
MdmLevelHierarchy mdmPRODHier = (MdmLevelHierarchy)mdmPRODDim.getDefaultHierarchy();

StringSource custHier = (StringSource)mdmPRODHier.getSource();

StringSource prodHier = (StringSource)mdmPRODHier.getSource();
Source famSel =
prodHier.selectValues(new String[] {"PRODUCT_PRIMARY::FAMILY::ACC",
"PRODUCT_PRIMARY::FAMILY::OS"});
Source querySource2 = units.join(timeSel).join(famSel).join(mdmPRODHier.getSource());

The result of the above query is a Cartesian product, and each family is matched with each product hierarchy as:

7: (PRODUCT_PRIMARY::CLASS::HRD--------PRODUCT_PRIMARY::FAMILY::ACC--------CALENDAR_YEAR::YEAR::CY1999--------*35.99375*--------)
8: (PRODUCT_PRIMARY::CLASS::HRD--------PRODUCT_PRIMARY::FAMILY::ACC--------CALENDAR_YEAR::YEAR::CY2000--------*32.9807070707071*--------)
9: (PRODUCT_PRIMARY::CLASS::HRD--------PRODUCT_PRIMARY::FAMILY::ACC--------CALENDAR_YEAR::YEAR::CY2001--------*36.8141666666667*--------)
10: (PRODUCT_PRIMARY::CLASS::HRD--------PRODUCT_PRIMARY::FAMILY::OS--------CALENDAR_YEAR::YEAR::CY1999--------*58.3433333333333*--------)
11: (PRODUCT_PRIMARY::CLASS::HRD--------PRODUCT_PRIMARY::FAMILY::OS--------CALENDAR_YEAR::YEAR::CY2000--------*62.113125*--------)
12: (PRODUCT_PRIMARY::CLASS::HRD--------PRODUCT_PRIMARY::FAMILY::OS--------CALENDAR_YEAR::YEAR::CY2001--------*65.7258333333333*--------)

As we can see here it did a match of the HRD (Hardware) with ACC (Accessories) and it has a value, but is should not have, as the ACC belongs to the class SFT (Software). See the attached screenshot for reference.

Is there a way to make an exclusion, so that when I join those two sources, only values that are possible are shown, meaning in this case that the ACC would only be shown under the SFT class as in the screenshot.

All help is more than welcome,

Thank you.

Adnan
  • 25,882
  • 18
  • 81
  • 110

1 Answers1

1

The problem is that you are joining the same dimension twice. The PRODUCT_PRIMARY dimension provides both the class and the family, so when you say:

units.join(timeSel).join(famSel).join(mdmPRODHier.getSource());

you are first limiting it to just your two FAMILY values and then expanding it back out to everything in the PRODUCT_PRIMARY hierarchy. The Cartesian product hence matches every product regardless of family, because the final criteria always matches.

I think what you are wanting when you speak of "only values that are possible are shown" you want to be able to drill up and down the hierarchy to find out what the valid children of HRD and SFT are and what the valid parents of OS and ACC are. The Oracle documentation explains how to do this with examples starting on page 6-17.

Simon G.
  • 6,587
  • 25
  • 30