Open topic with navigation

Using the Domain Designer

You use the Domain Designer to define all the components of a Domain. From the bottom of the Add New Domain or Edit Domain pages, click Create in Domain Designer or Edit in Domain Designer, respectively to open the Domain Designer console. Along the top of the Domain Designer console are tabs for configuring various aspects of the design:

•    Tables tab – Select all tables whose columns you want to use in the Domain, either directly or indirectly.

•    Derived Tables tab – Enter queries whose results appear as derived tables available in the Domain.

•    Joins tab – Define inner and outer joins between all the tables and derived tables.

•    Calculated Fields tab – Enter expressions whose results appear as calculated fields.

•    Pre-filters tab – Specify conditions on field values to limit the data accessed through the Domain.

•    Display tab – Organize the visual aspects of the Domain and change the display properties of tables, columns, sets, and items exposed to Domain users.

From the Tables tab, you can navigate to any other tab. To navigate between tabs, click the tab name at the top of the Domain Designer. Before you can save the design, you must choose which sets and items to expose to users of the Domain. The OK button on the console validates the design and saves it in the location you specified earlier. For more information, see section Domain Validation. After saving a Domain, you can modify it using the Domain Designer.

The Cancel button on the console exits the Domain Designer without saving any of the design settings. For new Domains, no design is saved; when modifying an existing design, it remains unchanged.

Tables Tab

The Tables tab contains two panels:

•    Data Source – Shows the tables and columns in the data source or schema you chose in the Add New Domain page.

•    Selected Tables – Shows all tables and columns that you use to design the Domain. Initially, this panel is blank.

Typically, you move the following tables from Data Source to Selected Tables:

•    Tables that need to be joined

•    Tables that you want to reference in the Domain design, even if their columns do not appear directly in the Domain.

For example, select the tables containing columns that you want to use in a derived table or calculated field.

An understanding of the logical design of tables in the data source is critical to selecting which tables need to be joined.

To select tables for use in designing the Domain, first expand the table icons beside the table names to inspect the columns of a table. Double-click or drag a table name in the Data Source panel to move it to the Selected Tables panel. Alternatively, use or to move a table from one panel to the other.

To remove a table, double-click or drag it out of Selected Tables. You can also click to clear Selected Tables.

The Inspect new tables and automatically generate joins check box at the bottom of Selected Tables creates joins only if the database has been configured with referential constraints (foreign keys). Otherwise, selecting it has no effect.

The Tables tab does not detect changes to the database tables and columns in real-time. To update a Domain after making changes to the database structure, click OK to close the Domain Designer, then launch it again.

Keep the following points in mind regarding Domain updates:

•    New tables and columns appear in the Data Source panel; new columns appear under their table name.

•    To add a new column to the Domain, move its table to Selected Tables.

The Tables tab works only with entire tables.

•    From Selected Tables, respond affirmatively to the prompt to remove tables and columns deleted from the database.

The Domain Designer removes those columns or tables from the Tables tab.

Manage Data Source Dialog Box

The name of the data source that you choose for the Domain appears at the top of the Data Source panel on the Tables tab. Click the name of the data source to make changes to it. The Manage Data Source dialog box appears:

As shown in this figure, the default name for a data source is the display name of its repository object. You can edit the name by typing a new one. Usually, this is not necessary because Domain users do not see the name of the data source. However, if you select a new data source for the Domain, the name in the design does not change automatically. In this situation, you typically change the name in the design to match the new data source.

If the database changes servers, you need to create a new data source object and use it to replace the previous one in the Domain. To change the data source, select a new one, and click OK to apply the changes.

If the database supports schemas, such as PostgreSQL or Oracle RDBMS, click Select Schemas at the bottom of the Manage Data Source dialog box to choose among available schemas in the data source. The tables in the schemas that you choose appear in the Data Sources panel.

Derived Tables Tab

You create a derived table in a Domain by first building a custom query using the objects you selected on the Tables tab. Because Domains are based on JDBC and JNDI data sources, you write the query in SQL. You run the query, and then select columns from the query result for use in the Domain design.

To define a derived table:

1.     Type a name for the table in the Query ID field.

2.     Enter a valid SQL query in Query. The query may refer to any table or column in Available objects. Only queries that begin with the select statement are allowed. Do not include a closing semi-colon (;).

3.     When the query is complete, click Run Query to test it and choose the list of columns in the result.

4.     By default, all columns in the result are selected. Use Ctrl-click to change the selection. If you want only a few columns out of many, it is easier to specify the column names in the SELECT clause of the query.

5.     Click Save Table to add the derived table to Available objects. A distinctive icon, identifies it as a derived table.

Joins Tab

Joins create associations between tables so that their rows may be presented together in the same report. Multiple joins associate columns across many tables to create powerful data visualizations when used in reports. The number of tables and joins in the Domain depends on your business needs, as described in section Domain Use Cases. The server supports the four most common join types, all based on equality between values in each column.

To join tables for the Domain, use the Joins tab. On the Joins tab, the list of selected and derived tables is duplicated in the Left Table and Right Table panels. Expand tables in both panels, select a column in each table having the same logical meaning and compatible formats, then click a join icon:

Join Inner – The result contains only rows where the values in the chosen columns are equal. In the support case example in section Example of Creating a Domain, the result of an inner join contains only support cases that have been assigned to a support engineer.

Join Left Outer – The result contains all the rows of the Left Table, paired with a row of the Right Table when the values in the chosen columns are equal or contain blanks. If the support cases are in the Left Table of the example, the result of a left outer join contains all support cases even if they do not have an assigned engineer.

Join Right Outer – The result contains all the rows of the Right Table, paired with a row of the Left Table when the values in the chosen columns are equal or contain blanks. If the users are in the Right Table of the example, the result of a right outer join contains all the users and the support cases assigned to each engineer, if there are any. In this example, a user might also appear several times if different support cases refer to the same support engineer user ID.

Full Outer Join – The result contains all rows from both tables, paired when the joined columns are equal, and filled with blanks otherwise.

The new join appears in All Joins | Joins on Selected Table panel.

In order to create a join between two tables, each table must have a column with the same meaning. For example, a table with data for support cases has a column for the assigned engineer user ID that can be joined with the table of user data that has a user ID column.

In some cases, you may need to duplicate a table in order to join it several times without creating a circular join, or in order to join it to itself. You can also duplicate a table so it may be joined with different tables for different uses. Click the following icons above the Right Table to make a copy, change the name, or delete a table:

•    Copy – Copies the selected table and gives it a name with sequential numbering. The copy appears in both Left Table and Right Table.

•    Change ID – Changes the name of the selected table. The new name becomes the ID of the table throughout the Domain, and is updated everywhere it appears in the Domain Designer.

•    Delete – Removes the table from both lists. If the deleted table was the only instance of a table, removing it on the Joins tab also removes it from the list of selected tables on the Tables tab.

You use the All Joins | Joins on Selected Table panel to see the defined joins, to remove a join, and to change the join type:

•    All Joins – Lists all joins defined for the Domain.

•    Joins on Selected Table – Lists only joins defined on the table you select, simplifying the view you have of many joins.

•    Join Type – Changes the type of join.

•    Remove – Removes the selected join definition from the list of joins and from the Domain design.

After creating joins, one or more join trees appear on the Calculated, Pre-Filters and Display tabs. For example, if you join tables A and B, B and C, then join tables D and E, the result is two join trees. Columns of table A and table C may appear in the same report because their tables belong to the same join tree. Tables A and D are said to be unjoined; their columns may not be compared or appear in the same report. Tables that are not joined appear individually along with the join trees.

Calculated Fields Tab

You create a calculated field for the Domain by writing an expression that computes a value based on the data in another column or columns. In order for the value to be coherent, all columns that appear in the expression for a calculated field must be from the same join tree.

To create a calculated field:

1.     Select the Calculated Fields tab to begin defining a new calculated field.

2.     In Field Name, enter a short name for the calculated field. This name becomes the ID of the field in the Domain.

3.     In Type, select a datatype for the calculated field. The expression you write must return a value of this type.

4.     Enter an expression to compute the value of the calculated field.

To insert a reference to the value of another column:

Expand the join-tree to find its table and double-click the column name.

The column name appears in the expression at the cursor, qualified by its table name.

5.     Click Save Field to save the new calculated field. To clear the calculated field editor without saving, click Cancel.

After saving the calculated field, the Domain Designer validates the expression and warns you of any errors at this time. If there are errors, use the indications of the error message to help correct the expression. After validation, a calculated field appears in the table or join tree whose columns are used in the expression.

Calculated fields have a distinctive icon, , for easy recognition in Available Fields.

An expression that does not use any columns has a constant value. For example, you might create an integer field named Count that has the value 1 and later has a default summary function to count all occurrences. Constant fields are independent of join trees and automatically appear in a set called Constants.

To view, edit, or delete the definition of a calculated field:

1.     Cancel any input in the calculated field editor, then click the name of the field in Available Fields.

2.     Modify its name, its type, or its expression.

3.     Click OK to save the new definition, or click Cancel if you just viewed the field definition.

Click Delete Field to remove the calculated field from the Domain design.

Pre-filters Tab

A filter on one or more columns reduces data that is not needed or wanted in reports based on the Domain. For example, financial reports for the current fiscal year may need data from the previous fiscal year for comparison, but nothing earlier. It is always good practice to filter out irrelevant data to reduce the size of query results and processing time within the server.

Also, reports based directly on the Domain can define their own filters. Putting often-used filters in the Domain design avoids the need for each user to define filters independently and also reduces the chance for errors.

You can define a filter on a column that you do not plan to expose in the Domain. The filter remains active and only data that satisfies all defined filters appears to report users. For example, you can filter data to select a single country, in which case it doesn’t make sense for the column to appear in a report. However, you should clearly document such data restrictions in the description of the Domain, so that users understand what data is accessible through the Domain.

To define a filter:

1.     Double-click a column in Fields.

Alternatively, you can drag a column to the Filters panel. The column appears in the Filters panel with a list of conditional operators you can apply to that column.

2.     Choose the comparison operator and filter value from the drop-down.

In the Filters panel, the choice of comparison operators depends on the datatype of the column. For example, string types offer a choice of string search operators and date types offer time comparison operators. The filter value depends on the datatype and the comparison operator.

For example, if you select a date column with the is between operator, the Filters panel displays two calendar icons for specifying a date range:

Text columns have both substring comparison operators such as starts with or contains and whole string matching such as equals or is one of. When you select a whole string matching operator, the panel displays a list of all existing values for the chosen column, retrieved in real-time from the database. If there are more than 50 values to display, use the search controls to the left and click to narrow the list of available values. For multiple value matching, double-click the available values to select them. You may perform multiple searches and select values from each list of results.

3.     Click OK to define the filter. To clear the condition editor without saving a filter, click Cancel.

Filters shows all the filters you have defined. The overall filter applied to the data is the logical AND of all conditions you defined.

In Filters, click Change to modify a filter you defined. Click OK to save the changes. After selecting a row, you can click the Remove button to remove it from the list.

Display Tab

On the Display tab, you specify which columns and calculated fields to expose through the Domain and how they appear. Typically, you select only columns that are useful in building or filtering reports, and omit other columns for simplicity. You also can modify display properties, such as the label and description of tables and sets and items for each specified column. Using meaningful labels and descriptions to help users of the Domain.

The Display tab contains three panels:

•    Resources – Lists tables or join trees, depending on the view you choose.

•    Sets and Items – Lists resources that appear to report creators who use the Domain.

•    Properties – Displays and modifies properties of selected sets and item.

In the Resources panel you can view resources as either a Join Tree or a Table List:

•    Join Tree – Displays joined, unjoined, and calculated tables for this Domain. Joined tables appear as join trees.

•    Table List – Displays the selected and calculated tables for this Domain.

In the Table List view, you can use the Delete Table button to remove the selected table from the Resources panel and from the Domain design. The table no longer appears on the Joins tab, and any joins to the table are deleted. Use carefully.

To specify which columns and calculated fields are exposed to users of the Domain, you move resources from the Resources panel to the Sets and Items panel:

•    Set – A group of items independent of the tables in which the columns originate.

•    Item – A column or calculated field, along with its display properties, that you want to appear in the Domain.

All items in a set correspond to columns in a join tree. Sets and items appear to report creators who use the Domain. Sets are optional. You can create a list of items outside of any sets. If you want to display only a few of the columns from the join tree, first create a new set in Sets and Items. Next, add items from the join tree in Resources.

To move a resource to Sets and Items, you can drag it from Resources and drop it in Sets and Items, or you can use one of the following buttons:

Add to Sets – Select a resource in Resources and click this icon to add it to Sets and Items. Tables are added as sets, and columns are added as items outside of any set.

Add to Selected Set – Select a destination set in Sets and Items, select a resource in Resources, and click this icon. Tables are added to Sets and Items as subsets, and columns are added as items within the destination set. You can also expand sets in Sets and Items and drag tables and columns from Resources to the destination set.

To create new sets or delete items, use the New Set and Delete buttons:

New Set – Creates a new set or subset in Sets and Items. If a set is not selected, clicking New Set creates a top-level set; otherwise, clicking New Set creates a subset.

Delete – Removes the selected set or item from Sets and Items. You can also double-click the object or drag and drop it in a blank area of Resources. Items removed from Sets and Items automatically reappear in Resources.

To move most of the tables and columns in Resources to Sets and Items:

1.     Double-click the join tree name in Resources, or drag-and-drop it from Resources to Sets and Items. All tables are created as sets, and the columns are created as items within the sets.

2.     Remove any unwanted sets or items individually from Sets and Items using the Delete Item button.

To change the order of items within a set in Sets and Items:

1.     Select the item.

2.     Reposition the item using one of the following buttons:

Move the selected set or item to the top.

Move the selected set or item up.

Move the selected set or down.

Move the selected set or item to the bottom.

You can also use these buttons to reposition sets within Sets and Items.

To move items and subsets into other sets:

1.     In Sets and Items, select the item or subset you want to move.

2.     Click Delete Item.

3.     In Sets and Items, select the destination set.

4.     In Resources, select the item or subset deleted from Sets and Items.

5.     Click to add the item or subset from Resources to the destination set.

Subsets always appear after the items in a set.

The Properties Panel

In the Properties panel on the Display tab, you can view, edit, and save properties of a set or item. You select an item in the Sets and Items panel, and click the Edit button. For example, open the Simple Domain example in the repository for editing in the Domain Designer. On the Display tab, select the Opportunities: Amount item, then click the Edit button in Properties. Set the Data Format and Summary Function, as shown in the previous figure. Click the Save button. The property settings determine how sets and items appear to users of the Domain.

The following table shows the properties and possible actions you can perform on the, depending on what you select in the other panels. For example, if you select the ID of a table in the Resources panel, you can view and edit the ID.

Labels and descriptions are visible to users of the Domain. Descriptions of sets and items appear as tooltips in the Ad Hoc Editor and help report creators understand their purpose. The internationalization keys are the property names of internationalized strings in locale bundles. Selecting an item in Sets and Items displays the internationalization keys, if there are any, for the item under Bundle Keys.

After defining the list of sets, subsets and items, refine the way the Domain appears to users by renaming and providing descriptions for sets, subsets, and items. Click Edit in the Properties panel to modify properties. The following table describes each of the properties in detail:

From the Export menu you can choose to export the design or locale bundle for the Domain, as described in the next section.

Designer Tool Bar

The tool bar buttons operate on the Domain design in its current state, regardless of which tab is selected.

•    Check Design – Validates the Domain as described in the next section.

•    Export Design – Exports the XML design for the Domain. Use this menu item if you want to edit the XML in an external editor, for example to duplicate settings with copy-paste or to enter a complex formula. Exporting the XML design from the Domain Designer avoids having to write it from scratch. For more information, see section The Domain Design File.

•    Export Bundle – Generates the internationalization keys and saves them to a Java properties file that serves as a template for the locale bundles. Use this menu item to create a template for the locale bundles after you have defined the sets and items on the Display tab. You can select a check box to automatically generate keys based on the set and item IDs. This option is visible only after you define localized sets or items. For more information, see section Locale Bundles.

You can save time editing the properties for a large number of sets and items by using the exported XML file instead of using the properties table on the Display tab. For more information about saving and uploading XML, see section The Domain Design File.

Before exporting a bundle, make sure the set and item IDs are finalized because they are used to generate the keys. The generated keys are added to the Domain design and appear in the table of properties. For more information about the internationalization keys, see section Locale Bundles.

Domain Validation

The validation of a Domain ensures that all of its components are consistent. The Domain Designer checks the syntax of files when they are uploaded, but overall consistency must be checked when saving a new or edited Domain.

When you click Check Design, the Domain Designer performs the following validation.

1.     Verifies that the tables and columns of the Domain design exist in the data source.

2.     Verifies that all items in each defined set originate in the same join tree.

3.     Verifies that all items reference existing columns.

4.     Verifies that derived tables have valid SQL queries.

5.     If a security file has been uploaded, verifies that all items and sets in the security file exist in the Domain design.

If validation fails, the Domain Designer remains open and a message appears to help you correct the error. Make the necessary changes to the settings and save again. If the settings are in the uploaded files, edit the files and upload them again.

Validation is important because the Domain design may include derived table queries and calculated field expressions entered by the user.

Validation occurs at the following times:

•    When opening the Domain Designer. This check detects any inconsistencies in Domain designs from uploaded files.

•    When navigating from tab to tab under certain circumstances. This check detects problems on the tab where they occur.

•    When changing the data source.

•    When exporting the Domain design file.

•    When clicking Check Design in the Domain Designer.

•    When clicking OK to exit the Domain Designer.

Unless you click Check Design, no message appears when validation succeeds. When validation fails, however, a message appears to help you correct the error.

Open topic with navigation