Search TipsCreating BI-Friendly User-Defined Views from User Extension Tables
Overview
When a new User Extension (UE) table is created or modified
This new view includes columns with look-up data for LOVs. For example, if the UE table includes a field JOB_CODE, the new view will also include column JOB_DESC (Job Description). The view will have the same name as its UE Table Code and will be located in CMIC_USER_DEFINED_VIEWS schema (owner).
The user-defined view is generated for new UE tables only. However, users can generate this view on-demand for previously created UE tables by navigating to the User-Defined Views Maintenance screen, selecting the UE table, and pressing the [Create Views] button.
Process
The following example shows how a BI-friendly user-defined view is created in the System Data module and made available in BI Catalog Builder.
Pgm: UETABLE – UE Table Maintenance; standard Treeview path: System > User Extensions > Table Maintenance
A UE table called JABADDINFO (Job Additional Info) has been created in the UE Table Maintenance screen in the System Data module . A special category field for a job has been created called DM_CATCODE.
Edit Data Source Screen in BI Catalog Builder
In BI Catalog Builder, select your data source from the Edit Data Sources screen. Right-click on the data source and select "Edit Available Tables/Views". The screen shown above will be launched, which is used to edit the available tables/views of the selected data source. Add the corresponding view: CMIC_USER_DEFINED_VIEWS.JOBADDINFO.
Create Business Area Screen in BI Catalog Builder
Then add the view into a business area, as shown above.
Edit Data Sources Screen in BI Catalog Builder
The new folder will be created containing look-up fields with descriptions for all LOV-enabled UE fields, as shown above.
These user-defined views will be generated automatically for all newly created UE tables and whenever a previously created UE table is modified.
To learn more about using this screen, refer to Edit Data Sources screen.