businessdecisionsystems.com

OBIEE – Scripted Testing

Posted on July 27, 2011 by John Lamont Watson

It is possible to run a pre-defined report against the BI Server via command line and read the output. The obvious benefit of this is that it allows us to create a suite of tests that could be ran at the touch of a button.

Input XML File

A single report is defined in OBIEE as XML; that XML is available in the Advanced tab when defining a request in Answers.

A report has two files associated to it in the Catalog; one of the files will contain this XML and the other file, an ATR file, is a binary file containing the permissioning for the object. For this blog we are interested only in the XML File; and that is all we need. We could also use the BI SQL tranlsation found in the same screen, however, I have found this less reliable.

Executing a Report

Once a report exists as an XML file then we can run it against the BI Server using the NQCMD command and the parameters below. The executable is stored in the directory OracleBI/server/Bin.

d datasource – set this as AnalyticsWeb
u username – such as Administrator
p password – such as SADMIN
s source file – this would be your XML file
o output file – the results will be recorded in this file
utf16 – defines the coding standard to use – I would use UTF16

An example of the command as I have used it is below:

nqcmd –d AnalyticsWeb –u Administrator –p SADMIN –utf16 –s test1.xml –o test1.txt

The Output Text File

The results can be displayed to screen and written to a text file. They will display the BI SQL for the report, which will be followed by the report results (note: there is little formatting and column headings will be referred to as saw1, saw2 etc).

In Summary

In previous projects I have scripted the running of a set of test reports stored in a test directory. Output files have then been interrogated automatically seeking a pass or fail. The complexity and flexibility of these scripted tests is really upto you. It can significantly reduce the time taken for testing cycles.

Posted in Scripting, Testing, obiee | Leave a comment

OBIEE – Query Hints

Posted on May 11, 2011 by John Lamont Watson

I’ve found quite a few blogs describing how to add an oracle database hint to a physical table in OBI; but they all stop short of showing how they can be added to an Alias Object. There is a section at the end of the blog addressing this.

Database Hints allow us to change suboptimal SQL Execution Plans; they simply allow us to provide more information to the optimizer and influence the plan executed. A database hint will take the form as below.

SELECT
   /*+ index(T222,PK) */
   ‘ROW_WID’ as c1
FROM
   W_CUSTOMER_D T222

OBIEE Query Hints

In OBIEE we can add a database hint to a table object in the physical layer; whenever this table is referenced in a query the hint will be applied. Importantly, a hint should use the table alias whenever a query specifies an alias for a table; the table itself should not be used. If you look at the underlying SQL of an OIBEE query, via the OBIEE Query Log, you will notice OBIEE will always use an alias for a table in the generated SQL; an alias taking the form such as T222 above.

The first step to adding our hint to a table is to determine the alias that OBIEE will use in the generated SQL. Select to Query Repository from the Tools Menu of the Administration Tool. The Query Repository Tool will open, as in the image below. Enter the Name of the table in the Name textbox and select Physical Table from the Type menu; click query to run the tool. Notice the use of the wildcard character, *.

In this example you can see we have a physical table in the database, W_CUSTOMER_D, and that I have created an Alias ojbect based on that table of Dim_W_CUSTOMER_D. In the ID column the two objects have IDs of 3001:111 and 3001:222 respectively. If we were using the table in our query, we can deduce we will use an alias of T111. Actually we used the Alias object and the database alias generated would be T222. You can see it is the latter segment of the ID that is used.

We can now add the hint; a hint can not be added to an OBIEE Alias object, only to an underlying Table object. We open the properties for the table object and add the hint to the text box as below.

You can see that the hint is applied to the OBIEE Alias object, T222; but we are creating that definition on the underlying Table object. If there were no Alias object and the table itself were used in the query then we would need to use the SQL generated alias of T111.

Hinting an Alias Object

But what do we do if we have multiple Alias’ objects sharing the same underlying Table object; this happens all the time.

We can’t apply the hint to the Alias object itself; instead we apply it to physical joins to and from the Alias object. The screenshot below shows where we apply the hint for the Alias table above.

You can see that we’ve populated the hint textbox referencing the SQL alias T222. Whenever this join is used the hint will be added to the query.

Posted in Performance, RPD Metadata, obiee | Leave a comment

OBIEE – Report Selection Prompt

Posted on April 4, 2011 by John Lamont Watson

I was asked yesterday how to create View Selector type functionality that will allow the user to select between reports, rather than just views of the same report. I have a feeling this information must already be available, but I said that I’d provide instructions on how to achieve this and may as well add it to my blog.

I will go through the steps with screenshots. We want to create 2 reports, Option 1 and Option 2; we need to create a third report that will return either true or false (results or no results). Both reports, Option 1 and Option 2 will be placed on the Dashboard, both in their own Sections; and both sections with Guided Navigation making use of the conditional request. We create a Dashboard prompt giving the options of Option 1 and Option 2; and add a filter to the conditional report so that it filters by the value selected in the prompt. Essentially if Option 1 is selected in the prompt then Report 1 will be displayed; and if Option 2 is selected then Report 2 will be displayed. Reading through this sounds very complex, but it isn’t really – if you haven’t understood then follow the steps below.

Create the Dashboard Prompt

The Dashboard Prompt will display a list of the Reports available; we can enter anything that we like for each option. To achieve this we must use SQL to generate the Show Values. OBI forces us to select an existing column to populate the prompt, but we get around this by using the expresssion CASE WHEN 1=2; the expression will never result to true and will always show the result of the else statement, our option. We create a SQL statement for each option we would like in the Prompt and union the statements together (in the order that we would like them to appear).

Note: The Column used should match the values you would like to generate

Prompt Show SQL

For the SQL above, obviously change the references to “column”, “table” and “Business Model”. Once happy with your SQL I would usually select the Preview Button to check my code.

Test Prompt Show SQL

For this functionality you would usually not want an ‘All Choices’ option; uncheck its inclusion. We should also choose to default to a Specific Value to restrict the list to only valid options. Click on the elipses button and type your preferred default value from the last - no need for quotes, just the text itself. I would usually verify that the default value is working by viewing the preview again.

Updated Prompt

The only remaining task is to populate a Presentation Variable using the Set Presentation Variable Drop Down. I have created a variable pVar_ViewOption. You will also probably want to relabel the prompt to something more meaningful and then you can save it.

Set Presentation Variable

Create a Conditional Report

We need to create a conditional report; the report will filter by the presentation variable created. We will design the report to return values when one Prompt option is selected and return no values otherwise. Essentially the report will return true or false, based upon the option selected in the prompt.

We need only a single column in the report; which should be the column referred to by the prompt. Similar to the expression used in the prompt, we use a CASE statement to always return the relevant option; in this case the preffered option.

Conditional Column Expression

We now add the filter to the conditional report to filter this column by the Presentation Variable. If the option displayed by the column is selected then the report returns results; otherwise it returns no results.

Conditional Filter

The Conditional Report is complete. We also need to create the reports to be displayed with each option. For this example I’ve created one for each of the 2 options. So thats 3 reports in total and a prompt. The screenshot below shows these object in My Folder.

My Folder

Configure the condition on a Dashboard

Create a section for the prompt and another for each report.

Dashboard Layout

We need to use Guided Navigation on each of the Report Sections; we will only show each section based upon the results returned by the Conditional Request we have created. The Guided Navigation setup for Report Option 1 is given below; notice that it returns a report on the condition the conditional report returns results (ie the prompt selection is our preferred option).

Report Option 1 Guided Navigation

Report 1 - Conditional Navigation

We need to set Guided Navigation for the second report to show when our conditional report returns no results, as below.

Report 2 - Conditional Navigation

And thats the process complete. When we save our Dashboard and view the page our prompt is shown and defaults to the preferred option; the guided navigation kicks in to display the first report and not the second.

Test One

And then when we select the other option in our prompt the guided navigation kicks in to display only Report Option 2 section, and not our other Report Section.

Report Two Test

It is worth noting that behind the scenes all three reports will be ran by the BI Server. I wouldn’t be concerned unless the approach causes too much load on the server. However, this approach should not be used to improve performance; it doesn’t work like that unfortunately.

Posted in Presentation Services, obiee | Leave a comment

OBIEE – MSSQL and Dates

Posted on February 23, 2011 by John Lamont Watson

My first time working with OBIEE on an MSSQL database. I have a base script that I use to create and populate the Date Dimension on an Oracle Database. I have created the equivelent for MS SQL.

MSSQL and Dates

For a fuller description on creating an OBIEE Date Dimension please follow the link. The blog uses an Oracle Database as an example, but you could replace the Oracle SQL Script with the T-SQL script above and follow the rest of the article.

Good Luck!

Posted in RPD Metadata, Time & Dates, Uncategorized, obiee | Leave a comment

OBIEE – 2 Way Merge

Posted on February 4, 2011 by John Lamont Watson

The 2 Way Merge, or Parentless 3 Way Merge, is the most reliable method that we have, out of the box, for merging mutliple repository files. If you have no idea what I’m talking about then follow the link to understand the OBIEE 3 Way Merge.

How does it differ from the 3 Way Merge?

Usually we would checkout a Modified RPD from the MUD, taking a snapshot at checkout called the Original RPD in the process. When we checkin our changes then we take a subsequent snapshot of the MUD called the Current RPD. We merge the Current RPD with the Modified version, using the Original to help understand the source of conflicts.

We may want to merge 2 entirely different RPDs from different streams of work. I would say that it is good practice to keep a Trunk (or baseline) RPD and consider this as the Current RPD. Additional RPDs can be merged into this baseline; they should be merged one at a time and each time regarded as the Modified version. A blank RPD should be created and this should be regarded as our Original version.

Files required for the Merge

Steps to merge into a single Merged RPD are detailed in the next section.

Creating the Merged RPD

This is not the only method of merging RPDs, but it is quite reliable; there is a short discussion at the end of the blog, following the How To steps.

Merging 2 Repository Files

Make sure that you have the three source files, as described in the previous section. We need to open the Current version, which will be your Baseline. I would also take a backup.

Once open, select Merge from the File Menu

: Select Merge from File Menu

You will be asked to select the Original Repository; this will be the blank RPD created

: Select the Original RPD

In the Merge Repositories Window, click to Select your Modified RPD file

: Click to Select Modified RPD

Browse to and select the Modified version, the in-coming RPD.

: Select the Modified Branch RPD

You will need to provide valid credentials to login

: Enter Modified Login Credentials

In the Merge Repositories Window you have all source files named; select which RPD to take each conflict from. Once all conflicts are resolved you may click Merge.

: Resolve any Conflicts & click Merge

Once the Merge is complete, click Yes to perform a consistency check; you should do this

: Click Yes to check consistency

All going well there should be no issues; click Close to complete the process

: Click Close to complete check

The process is quite straight forward; and although you will find some niggling issues, in the main it is a reliable one.

Points to Note

The main problem with this approach is that any objects that do not translate into the Presentation Layer will not be included in the Merged RPD. I think it is rare this issue will occur; this currently occurs with a client where we have a database used only for a session variable; the database is not included in the Merged RPD and the initialization pool errors during the conssitency check as it does not relate to any Connectin Pool.

To resolve this issue you can manually copy and paste the Database Object into the Merged RPD at the end of the process. If the process is likely to repeat with a fequency then we can use UDML scripting to apply the missing objects following the Merge.

There are other methods of merging Repositories, but I find this method most reliable.

Posted in RPD Metadata, Release Management, Uncategorized | Leave a comment

OBIEE – Repository Merge

Posted on February 3, 2011 by John Lamont Watson

The OBIEE Multi User Development Environment makes use of the three way merge process. The process itself is described below. This is useful when planning mechanisms in a multiuser development environment.

When we Checkout a Project we create two RPD files on our local development machine; one is a snapshot of the Shared MUD RPD, generally referred to as the Original; the other is a subset of the MUD RPD containing only our selected project(s), generally referred to as the Modified version. The Original version will be used during Checkin to help determine why any conflicts have occured between the shared MUD RPD and the Modified version.

The Checkout Process

Changes are configured in the Modified version of the RPD. When we want to check these changes into the Current (shared) RPD then we start by Merging Local Changes. When we do so, as shown in the diagram below, a lock is placed on the MUD RPD and a current snapshot is copied locally; the lock ensures the validity of the snapshot (Current.rpd).

Merging Local Changes

The Original, Modified and Current RPDs will be analyzed and conflicts displayed to the user if choices are required. Once conflicts are resolved the 3 way merge will be actioned, producing a single Merged RPD. The lock remains on the MUD.rpd; again, ensuring the validity of your Merged.rpd.

The 3 Way Merge

You can see from the schematics that throughout this process the MUD RPD has been locked to prevent other changes being made. The final step to the process is for us to Publish our Merged RPD, releasing the lock and overwritting the MUD.rpd.

Publishing the Merged RPD

That is the process completed; the MUD is unlocked and other users are now free to Checkin their changes. It should be noted that at any point in the process, until we Publish, we could back out our changes by Discarding Local Changes; this would delete our local files and release the lock on the MUD enabling other developers to checkin their changes.

Discarding Local Changes

But How Does this Help Me?

We can action a 3 way merge manually by selecting Merge from the File Menu and certainly, as a developer, there will be a point where you will need to merge one code stream into another, or more correctly a branch of code into the trunk. There are many ways to achieve this, all with their pros and cons; I have found the most reliable method to be the Parentless 3 Way Merge.

Posted in RPD Metadata, Release Management, obiee | Leave a comment

OBIEE – Multi User Development

Posted on February 2, 2011 by John Lamont Watson

The concept of the MUDE, or Multi User Development Environment, was introduced way back with Siebel Analytics 7.7. In theory it is really quite simple; in practice, although useful, it can be tedious.

Setting up the MUD

Set up is very easy. Primarily all you need is a shared folder on a filesystem, accessible to all of the relevant developers.

Open the Administration Tool
Select Options from the Tools Menu, as in the screenshot below.

Select Options from Tools Menu

Select the Multi-User Tab
Browse to the Folder containing your Multi User RPD File
Enter your Name and Click OK

Complete the Options' Multiuser tab

And actually thats it; you’re setup. Each developer that requires access must follow the same process.

Using the MUD

For the rest of this article we will use the samplesales.rpd file as an example. The RPD is organized into 2 Projects. When we want to work on the RPD in a shared environment we check out the project that we want to work on; make our changes; merge our code back into the shared repository and then, once totally happy, publish our merged repository to replace the current version.

Checking Out a Project

Select Multiuser, Checkout from the File MenuSelect Multiuser Checkout from the File Menu
You will be prompted for a Username and Password for the MUD RPD

: Provide Username and Password

From the Project List, select the Project(s) that you would like to Checkout and Click OK

Select Project(s) to Checkout

A subset of the Current Repository will be created locally, containing only the project(s) selected. Choose a name and location for the file; I would just accept defaults; and click Save.

Save your Modified, or subset, Repository File

The subset Repository, known as the Modified version, is now stored locally and should be open in the Admin Tool. This is the point at which you should generally make changes.

Merging the Project back into the MUDE

Select Multiuser, Merge Local Changes from the File Menu

Select Multiuser Merge Local Changes from the File Menu

You should receive a request for Lock information, as below. If you don’t you will probably receive Information that the Repository is already locked. The information may tell you when it is likely to be available, or you may have to contact the person currently locking it. You don’t need to Complete the Lock Information, but it may help your fellow developers. Click OK.

Enter Lock Information and Click OK

You will get a Merge Repositories Dialog Box. Any conflicts between your Modified RPD and the Current RPD will be detailed here; for each conflict you must specify which specify which RPD is correct; the Merge button will not be available until all conflicts are resolved. Resolve any conflicts and click Merge.

Note: When you Checkout, a copy of the project(s) checked out will be contained in your Modified Repository, stored locally; this will contain any changes that you have made during Checkout. At the point of Checkout a copy of the whole MUD Repository is also taken and this is referred to as the Original Repository. The Current Repository is the MUD, containing any changes made since your Checkout. The 3 versions of the RPD are required for the 3 Way Merge.

At this point you should check Consistency of the Merged Repository, Click Yes

Click Yes to Check Consistency

All going well, you should recieve a blank sheet as below; click to Close the Consistency Check

Click to Close the Consistency Check

You should now have a copy of the Current Repository with your changes merged. At this point we have not committed to the merge; we can still Discard Local Changes; although we would lose our changes, we would not impact the MUD. We have also locked the MUD so no other changes can be made until we do either discard these changes or publish them to overwrite the Current Repository.

Publishing the Merged Repository

Select Multiuser, Publish to Network from the File Menu

Select Multiuser - Publish to Network

At this point you will be asked again whether you would like a Consistency Check. If you have made no further changes since the Merge then there is no reason to. However, you are able to make changes post merge, whilst the Repository is still locked; this second Consistency Check should be used in this case.

When to Configure the Merged RPD

We should configure a locked RPD, post Merge, when our changes could be considered global to the RPD; such as changing the Administrator password. It is not necessary, but may be useful for example if we introduced a new Dimension that joins to every Fact. And as you start to use the MUD you will come to learn there are a couple of strange ‘features’ that can be overcome by editing in the locked RPD.

You should remember that this is not a risky approach as you are not committed until you Publish your changes; but locking the RPD for longer periods may impact other developers.

OBIEE Project Management

So far I’ve not mentioned how you organize your RPD into Projects, but you will need to know how to do this and how to make changes. You can manage projects in either the Checked Out or Locked RPD.

Select Projects from the Manage Menu

Select Project from the Manage Menu

The Project Manager will open; double click to view or edit an existing Project

Double Click on an existing Project to View it

Note: To Create a new Project Select NewProject from the Action Menu

In the Project Definition Window the applet on the left shows Objects available in the RPD and the applet on the right shows those Objects included in this Project. Use the Add and Remove buttons to manage the Objects contained withint this Project and click OK.

Choose the Objects contained in the Project

Note: An object can be contained in more than one Project; and indeed the same Project can be checked out by many different users at the same time.

The next time you Checkout, any new projects should be displayed; and edited projects should reflect any changes made.

Posted in RPD Metadata, Release Management, obiee | 1 Comment

OBIEE – Performance

Posted on January 14, 2011 by John Lamont Watson

I don’t think that I’ve been involved in a project yet that did not meet performance hurdles. We have always overcome them, but do tend to be found towards the end of the project when things are already stressfull enough. I have decided to blog about the subject and hopefully help some people.

Performance is a large issue to cover; I guess that I will create quite a few blogs that will hopefully stitch together nicely. This is really just an introduction to the text.

What can we tune

When you come across obiee Performance issues then there are 4 main areas that you may look at to try and improve performance.

SQL Tuning
Database Tuning
System Tuning
Network Tuning

Of these areas it is most likely that you will spend your time in the first area, SQL tuning, and that is area that I will cover. The BI Server is really an engine that uses the definitions given in the RPD to translate user Requests into SQL, submit them to the relevant datasource(s) and return the result. The definitions determine the SQL ultimately created; we use the definitions in the RPD file to ultimately determine the SQL generated.

Where do we Start

Because tuning is such a big subject when we get hit with a problem it can be quite overwhelming to resolve it; when the issue is down to SQL Performance then we can follow a 3 step process. Actually, the process is similar regardless, but we will only be looking at SQL Performance.

1 - Isolate the Problem to a Single SQL Statement

To make the problem manageable then we need to reduce the issue to its most basic form; for example remove tables and columns in the query to only those causing the issue. It may be that there is an issue with the join to one dimension from the fact and another issue to another dimension; treat them differently, they may well be different issues. They may be the same issue, but breaking the issue down will make it more manageable and help to understand the overall problem.

To isolate the problem, the first thing that we do must be to derive the SQL submitted by the BI Server. We have a blog on the obiee query log, which will help if you don’t know how to do this. If you still have difficulties finding a query then it could be down to caching; we have a blog on purging obiee server cache, which would help with this issue.

2 – Analyze the SQL to determine the cause of the problem

Once you actually locate and analyze the problem you will usually find that the problem itself is quite easy to fix; the difficult, or at least time consuming bit, is in the analysis.

To analyse the SQL we need to extract it from the query log and then use a tool such as Oracle SQL Developer, or Toad, to submit it to the plan table and then read the explain plan from the plan table – see my blog on the obiee explain plan.

3 – Fix the Problem

As suggested earlier, it is very rare that there is not a solution to a performance problem, although the fix may vary in its complexity.

Every problem is unique, but hopefully some of the problems that I mention may suggest some solutions to help you.

Posted in Uncategorized | Leave a comment

OBIEE – Explain Plan

Posted on January 14, 2011 by John Lamont Watson

To analyse the SQL generated by the BI Server you will most likely use the Explain Plan; this blog hopes to increase your understanding and ultimately help resolve performance issues.

What is an Explain Plan

An execution plan, sometimes called a row source tree, is logically a tree of row source operators, where each operator is just a function written in C. The functions are mutually exclusive; they have no knowledge of one another. Some operators are hidden, such as the STATISTICS operator, and are not visible in the Explain Plan. Some operators also have options, affecting their behaviour; such as the INDEX operation which has options such as UNIQUE, RANGE SCAN, SKIP and SCAN, etc.

We can run and display an Explain Plan to help us understand which functions are being chosen by the Oracle Optimizer. Through experience you will learn that some functions do certain jobs better than others; we can manipulate the SQL or DB settings to ensure the Optimizer chooses the better function.

Importantly, the Explain Plan contains the following information regarding your query.

Ordering of the tables referenced
Access method for each table
Join method for each join operation
Data operations, such as filter, sort or aggregation

And in addition:

Optimization (Cost and Cardinality)
Partitioning
Parallel Execution

It is worth bearing in mind when looking at obiee queries that the BI server harbours its own Execution Plan, which may be the same or different to that of the database. We have a blog on obiee cursor sharing; to help overcome this issue.

Running an Explain Plan

A table exists, the Plan Table; it is set up automatically, that stores the Explain Plans for all users. To submit a query to the table we must precede the query with the command EXPLAIN PLAN FOR, as in the example below. More information is available in abundance online for those interested.

Submitting a query to the Plan Table

Displaying the Explain Plan

Once we have asked for an Explain Plan to be made available through through the Plan Table. We need to display to query to read it. The command below is one way to display the contents of the Plan Table. The command below will get you by, but there are more options available; again information available all over the Interent.

Displaying Plan Table

Understanding the Plan Table Output

More to follow …

Posted in Uncategorized | Leave a comment

OBIEE – Query Log

Posted on December 22, 2010 by John Lamont Watson

The obiee query log, NQQuery.log, exists in the directory …/OracleBI/server/Log. As the name suggests it records the queries requested from the BI Server. The initials NQ remind us of obiee’s history, being originally created by a company named NQuire; these clues are littered throughout the application.

The Logging Level

The contents of the log file are, or at least the level of detail is determined by a Repository Variable, LOGLEVEL. The variable can accept values from 0 to 5, 5 being the most verbose. As a general rule I set the variable to 0 in production, effectively disabling loggin and improving performance; 2 in development, setting to 3 on occassion when 2 is not enough to resolve the issue.

Setting as a System-Wide Session Variable:

We can set the logging level globally for all users. We can set this in the RPD using theAdministration Tool and this logging level will take precendence of other logging settings.

Select to manage Variables from the Manage Menu
In Variable Manager Select New, Session, Variable from the Action menu
Give the Variable the name of LOGLEVEL, a value of 2 and click OK
Close Variable Manager

Setting with the User Object for a particular user:

We can also set the LOGLEVEL by user, however, this value will be overridden by the Session Variable if it has been created. To set the Logging Level for a specific user, follow these steps.

Select to manage Security from the Manage Menu
In Security Manager Select the Users Object Type from the left pane
The right pane lists the user objects, open the properties for an a user object
Give the Logging Level a vlue 0 to 5 and click OK
Close Security Manager

Setting for a specific Request:

You can set the LOGLEVEL variable for a specific request. In this example you will update the session variable LOGLEVEL before the request is run and, as such, this will take precedence over the Session Variable set in the RPD.

Select to Modify a request or create a new one
Navigate to the Advanced Tab
In the Prefix Text Box type SET VARIABLE LOGLEVEL=2;
Now when you navigate to the results tab an entry will be made to the log file
Save the changed request if you would like always to be the case

In the above example there is no space either side of the equals sign; the statement must end in a semi colon and, again, there is no space either side of it.

Viewing the Log File

There are a couple of ways to view the query log. All query logging is recorded in the Query Log File, NQQuery.log; we can view this file using a text editor if we have access to the OS. OBIEE uses a log viewer utility nQLogViewer to structure this log file into a more useful format called the Session Monitor; we can use the Session Monitor online if we have administrative access. And finally we can use the nQLogViewer utility ourselves to view specific information, but again we require OS access.

Using the Session Monitor:

Select Administration from the Settings Menu
From the Administration Window select to Manage Sessions
The Session Monitor will open

The Session Monitor is split into two tables, Sessions and Cursor Cache. Sessions lists the users currently logged on, or with a thread still running; and the Cursor Cache table displays the queries shown in the log file, NQQuery.log.

The session records show sessions by user ID and other details such as the client machine name, IP address, browser details, timestamp of login and timestamp of last request.

And the Query records show each request by ID; each record detailing the user ID, the status of the query, how long it has taken to run, when it was run, the query itself, its location in the catalog (if appropriate) and the number of records returned. In the Action column you can select to view the log in more detail.

Session Monitor Query Record

Using the Viewer Utlity:

We can use the utility nQLogViewer to view log records; as stated above, each entry in the query log is tagged with the user ID of the user who issued the query, the session ID of the session in which the query was initiated, and the request ID of the query; and we can use these details to restrict the utility to a spcific query.

To use the nQLogViewer utility we must open a command window in either Linux or Windows. Navigate to …/OracleBI/server/Bin. Use the command with the syntax below.

nqlogviewer [-u<user_ID>] [-f<log_input_filename>]
[-o<output_result_filename>]
[-s<session_ID>] [-r<request_ID>]

I would like to note that the request ID is generated in a circular manner; when IDs run out then new requests will re-use existing IDs. This may explain why different queries can sometimes display in a single log record.

Interpreting Log Records

Whether looking at the Query log directly, or using the Viewer Utility or via the Session Monitor the Log Record is the same; they all come from same log file. A log record can be broken down into sections; these sections are described brielfy below.

The log will start with the user ID for the requesting user (followed by the user Account code in hex) and the timestamp for running the request.

SQL Request:

This section provides the Application SQL produced in the Presentation Layer; it does not define relationships, just the columns used. It can be used to rerun the exact query in the Answer part of the application; although it is less reliable than using the request xml (not available in the log). It will also show additional attributes such as Pre and Post SQL and the path or location of a saved request.

General Query Information:

This section describes the repository, business model and catalog from which the report has been run; usually useful in putting together query statistics. I will providing a later log that goes into more detail on this.

Logical Request:

Displays the columns used in the report from the Logical Layer; this section will be displayed when the Log Level is set to 2 or above.

Execution Plan:

When we set the Log Level to 2 or above then we also get to see the Execution Plan.

Database Query:

Identify this section from the initial text Sending query to the database named <data_source_name>; the data_source_name is the name of the data source to which the Oracle BI Server is connecting. If the server is connecting to Multiple database each query will have an entry in the log.

This is the section of the log that you are most likely to be interested in and is the most useful. It provides the actual SQL generated and executed against the database. It can be used to verify that the application is behaving as required; especially useful when troubleshooting; and also to support performance tuning.

I would like to point out that the SQL that you read at times may not refelct the SQL that is actually executed on the database. You may notice sometimes that the SQL in the log executes very well, but the OBIEE query seems to perform poorly. This is due to this unexpected difference. I have blogged about this particular risk to OBIEE Performance.

Query Status:

Essentially the status section will tell you whether the query has succeeded or failed. You will usually know this before looking at the log, but I guess its worth having it in there.

Log Retention

In terms of configuring the query log we can use the parameter USER_LOG_FILE_SIZE to set the limit of the Query Log. When the size of the log file grows to half of that value it will be renamed to NQQuery.log.old and fresh queries will be logged to the recreated file NQQuery.log. Other than the size of the file system there are no further limitations to the size of the log file. An example of the NQSConfig.INI file is given in the screenshot below.

Query Log Sizing in the NQSConfig.INI

Happy Logging and Happy Christmas!

Posted in Logs, Presentation Services, obiee | Leave a comment

businessdecisionsystems.com

OBIEE – Scripted Testing

OBIEE – Query Hints

OBIEE – Report Selection Prompt

OBIEE – MSSQL and Dates

OBIEE – 2 Way Merge

OBIEE – Repository Merge

OBIEE – Multi User Development

OBIEE – Performance

OBIEE – Explain Plan

OBIEE – Query Log

Recent Posts

Recent Comments

Archives

Categories

Meta