Custom Views
This section describes how to use custom views to add flexibility and extend the functionality of Yotilla. Custom views makes it possible to implement transformations and business logic that currently cannot be implemented with business formulas, such as aggregations.
We recommend that you read the Getting Started and Using Yotilla sections first to better understand the procedure and the terminology used in this section.
Typical use cases for custom views are:
- Using aggregations or analytical statements in business formulas
- Implementing complex transformations and business logic
- Creating calculated business keys or business references
The following provides an overview of how custom views work and describes the general workflow for creating them. For step-by-step examples of how to create a custom view for a specific use case, see Custom Views Examples.
How Custom Views Work
A custom view is based on a database view created by the user on the target database. This database view will access the data in the data warehouse.
To simplify how the database view accesses the data, and to guarantee that the right data is available at the right time, Yotilla provides input views. An input view contains the business attributes of a single business object required to implement the custom view logic. If business attributes of multiple business objects are required, a separate input view is generated by Yotilla for each business object. The required business attributes are defined in the USES clause of the TinML command DEFINE CUSTOM VIEW.
Yotilla will always create an input view representing the load timeline of the business object. If the business object has at least one source with an inscription time, Yotilla will also create an input view representing the inscription timeline of the business object.
Input views are stored in the iv schema on the target database and are named using the pattern v_<business object name>__for__ <custom view name>_<timeline>.
<business_object_name>
|
Name of the business object with the required business attributes |
<custom_view_name>
|
Name of the custom view |
<timeline>
|
LTL = load timeline, ITL = inscription timeline |
The database view must only access the input views that were provided specifically for the custom view. It must never directly access the created data warehouse tables and business object views, since the result may then be incomplete and/or out of date.
Create a Custom View
Creating a custom view requires a sequence of steps to be carried out both in Yotilla and on the target database. The following is a general description of each of the major steps in the procedure for creating a custom view. For step-by-step examples, see Custom Views Examples.
Prerequisites
The user or role creating the custom view must have privileges to create a schema on the target database.
Procedure
Step 1: Create a schema on the target database
This step cannot be carried out in Yotilla, it must be done using a database client connected to the target database.
Create a new schema on the target database using your preferred database tool. This schema is where you will create the database view for your custom view. The schema can be used for multiple custom views.
Grant read privileges on the schema to the roles yotilla_admin_role and yotilla_execute_template_role.
For more information about the roles created by Yotilla, see Prepare the Target Database.
Step 2: Define a source system for custom views
Define a new source system to be used only for your custom views.
If you are defining the new source system using the TinML command DEFINE SOURCE SYSTEM, use the ON TARGET SYSTEM option.
Step 3: Define the custom view and the required business attributes using TinML
Use the TinML command DEFINE CUSTOM VIEW and specify the required business attributes in the USES clause.
To change the USES clause in an existing custom view, use ALTER CUSTOM VIEW.
Step 4: Let Yotilla generate the input views
On the Sources page, click on Apply for the custom view.
Yotilla will generate input views on the target database based on the defined business attributes. The input views are created in the iv schema.
Step 5: Create the database view of the custom view
This step cannot be carried out in Yotilla, it must be done using a database client connected to the target database.
Create a database view in the custom views schema that you created in the first step.
The database view must provide a column with a valid from
timestamp that defines the validity of each row. This attribute will be used as the inscription time in the custom view. The database view must provide the data for the complete timeline.
The SELECT statement of the database view must only access the input views provided for the custom view. The database view must never directly access the created data warehouse tables and business object views, since the result may then be incomplete and/or out of date.
Step 6: Scan the database view of the custom view
On the Sources page, click on Scan metadata for the custom view. The database view columns of your custom view will be scanned and displayed on the page.
Step 7: Map a business object to the custom view columns
Yotilla will by default map a new business object to all custom view columns. On the Sources page, rename or remove the new business object and map an existing business object to the custom view columns. Mark the custom view columns used as business key for the mapped business object. You can also add business references to your custom view.
You can only map exactly one business object to a custom view.
Step 8 : Configure the custom view
In the Edit source dialog for the custom view, select the attribute to use as the inscription time. If your custom view provides a delete flag, you can also define it in this dialog.
Done!
Your custom view definition is now complete, and the business attributes of the custom view can be used like any other business attribute in business formulas or in the dimensional model.
Modify a Custom View
To modify an existing custom view, repeat steps 3 to 8 in Create a Custom View.
