Add a database script

Database scripts allow you to execute commands (via T-SQL) at specific times during warehouse processing. The scripts are specified using the Database Scripts section on the model tab.

You can create any number of custom database scripts, and, like with other data model resources, the configuration of each script takes place on a separate tab.

Add a database script

  1. Open an existing model.

  2. Expand the Database Scripts section.

  3. Collapse the design panel.

  4. Click + DatabaseScript.

  5. Name the script, replacing the default Database Script text.

  6. Optional:

    1. Add a description.

    2. Use the Enabled check box to activate the script.

      Note

      You can fully define the script and all of its settings, but it will not be included in the model processing until you enable it via this check box. Clearing this check box is a convenient way to temporarily disable a script.

  7. From the Script Type drop-down, select one of the following options, based on when you want the script to execute:

  8. Initialize - The script will be executed during warehouse publishing, before data migration. These types of scripts are run immediately before views and tables are created for pipelines. For example, a script might be used to create a stored procedure or function which is to be later used in a SQL step or calculation in a pipeline.

  9. Process - The script will be executed during warehouse processing (after data migration), but before the cube is processed. These types of scripts are run at the same time as pipelines are processed. For example, a script could be used to populate a warehouse table from a specific external source table.

    Note

    The precise run order of pipelines and scripts is determined by the Uses Pipelines and Uses Database Scripts settings in each script, and the Uses Database Scripts property in each pipeline. The run order of scripts can be viewed during processing in the Status area of the model Properties panel.

  10. For Process scripts, specify pipelines used by this script via the Uses Pipelines setting.

    1. Begin typing the name of the pipeline you want to add or click the + button in the right part of the corresponding text box. A list of matching, selectable pipelines appears.

    2. Select the pipeline from the list.

    3. If the database script modifies the warehouse table for a particular pipeline, right-click a pipeline in the text box and select Script Modifies Pipeline.

      Important

      This setting can only be used with Process scripts. If pipelines are added to a Process script and the type is changed to Initialize, the specified pipelines are still listed but the dependency is ignored. The same occurs if pipelines are drag-dropped into the text box.

      The specified pipeline will show the current database script in the Used By entry of the pipeline's Properties panel (in the Processing Dependance’s area). You can clear this setting by right-clicking the pipeline again and selecting Clear Script Modifies Pipeline.

      Note

  11. Specify other database scripts used by this script via the Uses Database Scripts setting. These scripts are those that must be executed before the current script is executed during model processing.

    1. Begin typing the name of the script you want to add, or click the + button in the right part of the corresponding text box.

    2. Select the pipeline from the list.

      Note

      The current database script cannot depend on a database script of a different type (as determined using the ScriptType setting).

  12. If you want the script's SQL appear in the Data Hub application log file, click the Show in Log check box.

  13. In the large SQL Statement text box, specify the T-SQL for the database script.

  14. As you type your T-SQL code, any matching items appear in a list, which you can select (such as pipelines, parameters, keywords, etc.). You can also add pipelines by dragging-and-dropping them directly from Resource Explorer.

  15. Click Save.

In this section: