Skip Headers
Oracle® Fusion Middleware Developer's Guide for Oracle Adaptive Access Manager
11g Release 1 (11.1.1)

Part Number E15480-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

19 Creating OAAM Oracle BI Publisher Reports

This chapter contains instructions on creating Oracle BI Publisher reports on data in the OAAM schema.

19.1 Create Oracle BI Publisher Reports on Data in the OAAM Schema

Refer to the following sections to create OAAM reports from the Oracle Adaptive Access Manager database. In code listings OAAM table and field names are bold and italic.

19.1.1 Create a Data Model

To create a data model, see the Oracle BI Publisher documentation for details.

19.1.2 Map User Defined Enum Numeric Type Codes to Readable Names

Several fields in many tables are numeric type codes, which correspond to OAAM User Defined Enums. Refer to Chapter 7, "OAAM Extensions and Shared Library to Customize OAAM" for more information about OAAM User Defined Enums. Information on how to map those type codes to readable names is presented in this section.

There are two methods for resolving these names, and the one to choose depends on whether you need to display English only or you need to display internationalized strings.

19.1.2.1 Results Display

To display a readable string rather than a type code value in the report output, the report writer will need to add a join to the tables that hold the User Defined Enums, and then add the field to the select clause.

19.1.2.2 English Only User Defined Enum Result Display

The following SQL code shows how to add the join criteria to the query:

SELECT …
FROM …
LEFT OUTER JOIN (
      SELECT enumElement.num_value, enumElement.label
      FROM v_b_enum enum
             INNER JOIN v_b_enum_elmnt enumElement ON on enum.enum_id = enum_element.enum_id
      WHERE enum.prop_name = 'enum name') alias
      ON table.type_field = alias.num_value
…

In this code, table.type_field is the field containing a type code value that you want to replace with a string. Alias is the name you are giving the inner select clause. Finally, enum_name is the property name of the User Defined Enum.

To display in the report, you need to add alias.label to the select clause.

19.1.2.3 Internationalized User Defined Enum Result Display

The following SQL code shows how to add the join criteria to the query:

SELECT …
FROM …
LEFT OUTER JOIN (
      SELECT t0.config_value, element.num_value
      FROM v_b_config_rb t0
      INNER JOIN (
             SELECT enum_element.num_value, enum_element.str_value, enum.prop_name
             FROM v_b_enum enum
                   INNER JOIN v_b_enum_elmnt enum_element ON enum.enum_id = enum_element.enum_id
             WHERE enum.prop_name = 'enum name') element
             ON t0.config_name=element.prop_name || '.' || element.str_value || '.name'
      WHERE t0.locale_id = (
             SELECT locale_id FROM v_b_locale
             WHERE language = substr(:xdo_user_ui_locale, 1, 2)
                   AND country = substr(:xdo_user_ui_locale, 4, 2)
                   AND (substr(:xdo_user_ui_locale, 1, 2) in ('de', 'en', 'es', 'fr', 'it', 'ja', 'ko')
                         OR (substr(:xdo_user_ui_locale, 1, 2) = 'pt' AND substr(:xdo_user_ui_locale, 4, 2) = 'BR')
                         OR (substr(:xdo_user_ui_locale, 1, 2) = 'zh' AND substr(:xdo_user_ui_locale, 4, 2) IN ('CN', 'TW')))
             UNION SELECT locale_id FROM v_b_locale
             WHERE language = substr(:xdo_user_ui_locale, 1, 2)
                   AND NOT EXISTS(SELECT locale_id FROM v_b_locale
                   WHERE language = substr(:xdo_user_ui_locale, 1, 2)
                         AND country = substr(:xdo_user_ui_locale, 4, 2))
                         AND country IS NULL
                         AND (substr(:xdo_user_ui_locale, 1, 2) in ('de', 'en', 'es', 'fr', 'it', 'ja', 'ko')
                               OR (substr(:xdo_user_ui_locale, 1, 2) = 'pt' AND substr(:xdo_user_ui_locale, 4, 2) = 'BR')
                               OR (substr(:xdo_user_ui_locale, 1, 2) = 'zh' AND substr(:xdo_user_ui_locale, 4, 2) IN ('CN', 'TW'))) 
             UNION SELECT locale_id FROM v_b_locale
             WHERE language = 'en'
                   AND NOT (substr(:xdo_user_ui_locale, 1, 2) in ('de', 'en', 'es', 'fr', 'it', 'ja', 'ko')
                         OR (substr(:xdo_user_ui_locale, 1, 2) = 'pt' AND substr(:xdo_user_ui_locale, 4, 2) = 'BR')
                         OR (substr(:xdo_user_ui_locale, 1, 2) = 'zh' AND substr(:xdo_user_ui_locale, 4, 2) IN ('CN', 'TW'))))
      ORDER BY t0.config_name) alias
      ON table.type_field = alias.num_value
…

In this code, table.type_field is the field containing a type code value that you want to replace with a string. Alias is the name you want to give the inner select clause. Finally, enum_name is the property name of the User Defined Enum.

To display in the report, you need to add alias.config_value to the select clause.

19.1.3 Adding Lists of Values

Add parameters to your report definition to enable your users to interact with the report and specify the data of interest from the data set.

To allow a user to select from a list of readable strings representing type codes, the report writer will need to create a List of Values (LOV) from a query on the User Defined Enums tables, filtered by the enum name.

19.1.3.1 User Defined Enums as List of Values for Filtering, English Only

The following listing shows how to write the query to populate the list of values.

SELECT enumElement.label, enumElement.num_value
FROM v_b_enum enum
      INNER JOIN v_b_enum_elmnt enumElement ON on enum.enum_id = enumElement.enum_id
WHERE enum.prop_name = 'enum name'
ORDER BY enumElement.label

The following listing shows how to filter the report based on this LOV.

WHERE …
AND (:parameter IS NULL OR :parameter = table.type_field)

In these listings, enum_name is the property name of the User Defined Enum, table.type_field is the field containing a type code value that you want to replace with a string, and parameter is the named parameter. See the Oracle BI Publisher documentation for information about creating and setting up report parameters.

19.1.3.2 User Defined Enums as List of Values for Filtering, Internalized

The following listing shows how to write the query to populate the list of values.

SELECT t0.config_value, element.num_value
FROM v_b_config_rb t0
INNER JOIN (
      SELECT enum_element.num_value, enum_element.str_value, enum.prop_name
      FROM v_b_enum enum
             INNER JOIN v_b_enum_elmnt enum_element ON enum.enum_id = enum_element.enum_id
      WHERE enum.prop_name = 'enum name') element
      ON t0.config_name=element.prop_name || '.' || element.str_value || '.name'
WHERE t0.locale_id = (
      SELECT locale_id FROM v_b_locale
      WHERE language = substr(:xdo_user_ui_locale, 1, 2)
             AND country = substr(:xdo_user_ui_locale, 4, 2)
             AND (substr(:xdo_user_ui_locale, 1, 2) in ('de', 'en', 'es', 'fr', 'it', 'ja', 'ko')
                   OR (substr(:xdo_user_ui_locale, 1, 2) = 'pt' AND substr(:xdo_user_ui_locale, 4, 2) = 'BR')
                   OR (substr(:xdo_user_ui_locale, 1, 2) = 'zh' AND substr(:xdo_user_ui_locale, 4, 2) IN ('CN', 'TW')))
      UNION SELECT locale_id FROM v_b_locale
      WHERE language = substr(:xdo_user_ui_locale, 1, 2)
             AND NOT EXISTS(SELECT locale_id FROM v_b_locale
             WHERE language = substr(:xdo_user_ui_locale, 1, 2)
                   AND country = substr(:xdo_user_ui_locale, 4, 2))
                   AND country IS NULL
                   AND (substr(:xdo_user_ui_locale, 1, 2) in ('de', 'en', 'es', 'fr', 'it', 'ja', 'ko')
                         OR (substr(:xdo_user_ui_locale, 1, 2) = 'pt' AND substr(:xdo_user_ui_locale, 4, 2) = 'BR')
                         OR (substr(:xdo_user_ui_locale, 1, 2) = 'zh' AND substr(:xdo_user_ui_locale, 4, 2) IN ('CN', 'TW'))) 
      UNION SELECT locale_id FROM v_b_locale
      WHERE language = 'en'
             AND NOT (substr(:xdo_user_ui_locale, 1, 2) in ('de', 'en', 'es', 'fr', 'it', 'ja', 'ko')
                   OR (substr(:xdo_user_ui_locale, 1, 2) = 'pt' AND substr(:xdo_user_ui_locale, 4, 2) = 'BR')
                   OR (substr(:xdo_user_ui_locale, 1, 2) = 'zh' AND substr(:xdo_user_ui_locale, 4, 2) IN ('CN', 'TW'))))
ORDER BY t0.config_name

The filtering is performed in the same manner as the English Only version.

19.1.4 Adding Geolocation Data

The OAAM schema includes tables that map IP address ranges to location data including city, state, and country. The relevant tables are VCRYPT_IP_LOCATION_MAP, VCRYPT_CITY, VCRYPT_STATE, and VCRYPT_COUNTRY. Many tables contain IP addresses, and VCRYPT_IP_LOCATION_MAP contains foreign keys to each of VCRYPT_CITY, VCRYPT_STATE, and VCRYPT_COUNTRY.

In OAAM, IP addresses are stored as long numerals. The following listing shows how join a table containing an IP address to the VCRYPT_IP_LOCATION_MAP.

SELECT ...
FROM vcrypt_tracker_usernode_logs logs
      INNER JOIN vcrypt_ip_location_map loc ON (
             logs.remote_ip_addr >= loc.from_ip_addr AND logs.remote_ip_addr <= loc.from_ip_addr
      )

For user input and display purposes, you will normally want to use the standard four-part IP address. The following listing shows how to display a numeric IP address as a standard IP, where ipField is the field or parameter containing the numeric IP address you want to display.

…
to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 1, 3), 'XX')) || '.' ||
      to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 4, 2), 'XX')) || '.' ||
      to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 6, 2), 'XX')) || '.' ||
      to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 8, 2), 'XX'))
...

The following listing shows how to convert a standard IP address to the long numeric format.

…
to_number(substr(ipField, 1, instr(ipField, '.')-1))*16777216 +
      to_number(substr(ipField, instr(ipField, '.', 1, 1)+1, instr(ipField, '.', 1, 2)-instr(ipField, '.', 1, 1)-1))*65536 +
      to_number(substr(ipField, instr(ipField, '.', 1, 2)+1, instr(ipField, '.', 1, 3)-instr(ipField, '.', 1, 2)-1))*256 +
      to_number(substr(ipField, instr(ipField, '.', 1, 3)+1))

19.1.5 Adding Sessions and Alerts

Sessions and alerts exist in the VCRYPT_TRACKER_USERNODE_LOGS and VCRYPT_ALERT tables, respectively. They join to each other via the REQUEST_ID field, and they each join to the geolocation data via the VCRYPT_IP_LOCATION_MAP table via the BASE_IP_ADDR field.

19.1.5.1 Type Code Lookups

The session table and the alert table have several type code fields that may be translated into readable text by following the instructions to look up the user defined enums by name. The following tables will list the type code fields and the name of the user defined enum.

Table 19-1 VCRYPT_TRACKER_USERNODE_LOGS

Field Name User Defined Enum Name

AUTH_STATUS

auth.status.enum

AUTH_CLIENT_TYPE_CODE

auth.client.type.enum


Table 19-2 VCRYPT_ALERT

Field Name User Defined Enum Name

ALERT_LEVEL

alert.level.enum

ALERT_TYPE

alert.type.enum

ALERT_STATUS

alert.status.enum

RUNTIME_TYPE

profile.type.enum


19.1.6 Example

This report will show a list of sessions, with user id, login id, auth status, and location. To start with, you will need to create two date parameters, fromDate and toDate. The query will look like the following:

SELECT s.request_id, s.user_id, s.user_login_id, auth.label, country.country_name, state.state_name,
city.city_name
FROM vcrypt_tracker_usernode_logs s
      INNER JOIN vcrypt_ip_location_map loc ON s.base_ip_addr = loc.base_ip_addr
      INNER JOIN vcrypt_country country ON loc.country_id = country.country_id
      INNER JOIN vcrypt_state loc ON loc.state_id = country.state_id
      INNER JOIN vcrypt_city city ON loc.city_id = city.city_id
      LEFT OUTER JOIN (
             SELECT enumElement.num_value, enumElement.label
             FROM v_b_enum enum
                   INNER JOIN v_b_enum_elmnt enumElement ON on enum.enum_id = enum_element.enum_id
             WHERE enum.prop_name = 'auth.status.enum') auth
             ON s.auth_status = auth.num_value
WHERE (:fromDate IS NULL OR s.create_time >= :fromDate)
      AND (:toDate IS NULL OR s.create_time <= :toDate)
ORDER BY s.create_time DESC

19.1.7 Adding Layouts to the Report Definition

BI Publisher offers several options for designing templates for your reports. Refer to the Oracle Business Intelligence Publisher documentation for instructions.

19.2 Building OAAM Transactions Reports

This section explains how you can build transaction reports. It contains the following topics:

19.2.1 Get Entities and Transactions Information

To get the Transaction Definition key and Entity Definition keys, follow these steps:

  1. Log into OAAM Admin and go to the Transactions menu and search for the transaction definitions you are interested in.

  2. Go to the General tab and note down the Definition Key of the transaction. This is the "Transaction Definition Key" of the transaction.

  3. Go to the Entities tab of the transaction and note down the distinct list Entity Name.

  4. Choose the Entities menu option to search for Entities and note the Key of each of those entities. That is the "Entity Definition Key" of the entities.

19.2.2 Discover Entity Data Mapping Information

To discover entity data mapping information that you will need to create your report, follow the procedures in this section.

19.2.2.1 Information about Data Types

For your reference, number data types are listed in the following table.

Table 19-3 Information about Data Types

Data Type Description

1

Represents String data

2

Represents Numeric data. Data stored is equal to (Original value * 1000).

3

Date type data. Store the data in "'YYYY-MM-DD HH24:MI:SS TZH:TZM" format and also retrieve it using same format.

4

Boolean data. Stored as strings. "True" represents TRUE and "False" represents FALSE


19.2.2.2 Discover Entity Data Details Like Data Type, Row and Column Mappings

To get the entity data details that you will need to construct your report, follow these steps:

  1. Get the Entity Definition Key by looking at the entity definition using the OAAM Admin Console.

  2. Get details of how entity data is mapped using the SQL Query:

    SELECT label,
      data_row,
      data_col,
      data_type
    FROM vt_data_def_elem
    WHERE status =1
    AND data_def_id =
      (SELECT data_def_id
      FROM vt_data_def_map
      WHERE relation_type   ='data'
      AND parent_obj_type   =3
      AND parent_object_id IN
        (SELECT entity_def_id
        FROM vt_entity_def
        WHERE entity_def_key=<Entity Definition Key>
        AND status =1
        )
      )
    ORDER BY data_row ASC,
      data_col ASC;
    

19.2.2.3 Build Entity Data SQL Queries and Views

The above SQL query gives a list of data fields of the entity with data type and row, column position. Using that information, build a SQL query based on the following information that represents data of the given entity. It is also recommended to create/build a view based on this SQL query that represents data of the given entity.

Note:

EntityRowN represents an entity data row. If your entity has 3 distinct data_row values from the above query then you would have 3 EntityRows, name the aliases as EntityRow1, EntityRow2, and so on, and similarly take care of the corresponding joins as shown below.

SELECT ent.ENTITY_ID,
    ent.EXT_ENTITY_ID,
    ent.ENTITYNAME,
    ent.ENTITY_KEY,
    ent.ENTITY_TYPE,
    EntityRowN<row>.DATA<col> <column_name>,
    (EntityRowN<row>.NUM_DATA<col>/ 1000.0) <numeric_column_name>,
    to_timestamp_tz(EntityRowN<row>.DATA<col>, 'YYYY-MM-DD HH24:MI:SS TZH:TZM') <date_column_name>,
    ent.CREATE_TIME,
    ent.UPDATE_TIME,
    ent.EXPIRY_TIME,
    ent.RENEW_TIME
  FROM 
    VT_ENTITY_DEF entDef,
    VT_ENTITY_ONE ent
    LEFT OUTER JOIN VT_ENTITY_ONE_PROFILE EntityRowN
          ON (EntityRowN.ENTITY_ID    = ent.ENTITY_ID
          AND EntityRowN.ROW_ORDER    = <row>
          AND EntityRowN.EXPIRE_TIME IS NULL)
    LEFT OUTER JOIN VT_ENTITY_ONE_PROFILE EntityRowN+1
        ON (EntityRowN+1.ENTITY_ID    = ent.ENTITY_ID
          AND EntityRowN+1.ROW_ORDER    = <row+1>
        AND row1.EXPIRE_TIME IS NULL)
  WHERE 
        ent.ENTITY_DEF_ID      = entDef.ENTITY_DEF_ID and 
        entDef.ENTITY_DEF_KEY=<Entity Definition Key>

19.2.3 Discover Transaction Data Mapping Information

To discover transaction data mapping information that you will need to create your report, follow the procedures in this section.

19.2.3.1 Discover Transaction data details like Data Type, Row and Column mappings

To get entity data details you will need to construct your report, follow these steps:

  1. Get list of transaction to entity definition mapping Ids using the following SQL:

    SELECT map_id
    FROM 
    vt_trx_ent_defs_map,
            vt_trx_def
    WHERE 
    vt_trx_ent_defs_map.trx_def_id = vt_trx_def.trx_def_id
    AND vt_trx_def.trx_def_key  =<Transaction Definition Key>
    
  2. Use the following SQL query to get details of all transaction data fields, their data type and their row, column mapping:

    SELECT label,
      data_row,
      data_col,
      data_type
    FROM vt_data_def_elem
    WHERE status    =1
    AND data_def_id =
      (SELECT data_def_id
      FROM vt_data_def_map
      WHERE relation_type   ='data'
      AND parent_obj_type   =1
      AND parent_object_id IN
        (SELECT trx_def_id
        FROM vt_trx_def
        WHERE trx_def_key='mayo_pat_rec_acc'
        AND status       =1
        )
      )
    ORDER BY data_row ASC,
      data_col ASC;
    

19.2.3.2 Build Transaction Data SQL Queries and Views

Use the information from the previous section and build a SQL query that represents transaction data based on the following:

Note: It is recommended to build a view based on this Query so that it is easier to build reports

SELECT trx.LOG_ID,
    trx.USER_ID,
    trx.REQUEST_ID,
    trx.EXT_TRX_ID,
    trx.TRX_TYPE,
    trx.STATUS,
    trx.SCORE,
    trx.RULE_ACTION,
    trx.TRX_FLAG,
    trx.POST_PROCESS_STATUS,
    trx.POST_PROCESS_RESULT,
    TxnDataRowN<row>.DATA<col> <data_column_name>,
   (TxnDataRowN<row>.NUM_DATA<col>/ 1000.0) <numeric_column_name>,
    to_timestamp_tz(TxnDataRowN<row>.DATA<col>, 'YYYY-MM-DD HH24:MI:SS TZH:TZM') <date_column_name>,
    (SELECT entTrxMap.MAP_OBJ_ID
    FROM VT_ENT_TRX_MAP entTrxMap
    WHERE entTrxMap.DEF_MAP_ID = <Transaction to Entity Mapping Id of Entity1_Name>
    AND entTrxMap.TRX_ID       = trx.LOG_ID
    ) <EntityN_Name>,
    (SELECT entTrxMap.MAP_OBJ_ID
    FROM VT_ENT_TRX_MAP entTrxMap
    WHERE entTrxMap.DEF_MAP_ID = <Transaction to Entity Mapping Id of Entity2_Name>
    AND entTrxMap.TRX_ID       = trx.LOG_ID
    ) <EntityN+1_Name>,
    trx.CREATE_TIME,
    trx.UPDATE_TIME,
    TRUNC(trx.create_time, 'HH24') created_hour,
    TRUNC(trx.create_time, 'DDD') created_day,
    TRUNC(trx.create_time, 'DAY') created_week,
    TRUNC(trx.create_time, 'MM') created_month,
    TRUNC(trx.create_time, 'YYYY') created_year
  FROM VT_TRX_DEF trxDef,
    VT_TRX_LOGS trx
  LEFT OUTER JOIN VT_TRX_DATA TransactionDataRowN
  ON (TransactionDataRowN.TRX_ID         = trx.LOG_ID
  AND TransactionDataRowN.ROW_ORDER      = <rowN>)
LEFT OUTER JOIN VT_TRX_DATA TransactionDataRowN+1
  ON (TransactionDataRowN+1.TRX_ID         = trx.LOG_ID
  AND TransactionDataRowN+1.ROW_ORDER      = <rowN+1>)
  WHERE trx.TRX_DEF_ID      = trxDef.TRX_DEF_ID  and
trxDef.TRX_DEF_KEY=<Transaction Definition Key>

19.2.4 Build Reports

Follow the instructions in this section to build reports for entities and transactions.

19.2.4.1 Building Entity Data Reports

Use the SQL Queries or Views built using the information mentioned in Section 19.2.2.3, "Build Entity Data SQL Queries and Views."

19.2.4.2 Building Transaction Data Reports

Use the SQL Queries or Views built using the information mentioned in Section 19.2.3.2, "Build Transaction Data SQL Queries and Views."

19.2.4.3 Joining Entity Data Tables and Transaction data tables

You can join the transaction data views you built with entity data view using VT_ENT_TRX_ MAP.MAP_OBJ_ID which is indicated using the pseudo column <EntityN_Name>.