Skip to Content

Universe - How to model table aggregation and basic lookup to other table

Good Day BI Experts,

I got the requirement to create a report based on two ERP tables that should show two things:

Outputs an aggregated key figure (total costs) from one table that has several entries (several costs per cost location) and it should display from another table one key figure that has only one entry (Overall cost plan per cost location) – so aggregation and then basic lookup in another table.

What is the best way to achieve that in a universe / webi report?

Joining the two tables will join in the cost plan several times (for every record in cost table). Should I use the average function on the cost plan figure to overcome that or is there a better way modelling that (dim merge in the webi Report)?

Many thanks in advance for some advice.

Axel

Add comment
10|10000 characters needed characters exceeded

  • corrected the tag.this question is not related with business object platform.Please select the correct tag in future.

    Can you please expalin with the diagram.

  • Get RSS Feed

3 Answers

  • Feb 08 at 02:02 PM

    Attached example for question

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08 at 02:22 PM

    It's look like if you join on Cost location between two tables and create measure objects for actual & plan will work.Cost element is anyway in the table but not required in the final report.

    How many records do you have in both tables? If records volume is very high in both table then report will take more time to refresh. in that case you create two webi queries and use merge dimension at report level.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 09 at 06:54 AM

    Hi Amit,

    but the join will produce wrong figures for plan as it will be joined for every cost elment - or do I miss something?

    Best regards,

    Axel

    Add comment
    10|10000 characters needed characters exceeded