Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE: Is there a System Repository variable for the logical request hash ?
Is there any System Repository variable related to the OBIEE logical query (for instance where the logical request hash is saved ?)
Answers
-
A "variable"?
How should that work? For a dashboard with 3 prompts and 8 analyses that variable would hold....what?
What's your use case? What are you tring to do?
0 -
We want to monitor the physical queries (that runs in the database) that are generated by one logical query (OBIEE).
To do that we are instrumenting OBIEE by setting connection script. See this blog
https://www.rittmanmead.com/blog/2015/03/instrumenting-obiee-database-connections-for-improved-performance-diagnostics/
It pass to the database the following session variable:
SAW_SRC_PATH
SAW_DASHBOARD
SAW_DASHBOARD_PG
SAW_SRC_PATH gives you the report. With it, you can see all queries that comes from this report. But we want to go further in the analytics, we want to pass along the logical query hash (The hash of the logical query). We are making some OBIEE query benchmark and we can't rely on the generated physical query.What is the system session repository variable of the logical OBIEE query hash.?
0 -
Baligera D-Oracle wrote:What is the system session repository variable of the logical OBIEE query hash.?
You havent been listening to what I said.
But never mind. LSQL is in the Usage Tracking tables.
0 -
Sorry but if you have the analysis reference (which you have) you can't really go to a lower level, that's the same as LSQL hash in the end (you of course cover changes to the analysis somewhere else).
1 analysis = 1 LSQL = 1 LSQL hash
1 analysis = 1 absolute path to that analysis
If you copy that analysis in 2 different locations or with different names you have:
2 analysis = 2 LSQL = 1 LSQL hash (because the LSQL is still the same, not affected by the location)
2 analysis = 2 absolute path to that analysis
So the current information of the path of the analysis is more detailed than the hash of the LSQL ...
0 -
Thanks! but that's not how it seems to works for me.
From one Analysis (only one path one XML in the catalog), you may have several Logical SQL.
1 analysis = 1 absolute path to that analysis = multiple LSQL
The good example if when you use prompt to be able to have a parametric analysis.
The user may choose for instance, a category, a different date and therefore the generated LSQL is not the same even if they comes from the same analysis.
No ?
Example:
One analysis with the following LSQL
'
select sum(quantity) where year = '2015'
'
If the user chooses in the dashboard prompt another year (2016), the LSQL becomes
'
select sum(quantity) where year = '2014'
'
Therefore we got:
1 analysis = Multiple lsql
And from a database perspective, tracking the LSQL is more natural.0 -
True when you use the analysis, but the single object is the analysis which has 1 LSQL globally (if you translate custom filters into generic filters).
Real query generated at the time of executing the report with the context is available in the UT tables, as Christian said.
But all these possible queries always come from one and single object in the catalog, that's why the instrumentation blog rmoff wrote goes down to the analysis path and that's it.
All the possible queries still only apply to the same and unique analysis.
So if you match the logical sql and physical sql from UT with the query executed on the DB you get the link between the 2 pieces.
0 -
Thanks for thinking along.
From a database point of view (which is ours), the single object is a query not a report. The execution plan of two query that comes from the same analysis can be really different.
Two points:
* From a logical SQL, OBIEE (OBIS) can generate different Physical SQL. For instance, we have here a exalytics platform and if OBIEE is installed on it, the same logical sql will not produce the same physical sql that on our dev environment. This is not always possible to retrieve the logical SQL from the physical SQL and from the report path.
* The lookup is not really efficient as it's against a LOB column and against a LOG table (big table)
That's why we would like to be able to pass this logical SQL hash to the back-end database on the connection level.
Actually, we are using the analysis (report, saw_src_path) as statistical object but the runtime deviation is often too big between two query of the same report.0 -
Hi Christian Berg /Gianni Ceresa
Can you please response to the above update.Customer is expecting an update on this.
Best Regards,
Baligera
0 -
As far as I know there isn't any variable with the LSQL hash.
You can of course open a SR and ask your colleagues (as your work for Oracle).
0 -
Baligera D-Oracle wrote:Customer is expecting an update on this.
Fulfilling explicit customer expectations is what we're normally get paid for. You know....how the consulting business works ;-)
0
