Oracle Analytics Cloud Physical Layer Mapping with Complex Join

brenda_strong

I am looking for examples of complex join expression in the physical layer of the semantic Business Model. I've searched for over an hour and only found one example, and it did not work for me. I hope someone can help me with this as I have run out of ideas.

I'm using Oracle BI Administration Tool version 12.3.7

I have two tables detail_postings_view v and fundbalrptgrp f.

v.c_fund = f.c_fund
v.c_account = f.c_account
v.c_object = f.c_object but f.c_object is null in some rows because those rows have more than one possible c_object
v.decode_key = f.decode_key but f.decode_key is null in some rows because those rows have more than one possible decode_key

Basically, some of the fund/account combinations are repeated in the fundbalrptgrp table, but need to be separated into negative results/positive results (designated by the decode key) in the report. There is also some overlap of the object codes. These are the two reasons I need a complex join.

Below is an example of what I've tried along with variations of it. I don't get any error in the Expression Builder, but when I click OK in the Physical Diagram Expression window I get an error Only columns, designated predicates and operators are allowed. I can't find anything online explaining what that actually means.

I also tried a variation of the same below but using a CASE statement. When I tried that I get a Syntax error

"fundbalrptgrp_alias1"."C_FUND" = "detail_postings_view_alias1"."C_FUND"
AND
((
("fundbalrptgrp_alias1"."C_GRP_LVL" = 'LVL100'
OR "fundbalrptgrp_alias1"."C_GRP_LVL" = 'LVL200')
AND "fundbalrptgrp_alias1"."C_ACCOUNT" = "detail_postings_view_alias1"."C_ACCOUNT"
AND "fundbalrptgrp_alias1"."C_OBJECT" = "detail_postings_view_alias1"."C_OBJECT"
)
OR
(
("fundbalrptgrp_alias1"."C_GRP_LVL" = 'LVL300'
OR "fundbalrptgrp_alias1"."C_GRP_LVL" = 'LVL600')
AND "fundbalrptgrp_alias1"."C_ACCOUNT" = "detail_postings_view_alias1"."C_ACCOUNT"
AND "fundbalrptgrp_alias1"."C_OBJECT" = "detail_postings_view_alias1"."C_OBJECT"
AND "fundbalrptgrp_alias1"."DECODE_KEY" = "detail_postings_view_alias1"."DECODE_KEY"
)
OR
(
"fundbalrptgrp_alias1"."C_GRP_LVL" = 'LVL500'
AND ""fundbalrptgrp_alias1"."C_ACCOUNT" = "detail_postings_view_alias1"."C_ACCOUNT"
))

Gianni Ceresa

I just gave it a try in the Model administration tool of OAS 2023 (7.0) and OAS 2024 (7.6) (your version of model admin tool is an OAC version in between these 2).

Your original complex join works just fine.

I just rewrote it in a different way but it's the same join condition (of course the expression is longer than this because FQDN of columns are used, with the database name and schema name before table and column):

"detail_postings_view_alias1"."C_FUND" = "fundbalrptgrp_alias1"."C_FUND"
AND "fundbalrptgrp_alias1"."C_ACCOUNT"  = "detail_postings_view_alias1"."C_ACCOUNT" 
AND (
( "fundbalrptgrp_alias1"."C_GRP_LVL" IN ('LVL100', 'LVL200') 
AND "fundbalrptgrp_alias1"."C_OBJECT" =   "detail_postings_view_alias1"."C_OBJECT" )
OR
( "fundbalrptgrp_alias1"."C_GRP_LVL" IN ('LVL300', 'LVL600') 
AND  "fundbalrptgrp_alias1"."C_OBJECT" =   "detail_postings_view_alias1"."C_OBJECT"
AND  "fundbalrptgrp_alias1"."DECODE_KEY" =   "detail_postings_view_alias1"."DECODE_KEY" )
OR 
( "fundbalrptgrp_alias1"."C_GRP_LVL" = 'LVL500' )
)

I don't have the error you got.

Was that join a foreign key join first and then you tried to change it? This doesn't work, you need to delete the join and create a new one being a complex join from the first time you save it.

Still, depending on the usage you will have of this piece of data, using multiple LTS could perform a lot better because the tool will only query the right LTS with a more simple join condition instead of having to perform the full join condition to then only retrieve one of the 3 parts).

Kathy-Oracle

I am not finding any examples, but have you had a chance to review the online documentation About Complex Joins

https://docs.oracle.com/en/middleware/bi/analytics-server/metadata-oas/work-physical-foreign-keys-and-joins.html#GUID-3E942DFC-50FC-4D28-8B6B-3A57700D14BE

Gianni Ceresa

A complex join is just a “complex” name for a join condition that isn’t columnA = columnB (a straightforward primary - foreign key relation).

The syntax to be used is still LSQL syntax and not your own datasource syntax, but the expression editor where you can write the complex join expression lists all the available functions and allowed expressions and columns.

I can’t remember if a static value can be used there, like your C_GRP_LVL values.

You could anyway simplify a bit the join condition: C_ACCOUNT isn’t always the same everywhere?

And then you maybe could consider using separate aliases for the 3 different join conditions, and you then merge them back into the same logical object by using different logical table sources setting the C_GRP_LVL filter at the LTS level.

Performance will also be better if you don’t always query the 3 LTS at the same time, because the join conditions will be a lot faster than the single long expression you tried to use.

brenda_strong

@Gianni Ceresa You were right. I deleted the join and then tried it and did not get the error. I haven't tested it yet though. If it runs too slow, how would I create separate aliases for the 3 different join conditions and then merge them back? Also thank you for your response!

Gianni Ceresa

If when you analyze your data you do use only one or two of the 3 "OR" conditions in that join expression, what you can do is to create new aliases for that physical table and join them to the other table with the 3 different join expression (that would become just foreign key joins with columnA = columnB and … ).

Then when you create the logical table representing this object, you add 3 logical table sources to it. Because physically it's the same object (3 aliases but same object) and only the join condition change, the 3 LTS is really just a way to represent the 3 different joins.

Then you can write the WHERE clause on the C_GRP_LVL at part of the LTS definitions, and you can also configure fragmentation on the 3 LTS to tell the tool what piece of data it will find in each of the 3 LTS.

Depending on your analysis, if you only look for data with a filter C_GRP_LVL = 'LVL200' in your analysis, the query sent to your database will be just for that single LTS, without executing a query (with a join expression) that would be for the other 2 LTS.

brenda_strong

@Kathy-Oracle Thanks for the suggestion. I had already read that, but there's only a couple paragraphs about complex joins and how they work. There is no information at all about how to go about it, and no examples.

@Gianni Ceresa Now I understand what you mean by creating 3 LTS and it makes a lot of sense. Thank you for explaining. I had a chance to work on my report yesterday and it works fine with the complex join. I don't notice any slowness. However, when I try to add a new pivot table to the analysis, it freezes up and gives an error saying there's too much data or something like that. I was only able to get it to work by filtering to one day and one level of data. Oddly, after creating the pivot table that way, it doesn't give me an error when I take off the filters. I only noticed this happening after adding the complex join.

Gianni Ceresa

It's possible that the complex join is returning more data than the join you had previously. There shouldn't be a different in data quantity limits based on the kind of join. Actually the tool doesn't care about the kind of join, it is just used to generate a query. The data limits jumps in based on the number of rows/columns/cells returned. There it is just possible that the new complex join, having all those conditions, now return more data than before.