Page tree
    Skip to end of metadata
    Go to start of metadata

    This topic describes how to import generic data using the Columnar SQL Extractor.

    This topic contains the following sections:

    Integration steps

    To integrate TrueSight Capacity Optimization with the CSV columnar database extractor, perform the following task:

    1. Navigate to Administration >  ETL & SYSTEM TASKS > ETL tasks.
    2. In the ETL tasks page, click Add > Add ETL under the Last run tab.
    3. In the Add ETL page, set values for the following properties under each expandable tab.

      Note

      Basic properties are displayed by default in the Add ETL page. These are the most common properties that you can set for an ETL, and it is acceptable to leave the default selections for each as is.

      Basic properties

      Property Description
      Run configuration
      ETL module Select Generic - Columnar database extractor.
      ETL task name Default name is already filled out for you.
      Run configuration name Default name is already filled out for you.
      Deploy status Select Production.
      Description (Optional) Enter a brief description.
      Log level Select how detailed you want the log to be:
      • 1 - Light: Add bare minimum activity logs to the log file.
      • 5 - Medium: Add medium-detailed activity logs to the log file.
      • 10 - Verbose: Add detailed activity logs to the log file.
      Execute in simulation mode Select Yes.
      When set to Yes, the ETL will not store actual data into the data warehouse. This option is useful while testing a new ETL task.
      Module selection

      Ensure that the Based on datasource option is selected.

      Note

      If you select Based on Open ETL template, TrueSight Capacity Optimization is integrated with a Generic extractor based on the selected Open ETL template. For more information, see   Generic ETL based on a template .  

      Module description A link that points you to technical documentation for this ETL.
      Datasets
      1. Click Edit.
      2. Select one (click) or more (shift+click) datasets that you want to include from Available datasets and click >> to move them to Selected datasets.
      3. Click Apply.
      Entitycatalog
      Sharing status Select any one:
      • PRIVATE: Select this option if this is the only ETL that extracts data from the given set of resources and the lookup table is not shared with the specified ETL task.
      • SHARED: Select this option if more than one ETL extracts data from the given set of resources and the lookup table is shared with the specified ETL task.
      Object relationships
      After import

      Specify the domain where you want to add the entities created by the ETL. You can select an existing domain or create a new one.

      Select any one of the following options:

      • leave all new entities in 'Newly Discovered'.
      • move all new entities in a new Domain.
        • New domain: Create a new domain. Specify the following properties under New domain:
          • Parent: Select a parent domain for your new domain from the domain selector control.
          • Name: Specify a name for your new domain.
      • move all new entities in an existing Domain 
        • Domain: Select an existing domain from the domain selector control.
      SQL query
      SQL query This tab is only available when a custom database query has to be built in order to get the data from a generic source. The form allows you to configure the following properties. A text area in which you can type the SQL extraction query.
      Check last counter Select any one:
      • Yes: Select Yes to check last counter.
      • No: Do not check last counter.
      Connection parameters
      Database type

      Select the database type:

      • Oracle
      • Microsoft SQL Server/Sybase
      • Otherdatabase .
      Database user

      Type in the database user name.

      Database password Enter the database password.
      Database TNS name Type in the Database TNS name.
      Data source name (Applies to  Microsoft SQL Server/Sybase ) Type in the data source name.
      Database name  (Applies to  Microsoft SQL Server/Sybase ) Type in the database name.
      Connection URL (Applies to  Other database ) Enter the connection URL.
      ETL task properties
      Task group Select a task group to classify this ETL into.
      Running on scheduler Selectthescheduleryouwant to run the ETL on.
      Maximum execution time before warning The number of hours, minutes ordaystotoexecutethe ETL for before generating warnings, if any.
      Frequency Select the frequency of ETL execution. Available options are:
      • Predefined: Select a Predefined frequency from Each Day, Each Week or Each Month.
      • Custom: Enter a Custom frequency (time interval) as the number of minutes, hours, days or weeks to run the ETL in.
      Start timestamp: hour\minute (Applies to Predefined frequency) The HH:MMstarttimestamp to add to the ETL execution running on a Predefined frequency.
      Custom start timestamp Select a YYYY-MM-DD HH:MMtimestampto add to the ETL execution running on a Custom frequency.

      Note

      To view or configure Advanced properties, click Advanced. You do not need to set or modify these properties unless you want to change the way the ETL works. These properties are for advanced users and scenarios only.

      Advanced properties

      Property Description
      Run configuration
      Default last counter Set the default last counter in dd/mm/yyy hh:mm:ss format.
      Collection level 
      Metric profile selection

      Select any one:

      • Use Global metric profile: Select this to use an out-of-the-box global profile, that is available on the Adding and modifying metric profiles page. By default, all ETL modules use this profile.
      • Select a custom metric profile: Any metric profiles you add in the Add metric profile page (Administration  >  DATAWAREHOUSE  >  Metric profiles).

      For more information, see Adding and modifying metric profiles.

      Levels up to

      The metric level defines the amount of metric imported into the data warehouse. If you increase the level, additional load is added to the data warehouse while decreasing the metric level reduces the number of imported metrics.

      Choose the metric level to apply on selected metrics:

      • [1] Essential
      • [2] Basic
      • [3] Standard
      • [4] Extended

      For more information, see Aging Class mapping.

      Metric

      Metric list  <for selected dataset>

      Note: This property is activated only after you select Datasets under theRun configuration tab.

      1. Click Edit.
      2. Select one (click) or more (shift+click) metrics that you want to include from Available items and click  >>  to move them to Selected items.
      3. Click Apply.
      Format customization
      Timestamp format Specify a format that should be used by the ETL if the user tables or CSV files use an unsupported format.<YYYY-MM-DDHH:MM:SS>isthe supported format. Depending on the type of ETL used, you might need to specify a custom format.
      Percentage format
      • 0 to 1:
      • 0 to 100:
      Additional properties
      List of properties
      1. Click Add.
      2. Add an additional property in the etl.additional.prop.n box.
      3. Click Apply.
        Repeat this task to add more properties.
      Loader configuration
      Empty dataset behavior Choose one of the following actions if the loader encounters an empty dataset:
      • Abort: Abort the loader.
      • Ignore: Ignore the empty dataset and continue parsing.
      ETLlogfilename Name of the file that contains the ETL execution log; the default value is: %BASE/log/%AYEAR%AMONTH%ADAY%AHOUR%MINUTE%TASKID
      Maximumnumberofrows for CSV output A number which limits the size of the output files.
      CSV loaderoutputfilename Name of the file generated by the CSV loader; the default value is: %BASE/output/%DSNAME%AYEAR%AMONTH%ADAY%AHOUR%ZPROG%DSID%TASKID.
      BCO loaderoutputfilename Name of the file generated by the TrueSight Capacity Optimization loader; the default value is: %BASE/output/%DSNAME%AYEAR%AMONTH%ADAY%AHOUR%ZPROG%DSID%TASKID.
      Detail mode Select the level of detail:
      • Standard: Data will be stored on the database in different tables at the following time granularities: Detail (configurable, by default: 5 minutes), Hourly, Daily, Monthly.
      • Raw also: Data will be stored on the database in different tables at the following time granularities: Raw (as available from the original data source), Detail (configurable, by default: 5 minutes), Hourly, Daily, Monthly.
      • Raw only: Data will be stored on the database in a table only at Raw granularity (as available from the original data source).

      For more information on granularities, see Accessing data using public views and Sizing and scalability considerations.

      Reduce priority
      • Normal:
      • High:
      Remove domain suffixfromdatasourcename (Only for systems) If set to True, the domain name is removed from the data source name. For example, server.domain.com will be saved as server.
      Leave domain suffix to system name (Only for systems) If set to True, the domain name is maintained in the system name. For example: server.domain.com will be saved as such.
      Update grouping object definition If set to True, the ETL will be allowed to update the grouping object definition for a metric loaded by an ETL.
      Skip entity creation (Only for ETL tasks sharing lookup with other tasks) If set to True, this ETL does not createanentity, and discards data from its data source for entities not found in TrueSight Capacity Optimization. It uses one of the other ETLs that share lookup to create the new entity.
      Scheduling options
      Hour mask Specify a value to execute the task only during particular hours within the day. For example, 0 – 23 or 1,3,5 – 12.
      Day of week mask Select the days so that the task can be executed only during the selected days of the week. To avoid setting this filter, do not select any option for this field.
      Day of month mask Specify a value to execute the task only during particular days within a month. For example, 5, 9, 18, 27 – 31.
      Apply mask validation By default this property is set to True. Set it to False if you want to disable the preceding Scheduling options that you specified. Setting it to False is useful if you want to temporarily turn off the mask validation without removing any values.
      Execute after time Specify a value in the hours:minutes format (for example, 05:00 or 16:00) to wait before the task must be executed. This means that once the task is scheduled, the task execution starts only after the specified time passes.
      Enqueueable Select one of the following options:
      • False (Default): While a particular task is already running, if the next execution command arises – it is ignored.
      • True: While a particular task is already running, if the next execution command arises – it is placed in a queue and is executed as soon as the current execution ends.
    4. Click Save.
      You return to the Last run tab under the ETL tasks page.
    5. Validate the results in simulation mode: In the ETL tasks table under ETL tasks > Last run, locate your ETL (ETL task name), click  to run the ETL.
      After you run the ETL, the Last exit column in the ETL tasks table will display one of the following values:
      • OK: The ETL executed without any error in simulation mode.
      • WARNING: The ETL execution returned some warnings in simulation mode. Check the ETL log.
      • ERROR: The ETL execution returned errors and was unsuccessful. Edit the active Run configuration and try again.
    6. Switch the ETL to production mode: To do this, perform the following task:
      1. In the ETL tasks table under ETL tasks > Last run, click the ETL under the Name column.
      2. In the Run configurations table in the ETL details page, click  to edit the active run configuration.
      3. In the Edit runconfigurationpage, navigate to the Run configuration expandable tab and set Execute in simulation mode to No.
      4. Click Save.
    7. Locate the ETL in the ETL tasks table and click  to Run it, or schedule an ETL run.
      After you run theETL, or schedule the ETL for a run, it will extract thedataform the source and transfer it to the TrueSight Capacity Optimization database.

    Configuring the ETL

    Below is an example query that can be used to extract generic data. It can be used as a template to create custom extraction queries:

    SELECT d.ts as TS, d.interval as DURATION, d.id as DS_SYSNM, d.name as SYSNM, d.fsname as SUBOBJNM, d.avgvalue as BYFS_FREE, d.size as BYFS_SIZE
    from my_table d and d.ts > ?
    

    You can use the :PARL placeholder parameter one or multiple times in a query; it is replaced by the last saved lastcounter value.

    #Example 1
    SELECT d.ts as TS, d.interval as DURATION, d.id as DS_SYSNM, d.name as SYSNM, d.fsname as SUBOBJNM, d.avgvalue as BYFS_FREE, d.size as BYFS_SIZE
    from my_table d and d.ts > :PARL
    
    #Example 2
    SELECT d.ts as TS, d.ats as ACTIVITYDATE, d.interval as DURATION, d.id as DS_SYSNM, d.name as SYSNM, d.fsname as SUBOBJNM, d.avgvalue as BYFS_FREE, d.size as BYFS_SIZE from my_table d and d.ts > :PARL OR d.ats > :PARL

    To import system metrics, configure the SYSDAT dataset to import system metrics. The 'TS' column is used as last counter. You can configure the default last counter in Advanced mode; if not configured, the default is 7 days from the ETL start date.

    To import business driver data, configure the WKLDAT dataset as shown below:

    SELECT d.ts as TS, d.interval as DURATION, d.id as DS_WKLDNM, d.name as WKLDNM, d.pagesinperiod as WEB_HITS, d.accountsrefresh as TOTAL_EVENTS
    from my_table d and d.ts > ?

    The recommended minimum and maximum sampling interval in TrueSight Capacity Optimization is 5 minutes and 5 days.

    Supported datasets

    This ETL supports vertical datasets: SYSDAT WKLDAT .

    Metric filtering is performed within the extraction query. Only one dataset per extraction is supported.

    Related topics

    Using ETL datasets

    Generic - CSV file parser

    Developing custom ETLs

    Dataset reference for ETL tasks

    Horizontal and Vertical datasets

    Viewing datasets and metrics by dataset and ETL module

    Determining how to aggregate data to a given resolution using SQL

    4 Comments

    1.  

    2.  

      1.  

        1.