Aggregate awareness does not work for the lower level table

building semantic layer
1

Hi

I have defined two aggregate aware objects as follows

@Aggregate_Aware(CASE_TYPE.CASE_GRP_CD,CDM_DIM_CASE.CASE_GRP_CD)

and

@Aggregate_Aware(CASE_CATEGORY.CASE_CATGRY_CD,CDM_DIM_CASE.CASE_CATGRY_CD)

As you can see the two objects first select columns from two higher level tables ,namely CASE_TYPE and CASE_CATEGORY.
However as per aggregate awareness, the second option is to select the column from a low level transaction table called CDM_DIM_CASE. Both objects refer to this same transaction table at the lower level.

The objects work perfectly if I use them separately for separate queries. They will select the objects from the higher CASE_TYPE and CASE_CATEGORY tables when I use them separately.
However if I combine both objects in the same query, suddenly they start referencing the CDM_DIM_CASE table which is lower in the aggregate aware hierarchy. However there is abolsutely no aggregate aware settings between these objects which would cause this to happen. Eg:- When I select the above two objects, the following code is generated, even though the CDM_CASE_TABLE is second in the aggregate aware matrix for both objects

SELECT
  CDM_DIM_CASE.CASE_GRP_CD,
  CDM_DIM_CASE.CASE_CATGRY_CD
FROM
  CDM_DIM_CASE

When I select these objects separately, they select the correct table according to the agrgegate aware matrix

SELECT
  CASE_TYPE.CASE_GRP_CD
FROM
  CASE_TYPE

and the other object generates

SELECT
  CASE_CATEGORY.CASE_CATGRY_CD
FROM
  CASE_CATEGORY

I have narrowed down the issue as follows. if you have 2 aggregate aware objects in which the lower level of the aggregate awareness matrix points to a single common table, the query generated seems to refer the single low level table, rather than joining the two high level tables as had been specified in the aggregate aware matrix

Please help

[Moderator Edit: Added code formatting - Andreas]

AmalkaWijayaweera (BOB member since 2009-07-16)

2

Have you set the Incompatible Objects correctly?

dessa :madagascar: (BOB member since 2004-01-29)

3

Yes, I checked this repeatedly and they are set correctly

I sent this to BO support , but havent got much joy from them

looks like the the aggregate navigation wouldnt work when both lower tables in the 2 objects are from the same table

AmalkaWijayaweera (BOB member since 2009-07-16)

4

The problem isn’t the lower level table, it’s the upper level tables are different. Aggregate Aware requires a “linear” path, and the same path for all compatible objects.

What do I mean my “linear” path?

Your aggregate navigation has to go like this:

A
|
B
|
C

What you have instead is:

A
|
C-B

You have two different “approach vectors” and rather than use A or B and try to figure out the joins between then, the best common choice is C, which explains the results you are getting.

In other words, it’s working as designed.

Dave Rathbun :us: (BOB member since 2002-06-06)