Chapter 15: Almost There

---

This is a bit of a loose-ends chapter in which you finalize and quality-assure the universe, prepare universe documentation, and monitor system usage. If you have had close conversations with the business users and parsed objects as you built them, you may find that some of these steps are perfunctory. If, on the other hand, you built the universe in isolation and didn’t test along the way, you will find this is an intensive time to recycle the universe.

Universe Integrity

As you have built the universe, you have done a number of integrity checks. In modifying the SQL of individual objects, you should have Parsed each one. The Parse validates that you have entered the SQL correctly. In building joins, you have detected cardinalities and loops to create contexts and aliases.

The overall universe integrity check becomes a final chance to catch anything you may have missed in earlier stages. If you are using linked universes, it checks the integrity in both the kernel and derived universes. It also will determine if anything has changed in the data source structure that would make an existing object or join invalid.

To check the integrity of a universe, select the pull-down menu Tools | Check Integrity or select the Check Integrity button from the toolbar.

Designer presents you with the following dialog box:

At this point, you can choose Check All to have Designer perform all possible checks, or you can choose to check the integrity of individual components. Table 15-1 explains the purpose of each check.

Table 15-1: Settings to Check the Integrity of Universe Components

Option	Purpose
Check All	Checks all components within the universe.
Check Universe Structure	Compares the structure information in the Structure pane with the tables and columns within the RDBMS. If there is a difference between the two, Designer will give a warning. Tables within the Table Browser that have been removed or renamed but that are not in the Structure pane will not cause an error. If you regularly refresh your structure with View | Refresh Structure, this check should return no errors.
Parse Objects	Similar to the Parse button within the Object definition, this verifies that the SQL SELECT and WHERE syntax is correct and that the TABLE.COLUMN for each object is valid.
Parse Conditions	Similar to Parse Objects but checks predefined conditions only. This does not check the WHERE clause of regular objects.
Check Cardinalities	This will check if the cardinality has been defined for each join. It will not detect the relationship between the tables.
Check For Loops	This will detect any loops and inform you if they were resolved with an alias or context. Designer will list any loops that have not been resolved.
Check For Context	When you start using contexts, you must ensure that all joins defined belong to a context. The one exception is a shortcut join. These do not need to belong to a context but will generate an integrity check error if they are not defined to one; you can ignore this error as it does not cause a problem during query execution.
Quick Parsing	Quick parsing checks the syntax only.
Thorough Parsing	This provides more extensive checking than quick parsing and can be slow to execute on very large universes. For example, it will check that the SQL statement format matches the object type. If the SQL statement includes a date field and the object type is set to character, the check will return an error.

To perform the check, select the desired components and click OK. The following screen shows the results from Check All with Quick Parsing. Designer groups each warning according to the component tested, as listed in Table 15-1. Next to each component, Designer will display an OK or an Error count. Initially, any warnings and errors are collapsed. To see the full error, click the + sign next to the component.

Under Structure, you have one divergence or potential problem. The table ARTICLE_COLOR_LOOKUP exists in the universe structure but is not joined to anything. Designer does not list this as an error, because it may be valid for one table to exist on its own. If there are no objects associated with this table, you can delete the table from the Structure pane. If there are objects that use this table, then you need to add a join from the ARTICLE_COLOR_LOOKUP to the fact table and then include it in the context.

Under Cardinalities, you have one divergence in that no cardinality was defined for the join from PRODUCT_PROMOTION_FACTS to ARTICLE_LOOKUP. Designer does not list this as an absolute error, because cardinality detection is used primarily to propose contexts. The absence of cardinality settings will not create an invalid universe or user error. To add the cardinality, you can double-click the join statement and Designer will present you with the Edit Join dialog box. Note that the cardinality has not been defined:

Under Check loop, Designer lists the status as OK. There are no errors within this component. You can expand this folder to display any loops and the contexts or aliases used to correct them.

Under the Context check, you have a genuine error that can cause errors when a user executes a query. There is a join between SHOP_FACTS and OUTLET_LOOKUP that does not belong to any context. Once you start using contexts, all joins must belong to at least one context. The exception is shortcut joins (see Chapter 7). By definition, these joins are most often used outside of fact tables and contexts. Therefore, even if they produce an error during the integrity check, they still work correctly, as the shortcut join is used only when a user builds a query that involves only the two tables.

In the following screen, Check All was selected with Thorough Parsing. Note that Designer reported four more errors over the preceding check. Thorough checking may take longer to run for large universes.

In some cases, the results will inform you of an error but not the cause. Time/Year Parse failed because the object type is set to Date although the physical database column is Character. The measure object Number of Weeks failed because it used a SUM on a character field. You must either use COUNT or convert the character field to a numeric field before using the SUM function.

Checking as You Go

With the preceding two object errors, had you clicked Parse during the object modification, you also would have received an error. Hence a very important lesson: unless you like solving many problems at once, check the universe integrity as you make modifications. Designer allows you to set options that will force you to adhere to this practice. These options are global for all universes you develop from this particular workstation. From the pull-down menu, select Tools | Options and the General tab.

In the Integrity box, set the option Automatic parse upon definition to have Designer check the SQL syntax whenever you add or modify an object or join. With this setting, you will not be able to add objects or joins that contain errors. Designer will not warn you about isolated joins when you create them, but it will prevent you from adding joins that contain an incorrect SQL statement (for example, if you define a complex join and skip a parenthesis or join operator).

The second setting, Send check integrity warning before export, will prompt you to do an integrity check prior to exporting the universe to the repository. The warning is not particularly intelligent; if you do an integrity check on all components but did not select the Check All option, Designer will still remind you to do an integrity check. With this setting, you cannot export a universe that has unrecoverable errors. For example, class and object names are limited to 35 characters. When you select File | Export, Designer automatically runs the integrity check and will not allow you to export the universe until this error is resolved. Other errors such as isolated joins or SQL errors can still be exported and corrected at a later time. Designer prompts you to check the universe integrity prior to export:

The last setting, Check universe integrity at opening, forces an integrity check as soon as you open the universe. While this is a useful reminder to resolve errors, I personally find it annoying when used in conjunction with the first two settings.

Caution 

If you use either of these last two options, consider setting Quick Parse as the default. Thorough Parsing can be slow on large universes and cannot be interrupted. You can always set Thorough Parsing for checks as you specifically request them.

---