3. Using the Domain Designer
The Domain Designer is an interactive dialog for specifying all the settings in the design of a Domain. Along the top of the Domain Designer are tabs for various aspects of the design:
Tables – Select all tables whose columns you wish to use in the Domain, either directly or indirectly.
Derived Tables – Enter queries whose results appear as derived tables available in the Domain.
Joins – Define inner and outer joins between all the tables and derived tables.
Calculated Fields – Enter expressions whose results appear as calculated fields.
Filters – Specify conditions on field values to limit the data accessed through the Domain.
Display – Give display properties to various tables and columns to expose them sets and items in the Domain.
Once you have selected the tables on the first tab, you may jump to any tab, but you cannot save your settings until you make a selection on the Display tab.
To navigate between tabs, click the tab name at the top of the dialog or the Next and Back buttons at the bottom.
The Cancel button exits the dialog without saving any of your design settings. For new Domains, no design is saved; when modifying an existing design, it remains unchanged.
The Done button validates your design then saves it in the Domain, replacing any current design.
The tool bar buttons operate on the Domain design in its current state, regardless of which tab is selected.
Check Design - Validates the domain.
Export Design to XML - Exports the Domain design in its current state to an XML file. Use this feature if you wish to edit your Domain design in an external editor, for example to duplicate settings with copy-paste or to enter a complex formula. Exporting the XML from the Domain Designer avoids having to write it from scratch.
Export Bundle Stub - Exports a Java properties file for the labels and descriptions of sets and items in your design. Use this button to create a template for your locale bundles after you have defined your sets and items on the Display tab. It gives you the option of automatically generating keys based on the set and item IDs.
Cancel - Closes the dialog without saving your Domain design and returns to the Data and Design page. Same as clicking Cancel at the bottom of the dialog.
Done - Saves your current selections as the Domain design and returns to the Data and Design page. Same as clicking Done at the bottom of the dialog.
The Tables tab presents all the tables and columns found in the chosen data source so you can select the ones to appear in the Domain. For data bases that support schemas, such as an Oracle RDBMS, you will be prompted to choose one or more schemas, and the Tables tab will present all tables and columns found in those schemas.
Typically, you select the tables that need to be joined, therefore you must understand the logical design of tables in your data source. Additionally, select all tables you wish to reference in your Domain design, even if their columns will not appear directly in the Domain. For example, select the tables containing columns that you will use in a derived table or calculated field.
Click the tree icons beside the table names to inspect the columns of a table. However, only whole tables may be selected on this tab; column-level selections are made on the Display tab.
Double-click or drag a table name in the Choose tables list to move it to the Selected tables list. Alternatively, single-click a table name and use the arrow button between the list.
To remove a table you do not want, double-click it or drag it out of the Selected tables list. You can also click the double-arrow button to clear the list of Selected tables.
The Inspect new tables and automatically generate option creates joins only if your database has been configured with referential constraints (foreign keys). Otherwise, selecting it has no effect. If applicable, the generated joins will appear on the Join tab.
The Data Source field at the top shows the name of the data source for your Domain.
Click Manage to change the chosen data source.
The Tables tab does not detect changes to your database tables and columns in real-time. To update a Domain after making changes to your database structure, click Done to close the Domain Designer, then launch it again:
New tables and new columns appear in left-hand list; new columns appear under their table name. Because the Tables tab selects entire tables, you must add all new columns together by moving their table name to the right-hand list.
For deleted tables and columns that were selected in the Domain, you are prompted to remove them from the right-hand list. When you accept, the Domain Designer removes those columns or tables from the Tables tab. However, if you had selected the dropped columns for display, you must manually remove them from the Display tab, otherwise the Domain will have a validation error.
Domains assign aliases to data sources. The Manage Data Source dialog on the Tables tab lets you set aliases, as well as change the data source. The default alias for a data source is the display name of its repository object.
Figure 9.3. Manage Data Source Dialog
You can edit the alias by double-clicking the alias name and typing a new alias. Usually this is not necessary because the data source alias is not displayed to Domain users.
If your data source supports schemas, such as an Oracle RDBMS, click Select Schemas to choose among all available schemas in the data source. All the tables in the schemas you choose will appear in the left-hand list on the Tables tab.
The Replace button lets you select a different data source to be associated with the alias. Use this button to replace the data source with an equivalent one. For example, if your database changes servers, you need to create a new data source object and use it to replace the previous one in your Domain.
To change the data source and alias, click Delete to remove the current one, and then click Add to open a dialog to select an available data source from the repository.
Click OK to apply your changes, or click Cancel to return to the Tables Tab without changing the data source.
When you change the data source, all previous settings in the Domain Designer that do not conform to the new data source will be lost.
A derived table in a Domain is defined by a custom query and a selection of the columns in the result. Because Domains are based on JDBC data sources, the query is written in SQL. The result of an SQL query is a table whose structure and contents are determined by the clauses in the query. For example, the WHERE clause may contain conditions that determine the rows of the derived table. Once a derived table is defined, the columns selected from the result are available for use in the Domain design.
Click New to begin defining a derived table. Type a name for your table in the Query ID field.
Enter a valid SQL (JDBC) query in the Query field. Your query may refer to any table or column available in the data source shown. The syntax for a valid SQL query does not include a closing semi-colon (;).
Expand the tree icons of the tables in the left-hand list to view column names you may wish to use, but do not click them.
When your query is complete, click Run to test it and see the list of columns in the result.
By default, all columns in the result are selected. Use control-click to change the selection. If you only want a few columns out of many, it is easier to specify the column names in the SELECT clause of the query.
Click Create to add the derived table with the current selection of columns to the left-hand list of tables and columns. A distinctive icon identifies it as a derived table.
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 your Domain depends on your business needs.
To create a join between two tables, each 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. Analytics supports the four most common join types, all based on equality between values in each column:
Join Inner - The result will contain only rows where the values in the chosen columns are equal.
Join Left Outer - The result will contain all the rows of the left-hand table, paired with a row of the right-hand table when the values in the chosen columns are equal and with blanks otherwise.
Join Right Outer - The result will contain all the rows of the right-hand table, paired with a row of the left-hand table when the values in the chosen columns are equal, and with blanks otherwise.
Full Outer Join - This join type cannot be created directly, it can only be selected by editing an existing join. The result will contain all rows from both tables, paired when the joined columns are equal, and filled with blanks otherwise.
On the Joins tab, the list of selected and derived tables is duplicated in two lists, one to choose the left table, the other to choose the right table. Expand a table in each list, select a column in each table with the same logical meaning and compatible formats, then click one of the join icons. The new join appears below in the list of joins. 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.
Copy Table - Copies the selected table and gives it a name with sequential numbering. The copy appears in both lists.
Change Table ID - Lets you edit 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 Selected Table - 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.
Below the lists of left and right tables, the All Joins tab summarizes the joins you have defined. If you have many joins in your domain, use the Joins on Selected Table tab to see only those defined on the table currently selected in the left-hand list. When you select a join on either tab, you can change the join type by selecting another type in the drop-down list. This list include the Full Outer join type that cannot be created directly.
Delete Join - Removes the selected join definition from the list of joins and from the Domain design.
The result of the Joins tab is a number of join trees that appear on subsequent tabs. For example, if you join tables A and B, B and C, then join tables D and E, there will be 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.
3.6. Calculated Fields Tab
A calculated field is defined by an expression that computes a value based on the values of other columns. In order for the values to be coherent, all columns that appear in the expression for a calculated field must be from the same join tree.
Click Create Field to define a calculated field.
Enter a short name for your calculated field. This name becomes the ID of the field in the Domain; you can later give it a descriptive label and full description.
Select a data type for your calculated field. The expression you write must compute a value of this type. For most expressions this is also the data type of the columns you wish to use in the expression. Therefore, you need to be familiar with the data types of the various columns in your data source.
Enter the expression to compute the value of your calculated field. The expression uses the Domain Expression Language fully described in the section titled "DomEL Syntax." 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. Do not insert column reference from unjoined trees, because the calculated fields editor does not validate expressions as they are written.
Figure 9.4. Domain Designer - Calculated Fields Editor
Click OK to save your new 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. To clear the calculated field editor without saving, click Cancel.
Once validated, 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 the list of columns.
An expression that does not use any columns will have 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.
Calculated fields may be used to compute other calculated fields. Double-click the calculated field name to insert a reference to it into an expression.
To view, edit, or delete the definition of a calculated field, cancel any input in the calculated field editor, then click the name of the field in the left-hand list.
A filter on one or more columns reduces data that is not needed or not 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 Analytics.
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 them 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 will still be active and only data that satisfies all defined filters will appear to report users. For example, you may filter data to select a single country, in which case it does not make sense for the column to appear in a report. However, you should clearly document such data restrictions in the description of your Domain, so that users understand what data is accessible through the Domain.
Select a column in the left-hand list and then click Create Condition to begin defining a filter on that column. Alternatively, you can click the arrow next to the column name or double-click the column name.
To define a filter that compares two columns of the same data type, select the second column with Control-click, and then click Create Condition. This button is enabled only when two columns of the same type are selected.
In the Condition Editor, the choice of comparison operators depends on the data type of the column. For example, string types offer a choice of string search operators and date types have a choice of time comparison operators. The filter value depends on the data type and the comparison operator. For example, if you select a date column with the “is between” operator, the condition editor will display 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 dialog displays a list of all existing values for the chosen column retrieved in real-time from your database. If there are more than 50 values to display, use the search controls to the left and click to narrow your list of available values. For multiple value matching, double-click the available values to select them. You can perform multiple searches and select values from each list of results.
After choosing the comparison operator and filter value, click OK to define your filter. To clear the condition editor without saving a filter, click Cancel.
The list of Current Conditions shows all the filters you have defined. The note at the bottom of the current conditions reminds you that “data rows much match all conditions.” In other words, the overall filter applied to your data is the logical AND of all conditions you have defined.
Click a row of Current Conditions to edit it again in the Condition Editor. Click OK to save your changes. After selecting a row, you may also click Delete Condition to remove it from the list.
The Display tab lets you specify which columns and calculated fields are exposed through the Domain and how they will appear. Typically, only columns that are useful in reports or for further filtering should be selected, and unneeded columns should be omitted to simplify the report creation interface. On the Display tab, you also define display properties for each chosen column, such as a label and description, to further help report creators.
A column along with its display properties is called an item in the Domain. A set is a grouping of items independent of the tables in which the columns originate, however, all items in a set must correspond to columns in the same join tree. Sets are optional; you can create a list of items outside of any sets.
The Join Tree list at the left of the tab contains all the join trees and unjoined tables, including any calculated fields in their defined locations. The list of Sets and Items to the right shows the sets and items that will appear to report creators. You may include any combination of joined and unjoined items in the list of Sets and Items, but when creating a report, users will only be able to include items that originate in the same join tree.
If you only want to display a few of the columns from the join tree, start by creating sets in the right-hand list. Then add items chosen among the columns of the left-hand join tree.
Add New Set - Creates a new set or subset in the list of sets and items. If no set is selected it creates a top-level set; if a set or item within a set is selected, it creates a subset.
Change Label - Lets you edit the name of the selected set or item. The label name is also updated in the list of properties.
Add Selected - Make a selection in the join tree and click this icon: tables are added as sets, and columns are added as items outside of any set. You can also drag-and-drop tables and columns from the join tree to the list of sets and items.
Add to Set - Select a destination set in the right-hand list, then make a selection in the join tree and click this icon: tables are added as subsets, and columns are added as items within the destination set. You can also expand sets in the right-hand list and drag-and-drop tables and columns to the destination set.
If you want to display all or most of your tables and columns in the join tree, double-click the join tree name or drag-and-drop it to the list of sets and items. All tables are created as sets, and the columns they contain are created as items within the sets. Then remove any unwanted sets or items individually from the right-hand list as follows:
Delete (above Sets and Items) - (above Sets and Items) – Removes the selected set or item from the left-hand list. You can also double-click the object or drag-and-drop it to a blank area of the join tree list. Removed items automatically reappear in the join tree or table where they originated in the left-hand list.
The left-hand list also offers an alternative view mode: click Table List just below the icons. The table list displays the list of unjoined tables and derived tables, as they appear on the Join tab. It also activates the following icons:
Add All (active only if the right-hand list is empty) - Creates all the tables as sets and all the columns they contain as items in the right-hand list. You can then remove any unwanted sets or items from the right-hand list as described above.
Change Table ID - Lets you edit 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 Selected (above Table List) - (above Table List) – Removes the currently selected table from the left-hand list and from the Domain altogether. The table is removed from the Joins tab, and any joins with this table are deleted. Use carefully.
After your list of sets, subsets, and items is defined, refine your Domain display by reordering, renaming, and providing descriptions for them:
To change the order of sets or items, select the objects and use the arrow buttons to the right of the list to move them up or down. You can reorder items within a set and sets within the list, but items and subsets may not be moved into other sets. To achieve this, remove the item or subset and add it again in the new set. Subsets always appear after the items in a set.
To rename a set or item, select it then click the Change Label icon or double-click its label in the properties table.
To add a description to a set or item, select it, and then double-click its description in the properties table. Set and item descriptions appear as tooltips in the Ad Hoc Editor and help report creators understand their purpose.
The properties table is located on the Display tab to the right of the list of sets and items. It lets you view and edit the display properties for the currently selected set or item. Display properties determine how the sets and items will appear to users of the Domain. When the left-hand list displays the Table List, the properties of tables and columns can also be viewed.
The following properties are available, depending on the selected object:
Table – The name of the table and the name of the data source (view-only)
Field (column) – The name of its table in the data source and its Java data type (view-only)
Set – The label, description, and internationalization keys (view and edit)
Item – The label, description, internationalization keys, and default format and summary properties (view and edit)
Table and field properties appear only when the Table List view is enabled. All table and field properties are read-only. For sets and items, properties shown in red are read-only. All others you can double-click to edit, or click ... to edit the description.
The label and description help report creators understand the data represented by the set or item. The internationalization keys are the property names of internationalized strings in locale bundles. The data format and summary properties determine how the item will appear by default in a report. The following table describes each of the properties in detail.
| Property||Appears On ||Description |
|ID||Table, Field Set, Item||An identifier used within the Domain. Table and field IDs are based on the names in the data source, but you may change the ID of a table as long as it remains unique. Set and item IDs are a separate namespace in which each ID must be unique, although based on table and field IDs by default. When creating a Domain, you may change any set or item ID as long as it is unique among all set and item IDs. When editing a Domain that has been used to create Topics and reports, you should not change any IDs. For more information, see the section titled "Maintaining Referential Integrity."|
|Data Source||Table, Field||Alias of the data source for the selected field or table.|
|Source Table||Table, Field||Name of the selected table or of the field's table in the data source. Does not change when the ID property of a table is modified.|
|Type||Field||Java type of the selected field.|
|Label||Set, Item||User-friendly name displayed in the Choose Ad Hoc Data wizard and the Ad Hoc Editor.|
|Description||Set, Item||User-friendly description displayed as tooltip on the label in the Ad Hoc Editor. The description helps the report creator understand the data represented by this set or item.|
|Label ID||Set, Item||Internationalization key for the label property; locale bundles associate this key with the localized text for the set or item label.|
|Descr. ID||Set, Item||Internationalization key for the description property; locale bundles associate this key with the localized text for the set or item description.|
|Source||Item||References the Domain names of the table and field associated with this item; the syntax is |
|Data Format||Item||Default data format for the item when used in a report. The data format is a numerical or date format, for example ($1,234) or 01/01/2009. Items with string values do not have a data format.|
|Summary||Item||Default summary function for the item when used in a report. Numerical items have functions such as sum or average, all others let you count distinct values or count all values.|
Labels and descriptions may contain any characters, but the ID property value must be alphanumeric and not start with a digit.
If you want to edit the properties for a large number of sets and items, the following buttons in the tool bar can help:
Export Design to XML - Lets you edit the properties of your sets and items within an XML file. For a large number of sets and items, this could be faster than using the properties table on the Display tab.
Export Bundle Stub - Generates the internationalization keys if needed and saves them to a file that serves as a template for your locale bundles. Make sure your set and item IDs are finalized because they are used to generate the keys. The generated keys are added to your Domain design and appear in the table of properties.
3.10. Domain Design Validation
The Domain Designer must ensure that all tables and columns are consistent with the data source. 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 will detect any inconsistencies in Domain designs from uploaded files.
In certain cases, when navigating from tab to tab. This will detect problems on the tab where they occur.
If you change the data source.
When you export the design file.
When you click the Validate tool bar icon.
When you click Done to exit the Domain Designer.
Validation performs the following steps:
Verify that all tables and columns exist in the data source.
In each defined set, verify that all items originate in the same join tree.
Verify that all items reference existing columns.
Verify that derived tables have valid SQL queries.
Unless you clicked the Validate tool bar icon, there is no messages when validation succeeds. However, a message appears when validation fails to help you correct the error.