Choose field based on context

[first_last]

I have two tables: ENCOUNTER and HOSPITALIZATION. HOSPITALIZATION contains a subset (~20%) of ENCOUNTER's data. In addition, they share some of the fields (e.g. ADMISSION_TIME).

I would like to minimize duplication by sharing dimensions (e.g. Admission Time) when possible, where the tool is smart enough to determine which table needs to source the field's data.

With that in mind, is there a way to use contexts for this purpose? There might be two contexts: All Encounters and Hospitalizations, that contain the respective table. The object's dimension would use the context's name to determine the field:

If @Context = "All Encounters" THEN ENCOUNTER.ADMISSION_TIME 
Else HOSPITALIZATION.ADMISSION_TIME

Is this possible? Could the @Aggregate_Aware function be used for this purpose?

[Joe_Peters]

I'm not completely clear on your requirement, but it sounds like a possible case for @Aggregate_Aware. That would allow you to set a "preferred" table that will be used if the query contains only objects that can be sourced from that table. If the query includes any objects that cannot be sourced from that table, then the "unpreferred" table is used.

Generally this is used with summary tables. Let's say you have a fact table with a date/time field, and a summary table that rolls up the measure to date. You could use @AA to set all of the measures (and common dimensions) to prioritize the summary table. But the date/time field would not have an @AA function, since it does not exist in the summary table. If the query includes the Date/Time objects, then the fact table is used; if not, then the summary table is used.

It gets murkier if the overlap between the tables is horizontal rather than vertical. That is, if the summary table contains a filtered subset of the base table. Then it's not possible to tell from the selected objects which table to use.

[first_last]

I don't think @AA will work.

I created a simple class that contained fields from both tables.

ID

@Aggregate_Aware(HOSPITALIZATION.ID,ENCOUNTER.ID)

Appt Time

ENCOUNTER.APPT_TIME

Admsn Time

HOSPITALIZATION.ADMISSION_TIM

Testing

ID only

SELECT HOSPITALIZATION.ID FROM HOSPITALIZATION

ID and Appt Time

SELECT  HOSPITALIZATION.ID,ENCOUNTER.APPT_TIME
FROM HOSPITALIZATION
RIGHT OUTER JOIN ENCOUNTER ON (ENCOUNTER.ID=HOSPITALIZATION.ID)

ID and Admsn Time

SELECT HOSPITALIZATION.ID,HOSPITALIZATION.ADMSN_TIME
FROM HOSPITALIZATION

Ideally, the second scenario (ID and Appt Time) would have used only the ENCOUNTER table.

[Joe_Peters]

One of the requirements for @AA is that the referenced tables must be in different contexts. This would be the case in the detail/summary scenario I described above. If the tables (encounter and hospitalization, in your case) are in the same context, then it will join them, as you've found.