Domain migration to PostgreSQL environment

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

How to move custom domains when doing a migration to Postgres (from Oracle or MSSQL). How was the migration of custom domains handled on GCP?

Environment

Release : Any

Resolution

The following has to be done during the migration of custom Jaspersoft domains to Postgres:

• Changes in column and table names to lowercase.

If you export a domain from an Oracle environment, open the schema.xml.data file and then collapse the main tags inside the domain xml schema, you will find 4 main blocks.

 

 

<itemGroups> is where we define how the domain will display in the Ad hoc view so users can select fields and build their ad hoc reports. It contains aliases to columns and tables and these aliases are used by ad hoc views. We do not recommend changing these aliases as they would most likely break ad hoc views based on the domain.

 

<resources> is where we establish the association between aliases and tables, and also columns.

 

First, change the table names in datasourceTableName to lowercase. Do not change the id. This is the table alias referenced in <itemGroups>

 <jdbcTable id="DWH_INV_INVESTMENT" datasourceId="CA_PPM_DWH_BEAN" schemaAlias="schema1" datasourceTableName="dwh_inv_investment">

Second, change the column names in fieldDBName to lowercase. Again, do not change the id. This is also the column alias referenced in <itemGroups>

 

        <jdbcTable id="DWH_INV_INVESTMENT" datasourceId="CA_PPM_DWH_BEAN" schemaAlias="schema1" datasourceTableName="dwh_inv_investment">
            <fieldList>
                <field id="BASELINE_FINISH" type="java.sql.Timestamp" fieldDBName="baseline_finish"/>

• OOTB domains resolve the language code through an expression. We also change the fieldDBName there to lowercase.

<field id="CALC_LANGUAGE_CODE" type="java.lang.String" dataSetExpression="groovy('authentication.getPrincipal().getAttributes().find{ it.attrName == &quot;ppmUserLanguage&quot; }.attrValue')" fieldDBName="calc_language_code"/>

 

• Joins also use aliases to reference tables and columns, so it does not require changes.

            <joinInfo alias="DWH_INV_INVESTMENT" referenceId="DWH_INV_INVESTMENT"/>
            <joinList>
                <join left="DWH_INV_INVESTMENT" right="DWH_INV_INVESTMENT_LN" type="inner" expr="DWH_INV_INVESTMENT.INVESTMENT_KEY == DWH_INV_INVESTMENT_LN.INVESTMENT_KEY 

and DWH_INV_INVESTMENT_LN.LANGUAGE_CODE == DWH_INV_INVESTMENT_LN.CALC_LANGUAGE_CODE" weight="1"/>

                <join left="DWH_INV_INVESTMENT" right="DWH_INV_PROJECT" type="inner" expr="DWH_INV_INVESTMENT.INVESTMENT_KEY == DWH_INV_PROJECT.INVESTMENT_KEY" weight="1"/>

• The security file also uses aliases, so no changes there as well.

<filterExpression>DWH_INV_SECURITY_V.USER_UID == groovy('authentication.principal.username')</filterExpression>

• OOTB domains do not include any derived tables so you will not have to change anything related to that, however, you may have to go through all derived tables and make sure they are compatible with Postgres. Derived tables are queries like views, so you will have to convert them and make sure column and table names are lowercase in their xml references.

• The Update Domain Schema is always a good option to fix/update the domain schema syntax after making changes, so we recommend to run it for each domain. 

 

• As always, we recommend creating backups of all domains and ad hoc views/reports associated to the domains before making any changes.