Aggregate Aware and Aggregate Navigation on IDT !!!
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.
Sheikh Ayub replied
I was just about to post that half of the issue is resolved, actually the "Thorough_Aggregate_Aware" is supposed to be turned off inorder for multiple sql to work. but now i have a bigger confusion - now that i have two sql getting generated via different contexts,what i am trying to understand is how is this solution any different than selecting "multiple SQL from each measure" in the parameter box?
They both seem to be the same , i thought multiple SQL for each measure created a separate select statement for each measure selected even if they were from the same table, but it seems that it is more like a separate SQL statement per list of measures per table(which means that all my measure from table 1 will be in the same select statement and others from table 2 will be in the other) - which looks exactly the same as what i have where i have a selected statement for measure from fact 1 and fact 2 - i am struggling to understand the difference between "SQL statement for each measure option" Vs
doing the whole process of creating an alias of the middle table and have your measures come from alias table etc.