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-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. 

   Tip

   For similar column names, click Guess Matching Fields to let Reporting Metadata Studio determine the columns.

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
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.
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.

Task 7: 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 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-the-visual-query-builder.