OBIEE RPD Question

2934998

Hi,

I need to join a new table to an existing fact table and a dimension join with the following scenario:

Dim A
1
2
3

Fact
1
2
3

Dim B
1
3

Fact -> Dim A

Fact -> Dim B

In my report i want to show all the records from dim A but wherever it matches with Dim B have a *, how can we implement this in RPD? So the output in Publisher i am looking for is:

Output
1* 2 3*

Any help is appreciated

Thanks,

Nimesh

Gianni Ceresa

You simply join your new dimension as a normal dimension and in the business model you set the join between the fact and that dimension as an outer join.

You can then test if the join exist or not by checking if the column of DimB is null or not and display the *.

[Deleted User]

Yeah as Gianni said just get it into the LTS of the fact source and then do a check

Ed Chavez

how about you just create an opaque view with whatever select statement you need?

selec NVL(columnname,*) from dim a

inner join fact f

outer join dim b

[Deleted User]

Are you serious?! O_O

Gianni Ceresa

Sorry @3332985 but in that case do not spend the many K $$$ for OBIEE license and download Oracle SQL Developer for free

(Actually as it's for Publisher he can directly bypass the RPD and write the query)

The RPD model perfectly support outer joins and any kind of strange model, not just nice stars coming directly from the DB like that. So it's just 1-2 click more and same result.

Ed Chavez

Is that why OBIEE 12c scrapped Opaque Views?

wait, it still has opaque views! Wonder when are they gonna rid of them, so useless... the purpose of my reply, although is not the ideal solution, is to expand the conversation, to show the many possibilities of doing something, and at least to acknowledge a "defect" that Oracle made available in case you find it helpful for other situations. I wasn't really trying to get the best answer, but to answer something different than doing it through the Business Model.

2934998

Thank you everybody, I believe Gianni's solution will work perfectly!

Regards,

Nimesh