Issue with data filtered in universe on WEBI

Hello to all.

I'm having an issue with an universe created in IDT. I created an universe from the tables that come from the ERP. Everything works fine, with a filter that governs the whole universe by the field "BUDAT" from the table ZBSIS.

The only detail, is that the tables BSIK, BSAK are not bringing the correct amount after the month may 2016. After that month the values in these tables do not match with the ERP tables values. I used the option of "union" that provides me the Webi from the query panel to join that tables. I tried modifying the outter join, the left join and the right join. The strange thing, is that you try with a universe in which only I have the table ZBSIK and ZBSAK, without the global filter configured, only a filter that takes the column of the posting date union (BUDAT) it works correctly.

Any suggestion? Thank you very much for your help.

totalerpmayo.png

totalwebimayo.png

totaljunioerp.png

totalwebijunio.png

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Nov 04, 2016 at 07:43 PM

    what is the join condition between BSIK, BSAK and BSIS?

    BUDAT field is date column in tables?

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 04, 2016 at 08:15 PM

    Hi Amit,

    The conditions are n,n

    @catalog('Webi')."Global.INFOSET"."ZBSIS_BO"."BSIS-BUDAT"=@catalog('Webi')."Global.INFOSET"."ZBSIK_BO"."BSAK-BUDAT"

    @catalog('Webi')."Global.INFOSET"."ZBSIS_BO"."BSIS-BUDAT"=@catalog('Webi')."Global.INFOSET"."ZBSAK_BO"."BSAK-BUDAT"

    and BUDAT is DATE column.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 04, 2016 at 10:19 PM

    try selecting the outer join on BSIK AND BSAK tables and see

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 09, 2016 at 04:23 PM

    Hi ,

    Not sure USE_ENHANCED_QUERY_STRIPPING=Yes parameter in business layer will help you.

    It Specifies the query stripping mode for relational universes. If set to Yes, the system optimizes only the SELECT and GROUP BY clauses to avoid fetching unused data but does not modify the other clauses to respect the original query semantic. If set to No or not set, the system generates optimized queries by completely ignoring the stripped objects with their corresponding tables and joins.

    Thanks

    Sateesh

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 10, 2016 at 09:58 PM

    You might be facing a so called fan/chasm trap.
    A fan/chasm trap occurs whenever at least one row in a fact table has two or more matching rows in a table, that is joined to the fact table.
    Therefore, please check for the Join Cardinalities, not just according to an ER-diagram, but testing with real production data. then set the join cardinality for all joins in IDT and post back here.


    An example for a classic fan/chasm trap is the ORDER_HEADER table und ORDER_LINE_ITEM table.

    --------------------------------------------------------------------------------------------------

    Example for a trap:
    The Order HEADER table contains among other things the discount (10 US$) I might have been offererd via a promotion code. But I ordered 3 items, a book, an UHD TV and an umbrella in one go. My OrderID being '007' in this example.

    If I ran the following SQL code:

    SELECT  
      Order_HEADER_Table.OrderID,
    SUM (Order_HEADER_Table.DISCOUNT),
    COUNT (Order_ITEM_Table.ItemID) FROM Order_HEADER_Table INNER JOIN Order_ITEM_Table
    ON Order_HEADER_Table.OrderID = Order_ITEM_Table.OrderID
    WHERE
    Order_HEADER_Table.OrderID = '007' // which is my particular order
    GROUP BY
    Order_HEADER_Table.OrderID

    The so reported Discount value would be overstated as 30 US$, which is the Discount, but incorrectly multiplied by 3, the three matching rows in the Line Item table.

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content