Editing Your Transformations

Dimension Basics

The Dimension Designer's Transformation Editor enables you to create the query transformations that describe how to derive your virtual metadata information from physical metadata sources or other virtual metadata and how to update the sources.

The Transformation Editor provides a robust set of tools you can use to create these SQL queries. You can use these tools, or you can simply type a SQL query into the Transformation Editor.

When you double-click a transformation node in a transformation diagram, the Transformation Editor displays.

If you use the MetaMatrix XA Server, the tabs on the bottom of the Transformation Editor allow you to enter SQL for each type of query this virtual class supports. If this virtual class does not support updates, only the SELECT tab is available.

You can enter separate SQL queries on each available tab to accommodate that type of query.

Within the Transformation Editor, you can:

- Disable specific update transformation types on this virtual class.
- Build or edit a criteria clause to use in your transformation.
- Build or edit an expression to use in your transformation.
- Find a string within your transformation.
- Replace a string within your transformation.
- Validate the Transformation to ensure its content contains no errors.
- Reconcile target attributes to ensure the symbols in your Transformation match the attributes in your virtual metadata class.

You can also set preferences that impact the display of your Transformation Editor. For more information, see “Transformation Editor Preferences.”

Transformation Toolbar Actions

The Transformation Editor toolbar and it's actions is summarized below.

> Cursor Position (line, column)
> The SqlEditor shows the current line and column position of the insertion cursor. For example, Cursor Position(1,4) indicates that the cursor is presently located at column 4 of line 1.

> Supports Update
> The 'Supports Update' checkbox allows you to enable or disable updates for the current transformation target. If 'Supports Update' is checked, the editor shows four tabs at the bottom for the Select, Update, Insert and Delete transformations. If 'Supports Update' is unchecked, all updates are disabled and only the Select transformation is displayed.

> Reconcile
> The "Reconcile Transformation SQL with Target Attributes" action allows you to resolve any discrepancies between the transformation symbols and the target attributes. Pressing this button will display the "Reconcile Virtual Target Attributes" dialog box in which you can resolve descrepancies.

> Save/Validate
> The 'Save / Validate SQL' button saves edits to the current transformation and validates the transformation SQL. Any Warning or Error messages will be displayed at the bottom of the editor in the messages area. If the SQL validates without error, the message area is not displayed.

> Criteria Builder
> The 'Criteria Builder' allows you to build a criteria clause in your transformation. The button will enable if the cursor position is within a query that allows a criteria. Pressing the button will launch the Criteria Builder dialog. If the Criteria Builder is launched inside an existing criteria, that criteria will be displayed for edit, otherwise the Criteria Builder will be initially empty.

> Expression Builder
> The 'Expression Builder' allows you to build an expression within your transformation. The button will enable if the cursor position is at a location that allows an expression. Pressing the button will launch the Expression Builder dialog. If the Expression Builder is launched inside an existing expression, that expression will be displayed for edit, otherwise the Expression Builder will be initially empty.

> Expand Select *
> The 'Expand Select' button allows you to expand a "SELECT *" clause into a SELECT clause which contains all of the SELECT symbols. The button will enable only if the cursor is within a query that contains a SELECT * clause that can be expanded.

> Increase Font Size
> The 'Increase Font' button increases the font size of all editor text by 1.

> Decrease Font Size
> The 'Decreate Font' button decreases the font size of all editor text by 1.

> Show/Hide Messages
> The 'Show Messages' button toggles the display of the message area at the bottom of the transformation editor.

> Optimize SQL
> The 'Optimize SQL' button, when toggled 'ON', will use the short names of all SQL symbols that can be optimized. Some symbol names may remain fully qualified in the event of a duplicate name or if the optimizer is unable to optimize it. When the action is toggled 'OFF', all symbol names will be fully-qualified.

> Import SQL Text
> The 'Import SQL Text' button allows you to import a sql statement from a text file on your file system. Pressing this button will display an import dialog in which you can navigate to the file.

> Export SQL Text
> The 'Export SQL Text' button allows you to export the currently displayed sql statement into a text file on your file system. Pressing this button will display an export dialog in which you can choose the location for export.

> Close "X"
> The 'Close' button closes the transformation editor.

### Using the Criteria Builder

> The Transformation Editor’s Criteria Builder offers you a quick, graphical means to build criteria clauses in your transformations based on meta objects in your diagram. If you click a criterion before you launch the Criteria Builder, you can use the Criteria Builder to edit the selected criterion; otherwise, you can create a new criterion from scratch.

> This procedure provides an example of building a criteria clause using the Criteria Builder. When building your own criteria, you can mix and match the values and constants with whatever logic you need to build powerful and complex criteria.

> To use the Criteria Builder:

> Step 1: In the Transformation Editor, click the Launch Criteria Builder button .

> Step 2: The Criteria Builder displays.

> The two tabs at the top, Tree View and SQL View, show the current contents of the criteria you have built.
> The Criteria Editor at the bottom allows you to build a criteria clause. To build a criteria clause, you must add information to the left side of the predicate, select a comparison operator, and add a value to the right side.

> Step 3: The radio buttons on either side of the Predicate Editor let you choose what type of content to place in that side of your predicate. Click the radio button of the type of content you want to place in your criteria. You can click:

> - Attribute to add an attribute to the predicate. If you click the Attribute radio button, the Predicate Editor looks like this:

> > From the tree, select the attribute you want to add to the expression. You can select an attribute from any of the source classes in the transformation.

> - Constant to add a hard-wired constant value to the predicate. If you click this radio button, the Predicate Editor looks like this:

> > Select the datatype for this constant from the Type drop-down list and enter the value in the Value edit box.

> - Function to add a function. If you click the Function radio button, the Predicate Editor looks like this:

> > Click the Edit button to use the Expression Builder to construct a function to use in the predicate of your SQL Criterion. For more information about the Expression Builder, see “Using the Expression Builder.”

> Step 4: Set a value left side of the predicate and, when necessary, the right side of the predicate. If the right side of the predicate does not require a value of some sort, the Criteria Builder will not let you enter one.

> Step 5: Click Apply.

> Step 6: When you have created both a Left Expression and a Right Expression in the Predicate Editor, click Apply to add the criterion to the tree view at the top of the dialog box.

> The criteria clause displays in the Criteria tree.

> You can create complex criteria by joining other criteria with this one. To join criteria with this one, select the criteria in the Criteria tree and click:

> - Delete to remove the selected criterion.
> - AND to create a new criterion that must also be true.
> - OR to create a new criterion that can be true instead of the selected criterion.
> - NOT to establish negative criterion.

> If you join a criterion to the one you just completed, you build the expression the same way, using the Expression Editors panel and the Predicate Editor panel. You can create complex, nested criteria by judicious use of the AND and OR buttons.

> Once you have created the complete criteria you want, click OK to add it to your transformation.

### Using the Expression Builder

> The Transformation Editor’s Expression Builder offers you a quick, graphical means to build expressions in your transformations. This Expression Builder lets you create:

> - Attributes by selecting an attribute.
> - Constants by selecting the datatype and value.
> - Functions from both the standard MetaMatrix SQL functions and your enterprise’s custom user-defined functions. If you select a function before you launch the Expression Builder, you can use the Expression Builder to edit the selected function; otherwise, you can create a new function from scratch.

> This procedure provides an example of building a function using the Expression Builder.

> To use the Expression Builder:

> Step 1: In the Transformation Editor, click the location where you want to insert the function.

> Step 2: Click the Expression Builder button, . The SQL Expression Builder displays.
> The two tabs at the top, Tree View and SQL View, show the current contents of the expression you have built. To build an expression, you must specify the type of expression you want to build and populate it. In most cases, you will use the Expression Builder to construct a complex expression.

> Step 3: Click the Function radio button to add a function.

> NOTE: You can simply add constants and attributes as expressions by themselves using the Attritute or Constant radio buttons; however, the Expression Editor is most useful for functions.

> Step 4: The Expression Editor displays the Function editor:
> From the Category drop-down list, choose the type of function you want to add. By default, the MetaMatrix System offers the following categories:

> - Conversion for functions that convert one datatype into another.
> - Datetime for functions that handle date or time information.
> - Miscellaneous for other functions.
> - Numeric for mathematic and other numeric functions.
> - String for string manipulation functions.

> NOTE: Any additional categories represent those containing user-defined functions your site has created.

> Step 5: From the Function drop-down list, select the function you want. The table beneath the drop-down lists displays the number of arguments required for this function.

> Step 6: Click Apply.

> Step 7: Your function displays in the tree at the top. Subnodes display for each argument you need to set for this function.
> You need to set an attribute or constant value for each subnode in the tree to specify the arguments this function needs. You can also nest another function in the tree using the Function editor.

> Step 8: Click each subnode in the tree and use the editors at the bottom of the dialog box to apply an attribute, constant, or function value to it.

> Step 9: When you have added values to all nodes:
> Click OK to add this expression to your query or Cancel to close the dialog box without inserting the expression.
> If the OK button does not enable, you have not added a value to all nodes in the tree.

> You can also nest functions within your expressions by selecting an argument and selecting a function for that argument. The nested function displays in the tree beneath your root function and its arguments display as well. Using the Expression Builder and nested functions, you can create complex logic within your query transformations.

Related Topics

- Create a New VDB
- Transformation Editor Preferences
- Web Service Advisor View
- Getting Started with Metamatrix Dimension

(c) Copyright © 2000-2006 MetaMatrix, Inc. All rights reserved.
Visit http://www.metamatrix.com