joining two Dimensions from different Universes Problem

building reports webintelligence
1

Hi all
I two tables
tab1 has
ID Address city form univ1
1 aaaaa x
2 bbbbbb y
3 cccccc z

tab2 has
ID InvenoryNum Shipdate form univ2
1 1233445 mm/dd/yy
1 234521 mm/dd/yy
2 3333333 mm/dd/yy
2 4444444 mm/dd/yy
2 44444566 mm/dd/yy
3 47658957 mm/dd/yy

this is how i need

ID Address city InvenoryNum Shipdate
1 aaaaa x 1233445 mm/dd/yy
1 aaaaa x 234521 mm/dd/yy
2 bbbbbb y 3333333 mm/dd/yy
2 bbbbbb y 4444444 mm/dd/yy
2 bbbbbb y 44444566 mm/dd/yy
3 cccccc z 47658957 mm/dd/yy

i pulled two queries in report
how should i proceed for this one?
I tried joining ID’s using Merged Dimensions gives me full outer join
how can i create a equi join?
Please advise

arjunanand :us: (BOB member since 2007-08-30)

2

That’s how it works. Filter out the NULL values in the Measures from both the data providers. Hope that helps.

zack :us: (BOB member since 2007-08-02)

3

if both the objects are compatible in type then only you can merge those two dimensions. u can use the merged dimension inn your report and if you dont want to see the records fetched by full outer join you need to check the below property from report properties.
remove duplicates from the report

yalisis (BOB member since 2009-07-27)

4

Thanks for your reply

let me eloborate my question again

tab1 has
ID Address city form univ1
1 aaaaa x
2 bbbbbb y
3 cccccc z

tab2 has
ID InvenoryNum Shipdate form univ2
1 1233445 mm/dd/yy
1 234521 mm/dd/yy
2 3333333 mm/dd/yy
2 4444444 mm/dd/yy
2 44444566 mm/dd/yy
3 47658957 mm/dd/yy
4 7648736 mm/dd/yy
4 9898798 mm/dd/yy
5 9845940 mm/dd/yy
6…

this is how i need

ID Address city InvenoryNum Shipdate
1 aaaaa x 1233445 mm/dd/yy
1 aaaaa x 234521 mm/dd/yy
2 bbbbbb y 3333333 mm/dd/yy
2 bbbbbb y 4444444 mm/dd/yy
2 bbbbbb y 44444566 mm/dd/yy
3 cccccc z 47658957 mm/dd/yy

how should i proceed… do i need to merge the ID dimensions?
even if i merge i am not able to insert some of other dimensions in the report pane…
other universe objects are still greyed out?

arjunanand :us: (BOB member since 2007-08-30)

5

Hi Arjun,

Yes you merge the ID dimension objects.

Either one or the other DPs dimensions cannot be dragged into the same block.
That’s because BO doesn’t know something you do.
If you know the ID, the city and adress can have only 1 value.
You need to tell this to BO.

Make the address and city dimension objects available as two new detail variables.
(Create variable, formula =[dimension object])
They should be a detail of the dimension ID.

These detail variables you can drag into the same table.

Hope this helps,
Marianne

P.S. a quick and dirty alternative; use =max([dimension object]) in the combination table block. As their is only 1 value, the max will display the right value :wink:

marianne :netherlands: (BOB member since 2002-08-20)

6

Thanks for your reply marianne

I created the detail object
when i drag and drop in the detail

column values are with #MULTIVALUE

then i checked the show rows with empty dimenions

it worked fine
but i struck at one more thing here

when i turn on drill, drag and drop the ID of the First table
the drop down have all the data of both tab1 and tab2 ID’s

all i need is the ID drop down should have only values of the Tab1

how should i do that?

arjunanand :us: (BOB member since 2007-08-30)

7

Well i did a work around for this one…
I filtered out only Tab1 id’s in both queries it seems working
but i am looking for some permanent solution for this any suggestions??
can we do at the universe level??

arjunanand :us: (BOB member since 2007-08-30)