Clarity:Domain schema contains resources that are not in the selected data source for PostgreSQL
search cancel

Clarity:Domain schema contains resources that are not in the selected data source for PostgreSQL

book

Article ID: 193760

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

The domains migrated to PostgreSQL are showing errors when viewed or edited in the Domain Designer. The domains are not invalid or corrupted, they are valid domains. However, a known issue with the Domain Designer prevents the correct visualization of the tables and columns included in those migrated domains. In some cases, editing the domains via Domain Designer can also result in undesirable results.

In the Domain Designer, when you are editing a domain and you navigate to the Tables tab, the tables and columns listed in Selected Tables (right panel) display in red indicating that those database entities do not exist in the database. A pop-up message shows up explaining that the schema contains resources that are not in the selected data source and their information will be deleted.

Environment

Clarity with Jaspersoft  in Google Cloud Platform 

 

Cause

This is a defect DE53507 and fixed by upgrading to Jaspersoft 7.1.3 

Resolution

Click Cancel when prompted with the Export Bundle pop-up message. By clicking Cancel the resources incorrectly listed as not existing in the data source will not be deleted from the domain schema.

This issue applies to custom domains migrated to PostgreSQL and custom domains created from copies of OOTB domains. This issue only applies to PostgreSQL.

Please follow the instructions below in case you need to edit those custom domains.

Important!  Always save a backup of the domain schema file before making any changes.

Instructions:

 

Default domains:

We strongly recommend that you do not make any changes to the default domains (OOTB domains) that come with the application and advanced reporting solution. These domains are dynamically updated and overwritten during upgrades and restore procedures. Changes to these domains can be lost in restore procedures or can damage their structure causing issues for the dynamic update. Instead, always make a copy of the default domain and then make your changes to the copy.

Custom Domains created in PostgreSQL:

The domains created from scratch in PostgreSQL environments do not have this issue. Only domains that are migrated from Oracle environments or are copies of the default domains have this issue. This issue only applies to PostgreSQL.

Custom Domains migrated to PostgreSQL:

  1. Tables tab - Adding a new column to a previously selected table in a custom domain.
  2. Tables tab - Adding a new table and columns to selected tables in a custom domain.
  3. Display tab - Exposing columns.
  4. Calculated Fields tab - Adding a calculated field.
  5. Derived Tables tab - Adding a derived table.
  6. Removing a previously selected table from the selected tables.
  7. Removing a previously selected column from a selected table.
  1. Tables tab - Adding a new column to a previously selected table in a custom domain

Follow these instructions if you created a new attribute for an object in Clarity Studio, or included a new attribute to the Data Warehouse, and now you need to add the attribute to your existing custom domains so it can be exposed in your ad hoc reports.

In order to avoid syntax issues and incorrect data types, we recommend that you create a dummy domain and export its design so you can have an example of the <field> entry for the new custom attribute that you can use later.

To create the dummy domain:

  1. Log in to Clarity as an administrator and navigate to Advanced Reporting.
  2. Select Create > Domain.
  3. Enter a name for the domain and under Location, browse to select the location.
  4. Under Data Source, browse to select the data source.
  5. Click Create with Domain Designer to launch the domain designer.
  6. Select the corresponding database schema.
  7. In the Tables tab, select the table where the attribute is included.

 For this example we are going to use the Investment object where a new_domain_attr attribute was created as data type String. In this case, we are going to select the dwh_inv_investment table.

  1. Navigate to the Display tab and select any given attribute or the entire table.
  2. Click the Export Design button now that is available.
  3. Click No when prompted to confirm if you want to include the schema name in the table names.

 

  1. Save the schema xml file locally and edit it using an external editor.
  2. Locate the attribute <field> entry in the schema file.

For this example, the <field> entry is included as part of the DWH_INV_INVESTMENT table definition (tableName="dwh_inv_investment") as follows:

 <field id="new_domain_attr" fieldDBName="new_domain_attr" type="java.lang.String"/>

  1. Save this entry to use it next.

To edit the custom domain:

  1. Log in to Clarity as an administrator and navigate to Advanced Reporting.
  2. Locate the custom domain.
  3. Right-click the existing custom domain and select Edit from the context menu.
  4. Under the Domain Designer, click Edit with Domain Designer.
  5. Click the Export Design button in the toolbar to export the XML file of the domain.

 Important!  Always save a backup of the domain schema file before making any changes.

  1. Cancel editing the domain.
  2. Edit the XML file in an external editor.
  3. Locate the < jdbcTable> entry corresponding to the table where the new attribute should be added.

For this example, search for tableName="dwh_inv_investment".

  1. Include the entry previously created. It can be the last entry. Optionally, change the ID to uppercase letters so it is consistent with the other references.

 <field id="NEW_DOMAIN_ATTR" fieldDBName="new_domain_attr" type="java.lang.String"/>

  1. Locate all join trees where the table is included. The new attribute should also be included in the join tree among other attributes from the same table. Search for <jdbcTable id="JoinTree, for example and include the attribute.

For this example, the <field> entry is included as part of the JoinTree_1 with all other DWH_INV_INVESTMENT attributes. Note that for this type of entry you need to include the table alias as part of the id.

<field id="DWH_INV_INVESTMENT.NEW_DOMAIN_ATTR" fieldDBName="new_domain_attr" type="java.lang.String"/>

  1. Save the domain schema file.
  2. Navigate back to the Domain Designer and select the Upload option to import the XML file modified externally. The domain is uploaded if there are no syntax or integrity issues.
  3. Click Submit to update the domain.

 Now that the field is part of the table, you can edit the domain again and expose it from the Display tab or use it in calculated attributes. These changes can be made from the Domain Designer.

  1. Tables tab - Adding a new table and columns to selected tables in a custom domain

Follow these instructions if you need to add a table that was not already selected to your existing custom domains so you can satisfy a join requirement or expose the columns on this table in your ad hoc reports.

Important!  Always save a backup of the domain schema file before making any changes.

To edit the custom domain:

  1. Log in to Clarity as an administrator and navigate to Advanced Reporting.
  2. Locate the custom domain.
  3. Right-click the existing custom domain and select Edit from the context menu.
  4. Under the Domain Designer, click Edit with Domain Designer.
  5. Navigate to the Tables tab.
  6. Click Cancel when prompted with the Export Bundle pop-up message.

Important!  If you click OK instead of Cancel, cancel editing the domain and start over. The resources won’t be removed if you do not save the domain.

  1. Select the new table from the left panel to move it to the Selected Tables (right panel).
  2. Navigate to the Joins tab to include the new table as part of the join tree.
  3. Define the join between an existing table and the new table as you would normally do it.
  4. Click OK.

 

Important!  If after you click OK, you receive a message “Please Wait Loading…” and the application spins and does not respond, click the Refresh button available in Clarity (not the Refresh button in the browser) and click OK again.

 

  1. Click Submit to update the domain.

 

Now that the table is included in the domain and the join established, you can edit the domain again and expose the table columns from the Display tab or use them in calculated attributes. These changes can be made from the Domain Designer.

  1. Display tab - Exposing columns

This issue does not affect the Display tab. You can modify display sets and fields from the Domain Designer as you usually do.

  1. Calculated Fields tab - Adding a calculated field

When creating a calculated field for the domain, write the expression to compute a value based on the columns of the same join tree using the correct alias for those columns.

For example,

concat(DWH_INV_INVESTMENT.ENTITY,' - ',DWH_INV_INVESTMENT.DEPARTMENT )

“DWH_INV_INVESTMENT” is the table alias for the tableName="dwh_inv_investment" whereas “ENTITY” and “DEPARTMENT” are the column aliases for the fieldDBName="entity" and fieldDBName="department" respectively.

  1. Derived Tables tab - Adding a derived table

When adding a derived table for the domain, write the query as you would normally do. When you join the derived table and you click OK, if you receive a message “Please Wait Loading…” and the application spins and does not respond, click the Refresh button available in Clarity (not the Refresh button in the browser) and click OK again.

 

  1. Removing a previously selected table from the selected tables

Follow these instructions if you need to remove a table that was already included in your custom domains.

You can only remove a table from a domain if its columns are not used by any ad hoc view/report. If your ad hoc views/reports are referencing any of the columns of that table, you need to first remove them from the ad hoc views/reports and then remove from the domain.

Once no ad hoc view/report is referencing the table columns, edit the domain and remove references to the table and columns from display fields, calculated fields, derived tables, pre-filters, joins and any other reference. After that you can navigate to the Tables tab and remove the table.

To edit the custom domain:

  1. Log in to Clarity as an administrator and navigate to Advanced Reporting.
  2. Locate the custom domain.
  3. Right-click the existing custom domain and select Edit from the context menu.
  4. Under the Domain Designer, click Edit with Domain Designer.
  5. Navigate to Display, Pre-Filters, Calculated Fields and Derived Tables and remove any references to the table and table columns.
  6. Navigate to the Joins tab and remove any joins to the table.
  7. Click OK.

 

Important!  If after you click OK, you receive a message “Please Wait Loading…” and the application spins and does not respond, click the Refresh button available in Clarity (not the Refresh button in the browser) and click OK again.

  1. Click Submit to update the domain.
  2. Edit the domain again and navigate to the Tables tab.
  3. Click Cancel when prompted with the Export Bundle pop-up message.

Important!  If you click OK instead of Cancel, cancel editing the domain and start over. The resources won’t be removed if you do not save the domain.

  1. Select the selected table from the right panel to remove it from the Selected Tables (right panel).
  2. Click OK.
  3. Click Submit to update the domain.

 

  1. Removing a previously selected column from a selected table

Follow these instructions if you need to remove a column that was already included in your custom domains.

You can only remove a column from a domain if the column is not used by any ad hoc view/report. If your ad hoc views/reports are referencing that column, you need to first remove it from the ad hoc views/reports and then remove from the domain.

Once no ad hoc view/report is referencing the column, edit the domain and remove references to the column from display fields, calculated fields, derived tables, pre-filters, joins and any other reference.  After that you can edit the XML file and remove it from the tables.

To edit the custom domain:

  1. Log in to Clarity as an administrator and navigate to Advanced Reporting.
  2. Locate the custom domain.
  3. Right-click the existing custom domain and select Edit from the context menu.
  4. Under the Domain Designer, click Edit with Domain Designer.
  5. Navigate to Display, Pre-Filters, Calculated Fields and Derived Tables and remove any references to the table and table columns.
  6. Navigate to the Joins tab and remove any joins to the table.
  7. Click OK.

 

Important!  If after you click OK, you receive a message “Please Wait Loading…” and the application spins and does not respond, click the Refresh button available in Clarity (not the Refresh button in the browser) and click OK again.

  1. Click Submit to update the domain.
  2. Edit the domain again.
  3. Click the Export Design button in the toolbar to export the XML file of the domain.

 

Important!  Always save a backup of the domain schema file before making any changes.

  1. Cancel editing the domain.
  2. Edit the XML file in an external editor.
  3. Locate all references to the column (fieldDBName) and remove all those entries.

For this example, search for fieldDBName="new_domain_attr" and remove all references as follows:

<field id="NEW_DOMAIN_ATTR" fieldDBName="new_domain_attr" type="java.lang.String"/>

 <field id="DWH_INV_INVESTMENT.NEW_DOMAIN_ATTR" fieldDBName="new_domain_attr" type="java.lang.String" />

  1. Save the domain schema file.
  2. Navigate back to the Domain Designer and select the Upload option to import the XML file modified externally. The domain is uploaded if there are no syntax or integrity issues.
  3. Click Submit to update the domain.

Now that the field is removed from the table, you can edit the domain again and make other changes as desired from the Domain Designer.

 

 

 

Additional Information

Understanding the issue in more detail:

In Jaspersoft, ad hoc queries are built using quoted table and column names and quoted names in PostgreSQL are case-sensitive. For instance, “dwh_inv_investment” is not equivalent to “DWH_INV_INVESTMENT”, they are two different tables.

Table and column names specified in the domain schema xml file must match the table and columns names used in the database.

In order to support ad hoc views and domains migrated to PostgreSQL that were previously created in Oracle, domains were modified to have lowercase references to table and column names whereas aliases were kept uppercase.

 Table name example:

 <jdbcTable id="DWH_INV_INVESTMENT" datasourceId="CA_PPM_DWH_BEAN" tableName="schema1.dwh_inv_investment">

The table name specified by the “tableName” property must be lowercase to match the table name in PostgreSQL.

The table alias specified by the “id” property must be uppercase to match existing references in ad hoc views and reports.

 Column name example:

 <field id="INVESTMENT_NAME" type="java.lang.String" fieldDBName="investment_name"/>

The column name specified by the “fieldDBName” property must be lowercase to match the column name in PostgreSQL.

The column alias specified by the “id” property must be uppercase to match existing references in ad hoc views and reports.

 

Powered by Wolken Software