Create and Publish Data Views and Metadata

To use a data view in user-defined reports (UDRs), data cubes, or custom reports, create and publish the data view.

Create Data Views

Before you begin, ensure that you are working in Admin mode.

  1. Go to the Company Workspace tab and switch to Admin mode.
  2. In the left Navigator, select Data Structure Setup, and then select Data Views.
  3. In the User-Defined Data Views log (right pane) select New from the toolbar.
  4. In the Create a Data View window, enter the following:
    • Prefix: By default, the field is populated with unifier__.
    • Name: Enter a name for the data view.
    • Label: Enter a label for the data view.
    • Description: (Optional) Enter a description of the data view.
    • SQL Definition: Enter the SQL query for the data view.

    Note:

    • If you plan to use the data view in UDRs, the SQL query must include a project_id column so that the data view appears in the project-level or shell-level UDR data type list.
    • Do not use Oracle reserved words or special characters, such as parentheses (( and )), as column names. Follow Oracle Database column naming conventions when you create data views.
    • Do not use double quotation marks for column aliases in SQL queries. Double quotation marks can cause errors when you run UDRs or dashboards.
    • For more information about creating SQL queries for data views, contact Oracle Consulting Services.

  5. Select OK.

    A warning message may appear and list columns enclosed in double quotation marks.

Check a Data View for Potential Issues

  1. Go to the Company Workspace tab and switch to Admin mode.
  2. In the left Navigator, select Data Structure Setup, and then select Data Views.
  3. In the User-Defined Data Views log (right pane), select the data view.
  4. From the Explain Plan menu on the toolbar, select Run Explain Plan.
  5. Review the output.
  6. If an entry in the Operation column is highlighted in yellow, move the pointer over the highlighted entry to view additional information.
  7. After you review the recommendations, select Close.

    If the system identifies potential issues, an alert icon appears in the Performance Alert column of the User-Defined Data Views log.

Publish Data Views

  1. Go to the Company Workspace tab and switch to Admin mode.
  2. In the left Navigator, select Data Structure Setup, and then select Data Views.
  3. In the User-Defined Data Views log (right pane), select an unpublished data view.
  4. From the Status menu on the toolbar, select Published.

  5. Select OK.

    A warning message may appear and list columns enclosed in double quotation marks.

Label Display in Published Data Views

When a Data View is published, column labels are determined using the following logic:
  • The system first checks the Data Structure setup for a matching data element. If a match is found, the label defined for that data element is used. For example, a field named short_desc displays as Short Description when a corresponding data element exists in the Data Structure setup with that label.
  • If no matching data element is found, the system applies default formatting:
    • Labels in all uppercase are converted to title case. For example, CONTRACT AMOUNT displays as Contract Amount. Words containing underscores are displayed in title case. For example, ue_con_amount displays as Ue_con_amount.
    • Labels that you modify manually in metadata are preserved.

Publish Metadata of a Data View

You can publish metadata for a data view to associate metadata definitions with the data set for labels and values. Publishing metadata enables report consumers to see meaningful labels and field values in business processes and reports instead of internal data values.

For example:

  • If a data element uses a pull-down menu data definition type, publish the metadata so that reports display the pull-down label instead of the stored value.
  • If your data view includes the year_pd data element, publishing the metadata enables the report to display the label Year instead of year_pd. The report also displays meaningful values, such as 1999, 2001, and 2002, instead of internal numeric values such as 1, 2, and 3.

Prerequisite

Before you begin, ensure that the data view is published and that you are working in Admin mode.

Procedure

To publish metadata of a data view:

  1. Go to the Company Workspace tab and switch to Admin mode.
  2. In the left Navigator, select Data Structure Setup, and then select Data Views.
  3. In the User-Defined Data Views log (right pane), select a published data view.
  4. From the toolbar, select Metadata.

  5. In the Metadata for <data view> window, do the following:

    1. To assign default metadata definitions automatically to metadata elements that do not have a data definition, select Assign Default Definition from the toolbar.

      Default data definitions are not applied to metadata elements that already have data definitions.

      Use Assign Default Definition if you want to apply metadata definitions in the following situations:

      • Visualizations and dependent data sources
      • Data views that you export and import by using configuration packages or add-on configuration packages

      Alternatively, for each data element, double-click the Definition field and select a data definition.

      The available data definitions are filtered based on the data elements that the data view uses.

    2. For each data element that you want to use in a UDR, double-click the Label field and enter the label that you want the report to display.
    3. Select Publish Metadata.

      A warning message may appear and list columns enclosed in double quotation marks.