Interpreting Siebel Analytics Log Information

NQServer.log

NQServer.log contains information on server startup, shutdown, rpd/subject area loading, initialization variables' success or failure, etc. Any other server error messages during execution are also reported in this file. The history of the server startup or shutdown and other error messages reported during server execution are all appended to this log file.

NQQuery.log and Interpretation

This file contains information about all logical and physical SQL executed by the Analytics server.
Query logging level can be set from 1 to 5 in the user object properties of the Siebel Analytics Administration Tool. In most cases, a maximum logging level of 2 is recommended. For some advanced troubleshooting, higher log levels may be set.

For implementations where users do not exist in the rpd file, such as when integrated with the Siebel Application, the logging level for a user can be set by an Initialization Block for a session variable called LOGLEVEL.

NQQuery.log contains information about all the queries issued by all the users. In order to examine a particular query for a user, the Manage Sessions view in Analytics Web, accessed from Admin > Manage Sessions may be very useful. This view extracts details of a particular query from the whole NQQuery.log that is easier to examine and debug.

Information at All Levels
The NQQuery.log file contains many various information about query execution:


The logical query


+++Administrator:140000:14000c:----2003/10/13 15:20:49
##############################################
-------------------- SQL Request:
SELECT
Products.Type,
Markets.Region,
RANK("Sales Measures".Units),
"Share Measures"."Share of Week Dollars"
FROM Paint
A unique string on top of the query uniquely identifies each logical query. In this example, it is +++Administrator:140000:14000c. This is how it is identified for extraction from the NQQuery.log file for the Manage Session view in Siebel Analytics Web.

The corresponding physical database query (or queries)


++Administrator:140000:14000c:----2003/10/13 15:20:49
-------------------- Sending query to database named SQL_Paint (id: <<41166>>):


select sum(T212."Units") as c1,
sum(T212."Dollars") as c2,
T226."PerDesc" as c3,
T218."region" as c4,
T239."Type" as c5
from
"Fact" T212,
"Market" T218,
"Period" T226,
"Product" T239
where T239."LevelX" = 'UPC' and T218."LevelX" = 'MARKET' and T226."LevelX" = 'WEEK' and T212."ProdKey" = T239."ProdKey" and T212."MktKey" = T218."MktKey" and T212."PerKey" = T226."PerKey"
group by T218."region", T226."PerDesc", T239."Type"

There are often multiple physical SQL queries generated which are identified with a unique identifier. In this case it is (id: <<41166>>).


Specific Information for Log Level 3
As well as showing the logical and the physical SQL, this shows the Logical Request. The Logical Request details how the Siebel Analytics SQL Engine intends to organize the data.

+++Administrator:140000:14000c:----2003/10/13 15:20:49
-------------------- Logical Request (before navigation):
+++Administrator:140000:14000c:----2003/10/13 15:20:49
-------------------- Logical Request (before navigation):
RqList
Products.Type as c1 GB,
Markets.Region as c2 GB,
Rank(Units:[DAggr(Sales Facts.Units by [Markets.Region, Periods.Week, Products.Type])] at_distinct [Markets.Region, Periods.Week, Products.Type]) as c3 GB,


Dollars:[DAggr(Sales Facts.Dollars by [Markets.Region, Periods.Week, Products.Type] SB Week)] / nullif( Dollars:[DAggr(Sales Facts.Dollars by [Markets.Region, Products.Type] SB All Periods)] , 0) * 100 as c4 GB,Periods.Week as c5 GB

Features to note about the Logical Request plan:

A single logical column may be shown multiple times even though the selected columns in Siebel Answers have different names. This occurs when the other columns are calculations based on this column.

Method used to calculate column aggregation.

For example, "Share Measures"."Share of Week Dollars" is calculated by taking the Dollars column results and then grouping them by the dimensions Market and Product at the dimension hierarchy levels of Region and Type. This is then summed at the level of Week in the Period dimension indicated by the syntax (SB Week).
The result is then divided by Dollars grouped by Market and Products but over all periods (SB All Periods). Note that the name after the letters SB is the name of the dimension hierarchy level. So, if a hierarchy level is named LVL1, the syntax will be SB LVL1, not the name of the logical column associated with that level. In this particular example, All Periods is the name of the dimension hierarchy level.
Dollars:[DAggr(Sales Facts.Dollars by [Markets.Region, Periods.Week, Products.Type] SB Week)] / nullif( Dollars:[DAggr(Sales Facts.Dollars by [Markets.Region, Products.Type] SB All Periods)] , 0) * 100 as c4 GB Periods.Week is part of the logical query plan although it was not selected in the initial SQL from Siebel Answers. This is because it is needed for grouping data. The purpose is indicated by the letters GB.

Periods.Week as c5 GB

Formula calculations such as RANK configured in the Siebel Answers Criteria tab interface are done by the SQL engine at this level.
Rank(Units:[DAggr(Sales Facts.Units by [Markets.Region, Periods.Week, Products.Type])] at_distinct [Markets.Region, Periods.Week, Products.Type]) as c3 GB

Specific Information for Log Level 4

At logging level 4, the Execution Plan is also recorded. This details how the Analytics SQL Engine intends to find the data required to satisfy the request.

+++Administrator:140000:14000c:----2003/10/13 15:20:49
-------------------- Execution plan:

RqList <<41374>> [for database 0:0,0]
D1.c1 as c1 [for database 3023:205,10],
D1.c2 as c2 [for database 3023:205,10],
D1.c3 as c3 [for database 0:0,0],
D1.c4 as c4 [for database 3023:205,10]
Child Nodes (RqJoinSpec): <<41387>> [for database 0:0,0]

Even for simple queries, the Execution Plan can be very complex. However, some useful information may be extracted.
For example, the following information available from the execution plan can be used to do the following:
Locating the target database for each query and selected column
Child Nodes (RqJoinSpec): <<4772>> [for database 3023:2457,10]
The identity of database 3023:2457 can be found by using the Query Repository tool in the Analytics Administration Tool. A search for all database objects will bring back a list of names and the internal id number assigned to that database. This is useful particularly in troubleshooting queries for data that is partitioned across databases.


Locating information related to column formula calculations such as CASE, CAST, and SUM that are done by the SQL engine as intended in the repository metadata design.
case when D903.c1 is not null then D903.c1 when D903.c2 is not null then D903.c2 end as c1 GB [for database 3023:205,10]
Verifying that the Logical Table Source Content Filters and data groupings are applied as intended.
DetailFilter: Product.LevelX = 'BRAND' and Market.LevelX = 'REGION' and Period.LevelX = 'MONTH' and Fact.ProdKey = Product.ProdKey and Fact.MktKey = Market.MktKey and Fact.PerKey = Period.PerKey [for database 0:0]
GroupBy: Market.region, Product.Type

Specific Information for Log Level 5

At logging level 5, there are row counts included in the Execution Plan. This allows you to track, what part of the physical SQL and which row count come from which part of the Execution Plan.

Execution Plan entry:
RqList <<4641>> [for database 3023:2457,10]
maps to the Physical SQL entry:
-------------------- Sending query to database named sql_supplier (id: <<4641>>):
which maps to:

-------------------- Rows 6, bytes 336 retrieved from database query id: <<4641>>

Log level 5 also provides some performance statistics about the query:

-------------------- Summary Stats: Total elapsed time 2, compilation 0, execution 2, db-connect 1 (secs)

Reference : Interpreting Siebel Analytics Log Information [ID 477427.1]