RPD. CAST in BMM

aPsikus

@Robert Angel,

additional question.

What we have done is changing Rating in Presentation layer (from number/double to string).

In database it is still number/double.

In current setup it is not possible to use it as filter or Keep Only in analysis, as string do not match with number.

Is there any way to solve it? Or whole change in RPD doesn't make sense if we would like to use it as filter?

Robert Angel

Hi,

not fully following your logic here.

Say I have something in the DB that is; -

Num_Field  of number data type

I create a field in the business model layer based on this which uses case logic to turn its value 1,2,3,4,5 / 999 into some text groups stored in a varchar. I then expose this for use in the presentation layer.

I can then use it in answers for filters or whatever other purpose...

However, more performant would be to have your ETL process convert the current values for a new table field using the same case logic.

Others may disagree with my last statement as it is not OBIEE-centric, and presumes you have an ETL resource to hand should the day come that there is a 6,7,8 that requires you to revisit the case logic.

aPsikus

You are right, but this is the case.

New field in Presentation layer is string/varchar type.

If you will add it to filter, or on the analysis, it will be string/varchar.

In related column in database is number/double.

In this case filtering do not work, as in database table there is no string/varchar, and you can't compare it with number.

Unless I have missed some setup, because it doesn't work.

aPsikus

Finally I have found a reason why it was not working in filter.

As there was CAST to char (3), it was adding spaces at the end, and it this case there was no such values.

Adding TRIM solved the problem.

Robert Angel

Didn't I say exactly that - "What I am telling you though, is your varchar2(4) is probably part of your problem, because I have observed that frequently cast puts extra characters that you did not want / anticipate"?!

See also ->  "The reason I asked on why put it in a varchar2(4) is that extra space may cause it to be '999 ' or similar, hence I would either go for the 3 characters or I would trim it..."