One of my clients has just moved to a 4.1 sp6 environment. We have had plenty of universes in 3.1 environment but now we want to leverage IDT and its new capabilities for our new universes. We are in a process of creating a new universe in IDT which has potential fan traps, so going back to the basics of what we did in the 3.1 designer, e.g we have 3 tables as shown below with 1:N relationship, we create a alias of the header table (in our case Table B) and follow the following step:
A ----<B ------C
Step 1: Context 1: A ---< B ---< C
Step 2: Context 1_N: A ---< B ---< B’
Step 3: Use @ Aggregate Aware in Object Definition of the measures as follows :
@AggregateAware (sum(b.measure) , sum(b'.measure))
/*this helps select which table to get the measure from incase of incompatibility set in aggregate navigation.
Step 4: Entity B is made incompatible to the measures in Entity C using Aggregate Navigation.
Universe Parameter setting:
Check : Multiple SQL for each context
Unchecked : Multiple SQL for each measure /* i want my above seperate context as defined in step 1 and 2 take care of sync SQL rather than this property , because other wise this property will create a seperate select statement for even the measures in the same context
Issue: When i apply the same procedure in IDT 4.1 sp5, it seems not to create a synchronized SQL. When i created the aggregate aware and applied aggregate navigation, it seems that when i bring measure from table B and table C, it uses the right object which is from table B' as per aggregate aware definition, but it is not creating the synchronized SQL.
I made sure that the "Context1_N" shown in step 2 doesnt include the join to table C, but for whatever reason it is not treating it as a separate context and giving me one SQL !!
Are there any parameter setting apart from "Multiple SQL from each context" that i need to set in the new 4.1 environment? or is there something else that needs to considered.
Please let me know if someone else has encountered this issue.