Skip to Content

Web Intelligence - compare two tables from different datasources

Hi all,

i need to compare two tables from two different datasources (universes) in web intelligence. Both contains the same fields: user name, logical system, assigned role.

For example table one has three values:

  1. USER1 - R3 - ROLE
  2. USER1 - R3 - ROLE2
  3. USER1 - R3 - ROLE3

The second table:

  1. USER1 - R3 - ROLE
  2. USER1 - R3 - ROLE2

As you can see the last record from the first table is missing in the second table. I need to create a table with same fields user name, logical system, assigned role and an identifier that tells me if the record from first table is present in second. So the result will look like this

  1. USER1 - R3 - ROLE - OK
  2. USER1 - R3 - ROLE2 - OK
  3. USER1 - R3 - ROLE3 - Not OK

Tried merging dimension but its not working. Or maybe i am doing it wrong.

Thanks for any help.

Best regards,

Martin Zluky

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 19, 2016 at 11:29 AM

    Try merging the Username and Logical system. Create a detail and for the Table2 assigned role and compare them. I tried it and worked for the sample data.

    Look at the screenshot

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 19, 2016 at 03:49 PM

    Hi Kuldeep,

    thanks for your thoughts. It definitely helped me to get on the right way and i am closer and closer to figure it out. But basicly it works like you said. Few changes i made:

    1. There is no need to merge dimensions
    2. I made a dimension variable, not a detail variable for the assigned role from table 2
    3. In query properties i unchecked "Retrieve duplicate rows"

    It works for my case, can be different in other cases.

    Best regards,

    Martin Zluky

    Add comment
    10|10000 characters needed characters exceeded