OBIEE physical SQL generator

[Nagendra]

Hi experts,

Is there a shortcut to generate the physical SQL for the OBIEE report ran

I mean i go to admin manage sessions get it there after doing cache clearing in the admin tab

Is there a much better way to get the physical SQL which is must faster

let me know

Thanks

[Robin Moffatt]

Yes you can use the OBIEE web services, or the Catalog Manager from the command line to do it automatically.

--

Robin Moffatt

Principal Consultant

M: +44 7841 207 265 • E: robin....@rittmanmead.com

S: robinmoffatt • T: @rmoff

www.rittmanmead.com

Registered Office : Suite B, First Floor, Moore House, 11 - 13 Black Lion Street, Brighton, BN1 1ND, United Kingdom

Company No. : 6032852

VAT No. : 900 3839 48

Please note that this email communication is intended only for the addressee and may contain confidential or privileged information. The contents of this email may be circulated internally within your organisation only and may not be communicated to third parties without the prior written permission of Rittman Mead Consulting.  This email is not intended nor should it be taken to create any legal relations, contractual or otherwise.

--
--
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to
obiee-enterprise-met...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

---
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-met...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Naeem Akhtar]

Hi,

You can get this the way you want by enabling "Usage Tracking".

Regards

Naeem Akhtar

--

[Nagkandala]

Thanks Naeem

Can 

 we just get the physical sql 

Every time we run a report 

Using usage tracking if so how ?

[Nagkandala]

Thanks robin 

CAn you elaborate ?

Do you have a document with Steps?

[Robin Moffatt]

If you want to generate the Physical SQL without running the report, using web services or catalog manager

If you want to pick up the Physical SQL of a report that has already run, use Usage Tracking or nqquery.log.

--

Robin Moffatt

Principal Consultant

M: +44 7841 207 265 • E: robin....@rittmanmead.com

S: robinmoffatt • T: @rmoff

www.rittmanmead.com

Registered Office : Suite B, First Floor, Moore House, 11 - 13 Black Lion Street, Brighton, BN1 1ND, United Kingdom

Company No. : 6032852

VAT No. : 900 3839 48

Please note that this email communication is intended only for the addressee and may contain confidential or privileged information. The contents of this email may be circulated internally within your organisation only and may not be communicated to third parties without the prior written permission of Rittman Mead Consulting.  This email is not intended nor should it be taken to create any legal relations, contractual or otherwise.

[chet justice]

​Got any examples Mr. Moffatt?

[Girish]

Robin, you sure you don't mean to say "Logical" SQL instead of "Physical" ?

I don't recall there being a way to extract "Physical" SQL using Web services or catalog manager.

There is the generateReportSQL method (ReportEditingService binding) but that is to generate Logical SQLs from what I can remember.

Physical SQL is an execution time entity and will need the BI server to perform all its physical query navigation & generation, no ?

Of course I could be totally off the ball here - so do let us know if there is a way.

Rgds

Girish

Regards,
Girish Lakshmanan

[Naeem Akhtar]

Hi,

We can use Usage Tracking for Physical query as well.

Below are the steps

http://docs.oracle.com/cd/E28280_01/bi.1111/e10541/usage_track.htm#BIESG214

QUERY_BLOB column in S_NQ_DB_ACCT table will contain Physical SQL.

Regards
Naeem Akhtar

[Robin Moffatt]

Girish -- absolutely right, my bad. I should have said Logical SQL can be generated that way without running the report. 

[John Manzella]

The chetster

Sent from my iPhone

[Girish]

Naeem, it is well known & everyone agrees that usage tracking can show Physical SQL but that requires "execution" of the analysis.(Can be manual or nqcmd can be used to automate execution)

I was more keen on exploring the other 2 options suggested by Robin - i.e. ways of  obtaining physical SQL without execution.

Rgds

Girish

[Stewart Bryson]

It doesn't know the physical SQL until intelligent request generation... Aka, the OBIEE "parse". You need to issue logical SQL to the bi server to get the physical SQL back. Sorry. 

Sent from my iPhone

[Robin Moffatt]

Just to come back to this - I knew I'd seen something somewhere... in SA406 there is a script /home/oracle/scripts/PhysicalSQLGenerator that extracts the Logical and Physical SQL. It looks like it pulls the Logical SQL from web service and then nqcmd with -NoFetch to get the bi server to parse it without running it. I don't know if it actually works that way or not without testing it. 

[chet justice]

Sounds like an awesome blog post. I'll just sit back and wait since you always beat me to it anyway. See: https://rnm1978.wordpress.com/2011/10/10/instrumenting-obiee-the-final-chapter/

:)

[Girish]

Cheers Robin.

Just had a quick peek at that. It is a single Java class that generates logical sql text files by calling the generateReportSQL web service.

 

It prefixes the output logical SQLs with the following BI server request variables

 

SET VARIABLE SKIP_PHYSICAL_QUERY_EXEC=1, LOGLEVEL=2, DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1,

QUERY_SRC_CD='SampleApp-PSQLGEN', SAW_SRC_PATH=

  

So looks like you can basically execute the generated logical SQLs and then fetch the Physicals from the normal NQQuery logs.

Still a very convoluted way Id say :)

If anyone is interested in the source java file, I've attached it. (I ran it past a decompiler to get the source.) 

 

 

 

 

 

 

[Stewart Bryson]

I don’t believe it’s fetching the physical SQL form the logs… it’s having the BI Server parse the logical SQL and return the physical SQL without actually executing it.

Unless I am misreading the Java class.

On Mar 19, 2015, at 10:36 AM, Girish <giri...@gmail.com> wrote:

Cheers Robin.

Just had a quick peek at that. It is a single Java class that generates logical sql text files by calling the generateReportSQL web service.

 

It prefixes the output logical SQLs with the following BI server request variables

 

SET VARIABLE SKIP_PHYSICAL_QUERY_EXEC=1, LOGLEVEL=2, DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1,

QUERY_SRC_CD='SampleApp-PSQLGEN', SAW_SRC_PATH=

  

So looks like you can basically execute the generated logical SQLs and then fetch the Physicals from the normal NQQuery logs.

Still a very convoluted way Id say :)

If anyone is interested in the source java file, I've attached it. (I ran it past a decompiler to get the source.) 

 

 

 

 

 

 

On Thu, Mar 19, 2015 at 1:50 PM, Robin Moffatt <robin....@rittmanmead.com> wrote:

Just to come back to this - I knew I'd seen something somewhere... in SA406 there is a script /home/oracle/scripts/PhysicalSQLGenerator that extracts the Logical and Physical SQL. It looks like it pulls the Logical SQL from web service and then nqcmd with -NoFetch to get the bi server to parse it without running it. I don't know if it actually works that way or not without testing it. 

<2015-03-19_13-48-38.png>

--

Regards,
Girish Lakshmanan

<generateSQL.java>

[Girish]

As far as I can see, the java class stops with writing logical SQLs to files in an output directory.

Running those LSQLs and fetching the Physical SQLs is a manual task I believe. I don't see anything there that deals with Physical SQLs.

Rgds

Girish

[Girish]

....and if the class name is any hint...it is named "GenerateLSQL" where L stands for Logical I would guess.

Rgds

Girish

--

Regards,
Girish Lakshmanan

[Girish]

...oh actually, I just noticed that there is a separate shell script in the scripts directory

That script does indeed call nqcmd reading from the generated LSQLs(like Robin suggested  yesterday)

 

Rgds

Girish

--

Regards,
Girish Lakshmanan

[Stewart Bryson]

Yep… that’s it Girish. Neat little trick.

[JTesson State University New York Systems]

So this sounds like a promising technique to incorporate into a new product of some kind!

M: +44 7841 207 265 • E: robin.moffatt@rittmanmead.com

S: robinmoffatt • T: @rmoff

www.rittmanmead.com

Registered Office : Suite B, First Floor, Moore House, 11 - 13 Black Lion Street, Brighton, BN1 1ND, United Kingdom

Company No. : 6032852

VAT No. : 900 3839 48

Please note that this email communication is intended only for the addressee and may contain confidential or privileged information. The contents of this email may be circulated internally within your organisation only and may not be communicated to third parties without the prior written permission of Rittman Mead Consulting.  This email is not intended nor should it be taken to create any legal relations, contractual or otherwise.

On Wed, Mar 11, 2015 at 8:55 AM, Nagkandala <nagka...@gmail.com> wrote:

Thanks robin 

CAn you elaborate ?

Do you have a document with Steps?

On Mar 10, 2015, at 10:45 AM, Robin Moffatt <robin....@rittmanmead.com> wrote:

Yes you can use the OBIEE web services, or the Catalog Manager from the command line to do it automatically.

--

Robin Moffatt

Principal Consultant

M: +44 7841 207 265 • E: robin.moffatt@rittmanmead.com

S: robinmoffatt • T: @rmoff

www.rittmanmead.com

Registered Office : Suite B, First Floor, Moore House, 11 - 13 Black Lion Street, Brighton, BN1 1ND, United Kingdom

Company No. : 6032852

VAT No. : 900 3839 48

Please note that this email communication is intended only for the addressee and may contain confidential or privileged information. The contents of this email may be circulated internally within your organisation only and may not be communicated to third parties without the prior written permission of Rittman Mead Consulting.  This email is not intended nor should it be taken to create any legal relations, contractual or otherwise.

On Tue, Mar 10, 2015 at 5:39 PM, Nagendra <nagka...@gmail.com> wrote:

Hi experts,

Is there a shortcut to generate the physical SQL for the OBIEE report ran

I mean i go to admin manage sessions get it there after doing cache clearing in the admin tab

Is there a much better way to get the physical SQL which is must faster

let me know

Thanks

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 

Regards,
Girish Lakshmanan

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 

Regards,
Girish Lakshmanan

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 
-- 
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to

obiee-enterprise-methodology+unsub...@googlegroups.com

For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

--- 
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-methodology+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 

Robin Moffatt

Principal Consultant

M: +44 7841 207 265 • E: robin....@rittmanmead.com

S: robinmoffatt • T: @rmoff

www.rittmanmead.com

...

[Robin Moffatt]

Here's a write up of the method : http://www.rittmanmead.com/2015/03/obiee-nqcmd-tidbits/

tl;dr - set the SKIP_PHYSICAL_QUERY_EXEC request variable.

[Girish]

Good job writing all that up Robin.

Rgds
Girish

[Rebecca Widom]

This so great to hear!  My group recently finished a migration from Oracle Discoverer into OBIEE, and one of the features that our users really miss is the menu option "Show SQL."  They used to use it to have Discoverer write the basics of a SQL query that they could then use to match against external data they loaded into our system.  We've got a workaround with high log levels in production and a dashboard on usage tracking data, but it's far from ideal.  Any ideas on setting up an integrate GUI interface using this tool for ad hoc end users?

[Stewart Bryson]

Rececca:

Although the functionality described below is excellent, it’s not necessary for you requirement. Instead… all you need to do is enable the logging level for the repository to at least 2 to start showing physical SQL. This write the information to the nqquery.log file, but it also makes it available from within the OBIEE front-end. Once the logging level has been set (either system-wide or individually for each user with the LOGLEVEL session variable) you can view physical SQL for either your analysis, or anyone else’s. Click the Administration tab in the Analytics portal, and then choose Manage Sessions. Find the session you are interested in, and then select View Log. You’ll see the OBIEE Logical SQL followed by the Physical SQL.

Let me know if you have any questions.

Stewart

On Mar 24, 2015, at 9:34 AM, Rebecca Widom <rebecc...@gmail.com> wrote:

This so great to hear!  My group recently finished a migration from Oracle Discoverer into OBIEE, and one of the features that our users really miss is the menu option "Show SQL."  They used to use it to have Discoverer write the basics of a SQL query that they could then use to match against external data they loaded into our system.  We've got a workaround with high log levels in production and a dashboard on usage tracking data, but it's far from ideal.  Any ideas on setting up an integrate GUI interface using this tool for ad hoc end users?

[Rebecca Widom]

Stewart --

 

The issue is that we have 100+ authors doing ad hoc queries, and we don't want to give them all full Admin rights.  We just want them to be able to see the physical SQL of a query they're writing, or otherwise have access to in the catalog (e.g. some queries might have PII related to medical records, and not every user should be able to see those).  Is there a way to isolate just the access to the physical SQL within the Admin rights? 

 

Ideally, we could add a button to the advanced tab, so that in addition to seeing the logical SQL as they already do there, they could click to see the physical SQL parsed from that particular logical SQL.

 

Thanks.

 

Rebecca

[Aål•k]

Rebecca,  are they writing physical queries or creating analysis? Also I believe the mindset needs to  change discover is an old way  and OBIEE is the new way..  If both systems are running in parallel why can't the output be compared as part of smoke test?

My 0.025 pennies :)

--

[Stewart Bryson]

Based on the requirements and the options, Usage Tracking is your best bet. You can build a Dashboard around OBIEE usage, and one of the things that Usage Tracking collects is physical SQL.

You could build the button… but it would take less time to implement Usage Tracking and reporting capabilities around it… and you’d get a lot more than just physical SQL.

Stewart

[Christian Berg]

+1 on what Stewart just said. Have a look at what's in the SA406 in Lifecycle Admin - Usage Tracking and Query Analysis:

http://slc02ojw.oracle.com:7780/analytics/saw.dll?dashboard&PortalPath=%2Fshared%2F10.%20Lifecycle%20and%20Admin%2F_portal%2F10.20%20Usage%20Tracking

http://slc02ojw.oracle.com:7780/analytics/saw.dll?dashboard&PortalPath=%2Fshared%2F10.%20Lifecycle%20and%20Admin%2F_portal%2F10.21%20Query%20Analysis

C.

[Amin Adatia]

So how would the new way be such that the actual query needs to be hidden? What is so secret about a query?

 

 

Regards

 

Amin Adatia (am...@knowtech.ca)

KnowTech Solutions Inc. (www.knowtech.ca)

Mobile : +1-613-864-8378

[Jeff McQuigg]

>> Also I believe the mindset needs to  change

I agree with this statement. I have yet to ever come across a customer who has needed end users, even power users, to see physical SQL.  (I have enough difficulty getting developers to look at SQL)   OBI is a different beast than Discoverer, one where it is supposed to hide the complexities of the underlying physical model and resulting SQL so users can focus on getting actual Answers to business questions.  It kind of defeats the whole purpose of a modern BI tool if people are getting into the details of the data model and how SQL looks. 

This is most likely one of those tricky "technical requirements" that really isn't a requirement at all, and hence we all spend a lot of effort on something that provides little, no, or even negative value.  Seeing SQL is a solution, not a requirement.  What is the need that seeing SQL will fulfull?

For example, if the "requirement" is driven by questions about quality and accuracy of reports, Physical SQL is the worst way for a user to confirm the results are correct - use the regular bag of QA tools that we employ on any project; perhaps go the extra mile here and there if this is a major concern.  Maybe you can provide some admin access during QA cycles, or have someone on the IT team provide the SQL for a while - all in an effort to build a level of trust in a few (not all) key users.  But for QA purposes you don't need that in an ongoing basis in Production.

Unless there is a compelling reason, like some extreme Audit requirement (which sounds bogus as well), I would say it's not possible without an extreme amount of effort, and the solution, whatever it is, will be hacked together and become a maintenance, support and upgrade issue.  Your effort is better spent elsewhere. 

Effectively this is a Change Management issue not a technical issue.

Jeff M.

---

From: Aål•k <alu...@gmail.com>
To: obiee-enterpri...@googlegroups.com
Sent: Wednesday, March 25, 2015 8:24 AM

Subject: Re: [OBIEE EMG] Re: OBIEE physical SQL generator

[Girish]

Jeff,

You're absolutely right in an ideal world - but the world is full of oddities and far from ideal :)

In some companies, I've come across BI Authors who've had direct relationships with DBAs to help them debug or help with urgent reports that have intermittent performance issues for example.

Having the physical SQL handy would save them a lot of time fishing for it.

I know you're going to say that BI authors shouldn't be speaking to DBAs :) but again - as BI consultants we can't fix everything about a company we work for.

I wouldn't jump to conclusions saying its an invalid requirement without looking at the full context of the Organisation in question.

Kind Rgds

Girish

Regards,
Girish Lakshmanan

[Amin Adatia]

Especially if you are working on Exadata and the CBO picks an index which just makes no sense and you have to then add hints

 

 

Regards

 

Amin Adatia (am...@knowtech.ca)

KnowTech Solutions Inc. (www.knowtech.ca)

Mobile : +1-613-864-8378

 

[Amin Adatia]

Hi!

 

Why do you want to think how other people work? I like to be able to look at the SQL

 

 

Regards

 

Amin Adatia (am...@knowtech.ca)

KnowTech Solutions Inc. (www.knowtech.ca)

Mobile : +1-613-864-8378

 

[Christian Berg]

I fail to see how that would be of any importance, interest or even meaning to any "end user or power user" - the user groups Jeff mentioned. Exadata, CBO, indexes...you might as well speak about10-dimensional space in ancient Babylonian for all that an end user will understand.

[Amin Adatia]

Sorry but every End User is not just a dumb click – click -- run

 

 

Regards

 

Amin Adatia (am...@knowtech.ca)

KnowTech Solutions Inc. (www.knowtech.ca)

Mobile : +1-613-864-8378

 

--

[Christian Berg]

Nobody's saying that but just because you know how to drive a car like an F1 driver doesn't mean you can troubleshoot the engine management software. And neither would Hamilton or Vettel.

[Stewart Bryson]

I respect Jeff's opinion, and in general I think he's right. But people who are used to writing SQL will want to see it. They will usually be dismayed by what OBI generates... Not because it's wrong or bad... Just different. The dangerous part in this is if this different SQL is viewed as a failed solution because of it. They should be trained to gauge success on results, not SQL. If that screen is in place, then letting them see SQL is handy. 

Usage tracking provides this. No customization necessary. 

Sent from my iPhone

--

[Joel Acha]

My 2 pennies worth: in an ideal world, users should just have access to consume OBIEE content with a handful of these users being super users - Authors who can in addition create content which can be consumed by 1st set of end users. 

One of the main reasons of a company's investment in a tool like OBIEE is to abstract the complex technical logic away from end users. 

Questions should be asked when end users request access to the "under-the-bonnet" logic. OBIEE and other analytical reporting tools should be answering all of these questions purely from the reports/dashboards. Christian's analogy is spot on - we're good at building the reporting content and end users are good at making sense of the reporting content. 

If only it was that simple!

Sent from Outlook

--

[Stewart Bryson]

I agree Joel... But this case is slightly different in that all of the end-users are used to being in direct control of the SQL development and analysis. OBIEE should support a bunch of use cases, including this one. Over time, they can learn to focus on results. That's hard in the adoption phase. 

Sent from my iPhone

[chet justice]

And for those who are talking to their DBAs, I get that. There's a better way to do that though that is more proactive, not reactive. Instrument your connections pools.

See: http://www.rittmanmead.com/2015/03/instrumenting-obiee-database-connections-for-improved-performance-diagnostics/

Robin's previous efforts recorded on his personal blog: https://rnm1978.wordpress.com/2011/10/10/instrumenting-obiee-the-final-chapter/

This will allow your DBAs the ability to find the troublesome performance issues more easily and use the tools they have available to them (AWR, ASH, EM, etc). That SQL query you run over to them may run fine when they run it, but they'll be able to look at that previous run, if you have OBIEE instrumented properly, and gather all the context around that particular run.

[Stewart Bryson]

+1

Sent from my iPhone

[Amin Adatia]

Sorry again .. there are many things you need to be able to do when you have a car or when you learn how to drive a car. Calling 1-800-CAR-HELP does not work in remote places and when in -50C temperature and the help is 6 hours away. But anyway, I don’t think your analogy is correct. It appears that Lauda and Hunt knew a lot about the engines and performance tuning and making parts. And Hamilton and Vettel are hardly button pushers the way you, it seems, want the OBIEE users to be. J

 

So what kind of thing is this new OBIEE which wants to hide everything? Does having it available kill the execution?

--

[Girish]

+1 for the instrumentation approach. I quite like it.

I believe the approach can potentially be extended to other non-oracle  relational databases  and perhaps to non relational physical sources like SSAS or Essbase (if the DBA wishes to see MDX)

However, OBIEE is still the only place we can see cross database federated queries so I still think that Oracle should  consider giving a little box in the Analysis Advanced tab with a button that says "Get Physical Query". I would certainly benefit from it let alone the end users. It comes down to sheer practicality and UX.

[Stewart Bryson]

I agree with you Girish. There is no reason this shouldn’t be available. Hell… make it a new privilege if you are concerned with the implications.

There was once (11.1.1.6.2 I think) an Analysis View called “Logical SQL” that you could add to a Compound Layout to see Logical SQL. If they could bring that back, and also provide Physical SQL, that would excellent.

[Christian Berg]

Takes 5 minutes to write that Enhancement Request, guys :-)

[Stewart Bryson]

But Christian… I’ve already spent hours now responding to this email thread. Where will I find the time?

[chet justice]

I'm tacking on.

Agreed on going a more formal route.

Back to generating physical SQL though. The possibility that I haven't seen mentioned yet is being able to tie that into the RPD.

How nice would it be to make a change to a level, click a button and see the physical SQL generated (yes, yes, you'd have to feed it some parameters blah blah blah, don't get bogged down in the gory details)? 

Instead

1. Go to browser of choice
2. Run report (help me if the network is slow, or the database is slow, or...)
3. Open up admin tab
4. manage sessions
5. View log
6. Find SQL
7. Paste SQL in SQL Developer, Ctrl + F7 to make it human readable
8. Analyze

I'd like to skip 1-6 and replace that with my "blah blah blah" admission up above. 

Thoughts?

[Girish]

Agreed. The presentation services needs to play no part in this so can potentially get out of the way.

Having said that - This can only be conceived in an online mode though(as only nqserver knows how to generate physical queries) - and anyone who has worked on a large RPD(like the ones that BIApps ships with) will agree that online mode is just too slow to be practical.

If they can fix online-mode to usable levels, I think this would be a brill idea.

Rgds

Girish

Regards,
Girish Lakshmanan

[Amin Adatia]

And I would also want to see the Execution Plan used

 

 

Regards

 

Amin Adatia (am...@knowtech.ca)

KnowTech Solutions Inc. (www.knowtech.ca)

Mobile : +1-613-864-8378

 

[Christian Berg]

5 minutes off your Marcel comic app. Sorry mate.

[Christian Berg]

*Marvel obviously

[Robin Moffatt]

Logical SQL view is still there in  11.1.1.7.150120

Robin Moffatt

Principal Consultant

M: +44 7841 207 265 • E: robin....@rittmanmead.com

S: robinmoffatt • T: @rmoff

www.rittmanmead.com

Registered Office : Suite B, First Floor, Moore House, 11 - 13 Black Lion Street, Brighton, BN1 1ND, United Kingdom

Company No. : 6032852

VAT No. : 900 3839 48

Please note that this email communication is intended only for the addressee and may contain confidential or privileged information. The contents of this email may be circulated internally within your organisation only and may not be communicated to third parties without the prior written permission of Rittman Mead Consulting.  This email is not intended nor should it be taken to create any legal relations, contractual or otherwise.

[Stewart Bryson]

Interesting Robin. I see it on one VM but not on another one. Was your environment a straight upgrade to 150120?

Sent from my iPhone

On Mar 25, 2015, at 4:02 PM, Robin Moffatt <robin....@rittmanmead.com> wrote:

Logical SQL view is still there in  11.1.1.7.150120

<2015-03-25_14-01-54.png>

[Rebecca Widom]

Wow, really hit a nerve here!

We do have a dashboard based on usage tracking in place already, and authors can see the logical SQL on the "advanced" tab, but logical SQL doesn't address our requirements (below).  I was just wondering if there's a better solution for showing physical SQL that avoids having to save data from logs into a table.  Sounds like not.

Why queries need to be hidden: We have some users who write Answers with specific client information (name, SSN, other PII) in the filters, so that the queries themselves have PII that needs to be protected.  I know, I know, better to have parameters, but...  I don't control everything my users do, and when we proposed making query info public for other reasons we got major pushback.

As to why people need to see the physical SQL: It is totally a work around for not being able to write their own SQL, but being comfortable with Answers.  There are two real requirements. 1) Large extracts (more than 100K records).  This is definitely something we're trying to train people out of, and we're making some progress, but... 2) Flexibly matching external data against our main model.  Users are used to writing a Discoverer query and then just modifying it slightly to include their tables and running that.  We're looking at strategically placing some "user data" tables in the rpd, and that will cover many use cases, but not all because we can't know all up front.  We also have to figure out the best way to maintain those user tables over time...

Thanks all!

Rebecca

[Rebecca Widom]

Actually, I should say that the large extracts is rarely the real requirement, but then it comes back to training and so on.

On Tuesday, March 10, 2015 at 1:39:27 PM UTC-4, nag kandala wrote:

Hi experts,

Is there a shortcut to generate the physical SQL for the OBIEE report ran

I mean i go to admin manage sessions get it there after doing cache clearing in the admin tab

Is there a much better way to get the physical SQL which is must faster

let me know

Thanks

[Stewart Bryson]

Usage tracking is not saving logs to a table. The BI Server writes the data directly to a table. It's extremely easy to implement... It actually doesn't get much easier than usage tracking. 

Sent from my iPad

--

[Amin Adatia]

I an not wait until we just have conceptual SQL. I still think the decision to hide or remove the actual SQL was bad bordering on idiotic. Maybe sometimes the generated SQL can be a starting point in learning something new.

Regards

---

Date: Sat, 28 Mar 2015 05:47:56 -0700
From: rebecc...@gmail.com
To: obiee-enterpri...@googlegroups.com
Subject: [OBIEE EMG] Re: OBIEE physical SQL generator