Generally, to maintain referential integrity, avoid deleting items from a Domain that Ad Hoc views and reports need to use. Sometimes, removing an item or set from a Domain is unavoidable. For example, you included an item that exposes confidential data to unauthorized users, or the underlying database has changed, making the item invalid.
In a typical scenario, an administrator or data analyst creates a Domain and many end-users create Topics and Ad Hoc views based on it. When deleting Domain items in such a situation, tell users beforehand to modify Ad Hoc views to prevent reports from using the item. Inevitably, a user fails to modify a view as requested, and needs to fix it. The server provides a two methods for fixing the view. The first method is the option of opening the view in the Ad Hoc Editor and removing the deleted items. The second method is editing the Domain to replace the deleted item with a placeholder.
The following procedures assume a user created an Ad Hoc view based on a Domain. The Domain is similar to the one described in Example of Creating a Domain. The Domain exposes the accounts_opportunities table that contains no useful columns and is necessary only to join the accounts and opportunities tables. Unfortunately, some users were confused by the additional items exposed by the Domain, and used the date_modified item in their views. You want to delete the accounts_opportunities table from Sets and Items, so it is no longer exposed to users.
To edit a problematic Domain:
| 1. | Locate the problematic Domain in the repository, right-click, and choose Edit. The Edit Domain page appears. |
| 2. | Click Edit with Domain Designer. |
| 3. | In the Domain Designer, click the Display tab and select the public_accounts_opportunities set in Sets and Items. |
Deletion of Items in Use in Reports from a Domain |
|
| 4. | Click Delete Item. The Confirm dialog box appears, listing all of the Ad Hoc views and reports using that use the item and will be affected. |
Indication of Referential Integrity Problem |
|
| 5. | Click OK in the Confirm dialog box to continue. |
| 6. | Click OK in the Domain Designer. |
A confirmation window appears with a message that the Domain changes may impact user work flows.
Domain Changes Impact Message |
|
| 7. | Click Yes to continue with the modified Domain and then Submit on the Edit Domain page. |
The items have been removed from the Domain. You will need to fix the referential integrity problems by either removing the items from the Ad Hoc view or adding a placeholder.
To fix referential integrity problems by removing items in the Ad Hoc Editor:
| 1. | Locate the Ad Hoc view you want to edit in the repository. |
| 2. | Click the Ad Hoc view name. |
The Missing Data window opens and displays the missing items.
Missing Data Window |
|
| 3. | To remove the missing data from the Ad Hoc view, click Remove Items. |
The Ad Hoc Editor opens with the view loaded. The missing items have been removed from the Data Source Selection and Ad Hoc View panels. Clicking Cancel closes the Ad Hoc Editor.
| 4. | Hover your mouse over |
| 5. | Select Save Ad Hoc View. |
To fix referential integrity problems by adding a placeholder:
| 1. | Open the edited Domain in the Domain Designer. |
| 2. | Click the Calculated Fields tab for the Domain. |
| Because a calculated field is a custom column of a user-selected type, it can serve as a placeholder. The value of the custom column is irrelevant, but it needs to be recognized as a placeholder when it appears in a report. |
Creation of Calculated Field as Placeholder for a Deleted Item |
|
| The ZZ prefix in Name distinguishes the placeholder from other columns in the Domain. The value in Type must match the datatype of the deleted item, in this case a timestamp. For information about calculated field datatypes, see Datatypes. |
| 4. | Click the Display tab. |
| A placeholder item must be located in the placeholder set to mimic the structure of the deleted item, and generally, in exactly the same path of nested sets as the original item. You deleted the accounts_opportunities set, so you need to create a set for the placeholder item. |
| 5. | In Sets and Items, click New Set to create the placeholder set. |
| 6. | In Resources, expand the list of Constants and select ZZdate_modified. |
Calculated Field Set Up as a Placeholder |
|
| 7. | Click |
| 8. | In Sets and Items, select the placeholder set, newSet1. |
| 9. | In Properties, click Edit and change these properties: |
Property | New Value |
ID | accounts_opportunities |
Label | ZZDeletedSet |
Description | Placeholder for deleted set |
| 10. | In Sets and Items, select ZZdate_modified. |
| 11. | In Properties, click Edit and change these properties: |
Property | New Value |
ID | date_modified1 |
Label | ZZDeletedField |
Description | Placeholder for deleted item |
| Distinguishing the placeholders from real items by using the ZZDeletedSet and ZZDeletedField labels discourages their use in building new Ad Hoc views. The IDs, although invisible to users, must be identical to those of the deleted item and set, so the server can open and run the view. |
You can see the modified properties for the placeholder item in “Modification of ID Properties to Match the Deleted Item”.
Modification of ID Properties to Match the Deleted Item |
|
| 12. | Click OK in the Domain Designer and then Submit on the Edit Domain page. |
Alternatively, you can edit the XML design file of a Domain to fix referential integrity problems. The modifications are the same as described in this procedure, but the order is slightly different.
To fix referential integrity problems in the XML design file of a Domain:
| 1. | Instead of deleting the item, first create the constant calculated field. You can do this in the Domain Designer even before exporting the design file. You must be familiar with the design file syntax to create the calculated field with the correct type and expression, as described in Structure of the Design File. |
| 2. | Next, locate the item definition and change its resourceID so that it references the newly created calculated field. This removes the reference to the unwanted column, and replaces it with an item that references the placeholder field. |
Recommended Comments
There are no comments to display.