Oracle® Healthcare Foundation

Dashboards User's Guide

Release 7.2

E94537-01

September 2018

Oracle Healthcare Foundation Dashboards User’s Guide, Release 7.2

E94537-01

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Contents

Overview

- Documentation Accessibility

1 OHF Dashboards

- Value Based Care Dashboard

- Member Analysis

- PMPM Analysis

- Risk Score Analysis

- Rx Claim Analysis

- ACO Measures

- Patient Caregiver Experience

- Derived Measures Dashboard

- Patient Browser Dashboard

- Clinical Dashboard

- Summary

- Stroke Encounters

- LOS Analysis

- Financial Dashboard

- Reimbursement Analysis

- Claims Analysis

- Encounter Charge Analysis

- Billing Analysis

- Audit Trail and Usage Tracking Dashboard

- Usage Summary

- Audit Trail

- Dashboard Usage

- Data Lineage Dashboard

- Report Lineage

- Healthcare Common Datamart Lineage

2 Enable the OHF SSA Usage Tracking

- Set Up the Data Model

- Enable the Usage Tracking Feature in the NQSConfig.INI File

- Configure the OHF SSA RPD File

- Deploy the OHF SSA RPD File

3 Data Lineage Extensibility

- Refresh the RPD Lineage Metadata

- Refresh the Catalog Lineage Metadata

- Refresh the OBIEE Dashboards

Overview

This document contains details on the Oracle Healthcare Foundation (OHF) Dashboards, a comprehensive set of examples for Clinical, Financial, and Value Based Care domains that are created using Self-Service Analytics (SSA). The OHF Dashboards are distributed as free content and are not maintained or supported by Oracle as a licensed product.

The dashboards run on Oracle Business Intelligence (OBIEE) and can be used to visualize data from the Healthcare Common Data Mart (HCD). For details on the dashboards' deployment instructions, see Oracle Healthcare Foundation Installation Guide.

Oracle also provides sample data files to populate HCD with sample data for the OHF Dashboards or for other analytics uses. For instructions on how to deploy the sample data, see the Oracle Healthcare Foundation Sample Data Deployment Guide.

You can access the OHF Dashboards from the Dashboard menu in OBIEE, as shown below:

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

1 OHF Dashboards

The following are the OHF Dashboards and pages. You can customize or extend the dashboards and reports based on your downstream application use cases:

Table 1-1 OHF Dashboards

Dashboard

Pages

Value Based Care Dashboard

- Member Analysis

- PMPM Analysis

- Risk Score Analysis

- Rx Claim Analysis

- ACO Measures

- Patient Caregiver Experience

Derived Measures Dashboard

Allows you to browse derived measures, such as: HCAHPS, HIQR/HQQR, PQRS, or CQM.

Patient Browser Dashboard

Create, save, or retrieve queries for a cohort of patients by: demographics, diagnosis, procedure, drug, DRG, service line, etc.

Clinical Dashboard

- Summary

- Stroke Encounters

- LOS Analysis

Financial Dashboard

- Reimbursement Analysis

- Claims Analysis

- Encounter Charge Analysis

- Billing Analysis

Audit Trail and Usage Tracking Dashboard

- Usage Summary

- Audit Trail

- Dashboard Usage

Data Lineage Dashboard

- Report Lineage

- Healthcare Common Datamart Lineage

The reports in these dashboards work for the Hospital enterprise. You can filter some reports by organization.

Value Based Care Dashboard

The Value Based Care Dashboard contains the following pages:

- Member Analysis

- PMPM Analysis

- Risk Score Analysis

- Rx Claim Analysis

- ACO Measures

- Patient Caregiver Experience

Member Analysis

The Member Analysis page displays analytics based on the Member Month Eligibility and Medical Claim subject areas.

Figure 1-1 Member Analysis Page

You can use the filters at the top of the page to focus on specific payers and calendar years. This page has the following reports:

- Total medicare, medicaid, and commercial members. This report shows the organization's exposure to different insurers based on the number of patients they cover. Click on the bars in the graph to display details about all the members covered by each insurer.

- Total Members by Primary Care Physician. This report shows each primary care physician's exposure to different insurers based on the number of patients they cover. Click on the bars in the graph to display details about all the members covered by each insurer for every primary care physician.

- Total Members by Attributed Provider. This report shows each attributed provider's exposure to different insurers based on the number of patients they cover. Click on the bars in the graph to display details about all the members covered by each insurer for every attributed provider.

PMPM Analysis

The PMPM Analysis page displays analytics based on the Member Month Eligibility, Medical Claim, and Rx Claim subject areas.

Figure 1-2 PMPM Analysis Page

You can use the filters at the top of the page to focus on specific payers, organizations, insurance program types, and calendar years. This page has the following reports:

- PMPM overall, medical and drug cost in dollars, overall dollars spent, and average cost per procedure. This report allows you to see the average monthly costs per member and their distribution between medical and drug PMPM costs. It also allows you to evaluate your total costs and the cost of a typical procedure.

- PMPM Cost and Members by Attributed Physician. This report helps you understand the PMPM costs that attributed physicians generate through the average monthly cost per member and the total number of members assigned. The distribution between emergency and inpatient members also helps you understand the associated costs.
Click any bar in the PMPM graph do display a detailed breakdown of all the amounts for each attributed physician.

- High Cost Members by Primary Care Physician. This report helps you understand the costs that primary care physicians generate through the average monthly cost per member and the total number of members assigned. The distribution between emergency and inpatient members also helps to understand the associated costs.
Click any bar in the PMPM graph do display a detailed breakdown of all the amounts for each primary care physician.

- Claim Payments by PCP - Graphical representation of the claimed payment amounts and billed amounts by primary care physician. This report allows you to compare the overall payments claimed by primary care physicians and to check how they are covered by the billed amounts.

- Claim Payments by Procedure - A tabular report of the claim payments for each procedure compared to the total amount. The percentage helps you identify the procedures that generate the largest claims.

- Claim Payments by Care Setting - Graphical representation of the claim payment amounts for each case setting. This report helps you visualize the care settings that generate the largest claims.

- Claim Payments by Member - A tabular report that lists all members and their claim payments. This report allows you to drill down to the payments claimed for each member.

Risk Score Analysis

The Risk Score Analysis page displays analytics based on the Member Risk Score subject area.

Figure 1-3 Risk Score Analysis Page

You can use the filters at the top of the page to focus on specific primary care physicians and calendar years. This page has the following reports:

- Total number of patients with risk score, average scoring events per patient and average days since last scoring event. This report helps you understand the scoring activity volume based on the number of patients it is performed on, the number of scoring events per patient, and their frequency.

- Risk analysis by gender, ethnicity, and age. Correlate this report with the total numbers of patients in each category to understand which genders, ethnic groups, and age bands are more exposed to risks.

Rx Claim Analysis

The Rx Claim Analysis page displays analytics based on the Prescription Claim subject area.

Figure 1-4 Rx Claim Analysis Page

You can use the filters at the top of the page to focus on specific payers, organization providers, insurance program types, and calendar years. This page has the following reports:

- Total Prescriptions and Prescription Cost by Medicines - This report helps you understand how often different medicines are prescribed and the costs that these prescriptions generate.
Click on a bar or on a medicine name to display detailed prescription information for all the members who have been prescribed the medicine. There are 2 levels of drill down. Clicking further on the member number shows another detailed report for each member with Bill details.

- Avg out-of-pocket cost for a branded drug in Rx - Shows the average out of pocket cost that patients pay for a given set of medicines along with the breakdown of that cost by deductible, patient responsibility, and copay amounts.

ACO Measures

The ACO Measures page uses quality measurements based on the Accountable Care Organization (ACO) standards. You can customize this page to be used for any quality measure.

Figure 1-5 ACO Measures Page

You can use the filters at the top of the page to focus on specific organization providers and measure types. This page has the following report:

- ACO Quality Measures - Graphical representation of the percentile rating for the selected organization providers for specific measure types. This report allows you to understand how organization providers compare to each other on different measures. The detailed tabular report below the chart also includes the percentile variance from the previous year to the current year, providing insight into the progress of an organization over different ACO quality measures.

Note:
The derived measure model supports drill-down to a patient, member, individual and organization service provider, encounter, internal organization, medical claim, and Rx claim from the quality measure results.

Patient Caregiver Experience

The Patient Caregiver Experience page centralizes responses from patient surveys.

Figure 1-6 Patient Caregiver Experience Page

You can use the filters at the top of the page to focus on specific organization providers, departments, physicians, and calendar years. This page has the following report:

- Patient Satisfaction Survey - Shows the survey response results rolled up to the categories, such as cleanliness of hospital environment, communication about medicines, and communication with doctors and nurses. Click on a graph bar to display the detailed answers for that survey category.
On the right side, the survey displays the performance score for each area. For example, "During the hospital stay, how often doctors explained in a way that the patient could understand?" has a score of 80%, which means 80% of patients agreed that the doctors explained clearly. Click on a percentage number to display the detailed answers for that survey question.

Derived Measures Dashboard

Figure 1-7 Derived Measures Dashboard

The Derived Measures dashboard allows you to browse derived measures, such as: HCAHPS, HIQR/HQQR, PQRS, or CQM. Use the panel on the left to select your filtering criteria and click Apply to display the results in the table on the right. Also see ACO Measures for measure comparisons between organizations and progress tracking.

Patient Browser Dashboard

Figure 1-8 Patient Browser Dashboard

The Patient Browser allows you to create, save, or retrieve queries for a cohort of patients by: demographics, diagnosis, procedure, drug, DRG, service line, etc. Use the panel on the left to select your filtering criteria and click Apply to display the results in the table on the right. A summary count for your results is displayed at the top of the page for the number of patients, visits, and discharges.

Click on an MRN to display that patient's billing information.

Clinical Dashboard

The Clinical dashboard contains the following pages:

- Summary

- Stroke Encounters

- LOS Analysis

Summary

Figure 1-9 Summary Page

You can use the filters at the top of the page to focus on preferred organization providers, physicians, or calendar years. The page has the following reports:

- Overview - An overview of counts in a hospital enterprise including the number of patients, discharges, emergency department patients, inpatients, and outpatients. This helps you to compare the inflow and outflow of patients, and to allocate resources based on the distribution of patient care efforts between emergency, ambulatory and inpatient services.
Click on a number to display a detailed list of all the patients in that category. From there, you can click the patients' MRNs to further drill down their billing details.

- Patients by Admission Type. This report allows you to see the inflow of patients that go through different admission types (referral, transfer, emergency, etc.) and to adjust your admission operations accordingly.
Click on a bar to display a detailed list of all the patients in that category. From there, you can click the patients' MRNs to further drill down their billing details.

- Discharges by Diagnosis - Modify this report to show the number of discharges for a specific diagnosis of interest. You can correlate this report with the Top 10 Diagnoses report and see if they are proportional. If the number of discharges for a specific diagnosis is trailing, it may indicate a long-term tendency for cases with that specific diagnosis to accumulate in the organization. You may want to investigate such situations and address them.
Click on a bar to display a detailed list of all the patients with that diagnosis. From there, you can click the patients' MRNs to further drill down their billing details.

- Encounters by Organization. This report allows you to see the workload distribution between your organizations and plan your staffing, budgeting, and resource allocations accordingly.

- % of Encounters by Diagnosis - Modify this report to show the % of encounters for a specific diagnosis of interest. You can use this report to estimate the mix of specialists that you need, based on the workloads that the various types of treatments require.

- Encounters by Type - The balance between the inpatient and outpatient services you provide may help your organization adjust its focus either on clinics or hospitals.

- Top 10 Diagnosis - Modify this report to show the number of encounters for a specific diagnosis of interest. You can use this report to see which treatments that you provide are the most labor intensive.
Click on a bar to display a detailed list of all the patients with that diagnosis. From there, you can click the patients' MRNs to further drill down their billing details.

- Discharges by Year-Month - You can correlate this report with the number of encounters by year-end and see if there are any seasonal slow-downs in treatment delivery.

- Encounters by Year-Month - You can use this report to see the long-term trends and seasonal spikes in workload.

- Discharges by Ethnicity - Shows a comparative analysis of number of discharges over two years by ethnicity. Modify this report to show the number of discharges for specific years of interest. You can correlate this report with the number of encounters by ethnicity to see if there is a systematic tendency to discharge members of certain ethnic groups after fewer investigations than the baseline.

- Encounters by Ethnicity - Shows a comparative analysis of the number of encounters over two years by ethnicity. Modify this report to show the number of encounters for specific years of interest. You can correlate this report with the number of discharges by ethnicity to see if there is a systematic tendency to discharge members of certain ethnic groups after fewer investigations than the baseline.

- Encounters by Gender - Shows the workload distribution between male and female patients. You can use this report to plan gender-specific healthcare measures.

Stroke Encounters

The Stroke Encounters page shows a holistic view of the number of encounters in a Healthcare organization for different dimensions such as diagnosis, procedure, and time. It uses the Stroke diagnosis and Endovascular procedure subject areas. You can modify the page to use any diagnosis and procedures based on your use case.

Figure 1-10 Stroke Encounters Page

You can use the filters at the top of the page to focus on specific time periods. This page has the following reports:

- Encounters with Stroke Diagnosis - Shows the total number of encounters with stroke diagnosis in an organization against the total number of encounters. This lets you compare and contrast the volume of encounters in your organizations for a specific diagnosis.

- Encounters with Stroke Diagnosis who received Endovascular procedure - Shows the number of encounters with stroke diagnosis and the patients who received endovascular procedure treatment. This lets you evaluate the frequency of endovascular procedures performed on patients who suffered a stroke.
The chart on the right displays the evolution of the number of encounters for a selected organization during the analyzed time period. This report lets you see long-term trends and seasonal spikes for endovascular procedures performed on patients who suffered a stroke.

- Patients Admitted with Stroke who received Endovascular Procedure - Tabular report showing the list of patients with Stroke diagnosis who have received endovascular procedure for the chosen time period. This report allows you to drill down to the specifics of each encounter attended by individual patients.

- Organizations - Encounters and Diagnosis - Tabular report showing monthly encounters for each diagnosis per organization.

LOS Analysis

Figure 1-11 LOS Analysis Page

The Length of Stay (LOS) Analysis page has the following reports:

- Average Length of Stay (Est vs Act) By Time - Comparative analysis report that shows the average estimated LOS and average actual LOS over a period of time filtered by organization service provider. You can use this report to analyze and address issues with your LOS forecasting methodology for an organization service provider.

- Average Length of Stay (Est vs Act) By Organization Service Provider - Comparative analysis report that shows the estimated LOS and actual LOS by organization service provider. This report provides insight into the LOS forecasting gaps from each organization service provider.

- Average Length of Stay (Est vs Act) By Diagnosis - Comparative analysis report that shows the average estimated LOS and average actual LOS by diagnosis. You can use this report to analyze and address any issues with your LOS forecasting methodology for individual diagnoses.

Financial Dashboard

The Financial dashboard contains the following pages:

- Reimbursement Analysis

- Claims Analysis

- Encounter Charge Analysis

- Billing Analysis

Reimbursement Analysis

The Reimbursement Analysis page covers the reimbursement amounts for the patient accounts.

Figure 1-12 Reimbursement Analysis Page

You can use the filter at the top of the page to focus on specific calendar years. This page has the following reports:

- Patient Account by Business Unit. This report helps you understand the distribution of patient accounts across business units and it helps in resource planning to manage patient accounts effectively.

- Total Expected Reimbursement Amount by Cost Center, filtered by insurer - This report indicates the degree to which each insurer impacts the income of different cost centers. Click on a bar in the graph to display details about all the patients insured by the selected insurer for that cost center.
A tabular report below the graph lists the expected total reimbursement amounts for all the cost centers and insurers. Click on an amount to display details about all the patients insured by the selected insurer for that cost center.

- Total Reimbursement Amount owed to Business Units, filtered by insurer - This report indicates the degree to which each insurer impacts the income of different business units. Click on a bar in the graph to display details about all the patients insured by the selected insurer for that business unit.
A tabular report below the graph lists the expected total reimbursement amounts for all the business units and insurers. Click on an amount to display details about all the patients insured by the selected insurer for that business unit.

- A tabular report with patient, patient account, and reimbursement amount details. This report allows you to track the details of all the reimbursements that apply to each patient.

Claims Analysis

The Claims Analysis page uses the Claim Submission subject area.

Figure 1-13 Claims Analysis Page

You can use the filter at the top of the page to focus on specific calendar years. This page has the following report:

- Total Claim Charges by Organization Service Provider - This report allows you to see the extent to which the charges claimed by each organization service provider are covered by insurers and to evaluate the risk of overdue or delinquent payments. The treemap chart on the right allows you to understand the exposure to the various service providers based on their relative sizes.
Click on a bar in the graph to display a detailed list of all the claims for that organization. From there, you can click on an MRN to display all the billing details for a patient or you can click on a claim number to display additional details for that claim.

Encounter Charge Analysis

Figure 1-14 Encounter Charge Analysis Page

You can use the filter at the top of the page to focus on specific calendar years. This page has the following reports:

- Total Charges by Year-Month - This report helps you understand the trend in organization spending for patient services over a chosen period of time.

- Total Charges by Department - This report helps you understand the charging distribution between departments.
Click on a bar in the graph or on a department name in the table to display a detailed list of all the patient charges for that department.

Billing Analysis

The Billing Analysis page displays comparative billing information for your organizations and detailed bills for individual patients.

Figure 1-15 Billing Analysis Page

You can use the filter at the top of the page to focus on specific calendar years. This page has the following reports:

- Total Billed Amount and Total Payments by Organization Service Provider - Graphical report and a tabular report showing the total billed amount and the total payment by organization filtered by DRG. This report helps you identify costs and payments for patient services specific to each organization based on diagnosis-related groups.
Click on a bar in the graph or on a total billed or payment amount to display a detailed list of all the bills for that DRG for the selected organization. From there, you can further drill down the details of a specific bill by clicking on a bill number.

- Total Amount for Stroke Treatment by Organization Service Provider - You can modify this report to use any diagnosis or procedure. This report helps you compare the costs that organization service providers spend on stroke treatments.

- Total Amount for Stroke Treatment and Endovascular Procedure by Organization Service Provider - This report helps you compare the costs that organization service providers spend on stroke treatments and endovascular procedures.

Audit Trail and Usage Tracking Dashboard

Note:
To use the Audit Trail and Usage Tracking Dashboard, you must enable usage tracking for OHF. For details, see Enable the OHF SSA Usage Tracking.

The Audit Trail and Usage Tracking dashboard contains the following pages:

- Usage Summary

- Audit Trail

- Dashboard Usage

Usage Summary

Figure 1-16 Usage Summary Page

You can use the filter at the top of the page to focus on the preferred time period. The page has the following reports:

- Overview - This report displays the total number of users, dashboards, pages, and the total display time for all the OHF Dashboards. Based on the overall usage of the dashboards, you can estimate their usefulness and the load on the system.

- Most Popular Dashboards and Least Used Dashboards - You can use these graphs to evaluate which areas of analysis are the most relevant for future development and which ones have a low impact and might be phased out.
Click on a bar in the graph to display a detailed access log for that dashboard.

Audit Trail

Figure 1-17 Audit Trail Page

You can use the filters at the top of the page to focus on the preferred time period, dashboard page, and user.

This page displays a detailed access log based on the selected criteria for audit purposes.

Dashboard Usage

Figure 1-18 Dashboard Usage Page

You can use the filters at the top of the page to focus on the preferred time period, dashboard page, and user.

The page displays a tabular report of the total access counts, based on the selected criteria. You can use this information to investigate the pages that are accessed the most by specific users or the users who access the most a specific dashboard page.

Data Lineage Dashboard

Note:
For information on how to enable data lineage for your customized reports and dashboards or for customized RPD files, see Data Lineage Extensibility.

The Data Lineage dashboard contains the following pages:

- Report Lineage

- Healthcare Common Datamart Lineage

Report Lineage

Figure 1-19 Report Lineage Page

You can use the filters at the top of the page to focus on the preferred subject areas or reports.

The Report Lineage page tracks the data sources for the content displayed in the reports at the following levels: presentation, logical, datamart, data warehouse, and interface tables. This allows you to troubleshoot your reports and identify potential sources of errors in the OHF database or in OBIEE.

Healthcare Common Datamart Lineage

Figure 1-20 Healthcare Common Datamart Lineage Page

You can use the filters at the top of the page to focus on the preferred HCD tables and columns.

The Healthcare Common Datamart Lineage page displays the data mapping between the datamart, data warehouse, and interface tables. This allows you to better understand the OHF data model and better use the OHF data when building reports and dashboards.

2 Enable the OHF SSA Usage Tracking

This chapter explains how to enable the Usage Tracking feature for Oracle Healthcare Foundation in OBIEE. This allows you to run the reports in the Audit Trail and Usage Tracking Dashboard.

1. Set Up the Data Model

2. Enable the Usage Tracking Feature in the NQSConfig.INI File

3. Configure the OHF SSA RPD File

4. Deploy the OHF SSA RPD File

Set Up the Data Model

1. Make sure the Repository Creation Utility (RCU) has been successfully run while installing OBIEE. You should have the <prefix>_BIPLATFORM schema installed with the following tables in it:

- S_NQ_ACCT

- S_NQ_DB_ACCT

- S_NQ_INITBLOCK

2. Go to <OBIEE_HOME>/bi/bifoundation/samples/usagetracking.

3. Extract the UsageTracking-datafiles.zip archive.

4. Connect to the <prefix>_BIPLATFORM schema and execute the following SQL scripts in order:

1. Oracle_create_nQ_Calendar.sql

2. Oracle_create_nQ_Clock.sql

3. Oracle_nQ_Calendar.sql

4. Oracle_nQ_Clock.sql

5. Create a view in the <prefix>_BIPLATFORM schema:
CREATE OR REPLACE VIEW nq_login_group AS SELECT DISTINCT user_name AS login FROM s_nq_acct;

Enable the Usage Tracking Feature in the NQSConfig.INI File

1. Go to <OBIEE_HOME>/user_projects/domains/bi_domain/config/fmwconfig/biconfig/OBIS/.

2. Open the NQSConfig.INI file in a text editor.

3. In the [Usage Tracking] section set the ENABLE parameter to YES.

4. Set the DIRECT_INSERT parameter to YES.

5. Set the PHYSICAL_TABLE_NAME parameter to point to the S_NQ_ACCT table in the <prefix>_BIPLATFORM schema in the following format: "<Database>"."<Catalog>"."<Schema>"."<Table>";
Use the database, catalog, schema, and table names predefined in the OHF SSA RPD file:

- Database: "OHF Usage Tracking"

- Catalog: "Catalog"

- Schema: "dbo"

- Table Name: "S_NQ_ACCT"

6. Set the CONNECTION_POOL parameter in the following format "<Database>"."<Connection Pool>";
Use the database and connection pool values predefined in the OHF SSA RPD file:

- Database: "OHF Usage Tracking"

- Connection Pool: "OHF Usage Tracking Connection Pool"

7. Uncomment the INIT_BLOCK_TABLE_NAME parameter and set it to point to the S_NQ_INITBLOCK table in the <prefix>_BIPLATFORM schema in the following format: "<Database>"."<Catalog>"."<Schema>"."<Table>";
Use the following values:

- Database: "OHF Usage Tracking"

- Catalog: "Catalog"

- Schema: "dbo"

- Table Name: "S_NQ_INITBLOCK"

8. Uncomment the INIT_BLOCK_CONNECTION_POOL parameter and set it to refer to the connection pool for writing statuses to the S_NQ_INITBLOCK table in the <prefix>_BIPLATFORM schema in the following format: "<Database>"."<Connection Pool>";
Use the database and connection pool values predefined in the OHF SSA RPD file:

- Database: "OHF Usage Tracking"

- Connection Pool: "OHF Usage Tracking Connection Pool"

The above parameters should be set as below:

===============================================================================
# Parameters used for inserting data into the table (i.e. DIRECT_INSERT = YES).
#
# Init-Block Tracking Options are commented out and as a result disabled.
# To enable Init-Block Tracking Feature, define the two parameters for
# Init-Block, INIT_BLOCK_TABLE_NAME and INIT_BLOCK_CONNECTION_POOL.
#
PHYSICAL_TABLE_NAME = "OHF Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT"; # Or "<Database>"."<Schema>"."<Table>" ;
CONNECTION_POOL = "OHF Usage Tracking"."OHF Usage Tracking Connection Pool";
INIT_BLOCK_TABLE_NAME = "OHF Usage Tracking"."Catalog"."dbo"."S_NQ_INITBLOCK" ; # Or "<Database>"."<Schema>"."<Table>" ;
INIT_BLOCK_CONNECTION_POOL = "OHF Usage Tracking"."OHF Usage Tracking Connection Pool";

9. Stop and restart the OBIEE Services using the stop and start scripts available in <OBIEE_HOME>/user_projects/domains/bi_domain/bitools/bin.

Configure the OHF SSA RPD File

Update the OHF Usage Tracking Connection Pool in the OHF SSA RPD file to point to the <prefix>_BIPLATFORM schema:

1. In the Oracle BI Administration Tool, navigate to the selfserviceanalytics\hcd_rpd folder.

2. Open the ohf_ssa_hcd.rpd file.

3. In the RPD Physical layer, expand OHF Usage Tracking and double-click OHF Usage Tracking Connection Pool.

Figure 2-1 Selecting the OHF Usage Tracking Connection Pool

4. Update the data source name with the database host name, port number, and service name of the <prefix>_BIPLATFORM schema.

5. Update the username as the <prefix>_BIPLATFORM schema name.

6. Enter the schema password and confirm.

Figure 2-2 OHF Usage Tracking Connection Pool Setup

7. Save the ohf_ssa_hcd.rpd file and choose No when prompted for Consistency Check.

Deploy the OHF SSA RPD File

Use the datamodel.sh utility to upload the RPD file to the OBIEE server:

1. Go to [OBIEE_HOME]/user_projects/domains/bi/bitools/bin/.

2. Run the datamodel.sh utility using the following syntax:
datamodel.sh uploadrpd -I <location of rpd file to be uploaded> -W <RPDpwd> -SI <Service Instance Name> -U <weblogic user> -P <weblogic password>

To find the Service Instance Name, look in the <OBIEE Home>/user_projects/domains/bi/bidata/service_instances directory. For example:

Figure 2-3 Running the datamodel.sh Utility

After usage tracking is enabled, OBIEE starts to log usage information and statistics and display this information in the Audit Trail and Usage Tracking Dashboard.

3 Data Lineage Extensibility

This chapter explains how to set up the Data Lineage Dashboard to work with customized reports or BI metadata RPD files:

- Refresh the RPD Lineage Metadata

- Refresh the Catalog Lineage Metadata

- Refresh the OBIEE Dashboards

Refresh the RPD Lineage Metadata

If the OHF SSA RPD files has been extended, follow the instructions below to refresh the RPD lineage metadata:

1. Open the extended RPD file in the Oracle BI Administration Tool.

2. Go to Tools and select Utilities.

3. Select Repository Documentation and click Execute.

Figure 3-1 Utilities Window

4. Navigate to a directory where you want to save the repository metadata.

5. In the Save as type: selection box, choose Tab Separated Values (*.txt).

6. Enter a name for the file and click Save.

Figure 3-2 Saving the RPD Data Lineage

7. After the export completes, change the file's extension from .txt to .tsv.

8. Open SQL Developer.

9. Connect to the OHF Exterprise Schema where the OHF_RPD_LINEAGE table is installed.

10. Expand the Tables Section.

11. Right click on the OHF_RPD_LINEAGE table and select Import Data.

12. In the file browser, navigate to the location where you exported the RPD metadata file.

13. Choose the *.tsv file type and select the exported RPD metadata file.

14. Click Open.

15. When prompted to Import Data Record Terminator, click OK.

16. When prompted to Import Data Field Format, click OK.

17. In the Data Preview screen of the Data Import Wizard, set the Left Enclosure to none.

Figure 3-3 Data Preview

18. In the Import Data Field Format screen, click OK.

19. Click Next.

20. In the Import Method screen, click Next.

21. In the Selected Columns panel, make sure all columns are listed and click Next.

22. In the Column Definition screen, map all the source data columns to the target table columns. The source and target columns have similar names and can be matched either by name or by order.

Figure 3-4 Column Definition

23. After you map all the columns, click Next.

24. Clisk Finish.

25. A data import progress screen is displayed.

26. After the import is complete, a success message is displayed. Click OK.

27. Query the OHF_RPD_LINEAGE table to retrieve the record count and compare it with the row count in the RPD Metadata file:
Select count(*) from OHF_RPD_LINEAGE;

Refresh the Catalog Lineage Metadata

If the OHF SSA Dashboards have been extended, follow the instructions below to refresh the RPD lineage metadata:

1. Open the Command Prompt.

2. Navigate to the to bi\bitools\bin subdirectory in the Oracle BI Client Tool installation directory. For example:
cd C:\Install\middleware\BI_Home\bi\bitools\bin

3. Create a .txt file with the WebLogic credentials in the following format:
login=<username>
pwd=<password>

4. Execute the following command:
runcat.cmd -cmd report -online <BI Analytics URL where the dashboard catalog contents reside> -credentials <Path to the credentials file containing the weblogic user name and password> -outputFile <Path to the output file with extension .csv where the catalog metadata contents have to be stored> -folder <Location of the OBI Catalog Folder> -delimiter "|~" -type "Analysis" -fields "Owner:Folder:Name:Path:Subject Area:Table:Column:Formula"

For example:

runcat.cmd -cmd report -online http://obixxx.mycompany.com:9504/analytics/saw.dll? -credentials D:/login.txt -outputFile D:/ohf_hcd_catalog.csv -folder "/shared/Oracle Healthcare Foundation" -delimiter "|~" -type "Analysis" -fields "Owner:Folder:Name:Path:Subject Area:Table:Column:Formula"

5. After completion, a success message is displayed:
Making SOAP connection to http://obixxx.mycompany.com:9504/analytics/saw.dll?
Running Report..4..3..2..1..0..Done!

6. Open SQL Developer.

7. Connect to the OHF Enterprise Schema where the OHF_CATALOG_LINEAGE table is installed.

8. Expand the Tables section.

9. Right click on the OHF_CATALOG_LINEAGE table and select Import Data.

10. In the File browser, navigate to the location where the Catalog Metadata File is saved.

11. Choose the .csv file type and select the Catalog Metadata file.

12. Click Open.

13. When prompted to Import Data Record Terminator, click OK.

14. When prompted to Import Data Field Format, click OK.

15. In the Data Import Wizard, select the delimited format.

16. Set the Delimiter to |~.

17. Set the Left Enclosure to none.

Figure 3-5 Data Preview

18. When prompted to Import Data Field Format, click OK.

19. Click Next.

20. In the Import Method screen, click Next.

21. In the Selected Columns pane, make sure all columns are listed and click Next.

22. In the Column Definition screen, map all the source data columns to the target table columns. The source and target columns have similar names and can be matched either by name or by order.

Figure 3-6 Column Definition

23. After you map all the columns, click Next.

24. Click Finish.

25. A data import progress screen is displayed.

26. After the import is complete, a success message is displayed. Click OK.

27. Query the OHF_CATALOG_LINEAGE table to retrieve the record count and compare it with the row count in the Catalog Metadata file:
Select count(*) from OHF_CATALOG_LINEAGE;

Refresh the OBIEE Dashboards

To view the updated data lineage information in the Data Lineage Dashboard, purge the OBIEE cache by following the instructions below:

1. Log in to the Oracle BI Analytics page. For example: http://obixxx.mycompany.com:9704/analytics

2. Go to Administration.

3. In the Session Management section, select Manage Sessions.

4. Click Close All Cursors.

5. Go to Administration.

6. In the Maintenance and Troubleshooting section, select Issue SQL.

7. In the Issue SQL screen, type the following command in the text box:
Call sapurgeallcache()

8. Click Issue SQL.

9. After purging the cache, the following success message is displayed.
[59118] Operation SAPurgeAllCache succeeded!

Now, the custom reports will be visible in the Data Lineage Dashboard.

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2018, Oracle and/or its affiliates. All rights reserved.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Pre-General Availability Draft Documentation Notice

If this document is in public or private pre-General Availability status:

This documentation is in pre-General Availability status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Private Pre-General Availability Draft Documentation Notice

If this document is in private pre-General Availability status:

The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your pre-General Availability trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.