Setting Priority Group Numbers for Logical Table Sources

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For Example , A column is mapped to multiple LTS(LTS1 & LTS2)  and you want to give first priority to LTS1 whenever this particular column is being fetched in the report so in this case you can assign a higher priority to LTS1.

To assign priority group numbers, rank your logical table sources in numeric order, with 0 being the highest-priority source and so on.
Assigning priority groups is optional. All logical table sources are set to priority 0 by default.

See below snapshot:

OBIEE 11g : Query Logging

OBIEE 11g allows controlling the information that is being logged.

We can fetch the logical as well as physical query by enabling log level.

You can enable logging level for individual users, system user, and individual request.

OBIEE provides different levels of logging as from 0(no logging) to 5(all the information).



Query Logging Levels Description:

Logging Level	Information That Is Logged
Level 0	No logging.
Level 1	Logs the SQL statement issued from the client application. Also logs the following: ·         Physical Query Response Time — The time for a query to be processed in the back-end database. ·         Number of physical queries — The number of queries that are processed by the back-end database. ·         Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times). ·         DB-Connect time — The time taken to connect to the back-end database. ·         Query cache processing — The time taken to process the logical query from the cache. ·         Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this delta time is negligible, the elapsed time equals the response time. ·         Response time — The time taken for the logical query to prepare, execute, and fetch the last record. This matches the TOTAL_TIME_SEC that is logged in usage tracking. ·         Compilation time — The time taken to compile the logical query. ·         For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.
Level 2	Logs everything logged in Level 1. Additionally, for each query, logs the repository name, business model name, subject area name, SQL statement issued against the physical database, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.
Level 3	Logs everything logged in Level 2. Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails. Do not select this level without the assistance of Oracle Support Services.
Level 4	Logs everything logged in Level 3. Additionally, logs the query execution plan. Do not select this level without the assistance of Oracle Support Services.
Level 5	Logs everything logged in Level 4. Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Oracle Support Services.
Level 6 and 7	Not used.

Table Source : Oracle Docs-Managing Query Log 

Viewing logs:

Logs get stored in NQSQUERY.log file.

It is located at,

<instance_home>/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/

Also logs can be viewed from dashboard administration.

We can fetch logs from Administration>Manage Sessions

Here we can see all the logs generated for different users.

OBIEE 11g : Implicit Fact Column

An Implicit fact column is used when we have multiple fact tables and the report is getting generated using only dimension columns.

A User may request a report where it may have only Dimensions and no Fact columns. In this case, the server may sometimes get confused as to which fact table should it join to fetch the data. So it joins to the nearest fact table and pulls the data through it. So the report output obtained may be different from what the user is expecting.

So, in order to avoid this kind of error,we need to set Implicit Fact Column.

The goal of this is to guide the BI Server to make the best choice between two possible query paths.

We can set a fact attribute (measure) as an implicit fact column.

We can also create dummy implicit fact column on and assign any numeric value to it.

We can set implicit fact column in presentation catlog properties.


1.  Goto properties of presentation catlog in presentation layer.

2.  In implicit fact column section click on set and select any measure column from fact table.

3.  Click OK.

4.  Save your work.

Instead of selecting any fact measure column as implicit fact column, we can also define a dummy implicit fact.

1.  Create a Physical Column in Fact table in Physical Layer.

2.  Name it as Implicit_Column.

3.  Drag this column in Fact table from BMM layer.

4.  Double click on logical table source of fact table.

5.  In content tab, assign any numeric value to Implicit_Column.

Once this is done we can set this column as Implicit Fact Column in Presentation catlog as mentioned above.

Query to get GL Account and Account Segment Description

1.Query to get Account Code Combination Description

Code (SQL):

SELECT gl_flexfields_pkg.get_concat_description
                                          (chart_of_accounts_id,
                                           code_combination_id
                                          )
            FROM gl_code_combinations
 

2. Query to get Account Segment Description

Code (SQL):

SELECT gl_flexfields_pkg.get_description_sql
                                     (chart_of_accounts_id,--- chart of account id
                                      1,----- Position of segment
                                      segment1 ---- Segment value
                                     )
FROM gl_code_combinations
 

Get Invoice Number from PO Number

SELECT ap_inv.invoice_num,
po_hdr.segment1,
po_hdr.org_id
FROM ap_invoices_all ap_inv,
ap_invoice_distributions_all ap_dis,
po_distributions_all po_dis,
po_headers_all po_hdr
WHERE ap_inv.invoice_id = ap_dis.invoice_id
AND ap_dis.po_distribution_id = po_dis.po_distribution_id
AND po_dis.po_header_id = po_hdr.po_header_id
AND ap_inv.org_id = po_hdr.org_id
AND po_hdr.segment1 like ‘Give PO Number’
–AND ap_inv.invoice_num like ‘Give Invoice Number’

SQL QUERIES TO GET ORGANIZATION FROM REQUISITION, PURCHASE ORDER, PO RELEASE

From Requisition:

    select hr.name, prh.segment1, prh.org_id

    from po_requisition_headers_all prh,

    hr_all_organization_units hr

    where prh.org_id = hr.organization_id and

    prh.segment1 = '&Enter_Req_Number';

From Purchase Order:

    select hr.name, poh.segment1, poh.org_id

    from po_headers_all poh,

    hr_all_organization_units hr

    where poh.org_id = hr.organization_id and

    poh.segment1 = '&Enter_PO_Number';

From Purchase Order Release:
    select hr.name, poh.segment1, por.release_num, por.org_id

    from po_headers_all poh,

    po_releases_all por,

    hr_all_organization_units hr

    where poh.org_id = hr.organization_id and

    por.org_id = poh.org_id and

    poh.po_header_id = por.po_header_id and

    poh.segment1 = '&Enter_PO_Number' and

    por.release_num = '&Enter_Release_Num';

BIACM Queries

When Creating a Document on Domain values that have been mapped to target values etc in BIACM we can use the BIACOMP schema to derive various pieces of information.

Below are some querys that can be used to get information on mappings etc.

Query to get Offerings enabled
select offering_code,offering_name
from c_bia_offering
where installed_flag = 1;


Query to get offering, implementation project, task list

SELECT ATLT.TASK_LIST_NAME,
  ATT.Task_name,
  AIPT.IMPL_PROJECT_NAME,
  AIEI.*
FROM ASM_IP_EXECUTION_ITEMS AIEI,
  ASM_TASK_LISTS_TL ATLT,
  ASM_TASK_LIST_ITEMS ATLI ,
  ASK_TASKS_TL ATT ,
  ASM_IMPL_PROJECTS_TL AIPT
WHERE ATLT.TASK_LIST_ID   =AIEI.TASK_LIST_ID
AND ATLI.TASK_LIST_ITEM_ID=AIEI.TASK_LIST_ITEM_ID
AND ATT.TASK_ID           =ATLI.ITEM_TASK_ID
AND AIPT.IMPL_PROJECT_ID  =AIEI.IMPL_PROJECT_ID
AND ATLT.language ='US'
AND ATT.Language  ='US'
AND AIPT.Language ='US'
  --AND TASK_LIST_NAME= 'Oracle Financial Analytics' --Give the required Module name here
AND AIPT.IMPL_PROJECT_NAME
LIKE 'Financials _ P and S Implementation Project' ; -- Give your implementation project name here

--Query to get standard domain Mappings list in biacm which are loaded with BIAPPS installation

SELECT src.domain_code source_domain_code,
  trg.domain_code target_domain_code,
  srct.domain_name source_domain_name,
  trgt.domain_name target_domain_name
FROM c_domain_map cdm ,
  c_domain_tl srct,
  c_domain_tl trgt,
  c_domain src,
  c_domain trg
WHERE cdm.TRG_DOMAIN_KEY=trgt.DOMAIN_KEY
AND cdm.SRC_DOMAIN_KEY  =srct.DOMAIN_KEY
AND trg.DOMAIN_KEY      =trgt.DOMAIN_KEY
AND src.DOMAIN_KEY      =srct.DOMAIN_KEY
AND trgt.LANGUAGE_CODE  ='US'
AND srct.language_code  ='US'
AND src.domain_code     ='COUNTRY';