How to model this in rpd

Robert Angel

Hi,

My version is 12.2.1

I am trying to model the equivalent of; -

select tab1.VarFields, il.VarFields

from tab1

, (select *

from tab2

where tab2.fieldX = 'FlyInTheOintment') il

where tab1.fieldX = il.fieldX(+))

I am trying to do this without resorting to opaque views (for reasons of performance) and database views (ditto) and without building a new table via an ETL process.

Is it possible?

I know you can join two tables in the Logical Table Source, and I know that you can specify a filter in the Logical Table properties, BUT, my concern is that when the tab2 content returns no data applying the filter on that same table will cause tab1 content to be correspondingly filtered, as null data is never equal to anything.

thanks for your input in anticipation.

Robert.

Thomas Dodds

Materialize a view and auto refresh via your ETL cycle ... use it like a table in RPD.  This works with billions of rows where table sizes are in the 100s of GB.

Robert Angel

Thanks, I had thought of this but I am trying to avoid any kind of DB build if possible, I should have included MVs in my 'no go' list.

Christian Berg-0racle

Snowflake it in the dimension LTS with thus changing the query grain.

Robert Angel

Hi Christian,

Snowflake in the business model is a big "no no", surely?

And even then I am not getting how this solves the outer join and filter in combination issue?

Surely the physical sql generated would then be; -

select t1.VarFields, t2.VarField

from t1, t2

where t1.ColX = t2.ColX(+)

and  t2.ColY = 'FlyInTheOintment'

Which will still mean that t1 rows disappear because t2 rows cannot be both null and have ColY as 'FlyInTheOintment'?!

Thanks for your input, it is greatly appreciated!

Robert Angel

Sorry,

reading your answer again you mean snowflaking on the LTS source, so you see it as if it is a single table, yes, and set the inner table to be the driving table - this is obviously NOT a no no, but I still think my original thoughts on the physical SQL would be true and it would not resolve my requirement?

thanks again,

Robert.

Gianni Ceresa

Robert Angel wrote:... as null data is never equal to anything.

True, = doesn't help you, but something like Col1 = 'FlyInTheOintment' OR IfNull(Col1, 'I am a value which can not exist at all in this column, never ever') = 'I am a value which can not exist at all in this column, never ever'

Will match your nulls just fine

Pay attention at your 'I am a value which can not exist at all in this column, never ever' , be sure it can't really exist...

Robert Angel

Thanks for this, this does better than some versions of the database, where what you describe will still not return data....

Christian Berg-0racle

Hey you wanted sth not oqaue, view or ETL ;-) I never insinuated this would be the miracle solution.

Robert Angel

lol, is that the party line, we gave you what you asked for, you never asked for it to work?!

Thanks for ending my week on a belly laugh.

Christian Berg-0racle

Pretty much, yes