Create and Publish Data Views and Metadata

After you create a data view, you must publish it to be able to use it in user-defined reports, data cubes, or custom reports.

Note: If you intend to use data views in user-defined reports, be aware that the data views must contain project_id in the SQL query for the data view to show up in a project or shell-level UDR data type list.

Depending on the data elements you choose, you might need to publish the metadata associated with the data view. For example, if the selected data element has a data definition type of pull-down Menu, you will have to publish the metadata to see the actual label of the pull-down rather than the value. In the case where a data element is defined as Actual Value? based on a Yes/No data element, with 0= No and 1=Yes, if Actual Value? is used in the data view, you must publish the metadata of Actual Value? to get the Yes or No value rather than 0 or 1.

To create data views

Go to the Company Workspace tab and switch to Admin mode.
In the left Navigator, click Data Structure Setup > Data Views.
The User-Defined Data Views log opens.
In the toolbar, click New.
In the Create a Data View window that opens, the Prefix is auto-populated with Unifier_.
Enter the name of the data view, the label, an optional description, and the SQL query for the data view.
For more information on how to create SQL queries used in the creation and management of data views, consult the Oracle Consulting Services team.
Click OK.
Note: When creating data views, the column naming convention must follow the Oracle DB guidelines. Do not use the Oracle-reserved words such as "(", ")" and so forth to name the column. For details, refer to Oracle DB guidelines.

To check the data view for potential issues

Go to the Company Workspace tab and switch to Admin mode.
In the left Navigator, click Data Structure Setup > Data Views.
The User-Defined Data Views log opens.
Select the applicable data view.
In the toolbar, click Explain Plan and select Run Explain Plan.
Review the output.
If an entry in the Operation column is highlighted in yellow, hover over the highlighted entry to view additional information.
When you are done reviewing suggestions, click Close.
After you run an Explain Plan, the system displays an icon in the Performance Alert column of the User-Defined Data Views log if potential issues were identified.

To publish data views

Go to the Company Workspace tab and switch to Admin mode.
In the left Navigator, click Data Structure Setup > Data Views.
The User-Defined Data Views log opens.
Select an unpublished data view.
In the toolbar, click Status and select Published.
Click OK.

To publish metadata

You can associate metadata with its data set for both labels and values. Data views tend to contain technical shorthand from system and SQL abbreviations. While clear to the technical person who wrote the data view, report consumers will appreciate seeing the same field labels and field value selections that they see in their business processes and managers.

For example, in your data view you might include the data element "year_pd," which represents a pull down menu with selections for different years assigned to its data set. In your user-defined report, you might want to see the label "Year" instead of "year_pd"; and you might want to query on meaningful values, such as 1999, 2001, 2002, and so on, instead of the numeric values of the data definition, 1,2,3, and so on.

Go to the Company Workspace tab and switch to Admin mode.
In the left Navigator, click Data Structure Setup > Data Views.
The User-Defined Data Views log opens.
Select a published data view.
In the toolbar, click Metadata.
In the Metadata for <data view> window, double-click the Definition field for each data element and select the data definition for the selected element.
The data definitions available for selection are filtered by the data elements used.
Double-click the Label field for each element you want to use in the UDR, and rename it to whatever you want to see in the report.
Click Publish Metadata.
Notes:
- The SQL definition will consist of the SQL query used to populate the report with specific data based on the desired output.
- If using the data view as a Main view in a Project-level report, you must have a column named project_id. If using the data view in a Program-level report, you must have a column named program_id.

For example, a Project-level data view for a company registry might be:

SELECT pu.PID as PROJECT_ID,

p.PROJECTNUMBER as PROJECTNUMBER,

c.COMPANYNAME as COMPANY_NAME,

c.SHORTNAME as COMPANY_SHORT_NAME,

c.DESCRIPTION as COMPANY_DESCRIPTION,

c.CONTACTNAME as COMPANY_CONTACT_NAME,

u.FULLNAME as USER_FULL_NAME,

u.USERID as USER_ID,

u.USERNAME as USER_NAME,

u.EMAIL as USER_EMAIL

FROM SYS_USER_INFO_VIEW u,

SYS_COMPANY_INFO c,

SYS_PROJECT_INFO p,

SYS_PROJECTUSER pu

WHERE c.COMPANYID = p.COMPANYID

AND p.PID = pu.PID

AND u.userid = pu.userid

To create data views

To check the data view for potential issues

To publish data views

To publish metadata

See Also