cancel
Showing results for 
Search instead for 
Did you mean: 

Aggregate Aware and Aggregate Navigation on IDT !!!

former_member340306
Participant
0 Kudos

Hi,

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

                                         |

                                         |

                                         B'

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.

Thanks,

Sheikh



Accepted Solutions (1)

Accepted Solutions (1)

mhmohammed
Active Contributor
0 Kudos

Hi Hassan,

Do you really need alias of table B? What are you join on, between B and B'? Can't you get that measure from table B all the time as you've different contexts now?

You said that you're training users so they know what to bring and what not to in one query. I'm assuming you're worried about them bringing in objects from multiple contexts. Correct?

I've used Business Layer Views (BLV) to do that, I don't know if that's the way to do it. Whenever I try to search about BLV, I see blogs with same old garbage then BLVs are replacement for Derived Universes...

We had a Universe with 10 contexts, what I did is, created one Business Layer View for each Context, BLV 1 would include all the objects that we can pull and be assured that the SQL generated will be using Context 1 as no incompatible object in included in that BLV. So, I created those 10 BLVs, now I asked the users to select a BLV before creating a query, so they don't make an error of selecting objects from multiple queries even by mistake. Make sense?

Thanks,
Mahboob Mohammed

former_member340306
Participant
0 Kudos

i am not sure if i follow your response - first of all, it wont do it in the business later view, it will be done in the data foundation later - secondly, table B is part of the original context so measure cannot come from it only, because the moment we bring anything from C table which is the line item table, measure from B will inflate - so measure will have aggregate aware in them, Now we want measure to come from B' table anytime the measure from C are selected.

The only issue is when i do this process in IDT, it doesnt show me separate queries for each context, whereas it works fine in designer.

Sheikh

mhmohammed
Active Contributor
0 Kudos

Hi Hassan,

I wrote about the Business Layer View in response to what you responded to Swapnil, that you want to train Business Users to create report and they should know what to pull and what not to in a query.

I'll try to look further into your issue and let you know.

Also, turn ON the Thorough_Aggregate_Aware parameter in the Universe, check out page # 413.

http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp5_info_design_tool_en.pdf

Thanks,
Mahboob Mohammed

former_member340306
Participant
0 Kudos

This is a interesting comment, let me see if thorough aggregate aware is turned on. Are there any other universe parameters that i need to check, except for "multiple SQL for each context" ??

former_member340306
Participant
0 Kudos

Thorough_Aggregate_Aware parameter in the Universe is turned off.. still not working

mhmohammed
Active Contributor
0 Kudos

Hi Hassan,

It's a bit tough to visualize without snapshots which you might not have shared due to confidentiality of data. Even though you might have made sure that everything is setup correctly (e.g. Contexts, Aggregate Navigation and others steps), having another pair of eyes look at it may help. How about you try to use a sample universe (eFashion or Island Resort Marketing), convert to UNX and try to recreate the issue you have and see what happens. I think, Island Resort Marketing has a Fan Trap.

If not, may be you should use a Universe created on Adventure Works or Xtreme Sample database.

Or, may be you should create a ticket with SAP, you never know it may turn out to be a bug.

Thanks,

Mahboob Mohammed

former_member340306
Participant
0 Kudos

hey mahboob,

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.

Sheikh

Former Member
0 Kudos

Hi Sheikh,

"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."

Both the option solves the issue which you are facing. One difference which i can think of is like

-->Performance wise alias approach will be good as it will create only one sql statement for multiple measures coming from the same table.

Where as multiple sql for each measure option is at Global /Universe level, it will impact all the queries generated by the universe. It will generate multiple sql statements if there are multiple measures coming from a single table and has to merge the data at the report level. So processing load on webi server will increase.

For long term robust solution is to use alias and context rather than the check box

refer

Dave&amp;#8217;s Adventures in Business Intelligence &amp;raquo; Do I Have a Fan Trap?

Issue with check box

Multiple SQL Statement for Each Measure problems.

former_member340306
Participant
0 Kudos

Divya i believe your statement is not correct ,  it is correct from 3.1 perspective maybe, because i remember the same behavior from BOBJ 3.1 world that multiple SQL for each measure produced a select statement for each measure even if it was from the same table thats one of the reasons why i never used that option. but in 4.1 i have noticed a different behavior where it only produces a select statement per table !  u can double check the behavior in IDT but thats what i noticed in IDT, let me know if you see anything different. but i resolved the issue by modifying one of the parameters that i listed above.

Sheikh

mhmohammed
Active Contributor
0 Kudos

Hi Hassan,

Glad! You figured it out.


Thanks,
Mahboob Mohammed

Answers (6)

Answers (6)

mhmohammed
Active Contributor
0 Kudos

Hi Hassan,

I don't know if you've worked with IDT earlier, would you confirm something for me? Make sure whether you've explicitly excluded the joins which weren't supposed to be included in each of the contexts.

Why I say that is because, one weird difference between UDT and IDT is that, in UDT we would only select the joins which we want to include in a context, we don't do anything about the other joins. But, in IDT we have to explicitly exclude them by double-clicking on them while creating a context.

If you've done that already, then it may be something else, I didn't spend time into your issue.


Thanks,
Mahboob Mohammed

former_member340306
Participant
0 Kudos

Yes, i have excluded (not neutral) , but excluded the joins that i dont want. so thats done.

Sheikh

former_member340306
Participant
0 Kudos

I believe , i know how to resolve fan trap, and i explained the whole process in my initial post -- now considering that response , do you guys anything wrong with it? thats the question - has anyone tried to resolve the fan trap using that method in IDT and has it worked?

Sheikh

former_member340306
Participant
0 Kudos

I believe , i know how to resolve fan trap, and i explained the whole process in my inital post -- now considering that response , do you guys anything wrong with it? thats the question - has anyone tried to resolve the fan trap using that method in IDT and has it worked?

Sheikh

Former Member
0 Kudos

I think instead of using any options on IDT, you can create two separate queries on WEBI report & in first query get measures from A & B tables

And in another query get measures from B & C tables.

Then merge the objects accordingly on Report level.

Thanks,

Swapnil

former_member340306
Participant
0 Kudos

well thats not a effective way because then i have to train all users to know what they can bring and what not - that defeats the purpose of putting the intelligence on the universe level

former_member340306
Participant
0 Kudos

Divya,

I afraid i dont understand your message - were you intending to include a link in it?

or more details?

Sheikh

Former Member
0 Kudos
Former Member
0 Kudos

Hi Sheikh,

You can also read this book that will explain how to solve fan traps but also a lot of best practices about universes design: http://https://www.sap-press.com/universe-design-with-sap-businessobjects-bi_3412/

Didier

Former Member
0 Kudos

Hi Sheikh,

Refer this link to fix Fan trap

this can be achieved without aggregate association

2) Create report , 1st data provider as month, value with currentyear prompt