Creating a SQL-based data mart for a custom view

Create a SQL-based data mart to manipulate data of summary data mart tables. This data is displayed in a custom view.

Using the Data marts wizard, you can build a custom data mart to retrieve the data according to your requirements for a custom view. You can configure the data mart based on the following options:

• Identify the primary entity of the data mart
• Apply required sorting options
• Configure the filters to be displayed in the views
• Set the materialization type and schedule

You can either create a simple data mart or an advanced data mart (with the required filters and output columns).

Before you begin

Make sure that at least one summary data mart is created in your environment. For instructions, see Creating a summary data mart for a custom view.

To add a data mart for building a custom view

1. Navigate to Administration > Data marts.

2. Search for the summary data mart that you want to use for creating a SQL query.
3. Note down its identifier from the Identifier column.

4. Select Add > Data mart based on SQL.

5. On the Definition page:

   1. Select a primary entity. 
      Depending on the entity selected, the columns extracted by the query are checked to identify whether one or more columns contain System Identifier, Workload Identifier, or Application Identifier as the meaning.

   2. Type a SQL query by using the summary data mart identifier that you obtained in step 3.

6. On the Columns page, modify the properties of the columns in the data mart. These properties will be used and displayed on the custom view page where the data mart is used. For details, see Properties in the Output Columns section.

7. On the Materializer page, specify the following details:

   • Always see fresh data: Loads fresh data every time you access the associated custom view. 
     

     Every time the SQL query runs, it fetches data directly from the database. Hence, a complex query might require more time to run and the performance might be impacted.

     
     

   • Create materialized table: Loads materialized data in the associated custom view. 

     This option is useful when you have a complex SQL query and you want to see or use the fetched data quickly. The data mart is materialized and the data is stored in a new table, which is shown whenever you choose to Preview or view the data.

     Specify the following settings:
     From the Materializer schedule list, select the task to be used for materializing the data mart. You can select from the available out-of-the-box tasks or create a new one. For more information about creating a materializer task, see Configuring the Data Mart Materializer task.
     Advanced Settings
     

     Tablespace	Enter the name of the tablespace where the generated table is materialized. If no value is specified, the default tablespace of the product is used.
     Materialize validity	Enter a number and select hour, day, or week to specify the time validity of the materialized data mart. The data mart is not re-materialized if you run the associated materializer task again while the time specified in this field is still valid. For example: Consider data mart A with Materialize validity of 6 hours. The associated materializer task for this data mart runs daily at 5 A.M. If the associated materializer task runs again at 10 A.M., the data mart A is not re-materialized because the time specified in the Materializer validity field is still valid.
     Index count	Select the number of blocks of indexes that you need. You can choose maximum 4 blocks. For each index block, in the corresponding Index # columns field, select the indexes from the Source list and move them to the Selection list by using the button. And, enter the Index # tablespace name.

8. On the Name page, enter a name for the data mart.

9. Enter a unique identifier for the data mart. Every identifier is prefixed with 'ER_V_'. By default, the identifier is suggested based on the data mart name. You can edit this value. The identifier must contain only letters, numbers or underscores, and it cannot be empty. This identifier can be used in the SQL query while creating dependent data marts.
   

   If you modify the identifier of the data mart that is currently in use by one or more data marts, the dependent data marts will stop working. A warning message is displayed with a list of dependent data marts. You can choose to revert the changes or proceed with the update. If you choose to proceed, you must update the identifier in the dependent data marts to continue to use them.

   
   

10. Click Finish.
    The new data mart can be used to build a custom view. 

Properties in the Output Columns section

• To modify the label, meaning, or filter behavior of a column, click the name of the required column and specify the values.

  

  To use this data mart in a custom view that uses the Link renderer with page parameters, you must specify the Meaning value for each page parameter. Otherwise linking with page parameters does not work.

• Select one of the following options from the Meaning list. 

  
  

  Meaning option	Description
  Output column
  Value	Select this option if the output column contains a value that is a statistic derived from a metric. When you select Value as the Meaning, the following options are displayed: Related to metric: Select the required metric type to which this value in the output column is related. Then, select a required metric from list. If there is no metric with which the output column can be associated, select None. Unit of measure: When you select a metric in the Related to metric, the associated option is set in the Unit of measure. If the Related to metric is set to None, select the required option from the Frequently used list, or select Other and type the required unit of measure in the box for the value in the output column. For example, if the Related to metric is CPU Utilization, the Unit of measure is set to %.
  Identifier
  System Identifier Business Driver Identifier Domain Identifier	Select one of these options if the output column contains an entity identifier (system, business driver, or domain). Then specify the Filter behavior to use distinct values or specify the system types to be used as options in the filter when this data mart is used.
  Model Identifier Scenario Identifier	Select this option if the output column contains a model or a scenario identifier.
  Name
  System Name	Select this option to set the meaning as system name of the systems or clusters that are being evaluated. When you select System Name as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Business Driver Name	Select this option to set the meaning as business driver name of the systems that are being evaluated. When you select Business Driver Name as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Domain Name	Select this option to set the meaning as domain name of the systems that are being evaluated. When you select Domain Name as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Type Identifier
  System Type Identifier	Select this option to set the meaning as system type identifier of the systems or clusters that are being evaluated. When you select System  Type Identifier as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Business Driver Type Identifier	Select this option to set the meaning as business driver name of the systems that are being evaluated. When you select Business Driver Type Name as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Domain Type Identifier	Select this option to set the meaning as domain type identifier of the systems that are being evaluated. When you select Domain Type Identifier as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Type Name
  System Type Name	Select this option to set the meaning as system type name of the systems or clusters that are being evaluated. When you select System  Type Name as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Business Driver Type Name	Select this option to set the meaning as business driver type name of the systems that are being evaluated. When you select Business Driver Type Name as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Domain Type Name	Select this option to set the meaning as domain type name of the systems that are being evaluated. When you select Domain Type Name as the Meaning, the Filter behavior field is displayed with the value set as Distinct Values.
  Other
  Timestamp	Select this option if the value in the column indicates a timestamp for any activity. No additional options are displayed when you select Timestamp as the Meaning.
  Threshold Good	Select this option if the value in the column indicates good threshold value for any metric. No additional options are displayed when you select Threshold Good as the Meaning.
  Threshold Warning	Select this option if the value in the column indicates warning threshold value for any metric. No additional options are displayed when you select Threshold Warning as the Meaning.

• In the Sorting Options section, select the required columns from the Source table, and add them to the Selection table. Use the DESC or ASC options to sort the data in the descending order or ascending order respectively. 

• In the Time period label box, type the required label for data mart that are not sensible to time filter. This field is displayed only when there are no time filter macros defined for the data mart or there no columns that have the meaning set as Timestamp. When the data mart is not sensible to time filter, the the time filter for the selected custom view built using this data mart is disabled and this label is displayed in the selected view.

• In the Detected filters section, perform the following steps:

  1. Click the name of the filter.
  2. In the Editor of the filter name page, edit the Label and Description of the filter.

  3. Click Apply.

• Specify the required unit of measure. You can choose from the Frequently used list or select Other and type the required unit of measure in the box for the value. This field is displayed only when the column contains numeric value.