How to use Snippets to create resuable code in TimeXtender Classic

Snippets

Snippets are reusable parameterized pieces of code that can be used in field transformations as well as other parts of your project that incorporate code. Snippets allow developers to write a section of code once and then use it in various parts of the TimeXtender Data Integration solution wherever appropriate. Snippets not only save time but can also simplify the maintenance of code should any changes in the future be needed to it after it has been deployed.

Snippet Categories

1. SQL
2. SSAS Multidimensional 
3. Qlik

Adding an SQL Snippet

The steps to create a snippet are similar for the various snippet types. The following example of creating an SQL Snippet illustrates the process of adding a snippet.

1. On the Tools menu, click Snippets. The Snippets window appears.

   

    

2. Click Add and click SQL Snippet. The Create SQL Snippet window appears.

   

    

3. In the Name box, type a name for your snippet.
4. (Optional) In the Description box, type a description of what the snippet does.
5. Enter the script in the Formula box. For any variables ([YEAR] in the example above), highlight the variable and then click the Add Parameter button to add the highlighted text as a parameter name under Parameters. If parameters include brackets around them, make sure that the brackets are included in your parameter name below.
6. Under Parameters, change the Type to match what the variable represents. The various parameter types are as follows:
   • Table
   • Field
   • Database
   • User Defined Function
   • Stored Procedure
   • Value
7. Click OK to save the snippet.

Using a SQL Snippet

SQL snippets can be used in a number of situations.

1. To use a SQL snippet in a Field Transformation, right-click the field, click Add SQL Snippet Transformation and click on the SQL snippet you want to use.

   

   
   - OR -
   To use a SQL snippet as a stored procedure , right-click Stored Procedures and click Add Snippet Stored Procedure and click on the SQL snippet you want to use.

   

   
   - OR -
   To use a SQL snippet as a used defined function, right-click User Defined Functions and click Add Snippet User Defined Function and click on the SQL snippet you want to use.

   

   
   - OR -
   To use a SQL snippet as a script action custom step, right-click Script Actions and click Add Snippet Custom Step and click on the SQL snippet you want to use.

   

    

2. In the window that appears, map the available fields to the parameters in the snippet. Drag the field(s) from the list on the right and drop the field on the Object Name/Value column for the relevant variable. The Object Name/Value column and Variant column will populate automatically.

   

    

3. Click OK.

Using an SSAS Multidimensional Snippet

SSAS Multidimensional Snippets can be used in the creation of Measures in OLAP Cubes. T

The following example outlines the usage of an SSAS Multidimensional Snippet for the creation of a measure.

1. Right-click Measures and select “Add Snippet” and then select the desired snippet to be used.

   

    

2. Enter a Name for the new item and select the appropriate Associated Measure Group from the drop-down.

   

    

3. In the Value column for the Parameters listed below, select the appropriate item from the drop-down.

   

    

4. After selecting a value for all of the parameters, all of the red exclamation points should no longer be there and the setup should be complete. Click the OK button.

   

    

5. Map the Parameters to fields available from the tables on the right.

   

    

6. Deploy and Execute the OLAP Cube to validate everything is setup correctly and the new measure is added to the cube.

Changing a Snippet

When an object has been created using a snippet, changes cannot be made to the object content or formula directly. In order to change the object content, the snippet that was used to create the object needs to be amended as described below.

Right click on the object that uses the snippet and choose “Edit <object type>” from the context menu, as in the following “Edit SQL Snippet Transformation” example. 

Click “Change Snippet” to change the snippet that is being used and then click OK.