Add a database script

Open an existing model.

Expand the Database Scripts section.

Collapse the design panel.

Click + DatabaseScript.

Name the script, replacing the default Database Script text.

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.

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

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.

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.

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.

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

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

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

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.

Click Save.

Expand the Database Scripts section.

Select one or more scripts that you want to remove

Click on the X button or right click and click Delete.