Creating customized views

Before you begin

1. Finalize the database tables and columns based on your requirements.
2. Identify the relationships required to join database tables.
3. Install Reporting Metadata Studio.
   For details, see Installing-and-upgrading-Reporting-Metadata-Studio.

4. Make sure you have the access and secret keys for each environment where you want to publish the view.
   For instructions about generating these keys, see Setting up access keys for programmatic access.

To create and publish a view 

The process of creating and publishing a view involves the following tasks:
 

Task 1: To define a database connection

Reporting Metadata Studio can connect to most of the relational databases by using JDBC. You can define multiple connections to multiple databases.

1. Open Reporting Metadata Studio.
   1. Navigate to the following directory that contains the extracted files of Reporting Metadata Studio:
      Reporting_Metadata_Studio > metadata-editor
   2. Double-click reporting-metadata-studio.bat.
2. Right-click Connections and click New Connection.
   
   
    
3. In the Connection name field, enter a name for the connection.
4. Under Authentication, specify the credentials.
5. Click Test to validate connectivity, and then click OK.
6. Click OK.
7. On the Import Tables page, select the tables you want to import.
   The selected tables are added under the connection. You can also import tables later. 
8. (Optional) Click Explore to access and import a table inside a database schema.
   The connection is added under Connections.

Task 2: To import physical database tables

Import the physical database tables you want to include in a business model. Skip to the next step if you already imported them while defining the connection.

1. Right-click the database connection name and click Import Tables.

1. Use the shift or ctrl key to select multiple tables, and click OK.
   
   

   Tip

   To find a specific table, enter the table name in the Filter box or use a regular expression.

   All the imported tables are listed under the connection.

Task 3: To create a business model

Create a business model to map the physical structure of your database. The business model is an abstract database representation that includes business views, tables, and relationships.

1. Right-click Business Models, and click New Business Model.
2. Specify an ID, name, and description for the model.
   
   
   
   The specified name is displayed under Business Models.
3. From the Connection list, select the connection you defined.
4. To prevent JOIN conditions from processing in the WHERE clause, perform these steps:
   1. Click the  icon.
   2. Select Add a custom property.
   3. In the ID field, specify the ID as delay_outer_join_conditions.
   4. From the Type list, select Boolean.
   5. Click OK.
   6. In the Custom section, select the delay_outer_join_conditions check box.
5. Click OK.
   The model is created, and it displays the following entities: Business Tables, Relationships, and Business View

Task 4: To create business tables

Create a business table to map it to a physical table in the database. You can map multiple business tables to the same physical table.

1. Right-click Business Tables and click New Business Table.
2. From the Subject list, select the physical table you want to associate with the business table.
   
   
    
3. In the ID field, specify an ID for the table.
   Spaces are not permitted in the ID.
4. In the Name field under the General section, specify a name for the table.
   The table is added under Business Tables in the left pane.
5. Repeat these steps to add other business tables.

Task 5: To create relationships between business tables

Define relationships between any two business tables, link any two columns between them, and specify the type of relationship.

1. Right-click Relationships and click New Relationship.
2. From the From Table/Field list, select a source business table.
   
   
    
3. From the To Table/Field list, select a destination business table.
4. From the adjacent lists, specify the business columns from each table that identify the relationship. 
5. From the Relationship list, select one of the relationships, such as one-to-many, many-to-one, or many-to-many.
6. From the Join type list, select a joint type.
   The following joint types are supported: inner, left outer, and right outer

   Important

   Starting with version 25.2, the full outer join type is not supported.

   - If the full outer join is detected in a view that you import, an alert notification is displayed.
   - Publishing a view that includes the full outer join is not permitted.
   - If you create a SQL query by using a view that contains the full outer join, the error message is displayed.

7. In the Join order key field, specify the order to process the tables.
   This field applies only for outer joins.
8. (Optional) To use a complex join in the Where clause of a SQL statement:
   1. Select the Complex Join check box.

   2. In the Complex Join Metadata Formula field, add the expression for the complex join. 
      You must use the names of physical tables and columns in this expression.

      Example:

      To obtain task records of BMC Helix ITSM incidents only from the HPD:Help Desk table, perform these steps:

      1. Create a join between HPD:Help Desk and TMS:Task tables.

      2. In the Complex Join Metadata Formula field, add the following complex join criteria.

         AND(
         ["TMS_TASK.BC_TMS_TASK_ROOTREQUESTFORMNAME"]="HPD:Help Desk";
         ["HPD_HELP_DESK.HPD_INCIDENT_NUMBER"] = ["TMS_TASK.BC_TMS_TASK_ROOTREQUESTID"])

         

9. (Optional) In the Description field, add a description for the relationship.
10. Click OK.

The relationship between the business tables is created and displayed.

Task 6: To create a business view

Create a business view that includes categories and fields. Arrange business columns in these categories based on your business requirements.

1. Right-click Business View and select New Category.
2. Provide an ID, a name, and an optional description for the category.
3. Click OK.
4. Repeat these steps to define additional categories.
5. Right-click a category and select Manage Categories.
   
   
    
6. In the Available Business Tables area, select the required business table column and move it to the category in the Business View Categories area.
7. Repeat these steps for all the categories.
8. Click Close.
9. Click Save, provide a name for the view, and click OK.
   The view is saved locally.

(Optional) Task 7: To create a calculated field

Create a calculated field with a predefined value. You can use it as a filter while creating a SQL query in Visual Query Builder, which saves you the time to define filter conditions.

The following procedure explains how to create a calculated field to fetch the count of pending incidents.

1. Select File > Import from Helix Dashboards.
2. Select Incident Management and specify a name for the imported view.
3. Under Business Models, expand the view and Business Tables.
4. Right-click a business table and click Edit.
5. Click and select a column from the list in the Subject area.
   The column is added to the end of the table columns list.
6. Navigate to the end of the list and select the column that you added.
7. Click the overrides  icon in the following sections to update the column name, label, and properties:
   1. Name: Specify a name and ID according to your choice.
      
      
       
   2. Default Aggregation: Select an aggregation type from the Aggregation Type list.
      
      
       
   3. Data Type: Select Numeric from the Data Type list.
   4. Field Type: Select Fact from the Field Type list.
      
      
       

   5. Formula: Specify the following formula and select the Is the Formula Exact check box.

      SUM(CASE WHEN "HPD_HELP_DESK"."Status" = 'Pending' then 1 ELSE 0 END)

      where, HPD_HELP_DESK is the business table ID and Status contains the value in the Status column.

8. In the left pane, expand the business table that you edited, and locate the newly added column.
   The column is placed at the end of the list.
9. In the left pane, expand Calculated Fields and move the newly created column to the Calculated Fields list.
10. Publish the view.
    The calculated field is available for use in Visual Query Builder.

Task 8: To publish the view

Publish the view to the environment of your choice. If you modify a customized view, save the changes, and publish the view, the view name is populated in the Save as window. If you update the ID of a customized view and publish it, you are prompted to specify a new name for the view. After you publish the view, it becomes available for selection in the visual query builder. 

1. Specify the environments where you want to publish the view.
   1. Navigate to the directory where you extracted the files of Reporting Metadata Studio.
   2. Navigate to the following directory:
      metadata-editor > bmcauthconfiguration
   3. Copy the env.properties file and rename it; for example, QA-env.properties to publish the view to the QA environment.
   4. Add the environment details, such as tenant ID, URL, host name, and IP address.
   5. Repeat these steps to add other environments, such as staging and production.
2. Open Reporting Metadata Studio.
3. To publish the view to the environment of your choice:

1. 
   
   1. Select File > Publish to Helix Dashboards > <environment_name>.
      The environment name is the alias name taken from the env.properties file name.
      Example: From the QA-env.properties name, QA is shown as the environment name.
   2. Add the access key and secret key that are configured for the environment.
   3. Specify the name of the view file to be published.
   4. Click OK.
      A confirmation message about successful publishing is displayed.
2. (Optional) To change the default environment where you want to publish the view:
   1. Select File > Configure tenant environment.
   2. From the Environment list, select an environment.
   3. Select the Is Default check box.
   4. Click OK.
3. (Optional) To add a new environment in Reporting Metadata Studio:
   1. From the Environment Action list, select ADD.
   2. In the Environment Alias field, add the alias name.
   3. In the Tenant ID and Tenant URL fields, add the details required for authentication.
   4. To make this environment default, select the Is Default check box.
   5. Click OK.
      The environment is created. 
4. To delete an environment:
   1. From the Environment Action list, select DELETE.
   2. From the Environment list, select the environment you want to delete.
   3. Click OK.
      A confirmation message is displayed, and the environment is deleted.

The view is now available for selection while creating a SQL query in the visual query builder.

To create a copy of an out-of-the-box view 

You cannot edit any existing out-of-the-box view. You can create a copy of the view and then customize it. All the out-of-the-box views are prefixed with OOTB. 

1. Open Reporting Metadata Studio.
2. Select File > Import from Helix Dashboards.
3. Select the required XMI file of the view, and click OK.
   You are prompted to change the model name. 
4. Specify a new name for the model and click OK.
   The contents of the view are displayed.
   
   
    
5. Under Connections, connect to the data source by providing the credentials.
6. Modify the view.
7. Publish the updated view to the environment of your choice.
   For details, see To publish the view.

To refresh a custom view

Refresh a view to synchronize it with the latest changes made to the AR system. The following activities are performed during a refresh:

• The business columns that are not available in the AR system are removed along with their existing relationships and categories.
• If new columns are discovered in the AR system, they are added to the model.

1. Open Reporting Metadata Studio.
2. Under Business Models, right-click the model name and click Refresh.
   
   
   
   Depending on the number of columns to be processed, it might take some time for the refresh.

To apply the global constraint to a business table

Apply the global constraint to a business table of a view to define a filter condition, which saves you time because you do not need to add a separate filter while using the view in Visual Query Builder.

1. Open Reporting Metadata Studio.
2. Open the custom view that you created or import the required view.
3. Expand the business model.
4. Right-click the business table to which you want to apply the global constraint, and click Edit.
5. Click + and select Data Constraints on the Add New Property page.
6. Click OK and click Data Constraints from the Available list.
   
   
    
7. Under Settings, select Global Constraint.

8. Add the expression to specify the filter condition.
   Use the following syntax for the expression:

   [<Business Table ID>.<Business Column ID>]<Operator such as =,<,> and so on><value (use "" for a string value)>
   For multiple expressions, use ; between conditions.
   To specify AND / OR between a condition, use the following syntax:                                               

   <OR/AND>([<Business Table ID>.<Business Column ID>] <Operator><value>; [<Business Table ID>.<Business Column ID>] <Operator><value>)

   Examples of conditions for the Incident Management view

   To view the incidents that are critical and not resolved:

   AND ([HPD_HELP_DESK.HPD_PRIORITY] = "Critical";[HPD_HELP_DESK.HPD_STATUS] <> "Resolved")

   To view the incidents that are critical and for which group transfers are fewer than three:

   AND([HPD_HELP_DESK.HPD_PRIORITY] = "Critical";[HPD_HELP_DESK.HPD_GROUP_TRANSFERS] < 3)

   Supported operators in expressions

   Operator	Description
   =	Returns true if the two expressions are equal.
   >	Returns true if the first expression is larger than the second.
   <	Returns true if the first expression is smaller than the second.
   >=	Returns true if the first expression is larger than or equal to the second.
   <=	Returns true if the first expression is smaller than or equal to the second.
   <>	Returns true if the two expressions are not equal.
   +	Adds two values.
   -	Subtracts two values.
   *	Multiplies two values.
   /	Divides two values.

9. Click OK.

To delete a custom view

1. Select File > Delete Metadata View.
2. From the list of custom views, select the view that you want to delete, and click OK.
3. Click Yes to confirm deletion.

Where to go from here

Build a SQL query in the visual query builder based on the customized view. For details, see Creating-SQL-queries-by-using-Visual-Query-Builder.