Skip to Content

Aggregate Aware and Aggregate Navigation on IDT !!!

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



Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

7 Answers

  • Best Answer
    Apr 01, 2016 at 07:09 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 31, 2016 at 06:21 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 31, 2016 at 09:06 PM

    Divya,

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

    or more details?

    Sheikh

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 01, 2016 at 08:27 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Apr 01, 2016 at 06:22 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 01, 2016 at 06:23 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 01, 2016 at 06:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded