I'm facing quite an interesting problem. I have two physical tables with the same structure but with a different data. Requirement is to show same reports with one or another table. Idea is to have dynamically changed physical table name with session variable usage. Session variable can be change in UI so it was working until cache was turned on. When cache is turned on logical statements sent to OBI backend are the same even for different values of session variable that stores physical table name. Once cache is populated every users will get values from cache. This is possible source of discrepancy because some users might run reports with tableA values and some with tableB values.
Are there any options to set OBI to use data related to proper physical table name (i.e. accordingly to session variable value)? Model clone is not an option because it will be way to hard and complex to maintain both, beside same reports need to work sometimes with one table name and sometimes with other...
If I'm not wrong try to give a chance to OBIEE to use its Intelligence ...
Have you heard about Fragmentation or else the below at LTS properties->Content tab?
Check for these
Check the Check box for 'This source should be combined with other sources at this level'
As per this you got 'Value-Based' Fragmentation.
Try it out.
Hope it make sense, if does mark and rest send email
thank you, I've found another way to make it running. In fact there are two ways of doing it: filter LTS and have all data filtered from single table with session variable or use fragmentation content also with session variable.
Now tricky part is to set variable from UI, currently I'm using issue raw sql: call NQSSetSessionValue( 'String SV_SIGNOFF=aaa;' ) but I have to figure out how to change session non system variable value without need of administrator user rights.
There is GoURL method, but it's not working...
2. Add In ORACLE_HOME/bifoundation/web/display/authenticationschemas.xml
I've tested SET VARIABLE statement already but it doesn't set variable for the whole session (old variable value is showed in repo sessions manager). Same report run in a different tab gives me an error:
Metadata of results did not match metadata found in cache. This can be resolved by having an administrator refresh the server metadata.
call NQSSetSessionValue is working fine, but there will be a problem with usage because users don't have rights to access Issue SQL.
The scope of SET VARIABLE is only for that particular report where you are using. It will not impact at RPD/other reports.
You can check the session variable value that is getting changed for that particular report (you will be verifying that from the sql that is fired), then you will have to apply the same for all the reports that needs a different value for that session variable.
My preference for this sort of thing is to push the complexity into the database. If Table A and Table B have identical structures, create a database view that unions all the data into one place:
CREATE OR REPLACE VIEW both_tables AS
SELECT 'A' source_table, A.*
FROM tableA A
SELECT 'B' source_table, B.*
FROM tableB B
Then you can model new view this into your RPD and filter on the source_table column at runtime.
Of course, if you're not allowed to make changes to your data source, you could always include the above SQL as an Opaque view in your Physical layer. If you're going to do that, I'd recommend expanding out the "*"s to the full column list.