Creating a SQL-based data mart for a custom view

The following video (3:48) illustrates the process of creating a SQL-based custom data mart that can be used to build a custom view.

  https://youtu.be/3Wz_oo4aABo

Steps for creating a data mart for a custom view using the basic options

Before you begin

Access the Data marts page. For more details, see Managing-data-marts-for-custom-views-reports-and-report-templates .

To add a data mart for building a custom view

1. In the Data marts page, select Add > Data mart based on SQL.

   The Data marts wizard is displayed.

2. On the Welcome page, under the Purpose of data mart, select Build Capacity View.
3. Under the Content of data mart, select one of the following options:
   • Summary information for a set of entities: Adds a data mart that provides a summary information about the entities returned by the SQL query.
   • Time series details for a set of entities: Adds a data mart that provides a time series details about the entities returned by the SQL query. When you select this option, columns returned by SQL query are checked to ensure that one column contains Timestamp set as the Meaning. This is automatically detected when the query is executed or can be manually set for the required column in the Columns page.
4. Click Next.

5. On the Definition page, set values for the following properties:

   • Primary entities: Select any one of the following options:
     • System: Performs a check on the columns extracted by the query to identify whether one or more columns contain System Identifier as the meaning.
     • Business driver: Performs a check on the columns extracted by the query to identify whether one or more columns contain Workload Identifier as the meaning.
     • Domain: Performs a check on the columns extracted by the query to identify whether one or more columns contain Application Identifier as the meaning.
     • Other
   • SQL Query: Type SQL query to retrieve the required data by selecting the required columns of a table. 

   Tip

   To display filter for the columns in the view, you can use a query that contains a filter macro. For more details about filter macro, see Using filter macros with SQL queries.

6. Click Next.
7. 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.: 
   
   

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

     

     If you want 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.

     Example of using Link renderer with page parameter

     For example, you want a custom view that shows a table with all the virtual machines in your infrastructure and the view page should have a provision to link to the details of each VM.
     

     To do so, you need to configure a custom view page (VM Overview) to show a table with a list of VMs and a custom view page (VM Details) to show the VM details. The VM Details page is created using the System details template, which supports sysid as the page parameter. 

     1. In the data mart that is used for the VM Overview page, select System Identifier as the Meaning of the sysid column.
        
     2. In the custom view configuration, set the renderer for the VM Name column to Link and select the target view page as VM Details.
        
        

     When you click on a VM Name link in the VM Overview page, the page parameter sysid is used to identify the VM whose name is clicked and the VM Details page with the corresponding VM's information is displayed in the VM Details page.

     Note that the parameter is added as a suffix to the target view page URL as shown in the following screenshot.

     
     

     You can 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. Following options are displayed when you select Value as the Meaning: Related to metric: Select the required metric type to which this value in the output column is related. After you select the required metric type, select 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 drop-down 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). You can then specify the Filter behavior to use distinct values or you can 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 using the >>DESC or  >>ASC button to sort the data in the descending order or ascending order respectively. In the Selection table, select the required column, and add them to the Source table by using the << button. You can also change the order of display of columns added to the Selection table by using the Up or Down arrow buttons.
   • 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.

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

   Materialization option	Description
   Always see fresh data	Select this option to load fresh data every time you access the associated custom view. Failed to execute the [excerpt-include] macro.
   Create materialized table	Select this option to load materialized data in the associated custom view. Failed to execute the [excerpt-include] macro.

9. Failed to execute the [excerpt-include] macro.
   
10. Failed to execute the [excerpt-include] macro.
    
11. Click Finish.
    The new data mart is added in the Data marts list page and can be used to build a custom view.